postgresql 如果服务器在使用 dblink 时未请求密码，则非超级用户无法连接

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

提问by vchitta

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 caixauser is a non-super user and my target server (opdb server is having MD5 authentication mode.)

我想在我的应用程序中进行一些跨数据库引用。简而言之，我有两个名为 meta 和 op 的数据库。我想做一些从元到 op 数据库中的表的选择查询，如下所示，但出现以下错误。我尝试使用密码和不使用密码。顺便说一下，caixa用户是非超级用户，我的目标服务器（opdb 服务器具有 MD5 身份验证模式。）

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?

上述错误消息中的提示暗示了什么？我需要更改服务器的身份验证模式吗？如果不更改服务器的身份验证模式 (MD5)，我就不能运行上述查询吗？

采纳答案by Grzegorz Szpetkowski

From documentation:

从文档：

Only superusers may use dblink_connect to create non-password-authenticated connections. If non-superusers need this capability, use dblink_connect_uinstead.

只有超级用户可以使用 dblink_connect 创建非密码验证连接。如果非超级用户需要此功能，请改用dblink_connect_u。

and

和

dblink_connect_u() is identical to dblink_connect(), except that it will allow non-superusers to connect using any authentication method.

dblink_connect_u() 与 dblink_connect() 相同，不同之处在于它允许非超级用户使用任何身份验证方法进行连接。

That means your dblinkcall is using dblink_connectimplicitly. Use dblink_connect_uinstead or change your auth method to e.g. md5.

这意味着您的dblink呼叫正在dblink_connect隐式使用。使用dblink_connect_u替代或更改您的身份验证方法如MD5。

Note that you also need grant execute privilege to caixarole, for example by:

请注意，您还需要授予caixa角色执行权限，例如：

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):

工作示例（之后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'tneed dblink_connect_ufor md5 authenticated connection. There is one possibility I see. PostgreSQL has two differentconnection types: hostand local.

抱歉，回答有点误导。当然，你不能做需要dblink_connect_u的MD5验证的连接。我看到了一种可能性。PostgreSQL 有两种不同的连接类型：host和local。

Running:

跑步：

psql -h localhost ..

psql -h localhost ..

incorporates host connection, but

包含主机连接，但

dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');

dblink_connect('mycon','dbname=vchitta_op user=caixa password=caixa');

uses localtype, so if you have non-password method for local connection (for example ident method or trust), then it returns

使用本地类型，所以如果你有本地连接的非密码方法（例如 ident 方法或信任），那么它返回

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')

dblink_connect('mycon','hostaddr=127.0.0.1 dbname=vchitta_op user=caixa password=caixa')

for hostconnection. For clarity if possible please post your pg_hba.conf.

用于主机连接。如果可能的话，为了清楚起见，请发布您的pg_hba.conf.

I also checked what about CONNECTprivilege on vchitta_opDB, but error message is different:

我还检查了DB 上的CONNECT权限vchitta_op，但错误消息不同：

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.

回答by AlexM

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)

有一种解决方法对我有用。如果设置了“SECURITY DEFINER”选项，非超级用户可以以超级用户的权限执行功能。( 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

这意味着您可以创建一个函数（具有超级用户所有者和 SECURITY DEFINER 选项）进行跨数据库操作（使用dblink()不带密码）并在非超级用户下执行它

回答by kaushik

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

我有一个类似但不同的问题。我有两台具有相同 postgres.conf 和 pg_hba.conf 的服务器。然而，一个在 9.2.3 版本上，一个在 9.2.4 上

9.2.3

9.2.3

pg_hba.conf has

pg_hba.conf 有

    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

9.2.4

my pg_hba.conf has the same entry as above

我的 pg_hba.conf 与上面的条目相同

    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

现在我在 9.2.4 上更改我的 pg_hba.conf 如下

    local   all     dblinkuser      md5

and restart postgres

并重新启动 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.

我检查了 9.2.3 和 9.2.4 版本之间的更改日志，但找不到任何详细信息。

note: changing auth method from trust to md5 on 9.2.3 does not make any difference and still works.

注意：在 9.2.3 上将 auth 方法从 trust 更改为 md5 没有任何区别并且仍然有效。

回答by Tomá? Zálusky

I found this question googling for same error message, though I use fdw extension rather than db_link. Following steps helped to fix my problem:

我发现这个问题在谷歌上搜索相同的错误消息，尽管我使用 fdw 扩展名而不是 db_link。以下步骤有助于解决我的问题：

• find user has no password and set it on - alter user myuser with password 'mypassword'
• find authentication method is trustand set it to md5- vim /var/lib/postgresql/data_/pg_hba.conf
• reload pg_hba.conf- SELECT pg_reload_conf();from psql (log out and log in to verify password is required)
• (optionally try access from remote machine, db browser etc.)
• setup foreign server and its user mapping - CREATE USER MAPPING FOR CURRENT_USER SERVER myserver OPTIONS (user 'myuser', password 'mypassword');

• 找到用户没有密码并将其设置为 - alter user myuser with password 'mypassword'
• 找到身份验证方法trust并将其设置为md5-vim /var/lib/postgresql/data_/pg_hba.conf
• 重新加载pg_hba.conf-SELECT pg_reload_conf();从 psql （注销并登录以验证密码是必需的）
• （可选择尝试从远程机器、数据库浏览器等访问）
• 设置外部服务器及其用户映射 - CREATE USER MAPPING FOR CURRENT_USER SERVER myserver OPTIONS (user 'myuser', password 'mypassword');