Cluster level encryption for PostgreSQL 14

The discussions how and why TDE (Transparent data encryption) should be implemented in PostgreSQL goes back several years. You can have a look at these two more recent threads to get an idea on how much discussion happened around that feature:

• [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)
• Re: Internal key management system

Finally an essentials part for that infrastructure was committed and I am sure, many people have waited for that to appear in plain community PostgreSQL. Lets have a quick look how it works and if it easy to play with.

To get an encrypted cluster you need to specify that when you initialize the cluster with initdb. One additional requirement is, that PostgreSQL was compiled with “–with-openssl”:

postgres@debian10pg:/home/postgres/ [pgdev] pg_config | grep openssl
CONFIGURE =  '--prefix=/u01/app/postgres/product/DEV/db_1/' '--exec-prefix=/u01/app/postgres/product/DEV/db_1/' '--bindir=/u01/app/postgres/product/DEV/db_1//bin' '--libdir=/u01/app/postgres/product/DEV/db_1//lib' '--sysconfdir=/u01/app/postgres/product/DEV/db_1//etc' '--includedir=/u01/app/postgres/product/DEV/db_1//include' '--datarootdir=/u01/app/postgres/product/DEV/db_1//share' '--datadir=/u01/app/postgres/product/DEV/db_1//share' '--with-pgport=5432' '--with-perl' '--with-python' '--with-openssl' '--with-pam' '--with-ldap' '--with-libxml' '--with-libxslt' '--with-segsize=2' '--with-blocksize=8' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-7' '--with-systemd'

If that is given you can initialize a new cluster and tell initdb how to get the encryption key:

postgres@debian10pg:/home/postgres/ [pgdev] initdb --help | grep cluster-key-command
  -c  --cluster-key-command=COMMAND

If this key is provided, two internal keys are generated, one for the table and index files (and any temporary objects) and one for the WAL files:

postgres@debian10pg:/home/postgres/ [pgdev] initdb --pgdata=/var/tmp/pgenc --cluster-key-command=/home/postgres/get_key.sh
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Cluster file encryption is enabled.

creating directory /var/tmp/pgenc ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Zurich
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/tmp/pgenc -l logfile start

The command to get the key in this example is quite trivial:

postgres@debian10pg:/home/postgres/ [pgdev] cat /home/postgres/get_key.sh
echo "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"

In a real setup the key should of course come from an external key store. Lets try to start the cluster:

postgres@debian10pg:/home/postgres/ [pgdev] export PGPORT=8888
postgres@debian10pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pgenc/ start
waiting for server to start....2020-12-26 16:11:12.220 CET [7106] LOG:  starting PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-12-26 16:11:12.221 CET [7106] LOG:  listening on IPv6 address "::1", port 8888
2020-12-26 16:11:12.221 CET [7106] LOG:  listening on IPv4 address "127.0.0.1", port 8888
2020-12-26 16:11:12.234 CET [7106] LOG:  listening on Unix socket "/tmp/.s.PGSQL.8888"
2020-12-26 16:11:12.250 CET [7109] LOG:  database system was shut down at 2020-12-26 16:08:34 CET
2020-12-26 16:11:12.274 CET [7106] LOG:  database system is ready to accept connections
 done
server started

Why does that work? We did not provide the key at startup time so PostgreSQL somehow must know how to get the key. Actually there is a new parameter that automatically gets the command we specified when we initialized the cluster:

postgres@debian10pg:/home/postgres/ [pgdev] grep cluster_key /var/tmp/pgenc/postgresql.conf 
cluster_key_command = '/home/postgres/get_key.sh'

If we remove that and start again it will not work:

postgres@debian10pg:/home/postgres/ [pgdev] psql -c "alter system set cluster_key_command=''" postgres
ALTER SYSTEM
postgres@debian10pg:/home/postgres/ [pgdev] grep cluster_key /var/tmp/pgenc/postgresql.auto.conf 
cluster_key_command = ''
postgres@debian10pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pgenc/ stop
2020-12-26 16:15:29.457 CET [7106] LOG:  received fast shutdown request
waiting for server to shut down....2020-12-26 16:15:29.467 CET [7106] LOG:  aborting any active transactions
2020-12-26 16:15:29.469 CET [7106] LOG:  background worker "logical replication launcher" (PID 7115) exited with exit code 1
2020-12-26 16:15:29.473 CET [7110] LOG:  shutting down
2020-12-26 16:15:29.534 CET [7106] LOG:  database system is shut down
 done
server stopped
16:15:29 postgres@debian10pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/pgenc/ start
waiting for server to start....2020-12-26 16:15:31.762 CET [7197] LOG:  starting PostgreSQL 14devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-12-26 16:15:31.763 CET [7197] LOG:  listening on IPv6 address "::1", port 8888
2020-12-26 16:15:31.763 CET [7197] LOG:  listening on IPv4 address "127.0.0.1", port 8888
2020-12-26 16:15:31.778 CET [7197] LOG:  listening on Unix socket "/tmp/.s.PGSQL.8888"
2020-12-26 16:15:31.786 CET [7197] FATAL:  cluster key must be 64 hexadecimal characters
2020-12-26 16:15:31.787 CET [7197] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

The two keys that have been generated when the cluster was initialized can be found in $PGDATA:

postgres@debian10pg:/var/tmp/pgenc/ [pgdev] ls -la pg_cryptokeys/live/
total 16
drwx------ 2 postgres postgres 4096 Dec 26 16:08 .
drwx------ 3 postgres postgres 4096 Dec 26 16:08 ..
-rw------- 1 postgres postgres   72 Dec 26 16:08 0
-rw------- 1 postgres postgres   72 Dec 26 16:08 1

The reason for two separate keys is, that a primary and a replica cluster can have a different key for the table, index and all other files generated during database operations but still can have the same key for the WAL files. Btw: pg_controldata will also tell you if a cluster is encrypted:

postgres@debian10pg:/var/tmp/pgenc/base/12833/ [pgdev] pg_controldata -D /var/tmp/pgenc/ | grep encr
File encryption key length:           128

That really is a nice and much appreciated feature. Currently only the whole cluster can be encrypted, but I am sure that is sufficient for most of the use cases. Lets hope that it will not get reverted for any reason.