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