Postgres中如何无需密码使用dblink 避免函数中明文存储密码

dblink运行在PostgreSQL服务进程的上下文环境中，和手动执行psql命令的用户环境可能存在差异，导致无法读取pgpass的常见原因如下：

• .pgpass配置的主机名是首字母大写的Localhost，和连接时传入的localhost大小写不匹配，pgpass的匹配规则是严格大小写敏感的
• .pgpass的权限不符合要求，必须仅属主可读可写（权限位0600），且属主为PostgreSQL服务的运行用户（通常是postgres），否则会被直接忽略
• PostgreSQL服务运行用户的家目录不是/var/lib/postgres，导致服务进程找不到对应路径下的.pgpass文件

按推荐优先级排序：

方案1：同主机实例优先用unix域套接字认证（无需密码配置） #

如果远程数据库和当前数据库在同一台服务器上，直接用unix套接字连接即可完全跳过密码校验：

1. 修改pg_hba.conf配置文件，添加如下规则：

local   remote_db_name  myuser  peer

2. 执行命令重载PostgreSQL配置：

pg_ctl reload

3. 调用dblink_connect时不要指定host参数，默认走unix套接字连接，不需要输入密码：

SELECT dblink_connect('dbname=remote_db_name user=myuser');

方案2：使用FDW用户映射存储密码（安全性最高） #

通过PostgreSQL外部数据包装器的用户映射功能存储密码，密码仅对超级用户可见，不会出现在函数代码中：

1. 安装必要扩展：

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

2. 创建外部服务器配置，无需填写密码：

CREATE SERVER remote_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', port '5432', dbname 'remote_db_name');

3. 创建用户映射，绑定远程账号密码：

CREATE USER MAPPING FOR CURRENT_USER
SERVER remote_db_server
OPTIONS (user 'myuser', password 'mypassword');

4. 函数中调用dblink时直接引用服务器名即可，无需填写连接串：

SELECT dblink_connect('remote_db_server');

方案3：修复.pgpass配置让dblink可以正常读取 #

如果要继续使用pgpass的方式，按以下要求修正配置：

1. 把.pgpass里的Localhost改为全小写的localhost，确保和连接时的主机名完全一致
2. 修正.pgpass的权限和属主：

chmod 600 /var/lib/postgres/.pgpass
chown postgres:postgres /var/lib/postgres/.pgpass

3. 如果仍无法读取，可以在连接串中显式指定passfile路径：

SELECT dblink_connect('host=localhost user=myuser dbname=remote_db_name passfile=/var/lib/postgres/.pgpass');

内容的提问来源于stack exchange，提问作者B.Narasimha Kumar