Enabling TDE

Suggest edits

You enable transparent data encryption when you initialize a database cluster using initdb.

Using initdb TDE options

To enable encryption, use the following options with the initdb command or their fallback environment variables:

-y, --data-encryption

Initialize the new database cluster with transparent data encryption. See Transparent Data Encryption for more information. Optionally specify an AES key length. Valid values are 128 and 256. The default is 128.

--copy-key-from=<file>

Copy the data encryption key from the given location. You can use this option to copy a key from an existing cluster when preparing a new cluster as a target for pg_upgrade.

--key-wrap-command=<command>

Specify a command to wrap (encrypt) the generated data encryption key. The command must include a placeholder %p that specifies the file to write the wrapped key to. The unwrapped key is provided to the command on its standard input. If you don't specify this option, the environment variable PGDATAKEYWRAPCMD is used.

Use the special value - if you don't want to apply any key wrapping command.

You must specify this option or the environment variable fallback if you're using data encryption. See Securing the data encryption key for more information.

--key-unwrap-command=<command>

Specify a command to unwrap (decrypt) the data encryption key. The command must include a placeholder %p that specifies the file to read the wrapped key from. The command needs to write the unwrapped key to its standard output. If you don't specify this option, the environment variable PGDATAKEYUNWRAPCMD is used.

Use the special value - if you don't want to apply any key unwrapping command.

You must specify this option or the environment variable fallback if you're using data encryption. See Securing the data encryption key for more information.

--no-key-wrap

Disable key wrapping. The data encryption key is instead stored in plaintext in the data directory. (This option is a shortcut for setting both the wrap and the unwrap command to the special value -.)

Note

Using this option isn't secure. Use it only for testing purposes.

If you select data encryption and don't specify this option, then you must provide key wrap and unwrap commands. Otherwise, initdb terminates with an error.

Using environment variables

To simplify operations, you can set the key wrap and unwrap commands in the environment variables.

For example:

PGDATAKEYWRAPCMD='openssl enc -e -aes128-wrap -pbkdf2 -out "%p"'
PGDATAKEYUNWRAPCMD='openssl enc -d -aes128-wrap -pbkdf2 -in "%p"'
export PGDATAKEYWRAPCMD PGDATAKEYUNWRAPCMD

Setting the key parameter in postgresql.conf

When you enable TDE for a cluster, the initdb command initializes the data_encryption_key_unwrap_command parameter in the postgresql.conf configuration file. The string specified in data_encryption_key_unwrap_command unwraps (decrypts) the data encryption key.

The command must contain a placeholder %p, which is replaced with the name of the file containing the key to unwrap. The command must print the unwrapped (decrypted) key to its standard output.

If you don't specify this parameter, the environment variable PGDATAKEYUNWRAPCMD is used.

Use the special value - if you don't want to apply any key unwrapping command.

You must specify this parameter or the environment variable fallback if you're using data encryption. See Securing the data encryption key for more information.

You can set this parameter only at server start.

This parameter is normally initialized by initdb. Change it only if you change the key wrap method.

For more information on the configuration files, see PostgreSQL File Locations documentation.

Example

This example uses EDB Postgres Advanced Server 15 running on a Linux platform. It uses openssl to define the passkey to wrap and unwrap the generated data encryption key.

  1. Set the data encryption key (wrap) and decryption (unwrap) environment variables:

    export PGDATAKEYWRAPCMD='openssl enc -e -aes-128-cbc -pass pass:ok -out %p'
export PGDATAKEYUNWRAPCMD='openssl enc -d -aes-128-cbc -pass pass:ok -in %p'
    Note

    If you are on Windows you don't need the single quotes around the variable value.

  2. Initialize the cluster using initdb with encryption enabled. This command sets the data_encryption_key_unwrap_command parameter in the postgresql.conf file.

    /usr/edb/as15/bin/initdb --data-encryption -D /var/lib/edb/as15/data

  3. Start the cluster:

    /usr/edb/as15/bin/pg_ctl -D /var/lib/edb/as15/data start

  4. Run grep on postgresql.conf to see the setting of data_encryption_key_unwrap_command: 

    grep data_encryption_key_unwrap_command /var/lib/edb/as15/data/postgresql.conf
    Output
    data_encryption_key_unwrap_command = 'openssl enc -d -aes-128-cbc -pass pass:ok -in %p'

Checking for TDE presence using SQL

You can find out whether TDE is present on a server by querying the data_encryption_version column of the pg_control_init table.

A value of 0 means TDE isn't enabled. Any nonzero value reflects the version of TDE in use. Currently, when TDE is enabled, this value is 1.

# select data_encryption_version from pg_control_init();
 data_encryption_version
-------------------------
                       1
(1 row)