PostgreSQL Remote Connection debugging

Question

I'm having issues connecting to my PostgreSQL database from an online source.

PostgreSQL seems to be setup up fine it is running on it's default port 5432 and the postgresql.conf has the following line

listen_addresses = '*'

And the pg_hba.conf has the following

host    dbname    usname    all    md5

I can connect to the PostgreSQL database from a different machine using the following credentials in pgadmin

Name              local
Host              192.xx.xx.xx
Port              5432
Maintenance DB    dbname
Username          usname
Password          psword

Using these I can connect perfectly it seems to work fine.

Then I forwarded the port 192.xx.xx.xx:5432 on the ADSL router and checked it using the public IP address 197.xx.xx.xx (visible to the internet IP) http://www.canyouseeme.org/ Where I can see the port was forwarded ok.

I've disabled SELinux and disabled the firewall temporarily as well.

However when I try to connect using

Name              online
Host              197.xx.xx.xx
Port              5432
Maintenance DB    dbname
Username          usname
Password          psword

I get the server doesn't listen error message

could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "197.xx.xx.xx" and accepting TCP/IP connections on port 5432?

Why is this? Do I need to add an extra step to making it accept connections through port forwarding?

Answer 1

all isn't valid in the CIDR field of a host line in pg_hba.conf.

From the comments in the default pg_hba.conf file itself:

# host DATABASE USER CIDR-ADDRESS METHOD [OPTION]

and

CIDR-ADDRESS specifies the set of hosts the record matches. It is made up of an IP address and a CIDR mask that is an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies the number of significant bits in the mask. Alternatively, you can write an IP address and netmask in separate columns to specify the set of hosts.

Try this instead:

host    dbname    usname      197.xx.xx.xx/32  md5

that will allow connections to dbname from usname only from remote host 197.xx.xx.xx

If you want to allow the entire 197.xx.xx /24 subnet, use:

host    dbname    usname      197.xx.xx.0/24  md5