View mode

Using pg_tde with Cosmian KMS and PostgreSQL 17 (Percona)¶

This guide demonstrates how to configure PostgreSQL 17 with Percona’s pg_tde extension to use Cosmian KMS for transparent data encryption (TDE).

Using pg_tde with Cosmian KMS and PostgreSQL 17 (Percona)
Prerequisites
Configuration Steps
Troubleshooting & Notes

Prerequisites¶

Before starting, ensure you have:

PostgreSQL 17 (Percona Server for PostgreSQL 17.5.2 or later)
pg_tde extension installed
Access to a running Cosmian KMS server
Appropriate SSL certificates for KMIP communication

Configuration Steps¶

1. Configure PostgreSQL¶

Edit your postgresql.conf file to enable the required extensions:

shared_preload_libraries = 'pg_tde,percona_pg_telemetry'
pg_tde.wal_encrypt = on
pg_tde.enforce_encryption = on

Important: Changes to pg_tde.wal_encrypt or pg_tde.enforce_encryption require a PostgreSQL restart to take effect.

2. Restart PostgreSQL¶

After modifying the configuration, restart the PostgreSQL service:

sudo systemctl restart [email protected]

3. Configure the KMS Key Provider¶

Connect to your PostgreSQL database and add the Cosmian KMS as a key provider using the KMIP protocol:

SELECT pg_tde_add_global_key_provider_kmip(
  'kms_provider',                -- Provider name (can be customized)
  'kms-host.example.com',        -- Your KMS server hostname
  5696,                          -- KMIP port (default is 5696)
  '/path/to/client_cert.pem',    -- Client certificate file path
  '/path/to/client_key.pem',     -- Client private key file path
  '/path/to/ca_cert.pem'         -- Certificate Authority file path
);

Note: Replace the placeholder values with your actual KMS server details and certificate paths.

4. Set the Default Encryption Key¶

Configure the default encryption key using the KMS provider:

SELECT pg_tde_set_default_key_using_global_key_provider('key_01', 'kms_provider');

The first parameter (key_01) is the key identifier, and the second parameter (kms_provider) must match the provider name from step 3.

5. Enable the Extension¶

Create the pg_tde extension in your target database(s):

CREATE EXTENSION pg_tde;

6. Ensure event triggers are set (usually created on extension install)¶

CREATE EVENT TRIGGER pg_tde_ddl_start ON ddl_command_start
  EXECUTE FUNCTION pg_tde_ddl_command_start_capture();

CREATE EVENT TRIGGER pg_tde_ddl_end ON ddl_command_end
  EXECUTE FUNCTION pg_tde_ddl_command_end_capture();

If the trigger already exists, this error can be safely ignored.

7. Create encrypted tables¶

CREATE TABLE sensitive_data (
  id serial PRIMARY KEY,
  secret_text text
) USING tde_heap;

The USING tde_heap clause ensures the table is encrypted using pg_tde.

8. Verify if a table is encrypted¶

SELECT pg_tde_is_encrypted('public.sensitive_data'::regclass);
-- Expected output: t (true)

9. Insert and query data transparently¶

Encryption is transparent; use standard SQL commands:

INSERT INTO sensitive_data (secret_text) VALUES ('Top secret info');
SELECT * FROM sensitive_data;

Data is stored encrypted on disk but returned in plaintext when queried.

10. Check current encryption settings¶

SHOW pg_tde.wal_encrypt;
SHOW pg_tde.enforce_encryption;
SHOW pg_tde.inherit_global_providers;

Troubleshooting & Notes¶

shared_preload_libraries must include at least 'pg_tde'.
To change pg_tde.wal_encrypt or pg_tde.enforce_encryption, a server restart is mandatory.
Ensure SSL certificates are properly secured with appropriate file permissions
Store certificate files in a secure location accessible only to the PostgreSQL service
Regularly rotate encryption keys as per your security policy
Monitor KMS connectivity and have appropriate failover procedures

Common issues and solutions:

Function	Description
`pg_tde_add_global_key_provider_kmip(...)`	Add KMIP key provider
`pg_tde_set_default_key_using_global_key_provider(key, provider)`	Set default encryption key using a provider
`pg_tde_is_encrypted(regclass)`	Check if table is encrypted
`pg_tde_default_key_info()`	Show info about default encryption key

For more detailed information, refer to the official pg_tde documentation.