如何用Python脚本通过SSH隧道连接仅支持Unix域套接字的PostgreSQL

问题背景 #

远程PostgreSQL仅允许Unix域套接字连接，其pg_hba.conf配置如下：

# PostgreSQL Client Authentication Configuration for IServ
# TYPE  DATABASE    USER        CIDR-ADDRESS        METHOD
# 
local   all     proftpd                 trust
local   all     all                 ident map=map1
host    all     rsyslogd        127.0.0.1/32        md5
host    all     rsyslogd        ::1/128     md5
#host   all     all     127.0.0.1/32        md5
#host   all     all     ::1/128         md5

手动通过ssh -L 9999:/var/run/postgresql/.s.PGSQL.5432 user@remote.de建立隧道后，本地psql -h localhost -p 9999 -U dbuser database可正常连接，但使用SSHTunnelForwarder编写脚本时出现认证错误：

OperationalError: connection to server at "0.0.0.0", port 33153 failed: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "root", database "iserv", SSL off

报错原因 #

原代码中SSHTunnelForwarder默认转发TCP端口，远程PostgreSQL收到的是来自127.0.0.1的TCP连接，但pg_hba.conf仅允许**Unix域套接字（local规则）**的连接，无对应TCP连接的host规则，因此认证失败。

解决方法 #

配置SSHTunnelForwarder将本地TCP端口映射到远程的Unix域套接字路径，完全模拟手动SSH隧道的转发逻辑，同时确保数据库连接参数与手动操作一致。

完整Python脚本示例 #

import psycopg2
from sshtunnel import SSHTunnelForwarder

# 基础配置参数
ssh_host = "remote.de"
ssh_port = 22
ssh_username = "your_ssh_user"
# 优先使用SSH密钥认证（适配cron无密码运行），避免硬编码密码
ssh_private_key = "/path/to/your/private/key"

# 远程PostgreSQL的Unix套接字路径
remote_pg_socket = "/var/run/postgresql/.s.PGSQL.5432"
# 本地绑定的TCP端口（与手动操作的9999对应）
local_bind_port = 9999

pg_database = "your_database"
pg_user = "your_db_user"

try:
    # 建立SSH隧道：本地TCP端口 -> 远程Unix套接字
    with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_username,
        ssh_pkey=ssh_private_key,
        # 核心配置：指定远程目标为Unix套接字路径
        remote_bind_address=remote_pg_socket,
        local_bind_address=("localhost", local_bind_port)
    ) as tunnel:
        print(f"隧道已建立，本地监听端口：{tunnel.local_bind_port}")
        
        # 连接PostgreSQL，参数与手动操作完全匹配
        conn = psycopg2.connect(
            host="localhost",
            port=tunnel.local_bind_port,
            database=pg_database,
            user=pg_user,
            # 因远程使用ident认证，无需密码，需确保SSH用户与DB用户的映射符合map1规则
            password=None
        )
        
        # 示例：执行查询验证连接
        with conn.cursor() as cur:
            cur.execute("SELECT version();")
            result = cur.fetchone()
            print(f"PostgreSQL服务器版本：{result[0]}")
        
        conn.close()
        print("数据库连接已关闭")

except Exception as e:
    print(f"运行错误：{str(e)}")

关键配置说明 #

1. remote_bind_address：设置为远程PostgreSQL的Unix套接字路径，让SSH隧道将流量转发到Unix套接字而非TCP端口，匹配pg_hba.conf的local规则。
2. local_bind_address：绑定本地TCP端口，和手动操作的-L 9999:...逻辑一致，方便本地通过TCP协议连接。
3. SSH认证：cron任务需无密码运行，因此使用SSH密钥认证（ssh_pkey参数），需提前将本地公钥添加到远程服务器的~/.ssh/authorized_keys中。
4. 数据库连接：host设为localhost，使用本地绑定端口，用户和数据库名必须符合远程ident map=map1的映射规则。

Cron任务配置 #

将脚本保存为pg_ssh_tunnel.py，添加到crontab实现定时运行：

# 每天凌晨2点执行脚本，输出日志到指定文件
0 2 * * * /usr/bin/python3 /path/to/pg_ssh_tunnel.py >> /var/log/pg_tunnel.log 2>&1

注意事项：

• 确保Python环境已安装依赖：pip install psycopg2-binary sshtunnel
• 运行cron的用户需拥有SSH密钥的访问权限
• 日志目录需存在且具备写入权限

内容的提问来源于stack exchange，提问作者Martin L.