Search code examples
postgresqlcentos

Unable to remotely connect to port 5432 on centOS 7


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      -

Solution

  • 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.