How to Install and Secure PostgreSQL Server on RockyLinux 8

PostgreSQL is a popular open-source relational database management system. Known for its reliability, it is gaining popularity due to its robustness, flexibility, and performance. PostgreSQL is used for managing databases and numerous web and analytical applications. At the time of writing this article, PostgreSQL 13 is the latest version. This version comes with significant improvements to the indexing and lookup system that benefit large databases.

In this post, we will show you how to install and secure PostgreSQL on Rocky Linux 8.

Step 1 – Add PostgreSQL 13 Repository

By default, the latest version of PostgreSQL is not included in the Rocky Linux default repository. You can check the default available version in the AppStream repository using the following command:

dnf module list postgresql

You should see that only PostgreSQL version 10 is available:

Rocky Linux 8 - AppStream
Name                         Stream                   Profiles                             Summary                                             
postgresql                   9.6                      client, server [d]                   PostgreSQL server and client module                 
postgresql                   10 [d]                   client, server [d]                   PostgreSQL server and client module                 
postgresql                   12                       client, server [d]                   PostgreSQL server and client module                 
postgresql                   13                       client, server [d]                   PostgreSQL server and client module                 

In order to install the latest PostgreSQL version, you will need to install the PostgreSQL repo to your system.

You can install it using the following command:

dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Once the repo is created, you can proceed to the next step.

Step 2 – Install PostgreSQL 13 on Rocky Linux 8

Now, update your repository using the following command:

dnf update -y

Next, disable the default PostgreSQL repo using the following command:

dnf -qy module disable postgresql

Next, install the latest version of PostgreSQL by running the following command:

dnf install postgresql13 postgresql13-server

Once PostgreSQL 13 is installed, you will get the following output:

Last metadata expiration check: 0:00:08 ago on Fri 22 Oct 2021 08:38:58 AM UTC.
Dependencies resolved.
===============================================================================================================================================
 Package                                  Architecture                Version                                Repository                   Size
===============================================================================================================================================
Installing:
 postgresql13                             x86_64                      13.4-1PGDG.rhel8                       pgdg13                      1.5 M
 postgresql13-server                      x86_64                      13.4-1PGDG.rhel8                       pgdg13                      5.5 M
Installing dependencies:
 postgresql13-libs                        x86_64                      13.4-1PGDG.rhel8                       pgdg13                      414 k

Transaction Summary
===============================================================================================================================================
Install  3 Packages

Total download size: 7.4 M
Installed size: 31 M
Is this ok [y/N]: y

Next, initialize the PostgreSQL database with the following command:

/usr/pgsql-13/bin/postgresql-13-setup initdb

Sample output:

Initializing database ... OK

Next, start the PostgreSQL service and enable it to start at system reboot with the following command:

systemctl start postgresql-13
systemctl enable postgresql-13

You can check the status of PostgreSQL with the following command:

systemctl status postgresql-13

You should get the following output:

● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2021-10-22 08:39:47 UTC; 7s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 36412 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 36417 (postmaster)
    Tasks: 8 (limit: 11411)
   Memory: 16.8M
   CGroup: /system.slice/postgresql-13.service
           ├─36417 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─36419 postgres: logger 
           ├─36421 postgres: checkpointer 
           ├─36422 postgres: background writer 
           ├─36423 postgres: walwriter 
           ├─36424 postgres: autovacuum launcher 
           ├─36425 postgres: stats collector 
           └─36426 postgres: logical replication launcher 

By default, PostgreSQL listens on port 5432. You can check it with the following command:

ss -antpl | grep 5432

You will get the following output:

LISTEN 0      128        127.0.0.1:5432       0.0.0.0:*    users:(("postmaster",pid=36417,fd=7))
LISTEN 0      128            [::1]:5432          [::]:*    users:(("postmaster",pid=36417,fd=6))

Step 3 – Set a Password for Postgres User

By default, the password of the Postgres user is not set, so it is recommended to set a password for security reasons.

To set a password, log in to PostgreSQL with the following command:

su - postgres

Next, set a secure password with the following command:

psql -c "alter user postgres with password 'securepassword'"

Next, exit from the PostgreSQL shell using the following command:

exit

Step 4 – Change PostgreSQL Authentication Method

By default, PostgreSQL is configured to use the peer method to connect to PostgreSQL locally. but this method is not recommended for the production environment. It is recommended to change the authentication method from peer to scram-sha-256.

You can change it by editing the PostgreSQL main configuration file:

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

Find the following line:

local   all             all                                     peer

And, replace it with the following line:

local   all             all                                     scram-sha-256

Save and close the file, then restart the PostgreSQL service to apply the changes.

systemctl restart postgresql-13

Step 5 – Create a Database and User in PostgreSQL

First, log in to the PostgreSQL shell with the following command:

sudo -u postgres psql

You will get the following output:

could not change directory to "/root": Permission denied
psql (13.4)
Type "help" for help.

postgres=# 

Next, create a new PostgreSQL user named user1 using the following command:

CREATE USER user1 WITH CREATEDB CREATEROLE PASSWORD 'passoword';

To verify the PostgreSQL users, run:

\du

You will get the following output:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     | Create role, Create DB                                     | {}

To create a new PostgreSQL database named user1db, run:

CREATE DATABASE user1db OWNER user1;

To verify the PostgreSQL databases, run:

\l

You will get the following output:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 user1db   | user1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

Conclusion

Congratulations! You have successfully installed and secured PostgreSQL on Rocky Linux 8. For security reasons, it is always recommended to install the latest version of PostgreSQL in the production environment. Give it a try on VPS hosting from Atlantic.Net!