[SOLVED] Postgres "Connection timed out" when connecting from debian and ubuntu clients, but fine from Windows

Issue

Update:

This seems to be related to just how Docker changes the iptables and leaves its settings even after disabling its iptables rule setting capabilities. Issue can be resolved by modifying /etc/docker/daemon.json and setting { "iptables": true }

I just did a fresh install of Debian 11. Installed postgres without docker, and was able to connect to my server. I just installed docker and tried to connect from a postgres container and was not able to reach server. This issue seems to be related to firewalling around docker.

I am running postgres 13.4 on a debian server through docker, hosted on DigitalOcean. I've setup a very open config just to see if I can connect to my server from various clients that are not local. Here is how I've configured my pg_hba.conf file:

# 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 = '*'

My way to connect from client side is to use a postgres docker container so that I can use the psql command without installing postgres on that client. I've tried 3 different client machines.

Windows 10 from home network works fine:

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

Debian 10 from home network fails:

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?

Ubuntu 20.04 from Linode network fails:

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?

Here is the UFW status (I've changed the IP for privacy)

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)

Solution

This seems to be related to how Docker changes the iptables and leaves its settings even after disabling its iptables rule setting capabilities. Issue can be resolved by setting /etc/docker/daemon.json and setting { "iptables": true }

Another approach is to work with iptables directly.

Answered By - Dan

Answer Checked By - Cary Denson (WPSolving Admin)