问题描述
我想在我的应用程序中进行一些跨数据库引用.简而言之,我有两个名为 meta 和 op 的数据库.我想做一些从元到 op 数据库中的表的选择查询,如下所示,但出现以下错误.我尝试使用密码和不使用密码.顺便说一下,caixa
用户是非超级用户,我的目标服务器(op
db 服务器具有 MD5 身份验证模式.)
meta=>select * from dblink('dbname=op password=caixa','SELECT op_col from op_table') AS t(op_col varchar);
错误:需要密码
详细信息:如果服务器不请求密码,非超级用户将无法连接.
提示:必须更改目标服务器的身份验证方法.
上述错误消息中的提示暗示了什么?我需要更改服务器的身份验证模式吗?如果不更改服务器的身份验证模式 (MD5),我就不能运行上述查询吗?
来自文档:
只有超级用户可以使用 dblink_connect 创建未经密码验证的连接.如果非超级用户需要这个功能,请改用 dblink_connect_u.
和
dblink_connect_u() 与 dblink_connect() 相同,除了它将允许非超级用户使用任何身份验证方法进行连接.
这意味着您的 dblink
调用正在使用 dblink_connect
隐式.改用 dblink_connect_u
或更改您的身份验证方法例如md5.
请注意,您还需要向 caixa
角色授予执行权限,例如:
GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO caixa;授予执行功能 dblink_connect_u(text, text) TO caixa;
工作示例(在 GRANT
之后):
meta=>SELECT dblink_connect_u('conn1', 'dbname=op');元=>SELECT * FROM dblink('conn1','SELECT op_col from op_table')AS t(op_col varchar);op_col--------啊啊啊啊bbb抄送(3 行)元=>选择 dblink_disconnect('conn1');
抱歉回答有点误导.当然,您不需要需要 dblink_connect_u
进行 md5 身份验证联系.我看到了一种可能性.PostgreSQL 有两种不同的连接类型:主机和本地.
运行:
psql -h localhost ..
包含主机连接,但
dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');
使用local类型,所以如果你有本地连接的非密码方法(例如ident方法或trust),那么它返回
错误:需要密码详细信息:如果服务器未请求密码,则非超级用户无法连接.提示:必须更改目标服务器的身份验证方法.
检查
dblink_connect('mycon','hostaddr=127.0.0.1 dbname=vchitta_op user=caixa password=caixa')
用于主机连接.如果可能的话,为了清楚起见,请发布您的 pg_hba.conf
.
我还检查了 vchitta_op
DB 上的 CONNECT
权限,但错误消息不同:
REVOKE CONNECT ON DATABASE vchitta_op FROM PUBLIC;从 caixa 撤销数据库 vchitta_op 上的连接;SELECT dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');错误:无法建立连接详细信息:致命:数据库vchitta_op"的权限被拒绝详细信息:用户没有 CONNECT 权限.
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);
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?
From documentation:
and
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 authenticatedconnection. 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.
这篇关于如果服务器在使用 dblink 时未请求密码,则非超级用户无法连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!