Search code examples
postgresql

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

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?

Solution

  • 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.