Install, Configure and Secure PostgreSQL 12 on Ubuntu 18.04

PostgreSQL 12

There are many articles on the net that covers installing, configuring and securing PostgreSQL but many of them are missing or obsolete. The official documentation is great if you have time to read. After using MySQL and MongoDB for many years, I have decided to try PostgreSQL in one of my projects. You, as a reader, may find this article too sophisticated but the writer is a minimalistic, green developer. 🤷🏻‍♂️

The titles below are the main steps in the whole business. Look at this article, not just a setup of some software but the setup of a web application project.

The reader required to have a basic knowledge and experience in Linux, bash, ssh and SQL.

Emphasized texts in code blocks below indicate that it is a variable which you can replace it with anything appropriate you want.

Server Setup

This section is about making the developer and the server ready for PostgreSQL. Important points summarized below but not covered in detail since that would be a topic of another article.

1. Make ready public-private key pairs in your computer.
2. Have a cloud server Ubuntu 18.04 installed.
3. Authorize your public key with the server.

This is done automatically for me since I already have my public key in Digitalocean and selected it during server creation. Other providers such as Amazon AWS and Google Cloud have also similar mechanisms to authorize the public key.

4. Connect to the server with your private key.

ssh -i ~/.ssh/PRIVATE_KEY_NAME root@IP

5. Install the required packages for PostgreSQL. The reader may also want to install the required packages for his/her project, here.

apt update
# required for postgresql
apt install -y pkg-config build-essential software-properties-common dpkg-dev gcc g++ make wget tcl libicu60 libicu-dev libxml2-dev libxml2 libxslt1.1 libxslt1-dev libreadline7 libreadline-dev libssl-dev libsystemd-dev
# yes we are going to install postgresql from source :)
# required for the project (optional)
apt install -y PACKAGE_NAMES

6. Create OS users and group. We are going to create two users and one group that users are connected to. One is a super user and the other is web application user.

# create group
groupadd -f TEAM_NAME
# create super user
useradd -m -s /bin/bash SUPER_USER_NAME
usermod -aG sudo SUPER_USER_NAME
usermod -aG TEAM_NAME SUPER_USER_NAME
# create application user
useradd -m -s /bin/bash APP_USER_NAME
usermod -aG TEAM_NAME APP_USER_NAME

We are still the root user. Let’s switch to the super user.

# first set password for the super user
passwd SUPER_USER_NAME
# login as super user
su SUPER_USER_NAME

PostgreSQL 12 Installation

We are going to install PostgreSQL from the source. If you are curious refer also to the official documentation for more detailed information.

1. Download PostgreSQL Source Code:

# make sure that you are at your home directory
cd ~
# download postgresql
wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz
gunzip postgresql-12.0.tar.gz
tar xf postgresql-12.0.tar
cd postgresql-12.0

2. Configure PostgreSQL Source Code for installation. The following command has some options. The reader may remove these options or add other options optionally. Result of this command is also important. If you see a line that starts with configure: error: then you should resolve it first.

./configure --with-icu --with-openssl --with-systemd --with-libxml --with-libxslt

3. Make a build from the source. The command below makes a build according to our server and configuration. Finally, we are going to have a build to install the PostgreSQL as a result of the command below.

make > /dev/null 2>&1
# the reader may execute just "make" but there will be alot of output in the terminal.

4. Test if that build will run successfully on the server. The reader should see a message that says All X tests passed if everything goes right.

make check

5. Install. The command below is going to actually install PostgreSQL to our machine.

# may ask password for the user SUPER_USER_NAME that we set previously.
sudo make install
# will output a final message that says "PostgreSQL installation complete."

Configure PostgreSQL 12 Server

Like any database software, PostgreSQL Server needs a data directory to store its databases and privileged OS users to manage these databases.

We already have a user. Let’s authorize it to the directory we chose for PostgreSQL databases.

DATA_DIR=/usr/local/pgsql/data
sudo mkdir $DATA_DIR
sudo chown SUPER_USER_NAME $DATA_DIR

Tell PostgreSQL that we want to store our databases in the directory specified above.

# initiate database cluster
/usr/local/pgsql/bin/initdb --encoding=UTF8 --locale=C -D $DATA_DIR

The argument encoding determines what kind of characters our database will store. This is mostly set to UTF8 by default. You may not specify it at all. But it is important to know that encoding and locale are two important arguments when building international applications. The other argument locale set to C, unlike its default value which is UTF8.en_US mostly. locale plays an important role in sorting records in a table. C means unknown because we are going to specify locale in our index definitions and queries. (by adding COLLATE to our queries.) It is de-x-icu when we fetch posts in german or en-x-icu when we fetch posts in english for example.

Now we can start the PostgreSQL server. We are going to use a systemd file to make our PostgreSQL server always active in our machine.

# open an empty systemd file
sudo nano /etc/systemd/system/postgresql.service
# place the following content into the systemd file
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Type=notify
User=SUPER_USER_NAME
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
# press kntrl+x, then press y and then enter to save the file.
sudo systemctl daemon-reload
sudo systemctl start postgresql
sudo systemctl enable postgresql
# check
sudo systemctl status postgresql
# it should be: active (running)

PostgreSQL logs to stderr by default. Server admin must execute the journalctl -xeu postgresql command to see recent logs. However, it is much more efficient to save logs into files for better analysis.

# open postgresql.conf file and find "logging_collector" parameter
sudo nano /usr/local/pgsql/data/postgresql.conf
# set logging_collector to on
# press kntrl+x, then press y and then enter to save the file.
# restart postgresql server
sudo systemctl restart postgresql

Our web application needs a database to store its data.

/usr/local/pgsql/bin/createdb --encoding=UTF8 --locale=C APP_DB_NAME

We can connect to this database and execute some SQL commands.

/usr/local/pgsql/bin/psql APP_DB_NAME
# psql console will open
sum 1+5 as sum; # press enter
sum
-----
6
(1 row)
# don't forget to add a semicolon to the end of your queries

As you see above we don’t enter a password to connect to the database and execute SQL queries. PostgreSQL does provide strong authentication mechanisms but it must be configured by the admin. We are going to cover this in the next title. We also cover how to create a database user for our web application since using our super user doesn’t look like a good idea.

Securing PostgreSQL 12 Server

There are many aspects of security in PostgreSQL. Essential ones covered in this article. Essential ones are the ones which can reasonably be considered essential in single server setups.

There are different ways clients connect to the PostgreSQL server. The way we have used until now is UNIX socket connection (through our super user), PostgreSQL calls it a local type of connection. There is also a host type connection which is how our web application (client) connects to the PostgreSQL database. We are going to secure both of them, right now.

Make the following update in postgresql.conf file. We tell PostgreSQL that we are going to use a strong password encryption mechanism.

sudo nano /usr/local/pgsql/data/postgresql.conf
# find "password_encryption" parameter.
# uncomment it and set its value to scram-sha-256
password_encryption = scram-sha-256
# press kntrl+x, then press y and then enter to save the file.
sudo systemctl restart postgresql

Set a password for our super user.

# open psql console
/usr/local/pgsql/bin/psql APP_DB_NAME
# set password
alter user SUPER_USER_NAME with password 'SUPER_USER_PASS';

Password set for our single and super user but this is not enough. Authentication and access control managed by the pg_hba.conf file in the /usr/local/pgsql/data directory. Content of this file and meaning of the records it has:

sudo nano /usr/local/pgsql/data/pg_hba.conf
# default pg_hba.conf file
# trust all users to access all databases in local connections
local all  all                trust
# trust all users to access all databases in IPv4 connections
host  all  all  127.0.0.1/32  trust
# trust all users to access all databases in IPv6 connections
host  all  all  ::1/128       trust
# skipped replication connections

As you may guess, trust means don’t ask anything. 😱 We are going to change trust method to scram-sha-256 to provide password based strong authentication mechanism.

# secured ph_hba.conf file
# ask for password to all users to access all databases
local  all  all              scram-sha-256
host   all  all localhost    scram-sha-256
# you may also set replication connections
# press kntrl+x, then press y and then enter to save the file.
# restart postgresql server
sudo systemctl restart postgresql

We literally said anyone who tries to connect must give a valid password and must be from same machine. When we try to connect now:

/usr/local/pgsql/bin/psql APP_DB_NAME
# psql console will not open directly, it will ask for a password
Password for user SUPER_USER_NAME:

Every time we try to connect with our super user we will be asked for a password. There is a programmatic way to provide our password since we are also an OS user, PostgreSQL allows us to put a password file inside our home directory.

cd ~
sudo nano .pgpass
# enter the following as file content
localhost:5432:APP_DB_NAME:SUPER_USER_NAME:SUPER_USER_PASS
# press kntrl+x, then press y and then enter to save the file.
# configure file permissions
chmod 0600 .pgpass

We have one user in the PostgreSQL server and it is secured. But we won’t use it in our web application, of course. We are going to create another user and authorize it to manage the database.

PostgreSQL calls users as roles.

# enter sql console
/usr/local/pgsql/bin/psql APP_DB_NAME
# create user
create role APP_USER_NAME with login password 'APP_USER_PASS';
# privilige user for the database
alter database APP_DB_NAME owner to APP_USER_NAME;
# check for the new owner
\l

Our database only allows connections from localhost and clients must give a valid password to interact with the database. From now on, the reader may continue to set up the required services for the web application such as nginx, redis etc. However, user privileges and the structure of the database in PostgreSQL really depends on the project. There are concepts such as tablespaces and schemas which give admin more control about privileges.