Transparent Data Encryption Installation Guide

INSTALLING POSTGRESQL 12.X TDE FROM SOURCE

PostgreSQL 12.x TDE is a version of PostgreSQL which supports transparent data encryption. In many practical business cases it is necessary to encrypt data on disk. PostgreSQL TDE has been designed to do exactly that in the most efficient way possible.

Here we will show you how to install PostgreSQL TDE from source on Linux and Mac OS X by explaining the following steps:

1. Downloading the source code
2. Extracting and configuring PostgreSQL 12 TDE
3. Compiling the code
4. Setting up key management
5. Creating a database instance / cluster

2. EXTRACTING AND CONFIGURING POSTGRESQL 12 TDE

Once you have downloaded the file, you can easily unpack it. Here is how it works:

tar xvfz postgresql-12.3_TDE_1.0.tar.gz 

A directory will be created containing the entire source code of PostgreSQL TDE. Enter the directory and execute the following command:

For Mac OS X:

./configure --prefix=/Users/hs/pg12tde \ 
   --with-openssl \
   --with-perl \
   --with-python --with-ldap \
   CPPFLAGS="-I$(brew --prefix openssl)/include" \
   LDFLAGS="-L$(brew --prefix openssl)/lib"

⎟  Note: On OS X, it makes sense to install HomeBrew to make sure that you can install all the dependencies first to compile the code. It is the easiest way to get all the software to compile and run PostgreSQL.

For Linux:

On Linux, compiling PostgreSQL is pretty straight forward. Make sure that all the dependencies outlined in the documentation are met. Then run:

./configure --prefix=/usr/local/pg12tde --with-openssl --with-perl \
   --with-python --with-ldap

If something goes wrong during configuring, it is very likely that you have missed a package.

4. SETTING UP KEY MANAGEMENT

Key management is an important aspect. To encrypt a database instance, a key to do so has to come from somewhere. In case of PostgreSQL TDE, the key is coming from an flexible external program. Ideally the key DOES NOT COME from the local filesystem but from remote secure keystore.

Before creating your database instance, you have to write some code to make sure that the key can be read by the database during startup and instance creation.

Here is the most simplistic example possible:

% cat /somewhere/provide_key.sh 
#!/bin/sh 
echo 882fb7c12e80280fd664c69d2d636913

All you need is a program that prints the key to stdout – and that’s it! Make sure that PostgreSQL is able to execute this program:

 % chmod +x /somewhere/provide_key.sh 

⎟   Note: You don’t have to write a shell script – you can use any kind of executable such as a C, Go or Python.

ENCRYPTION BEHIND THE SCENES

The reason why you don’t need additional parameters is that initdb has already added the configuration to postgresql.conf for you:

 % grep encryption_key postgresql.conf 
 encryption_key_command = '/somewhere/provide_key.sh'

postgresql.conf already has the information to fetch the key on startup. The advantage is that you don’t have to adapt existing scripts. The only change you have to make is to add one more parameter to initdb. Et voilá – you are done.

IS MY DATABASE SERVER ENCRYPTED?

The most obvious and most common question at this point is: “Is that really everything? How can I figure out that my server is really encrypted?” To do that, you can make use of pg_controldata, which is a program extracting useful information out of your database server. Keep in mind that pg_controldata has NOTHING to do with TDE – it is there anyway. 

In case of PostgreSQL TDE, pg_controldata produces slightly more information than usual:

 % pg_controldata /some_where/ | grep -i encryp
 Data encryption:           on
 Data encryption fingerprint:          740A905130FE614CE0BE36B612157A09

As you can see, encryption is on and your data is secure.

However, if you don’t have access to the machine via SSH, there is a second way to see if data is encrypted:

 test=# SHOW data_encryption;
  data_encryption 
 ----------------
  on
 (1 row)

PostgreSQL TDE offers an additional variable. data_encryption on / off will also tell you whether the database has been encrypted or not.