Tuesday, September 12, 2017

PostgreSQL - Listen on All IPs

PostgreSQL version: 9.6
OS: Red Hat Enterprise Linux Server release 7.2 (Maipo)

By default, PostgreSQL is configured to listen to "localhost" on port 5432.
# netstat -atnp | grep 131069
tcp        0      0  *               LISTEN      131069/postmaster

If you try to connect to the database from its public IP:
# psql -h -U username

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

In order to open up the connection, you need to update "postgresql.conf" and "pg_hba.conf" files. In my case, I am running "postgresql-9.6", so two files are located at:
    - To have postgresql listening on
    - To allow incoming connections

Configuring postgresql.conf
Open postgresql.conf file and update line
listen_addresses = 'localhost'                  # what IP address(es) to listen on
listen_addresses = '*'                  # what IP address(es) to listen on

Configuring pg_hba.conf
Open pg_hba.conf file and update line
host    all             all               ident
host    all             all                  trust

Now restart the service:
#  systemctl restart postgresql-9.6.service

Try command "psql -h -U username" again, you should be able to login.
# psql -h -U username
psql (9.2.18, server 9.6.4)
WARNING: psql version 9.2, server version 9.6.
         Some psql features might not work.
Type "help" for help.


No comments: