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?