Install and Secure PostgreSQL on Ubuntu 22.04

PostgreSQL is a powerful open-source relational database management system (RDBMS) designed to handle a broad range of data operations. It supports SQL and is suitable for small projects as well as large-scale applications such as analytics, GIS, healthcare solutions, and dynamic web platforms.

This guide explains how to install PostgreSQL on an Ubuntu 22.04 server and secure it for production use.

Prerequisites

Before starting, ensure the following:

• An Ubuntu 22.04 server is available.
• You can access the server via SSH as a non-root user with sudo privileges.
• Your server is updated with the latest packages.

Install PostgreSQL

PostgreSQL is included in Ubuntu’s default APT repositories. Follow these steps to install PostgreSQL and configure it to start automatically at boot.

Update the Server Package Index

$ sudo apt update

Install the postgresql-common Dependency

$ sudo apt install -y postgresql-common -y

Run the PostgreSQL APT Repository Script

$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

When prompted, press Enter to add the new repository to your server sources.

This script enables the PostgreSQL APT repository (apt.postgresql.org) on your system. The distribution codename used will be noble-pgdg.

Press Enter to continue, or use Ctrl-C to cancel.

Install the PostgreSQL Database Server

$ sudo apt install -y postgresql

Start the PostgreSQL Service

$ sudo systemctl start postgresql

Verify the PostgreSQL Service Status

$ sudo systemctl status postgresql

Example output:

● postgresql.service - PostgreSQL RDBMS
    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: enabled)
    Active: active (exited) since Mon 2024-05-27 16:09:21 UTC; 35s ago
    Process: 5601 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 5601 (code=exited, status=0/SUCCESS)
        CPU: 3ms

Secure the PostgreSQL Database Server

PostgreSQL uses the default postgres user account. Follow these steps to enable password authentication and secure the server by restricting access to authorized users only.

Check the Installed PostgreSQL Version

$ psql --version

Example output:

psql (PostgreSQL) 17.4 (Ubuntu 17.4-1.pgdg22.04+2)

Log In as the postgres User

$ sudo -u postgres psql

Set a Strong Password for the postgres User

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'strong_password';

Create a New User with a Secure Password

postgres=# CREATE USER db_manager ENCRYPTED PASSWORD 'strong_password';

Exit the PostgreSQL Console

postgres=# quit;

Enable Password Authentication

Run the following command to change the default peer value in the scram-sha-256 field in the main PostgreSQL configuration file pg_hba.conf. This will enable password authentication on the server.

$ sudo sed -i '/^local/s/peer/scram-sha-256/' /etc/postgresql/17/main/pg_hba.conf

Replace 17 with your installed PostgreSQL version number if it differs.

Restart PostgreSQL to Apply Changes

$ sudo systemctl restart postgresql

Access the PostgreSQL Database Server

You can access the PostgreSQL console using the built-in psql utility or graphical tools for direct connections. The steps below show how to create a new sample database and assign it to a non-privileged user.

Create a Sample Database

Create a new PostgreSQL database named hospital and grant ownership to the db_manager user.

$ sudo -u postgres createdb hospital -O db_manager

When prompted, provide the password for the Postgres user you configured earlier.

Log In to the Database as db_manager

Use the following command to test access to the hospital database as the db_manager user:

$ sudo -u postgres psql -U db_manager -d hospital

Enter the password for the database user when prompted and press Enter to gain access.

Create a doctors Table

Inside the database, create a table named doctors:

hospital=> CREATE TABLE doctors (
           doctor_id SERIAL PRIMARY KEY,
           first_name VARCHAR(50),
           last_name VARCHAR(50),
           appointment_date DATE
       );

This SQL statement creates a table with the following columns:

• doctor_id as a primary key to uniquely identify each doctor.
• first_name and last_name for storing names.
• appointment_date for storing appointment dates.
• SERIAL automatically generates a unique ID for each record.

Insert Sample Data

Insert a few sample records into the doctors table:

hospital=> INSERT INTO doctors
       ( first_name, last_name, appointment_date)
       VALUES
       ( 'Ben', 'Joe', '2024-11-15'),
       ( 'Carson', 'Smith', '2024-02-28'),
       ( 'Donald', 'James', '2024-04-10');

Query the doctors Table

Retrieve all records from the table:

hospital=> SELECT * FROM doctors;

Example output:

doctor_id | first_name | last_name | appointment_date
-----------+------------+-----------+------------------
       1 | Ben        | Joe       | 2024-11-15
       2 | Carson     | Smith     | 2024-02-28
       3 | Donald     | James     | 2024-04-10
(3 rows)

Exit the PostgreSQL Console

hospital=> \q

Conclusion

In this article, you installed PostgreSQL on Ubuntu 22.04, configured it for security, and used the psql utility to create a database and tables. PostgreSQL is now ready to be integrated with your applications for secure and efficient database management. For further details, refer to the official PostgreSQL documentation.