Install PostgreSQL 9.6 with Transparent Data Encryption

Cluster encryption can be used if the DBA cannot or does not rely on the file system in terms of confidentiality.

If cluster encryption is enabled, PostgreSQL encrypts data  (both relations and write-ahead log) when writing to disk, and decrypts it when reading. The encryption is transparent, so the applications see no difference between the encrypted and unencrypted clusters.

PostgreSQL 9.6 with TDE on Ubuntu

In this blog, we go through the basic steps used to install PostgreSQL 9.6 with Transparent Data Encryption (TDE) on Ubuntu. You can download the patch for this here.

Create data directory for PostgreSQL:

Just for example, I am creating it at the default location:

sudo mkdir -p /usr/local/pgsql/data
sudo chown postgres:postgres /usr/local/pgsql/data

Install the libraries for readline, bison, flex, openssl, zlib and crypto:

sudo apt-get install libreadline8 libreadline-dev zlibc zlib1g-dev bison flex libssl-dev openssl

Run configure from the source code directory:

You can choose where all files will be installed by passing --prefix. Default is /usr/local/pgsql which I am using here for example. Make sure you enable openssl by passing --with-openssl:

sudo ./configure --prefix=/usr/local/pgsql --with-openssl
sudo make

The above two commands should run without errors. Now we are ready to install:

sudo make install

We can now proceed to initialize the cluster. For that, let's switch to the postgres user:

sudo su - postgres

As a good practice, lets add the PostgreSQL binaries to PATH:

export PATH=$PATH:/usr/local/pgsql/bin

To create an encrypted cluster, use the -K option to pass the initdb utility. For example:

initdb -D /usr/local/pgsql/data -K/usr/local/pgsql/keypass

Here /usr/local/pgsql/keypass is an executable file that returns either encryption key or encryption password with the appropriate prefix. In this case, we are passing the encryption_password in 8-16 characters in a simple executable file which outputs: 

encryption_password=<8-16_passphrase>

$ chmod 755 /usr/local/pgsql/keypass
$ cat /usr/local/pgsql/keypass
echo encryption_password=UrOs2k11CHiGo

Internally, PostgreSQL always uses the encryption key. If the encryption key command returns a password then a key will be generated from the password using a built-in key derivation function. Optionally, you can pass encryption_key as a hex encoded 256 bit key from any key store. 

$cat /usr/local/pgsql/keypass
echo encryption_key=<sh /location/return-key.sh>

On completion, initdb stores the encryption key command to postgresql.conf. Thus, the user can control the cluster using pg_ctl without passing the encryption command  again and again.

If encryption is enabled, full_page_writes must not be turned off, otherwise the server refuses to start. This is because the encryption introduces differences between data within a page, and therefore a server crash during a disk write can result in more serious damage of the page than it would do without encryption. The whole page needs to be retrieved from WAL in such a case to ensure reliable recovery.

vi postgresql.conf

full_page_writes = on

Once the PostgreSQL server is running, client applications should recognize no difference from an unencrypted cluster, except that data_encryption configuration variable is  set.

Unlike pg_ctl, some of the server applications (for  example pg_waldump) do not need the -K because they are not able to process the postgresql.conf file. 

Since WAL is encrypted, any replication solution based on log shipping assumes that all standby servers are encrypted using the same key as their standby server. On the other hand,  logica -replication allows replication between encrypted  and unencrypted clusters, or between clusters encrypted with different keys.

To read more about the ongoing discussion on adding TDE in core PostgreSQL, please check here.