0

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?

0

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
  • I tried changing the pg_hba.conf to "host all all 0.0.0.0/0 md5" but still no luck – TheLovelySausage Nov 4 '15 at 6:38
  • 1. don't do that. it'll be wide open to the internet when this problem is solved. limit it to just the host(s) and/or network(s) that need access. 2. do you have any firewall rules blocking access to port 5432? – cas Nov 4 '15 at 6:58
  • I've changed the pg_hba.conf to your recommendations "host dbname usname 197.xx.xx.0/24" and "host dbname usname 192.xx.xx.0/24" – TheLovelySausage Nov 4 '15 at 7:08
  • I tried to use "telnet 197.xx.xx.xx 5432" from another machine and I got "telnet: connect to address 197.89.125.31: Connection refused" – TheLovelySausage Nov 4 '15 at 7:19
  • I don't think it's to do with the firewall because it is disabled at the moment for testing and so is SELinux – TheLovelySausage Nov 4 '15 at 7:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.