PostgreSQL Installation and Security on Ubuntu 20.04

PostgreSQL is a sophisticated, open-source Relational Database Management System (RDBMS) capable of handling diverse data requirements. Using Structured Query Language (SQL), it efficiently manages data for applications of all sizes, including analytics platforms, GIS systems, healthcare solutions, and dynamic web apps.

This guide explains how to install PostgreSQL on an Ubuntu 20.04 server, activate the database service, and secure it for production deployment.

Prerequisites

Before proceeding, ensure the following:

• An Ubuntu 20.04 server is available.
• You can connect to the server via SSH as a non-root user with sudo privileges.
• The server is updated with the latest package information.

Install PostgreSQL

PostgreSQL can be installed from Ubuntu’s default APT repositories. Follow these steps to set up the database server packages and configure the service 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

Enable the PostgreSQL APT Repository

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

Press Enter when prompted to add the new repository to the system sources.

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

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

Install the PostgreSQL Database Server Package

$ sudo apt install -y postgresql

Start the PostgreSQL Service

$ sudo systemctl start postgresql

Check PostgreSQL Service Status

$ sudo systemctl status postgresql

Expected 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 privileged database account named postgres. To improve security, enable password authentication and protect access so only authorized users can connect.

Check the Installed PostgreSQL Version

$ psql --version

Expected Output:

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

Log in as the postgres User

$ sudo -u postgres psql

Set a Strong Password for the Default 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

To switch from peer authentication to scram-sha-256, update the main PostgreSQL configuration file (pg_hba.conf):

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

If your installed version differs, replace 17 in the path with the appropriate version number.

Restart the PostgreSQL Service

$ sudo systemctl restart postgresql

Access the PostgreSQL Database Server

The psql utility, which comes pre-installed with the PostgreSQL server package, allows access to the PostgreSQL console. Alternatively, graphical database clients can also be used for direct connections. Follow the steps below to connect to your PostgreSQL instance and create a sample database for a non-privileged user.

Create a Sample Database

Set up a new database named hospital and grant the db_manager user ownership rights:

$ sudo -u postgres createdb hospital -O db_manager

Enter the Postgres user password you configured earlier when prompted.

Test Database Access

Log in to PostgreSQL as db_manager and connect to the hospital database:

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

Provide the password for db_manager and press Enter to gain access.

Create a doctors Table

Inside the database, create a table called doctors:

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

This SQL statement sets up a new table with the following structure:

• doctor_id is the primary key and ensures each doctor entry is unique.
• first_name and last_name store doctor names.
• appointment_date records the date of patient appointments.
• SERIAL automatically generates IDs for new entries.

Insert Sample Data

Insert 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 stored in the doctors table:

hospital=> SELECT * FROM doctors;

Expected 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 Console

To leave the PostgreSQL console, run:

hospital=> quit;

Conclusion

PostgreSQL is now fully installed and operational on your Ubuntu 20.04 server. You have accessed the database with psql, created a sample database, defined tables, and inserted records. PostgreSQL can now be integrated with your applications to manage data securely. For more information, refer to the official PostgreSQL documentation.