The tde_heap access method works on top of the default heap access method and is a marker to point which tables require encryption.

Data in buffers stored unencrypted. Encryption and decryption happens only when read/write request to disk occurs.

Thus, there is no overhead for internal operations.

You can change the default table access method so that every table in the entire database cluster is created using the custom access method. For example, you can enable data encryption by default by defining the tde_heap as the default table access method.

However, consider the following before making this change:

Here is how you can set the new default table access method:

SELECT pg_tde_add_global_key_provider_kmip(
    'provider-name',
    'kmip-IP',
    5696,
    '/path_to/server_certificate.pem',
    '/path_to/client_cert.pem',
    '/path_to/client_key.pem'
);

The following example is for testing purposes only.

SELECT pg_tde_add_global_key_provider_kmip(
    'kmip','127.0.0.1',
    5696,
    '/tmp/server_certificate.pem',
    '/tmp/client_cert_jane_doe.pem',
    '/tmp/client_key_jane_doe.pem'
);

For more information on related functions, see the link below:

Functions

SELECT pg_tde_add_global_key_provider_vault_v2(
    'provider-name',
    'secret_token_path',
    'url',
    'mount',
    'ca_path'
);

The following example is for testing purposes only. Use secure tokens and proper SSL validation in production environments:

SELECT pg_tde_add_global_key_provider_vault_v2(
    'my-vault',
    '/path/to/token_file',
    'http://vault.vault.svc.cluster.local:8200',
    'secret/data',
    NULL
);

For more information on related functions, see the link below:

Functions

Run the following command:

SELECT pg_tde_set_default_key_using_global_key_provider(
    'name-of-the-key',
    'provider-name',
    'ensure_new_key'
);

This example is for testing purposes only. Replace the key name and provider name with your values:

SELECT pg_tde_set_key_using_global_key_provider(
    'test-db-master-key',
    'file-vault',
    'ensure_new_key'
);

After this, all databases that do not have something else configured will use this newly generated principal key.

pg_tde uses two kinds of keys for encryption:

pg_tde uses one principal key per database. Every internal key for the given database is encrypted using this principal key.

Internal keys are used for specific database files: each file with a different Object Identifier (OID) has a different internal key.

This means that, for example, a table with 4 indexes will have at least 5 internal keys - one for the table, and one for each index.

If a table has additional associated relations, such as sequences or a TOAST table, those relations will also have separate keys.

For encrypting relations pg_tde uses the following encryption algorithms:

For encrypting WAL AES-CTR-256 is used.

For encrypting internal keys AES-GCM-256. It provides data authenticity (integrity) so it is impossible in practice to spoof keys.

pg_tde makes it possible to encrypt everything or only some tables in some databases.

To support this without metadata changes, encrypted tables are labeled with a tde_heap access method marker.

The tde_heap access method is the same as the heap one. It uses the same functions internally without any changes, but with the different name and ID. In such a way pg_tde knows that tde_heap tables are encrypted and heap tables are not.

The initial decision what to encrypt is made using the postgres event trigger mechanism: if a CREATE TABLE or ALTER TABLE statement uses the tde_heap clause, the newly created data files are marked as encrypted. Then file operations encrypt or decrypt the data.

Later decisions are made when a database file is re-created with a different relfilenode as a result of a TRUNCATE or a VACUUM FULL command, the newly created file inherits the encryption information and is either encrypted or not.

WAL encryption is controlled globally via a global GUC variable, pg_tde.wal_encrypt, that requires a server restart.

WAL keys also contain the LSN of the first WAL write after key creation. This allows pg_tde to know which WAL ranges are encrypted or not and with which key.

The setting only controls writes so that only WAL writes are encrypted when WAL encryption is enabled. This means that WAL files can contain both encrypted and unencrpyted data, depending on what the status of this variable was when writing the data.

pg_tde keeps track of the encryption status of WAL records using internal keys. When the server is restarted it writes a new internal key if WAL encryption is enabled, or if it is disabled and was previously enabled it writes a dummy key signalling that WAL encryption ended.

With this information the WAL reader code can decide if a specific WAL record has to be decrypted or not and which key it should use to decrypt it.

Currently pg_tde only encrypts heap tables and other files such as indexes, TOAST tables, sequences that are related to the heap tables.

You can rotate principal keys to comply with common policies and to handle situations with potentially exposed principal keys.

Rotation means that pg_tde generates a new version of the principal key, and re-encrypts the associated internal keys with the new key. The old principal key is kept as is at the same location, because it may still be needed to decrypt backups or other databases.

Internal keys for tables, indexes and other data files are fixed once a file is created. There’s no way to re-encrypt a file.

There are workarounds for this, because operations that move the table data to a new file, such as VACUUM FULL or an ALTER TABLE that rewrites the file will create a new key for the new file, essentially rotating the internal key. This however means taking an exclusive lock on the table for the duration of the operation, which might not be desirable for huge tables.

WAL internal keys are also fixed to the respective ranges. To generate a new WAL key you need to restart the database.

Internal keys and pg_tde metadata in general are kept in a single $PGDATA/pg_tde directory. This directory stores separate files for each database, such as:

Also, the $PGDATA/pg_tde directory has a special global section marked with the OID 1664, which includes the global key providers and global internal keys.

The global section is used for WAL encryption. Specific databases can use the global section too, for scenarios where users configure individual principal keys for databases but use the same global key provider. For this purpose, you must enable the global provider inheritance.

The global default principal key uses the special OID 1663.

Principal keys are stored externally in a Key Management Services (KMS). In pg_tdea KMS is defined as an external key provider.

The following key providers are supported:

For each key provider pg_tde requires a detailed configuration including the address of the service and the authentication information.

With these details pg_tde does the following based on user operations:

Retreival of the principal key is cached so it only happens when necessary.

Key provider configuration or location may change. For example, a service is moved to a new address or the principal key must be moved to a different key provider type. pg_tde supports both these scenarios enabling you to manage principal keys using simple SQL functions.

In certain cases you can’t use SQL functions to manage key providers. For example, if the key provider changed while the server wasn’t running and is therefore unaware of these changes. The startup can fail if it needs to access the encryption keys.

For such situations, pg_tde also provides command line tools to recover the database.

To use pg_tde, users are required to:

Keyring providers can be added to either the global or to the database specific scope.

If pg_tde.inherit_global_providers is on, global providers are visible for all databases, and can be used. If pg_tde.inherit_global_providers is off, global providers are only used for WAL encryption.

To add a global provider:

pg_tde_add_global_key_provider_<TYPE>('provider_name', ... details ...)

To add a database specific provider:

pg_tde_add_database_key_provider_<TYPE>('provider_name', ... details ...)

To change a value of a global provider:

pg_tde_change_global_key_provider_<TYPE>('provider_name', ... details ...)

To change a value of a database specific provider:

pg_tde_change_database_key_provider_<TYPE>('provider_name', ... details ...)

These functions also allow changing the type of a provider.

The functions however do not migrate any data. They are expected to be used during infrastructure migration, for example when the address of a server changes.

Note that in these functions do not verify the parameters. For that, see pg_tde_verify_key.

To change a provider from a command line, pg_tde provides the pg_tde_change_key_provider command line tool.

This tool work similarly to the above functions, with the following syntax:

pg_tde_change_key_provider <dbOid> <providerType> ... details ...

Note that since this tool is expected to be offline, it bypasses all permission checks!

This is also the reason why it requires a dbOid instead of a name, as it has no way to process the catalog and look up names.

Providers can be deleted by using the functions:

pg_tde_delete_database_key_provider(provider_name)
pg_tde_delete_global_key_provider(provider_name)

For database specific providers, the function first checks if the provider is used or not, and the provider is only deleted if it’s not used.

For global providers, the function checks if the provider is used anywhere, WAL or any specific database, and returns an error if it is.

This somewhat goes against the principle that pg_tde should not interact with other databases than the one the user is connected to, but on the other hand, it only does this lookup in the internal pg_tde metadata, not in postgres catalogs, so it is a gray zone. Making this check makes more sense than potentially making some databases inaccessible.

pg_tde provides 2 functions to show providers:

These functions return a list of provider names, type and configuration.

pg_tde implements access control based on execution rights on the administration functions.

For keys and providers administration, it provides two pair of functions:

pg_tde_(grant/revoke)_database_key_management_to_role

Principal keys can be created or rotated using the following functions:

pg_tde_set_key_using_(global/database)_key_provider('key-name', 'provider-name', ensure_new_key)
pg_tde_set_server_key_using_(global/database)_key_provider('key-name', 'provider-name', ensure_new_key)
pg_tde_set_default_key_using_(global/database)_key_provider('key-name', 'provider-name', ensure_new_key)

ensure_new_key is a boolean parameter defaulting to false. If it is true the function might return an error instead of setting the key if it already exists on the provider.

With pg_tde.inherit_global_key_providers, it is also possible to set up a default global principal key, which will be used by any database which has the pg_tde extension enabled, but doesn’t have a database specific principal key configured using pg_tde_set_key_using_(global/database)_key_provider.

With this feature, it is possible for the entire database server to easily use the same principal key for all databases, completely disabling multi-tenency.

A default key can be managed with the following function:

pg_tde_set_default_key('key-name', 'provider-name', ensure_new_key)

Changing the default principal key will rotate the encryption of internal keys for all databases using the current default principal key.

pg_tde_key_info() returns the name of the current principal key, and the provider it uses.

pg_tde_server_key_info() does the same for the server key.

pg_tde_default_key_info() does the same for the default key.

pg_tde_verify_key() checks that the key provider is accessible, that the current principal key can be downloaded from it, and that it is the same as the current key stored in memory - if any of these fail, it reports an appropriate error.

Users with management permissions to a specific database (pg_tde_(grant/revoke)_(global/databse)_key_management_to_role) can change the keys for the database, and use the current key functions. This includes creating keys using global providers, if pg_tde.inherit_global_providers is enabled.

Also the pg_tde_(grant/revoke)_database_key_management_to_role function deals with only the specific permission for the above function: it allows a user to change the key for the database, but not to modify the provider configuration.

To create an encrypted table or modify an existing table to be encrypted, simply use USING tde_heap in the CREATE statement.

It is possible for users to use pg_tde with inherit_global_keys = on, refer to global keys / keyrings in databases, and then change this setting to off.

In this case existing references to global providers, or the global default principal key will remain working as before, but new references to the global scope can’t be made.

Database users don’t need permissions to any of the encryption functions: encryption is managed by the admins, normal users only have to create tables with encryption, which requires no specific permissions.

To modify the key provider configuration, specify all parameters depending on the provider type in the same way as you do when using the pg_tde_change_(global/database)_key_provider_<type> SQL functions.

The general syntax is as follows:

pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> <new_provider_type> <provider_parameters...>

Depending on the provider type, the additional parameters are:

pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> file <filename>
pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> vault <token_path> <url> <mount_path> [<ca_path>]
pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> kmip <host> <port> <cert_path> <key_path> [<ca_path>]

Use these functions to grant or revoke permissions to manage the key of the current database. They enable or disable all functions related to the key of the current database:

Managment of the global scope is restricted to superusers only.

Use these functions to grant or revoke the use of query functions, which do not modify the encryption settings:

You can add a new key provider using the provided functions, which are implemented for each provider type.

There are two functions to add a key provider: one function adds it for the current database and another one - for the global scope.

When you add a new provider, the provider name must be unqiue in the scope. But a local database provider and a global provider can have the same name.

You can change an existing key provider using the provided functions, which are implemented for each provider type.

There are two functions to change existing providers: one to change a provider in the current database, and another one to change a provider in the global scope.

When you change a provider, the referred name must exist in the database local or a global scope.

The change functions require the same parameters as the add functions. They overwrite the setting for every parameter except for the name, which can’t be changed.

Provider specific parameters differ for each implementation. Refer to the respective subsection for details.

Some provider specific parameters contain sensitive information, such as passwords. Never specify these directly, use the remote configuration option instead.

SELECT pg_tde_add_database_key_provider_vault_v2(
  'provider-name',
  'secret_token_path',
  'url','mount',
  'ca_path'
);

SELECT pg_tde_add_global_key_provider_vault_v2(
  'provider-name',
  'secret_token_path',
  'url','mount',
  'ca_path'
);

SELECT pg_tde_change_database_key_provider_vault_v2(
  'provider-name',
  'secret_token_path',
  'url',
  'mount',
  'ca_path'
);

SELECT pg_tde_change_global_key_provider_vault_v2(
  'provider-name',
  'secret_token_path',
  'url',
  'mount',
  'ca_path'
);

SELECT pg_tde_add_database_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  'port',
  '/path_to/server_certificate.pem',
  '/path_to/client_cert.pem',
  '/path_to/client_key.pem'
);
SELECT pg_tde_add_global_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  'port',
  '/path_to/server_certificate.pem',
  '/path_to/client_certificate.pem',
  '/path_to/client_key.pem'
);

SELECT pg_tde_change_database_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  'port',
  '/path_to/server_certificate.pem',
  '/path_to/client_cert.pem',
  '/path_to/client_key.pem'
);
SELECT pg_tde_change_global_key_provider_kmip(
  'provider-name',
  'kmip-addr',
  'port',
  '/path_to/server_certificate.pem',
  '/path_to/client_certificate.pem',
  '/path_to/client_key.pem'
);

This provider manages database keys using a local keyfile.

This function is intended for development or quick testing, and stores the keys unencrypted in the specified data file.

Add a local keyfile provider:

SELECT pg_tde_add_database_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

SELECT pg_tde_add_global_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

Change a local keyfile provider:

SELECT pg_tde_change_database_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

SELECT pg_tde_change_global_key_provider_file(
  'provider-name',
  '/path/to/the/key/provider/data.file'
);

where:

These functions delete an existing provider in the current database or in the global scope:

You can only delete key providers that are not currently in use. An error is returned if the current principal key is using the provider you are trying to delete.

If the use of global key providers is enabled via the pg_tde.inherit_global GUC, you can delete a global key provider only if it isn’t used anywhere, including any databases. If it is used in any database, an error is returned instead.

These functions list the details of all key providers for the current database or for the global scope, including all configuration values:

Creates or rotates the principal key for the current database using the specified database key provider and key name.

SELECT pg_tde_set_key_using_database_key_provider(
  'name-of-the-key',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

Creates or rotates the global principal key using the specified global key provider and the key name. This key is used for global settings like WAL encryption.

SELECT pg_tde_set_key_using_global_key_provider(
  'name-of-the-key',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

Creates or rotates the server principal key using the specified global key provider. Use this function to set a principal key for WAL encryption.

SELECT pg_tde_set_server_key_using_global_key_provider(
  'name-of-the-key',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

Creates or rotates the default principal key for the server using the specified global key provider.

The default key is automatically used as a principal key by any database that doesn’t have an individual key provider and key configuration.

SELECT pg_tde_set_default_key_using_global_key_provider(
  'name-of-the-key',
  'provider-name',
  'ensure_new_key'
);

The ensure_new_key parameter instructs the function how to handle a principal key during key rotation:

Tells if a relation is encrypted using the pg_tde extension or not. Returns NULL if a relation lacks storage like views, foreign tables, and partitioned tables and indexes.

To verify that a table is encrypted, run the following statement:

SELECT pg_tde_is_encrypted(
  'table_name'
);

You can also verify if the table in a custom schema is encrypted. Pass the schema name for the function as follows:

SELECT pg_tde_is_encrypted(
  'schema.table_name'
);

This can additionally be used to verify that indexes and sequences are encrypted.

Tells which algorithm is used for relation encryption. Returns NULL if a relation is not encrypted.

To get encryption algorithm for table in current schema just run:

SELECT pg_tde_get_algorithm(
  'table_name'
);

This also works for relations in custom schema:

SELECT pg_tde_get_algorithm(
  'schema.table_name'
);

Note that encryption algorithm for indexes and TOASTs is equal to encryption algorithm that is used for parent relation.

Displays information about the principal key for the current database, if it exists.

SELECT pg_tde_key_info();

Displays information about the principal key for the server scope, if exists.

SELECT pg_tde_server_key_info();

Displays the information about the default principal key, if it exists.

SELECT pg_tde_default_key_info();

This function checks that the current database has a properly functional encryption setup, which means:

If any of the above checks fail, the function reports an error.

SELECT pg_tde_verify_key();

This function checks that the server scope has a properly functional encryption setup, which means:

If any of the above checks fail, the function reports an error.

SELECT pg_tde_verify_server_key();

This function checks that the default key is properly configured, which means:

If any of the above checks fail, the function reports an error.

SELECT pg_tde_verify_default_key();

If you encrypted a table with the tde_heap access method and need to remove the encryption from it, run the following command against the desired table (mytable in the example below):

ALTER TABLE mytable SET ACCESS METHOD heap;

Note that the SET ACCESS METHOD command drops hint bits and this may affect performance. Running a plain SELECT count(*) or VACUUM command on the entire table will check every tuple for visibility and set its hint bits. Therefore, after executing the ALTER TABLE command, run a simple count(*) on your tables:

SELECT count(*) FROM mytable;

Check that the table is not encrypted:

SELECT pg_tde_is_encrypted('mytable');

The output returns f meaning that the table is no longer encrypted.

This also can be implemented using pg_tde_get_algorithm function. Non-NULL output means that table is encrypted:

SELECT pg_tde_get_algorithm('mytable');

Alternatively, you can create a new not encrypted table with the same structure and data as the initial table. For example, the original encrypted table is EncryptedCustomers. Use the following command to create a new table Customers:

CREATE TABLE Customers AS
SELECT * FROM EncryptedCustomers;

The new table Customers inherits the structure and the data from EncryptedCustomers.

(Optional) If you no longer need the EncryptedCustomers table, you can delete it.

DROP TABLE EncryptedCustomers;

Data files are encrypted with internal keys. Each file that relates to encrypted table has it's own internal key. For example, a table with 4 indexes will have 5 internal keys - one for the table and one for each index.

The initial decision on what file to encrypt is based on the table access method in Tantor SE. When you run a CREATE or ALTER TABLE statement with the USING tde_heap clause, the newly created data files are marked as encrypted, and then file operations encrypt or decrypt the data. Later, if an initial file is re-created as a result of a TRUNCATE or VACUUM FULL command, the newly created file inherits the encryption information and is either encrypted (i.e. for TRUNCATE) or not (i.e. ALTER TABLE SET ACCESS METHOD heap).

The principal key is used to encrypt the internal keys. It is stored in the key management store. When you query the table, the principal key is retrieved from the key store to decrypt the table. Then the internal key for that table is used to decrypt the data.

Principal key is retrieved from key management store only once, when it first needed, then it is cached in memory.

WAL encryption is done globally for the entire database cluster. All modifications to any database within a Tantor SE cluster are written to the same WAL to maintain data consistency and integrity and ensure that Tantor SE cluster can be restored to a consistent state. Therefore, WAL is encrypted globally.

When you turn on WAL encryption, pg_tde encrypts entire WAL files starting from the first WAL write after the server was started with the encryption turned on.

The same 2-key approach is used with WAL as with the table data: WAL pages are first encrypted with the internal key. Then the internal key is encrypted with the global principal key.

You can turn WAL encryption on and off so WAL can contain both encrypted and unencrypted data. The WAL encryption GUC variable influences only writes.

Whenever the WAL is being read (by the recovery process or tools), the decision on what should be decrypted is based solely on the metadata of WAL encryption keys.