如果服务器在使用 dblink 时未请求密码，则非超级用户无法连接 [英] Non-superuser cannot connect if the server does not request a password while using dblink

问题描述

我想在我的应用程序中进行一些跨数据库引用.简而言之，我有两个名为 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);

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?

解决方案

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.

这篇关于如果服务器在使用 dblink 时未请求密码，则非超级用户无法连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！