PostgreSQL RDS avoid hard coding the connection password when using dblink_connect()

Question

I have an AWS RDS instance of PostgreSQL in which I need to execute an SQL statement within a function using dblink_connect(text) and dblink_exec(text) while logged in with the postgres role (that I created).

CREATE OR REPLACE FUNCTION application.create_tenant_schemas(first integer, last integer) RETURNS void AS
DECLARE
  tenant VARCHAR;
  sql VARCHAR;
BEGIN
  FOR index IN first..last LOOP
    tenant := 'tenant_' || to_char(index, 'FM00000');

    sql := 'CREATE SCHEMA ' || quote_ident(tenant);
    RAISE NOTICE '%', sql;

    PERFORM dblink_connect('dbname=application user=postgres');
    PERFORM dblink_exec(sql);
    PERFORM dblink_disconnect();

  END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

The dblink_exec() is producing the following error messages:

[2F003] ERROR: password is required
Detail: Non-superusers must provide a password in the connection string.
Where: SQL statement "SELECT dblink_connect('dbname=application user=postgres')"

I found an answer that suggested using dblink_connect_u(text). When I tried this I got the following error messages:

[42501] ERROR: permission denied for function dblink_connect_u
Where: SQL statement "SELECT dblink_connect_u('dbname=application user=postgres')"

On AWS how can I give the user that created the RDS instance permission to execute function dblink_connect_u()? I tried the following without success:

GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO postgres;
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO postgres;

It seems my postgres user needs superuser permissions to which I apparently cannot have on AWS.

I also played with using %APPDATA%\postgresql\pgpass.conf (on Windows) to provide the password for dblink_connect(text) but dblink_connect(text) apparently ignores this file.

I was able to use a hard coded the password string to call dblink_connect(text) as follows:

PERFORM "pascal"."dblink_connect_u"('dbname=pascal user=postgres password=secret');

...ultimately, this is not an acceptable solution due to the hard coding of the password.

Does anyone have a suggestion for how to get RDS PostgreSQL to either use the password file or allow me to GRANT EXECUTE ON FUNCTION dblink_connect_u(text) or is there another alternative that I haven't come across?

UPDATE

Links to PostgreSQL documentation for trying to set up a foreign data wrapper / server in which to store the password for the user that's executing dblink_connect(text)

CREATE SERVER

CREATE FOREIGN DATA WRAPPER

CREATE USER MAPPING

CONCLUSION

CREATE SERVER "password_server" FOREIGN DATA WRAPPER "dblink_fdw"
OPTIONS (dbname 'application');

CREATE USER MAPPING FOR "postgres"
SERVER "password_server"
OPTIONS (user 'postgres', password 'pa55VV0&d');

... different source file ...

CREATE OR REPLACE FUNCTION application.create_tenant_schemas(first integer, last integer) RETURNS VOID AS $$
DECLARE
  tenant VARCHAR;
  sql VARCHAR;
BEGIN
  FOR index IN first..last LOOP
    tenant := 'tenant_' || to_char(index, 'FM00000');

    sql := 'CREATE SCHEMA ' || quote_ident(tenant);
    RAISE NOTICE '%', sql;

    PERFORM "dblink_connect"('password_server');
    PERFORM "dblink_exec"(sql);
    PERFORM "dblink_disconnect"();
  END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

Answer 1

You can create user mapping:

1. create server application_srv foreign data wrapper dblink_fdw OPTIONS (...;
2. create user mapping FOR app_user SERVER application_srv OPTIONS (user 'user_to_connect', password 'password goes here');
3. use application_srv in dblink connection name:

.

t# select * from dblink('application_srv','select max(t) from t') as t(m timestamp(0));
          m
---------------------
 2017-06-13 11:41:05
(1 row)

now the password will be displayed plain text in pg_user_mappings (pg_user_mapping should be not selectable for anyone in RDS), but the actual pass will be displayed only to rds_superuser