F.63. pg_tde — Transparent Data Encryption for Tantor SE — Tantor Special Edition 17.9 documentation

F.63. pg_tde — Transparent Data Encryption for Tantor SE
Prev	Up	Appendix F. Additional Supplied Modules and Extensions	Home	Next

F.63.1. Overview #

pg_tde is an extension that provides Transparent Data Encryption (TDE) to protect data at rest. pg_tde ensures that the data stored on disk is encrypted, and that no one can read it without the proper encryption keys, even if they gain access to the physical storage media.

F.63.2. Quickstart #

F.63.2.1. Installation #

You must include the extension in shared_preload_libraries before running CREATE EXTENSION, or it will fail:

shared_preload_libraries = 'pg_tde'

If GOST algorithms are needed, install the required modules. You can build from source or install via packages.

On Astra Linux, the libgost-astra package configures OpenSSL automatically.

After installation, restart Tantor SE and run:

CREATE EXTENSION pg_tde;

F.63.2.2. Creating Encrypted Tables #

A principal key is required to generate an internal key, which performs the actual encryption. For security, principal keys are stored externally and used on demand.

Currently supported key providers:

File-based
HashiCorp Vault
KMIP protocol

Example using a file-based provider:

SELECT pg_tde_add_database_key_provider_file('file-provider', '/tmp/test-file-provider');

Create a principal key:

SELECT pg_tde_set_key_using_database_key_provider('test-key', 'file-provider');

Create an encrypted table:

CREATE TABLE enc_tbl(x int) USING tde_heap;

Check if a table is encrypted:

SELECT pg_tde_is_encrypted('enc_tbl');

To specify the encryption algorithm, use a storage parameter:

CREATE TABLE enc_tbl(x int) USING tde_heap WITH (encryptor='magma');

Check the algorithm used:

SELECT pg_tde_get_algorithm('enc_tbl');

Available algorithms:

AES (default)
ChaCha20
Magma (GOST)
Kuznyechik (GOST)

The last two require additional modules.

Example:

-- Register a file-based key provider
SELECT pg_tde_add_database_key_provider_file('file-provider', '/tmp/test-file-provider');
-- Create a principal key
SELECT pg_tde_set_key_using_database_key_provider('test-key', 'file-provider');
-- Create encrypted table
CREATE TABLE enc_tbl(x int) USING tde_heap;

F.63.2.3. WAL Encryption #

Steps are similar to table encryption.

First, create a global key provider (used specifically for WAL):

SELECT pg_tde_add_global_key_provider_file('global-provider', '/tmp/test-wal-file-provider');

Then, create a principal key for WAL:

SELECT pg_tde_set_server_key_using_global_key_provider('wal-key', 'global-provider');

Enable WAL encryption:

ALTER SYSTEM SET pg_tde.wal_encrypt = on;

Restart the database server.

Caution

When WAL encryption is enabled, a new internal key is generated on every startup to defend against specific attack types.

All steps:

SELECT pg_tde_add_global_key_provider_file('global-provider', '/tmp/test-wal-file-provider');
SELECT pg_tde_set_server_key_using_global_key_provider('wal-key', 'global-provider');
ALTER SYSTEM SET pg_tde.wal_encrypt = on;
-- Restart Tantor SE

F.63.2.4. Encrypting Existing Tables #

If you have an unencrypted table, you can encrypt it via:

Changing the access method:
```
ALTER TABLE sample_tbl SET ACCESS METHOD tde_heap;
```
This uses AES by default; storage options cannot be specified.

Creating a new encrypted table and migrating data:

BEGIN;
CREATE TABLE temp_tbl(x int) USING tde_heap;
LOCK tbl;
INSERT INTO temp_tbl SELECT * FROM tbl;
DROP TABLE tbl;
ALTER TABLE temp_tbl RENAME TO tbl;
COMMIT;

You can specify the encryption algorithm in this case, but indexes must be recreated manually.

F.63.2.5. Encrypting All Tables #

To encrypt all newly created tables by default, set:

ALTER SYSTEM SET default_table_access_method = tde_heap;

Note

AES will be used unless overridden via WITH clause.

F.63.2.6. Key Rotation #

Currently, only principal key rotation is supported. Internal key rotation is not yet implemented.

Rotation simply involves assigning a new principal key:

SELECT pg_tde_set_key_using_database_key_provider('new-key');

Existing internal keys will be re-encrypted; table data remains unchanged.

Example:

SELECT pg_tde_add_database_key_provider_file('file-provider', '/tmp/test-file-provider');
SELECT pg_tde_set_key_using_database_key_provider('test-key-1', 'file-provider');
CREATE TABLE enc_tbl(x int) USING tde_heap;
INSERT INTO enc_tbl(x) VALUES (1), (2), (3), (4), (5);

-- Rotate the principal key
SELECT pg_tde_set_key_using_database_key_provider('test-key-2');

F.63.2.7. Replication #

Physical replication is fully supported. WAL records related to TDE are replayed on the replica.

Note

For GOST encryption, ensure required modules are installed on the replica.

F.63.2.8. OpenSSL Configuration #

On startup, the extension loads an OpenSSL configuration file. Use pg_tde.openssl_config to specify a custom path. If unset, the system default is used (e.g., /etc/ssl/openssl.cnf on Astra Linux).

If the libgost-astra package is installed, it configures this automatically.

Note

In our distribution, GOST modules (gost.so, gostprov.so) are located at /opt/tantor/db/{VERSION}/lib.

The pg_tde.preinit_algs parameter specifies algorithms to preload at startup for performance and validation.

Format: comma-separated list (like shared_preload_libraries). Supported: aes, chacha20, kuznyechik, magma.

Example:

pg_tde.preinit_algs = 'aes,chacha20,kuznyechik'

F.63.3. About Transparent Data Encryption #

Transparent Data Encryption (TDE) protects your data at rest by ensuring that even if the underlying storage is compromised, the data remains unreadable without the correct decryption keys. This is especially critical for environments handling sensitive, regulated, or high-value information.

This process runs transparently in the background, with minimal impact on database operations.

F.63.3.1. Benefits of pg_tde #

The benefits of using pg_tde are outlined below for different users and organizations.

Data safety: Prevents unauthorized access to stored data, even if backup files or storage devices are stolen or leaked.
Granular control: Encrypt specific tables or databases instead of the entire system, reducing performance overhead.
Operational simplicity: Works transparently without requiring major application changes.
Defense in depth: Adds another layer of protection to existing controls like TLS (encryption in transit), access control, and role-based permissions.

When combined with the external Key Management Systems (KMS), pg_tde enables centralized control, auditing, and rotation of encryption keys—critical for secure production environments.

F.63.3.2. How pg_tde works #

To encrypt the data, two types of keys are used:

Internal encryption keys to encrypt user data. They are stored internally, near the data that they encrypt.
The principal key to encrypt database keys. It is kept separately from the database keys and is managed externally in the key management store.

Note

For more information on managing and storing principal keys externally, including supported key management systems and the local keyring option, see Key management overview.

When a user creates an encrypted table using pg_tde, a new random key is generated internally for that table and is encrypted using the user specified algorithm. This key is used to encrypt all data the user inserts in that table. Eventually the encrypted data gets stored in the underlying storage.

The internal key itself is encrypted using the principal key. The principal key is stored externally in the key management store.

Similarly when the user queries the encrypted table, the principal key is retrieved from the key store to decrypt the internal key. Then the same unique internal key for that table is used to decrypt the data, and unencrypted data gets returned to the user. So, effectively, every TDE table has a unique key, and each table key is encrypted using the principal key.

F.63.3.3. Encrypted data scope #

pg_tde encrypts the following components:

User data in tables using the extension, including associated TOAST data. The table metadata (column names, data types, etc.) is not encrypted.
Temporary tables created during the query execution, for data tables created using the extension.
Write-Ahead Log (WAL) data for the entire database cluster. This includes WAL data in encrypted and non-encrypted tables.
Indexes on encrypted tables.

F.63.3.4. Table Access Methods and pg_tde #

A table access method is the way how Tantor SE stores the data in a table. The default table access method is heap. Tantor SE organizes data in a heap structure, meaning there is no particular order to the rows in the table. Each row is stored independently and identified by its unique row identifier (TID).

F.63.3.4.1. How the heap access method works #

Insertion: When a new row is inserted, Tantor SE finds a free space in the tablespace and stores the row there.

Deletion: When a row is deleted, Tantor SE marks the space occupied by the row as free, but the data remains until it is overwritten by a new insertion.

Updates: Tantor SE handles updates by deleting the old row and inserting a new row with the updated values.

F.63.3.4.2. Custom access method #

Custom access methods allow you to implement and define your own way of organizing data in Tantor SE. This is useful if the default table access method doesn’t meet your needs.

Custom access methods are typically available with Tantor SE extensions. When you install an extension and enable it in Tantor SE, a custom access method is created.

An example of such an approach is the tde_heap access method. It is automatically created only for the databases where you Configure pg_tde and configured the key provider, enabling you to encrypt the data.

To use a custom access method, specify the USING clause for the CREATE TABLE command:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
) USING tde_heap;

F.63.3.4.2.1. How tde_heap works with pg_tde #

The tde_heap access method works on top of the default heap access method and is a marker to point which tables require encryption. It uses the custom storage manager TDE SMGR, which becomes active only after you installed the pg_tde extension.

Every data modification operation is first sent to the Buffer Manager, which updates the buffer cache. Then, it is passed to the storage manager, which then writes it to disk. When a table requires encryption, the data is sent to the TDE storage manager, where it is encrypted before written to disk.

Similarly, when a client queries the database, the Tantor SE core sends the request to the Buffer Manager which checks if the requested data is already in the buffer cache. If it’s not there, the Buffer Manager requests the data from the storage manager. The TDE storage manager reads the encrypted data from disk, decrypts it and loads it to the buffer cache. The Buffer Manager sends the requested data to the Tantor SE core and then to the client.

Thus, the encryption is done at the storage manager level.

F.63.3.4.3. Changing the default table access method #

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:

This is a global setting and applies across the entire database cluster and not just a single database. We recommend setting it with caution because all tables and materialized views created without an explicit access method in their CREATE statement will default to the specified table access method.
You must create the pg_tde extension and configure the key provider for all databases before you modify the configuration. Otherwise Tantor SE won’t find the specified access method and will throw an error.

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

Add the access method to the default_table_access_method parameter:
via the ALTER SYSTEM command
Use ALTER SYSTEM, it requires superuser or ALTER SYSTEM privileges.
```
 ALTER SYSTEM SET default_table_access_method = tde_heap;
```
via the configuration file
Edit the postgresql.conf configuration file and add the value for the default_table_access_method parameter.
```
 default_table_access_method = 'tde_heap'
```
via the SET command
You can use the SET command to change the default table access method temporarily, for the current session.
Unlike modifying the postgresql.conf file or using the ALTER SYSTEM command, the changes you make via the SET command don't persist after the session ends.
You also don't need to have the superuser privileges to run the SET command.
You can run the SET command anytime during the session.
```
 SET default_table_access_method = tde_heap;
```
Reload the configuration to apply the changes:
```
SELECT pg_reload_conf();
```

F.63.3.5. Limitations of pg_tde #

Limitations of pg_tde

Tantor SE’s internal system tables, which include statistics and metadata, are not encrypted.
Temporary files created when queries exceed work_mem are not encrypted. These files may persist during long-running queries or after a server crash which can expose sensitive data in plaintext on disk.

F.63.3.5.1. Currently unsupported WAL tools #

The following tools are currently unsupported with pg_tde WAL encryption:

pg_createsubscriber
pg_receivewal
pg_verifybackup by default fails with checksum or WAL key size mismatch errors. As a workaround, use -s (skip checksum) and -n (--no-parse-wal) to verify backups.

The following tools and extensions have been tested and verified to work with pg_tde WAL encryption:

F.63.3.5.2. Supported WAL tools #

The following tools have been tested to work with pg_tde WAL encryption:

pg_tde_basebackup (with --wal-method=stream or --wal-method=none), for details on using pg_basebackup with WAL encryption, see Backup with WAL encryption enabled
pg_tde_resetwal
pg_tde_rewind
pg_tde_waldump
pg_upgrade

F.63.4. Features #

The following features are available for the extension:

Table encryption including:
- Data tables
- Index data for encrypted tables
- TOAST tables
- Temporary tables
Note
Metadata of those tables is not encrypted.

Global and per-db KMS providers
Table-level granularity for encryption and access control
Different encryption algorithms
Multiple Key management overview
WAL encryption support

F.63.5. Encryption algorithms #

pg_tde supports different table encryption algorithms:

AES
ChaCha-20
Magma
Kuznyechik

Extension uses openssl, so if it supports algorithm, then it is supported by the extension.

You can specify algorithm using encryptor storage parameter during creation of table. It accepts following values:

aes for AES
chacha20 for ChaCha-20
magma for Magma
kuznyechik for Kuznyechik

Example:

CREATE TABLE users(
    name text
) USING tde_heap WITH (encryptor='kuznyechik');

If algorithm is not specified, value from pg_tde.default_encryptor GUC is used. By default it’s value is aes, so AES is used by default.

Note

Some algorithms may be unavailable, so you can not use them. At the server startup extension will try to initialize all available algorithms and if some are not supported by openssl then WARNING will be written to log.

F.63.6. Configure pg_tde #

Before you can use pg_tde for data encryption, you must enable the extension and configure Tantor SE to load it at startup. This setup ensures that the necessary hooks and shared memory are available for encryption operations.

The pg_tde extension requires additional shared memory. You need to configure Tantor SE to preload it at startup.

Note

(Optional) Install GOST algorithms

pg_tde supports different encryption algorithms including GOST: Magma and Kuznyechik.

If GOST algorithms are needed (magma or kuznyechik), install the required OpenSSL modules. You can build from source or install via packages.

There is list of known packages for different distributions that contain GOST module for OpenSSL:

libgost-astra - Astra Linux
openssl-gost-engine - AltLinux
libengine-gost-openssl or libengine-gost-openssl1.1 - Debian/Ubuntu
openssl-gost-engine - RedOS

For other distributions you have to build it from source code. Project source code is located in GitHub repository.

When GOST modules installed you might need to update OpenSSL configuration. Some packages (i.e. libgost-astra) do it automatically. For others you might have to update configuration manually.

Configure shared_preload_libraries
You can configure the shared_preload_libraries parameter in two ways:
- Add the following line to the postgresql.conf file:
```
shared_preload_libraries = 'pg_tde'
```
- Use the ALTER SYSTEM command. Run the following command in psql as a superuser:
```
ALTER SYSTEM SET shared_preload_libraries = 'pg_tde';
```
Restart the Tantor SE cluster
Restart the postgresql cluster to apply the configuration.
```
sudo systemctl restart tantor-CONF_EDITION_SHORT_NAME__-server-CONF_PG_MAJOR_VERSION__
```
Create the extension
After restarting Tantor SE, connect to psql as a superuser or database owner and run:
```
CREATE EXTENSION pg_tde;
```
See CREATE EXTENSION for more details.
Note
The pg_tde extension is created only for the current database. To enable it for other databases, you must run the command in each individual database.
(Optional) Enable pg_tde by default
To automatically have pg_tde enabled for all new databases, modify the template1 database:
```
psql -d template1 -c 'CREATE EXTENSION pg_tde;'
```
Note
It’s recommended to use an external key provider (KMS) to manage encryption keys.

F.63.7. Key management overview #

In production environments, storing encryption keys locally on the Tantor SE server can introduce security risks. To enhance security, pg_tde supports integration with external Key Management Systems (KMS) through a Global Key Provider interface.

This section describes how you can configure pg_tde to use the local and external key providers. To use an external KMS with pg_tde, follow these two steps:

Configure a Key Provider
Set the Global Principal Key

Note

While key files may be acceptable for local or testing environments, KMS integration is the recommended approach for production deployments.

Warning

Do not rotate encryption keys while pg_basebackup is running. Standbys or standalone clusters created from such backups may fail to start during WAL replay. Schedule rotations outside your backup windows and run a new full backup afterward.

pg_tde has been tested with the following key providers:

KMS Provider	Description	Documentation
KMIP	Standard Key Management Interoperability Protocol.	Configure KMIP
Vault	HashiCorp Vault integration (KV v2 API, KMIP engine).	Configure Vault
Fortanix	Fortanix DSM key management.	Configure Fortanix
Thales	Thales CipherTrust Manager and DSM.	Configure Thales
OpenBao	Community fork of Vault, supporting KV v2.	Configure OpenBao
Keyring file (not recommended)	Local key file for dev/test only.	Configure keyring file

F.63.7.1. KMIP configuration #

To use a Key Management Interoperability Protocol (KMIP) server with pg_tde, you must configure it as a global key provider. This setup enables pg_tde to securely fetch and manage encryption keys from a centralized key management appliance.

Note

You need the root certificate of the KMIP server and a client key/certificate pair with permissions to create and read keys on the server.

It is recommended to review the configuration guidelines for the HashiCorp Vault Enterprise KMIP Secrets Engine if you’re using Vault.

For testing purposes, you can use a lightweight PyKMIP server, which enables easy certificate generation and basic KMIP behavior. If you’re using a production-grade KMIP server, ensure you obtain valid, trusted certificates from the key management appliance.

F.63.7.1.1. Example usage #

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

F.63.7.1.2. Parameter descriptions #

provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider
kmip-IP is the IP address of a domain name of the KMIP server
port is the port to communicate with the KMIP server. Typically used port is 5696
server-certificate is the path to the certificate file for the KMIP server
client_cert is the path to the client certificate.
client_key is the path to the client key.

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:

pg_tde function reference

F.63.7.2. Fortanix KMIP server configuration #

pg_tde is compatible with Fortanix Data Security Manager (DSM) via the KMIP protocol. For a full setup guide, see the Fortanix KMIP documentation here.

For more information on adding or modifying the provider, see the Add or modify KMIP providers topic.

F.63.7.3. Vault configuration #

You can configure pg_tde to use HashiCorp Vault as a global key provider for managing encryption keys securely. Both the open source and enterprise editions are supported.

Note

This guide assumes that your Vault server is already set up and accessible. Vault configuration is outside the scope of this document, see Vault’s official documentation for more information.

F.63.7.3.1. Example usage #

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

F.63.7.3.2. Parameter descriptions #

provider-name is the name to identify this key provider
secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
url is the URL of the Vault server
mount is the mount point where the keyring should store the keys
[optional] ca_path is the path of the CA file used for SSL verification

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',
    'https://vault.vault.svc.cluster.local:8200',
    'secret/data',
    '/path/to/token_file',
    '/path/to/ca_cert.pem'
);

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

pg_tde function reference

F.63.7.3.3. Required permissions #

pg_tde requires given permissions on listed Vault’s API endpoints:

sys/mounts/<mount> - read permissions
<mount>/data/* - create, read permissions
<mount>/metadata - list permissions

Note

For more information on Vault permissions, see the following documentation.

F.63.7.4. Thales KMIP server configuration #

pg_tde is compatible with the Thales CipherTrust Manager via the KMIP protocol. For a full setup guide, see the Thales documentation.

For more information on adding or modifying the provider, see the Add or modify KMIP providers topic.

F.63.7.5. Using OpenBao as a key provider #

You can configure pg_tde to use OpenBao as a global key provider for managing encryption keys securely.

Note

This guide assumes that your OpenBao server is already set up and accessible. OpenBao configuration is outside the scope of this document, see OpenBao’s official documentation for more information.

F.63.7.5.1. Example usage #

To register an OpenBao server as a global key provider:

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

F.63.7.5.2. Parameter descriptions #

provider-name is the name to identify this key provider
secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
url is the URL of the Vault server
mount is the mount point where the keyring should store the keys
[optional] ca_path is the path of the CA file used for SSL verification

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-openbao-provider',
    'https://openbao.example.com:8200',
    'secret/data',
    '/path/to/token_file',
    '/path/to/ca_cert.pem'
);

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

pg_tde function reference

F.63.7.6. Keyring file configuration #

This setup is intended for development and stores the keys unencrypted in the specified data file.

Note

While keyfiles may be acceptable for local or testing environments, KMS integration is the recommended approach for production deployments.

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

The following example is used for testing purposes only:

SELECT pg_tde_add_global_key_provider_file(
    'file-keyring',
    '/tmp/pg_tde_test_local_keyring.per'
);

F.63.8. Global Principal Key configuration #

You can configure a default principal key using a global key provider. This key is used by all databases that do not have their own encryption keys configured.

There are two main functions for this:

pg_tde_create_key_using_global_key_provider() creates a principal key at a global key provider
pg_tde_set_default_key_using_global_key_provider() sets the default principal key and rotates the internal encryption key if one is already configured

F.63.8.1. Create a default principal key #

Note

The sample output below is for demonstration purposes only. Be sure to replace the key name and provider with your actual values.

To create a global principal key, run:

SELECT pg_tde_create_key_using_global_key_provider(
    'key-name',
    'global_vault_provider'
);

Example:

postgres=# SELECT pg_tde_create_key_using_global_key_provider(
    'keytest1',
    'file-keyring'
);
 pg_tde_create_key_using_global_key_provider
---------------------------------------------

 (1 row)

F.63.8.2. Configure a default principal key #

To configure a global principal key, run:

SELECT pg_tde_set_default_key_using_global_key_provider(
    'key-name',
    'global_vault_provider'
);

Example:

postgres=# SELECT pg_tde_set_default_key_using_global_key_provider(
    'keytest1',
    'file-keyring'
);
 pg_tde_set_default_key_using_global_key_provider 
--------------------------------------------------

 (1 row)

F.63.8.3. Parameter description #

key-name is the name under which the principal key is stored in the provider.
global_vault_provider is the name of the global key provider you previously configured.

Note

If no error is reported, the action completed successfully.

F.63.8.4. How key generation works #

The key material (actual cryptographic key) is auto-generated by pg_tde and stored securely by the configured provider.

Note

This process sets the default principal key for the entire server. Any database without a key explicitly configured will fall back to this key.

F.63.9. pg_tde usage examples #

After enabling the pg_tde extension for a database, you can begin encrypting data using the tde_heap table access method.

F.63.9.1. Encrypt data in a new table #

Create a table in the database for which you have Configure pg_tde using the tde_heap access method as follows:
```
CREATE TABLE <table_name> (<field> <datatype>) USING tde_heap;
```
Warning
Example for testing purposes only.
```
CREATE TABLE albums (
    album_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    artist_id INTEGER,
    title TEXT NOT NULL,
    released DATE NOT NULL
) USING tde_heap;
```
Learn more about table access methods and how you can enable data encryption by default in the Table Access Methods and TDE section.
By default AES is used for encryption. If you want another algorithm, then you can specify it using encryptor storage parameter:
```
CREATE TABLE albums (
    album_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    artist_id INTEGER,
    title TEXT NOT NULL,
    released DATE NOT NULL
) USING tde_heap WITH (encryptor='chacha20');
```
For more information about supported algorithms refer to Supported algorithms page.
To check if the data is encrypted, run the following function:
```
SELECT pg_tde_is_encrypted('table_name');
```
The function returns true or false.
To get encryption algorithm use the following function:
```
SELECT pg_tde_get_algorithm('table_name');
```
(Optional) Rotate the principal key.

To re-encrypt the data using a new key, see Principal key management.

F.63.9.2. Encrypt existing table #

You can encrypt an existing table. It requires rewriting the table, so for large tables, it might take a considerable amount of time.

Run the following command:

ALTER TABLE table_name SET ACCESS METHOD tde_heap;

Caution

Using SET ACCESS METHOD drops hint bits which can impact query performance. To restore performance, run:

SELECT count(*) FROM table_name;

This forces Tantor SE to check every tuple for visibility and reset the hint bits.

Tip

Want to remove encryption later? See how to decrypt your data.

F.63.10. Configure WAL encryption #

Before enabling WAL encryption, follow the steps below to create a principal key and configure it for WAL:

Create the pg_tde extension if it does not exist:
```
CREATE EXTENSION IF NOT EXISTS pg_tde;
```
Set up the key provider for WAL encryption:
With KMIP server
Make sure you have obtained the root certificate for the KMIP server and the keypair for the client. The client key needs permissions to create / read keys on the server. Find the configuration guidelines for the HashiCorp Vault Enterprise KMIP Secrets Engine.
For testing purposes, you can use the PyKMIP server which enables you to set up required certificates. To use a real KMIP server, make sure to obtain the valid certificates issued by the key management appliance.
```
SELECT pg_tde_add_global_key_provider_kmip(
    'provider-name', 
    'kmip-addr', 
    5696, 
    '/path_to/client_cert.pem', 
    '/path_to/client_key.pem', 
    '/path_to/server_certificate.pem'
);
```
where:
- provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider.
- kmip-addr is the IP address of a domain name of the KMIP server
- port is the port to communicate with the KMIP server. Typically used port is 5696.
- server-certificate is the path to the certificate file for the KMIP server.
- client-cert is the path to the client certificate.
- client-key is the path to the client key.
Warning
This example is for testing purposes only.
```
SELECT pg_tde_add_key_using_global_key_provider_kmip(
    'kmip', 
    '127.0.0.1', 
    5696, 
    '/tmp/client_cert_jane_doe.pem', 
    '/tmp/client_key_jane_doe.pem', 
    '/tmp/server_certificate.pem'
);
```
With HashiCorp Vault
```
SELECT pg_tde_add_global_key_provider_vault_v2(
    'provider-name', 
    'url', 
    'mount', 
    'secret_token_path', 
    'ca_path'
);
```
where:
- provider-name is the name you define for the key provider
- url is the URL of the Vault server
- mount is the mount point where the keyring should store the keys
- secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
- [optional] ca_path is the path of the CA file used for SSL verification
With keyring file
This setup is not recommended, as it is intended for development.
Warning
The keys are stored unencrypted in the specified data file.
```
SELECT pg_tde_add_global_key_provider_file(
    'provider-name', 
    '/path/to/the/keyring/data.file'
);
```

Create principal key

SELECT pg_tde_set_server_key_using_global_key_provider(
    'key', 
    'provider-name'
);

Enable WAL level encryption using the ALTER SYSTEM command. You need the privileges of the superuser to run this command:
```
ALTER SYSTEM SET pg_tde.wal_encrypt = on;
```

Restart the server to apply the changes.

sudo systemctl restart tantor-CONF_EDITION_SHORT_NAME__-server-CONF_PG_MAJOR_VERSION__

Now WAL files start to be encrypted for both encrypted and unencrypted tables.

F.63.11. Technical Reference #

This section covers the internal components and tools that power pg_tde.

Use it to understand how encryption is implemented, fine-tune a configuration, leverage advanced CLI tools and functions for diagnostics and customization.

F.63.12. Architecture #

pg_tde is a data at rest encryption extension for Tantor SE.

Data at rest means everything written to the disk. This includes the following:

Table data files
Indexes
Sequences
Temporary tables
Write Ahead Log (WAL)

F.63.12.1. Main components #

The main components of pg_tde are the following:

Core server changes focus on making the server more extensible, allowing the main logic of pg_tde to remain separate, as an extension. Core changes also add encryption-awareness to some command line tools that have to work directly with encrypted tables or encrypted WAL files - these are implemented as separate versions of vanilla binaries, thus if you do not use encryption, then there is no overhead for operations.
The pg_tde extension itself implements the encryption code by hooking into the extension points introduced in the core changes, and the already existing extension points in the Tantor SE server. Everything is controllable with GUC variables and SQL statements, similar to other extensions.
The keyring API / libraries implement the key storage logic with different key providers. The API is internal only, the keyring libraries are part of the main library for simplicity. In the future these could be extracted into separate shared libraries with an open API, allowing the use of third-party providers.

F.63.12.2. Encryption architecture #

F.63.12.2.1. Two-key hierarchy #

pg_tde uses two kinds of keys for encryption:

Internal keys to encrypt the data. They are stored in Tantor SE’s data directory under $PGDATA/pg_tde.
Higher-level keys to encrypt internal keys. These keys are called principal keys. They are stored externally, in a Key Management System (KMS) using the key provider API.

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.

F.63.12.2.2. Encryption algorithm #

For encrypting tables pg_tde uses the following algorithms:

AES-128-CBC
ChaCha20
Magma
Kuznyechik

For WAL encryption it uses AES-128-CTR.

For encrypting internal keys AES-128-GCM is used.

F.63.12.2.3. Encryption workflow #

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 using a slightly modified Storage Manager (SMGR) API: when a database file is re-created with a different ID 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.

F.63.12.2.4. WAL encryption #

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 unencrypted 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 signaling 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.

F.63.12.3. Key and key provider management #

F.63.12.3.1. Principal key rotation #

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.

F.63.12.3.2. Internal key regeneration #

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.

F.63.12.3.3. Internal key storage #

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:

Encrypted internal keys and internal key mapping to tables
Information about the key providers

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.

F.63.12.3.4. Key providers (principal key storage) #

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:

HashiCorp Vault KV2 secrets engine
OpenBao implementation of Vault
KMIP compatible servers
A local file storage. This storage is intended only for development and testing and is not recommended for production use.

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:

Uploads a new principal key to it after this key is created
Retrieves the principal key from the service when it is required for decryption

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

F.63.12.3.5. Key provider management #

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.

F.63.12.3.6. Sensitive key provider information #

Caution

Authentication details for key providers are sensitive and must be protected. Do not store these credentials in the $PGDATA directory alongside the database. Instead, ensure they are stored in a secure location with strict file system permissions to prevent unauthorized access.

F.63.12.4. User interface #

F.63.12.4.1. Set up pg_tde #

To get started with pg_tde, follow these steps:

Add pg_tde to the shared_preload_libraries in postgresql.conf as this is required for the SMGR extensions
Execute CREATE EXTENSION pg_tde in the databases where they want to use encryption
Optionally, enable pg_tde.wal_encrypt in postgresql.conf
Optionally, disable pg_tde.inherit_global_providers in postgresql.conf (it is enabled by default)

F.63.12.4.2. Add providers #

You can add keyring providers to either the global or 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 ...)

F.63.12.4.3. Change providers #

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 but do not migrate any data. They are expected to be used during infrastructure migration, for example when the address of a server changes.

F.63.12.4.4. Change providers from the command line #

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

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.

This tool does not validate any parameters.

F.63.12.4.5. Delete providers #

Providers can be deleted by using the following 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.

F.63.12.4.6. List/query providers #

pg_tde provides 2 functions to show providers:

pg_tde_list_all_database_key_providers()
pg_tde_list_all_global_key_providers()

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

F.63.12.4.7. Provider permissions #

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_database_key_management_TO_role
pg_tde_REVOKE_database_key_management_FROM_role

F.63.12.4.8. Create and rotate keys #

Principal keys can be created using the following functions:

pg_tde_create_key_using_(global/database)_key_provider('key-name', 'provider-name')

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

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

F.63.12.4.9. Default principal key #

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-tenancy.

F.63.12.4.9.1. Manage a default key #

You can manage a default key with the following functions:

pg_tde_create_key_using_global_key_provider('key-name','provider-name')
pg_tde_set_default_key_using_global_key_provider('key-name','provider-name')
pg_tde_delete_default_key()

Note

pg_tde_delete_default_key() is only possible if there’s no database currently using the default principal key. Changing the default principal key will rotate the encryption of internal keys for all databases using the current default principal key.

F.63.12.4.9.2. Delete a key #

The pg_tde_delete_key() function unsets the principal key for the current database. If the current database has any encrypted tables, and there isn’t a default principal key configured, it reports an error instead. If there are encrypted tables, but there’s also a default principal key, internal keys will be encrypted with the default key.

Note

WAL keys cannot be unset, as server keys are managed separately.

F.63.12.4.10. Current key details #

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.

F.63.12.4.11. Key permissions #

Users with management permissions to a specific database (pg_tde_(grant/revoke)_(global/databse)_key_management_(to/from)_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.

F.63.12.4.12. Create encrypted tables #

To create an encrypted table or modify an existing table to be encrypted, use the following commands:

CREATE TABLE t1(a INT) USING tde_heap;
ALTER TABLE t1 SET ACCESS METHOD tde_heap;

F.63.12.4.13. Change the pg_tde.inherit_global_keys setting #

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

In this case, existing references to global providers or the global default principal key keep working as before, but new references to the global scope cannot be made.

F.63.13. GUC Variables #

The pg_tde extension provides GUC variables to configure the behaviour of the extension:

F.63.13.1. pg_tde.wal_encrypt #

Type - boolean

Default - off

A boolean variable that controls if WAL writes are encrypted or not.

Changing this variable requires a server restart, and can only be set at the server level.

WAL encryption is controlled globally. If enabled, all WAL writes are encrypted in the entire Tantor SE cluster.

This variable only controls new writes to the WAL, it doesn’t affect existing WAL records.

pg_tde is always capable of reading existing encrypted WAL records, as long as the keys used for the encryption are still available.

Enabling WAL encryption requires a configured global principal key. Refer to the WAL encryption configuration topic for more information.

Warning

The WAL encryption feature is currently in beta and is not effective unless explicitly enabled. It is not yet production ready. Do not enable this feature in production environments.

F.63.13.2. pg_tde.enforce_encryption #

Type - boolean

Default - off

A boolean variable that controls if the creation of new, not encrypted tables is allowed.

If enabled, CREATE TABLE statements will fail unless they use the tde_heap access method.

Similarly, ALTER TABLE <x> SET ACCESS METHOD is only allowed, if the access method is tde_heap.

Other DDL operations are still allowed. For example other ALTER commands are allowed on unencrypted tables, as long as the access method isn’t changed.

You can set this variable at the following levels:

global - for the entire Tantor SE cluster
database - for specific databases
user - for specific users
session - for the current session

Setting or changing the value requires superuser permissions. For examples, see the Encryption Enforcement topic.

F.63.13.3. pg_tde.inherit_global_providers #

Type - boolean

Default - on

A boolean variable that controls if databases can use global key providers for storing principal keys.

If disabled, functions that change the key providers can only work with database local key providers.

In this case, the default principal key, if set, is also disabled.

You can set this variable at the following levels:

global - for the entire Tantor SE cluster
database - for specific databases
user - for specific users
session - for the current session

Note

Setting this variable doesn’t affect existing uses of global keys. It only prevents the creation of new principal keys using global providers.

F.63.13.4. pg_tde.default_encryptor #

Type - string

Default - 'aes'

A string variable that specifies which encryption algorithm is used by default.

It is used when encryptor storage parameter is not used during CREATE TABLE command.

Another use case is changing encryption algorithm for already encrypted table. Set this setting to preferred algorithm and execute ALTER TABLE … SET ACCESS METHOD tde_heap.

You can set this setting to the value of any supported algorithm:

aes - AES-CBC 256-bit.
chacha20 - ChaCha20.
magma - GOST 28147-89 Magma.
kuznyechik - GOST R 34.12-2015 Kuznyechik.

This parameter is local to user and can be changed without admin priviledges or restarting database.

F.63.14. Functions #

The pg_tde extension provides functions for managing different aspects of its operation:

Note

If no error is reported when running the commands below, the operation completed successfully.

F.63.14.1. Key provider management #

A key provider is a system or service responsible for managing encryption keys. pg_tde supports the following key providers:

local file (not recommended for production use)
HashiCorp Vault / OpenBao
KMIP compatible providers

Key provider management includes the following operations:

creating a new key provider,
changing an existing key provider,
deleting a key provider,
listing key providers.

F.63.14.1.1. Add a key provider #

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.

pg_tde_add_database_key_provider_<type>('provider-name', <provider specific parameters>)
pg_tde_add_global_key_provider_<type>('provider-name', <provider specific parameters>)

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

F.63.14.1.2. Change an existing provider #

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.

pg_tde_change_database_key_provider_<type>('provider-name', <provider specific parameters>)
pg_tde_change_global_key_provider_<type>('provider-name', <provider specific parameters>)

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.

Note

The updated provider must be able to retrieve the same principal keys as the original configuration. If the new configuration cannot access existing keys, encrypted data and backups will become unreadable.

F.63.14.1.2.1. Add or modify Vault providers #

The Vault provider connects to a HashiCorp Vault or an OpenBao server, and stores the keys on a key-value store version 2.

Use the following functions to add the Vault provider:

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

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

These functions change the Vault provider:

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

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

where:

provider-name is the name of the key provider
url is the URL of the Vault server
mount is the mount point on the Vault server where the key provider should store the keys
secret_token_path is a path to the file that contains an access token with read and write access to the above mount point
[optional] ca_path is the path of the CA file used for SSL verification

F.63.14.1.2.2. Add or modify KMIP providers #

The KMIP provider uses a remote KMIP server.

Use these functions to add a KMIP provider:

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

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

These functions change the KMIP provider:

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

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

where:

provider-name is the name of the provider
kmip-addr is the IP address or domain name of the KMIP server
port is the port to communicate with the KMIP server. Most KMIP servers use port 5696.
server-certificate is the path to the certificate file for the KMIP server.
client-certificate is the path to the client certificate.
client-key is the path to the client key.

Note

The specified access parameters require permission to read and write keys at the server.

F.63.14.1.3. Add or modify local key file providers #

This provider manages database keys using a local key file.

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

Caution

Local key file providers are not recommended for production environments, they lack the security and manageability of external key management systems.

Add a local key file 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 key file 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:

provider-name is the name of the provider. You can specify any name, it’s for you to identify the provider.
/path/to/the/key/provider/data.file is the path to the key provider file.

F.63.14.1.4. Delete a provider #

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

pg_tde_delete_database_key_provider('provider-name')
pg_tde_delete_global_key_provider('provider-name')

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.

F.63.14.1.5. List key providers #

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

pg_tde_list_all_database_key_providers()
pg_tde_list_all_global_key_providers()

F.63.14.2. Principal key management #

Use these functions to create a new principal key at a given keyprover, and to use those keys for a specific scope such as a current database, a global or default scope. You can also use them to start using a different existing key for a specific scope.

Principal keys are stored on key providers by the name specified in this function - for example, when using the Vault provider, after creating a key named “foo”, a key named “foo” will be visible on the Vault server at the specified mount point.

F.63.14.2.1. pg_tde_create_key_using_database_key_provider #

Creates a principal key using the database-local key provider with the specified name. Use this key later with pg_tde_set_key_using_database_key_provider().

SELECT pg_tde_create_key_using_database_key_provider(
  'key-name',
  'provider-name'
);

F.63.14.2.2. pg_tde_create_key_using_global_key_provider #

Creates a principal key at a global key provider with the given name. Use this key later with the pg_tde_set_* series of functions.

SELECT pg_tde_create_key_using_global_key_provider(
  'key-name',
  'provider-name'
);

F.63.14.2.3. pg_tde_set_key_using_database_key_provider #

Sets the principal key for the current database, using the specified local key provider. It also rotates internal encryption keys to use the specified principal key.

This function is typically used when working with per-database encryption through a local key provider.

SELECT pg_tde_set_key_using_database_key_provider(
  'key-name',
  'provider-name'
);

F.63.14.2.4. pg_tde_set_key_using_global_key_provider #

Sets 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(
  'key-name',
  'provider-name'
);

F.63.14.2.5. pg_tde_set_server_key_using_global_key_provider #

Sets 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(
  'key-name',
  'provider-name'
);

F.63.14.2.6. pg_tde_set_default_key_using_global_key_provider #

Sets 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(
  'key-name',
  'provider-name'
);

F.63.14.2.7. pg_tde_delete_key #

Unsets the principal key for the current database. If the current database has any encrypted tables, and there isn’t a default principal key configured, it reports an error instead. If there are encrypted tables, but there’s also a default principal key, internal keys will be encrypted with the default key.

SELECT pg_tde_delete_key();

F.63.14.2.8. pg_tde_delete_default_key #

Unsets default principal key. It’s possible only if no database uses default principal key.

SELECT pg_tde_delete_default_key();

F.63.14.3. Encryption status check #

F.63.14.3.1. pg_tde_is_encrypted #

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.

F.63.14.3.2. pg_tde_get_algorithm #

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

SELECT pg_tde_get_algorithm(
    'table_name'
);

F.63.14.3.3. pg_tde_key_info #

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

SELECT pg_tde_key_info();

F.63.14.3.4. pg_tde_server_key_info #

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

SELECT pg_tde_server_key_info();

F.63.14.3.5. pg_tde_default_key_info #

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

SELECT pg_tde_default_key_info();

F.63.14.3.6. pg_tde_verify_key #

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

A key provider is configured
The key provider is accessible using the specified configuration
There is a principal key for the database
The principal key can be retrieved from the remote key provider
The principal key returned from the key provider is the same as cached in the server memory

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

SELECT pg_tde_verify_key();

F.63.14.3.7. pg_tde_verify_server_key #

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

A key provider is configured
The key provider is accessible using the specified configuration
There is a principal key for the global scope
The principal key can be retrieved from the remote key provider
The principal key returned from the key provider is the same as cached in the server memory

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

SELECT pg_tde_verify_server_key();

F.63.14.3.8. pg_tde_verify_default_key #

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

A key provider is configured
The key provider is accessible using the specified configuration
There is a principal key that can be used for any scope
The principal key can be retrieved from the remote key provider
The principal key returned from the key provider is the same as cached in the server memory

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

SELECT pg_tde_verify_default_key();

F.63.15. Streaming Replication with tde_heap #

This section outlines how to set up Tantor SE streaming replication when the pg_tde extension, specifically the tde_heap access method, is enabled on the primary server.

Before you begin, ensure you have followed the Configure pg_tde.

Note

You do not need to run CREATE EXTENSION on the standby. It will be replicated automatically.

F.63.15.1. Configure the Primary #

F.63.15.1.1. Create a principal key #

Use the pg_tde_set_server_key_using_global_key_provider function to create a principal key.

F.63.15.1.2. Create the replication role #

Create a replication role on the primary:

CREATE ROLE example_replicator WITH REPLICATION LOGIN PASSWORD 'example_password';

F.63.15.1.3. Configure pg_hba.conf #

To allow the replica to connect to the primary server, add the following line in pg_hba.conf:

host  replication  example_replicator  standby_ip/32  scram-sha-256

Ensure that it is placed before the other host rules for replication and then reload the configuration:

SELECT pg_reload_conf();

F.63.15.2. Configure the Standby #

F.63.15.2.1. Perform a database backup #

Run the base backup from your standby machine to pull the encrypted base backup:

export PGPASSWORD='example_password'
pg_tde_basebackup \
  -h primary_ip \
  -D /var/lib/pgsql/data \
  -U example_replicator \
  --wal-method=stream \
  --slot=tde_slot \
  -C \
  -c fast \
  -v -P

F.63.15.2.2. Configure postgresql.conf #

After the base backup completes, add the following line to the standby’s postgresql.conf file:

shared_preload_libraries = 'pg_tde'

F.63.15.3. Start and validate replication #

Assuming that the primary and the standby are running on separate hosts, start the Tantor SE service:

sudo systemctl start tantor-CONF_EDITION_SHORT_NAME__-server-CONF_PG_MAJOR_VERSION__

Warning

Key management consistency required for replication

If you're using a KMS provider, such as Vault or KMIP, make sure that both the primary and the standby have access to the same key management configuration, and that the paths to the configuration files are identical on both systems.

For example:

If you configure Vault with a secret path: /path/to/secret.file, then that file must exist at the same path on both the primary and the standby.
If you use the keyring_file provider, be aware that it stores key material in a local file and it is not designed for shared or concurrent use across multiple servers. It is not recommended in replication setups.

On primary:

SELECT client_addr, state FROM pg_stat_replication;

On standby:

SELECT
    pg_is_in_recovery()          AS in_recovery,
    pg_last_wal_receive_lsn()    AS receive_lsn,
    pg_last_wal_replay_lsn()     AS replay_lsn;

Tip

Want to verify that everything is working? After creating an encrypted table on the primary, run the following command on the standby to confirm that the encryption is active and the keys are resolved:

SELECT pg_tde_is_encrypted('your_encrypted_table');

F.63.16. Overview of pg_tde CLI tools #

The pg_tde extension introduces new command-line utilities and extends some existing Tantor SE tools to support encrypted WAL and tables.

F.63.16.1. New tools #

These tools are introduced by pg_tde to support key rotation and WAL encryption workflows:

pg_tde_change_key_provider: change the encryption key provider for a database
pg_tde_archive_decrypt: decrypts WAL before archiving
pg_tde_restore_encrypt: a custom restore command for making sure the restored WAL is encrypted

F.63.16.2. Extended tools #

These existing Tantor SE tools are enhanced to support pg_tde:

pg_tde_checksums: verify data checksums (non-encrypted files only)
pg_tde_waldump: inspect and decrypt WAL files
pg_tde_basebackup: creates DB backup for encrypted database
pg_tde_resetwal: pg_resetwal with encrypted WAL support
pg_tde_rewind: pg_rewind with encrypted WAL support

F.63.16.3. pg_tde_change_key_provider #

A tool for modifying the configuration of a key provider, possibly also changing its type.

This tool edits the configuration files directly, ignoring permissions or running postgres processes.

Its only intended use is to fix servers that can’t start up because of inaccessible key providers.

For example, you restore from an old backup and the address of the key provider changed in the meantime. You can use this tool to correct the configuration, allowing the server to start up.

Warning

Use this tool only when the server is offline. To modify the key provider configuration when the server is up and running, use the pg_tde_change_(global/database)_key_provider_<type> SQL functions.

F.63.16.3.1. Example usage #

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

F.63.16.3.2. Parameter description #

[optional] <datadir> is the data directory.pg_tde uses the $PGDATA environment variable if this is not specified
<provider_name> is the name you assigned to the key provider
<new_provider_type> can be a file, vault or kmip
<dbOid>

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-v2 <url> <mount_path> <token_path> [<ca_path>]
pg_tde_change_key_provider [-D <datadir>] <dbOid> <provider_name> kmip <host> <port> <cert_path> <key_path> [<ca_path>]

F.63.16.4. pg_tde_archive_decrypt #

The pg_tde_archive_decrypt tool wraps an archive command and decrypts WAL files before archiving. It allows external tools to access unencrypted WAL data, which is required because WAL encryption keys in the two-key hierarchy are host-specific and may not be available on the replay host.

Tip

For more information on the encryption architecture and key hierarchy, see Architecture.

This tool is often used in conjunction with pg_tde_restore_encrypt to support WAL archive.

F.63.16.4.1. How it works #

Decrypts the WAL segment to a temporary file on a RAM disk (/dev/shm)
Replaces %p and %f in the archive command with the path and name of the decrypted file
Executes the archive command

Note

To ensure security, encrypt the files stored in your WAL archive using tools like PgBackRest.

F.63.16.4.2. Usage #

pg_tde_archive_decrypt [OPTION]
pg_tde_archive_decrypt DEST-NAME SOURCE-PATH ARCHIVE-COMMAND

F.63.16.4.3. Parameter descriptions #

DEST-NAME: name of the WAL file to send to the archive
SOURCE-PATH: path to the original encrypted WAL file
ARCHIVE-COMMAND: archive command to wrap. %p and %f are replaced with the decrypted WAL file path and WAL file name, respectively.

F.63.16.4.4. Options #

-V, --version: show version information, then exit
-?, --help: show help information, then exit

Note

Any %f or %p parameter in ARCHIVE-COMMAND has to be escaped as %%f or %%p respectively if used as archive_command in postgresql.conf.

F.63.16.4.5. Examples #

F.63.16.4.5.1. Using `cp` #

archive_command='pg_tde_archive_decrypt %f %p "cp %%p /mnt/server/archivedir/%%f"'

F.63.16.4.5.2. Using `PgBackRest` #

archive_command='pg_tde_archive_decrypt %f %p "pgbackrest --stanza=your_stanza archive-push %%p"'

Warning

When using PgBackRest with WAL encryption, disable Tantor SE data checksums. Otherwise, PgBackRest may spam error messages, and in some package builds the log statement can cause crashes.

F.63.16.5. pg_tde_restore_encrypt #

The pg_tde_restore_encrypt tool wraps a normal restore command from the WAL archive and writes them to disk in a format compatible with pg_tde.

Note

This command is often use together with pg_tde_archive_decrypt.

F.63.16.5.1. How it works #

Replaces %f and %p in the restore command with the WAL file name and temporary file path (in /dev/shm)
Runs the restore command to fetch the unencrypted WAL from the archive and write it to the temp file
Encrypts the temp file and writes it to the destination path in Tantor SE’s data directory

F.63.16.5.2. Usage #

pg_tde_restore_encrypt [OPTION]
pg_tde_restore_encrypt SOURCE-NAME DEST-PATH RESTORE-COMMAND

F.63.16.5.3. Parameter descriptions #

SOURCE-NAME: name of the WAL file to retrieve from the archive
DEST-PATH: path where the encrypted WAL file should be written
RESTORE-COMMAND: restore command to wrap; %p and %f are replaced with the WAL file name and path to write the unencrypted WAL, respectively

F.63.16.5.4. Options #

-V, --version: show version information, then exit
-?, --help: show help information, then exit

Note

Any %f or %p parameter in RESTORE-COMMAND has to be escaped as %%f or %%p respectively if used as restore_command in postgresql.conf.

F.63.16.5.5. Examples #

F.63.16.5.5.1. Using `cp` #

restore_command='pg_tde_restore_encrypt %f %p "cp /mnt/server/archivedir/%%f %%p"'

F.63.16.5.5.2. Using `PgBackRest` #

restore_command='pg_tde_restore_encrypt %f %p "pgbackrest --stanza=your_stanza archive-get %%f \"%%p\""'

Warning

When using PgBackRest with WAL encryption, disable Tantor SE data checksums. Otherwise, PgBackRest may spam error messages, and in some package builds the log statement can cause crashes.

F.63.16.6. pg_checksums #

pg_checksums is a Tantor SE command-line utility used to enable, disable, or verify data checksums on a Tantor SE data directory. However, it cannot calculate checksums for encrypted files.

Encrypted files are skipped, and this is reported in the output.

Note

Ensure you have enabled page checksums before installing pg_tde

F.63.16.7. pg_tde_waldump #

pg_tde_waldump is a tool to display a human-readable rendering of the Write-Ahead Log (WAL) of a Tantor SE database cluster.

To read encrypted WAL records, pg_tde_waldump supports the following additional arguments:

keyring_path is the directory where the keyring configuration files for WAL are stored. The following files are included:
- 1664_keys
- 1664_providers

Note

pg_tde_waldump does not decrypt WAL unless the keyring_path is set.

F.63.16.8. pg_tde_basebackup #

pg_tde_basebackup is a tool to create backup of database cluster.

To read encrypted WAL records, pg_tde_basebackup supports the following additional arguments:

encrypt-wal flag indicates that the tool must encrypt streamed WAL. For this purpose you must have WAL encrypted key already setup.

F.63.16.9. pg_tde_resetwal #

pg_tde_resetwal is a tool to reset the write-ahead log and other control information of a Tantor SE database cluster.

The tools will look for $PGDATA/pg_tde directory to get WAL encryption keys.

F.63.16.10. pg_tde_rewind #

pg_tde_rewind is a tool to synchronize database data directory with another data directory that was forked from it.

The tools will look for $PGDATA/pg_tde directory to get WAL encryption keys.

F.63.17. How to #

F.63.17.1. Encryption Enforcement #

For pg_tde, encryption enforcement ensures that only encrypted storage is allowed for specific operations, tables, or the entire database. It prevents the accidental creation of unencrypted tables or indexes in environments where encryption is required for compliance, security, or policy enforcement.

F.63.17.1.1. What does enforcement do? #

When enabled, encryption enforcement:

Prevents creation of unencrypted tables or indexes
Enforces consistent encryption usage across tenants, databases, or users
Can be scoped globally, per database, or per role

F.63.17.1.2. Enforce encryption usage #

Use the following techniques to enforce the secure use of pg_tde.

F.63.17.1.2.1. Enforce encryption across the server #

To enforce encryption cluster-wide, set the pg_tde.enforce_encryption variable in postgresql.conf:

pg_tde.enforce_encryption = on

Note

Only superusers can set or change this variable.

This ensures that no user, including superusers, can create unencrypted tables. Superusers can however explicitly override the variable in their session.

F.63.17.1.2.2. Enforce encryption for a specific database #

To apply encryption enforcement only within a specific database, run:

ALTER DATABASE example_db SET pg_tde.enforce_encryption = on;

This ensures encryption is enforced only when connected to that database.

F.63.17.1.2.3. Enforce encryption for a specific user #

You can also enforce encryption on a per-user basis, run:

ALTER USER example_user SET pg_tde.enforce_encryption = on;

This ensures that the user example_user cannot create unencrypted tables, regardless of which database they connect to.

F.63.17.1.2.4. Override enforcement for trusted sessions #

Superusers can override the variable at the session level:

SET pg_tde.enforce_encryption = off;

This allows temporary creation of unencrypted tables in special cases, such as:

Loading trusted, public reference datasets
Benchmarking and test environments
Migration staging before re-encryption

Note

While superusers can disable enforcement in their session, they must do so explicitly. Enforcement defaults remain active to protect from accidental misconfiguration.

F.63.17.2. Remove Encryption from an Encrypted Table #

F.63.17.2.1. Method 1. Change the access method #

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.

F.63.17.2.2. Method 2. Create a new not encrypted table on the base of the encrypted one #

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;

F.63.17.3. Backup with WAL encryption enabled #

Note

If your database cluster uses encryption, then you must use pg_tde_basebackup tool instead of pg_basebackup.

To create a backup with WAL encryption enabled:

Copy the pg_tde directory from the source server’s data directory, for example /var/lib/postgresql/data/pg_tde/, including the wal_keys and 1664_providers files, to the backup destination directory where pg_tde_basebackup will write the backup.

Also copy any external files referenced by your providers configuration (such as certificate or key files) into the same relative paths under the backup destination, so that they are located and validated by pg_tde_basebackup -E.

Run:
```
pg_tde_basebackup -D /path/to/backup -E
```
Where:
- -D /path/to/backup specifies the backup location where you have to copy pg_tde.
- -E (or --encrypt-wal) enables WAL encryption and validates that the copied pg_tde and provider files are present and that the server key is accessible (required).

Note

The -E flag only works with the -X stream option (default). It is not compatible with -X none or -X fetch.
The -E flag is only supported with the plain output format (-F p). It cannot be used with the tar output format (-F t).

F.63.17.3.1. Key rotation during backups #

Warning

Do not create, change, or rotate global key providers (or their keys) while pg_tde_basebackup is running. Standbys or standalone clusters created from such backups may fail to start during WAL replay and may also lead to the corruption of encrypted data (tables, indexes, and other relations).

Creating, changing, or rotating global key providers (or their keys) during a base backup can leave the standby in an inconsistent state where it cannot retrieve the correct key history.

For example, you may see errors such as:

FATAL: failed to retrieve principal key "database_keyXXXX" from key provider "providerYYYY"
CONTEXT: WAL redo at ... ROTATE_PRINCIPAL_KEY ...

To ensure standby recoverability, plan key rotations outside backup windows or take a new full backup after rotation completes.

F.63.17.3.2. Restore a backup created with WAL encryption #

When you want to restore a backup created with pg_tde_basebackup -E:

Ensure all external files referenced by your providers configuration (such as certificates or key files) are also present and accessible at the same relative paths.
Start Tantor SE with the restored data directory.

F.63.17.3.3. Backup method compatibility with WAL encryption #

Tar format (-F t):

Works with -X fetch.
Does not support -X stream when WAL encryption is enabled. Using pg_tde_basebackup -F t -X stream will create a broken replica.

Streaming mode (-X stream):

Must specify -E (--encrypt-wal).
Without -E, backups may contain decrypted WAL while wal_encryption=on remains in postgresql.conf and pg_tde/wal_keys are copied. This leads to startup failures and compromised data in the backup.

Fetch mode (-X fetch):

Compatible with encrypted WAL without requiring any additional flags.

None (-X none):

Excludes WAL from the backup and is unaffected by WAL encryption.

Note

If the source server has pg_tde/wal_keys, running pg_tde_basebackup with -X none or -X fetch produces warnings such as:

pg_tde_basebackup: warning: the source has WAL keys, but no WAL encryption configured for the target backups
pg_tde_basebackup: detail: This may lead to exposed data and broken backup
pg_tde_basebackup: hint: Run pg_tde_basebackup with -E to encrypt streamed WAL

You can safely ignore the warnings with -X none or -X fetch, since no WAL streaming occurs.

F.63.17.4. Restoring encrypted backups #

To restore an encrypted backup created with an earlier key, ensure the key is still available in your Key Management System (KMS). The backup remains encrypted on disk, pg_tde uses the correct key to transparently access the data.

F.63.17.4.1. How pg_tde uses old keys to load backups #

KMS: stores the principal key(s) that were active when the backup was made.
Encrypted backup: contains data encrypted with the key available at that time.
Current server: pg_tde automatically loads the backup if the key is accessible through the KMS.

The table (internal) keys are stored within the backup. At runtime, pg_tde retrieves the principal key(s) from the configured KMS and uses them to decrypt the internal keys, enabling access to the encrypted table data.

Note

If the required key(s) get deleted or are missing, the backup cannot be read.

If the key(s) still exists but the KMS configuration has changed, you can use pg_tde_change_key_provider to update the configuration.

However, if the key(s) got deleted from all accessible KMS sources, the encrypted backup is unrecoverable.

F.63.18. FAQ #

F.63.18.1. Why do I need TDE? #

Using TDE provides the following benefits:

Compliance to security and legal regulations like General Data Protection Regulation (GDPR), Payment Card Industry Data Security Standard (PCI DSS), Federal Law No. 420-FZ of 30.11.2024 and others
Encryption of backups. Even when an authorized person gets physical access to a backup, encryption ensures that the data remains unreadable and secure.
Granular encryption of specific data sets and reducing the performance overhead that encryption brings.
Additional layer of security to existing security measures

F.63.18.2. When and how should I use TDE? #

If you are dealing with Personally Identifiable Information (PII), data encryption is crucial. Especially if you are involved in areas with strict regulations like:

financial services where TDE helps to comply with PCI DSS
healthcare and insurance - compliance with HIPAA, HITECH, CCPA
telecommunications, government and education to ensure data confidentiality.

Using TDE helps you avoid the following risks:

Data breaches
Identity theft that may lead to financial fraud and other crimes
Reputation damage leading to loss of customer trust and business
Legal consequences and financial losses for non-compliance with data protection regulations
Internal threats by misusing unencrypted sensitive data

If to translate sensitive data to files stored in your database, these are user data in tables, temporary files, WAL files. TDE has you covered encrypting all these files.

pg_tde does not encrypt system catalogs yet. This means that statistics data and database metadata are not encrypted.

F.63.18.3. Will logical replication work with pg_tde? #

Yes, logical replication works with the encrypted tables.

F.63.18.4. I use disk-level encryption. Why should I care about TDE? #

Encrypting a hard drive encrypts all data, including system, application, and temporary files.

Full disk encryption protects your data from people who have physical access to your device and even if it is lost or stolen. However, it doesn’t protect the data after system boot-up: the data is automatically decrypted when the system runs or when an authorized user requests it.

Another point to consider is PCI DSS compliance for Personal Account Numbers (PAN) encryption.

PCI DSS 3.4.1 standards might consider disk encryption sufficient for compliance if you meet these requirements:
- Separate the logical data access from the operating system authentication.
- Ensure the decryption key is not linked to user accounts.
Note that PCI DSS 3.4.1 is retiring on March 31, 2025. Therefore, consider switching to PCI DSS 4.0.
PCI DSS 4.0 standards consider using only disk and partition-level encryption not enough to ensure PAN protection. It requires an additional layer of security that pg_tde can provide.

pg_tde focuses specifically on data files and offers more granular control over encrypted data. The data remains encrypted on disk during runtime and when you move it to another directory, another system or storage. An example of such data is backups. They remain encrypted when moved to the backup storage.

Thus, to protect your sensitive data, consider using TDE to encrypt it at the table level. Then use disk-level encryption to encrypt a specific volume where this data is stored, or the entire disk.

F.63.18.5. Is TDE enough to ensure data security? #

No. Transparent Data Encryption (TDE) adds an extra layer of security for data at rest. You should also consider implementing the following additional security features:

Access control and authentication
Strong network security like TLS
Disk encryption
Regular monitoring and auditing
Additional data protection for sensitive fields (e.g., application-layer encryption)

F.63.18.6. How does pg_tde make my data safe? #

pg_tde uses two keys to encrypt data:

Internal encryption keys to encrypt the data. These keys are stored internally in an encrypted format, in a single $PGDATA/pg_tde directory.
Principal keys to encrypt internal encryption keys. These keys are stored externally, in the Key Management System (KMS).

You can use the following KMSs:

HashiCorp Vault. pg_tde supports the KV secrets engine v2 of Vault, for more information see Vault Configuration.
OpenBao implementation of Vault
KMIP-compatible servers, KMIP is a standardized protocol for handling cryptographic workloads and secrets management, for more information see KMIP Configure.

Note

HashiCorp Vault can also act as a KMIP server, managing cryptographic keys for clients that use the KMIP protocol. (KMIP functionality is available in Vault’s enterprise edition.)

Let’s break the encryption down into two parts:

F.63.18.6.1. Encryption of data files #

First, data files are encrypted with internal keys. Each file that has a different Object Identifier (OID) has an 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 or not.

The principal key is used to encrypt the internal keys. The principal key 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.

F.63.18.6.2. WAL encryption #

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.

F.63.18.7. Should I encrypt all my data? #

It depends on your business requirements and the sensitivity of your data. Encrypting all data is a good practice but it can have a performance impact.

Consider encrypting only tables that store sensitive data. You can decide what tables to encrypt and with what key.

We advise encrypting the whole database only if all your data is sensitive, like PII, or if there is no other way to comply with data safety requirements.

F.63.18.8. What cipher mechanisms are used by pg_tde? #

pg_tde currently uses a AES-CBC-128 algorithm. First the internal keys in the datafile are encrypted using the principal key with AES-CBC-128, then the file data itself is again encrypted using AES-CBC-128 with the internal key.

F.63.18.9. Is post-quantum encryption supported? #

No. Post-quantum encryption is not currently supported.

F.63.18.10. Can I encrypt an existing table? #

Yes, you can encrypt an existing table. Run the ALTER TABLE command as follows:

ALTER TABLE table_name SET ACCESS METHOD tde_heap;

Since the SET ACCESS METHOD command drops hint bits and this may affect the performance, we recommend to run the SELECT count(*) command. It checks every tuple for visibility and sets its hint bits. Read more in the Changing existing table section.

F.63.18.11. Do I have to restart the database to encrypt the data? #

You must restart the database in the following cases to apply the changes:

after you enabled the pg_tde extension
when enabling WAL encryption

After that, no database restart is required. When you create or alter the table using the tde_heap access method, the files are marked as those that require encryption. The encryption happens at the storage manager level, before a transaction is written to disk. Read more about how tde_heap works.

F.63.18.12. What happens to my data if I lose a principal key? #

If you lose encryption keys, especially, the principal key, the data is lost. That’s why it’s critical to back up your encryption keys securely and use the Key Management service for key management.

F.63.19. Uninstall pg_tde #

If you no longer wish to use Transparent Data Encryption (TDE) in your deployment, you can remove the pg_tde extension.

To proceed, you must have one of the following privileges:

Superuser privileges (to remove the extension globally), or
Database owner privileges (to remove it from a specific database only)

To uninstall pg_tde, follow the steps below.

F.63.19.1. Step 1. Remove `pg_tde` from all databases #

Before uninstalling, you must remove the extension from every database where it is loaded. This includes template databases if pg_tde was previously enabled there.

Clean up encrypted tables:

To decrypt a table and restore it to its default storage method:

ALTER TABLE <table_name> SET ACCESS METHOD heap;

Remove the extension once all encrypted tables have been handled:

DROP EXTENSION pg_tde;

Note

If there are any encrypted objects that were not previously decrypted or deleted, this command will fail and you have to follow the steps above for these objects.

F.63.19.2. Step 2. Turn off WAL encryption #

If you are using WAL encryption, you need to turn it off before you uninstall the pg_tde library:

Run:

ALTER SYSTEM SET pg_tde.wal_encrypt = off;

Restart the Tantor SE cluster to apply the changes:

sudo systemctl restart tantor-CONF_EDITION_SHORT_NAME__-server-CONF_PG_MAJOR_VERSION__

F.63.19.3. Step 3. Uninstall the `pg_tde` shared library #

Warning

This process removes the extension, but does not decrypt data automatically. Only uninstall the shared library after all encrypted data has been removed or decrypted and WAL encryption has been disabled.

Note

Encrypted WAL pages will not be decrypted, so any postgres cluster needing to read them will need the pg_tde library loaded, and the WAL encryption keys available and in use.

At this point, the shared library is still loaded but no longer active. To fully uninstall pg_tde, complete the steps below.

Run SHOW shared_preload_libraries to view the current configuration of preloaded libraries.

For example:

postgres=# SHOW shared_preload_libraries;
        shared_preload_libraries
-----------------------------------------
pg_stat_statements,pg_tde,auto_explain
 (1 row)

postgres=#

Remove pg_tde from the list and apply the new setting using ALTER SYSTEM SET shared_preload_libraries=<your list of libraries>.

For example:

postgres=# ALTER SYSTEM SET shared_preload_libraries=pg_stat_statements,auto_explain;
ALTER SYSTEM
postgres=#

Note

Your list of libraries will most likely be different than the above example.

If pg_tde is the only shared library in the list, and it was set via postgresql.conf you cannot disable it using the ALTER SYSTEM SET ... command. Instead:

Remove the shared_preload_libraries line from postgresql.conf
Run ALTER SYSTEM RESET shared_preload_libraries;

Restart the Tantor SE cluster to apply the changes:

sudo systemctl restart tantor-CONF_EDITION_SHORT_NAME__-server-CONF_PG_MAJOR_VERSION__

F.63.19.4. Step 4. (Optional) Clean up configuration #

At this point it is safe to remove any configuration related to pg_tde from postgresql.conf and postgresql.auto.conf. Look for any configuration parameters prefixed with pg_tde. and remove or comment them out, as needed.

F.63.19.5. Troubleshooting: PANIC checkpoint not found on restart #

This can happen if WAL encryption was not properly disabled before removing pg_tde from shared_preload_libraries, when the Tantor SE server was not restarted after disabling WAL encryption (see step 3.c).

You might see this when restarting the Tantor SE cluster:

2025-04-01 17:12:50.607 CEST [496385] PANIC:  could not locate a valid checkpoint record at 0/17B2580

To resolve it follow these steps:

Re-add pg_tde to shared_preload_libraries
Restart the Tantor SE cluster
Follow the instructions for turning off WAL encryption before uninstalling the shared library again

Note

Two restarts are required to uninstall properly if WAL encryption was enabled:

First to disable WAL encryption
Second to remove the pg_tde library

Prev	Up	Next
F.62. pg_surgery — perform low-level surgery on relation data	Home	F.64. pg_throttle — extension which add query throttling feature

F.63. pg_tde — Transparent Data Encryption for Tantor SE

F.63. pg_tde — Transparent Data Encryption for Tantor SE#