Chapter 32. Transparent Data Encryption

Cluster encryption can be used if the DBA cannot or does not want to rely on the filesystem in terms of data confidentiality. If this feature is enabled, PostgreSQL encrypts data (both relations and write-ahead log) when writing it to disk, and decrypts it when reading it. The encryption is transparent, so applications see no difference between encrypted and unencrypted clusters.

If you want to use this feature, please make sure that OpenSSL is installed on your server and that support in PostgreSQL is enabled at build time (see Chapter 16).

To create an encrypted cluster, call initdb with the option -K and the path to the command that will retrieve the encryption key. For example:

$ initdb -D /usr/local/pgsql/data -K /usr/local/pgsql/fetch_key_cmd

Here /usr/local/pgsql/fetch_key_cmd is an executable file that writes the encryption key to its standard output and returns zero.

The encryption key is expected in hexadecimal format, two characters (hexadecimal digits) per byte. Since PostgreSQL currently uses a key of length 16 bytes (128 bits), the expected length of the key string is 32 characters. For example: 882fb7c12e80280fd664c69d2d636913

initdb eventually puts the encryption key command into the postgresql.conf file, as the value of the encryption_key_command parameter. That ensures that you don't need to pass it to pg_ctl each time you want to start the server.

If you want to enter a password, you can use the pg_keytool utility to derive an encryption key from it. For example, if the -K option looks like the following example, the DBA will be asked for a password interactively:

$ initdb -K '(read -sp "Cluster encryption password: " PGENCRPWD; echo $PGENCRPWD | pg_keytool -D %D -w)' -D data

kdf_params file

If the cluster should be encrypted, initdb creates global/kdf_params file in the data directory. This file contains parameters of the key derivation function (KDF) and is needed to derive the encryption key from a password. Currently the only supported KDF is PBKDF2, so the parameters stored in the file are: 1) number of iterations and 2) salt. Since the salt is data directory specific, a different key will be derived from the same password for different data directories.

The kdf_params file contents is not secret, but if it gets lost, you can no longer derive the encryption key from the password. However but you can still use the encryption key if you have it stored somewhere.

As long as pg_keytool is called by initdb, you can put %D into the command and initdb will replace it with the actual directory path. Note, however, that only the actual value of the -K option is subject to this replacement. No replacement will take place if pg_keytool is called from a shell script whose path is passed to initdb via the -K option. In such a case, it may be easier to pass the data directory to pg_keytool via the PGDATA environment variable.

If the encryption_key_command setting is not present in postgresql.conf, the command can be passed to pg_ctl when starting the cluster:

$ pg_ctl -K '(read -sp "Cluster encryption password: " PGENCRPWD; echo $PGENCRPWD | pg_keytool -D %D -w)' -D data start


You need to use the -K option if the encryption key command needs to read the password from the console, like it does in the example above. In such a case, please comment out the encryption_key_command parameter in postgresql.conf.

Once the PostgreSQL server is running, client applications should recognize no difference to an unencrypted cluster, except that the data_encryption variable is set.

Since WAL is encrypted, any replication solution based on log shipping (Section 26.2) assumes that all standby servers are encrypted using the same key as their primary server. On the other hand, logical replication (see Chapter 30) allows replication between encrypted and unencrypted clusters, or between clusters encrypted with different keys.


Key rotation is currently not supported. If you need it, you can use the key management system of your choice and rotate the corresponding key encryption key (KEK) instead.