0

Pretty well-documented topic, but I haven't found my solution yet hence here I go. I am failing to connect to my Postgres database resulting in the following two errors. (the errors also occur with normal PG client or any other way to access the DB).

Error variant 1:

psycopg2.OperationalError: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Variant 2:

psycopg2.OperationalError: connection to server at "<host_ip>", port 5432 failed: Connection timed out (0x0000274C/10060) Is the server running on that host and accepting TCP/IP connections?

I am trying to connect to my dockerized Postgres database which is hosted on a VM at IP A.B.Q.X:5432 from a machine with IP A.B.Y.Z. Connecting to the database from any machine in the IP range A.B.Q.? is not a problem. I have made sure listen_addresses = '*' is set in the postgres.config. Furthermore, my pg_hba config looks as such:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local all all           trust
# trust local connections
host  all  all  0.0.0.0/0  trust
host  all  all  ::0/0       trust

# IPv4 external connections
host  all all 0.0.0.0/0     trust

This should mean all IPs are allowed if I am correct.

Furthermore, the firewall is disabled on the machine. sudo nmap -v -p 5432 [IP Host] does indicate the state is filtered though (some first tries it does say open, but then goes to filtered for any following attempts), for which I do not know where this should come from. The VM is pingable, and other ports on the VM I can easily connect to, which has me thinking it is still a Postgres issue and not a networking issue.

I also checked the port and made sure the rules are live in the database.

To make this even crazier; I also have another Postgres instance on A.B.E.F, for which I experience similar problems. However, here about every 15-20 minutes one request does manage to make a connection to the database and the query results are displayed instantly.

How can I fix this? Is there any additional firewall I am unaware of?

Kjell van Straaten
  • 43
  • 6
  • 1
    It is probably a firewall/gateway/router located between A.B.Q and A.B.Y. Usually firewalls block things by blackholing the connection (causing connection timeout errors), not by "Connection refused", so it is either a weird firewall, or something which isn't really a firewall but still blocks traffic. Your pg_hba isn't causing this, as the connection is not getting far enough along to be consulting pg_hba yet. – jjanes Apr 21 '23 at 22:48
  • @jjanes thanks for your reply, I updated my initial problem description to make it clear as I actually get a combination of connection refused and the connection closed unexpectedly. Since you mention it is something between A.B.Q and A.B.Y; how come there is no difficulty for me to connect to other ports on the same VM? for example, on the same host, we have a PostgrestAPI and UI which I can access from A.B.Y. Listening on port 5432 from the host also shows that there are connection requests being made. – Kjell van Straaten Apr 22 '23 at 06:30
  • "closed unexpectedly" upon initial connection usually indicates that a port forwarder is in use. The forwarder accepts the request, then if it realizes it can't do anything with it it just closes it (because it already accepted it it is too late to refuse the connection). That different ports behave differently is not surprising, letting that happen is one of the main things forwarders and firewall are for. Anyway, this is primarily a networking issue, not programming or DB. – jjanes Apr 22 '23 at 14:07
  • I second that this is a network issue that comes down to *...which is hosted on a VM *. Hosted where? What VM? – Adrian Klaver Apr 22 '23 at 15:28
  • when running nmap I indeed get 5432/tcp open, and then the second run get 5432/tcp filtered. I guess that indicates its a networking problem indeed. I'll ask network admins. – Kjell van Straaten Apr 22 '23 at 15:38

0 Answers0