Welcome to our beginner’s guide on installing, configuring, and securing PostgreSQL on a Linux system. PostgreSQL is a powerful, open-source relational database system that’s widely used for its robustness and advanced features. In this guide, we’ll walk you through the steps to get PostgreSQL up and running on your Linux machine, even if you’re new to Linux and databases.

Step 1: Installing PostgreSQL

Update Your System

First, let’s ensure your system is up to date. Open your terminal and run:

sudo apt update
sudo apt upgrade

Install PostgreSQL

Next, install PostgreSQL and the necessary tools:

sudo apt install postgresql postgresql-contrib

The postgresql package includes the core database server, and postgresql-contrib contains useful additional utilities.

Start and Enable PostgreSQL Service

To ensure PostgreSQL starts automatically after a reboot, enable the service:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 2: Configuring PostgreSQL

Switch to the PostgreSQL User

PostgreSQL creates a default user called postgres. Switch to this user to configure your database:

sudo -i -u postgres

Access the PostgreSQL Command Line

Enter the PostgreSQL interactive terminal:

psql

Create a New Database and User

In the psql shell, you can create a new database and user:

CREATE DATABASE mydb;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Replace mydb, myuser, and mypassword with your desired database name, username, and password.

Exit the PostgreSQL Command Line

To exit the psql shell, type:

\q

Exit the PostgreSQL User Session

Type exit to return to your regular user session.

Step 3: Securing PostgreSQL

Edit PostgreSQL Configuration Files

PostgreSQL configuration files are located in the /etc/postgresql/ directory. The main files we will edit are postgresql.conf and pg_hba.conf.

sudo nano /etc/postgresql/12/main/postgresql.conf

Set Listening Addresses

In postgresql.conf, find the listen_addresses setting and update it:

listen_addresses = 'localhost'

This ensures PostgreSQL listens only on the local machine. Save the file and exit.

Configure Client Authentication

Next, edit the pg_hba.conf file:

sudo nano /etc/postgresql/12/main/pg_hba.conf

Add the following line to configure local connections to use password authentication:

local   all             all                                     md5

This setting requires users to provide a password when connecting to the database.

Restart PostgreSQL Service

Apply the changes by restarting the PostgreSQL service:

sudo systemctl restart postgresql

Step 4: Basic Security Practices

Regular Backups

Ensure regular backups of your database using pg_dump or similar tools. A basic backup command is:

pg_dump mydb > mydb_backup.sql

Update Regularly

Keep your PostgreSQL installation up to date to benefit from security patches:

sudo apt update
sudo apt upgrade postgresql

Use Strong Passwords

Always use strong, unique passwords for your database users.

Conclusion

Congratulations! You’ve successfully installed, configured, and secured PostgreSQL on your Linux system. PostgreSQL is now ready to handle your data with robustness and security. As you grow more comfortable with PostgreSQL, you can explore its advanced features to make the most out of your database system.