Cannot connect to PostgreSQL from client - Error timed out
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?
Answer
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 connect to 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 connect to 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.