How to enable remote access on PostgreSQL Server

PostgreSQL is a secure database server. It has a strict security feature by default. On a standard installation, it only allow local connection to the server. But, in most cases database administrator needs access from remote computer for maintenance and other reasons. This tutorial is going to show you how to enable remote access on PostgreSQL Server. There are several steps we need to take to enable remote access to PostgreSQL. Before we continue, you may want to see how to install PostgreSQL Server on CentOS 7.

If the remote access is not enabled on PostgreSQL, you will get the following error when trying to connect it from pgAdmin for example. 

Steps to enable remote access on PostgreSQL Server

Connect to the server via SSH as root or user with sudo privileges. Now we need to edit few things. 

A. Modify pg_hba.conf file to add client authentication record

pg_hba.conf is located in /var/lib/pgsql/data directory. Edit the file using nano or any text editor you like

nano /var/lib/pgsql/data/pg_hba.conf

Add the following line to the end of the file. This will enable remote access from all client inside the network 192.168.1.0. Change it with your own network scenario. 

host    all     all     192.168.1.0/24  trust

B. Change the listen address

Now we need to edit the postgresql.conf inside the /var/lib/pgsql/data directory. 

nano /var/lib/pgsql/data/postgresql.conf

Find the following line:

#listen_addresses = 'localhost'

Change the line into

listen_addresses = '*'

This will make PostgreSQL listen to any addresses. 

Close and save the file. Restart postgresql service

systemctl restart postgresql

C. Allow access to port 5432

Since CentOS 7 uses firewalld, we need to allow port 5432 on the firewall. 

firewall-cmd --zone=public --add-port=5432/tcp

Now, try to open or connect to the PostgreSQL Server. I am using PgAdmin on Windows machine. Now, I can connect to my PostgreSQL Server without problem. 

Thanks for reading this how to enable remote access on PostgreSQL Server. See you on the next tutorial. Cheers