远程PostgreSQL数据库SSL SYSCALL连接错误原因及解决方法

问题场景 #

在本地Windows PowerShell执行以下命令连接远程PostgreSQL数据库：

psql -h 112.176.20.102 -p 5432 -d bookstore -U postgres -W

返回错误信息：

psql: error: connection to server at "112.176.20.102", port 5432 failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
SSL SYSCALL error: Connection reset by peer (0x00002746/10054)
connection to server at "112.176.20.102", port 5432 failed: expected authentication request from server, but received S

当前配置文件信息 #

1. pg_hba.conf 配置 #

# PostgreSQL Client Authentication Configuration File
# ===================================================
...
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host all all 0.0.0.0/0 md5

2. postgresql.conf 配置 #

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
...
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
...

问题成因 #

1. 网络拦截：Connection reset by peer提示连接被中途断开，大概率是远程服务器防火墙未开放5432端口，或是本地网络到目标服务器的5432端口被网关/运营商拦截。
2. SSL配置不兼容：PostgreSQL默认可能强制要求SSL连接，本地psql客户端未匹配SSL参数，导致握手失败后连接被重置。
3. 配置未生效：修改listen_addresses和pg_hba.conf后未重启PostgreSQL服务，服务器仍仅监听本地连接。

解决步骤 #

1. 开放服务器端口 #

• Linux服务器临时开放端口（重启后失效）：

  iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
  

• 永久开放端口（以firewalld为例）：

  firewall-cmd --add-port=5432/tcp --permanent
  firewall-cmd --reload
  

• 云服务器需额外在控制台安全组添加TCP 5432端口的入站规则。

2. 验证端口监听状态 #

在远程服务器执行以下命令，确认PostgreSQL是否监听所有IP：

netstat -tulpn | grep 5432

若输出显示0.0.0.0:5432或:::5432则正常；若仅显示127.0.0.1:5432，说明配置未生效，需重启服务。

3. 重启PostgreSQL服务 #

根据服务器系统执行对应命令：

# Debian/Ubuntu
sudo systemctl restart postgresql
# CentOS/RHEL（替换为实际版本号）
sudo systemctl restart postgresql-15

4. 跳过SSL测试连接 #

添加sslmode=disable参数尝试连接，验证是否为SSL问题：

psql -h 112.176.20.102 -p 5432 -d bookstore -U postgres -W -sslmode=disable

若连接成功，可后续在postgresql.conf中调整ssl参数，或配置客户端SSL证书。

5. 确认权限规则有效性 #

检查pg_hba.conf中host all all 0.0.0.0/0 md5规则的位置，确保它在所有更严格的IP规则之后（规则匹配顺序优先），同时确认postgres用户密码正确。

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