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;

Vao Tsun · Accepted Answer · 2017-06-21 08:03:55Z

4

You can create user mapping:

create server application_srv foreign data wrapper dblink_fdw OPTIONS (...;
create user mapping FOR app_user SERVER application_srv OPTIONS (user 'user_to_connect', password 'password goes here');
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

answered Jun 21, 2017 at 8:03

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Neoheurist Over a year ago

Nice idea. Unfortunately it seems that in order to do the CREATE SERVER command I need to define a FOREIGN DATA WRAPPER and to define the FOREIGN DATA WRAPPER I need to have superuser privileges which puts me back where I started...

Vao Tsun Over a year ago

no - I gave this solution from my tollbox. this is how I do it on RDS. please disclose the statement and error

Neoheurist Over a year ago

My mistake. I needed to install the dblink extension in the database in which I was trying to add the FOREIGN DATA WRAPPER - I was experimenting in a database that did not have the dblink extension installed...

Neoheurist Over a year ago

My calls are working - but when I create a do the CREATE SCHEMA the schema is being created in the wrong database - how do I set or change the database for the user mapping? (there isn't an option for dblink to pass the database name to connect)

Vao Tsun Over a year ago

create server application_srv foreign data wrapper dblink_fdw OPTIONS (dbname 'a') will create a server to connect to database name 'a'

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related