Securing the data encryption key

The key for transparent data encryption (the data key) is normally generated by initdb and stored in a file pg_encryption/key.bin under the data directory. This file actually contains several keys that are used for different purposes at run time. However, in terms of the data key, it contains a single sequence of random bytes.

Without any further action, this file contains the key in plaintext, which isn't secure. Anyone with access to the encrypted data directory has access to the plaintext key, which defeats the purpose of encryption. Therefore, this setup is suitable only for testing purposes.

To secure the data key properly, “wrap” it by encrypting it with another key. Broadly, you can use two approaches to arrange this:

  • Protect the data key with a passphrase. A wrapping key is derived from the passphrase and used to encrypt the data key.

  • The wrapping key is stored elsewhere, for example, in a key management system, also known as a key store. This second key is also called the key-wrapping key or master key.

If you don't want key wrapping, for example for testing, then you must set the wrap and unwrap commands to the special value -. This setting specifies to use the key from the file without further processing. This approach differs from not setting a wrap or unwrap command at all, and from setting either/both to an empty string. Having no wrap or unwrap command set when transparent data encryption is used results in a fatal error when running an affected utility program.

Postgres leaves this configuration up to the user, which allows tailoring the setup to local requirements and integrating with existing key management software or similar. To configure the data key protection, you must specify a pair of external commands that take care of the wrapping (encrypting) and unwrapping (decryption).

Using a passphrase

You can protect the data key with a passphrase using the openssl command line utility. The following is an example that sets up this protection:

initdb -D datadir -y --key-wrap-command='openssl enc -e -aes-128-cbc -pbkdf2 -out "%p"' --key-unwrap-command='openssl enc -d -aes-128-cbc -pbkdf2 -in "%p"'

This example wraps the randomly generated data key (done internally by initdb) by encrypting it using the AES-128-CBC (AESKW) algorithm. The encryption uses a key derived from a passphrase using the PBKDF2 key derivation function and a randomly generated salt. The terminal prompts for the passphrase. (See the openssl-enc manual page for details about these options. Available options vary across versions.) The placeholder %p is replaced with the name of the file to store the wrapped key.

The unwrap command performs the opposite operation. initdb doesn't need the unwrap operation. However, it stores it in the postgresql.conf file of the initialized cluster, which uses it when it starts up.

The key wrap command receives the plaintext key on standard input and needs to put the wrapped key at the file system location specified by the %p placeholder. The key unwrap command needs to read the wrapped key from the file system location specified by the %p placeholder and write the unwrapped key to the standard output.

Utility programs like pg_rewind and pg_upgrade operate directly on the data directory or copies, such as backups. These programs also need to be told about the key unwrap command, depending on the circumstances. They each have command-line options for this purpose.

To simplify operations, you can also set the key wrap and unwrap commands in environment variables. These are accepted by all affected applications if you don't provide the corresponding command line options. For example:

PGDATAKEYWRAPCMD='openssl enc -e -aes-128-cbc -pbkdf2 -out "%p"'
PGDATAKEYUNWRAPCMD='openssl enc -d -aes-128-cbc -pbkdf2 -in "%p"'

Key unwrap commands that prompt for passwords on the terminal don't work when the server is started by pg_ctl or through service managers such as systemd. The server is detached from the terminal in those environments. If you want an interactive password prompt on server start, you need a more elaborate configuration that fetches the password using some indirect mechanism.

For example, for systemd, you can use systemd-ask-password:

PGDATAKEYWRAPCMD="bash -c 'openssl enc -e -aes-128-cbc -pbkdf2 -out %p -pass file:<(sudo systemd-ask-password --no-tty)'"
PGDATAKEYUNWRAPCMD="bash -c 'openssl enc -d -aes-128-cbc -pbkdf2 -in %p -pass file:<(sudo systemd-ask-password --no-tty)'"

You also need an entry like in /etc/sudoers:

postgres ALL = NOPASSWD: /usr/bin/systemd-ask-password

Using a key store

You can use the key store in an external key management system to manage the data encryption key. The tested and supported key stores are:

  • Amazon AWS Key Management Service (KMS)
  • Google Cloud - Cloud Key Management Service
  • HashiCorp Vault (KMIP Secrets Engine and Transit Secrets Engine)
  • Microsoft Azure Key Vault
  • Thales CipherTrust Manager

AWS Key Management Service example

Create a key with AWS Key Management Service:

aws kms create-key
aws kms create-alias --alias-name alias/pg-tde-master-1 --target-key-id "..."

Use the aws kms command with the alias/pg-tde-master-1 key to wrap and unwrap the data encryption key:

PGDATAKEYWRAPCMD='aws kms encrypt --key-id alias/pg-tde-master-1 --plaintext fileb:///dev/stdin --output text --query CiphertextBlob | base64 -d > "%p"'
PGDATAKEYUNWRAPCMD='aws kms decrypt --key-id alias/pg-tde-master-1 --ciphertext-blob fileb://"%p" --output text --query Plaintext | base64 -d'

Shell commands with pipes, as in this example, are problematic because the exit status of the pipe is that of the last command. A failure of the first, more interesting command isn't reported properly. Postgres handles this somewhat by recognizing whether the wrap or unwrap command wrote nothing. However, it's better to make this more robust. For example, use the pipefail option available in some shells or the mispipe command available on some operating systems. Put more complicated commands into an external shell script or other program instead of defining them inline.

Alternatively, you can use the crypt utility to wrap and unwrap the data encryption key:

PGDATAKEYWRAPCMD='crypt encrypt aws --out %p --region us-east-1 --kms alias/pg-tde-master-1'
PGDATAKEYUNWRAPCMD='crypt decrypt aws --in %p --region us-east-1'

Azure Key Vault example

Create a key with Azure Key Vault:

az keyvault key create --vault-name pg-tde --name pg-tde-master-1

Use the az keyvault command with the pg-tde-master-1 key to wrap and unwrap the data encryption key:

PGDATAKEYWRAPCMD='crypt encrypt azure --vaultURL --name pg-tde-master-1 --version fa2bf368449e432085318c5bf666754c --out %p'
PGDATAKEYUNWRAPCMD='crypt decrypt azure --vaultURL --name pg-tde-master-1 --version fa2bf368449e432085318c5bf666754c --in %p'

This example uses crypt. You can't use the Azure CLI directly for this purpose because it lacks some functionality.

Google Cloud KMS example

Create a key with Google Cloud KMS:

gcloud kms keys create pg-tde-master-1 --location=global --keyring=pg-tde --purpose=encryption

Use the az keyvault command with the pg-tde-master-1 key to wrap and unwrap the data encryption key:

PGDATAKEYWRAPCMD='gcloud kms encrypt --plaintext-file=- --ciphertext-file=%p --location=global --keyring=pg-tde --key=pg-tde-master-1'
PGDATAKEYUNWRAPCMD='gcloud kms decrypt --plaintext-file=- --ciphertext-file=%p --location=global --keyring=pg-tde --key=pg-tde-master-1'

Alternatively, you can use the crypt utility to wrap and unwrap the data encryption key:

PGDATAKEYWRAPCMD='crypt encrypt gcp --out=%p --location=global --keyring=pg-tde --key=pg-tde-master-1 --project your-project-123456'
PGDATAKEYUNWRAPCMD='crypt decrypt gcp --in=%p --location=global --keyring=pg-tde --key=pg-tde-master-1 --project your-project-123456'

HashiCorp Vault Transit Secrets Engine example

# enable once
vault secrets enable transit

# create a key (pick a name)
vault write -f transit/keys/pg-tde-master-1

PGDATAKEYWRAPCMD='base64 | vault write -field=ciphertext transit/encrypt/pg-tde-master-1 plaintext=- > %p'
PGDATAKEYUNWRAPCMD='vault write -field=plaintext transit/decrypt/pg-tde-master-1 ciphertext=- < %p | base64 -d'

Key rotation

To change the master key, manually run the unwrap command specifying the old key. Then feed the result into the wrap command specifying the new key. Equivalently, if the data key is protected by a passphrase, to change the passphrase, run the unwrap command using the old passphrase. Then feed the result into the wrap command using the new passphrase. You can perform these operations while the database server is running. The wrapped data key in the file is used only on startup. It isn't used while the server is running.

Building on the example in Using a passphrase, which uses openssl, to change the passphrase, you can:

cd $PGDATA/pg_encryption/
openssl enc -d -aes-128-cbc -pbkdf2 -in key.bin | openssl enc -e -aes-128-cbc -pbkdf2 -out
mv key.bin

With this method, the decryption and the encryption commands ask for the passphrase on the terminal at the same time, which is awkward and confusing. An alternative is:

cd $PGDATA/pg_encryption/
openssl enc -d -aes-128-cbc -pbkdf2 -in key.bin -pass pass:<replaceable>ACTUALPASSPHRASE</replaceable> | openssl enc -e -aes-128-cbc -pbkdf2 -out
mv key.bin

This technique leaks the old passphrase, which is being replaced anyway. openssl supports a number of other ways to supply the passphrases.

When using a key management system, you can connect the unwrap and wrap commands similarly, for example:

cd $PGDATA/pg_encryption/
crypt decrypt aws --in key.bin --region us-east-1 | crypt encrypt aws --out --region us-east-1 --kms alias/pg-tde-master-2
mv key.bin

You can't change the data key (the key wrapped by the master key) on an existing data directory. If you need to do that, you need to run the data directory through an upgrade process using pg_dump, pg_upgrade, or logical replication.