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 with the AES-128-CBC (AESKW) algorithm. The encryption uses a key derived from a passphrase with 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 initdb utility replaces %p with the name of the file that stores 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"'
export PGDATAKEYWRAPCMD PGDATAKEYUNWRAPCMD

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)
  • Microsoft Azure Key Vault
  • Google Cloud - Cloud Key Management Service
  • HashiCorp Vault (KMIP Secrets Engine and Transit Secrets Engine)
  • Thales CipherTrust Manager
  • Fortanix Data Security Manager

To use one of the available key stores, see the configuration examples.

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'
Note

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.

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 key command with the pg-tde-master-1 key to wrap and unwrap the data encryption key:

PGDATAKEYWRAPCMD='az keyvault key encrypt --name pg-tde-master-1 --vault-name pg-tde --algorithm A256GCM --value @- --data-type plaintext --only-show-errors --output json | jq -r .result > "%p"'
PGDATAKEYUNWRAPCMD='az keyvault key decrypt --name pg-tde-master-1 --vault-name pg-tde --algorithm A256GCM --value @"%p" --data-type plaintext --only-show-errors --output json | jq -r .result'
Note

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.

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 gcloud kms 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'

HashiCorp Vault Transit Secrets Engine example

Enable transit with HashiCorp Vault Transit Secrets Engine:

vault secrets enable transit

Create a key and give it a name:

vault write -f transit/keys/pg-tde-master-1

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

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'

Fortanix Data Security Manager example

See Using Fortanix Data Security Manager with EDB Postgres for TDE for a step-by-step configuration tutorial.

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 key.bin.new
mv key.bin.new 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 key.bin.new
mv key.bin.new 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 key.bin.new --region us-east-1 --kms alias/pg-tde-master-2
mv key.bin.new key.bin
Note

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.