How to Encrypt a Database at Rest in PostgreSQL on Ubuntu 22.04

Introduction

PostgreSQL is a database management system that has been around since 1996. Just like other database systems; SQL, MySQL, Oracle, etc., the primary purpose of PostgreSQL is to provide users with a way of creating databases for storage and data retrieval. One of its outstanding features includes transactions and concurrency support.

This guide demonstrates how to encrypt a database in PostgreSQL. The term At Rest in the title refers to the encryption being present on the database even when it’s not being used or residing on the disk without any activity. Encryption is not a question of should but a must. Without proper security, information is at a heightened risk of attacks.

In this tutorial, you will learn a few ways of encrypting databases created in PostgreSQL. Some of the methods require external libraries such as Z-Lib, OpenSSL etc. As you go down the list, you will finally have an understanding of which method works best for you in terms of both time and efficiency.

Prerequisites

To complete this tutorial, you will need:

• An Ubuntu 22.10 server configured by following our Initial Server Setup for Ubuntu 20.04 guide and then setting up a PostgreSQL installation.
• Installation of the following libraries
• Bison
• ReadLine
• Flex
• Zlib
• OpenSSL
• Crypto

through the command:

sudo apt-get install libreadline8 libreadline-dev zlib1g-dev bison flex libssl-dev openssl

Method 1 — Encrypting Database on System Disk

The first method is about encrypting the database while it is on the disk. It is the simplest form of encryption available that targets the file system level.

To do this, you must first create an encrypted filesystem using LUKS, which is a disk encryption specification created specifically for Linux. Once the encrypted filesystem is set up, we will then copy our Postgres clusters inside this system to achieve the desired encryption.

First, you must set up a list of all the filesystems from the console as follows:

df -hl

Once all the filesystems are listed, choose one that does not contain important information because it will later be formatted. A USB drive connected to the system can also be used for encryption.

In the console output after running the above command, you will notice a column named Mounted on. This shows the partition on which that specific filesystem is mounted.

Let’s suppose that the chosen partition is /dev/vda15. You must then unmount is as follows:

sudo umount /dev/vda15

Next, format the unmounted partition:

sudo wipefs -a /dev/vda15

Once formatting is complete, it is time to make the partition encrypted using LUKS:

sudo cryptsetup luksFormat /dev/vda15

This will ask for a passphrase that must be provided. Make sure to enter a passphrase that is long and contains alphanumeric characters.

After the encryption is complete, a map_point must be created for this partition to be visible in the system. This can be achieved by executing the following command:

sudo cryptsetup luksOpen /dev/vda15 map_point

This mapping can then be seen under the /dev/vda15 partition using the command:

lsblk

An encrypted partition is now set up perfectly. What’s needed next is a filesystem to store our files within the partition.

This can be achieved by creating either an exFat or FAT32 filesystem. For now, let’s create a Fat32 filesystem as follows:

sudo mkfs.vfat /dev/mapper/map_point -n encrypt_part

The command given above has two parameters:

• /dev/mapper/map_pint : This refers to the map_point that we created earlier using luksOpen.
• encrypt_part: This is the name of the volume that you must mention while creating the file system. You can change it to any name that you like.

Now you just need to make a directory where you will mount this filesystem. This can be achieved by executing the following commands:

mkdir /dev/vda15c
sudo mount /dev/mapper/map_point /dev/vda15c

The directory /dev/vda15c can be seen with the filesystem by running the command:

df -hl

Your filesystem is now encrypted and can be used for database storage. One of the ways to achieve it can be to copy the pre-existing database that you want to secure, into this partition and unmount. When you want to use it again, just mount the partition back, and start working on the database.

Log into your postgres account as follows:

sudo -i -u postgres

Then for the prompt, run this:

psql

Now to view the directory that contains your databases, issue a command as follows:

show data_directory

Copy the directory shown in the output. Let’s suppose that it is something like /var/lib/postgresql/14/main. Now that you know where your database files are stored, you simply need to copy or move these files to your encrypted filesystem.

To move the entire postgresql folder to your encrypted filesystem, you simply need to execute the following command after logging out of your postgres account.

sudo -av /var/lib/postgresql /dev/vda15c

Once the transfer is complete, your database folder is now encrypted. You can then choose to go forward and unmount and close the partition.

To unmount, first stop the postgresql service and then issue the umount command:

sudo systemctl stop postgresql
sudo umount /dev/vda15c

You have now learned how to encrypt the postgresql database at the disk. There can be many other modifications to this method, such as relocating the postgresql directory entirely on the filesystem to save mounting/unmounting time. This will be touched on in a later article in detail.

Method 2 — Encrypting Database using Transparent Data Encryption (TDE)

If filesystem encryption is too daunting and not considered reliable by the user, there is another method for encrypting and decrypting data while writing and retrieving from disk.

TDE doesn’t exist for Postgresql in its original package. It has to be downloaded and used. And its’ pre-built configuration is only available for server-side encryption, while the server runs. There are other configurations of a TDE as well, such as client side, tablespace etc.

To begin with, you first need to install Postgresql TDE from a third-party tool known as CyberTec as:

wget https://download.cybertec-postgresql.com/postgresql-12.3_TDE_1.0.tar.gz

Once it’s downloaded, you need to extract the package:

tar xvfz postgresql-12.3_TDE_1.0.tar.gz

A directory with the name postgresql-12.3_TDE_1.0.tar.gz will now be created in your root directory.

Now you need to set the current directory to this created folder as:

cd postgresql-12.3_TDE_1.0.tar.gz

Once inside this folder, we will start configuring our installation. But before this, we need to install a few necessary libraries as follows:

sudo apt-get install libldap2-dev libperl-dev python-dev

Now you can configure the installation:

./configure --prefix=/usr/local/pg12tde --with-openssl --with-perl \
 --with-python --with-ldap

Once configured, you need to issue the following command:

sudo make install

This might take a while to complete. After the make command is complete, you will need to switch to the contrib folder within the extracted package and issue the make command again as follows:

cd contrib
sudo make install

After the completion of this command, you now need to set up the key that will be used for encryption. This step is pretty simple since all you have to do is just write a file that can output the key value. To do this, you can create a file as follows:

cd \
touch provide_key.sh

You now need to open the file:

nano provide_key.sh

Once in the file, code the key below and save:

echo 8ae8234234h243294324

The key can be any value that you desire. Now all you have to do is give this file an attribute for it to be accessible using chmod:

chmod %x /provide_key.sh

Now you can log in to your postgres account as follows:

sudo su - postgres

After this step, you now need to initialize your database in a directory of your choice. This is essential to be on the safe side, that is to reduce any permission errors during the running of the postgres server. Create the folder as:

sudo mkdir /usr/local/postgres

And then add the attributes to it for access later on:

sudo chmod 775 /usr/local/postgres
sudo chown postgres /usr/local/postgres

Now you can initialize your database with the key you created in the provide_key.sh file above. Before that, you also need to set the export path for the database

export PATH=$PATH:/usr/local/pgsql/bin

And finally hit:

initdb -D /usr/local/postgres -K /provide_key.sh

This will enable the encryption and provide you with a command at the very end to copy and run for executing the encrypted server. The command will look something like this:

 ....Success. You can now start the database server using:

 pg_ctl -D /usr/local/postgres -l logfile start

That is how you set up a TDE server for Postgres. The key that you are provided in the provide_key.sh file will be used for reading/writing any data that your server receives. This achieves the purpose of securing data during transmission.

You might have a question here then, how does it encrypt the database at rest? After all, the encryption/decryption only works when the server is running. Doesn’t at rest mean that the database is not being used?

Well, technically, you are right. But, there is a catch. The key that is being used to encrypt the data is also being used to decrypt the same data when it is retrieved. So as soon as the server shuts down, you can be assured that the data residing on the disk is encrypted with the key provided. Hence, it works to provide security in both scenarios; during use and at rest.

Now that you have a clear understanding of TDE, it is time to learn how to encrypt parts of a database at rest.

Method 3 — Encrypting Parts of Database

Encrypting parts of a database is a very efficient method as compared to the ones given above. The reason is that you are in control of what you encrypt and it doesn’t take as much time as the former.

For setting this up, you first need to install the extension pgcrypto. Log in to your postgres account and then run the following command:

create extension if not exists pgcrypto;

The pgcrypto document provides a brief list of commands that you can use for encrypting/decrypting data. Here, however, you will use the symmetric key encryption as a start.

For simple use, you can assume a key of length 1-3 for an encrypted value that is readable and not too long. Because the algorithm hashes the prefix appended before data and then encrypts it with a session key, the result is usually longer. This is an excellent measure in terms of protecting data, since the longer the length, the more difficult it is to break a password.

Hence, you can now test this out. Let’s begin with creating a test_login table:

create table test_login (name VARCHAR(50), password TEXT);

Now, you can insert values into it with the password being encrypted as it should:

insert into test_login (name, password) values ('Jonathon', pgp_sym_encrypt('123ab', 'd3a')::TEXT)

This password will now be saved on disk in this encrypted format. To decrypt this value, simply run the following query:

select
	name,
	pgp_sym_decrypt(password::bytea, 'd3a')
from
test_login;

The command will return the password in its original decrypted form. Hence, in this way you can achieve database encryption at rest when needed.

Conclusion

In this article, you learned three different ways of database encryption at rest while using postgresql.

Database encryption in the filesystem is less costly but more storage-intensive. It encrypts the entire partition and may lead to other sorts of data being encrypted too.

The second option of encryption using TDE is better since it also protects the data in real time. But it cannot encrypt the records individually and will mostly secure the entire database. Decryption in real-time will still be slow.

The third idea of encrypting parts of a database is the most efficient. It is not only less costly but also gives the user the ability to secure only data that he/she deems important rather than encrypting every other irrelevant record. Its drawback is that most users are unaware of how hackers can use inference ( an attack ) to pick out important information from a database, hence rendering the encrypted parts void. Thus, some might argue in this case, that entire database encryption is much more secure and reduces human errors and faults.

In the end, it is up to the user to find what works best for them. You can check more encryption options from the links provided in the article, which will also be elaborated on and explained in another tutorial later on so that you can not just understand but also test encryption in real time.