4

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?

2
  • 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, 2017 at 15:57
  • @jjanes I am copying one table from one db (server) to another one (client)
    – Randomize
    Oct 3, 2017 at 16:17

3 Answers 3

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.

1
  • 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, 2017 at 6:50
0

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
-1

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

1
  • 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?
    – John K. N.
    Jul 5, 2018 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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