3

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?

  • DBlink works between two servers, but you have sections labelled "on the server" and "on the client". It is not clear what your setup actually is here. Is 'server' the remote server and 'client' the local server? – jjanes Oct 3 '17 at 15:57
  • @jjanes I am copying one table from one db (server) to another one (client) – Randomize Oct 3 '17 at 16:17
0

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.

  • I have removed the mwu (it was my mistake). The problem it is not there, unless I am writing in a bad way the configuration or something else is required by rds – Randomize Oct 4 '17 at 6:50
0

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

  • 4
    Could you elaborate a bit on why your solution is THE solution? Could you explain the mechanics of why modifying the entry in the pga_hba.conf file from trust to md5 would resolve the issue? – hot2use Jul 5 '18 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.