使用postgres_fdw通过SSL连接PostgreSQL外部服务器遇权限错误

使用postgres_fdw通过SSL连接PostgreSQL外部服务器时，该配置在其他服务器可正常运行，但新机器上执行导入外部Schema命令时失败。

创建外部服务器的SQL命令如下：

create
    server my_fdw
    foreign data wrapper postgres_fdw
    options (
        host 'localhost',
        port '5432',
        dbname 'my_foreign_db',
        use_remote_estimate 'on',
        sslrootcert '/home/myhome/.postgresql/root.crt',
        sslcert '/home/myhome/.postgresql/postgresql.crt',
        sslkey '/home/myhome/.postgresql/postgresql.key',
        sslmode 'verify-full'
    )
;

grant usage on foreign server my_fdw to myuser;

create user mapping
    for myuser
    server my_fdw
    options (password_required 'false')
;

可正常通过SSL连接主数据库，但执行以下导入命令时：

import 
    foreign schema foreign_schema
    from server my_fdw
    into local_schema
;

出现权限错误：

ERROR:  could not connect to server "my_fdw"
DETAIL:  connection to server at "localhost" (::1), port 5432 failed: could not read root certificate file "/home/myhome/.postgresql/root.crt": Permission denied

证书文件归myhome用户所有，权限为0600。临时放宽权限可绕过错误，但会导致PostgreSQL运行失败（证书文件权限必须为0600或更低）。

PostgreSQL后台进程以postgres系统用户身份运行，而非你登录的myhome用户：

• 使用psql客户端连接主数据库时，是myhome用户的进程读取证书文件，因此权限正常；
• 执行postgres_fdw的外部连接操作时，是PostgreSQL的后台进程（postgres用户）尝试读取证书文件，但/home/myhome/.postgresql目录及文件属于myhome用户，权限0600会阻止postgres用户访问，因此触发权限拒绝错误。

方案1：将证书迁移到PostgreSQL进程可访问的目录 #

将证书文件移动到postgres用户拥有权限的目录（如PostgreSQL数据目录下的专用目录）：

1. 创建postgres用户的证书目录（若不存在）：

   mkdir -p /var/lib/postgresql/.postgresql
   chown postgres:postgres /var/lib/postgresql/.postgresql
   

2. 复制证书文件并修改权限：

   cp /home/myhome/.postgresql/*.crt /var/lib/postgresql/.postgresql/
   cp /home/myhome/.postgresql/*.key /var/lib/postgresql/.postgresql/
   chown postgres:postgres /var/lib/postgresql/.postgresql/*
   chmod 0600 /var/lib/postgresql/.postgresql/*
   

3. 更新外部服务器的SSL选项路径：

   ALTER SERVER my_fdw OPTIONS SET sslrootcert '/var/lib/postgresql/.postgresql/root.crt';
   ALTER SERVER my_fdw OPTIONS SET sslcert '/var/lib/postgresql/.postgresql/postgresql.crt';
   ALTER SERVER my_fdw OPTIONS SET sslkey '/var/lib/postgresql/.postgresql/postgresql.key';
   

方案2：配置PostgreSQL全局SSL证书参数 #

若外部服务器为本地实例，可通过postgresql.conf设置全局SSL证书路径，避免在外部服务器中单独指定：

1. 修改postgresql.conf（路径通常为/etc/postgresql/<version>/main/postgresql.conf）：

   ssl_ca_file = '/var/lib/postgresql/.postgresql/root.crt'
   ssl_cert_file = '/var/lib/postgresql/.postgresql/postgresql.crt'
   ssl_key_file = '/var/lib/postgresql/.postgresql/postgresql.key'
   

2. 重启PostgreSQL服务：

   systemctl restart postgresql
   

3. 移除外部服务器中的单独SSL路径配置：

   ALTER SERVER my_fdw OPTIONS DROP sslrootcert;
   ALTER SERVER my_fdw OPTIONS DROP sslcert;
   ALTER SERVER my_fdw OPTIONS DROP sslkey;
   

内容的提问来源于stack exchange，提问作者Reid Swanson