Securing data is of paramount importance in today’s digital age. PostgreSQL, a renowned database management system, facilitates data protection through encryption. This guide will unravel various methods to encrypt a database in PostgreSQL on the Ubuntu operating system.
Database Encryption: An Indispensable Security Measure
Database encryption is an essential security measure in today’s world where data breaches are becoming increasingly common. Utilizing encryption mechanisms, PostgreSQL ensures the protection of your data while at rest. It means the data is secured even when it’s inactive or stored on the disk.
Prerequisites for Database Encryption in PostgreSQL on Ubuntu
Before diving into the encryption process, make sure you have the following prerequisites:
- A configured Ubuntu 22.10 server.
- A PostgreSQL installation set up on your server.
- Installation of the necessary libraries: Bison, ReadLine, Flex, Zlib, OpenSSL, Crypto.
- Use the following command to install the above libraries:
sudo apt-get install libreadline8 libreadline-dev zlib1g-dev bison flex libssl-dev openssl
Method 1: Encrypting Database on System Disk
The first encryption method focuses on securing the database while it is stored on the disk. This approach employs file system-level encryption for the security of your data in PostgreSQL.
Setting up an Encrypted Filesystem Using LUKS
LUKS (Linux Unified Key Setup) is a disk encryption specification designed exclusively for Linux. It allows you to create an encrypted filesystem on which you can store your Postgres clusters.
To get started, you need to list all the filesystems with the following command:
df-hl
Choose a filesystem that doesn’t hold crucial data as it will be formatted later. After selecting a partition, for example,/dev/vda15
, unmount it using the following command:
sudo umount/dev/vda15
Next, format the unmounted partition:
sudo wipefs-a/dev/vda15
Now, it’s time to encrypt the partition using LUKS:
sudo cryptsetup luksFormat/dev/vda15
You will be asked for a passphrase — make sure it’s strong and contains alphanumeric characters.
After the encryption is complete, create a map_point for the partition to be visible in the system:
sudo cryptsetup luksOpen/dev/vda15 map_point
This mapping can then be viewed under the /dev/vda15
partition using the command:
lsblk
Creating a Filesystem within the Encrypted Partition
Next, create a filesystem within the encrypted partition. For this demonstration, we will set up a Fat32 filesystem:
sudo mkfs.vfat/dev/mapper/map_point-n encrypt_part
Next, create a directory to mount this filesystem:
mkdir/dev/vda15c sudo mount/dev/mapper/map_point/dev/vda15c
You can view the directory with the filesystem using the command:
df-hl
Transferring Database to Encrypted Filesystem
The final step in this method is to transfer your existing database to the encrypted filesystem:
sudo-av/var/lib/postgresql/dev/vda15c
After the transfer, your database folder is now encrypted and secured. You can unmount and close the partition whenever required:
sudo systemctl stop postgresql sudo umount/dev/vda15c
Method 2: Encrypting Database using Transparent Data Encryption (TDE)
The second method for securing your PostgreSQL database on Ubuntu involves Transparent Data Encryption (TDE). TDE is a mechanism that allows for the encryption and decryption of data while it’s being written and retrieved from the disk.
Installing PostgreSQL TDE
To get started with TDE, you need to install the PostgreSQL TDE package. This package isn’t available in the original PostgreSQL package and needs to be downloaded separately from a third-party tool known as CyberTec:
wget https://download.cybertec-postgresql.com/postgresql-12.3_TDE_1.0.tar.gz
After downloading, extract the package:
tar xvfz postgresql-12.3_TDE_1.0.tar.gz
Configuring PostgreSQL TDE
Once the package is extracted, you need to configure the installation. Before starting the configuration, install the necessary libraries:
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
After configuration, use the following command to install:
sudo make install
Switch to the contrib
folder within the extracted package and install again:
cd contrib sudo make install
Setting up the Encryption Key
Next, set up the key that will be used for encryption. To do this, create a file and write the key value:
touch provide_key.sh nano provide_key.sh
Write the key in the file and save:
echo 8ae8234234h243294324
Finally, make the file accessible using chmod
:
chmod%x/provide_key.sh
Initializing the Database
Now, you need to initialize your database in a directory of your choice. First, create a directory:
sudo mkdir/usr/local/postgres
Change the permissions for the directory:
sudo chmod775/usr/local/postgres sudo chown postgres/usr/local/postgres
Set the export path for the database:
exportPATH=$PATH:/usr/local/pgsql/bin
Finally, initialize your database with the key you created:
initdb-D/usr/local/postgres-K/provide_key.sh
And that’s it! You have successfully set up a TDE server for PostgreSQL on Ubuntu.
Method 3: Encrypting Specific Parts of a Database
The final method in our tutorial focuses on encrypting specific parts of a PostgreSQL database on Ubuntu. This method offers the flexibility of securing only the data that is deemed important rather than encrypting the entire database.
Installing the pgcrypto Extension
First, install the pgcrypto
extension. Log in to your postgres account and run the following command:
create extensionif not exists pgcrypto;
Encrypting Data
The pgcrypto
extension provides a list of commands for encrypting and decrypting data. You can use symmetric key encryption for this purpose. Let’s begin with creating a test_login
table:
create tabletest_login(nameVARCHAR(50), passwordTEXT);
Now, insert values into it with the password being encrypted:
insert intotest_login(name, password)values('Jonathon',pgp_sym_encrypt('123ab','d3a')::TEXT)
To decrypt this value, run the query:
select name, pgp_sym_decrypt(password::bytea,'d3a') from test_login;
The command will return the password in its original decrypted form. Hence, this method allows you to secure specific parts of a database.
Wrapping Up
In this tutorial, you have learned three different methods of securing a PostgreSQL database on Ubuntu — encrypting the database on the system disk, using Transparent Data Encryption (TDE), and encrypting specific parts of a database. Each method has its advantages and drawbacks, and the choice depends on the specific needs and constraints of your project.
The filesystem encryption method is less resource-intensive but requires more storage space. TDE provides real-time data protection but may slow down decryption operations. Encrypting specific parts of a database is a more efficient approach, as it allows you to secure only the important data. However, it requires advanced knowledge to prevent potential security vulnerabilities.
In the end, the choice of encryption method should align with your project’s requirements and your understanding of data security.