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?

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.