Non-superuser cannot connect if the server does not request a password while using dblink

Question

I want to do some cross database references 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?

Answer 1

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.

Answer 2

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