今天碰到个 PostgreSQL db_link 问题， 问题是这样的，数据库 mapfriends 需要访问数据库skytf 的一张表 test_41, 这两个数据库是在同一台主机上，在创建好了 view 后，查询报错，以下为详细信息。

创建视图

mapfriends库创建视图，如下：

1
2
3
4
5

CREATE OR REPLACE VIEW view_test_41 AS  
SELECT link_test_41.id  
 FROM dblink('dbname=skytf host=localhost port=1921 user=skytf password=skytf' ::text,  
 'select * from test_41'::text)  
 link_test_41( id integer,name character varying(32) );

备注：上面创建视图完成。

查询视图报错

1
2
3
4

mapfriends=> select * From view_test_41;  
ERROR: password is required  
DETAIL: Non-superuser cannot connect if the server does not request a password.  
HINT: Target servers authentication method must be changed.

备注：刚开始看到这个报错觉得很奇怪，因为在上一步创建 view 语句里已经写了数据库的密码，而这里又提示非超级用户需要输入密码,接着查看 pg_hba.conf 文件，也没发现什么异常。于是google一把没发现啥有用信息，后来直接咨询德哥了，德哥了解了一下情况后，觉得可能是 pg_hba.conf 的配置问题；

查看 pg_hba.conf 文件

1
2
3
4
5
6
7
8

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only  
local all all trust
# IPv4 local connections:  
host all all 127.0.0.1/32 trust  
host all all 0.0.0.0/0 md5
# IPv6 local connections:  
host all all ::1/128 trust

备注：这是老的 pg_hba.conf 文件，德哥发现下面这行有问题，即 host all all 127.0.0.1/32 trust， 建议认证方式改为 md5 试下。

修改后的 pg_hba.conf 文件

1
2
3
4
5
6
7
8
9

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only  
local all all trust
# IPv4 local connections: 增加一行  
host skytf skytf 127.0.0.1/32 md5  
host all all 127.0.0.1/32 trust  
host all all 0.0.0.0/0 md5
# IPv6 local connections:  
host all all ::1/128 trust

这里增加一行 skytf 库的 md5 认证方式, 重新加载 pg_hba.conf 使其生效, 重新加载命令:

1

pg_ctl reload -D $PGDATA )

再次查询视图

1
2
3
4
5
6
7

mapfriends=> select * from view_test_41;  
id  
----  
 1  
 2  
 3  
(3 rows)

备注：在修改 pg_hba.conf 文件后，果然可以查询了，看来德哥的思路是正确的，这次学习了。

查询 VIEW 定义

1
2
3
4
5
6
7
8

mapfriends=> select * from pg_views where viewname='view_test_41';  
schemaname | viewname | viewowner |  
definition  
------------+--------------+------------+-------------------------------------------------------------------------------------------  
-------------------------------------------------------------------  
mapfriends | view_test_41 | mapfriends | SELECT link_test_41.id FROM dblink('dbname=skytf host=localhost port=1921 user=skytf passw  
ord=skytf'::text, 'select * from test_41'::text) link_test_41(id integer, name character varying(32));  
(1 row)

备注：上面SQL可以查出视图的定义，包括主机，用户名，密码信息，并且以明文形式，所以 db_link 是一种危险的行为，这很容易暴露生产环境的信息，所以不建议在生产环境上用 db_link。

总结

之前查询视图报错是因为原来的 pg_hba.conf 设置了主机的认证方式为 trust, 而创建 db_link 语法里需要提供密码信息，这一点上是矛盾的，所以抛出之前的错误信息,解决方法只需要在将 trust 改为 md5 ，或者加一条db_link 到目标库 的 md5 认证方式即可。