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?
Only superusers may use dblink_connect to create non-password-authenticated connections. If non-superusers need this capability, use dblink_connect_u instead.
dblink_connect_u() is identical to dblink_connect(), except that it will allow non-superusers to connect using any authentication method.
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
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');
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.
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.
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
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.