Postgres to Postgres DBLink with AWS/RDS

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?

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.

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

Change the “trust” to “md5” on pg_hba.conf