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 17).

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. For the default key length of 128 bits (16 bytes), the expected length of the key string is 32 characters. For example: 882fb7c12e80280fd664c69d2d636913 Likewise, for the key lengths 192 and 256 bits, the key string length should be 48 and 64 characters respectively. Please note that non-default key length has to be specified explicitly, using the --key-bits option. For example:

$ initdb -D /usr/local/pgsql/data -K /usr/local/pgsql/fetch_key_cmd --key-bits=192

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)' -D data

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.

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 27.2) assumes that all standby servers are encrypted using the same key as their primary server. On the other hand, logical replication (see Chapter 31) allows replication between encrypted and unencrypted clusters, or between clusters encrypted with different keys.