Postgres to Postgres DBLink with AWS/RDS

Question

I am trying to use dblink functionality for Postgres 9.5.2 DB to Postgres 9.52 on AWS/RDS.

On the server:

CREATE SERVER myremotedb FOREIGN DATA WRAPPER
    dblink_fdw OPTIONS (host 'myremotedb.123456.eu-west-1.rds.amazonaws.com', port '5432', dbname 'my_db');

CREATE USER MAPPING FOR foouser SERVER myremotedb OPTIONS (user 'foouser', password '1234');

GRANT USAGE ON FOREIGN SERVER myremotedb TO foouser;

On the client:

SELECT dblink_connect('myremotedb', 'host=myremotedb.123456.eu-west-1.rds.amazonaws.com , port=5432 , user=foouser , password=1234 , dbname=my_db');

select * from dblink('myremotedb', 'SELECT * FROM my_data') AS T(id integer, name text);

but what I get is:

ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.

Any idea how can I start a connection?

Answer 1

You need to configure the remote server so that it demands a password when logging on as foouser to mwu. Right now it is using one of the other authentication methods.

Answer 2

The issue on the client side is that connection string should NOT have commas.

set role = myrole; 
select extension_data.dblink_connect('host=localhost dbname=db'); --ERROR:  password is required

set role = myrole;
select extension_data.dblink_connect('host=localhost , dbname=db'); --ERROR:  password is required

set role = myrole; 
select extension_data.dblink_connect('host=localhost dbname=db user=myrole password=somepass'); --OK

set role = myrole; 
select extension_data.dblink_connect('host=localhost , dbname=db , user=myrole , password=somepass'); --ERROR:  password is required

set role = postgres;
select extension_data.dblink_connect('host=localhost dbname=db'); --OK

set role = postgres;
select extension_data.dblink_connect('host=localhost , dbname=db'); --ERROR:  could not establish connection

Answer 3

Change the "trust" to "md5" on pg_hba.conf