Encryption key management with PostgreSQL TDE

Preamble

PostgreSQL TDE: How encryption keys are handled

Running PostgreSQL TDE is easy. However, it necessitates a fundamental comprehension of how keys are typically handled.

Deploying a PostgreSQL instance

Behind the scenes, initdb runs when a new PostgreSQL instance is built. When the service is started for the first time, most systems take care of that for you. Initdb can do things like deploy a config file, create the initial content of the data directory, and more.

The most important thing to remember is that TDE (Transparent Data Encryption) needs the data files on a disk to be encrypted. The obvious issue that follows is: How do we locate the key? To handle keys effectively, we need to be a little inventive because it’s not a good idea to store the key directly in the data directory.

When creating PostgreSQL TDE, we chose a straightforward plugin design. Key management practices can vary widely. There are many different keystores, and each company has different needs based on use cases, philosophies, rules, and maybe even the law.

PostgreSQL TDE uses a plugin, which is an executable we must specify, to get the key:

 [tde@cybertec]$ initdb --help

initdb initializes a PostgreSQL database cluster.

Usage:

   initdb [OPTION]... [DATADIR]

Options:

 ...   -k, --data-checksums      use data page checksums   -K, --encryption-key-command                             command that returns encryption key

The -K option requires you to point to a script that returns the key as a string. Note that this key is never visible to anybody but the database server. It does not show up in a process table. It does not show up anywhere. The beauty of this design is that any plugin can be created for any key manager. You can even run the command line and have the administrator enter the key to conduct tests. Although hardly anyone in the real world uses that strategy, it is still an option.

As stated: Provide a script or an executable that echoes the key to stdout. PostgreSQL will read it and use it to encrypt your data files. Having this script gives you complete freedom to work with any type of key management.

Here is an example:

 [tde@cybertec]$ ./initdb -D /path/db -K /path/key_manager.sh

User “hs” will be the owner of the database system’s files.

The server process must also belong to this user.

The database cluster’s initialization will include locales

COLLATE: en_US. en_US. UTF-8   CTYPE:    en_US. en_US. UTF-8   MESSAGES: en_US.UTF-8 MONETARY: de_DE UTF-8 NUMERIC: de_DE UTF-8 TIME:  de_DE. UTF-8

Accordingly, the default database encoding has been set to “UTF-8.”

English will be selected as the default text search configuration.

There are no data page checksums.

Data encryption is enabled.

creating the directory /path/db, ok creating subdirectories, ok selecting the dynamic shared memory implementation, ok posix: selecting default max_connections: 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/Vienna creating configuration files, ok running the 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:

 [tde@cybertec]$ ./pg_ctl -D /path/db -l logfile start

The-K option is really the path to success here. In the simplest of all cases, the script simply returns a constant:

 [tde@cybertec]$ cat /path/key_manager.sh  #!/bin/sh echo 4e5358ab309bcdea23450934546298ab

However, as was already mentioned, this is merely a showcase. Do not keep the key on the same server; rather, integrate it with a reliable key repository and manage the key remotely. Otherwise, TDE would be pointless and simply a waste of resources.

It’s crucial to keep in mind that the key must be 32 characters long and written in hexadecimal. Otherwise, an errorinitdb will occur.

 initdb: fatal: encryption key is too short, should be a 32 character hex key

There is bundled PG Keytool software for using secrets that people can memorize that accepts a passphrase as input and uses a key derivation function to create a properly formatted output key. This adds another layer of protection against dictionary attacks by using a key derivation function that takes a lot of time to run.

We can start PostgreSQL normally once the instance has been deployed.

Starting PostgreSQL

What initdb has done for us is to adjust the relevant values in postgresql.conf:

 # - Encryption - encryption_key_command = '/path/key_manager.sh'

The same program to retrieve the key is run every time the server boots up. Keep in mind that to decrypt those data files, we need this key. If the program does indeed return the correct key, the server has already taken precautions to ensure that nothing can go wrong. The server cannot be started if the key is not available. No back door, emergency key, or similar features exist. Your data is lost if you cannot provide the right key. The entire concept behind TDE is that. Backdoors are not what we want.

Ensuring encryption is enabled

How can I check to see if my PostgreSQL instance is encrypted, a common question? The way to do that is to control pg_controldata:

 [tde@cybertec]$ pg_controldata /path/db/ ... Database system identifier:           7036323720088499175 Database cluster state:               shut down pg_control last modified:             Di 30 Nov 2021 12:29:41 CET ... Mock authentication nonce: f12fd3308a81f946ffbb36a0a3bd4d41c87bbb9ba4d612b809a001a2202cdc6a Data encryption:                      on Data encryption fingerprint:          D2D2CED8FE9F3980599289B1F468A9FB

You can find the encryption information at the bottom of the listing. Make sure that you’re using the right version of pg_controldata. Otherwise, you won’t be able to find that information.

Popular keystores recommended for PostgreSQL TDE encryption keys

One of the main questions is: Which key store can one use for PostgreSQL? The answer is: any of them. Here are some that are quite popular and widely used by many companies. Be aware that this is not a comprehensive list.

• nitrokey.com
• YubiHSM
• HashiCorp Vault

In a typical production system, the database master key is made at random, encrypted with a key from a key management system, and then stored in the PostgreSQL data directory. This means that the whole database doesn’t have to be re-encrypted, and keys can be changed or even the key management system can be replaced.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​