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
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.
To complete this tutorial, you will need:
through the command:
- sudo apt-get install libreadline8 libreadline-dev zlib1g-dev bison flex libssl-dev openssl
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
- 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:
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_pointthat we created earlier using
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
/dev/vda15c can be seen with the filesystem by running the command:
- df -hl
Note: Running the command
lsblk will now mention the type as
crypt under the column
type of the
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:
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
- sudo -av /var/lib/postgresql /dev/vda15c
Warning: You might see in the
verbose output that some
attributes may not be transferred successfully. This is perfectly okay since the filesystem that we are transferring the files on is a
crypt type and will not allow permission modifications in any manner.
Once the transfer is complete, your database folder is now encrypted. You can then choose to go forward and
close the partition.
To unmount, first stop the
postgresql service and then issue the
- 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.
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
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
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:
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 %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.
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
- 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)
password will now be saved on disk in this encrypted format. To decrypt this value, simply run the following query:
- pgp_sym_decrypt(password::bytea, 'd3a')
The command will return the
password in its original decrypted form. Hence, in this way you can achieve database encryption at rest when needed.
In this article, you learned three different ways of database encryption at rest while using
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.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.