I want to do some cross database referen开发者_开发百科ces in my application. Briefly, i have two databases called meta and op. I want to do some select query from meta to a table in op database like below but getting the below error. I tried with password and without password. by the way caixa user is a non-super user and my target server (op db server is having MD5 authentication mode.)

meta=> select * from dblink('dbname=op password=caixa','SELECT op_col from op_table') AS t(op_col varchar);

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.

What the HINT in the above error message suggests? do i need to change the server's auth mode? Without changing the server's auth mode (MD5) can't i run the above query?

---

From documentation:

Only superusers may use dblink_connect to create non-password-authenticated connections. If non-superusers need this capability, use dblink_connect_u instead.

and

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

That means your dblink call is using dblink_connect implicitly. Use dblink_connect_u instead or change your auth method to e.g. md5.

Note that you also need grant execute privilege to caixa role, for example by:

GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO caixa;
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO caixa;

Working example (after GRANT):

meta=> SELECT dblink_connect_u('conn1', 'dbname=op');
meta=> SELECT * FROM dblink('conn1','SELECT op_col from op_table')
            AS t(op_col varchar);
 op_col 
--------
 aaa
 bbb
 ccc
(3 rows)
meta=> SELECT dblink_disconnect('conn1');

---

EDIT:

Sorry for slightly misleading answer. Of course you don't need dblink_connect_u for md5 authenticated connection. There is one possibility I see. PostgreSQL has two different connection types: host and local.

Running:

psql -h localhost ..

incorporates host connection, but

dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');

uses local type, so if you have non-password method for local connection (for example ident method or trust), then it returns

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.

Check

dblink_connect('mycon','hostaddr=127.0.0.1 dbname=vchitta_op user=caixa password=caixa')

for host connection. For clarity if possible please post your pg_hba.conf.

I also checked what about CONNECT privilege on vchitta_op DB, but error message is different:

REVOKE CONNECT ON DATABASE vchitta_op FROM PUBLIC;
REVOKE CONNECT ON DATABASE vchitta_op FROM caixa;

SELECT dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');
ERROR:  could not establish connection
DETAIL:  FATAL:  permission denied for database "vchitta_op"
DETAIL:  User does not have CONNECT privilege.

---

There's a workaround that did the trick for me. Non-superusers can execute functions with privileges of a superuser if "SECURITY DEFINER" option is set. ( http://www.postgresql.org/docs/9.1/static/sql-createfunction.html )

That means you can create a function (with superuser owner and SECURITY DEFINER option) that does cross-database manipulation (using dblink() without password) and execute it under non-superuser

---

I have a similar but a different issue. I have two servers with identical postgres.conf and pg_hba.conf. However one on version 9.2.3 and one on 9.2.4

9.2.3

pg_hba.conf has

    local   all     dblinkuser      trust

then I connect to database using any ordinary user

    theater_map=# select dblink_connect('dbname=TheaterDB user=dblinkuser password=dbl123');
    dblink_connect 
    ----------------
    OK
    (1 row)

success in connection.

9.2.4

my pg_hba.conf has the same entry as above

    theater_map=> select dblink_connect('dbname=TheaterDB user=dblinkuser password=dbl123');
    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.

NOW I change my pg_hba.conf on 9.2.4 as below

    local   all     dblinkuser      md5

and restart postgres

    theater_map=> select dblink_connect('dbname=TheaterDB user=dblinkuser password=dbl123');
    dblink_connect 
    ----------------
   OK
   (1 row)

---

I Checked the change log between versions 9.2.3 and 9.2.4 but could not find any details.

note: changing auth method from trust to md5 on 9.2.3 does not make any difference and still works.

---

I found this question googling for same error message, though I use fdw extension rather than db_link. Following steps helped to fix my problem:

• find user has no password and set it on - alter user myuser with password 'mypassword'
• find authentication method is trust and set it to md5 - vim /var/lib/postgresql/data_/pg_hba.conf
• reload pg_hba.conf - SELECT pg_reload_conf(); from psql (log out and log in to verify password is required)
• (optionally try access from remote machine, db browser etc.)
• setup foreign server and its user mapping - CREATE USER MAPPING FOR CURRENT_USER SERVER myserver OPTIONS (user 'myuser', password 'mypassword');

---

PostgreSQL 11.10

SELECT ext.column1 from
dblink('hostaddr=192.192.192.192 dbname=yourDbname user=yourUsername password=yourpass',
'select a."column1" from "Table1" a where a."column2"=2')
as ext(column1 text)