Configuring remote access to PostgreSQL in CentOS

In this process, we will learn how to configure remote access to a Postgres server which is disabled by default. Postgres employs a method called host-based authentication and it is the purpose of this process to introduce you to its concepts in order to provide the access rights you need to run a safe and secure database server.

To Start With: What Do You Need?

To complete this process, you will require a working installation of the CentOS 7 operating system with root privileges and a text editor of your choice. It is expected that PostgreSQL is already installed and running.

The Process

In the previous process, we have already modified the host-based authentication configuration pg_hba.conf file using sed to manage our Postgres’s client authentication from peer to md5. Here we will make changes to it to manage remote access to our Postgres server.

1. To begin, log in as root and first open the firewall to allow any incoming PostgreSQL connections to the server:
   firewall-cmd --permanent --add-service=postgresql;firewall-cmd --reload
2. Now open the host-based authentication configuration file in your favorite text editor by typing:
   vi /var/lib/pgsql/data/pg_hba.conf
3. Scroll down to the end of the file and append the following line, to make these lines read as follows (substitute the XXX.XXX.XXX.XXX/XX value with a network address you want to grant access to. For example, if the IP address of your server was 192.168.1.12 then the network address would be 192.168.1.0/24):
   host        all          all           XXX.XXX.XXX.XXX/XX       md5
4. When you have finished, simply save and close the file in the usual way before opening the main Postgres configuration file by typing:
   vi /var/lib/pgsql/data/postgresql.conf
5. Add the following lines to the end of the file:
   listen_addresses = '*'
port = 5432
6. When you have finished, save the file in the usual way before restarting the database server by typing the following command:
   systemctl restart postgresql
7. On any other computer which is in the same network (defined by the XXX.XXX.XXX.XXX/XX value set previously), you can now test if the remote connection to your Postgres server is working using the psql shell (if your client computer is CentOS, you need to install it using yum install postgresql) by logging in on the server remotely and printing out some test data. In our example, the Postgres server is running with the IP address 192.168.1.12.
   psql -h 192.168.1.12 -U <username> -d <database-name>

How Does It Work?

PostgreSQL is a safe and secure database system but where we access it (either remotely or locally) can often become a cause of confusion. It was the purpose of this process to lift the lid on host-based authentication and provide an easy-to-use solution that will enable you to get your system up-and-running.

So what did we learn from this experience?

We began the process by opening the Postgres service’s standard ports in firewalld in order to make a connection from any remote computer possible in the first place. Then we opened Postgres’s host-based authentication configuration file called pg_hba.conf with our favorite text editor. Remember, we already changed from peer to md5 authentication for all local connections to provide user-based authentication in a former process. The inserted host record line specifies a connection type, database name, a user name, a client IP address range, and the authentication method. Many of the previous commands may already be understood but it is important to realize that there are several different methods of authentication:

• trust: Allows the connection unconditionally and enables anyone to connect with the database server without the need for a password.
• reject: Allows the database server to reject a connection unconditionally, a feature that remains useful when filtering certain IP addresses or certain hosts from a group.
• md5: Implies that the client needs to supply an MD5-encrypted password for authentication.
• peer and ident: Access is granted if the client’s logged in Linux user name from the operating system can be found as a database user in the system. ident is used for remote connections and peer for local connections.

Having completed this task, we then saved and closed the file before opening the main PostgreSQL configuration file located at /var/lib/pgsql/data/postgresql.conf. As you may or may not be aware, remote connections will not be possible unless the server is started with an appropriate value for listen_addresses, and where the default setting placed this on a local loopback address it was necessary to allow the database server to listen to all network interfaces (signified by the use of a star symbol or *) for incoming Postgres connections on the 5432 port. When finished, we simply saved the file and restarted the database server.

There is always much more to learn, but as a result of completing this process, you not only have a better understanding of host-based authentication but you have the ability to access your PostgreSQL database server both locally and remotely.