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

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.

Tags:

Postgresql

Recent Posts