Setup Data Encryption at Rest in PostgreSQL

You must secure your data whenever you are working with any database. For PostgreSQL, it’s possible to secure the data at rest. The logic involves securing your data while it’s stored on the disk. That way, you will have one more line of defense to protect your data from an attacker.

Although PostgreSQL doesn’t offer built-in encryption options, you can setup the data encryption at rest using the third-party encryption methods. Today’s tutorial focuses on utilizing the Transparent Data Encryption (TDE) method to enable the filesystem-level encryption.

How to Setup the Data Encryption at Rest in PostgreSQL

When setting the data encryption at rest in PostgreSQL, the goal is to make the data unreadable on the filesystem by requiring a decryption key. That way, unauthorized access is eliminated.

When PostgreSQL is running on your server, you can setup the filesystem-level encryption using third-party tools such as the Linux Unified Key Setup (LUKS). You can find the appropriate solution for your system. Here, we are working with Ubuntu and setup the data encryption using the following steps.

Step 1: Install the Filesystem Encryption Tool

Once you select the encryption method, you must install the required tools. We select the filesystem-level encryption method and install LUKS. To install LUKS, install the cryptsetup as follows:

sudo apt-get install cryptsetup

Press “y” to continue the installation and ensure everything installs as expected.

Step 2: Setup an Encrypted Container

Since we are setting up the filesystem-level encryption, we must create an encrypted directory on our disk which contains the PostgreSQL data. Check the available devices on your operating system with the following command:

sudo fdisk -l

Next, select the appropriate device and run the following command. Here, we use the /dev/sdb device. You will be prompted to confirm the action by typing “YES” and then enter a passphrase.

You then must encrypt it using LUKS by running the following command:

Step 3: Format the Container

For the created container, we must format it. We use the “mkfs.ext4” option by running the following code:

sudo mkfs.ext4 /dev/mapper/postgres_encrypted

Step 4: Mount the Container

Next, let’s mount the encrypted container. Start by creating a directory in the /mnt/ as follows:

sudo mkdir /mnt/postgres

Once the directory is created, go ahead and mount the encrypted container using the “mount” command and specify the path.

sudo mount /dev/mapper/postgres_encrypted /mnt/postgres/

Step 5: Move the PostgreSQL Data

So far, we created an encrypted container to store our PostgreSQL data, but we have yet to move the data. Before moving the data, we must stop the PostgreSQL service.

sudo systemctl stop postgresql

To move the PostgreSQL data, run the following “copy” command and ensure that you copy it to the directory that we created earlier:

sudo rsync -av /var/lib/postgresql /mnt/postgres

Next, backup the original PostgreSQL data by moving it to a backup location.

sudo mv /var/lib/postgresql /var/lib/postgresql_backup

You then need to create a symbolic link for the directory for a quick access.

sudo ln -s /mnt/postgres/postgresql /var/lib/postgresql

That’s it. We managed to copy and move the PostgreSQL data to our filesystem-level encrypted container to ensure that we secure the data at rest.

Step 6: Edit the PostgreSQL Config File

The data_directory in the config file reflects the precious PostgreSQL data location. However, we must edit it to match the location of the PostgreSQL data in the encrypted container that we created. So, open the PostgreSQL config file using a text editor. Locate the data_directory section. It appears as shown in the following before we edit it. The path may differ depending on the PostgreSQL version installed on your system.

Change the path to direct to the encrypted container that we created in step 4. In our case, the new path is as follows:

Step 7: Save, Exit, and Restart

Save and exit the PostgreSQL configuration file. Next, start or restart PostgreSQL. You managed to setup the data encryption at rest in PostgreSQL.

That’s it! You can continue using PostgreSQL securely and enjoy the new filesystem-level encryption.

Conclusion

Setting up the data encryption at rest in PostgreSQL involves determining what encryption method to use and then setting it up. We selected the TDE encryption using LUKS to setup a filesystem-level encryption. Moreover, we detailed every step to follow to set it up. That’s it! Try it out and follow the provided steps.