Postgresql Centos Issue

Question

I am trying to enable Postgresql 9.3 to accept remote connections on CentOS6. I opened the port in iptables, set the port to 5432 in the pgconf file, set listen_addresses to '*' (accept all connections), and allow addresses in the pg_hba with host all all 0.0.0.0/0 trust. The postmaster is running on 5432. However, I still receive the following error. How do I get a valid connection?

The error:

could not connect to server: Connection refused
    Is the server running on host "50.63.141.236" and accepting
    TCP/IP connections on port 5432?

Relevant part of pg_hba.conf file:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
#opens postgres to the internet
host    all         all         0.0.0.0/0             trust

Relevant output from iptables -L:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:postgres
ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:postgres

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:postgres

Finally, proof that postmaster is running.

EN      1433/sshd
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LIST

Are you sure 0.0.0.0 is what you want -- as I understand it, this is an IP header for unknown. In our pg_hba.conf file, (also pg9.3 and centos), we have our actual external IP address, followed by 32, eg, 123.123.123.123/32 — John Powell
0.0.0.0/0 matches all IP addresses, because the subnet mask prefix /0 doesn't mask anything. So that's OK, so long as you're not Internet-facing. — Craig Ringer
I'm just trying to get it to work at the moment so I am not worried about IP addresses. — Andrew Scott Evans

Craig Ringer Craig Ringer · Accepted Answer · 2014-04-21T11:42:41

You haven't restarted PostgreSQL after changing listen_addresses. It's still listening on 127.0.0.1, i.e. loopback only. Or you edited the config file for a different PostgreSQL install.

If you connect to host 127.0.0.1 port 5432 it'll work. Or make sure you edited the right config and restart (not just reload) PostgreSQL.

Postgresql Centos Issue

1 Answers