Postgresql – postgres server works on LAN but not WAN (connection refused)

local-area-networkpostgresqlpsqlwide-area-network

I'm trying to set up a postgres server, but I cannot access it through the Internet. It works fine on my LAN. Server is just a desktop computer using Ubuntu 12.04 desktop.

disclaimer: I am not planning to use this configuration permanently. Ultimately I will connect through an SSH tunnel and some of what I'm doing could be unsecure, but right now I just want to figure out why it's not working.

In my postgresql.conf file, I set listen_addresses = '*'. To test that it wasn't something with my pg_hba.conf, I commented everything and just used

host all all all trust

There doesn't seem to be anything happening with the firewall – to make sure I installed gufw and allowed all incoming and outgoing traffic on both the server and client.

When I try to connect through my domain name, which points to the server's outside IP address, I get this message:

psql: could not connect to server: Connection refused
Is the server running on host "<hostname>" (<IP address>) and accepting
TCP/IP connections on port 5432?

The result of netstat -an | grep 5432 seems to indicate that postgres is indeed listening for connections on all addresses:

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     9771     /var/run/postgresql/.s.PGSQL.5432

Best Answer

a line in pg_hba.conf, for e. g.:

host all all 0.0.0.0/0 md5