I am trying to connect to Postgresql (v9.6) remotely and it seems that I can't. My Postgres resides on a CentOS 7 server. From that server I serve various web sites (at ports 80xx) and I am also using a service at port 5050 successfuly. It seems that only port 5432 has the problem. For instance:
$ lsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description: CentOS Linux release 7.4.1708 (Core)
Release: 7.4.1708
Codename: Core
$ sudo firewall-cmd --zone=public --add-port=5432/tcp --permanent
Warning: ALREADY_ENABLED: 5432:tcp
success
$ sudo firewall-cmd --reload
success
$ sudo iptables -S | grep "5432"
-A IN_public_allow -p tcp -m tcp --dport 5432 -m conntrack --ctstate NEW -j ACCEPT
$ netstat -nlp | grep 5432
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN -
tcp6 0 0 ::1:5432 :::* LISTEN -
unix 2 [ ACC ] STREAM LISTENING 516044892 - /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 516044894 - /tmp/.s.PGSQL.5432
This is what I do:
> psql -h xx.xx.xx.xx -p 5432 -U postgres
psql: could not connect to server: Connection refused
Is the server running on host "xx.xx.xx.xx" and accepting
TCP/IP connections on port 5432?
This is my pg_hba.conf
host all all 127.0.0.1/32 ident
host all all ::1/128 ident
host all all 172.17.0.0/24 trust
host all all 0.0.0.0/0 md5
and I also modified postgresql.conf
to listen to '*'
at port 5432
and restarted the server.
What am I missing?
PS: I can connect locally, because some of my web applications are using this server to store data.
EDIT: Is this OK?
$ netstat -tulpn | awk 'NR==2 || /:5432/'
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN -
tcp6 0 0 ::1:5432 :::* LISTEN -
It seems that I got the solution after all.
In CentOS 7 the two postgresql configuration files can be found in two places.
the ones I used and they are the wrong ones:
vim /var/lib/pgsql/data/pg_hba.conf
vim /var/lib/pgsql/data/postgresql.conf
The correct ones can be found in this directory
vim /var/lib/pgsql/9.6/data/postgresql.conf
vim /var/lib/pgsql/9.6/data/pg_hba.conf
I don't know why this happens, but now it is working. I am writing the answer in case someone needs it.