From 文档 http://www.postgresql.org/docs/current/static/contrib-dblink-connect.html:
只有超级用户可以使用 dblink_connect 来创建
无需密码验证的连接。如果非超级用户需要这个
能力、用途dblink_connect_u反而。
and
dblink_connect_u() 与 dblink_connect() 相同,只是它
将允许非超级用户使用任何身份验证方法进行连接。
这意味着你的dblink http://www.postgresql.org/docs/current/static/contrib-dblink.html呼叫正在使用dblink_connect http://www.postgresql.org/docs/current/static/contrib-dblink-connect.html隐含地。使用dblink_connect_u http://www.postgresql.org/docs/current/static/contrib-dblink-connect-u.html相反或将您的身份验证方法更改为例如md5。
请注意,您还需要授予执行权限caixa
角色,例如:
GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO caixa;
GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO caixa;
工作示例(之后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:
抱歉,答案略有误导。当然你don't need dblink_connect_u
用于 md5 验证
联系。我认为有一种可能性。 PostgreSQL 有两个不同的连接类型:host and local.
Running:
psql -h localhost ..
包含主机连接,但是
dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');
uses local类型,因此如果您有用于本地连接的非密码方法(例如 ident 方法或 trust),那么它会返回
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联系。为了清楚起见,如果可能,请发布您的pg_hba.conf
.
我还检查了关于CONNECT
特权于vchitta_op
DB,但错误消息不同:
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.