Cannot connect to PostgreSQL from client - Error timed out

Question

After many days I am trying to connect to my PostgreSQL instance, I decided time has come to ask for help.

I am trying to connect to my PostgreSQL db from a Windows machine.

I am trying pgAdmin 4 and dBeaver but both fail to connect. Below is the screenshot of the error I receive when connecting using dBeaver.

The connection I am creating is like so:

My users are (\du):

                                 List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 umberto   | Superuser, Create role, Create DB                          | {}

My databases (\l):

                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 umberto   | umberto  | UTF8     | C.UTF-8 | C.UTF-8 |
 wondermap | postgres | UTF8     | C.UTF-8 | C.UTF-8 |

Don't know exactly where to search for logs to dig into this problems on the server machine. The only thing I could find was the folder /var/log/postgresql where I see only two non gzipped files but the messages are referring to days previous to my attempts to connect.

Finally, my pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

What could be the problem?

Solution

I wouldn't generally look at postgres logs for troubleshooting a connection timeout error, because if postgres is rejecting the connections, they'll get rejected right away rather than giving you a timeout, so if you're getting a timeout it typically means it never reached postgres, so there will be nothing relevant in the logs.

In my experience, a connection timeout error is typically due to a windows/networking issue, for example a firewall on (or in front of) the server doesn't allow access on port 5432, or nothing is actually listening on port 5432 (could be that postgres isn't actually running, or it's configured to listen on a different port, etc).

My favourite tool for troubleshooting these sorts of connectivity issues on Windows is portqry. Usage is portqry -n [hostname] -e [port number]. It will try to connect to [hostname] on port [port number] and give you the results:

• Listening: portqry was able to connect to the host on the specified port, and an application was listening on that port. This is what you want.
• Not listening: portqry was able to reach the host on the specified port, but nothing was listening on that port. In the case of postgres, this may be because the service isn't running, or is listening on a different port.
• Filtered: portqry was unable to reach the host on the specified port. This means it was actually blocked from connecting, and this is generally caused by a firewall on the host or in between the client and host, which is preventing access to the host on that port.