How to install PostgreSQL server on RHEL 8 / CentOS 8

PostgreSQL is an free-opensource object-relational database management system. The objective of this tutorial is to perform an installation and basic configuration of PostgreSQL server on RHEL 8 / CentOS 8 Linux server.

In this tutorial you will learn:

• How to install PostgreSQL database server on RHEL 8 / CentOS 8
• How to start and enable PostgreSQL database server
• How to access PostgreSQL database from localhost and remote location
• How to set password for the default postgres user
• How to enable PostgreSQL to listen on all networks
• How to secure PostgreSQL remote connection with MD5 password authentication
• How to open PostgreSQL firewall port
• How to establish remote connection to PostgreSQL server using psql client

Initializing and accessing PostgreSQL database on Red Hat Enterprise Linux 8

Software Requirements and Conventions Used

Local PostgreSQL Installation and database access step by step instructions

1. Install PostreSQL server. Execute the below dnf command to perform a PostreSQL server package installation:

   # dnf install postgresql-server
   

2. Initialize PostgreSQL database:

   # postgresql-setup --initdb --unit postgresql
    * Initializing database in '/var/lib/pgsql/data'
    * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
   

3. Start PostgreSQL and optionally enable it to start after reboot.

   # systemctl start postgresql
   # systemctl enable postgresql
   

   At this point the PostreSQL server should be up and running and listening on localhost port 5432. Use ss command to confirm that this is the case:

   $ ss -nlt
   State  Recv-Q  Send-Q     Local Address:Port     Peer Address:Port  
   LISTEN 0       128              0.0.0.0:111           0.0.0.0:*     
   LISTEN 0       32         192.168.122.1:53            0.0.0.0:*     
   LISTEN 0       128              0.0.0.0:22            0.0.0.0:*     
   LISTEN 0       128            127.0.0.1:5432          0.0.0.0:*     
   LISTEN 0       128                 [::]:111              [::]:*     
   LISTEN 0       128                 [::]:22               [::]:*     
   LISTEN 0       128                [::1]:5432             [::]:*
   

---

---

4. Access PostreSQL database.When you install PostgreSQL database on yourRHEL 8 / CentOS 8 system the installer will also automatically create a new default user postgres.

   The default password for postgres user is not set, hence it is empty. To access the PostgreSQL database first execute su command as root user to switch to postres user. Then, type psql to login to the database.

   NOTE
   Any attempt to access PostgreSQL database as a root user will result in psql: FATAL: role "root" does not exist error message.

   Example:

   # su - postgres
   $ psql
   psql (10.5)
   Type "help" for help.
   
   postgres=#
   

   NOTE
   To exit from PostreSQL database shell type \q or hit CTRL+d key combination.

PostgreSQL database remote access and secure connection

1. Set password for the postgres user.In order to access the PostreSQL server remotely we will first set password for the postres user:

   # su - postgres
   $ psql
   psql (10.5)
   Type "help" for help.
   
   postgres=# \password postgres
   Enter new password: 
   Enter it again: 
   postgres=# exit
   postgres-# \q
   

---

---

2. Enable PostgreSQL server to listen on all available networks.Edit the main configuration file /var/lib/pgsql/data/postgresql.conf:

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

   Once ready add the following line somewhere to the CONNECTIONS AND AUTHENTICATION section:

   listen_addresses = '*'
   

   WARNING
   The above configuration will enable PostreSQL to listen on all available networks. It is recommended to set more strict rules to in order to allow access to PostgreSQL only from selected network(s).

   Use ss command to confirm that PostgreSQL is listening on 0.0.0.0 network:

   $ ss -nlt
   State  Recv-Q  Send-Q     Local Address:Port     Peer Address:Port  
   LISTEN 0       128              0.0.0.0:111           0.0.0.0:*     
   LISTEN 0       32         192.168.122.1:53            0.0.0.0:*     
   LISTEN 0       128              0.0.0.0:22            0.0.0.0:*     
   LISTEN 0       128            0.0.0.0:5432          0.0.0.0:*     
   LISTEN 0       128                 [::]:111              [::]:*     
   LISTEN 0       128                 [::]:22               [::]:*     
   LISTEN 0       128                [::]:5432             [::]:*
   

3. Enable MD5-encrypted password authentication:

   # echo "host all all 0.0.0.0/0  md5" >> /var/lib/pgsql/data/pg_hba.conf
   

4. Apply PostgreSQL configuration changes:

   # systemctl restart postgresql
   

5. Open firewall port 5432 for a remote PostgreSQL incoming traffic:

   # firewall-cmd --zone=public --permanent --add-service=postgresql
   # firewall-cmd --reload
   

6. Connect to the PostgreSQL database server from a remote location.First install the psql PostgreSQL client tool on your remote host:

   RHEL/CENTOS
   # dnf install postgresql
   UBUNTU/DEBIAN
   # apt install postgresql-client
   

   Create a remote connection to host eg. 192.168.1.151 as a postgres user and user password as defined in the aboveStep 1:

   $ psql -h 192.168.1.151 -U postgres
   Password for user postgres: 
   psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 10.5)
   Type "help" for help.
   
   postgres=#