In this recipe, 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 recipe to introduce you to its concepts in order to provide the access rights you need to run a safe and secure database server.
To complete this recipe, 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.
In the previous recipe, 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.
firewall-cmd --permanent --add-service=postgresql;firewall-cmd --reload
vi /var/lib/pgsql/data/pg_hba.conf
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
vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*' port = 5432
systemctl restart postgresql
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>
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 recipe 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 recipe 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 recipe. 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:
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 recipe, 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.