Cluster encryption can be used if the DBA cannot or does not rely on the file system in terms of confidentiality. If this feature 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
sudo ./configure --prefix=/usr/local/pgsql --with-openssl
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
sudo su - postgres
As a good practice, lets add the PostgreSQL binaries to PATH:
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
/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:
$ chmod 755 /usr/local/pgsql/keypass
$ cat /usr/local/pgsql/keypass
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.
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.
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.