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:
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:
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:
Step 4: Mount the Container
Next, let’s mount the encrypted container. Start by creating a directory in the /mnt/ as follows:
Once the directory is created, go ahead and mount the encrypted container using the “mount” command and specify the path.
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.
To move the PostgreSQL data, run the following “copy” command and ensure that you copy it to the directory that we created earlier:
Next, backup the original PostgreSQL data by moving it to a backup location.
You then need to create a symbolic link for the directory for a quick access.
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.
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.