‍

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.