3

We got a Postgres database credentials (with SSL on) from our contractor, which allow us to connect to the DB using pdAdmin 3. The DB is read-only (can't do pg_dump) to us and basically the contractor will not grant us more privileges.

We need to fetch some data from this remote DB to our local DB. So I wanted to use dblink to perform this task.

I run this on psql:

insert into shifts select * from dblink('hostaddr=remote_addr port=9000 dbname=production user=user password=passwd', 'select user_id, location_id from shifts') as t1(user_id integer, location_id integer);

Then I got:

ERROR: password is required DETAIL: Non-superuser cannot connect if the server does not request a password. HINT: Target server's authentication method must be changed.

Since I am new to Postgres and dblink, I have no idea why it is complaining there is no password. And I wonder, to do a dblink connection, does the remote database needs to grant any more privileges?

If the pdAdmin 3 is able to connect to the remote DB with the credentials, what should I do to make dblink work?

Thanks!

nigong
  • 1,727
  • 3
  • 19
  • 33

1 Answers1

0

Yes only superuser can provide you to the facility to connect through DBLINK

just run this command below whether you are able to connect to database 

SELECT dblink_connect('myconn', 'dbname=postgres');
SELECT dblink_connect('myconn', 'hostaddr=remote_addr port=9000 dbname=production user=user password=passwd');

just give the name of database u want to connect after dbname

You can connect 

dblink_connect_u

dblink_connect_u() is identical to dblink_connect(), except that it will allow non-superusers to connect using any authentication method.

Link on postgres site is dblink

For Superuser ask them to create extension 

CREATE EXTENSION dblink;

for your database or schema .

SarthAk
  • 1,628
  • 3
  • 19
  • 24