Debian/Ubuntu连接Postgres提示Connection timed out、Windows正常如何解决

场景说明 #

在DigitalOcean托管的Debian服务器上通过Docker运行PostgreSQL 13.4，为测试非本地客户端连通性配置了宽松访问规则，仍出现部分客户端连接超时问题。

服务端相关配置 #

pg_hba.conf配置 #

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

postgresql.conf配置 #

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'

UFW防火墙规则 #

Status: active

To                         Action      From
--                         ------      ----
787/tcp                    ALLOW       Anywhere
Anywhere                   ALLOW       93.551.148.352
80/tcp                     ALLOW       Anywhere
443/tcp                    ALLOW       Anywhere
5432                       ALLOW       Anywhere
787/tcp (v6)               ALLOW       Anywhere (v6)
80/tcp (v6)                ALLOW       Anywhere (v6)
443/tcp (v6)               ALLOW       Anywhere (v6)
5432 (v6)                  ALLOW       Anywhere (v6)

客户端测试结果 #

所有客户端均通过Postgres官方Docker容器执行psql命令测试，无需本地安装Postgres：

• 家庭网络下Windows 10设备：连接正常，执行命令如下

  root@7eb2296a9cf6:/# psql -h 111.222.143.193 -p 5432 -U postgres
  Password for user postgres:
  

• 同家庭网络下Debian 10设备：连接失败，报Connection timed out错误，执行命令及报错如下

  root@38161cc233c1:/# psql -h 111.222.143.193 -p 5432 -U postgres
  psql: error: could not connect to server: Connection timed out
          Is the server running on host "111.222.143.193" and accepting
          TCP/IP connections on port 5432?
  

• Linode网络下Ubuntu 20.04设备：连接失败，同报连接超时错误，执行命令及报错如下

  root@3706fd7cbdd4:/# psql -h 111.222.143.193 -p 5432 -U postgres
  psql: error: could not connect to server: Connection timed out
          Is the server running on host "111.222.143.193" and accepting
          TCP/IP connections on port 5432?
  

根因与解决方案 #

该问题由Docker修改iptables的逻辑导致：即使此前关闭了Docker的iptables规则配置权限，其历史修改的规则仍会留存生效。
解决方法：修改/etc/docker/daemon.json文件，添加配置{"iptables": true}，重启Docker服务即可生效。

验证说明：全新安装Debian 11后，未安装Docker时Postgres服务可正常被外部访问；安装Docker后从Postgres容器内无法访问服务端，进一步确认问题与Docker相关的防火墙配置有关。

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