Enable PostgreSQL remote access- nothing seems to work

0
votes

I have tried everything I can find to enable to remote access to a postgresql 8.1 database on a centos server. I am trying to connect from my windows 10 PC using pgAdmin 3. I keep getting:

The server doesn't accept connections: the connection library reports could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "server ip" and accepting TCP/IP connections on port 5432?

This is what I have done:
pg_hba.conf added - 

host  all all  "my public IP address"/32  trust

postgresql.conf - 

listen_addresses='*'

restarted PostgreSQL

Added the following to iptables 

-A INPUT -p tcp -s 0/0 --sport 1024:65535 -d "my IP"  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT  
-A OUTPUT -p tcp -s "my ip" --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

restarted iptables

I have verified many times that I have the correct IP addresses everywhere.

Can anyone help with what I may be missing.

Update:
I stopped the iptables service and was able to connect so it is something with my iptable rules.

2
postgresqlcentosremote-accessiptables
Did you configure Postgres to listen on port 5432? And why are you using such an outdated Postgres version? – a_horse_with_no_name
Thanks for cleaning up my post. Still trying to work out the editor. Yes it is listening on port 5432. We are converting/updating a very old system. I am trying to connect to the old server so we can convert the data to the structure of the new system. Then it's bye-bye to the outdated PostgreSQL version. – ZeroGravity
Copied this from the top of phpPgAdmin that is running on the server. PostgreSQL 8.1.23 running on localhost:5432 – ZeroGravity

2 Answers

0
votes

If you really want to enable connections without password from other machines, in the line

host  all all  "my public IP address"/32  trust

"my public IP address" should be the IP address of the machine from which you attempt the connection.

Don't forget to run pg_ctl reload to load the changed configuration file.

To be promiscuous and allow passwordless connections from everywhere, use

host  all all  0.0.0.0/0  trust

If you encounter problems, set log_connections to on in postgresql.conf and consult the log file.

0
votes

I found the error with iptables. Using CentOS I needed to add
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5432 –s "my IP" -j ACCEPT
Everything with the PostgreSQL was correct. Just needed to change the iptable rules per above.