Postgresql Remote Connection

Question

I'm stumped. We have a postgres 9.2 database that we have been using for some time. Now we have a need to be able to connect to it from outside our network.

So we setup an External IP, and tested that we can connect to port 5432 from the remote host via telnet. So this proves that the network connection is working, firewalls, etc all good.

When I try to connect using:
psql -h db.host.com -d dbname -p 5432 -U user

I get back psql: server closed the connection unexpectedly.

I've verified that
listen_addresses = '*' is set in postgresql.conf

And in pg_hba.conf we have a line that reads (just for testing)
host all all 0.0.0.0/0 md5

And I've reloaded the database to verify its picked up the changes.
So this should allow connections from any source.

The psql version on DB server is 9.2.14 and on client is 9.2.13.

Any suggestions or ideas?

• Haleemur Ali: can you connect to the postgres server locally over tcp/ip? try replacing 0.0.0.0/0 with the actual ip that you're connecting from
• tman: Yes, I can connect locally and I've tried changing IP as well, we started with the IP the host was connecting with, but that resulted in same behavior.

Answer 1

I would try a few things to get some more information:

• Tail the postgres log on the server to see what is going on there when you connect.

• Run psql --version to make sure it more or less matches the server version. (Ah I see you've already done this, but I'll leave it here for posterity.)

• Run strace psql .... to see how far things get before the failure.

This still sounds like a network issue to me. What is the telnet command you're running? "External IP" sounds a bit like you're on AWS. Setting up remote access to a VPC takes a lot of steps. Do you have any other services open that you can reach from the outside, that are actually working?

You could also do some troubleshooting by shutting down the Postgres server and using nc to listen on 5432. Then connect with telnet and see if you can send data back and forth.

• tman: Paul, thanks for the suggestions. I had done the strace, and did verify the versions. And you were right, was trying to connect from AWS. Turns out, it was an irule I used in the load balancer to only allow access from the EC-2 IP ranges, which AWS publishes. When I disabled the irule, I could immediately connect. So it must be the irule logic, as I verified the public IP my EC2 instance was using and it was indeed listed in the irule via CIDR notification.
• Paul A Jungwirth: Glad you sorted it out!