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

Prerequisites¶

Before starting, ensure you have:

• PostgreSQL 17 Percona Server for PostgreSQL 17.x or later
• pg_tde extension installed
• Access to a running Cosmian KMS server
• Appropriate SSL certificates for KMIP communication TLS 1.2+

Before You Start: Understanding pg_tde Architecture¶

Global Architecture Overview¶

flowchart TB
    app["Application / SQL Queries
(SELECT, INSERT, UPDATE, DELETE)"]
    plain["Data in plaintext in memory"]
    subgraph pg["PostgreSQL + pg_tde Extension + Percona Patches"]
        smgr["SMGR (Storage Manager) — Interception Layer"]
        tde["TDE Tables
(USING tde_heap)"]
        nontde["Non-TDE Tables
(standard heap)"]
        enc["Encryption of pages
(CBC-128)"]
        noenc["No encryption"]
        walenc["Encryption (optional, WAL only)
(CTR-128) WAL"]
        smgr --> tde & nontde
        tde --> enc
        nontde --> noenc & walenc
    end
    disk["Disk (encrypted data)
WAL Files (encrypted if pg_tde.wal_encrypt = on)"]
    app --> plain --> smgr
    enc --> disk
    noenc --> disk
    walenc --> disk

KMIP Communication Flow¶

flowchart TB
    pg["PostgreSQL + pg_tde
(KMIP Client)"]
    kms["Cosmian KMS 5.6+
(KMIP Server)

Supported operations:
✓ Create · Get · Destroy · Register
✓ Locate · Activate · Revoke

Protocol: KMIP 1.x and 2.x
Profile: Baseline Server"]
    pg -->|"KMIP over TLS 1.2/1.3
Port 5696 (binary)
Required: client_cert.pem, client_key.pem, ca_cert.pem"| kms

Configuration Steps¶

1. Configure PostgreSQL¶

Edit your postgresql.conf file to activate the TDE extension:[1]

shared_preload_libraries = 'pg_tde,percona_pg_telemetry'

Important: Changes to shared_preload_libraries require a PostgreSQL restart to take effect.[1]

sudo systemctl restart [email protected]

2. Enable TDE Extension¶

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

CREATE EXTENSION pg_tde;

This will automatically create event triggers needed for pg_tde operation.

3. Configure the KMS Key Provider¶

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

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.[1]

Certificate Requirements:[2]

• All certificates must be in PEM format
• Client certificates must be X.509 compliant
• TLS 1.2 or higher is required for KMIP communication
• Certificate files must be readable by the PostgreSQL system user

4. Set the Default Encryption Key¶

Configure the default encryption key using the KMS provider:[1]

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

SELECT pg_tde_set_server_key_using_global_key_provider('key_01', '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.[1]

What happens in this step:

• pg_tde_create_key_using_global_key_provider() creates a Principal Key managed by Cosmian KMS
• pg_tde_set_server_key_using_global_key_provider() sets the server-level default key
• pg_tde_set_default_key_using_global_key_provider() sets the database-level default key

5. Ensure Event Triggers Are Set¶

Event triggers are usually created automatically when the extension is installed. To verify or recreate them:[1]

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.[1]

These triggers ensure that DDL operations (table creation, modification) properly handle encryption metadata.

6. Enable WAL Encrypt and TDE Enforce Encryption¶

Edit your postgresql.conf again and add:[1]

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.[1]

sudo systemctl restart [email protected]

About these parameters:[1]

• pg_tde.wal_encrypt = on encrypts Write-Ahead Log files (production-ready as of Percona PostgreSQL 17.5.3)
• pg_tde.enforce_encryption = on prevents creation of unencrypted tables when a default key is set (strongly recommended)

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.[1]

Important: Only tables created with USING tde_heap are encrypted. Existing non-TDE tables remain unencrypted unless migrated.

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:[1]

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.[1]

10. Check Current Encryption Settings¶

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

Encryption Scope and What Gets Encrypted¶

✅ WHAT IS ENCRYPTED¶

Application Data¶

Logs and Transactions¶

Backup and Recovery¶

❌ WHAT IS NOT ENCRYPTED¶

System Metadata and Catalogs¶

Tables and Files¶

Key Management: DEK, Internal Keys and Principal Keys¶

Two-Level Key Architecture¶

pg_tde uses a two-level key hierarchy for data encryption:[3]

flowchart TB
    principal["PRINCIPAL KEY (Master Key)

Stored externally in Cosmian KMS via KMIP
ONE per database
Encrypts Internal Keys (AES-128-GCM)
Accessible only via TLS KMIP connection

Created with pg_tde_create_key_using_global_key_provider()"]
    internal["INTERNAL KEYS (Data Encryption Keys / DEK)

Stored locally in $PGDATA/pg_tde/
Encrypted by Principal Key
ONE unique key per relation (OID)

Tables: AES-128-CBC · WAL: AES-128-CTR · Keys: AES-128-GCM"]
    data["ENCRYPTED DATA (User Data)

Table pages stored encrypted on disk
Index pages stored encrypted
WAL data encrypted (if pg_tde.wal_encrypt = on)"]
    principal -->|"Encrypts via KMIP wrap (AES-128-GCM)"| internal
    internal -->|"Encrypt (AES-128-CBC / CTR)"| data

Internal Keys (DEK) Details¶

Generation and Storage¶

Disk Structure Example¶

$PGDATA/pg_tde/
├── global/                          # Global section
│   ├── provider_config              # Global provider configuration
│   └── server_key.key               # WAL server key (if enabled)
│
└── 16384/                           # Database OID (example)
    ├── provider_config              # Database provider configuration
    ├── 16385.key                    # Internal key for table OID=16385
    ├── 16386.key                    # Internal key for index OID=16386
    ├── 16387.key                    # Internal key for index OID=16387
    ├── 16388.key                    # Internal key for TOAST OID=16388
    └── 16389.key                    # Internal key for sequence OID=16389

Security Considerations¶

Recommended Protections¶

# 1. Disable core dumps
echo "kernel.core_pattern = /dev/null" >> /etc/sysctl.conf
sysctl -p

# 2. Encrypt swap (optional but recommended)
# Use dm-crypt or zswap

# 3. Lock PostgreSQL memory (optional but recommended)
ulimit -l unlimited
# or in postgresql.conf:
# lock_memory = true

# 4. Secure KMS certificates
sudo chmod 400 /path/to/client_cert.pem
sudo chmod 400 /path/to/client_key.pem
sudo chown postgres:postgres /path/to/*.pem

Key Monitoring¶

Verify Current Keys¶

-- Check server default Principal Key
SELECT pg_tde_server_key_info();
-- Result: (key_name, provider_name)

-- Check current database Principal Key
SELECT pg_tde_key_info();

-- Check default Principal Key (if used)
SELECT pg_tde_default_key_info();

-- List all configured providers
SELECT * FROM pg_tde_list_all_global_key_providers();
SELECT * FROM pg_tde_list_all_database_key_providers();

Verify KMS Connectivity¶

-- Test Principal Key availability
SELECT pg_tde_verify_key();
SELECT pg_tde_verify_server_key();
SELECT pg_tde_verify_default_key();

-- If these fail: Check PostgreSQL logs for KMIP errors
-- tail -f $PGDATA/log/postgresql.log | grep -i kmip

Verification and Testing¶

Initial Setup Verification¶

-- 1. Verify extension is loaded
SELECT * FROM pg_extension WHERE extname = 'pg_tde';

-- 2. Verify configuration parameters
SHOW shared_preload_libraries;
SHOW pg_tde.wal_encrypt;
SHOW pg_tde.enforce_encryption;

-- 3. Verify KMS provider is configured
SELECT * FROM pg_tde_list_all_global_key_providers();

-- 4. Verify keys are set
SELECT pg_tde_server_key_info();
SELECT pg_tde_default_key_info();

-- 5. Verify KMS connectivity
SELECT pg_tde_verify_key();
SELECT pg_tde_verify_server_key();

-- 6. Create test table
CREATE TABLE test_encrypted (
  id serial PRIMARY KEY,
  data text
) USING tde_heap;

-- 7. Verify it's encrypted
SELECT pg_tde_is_encrypted('public.test_encrypted'::regclass);
-- Expected output: t (true)

-- 8. Test data insertion and retrieval
INSERT INTO test_encrypted (data) VALUES ('Test data');
SELECT * FROM test_encrypted;
-- Data should be returned in plaintext

WAL Encryption Verification¶

-- Check if WAL encryption is enabled
SHOW pg_tde.wal_encrypt;

-- Verify encryption is active
SELECT pg_tde_is_wal_encrypted();

-- Check WAL files (encrypted WAL segments have standard naming)
SELECT name FROM pg_ls_waldir()
ORDER BY name DESC LIMIT 5;

Troubleshooting & Solutions¶

Common Issues and Diagnostic Flow¶

1. PostgreSQL Fails to Start¶

ERROR: could not load shared library "pg_tde"
or
ERROR: could not connect to KMIP server

Diagnostic steps:

# 1. Check if extension is compiled correctly
ls -la $PGINSTALL/lib/pg_tde.so

# 2. Check PostgreSQL logs
tail -f $PGDATA/log/postgresql.log

# 3. Verify KMS is reachable
telnet <kms-host> 5696

# 4. Check certificate paths
ls -la /path/to/*.pem
file /path/to/client_cert.pem

SQL Verification:

SELECT pg_tde_verify_key();
SELECT pg_tde_verify_server_key();
SELECT * FROM pg_tde_list_all_global_key_providers();

Solutions:

• Verify shared_preload_libraries contains pg_tde
• Restart PostgreSQL after configuration changes
• Ensure Cosmian KMS is running: telnet <kms-host> 5696
• Check firewall/network between PostgreSQL and KMS

2. TLS Certificate Verification Failed¶

ERROR: SSL/TLS certificate verification failed
DETAIL: certificate verify failed / self signed certificate

Diagnostic:

# Verify certificate details
openssl x509 -in /path/to/ca_cert.pem -text -noout

# Check certificate expiration
openssl x509 -in /path/to/client_cert.pem -noout -dates

# Verify certificate chain
openssl verify -CAfile /path/to/ca_cert.pem /path/to/client_cert.pem

# Test KMIP connection manually (if PyKMIP available)
python3 -m kmip.demos.client -b /path/to/client_cert.pem \
  -k /path/to/client_key.pem \
  -ca /path/to/ca_cert.pem \
  --server <kms-host> --port 5696

Solutions:

• Verify certificate files exist and are readable: ls -la /path/to/*.pem
• Check certificate expiration dates
• Verify CA certificate chain is complete
• Ensure certificate paths in pg_tde_add_global_key_provider_kmip() are correct

3. Key Not Found or Access Denied¶

ERROR: Failed to retrieve principal key 'key_01' from KMS provider 'kms_provider'
DETAIL: Key not found / Access denied

Diagnostic:

-- Verify KMS connectivity
SELECT pg_tde_verify_key();
SELECT pg_tde_verify_server_key();

-- Check configured keys
SELECT pg_tde_key_info();
SELECT pg_tde_server_key_info();

-- Check provider configuration
SELECT * FROM pg_tde_list_all_global_key_providers();

Solutions:

• Verify the key exists on Cosmian KMS
• Verify the key name matches exactly (case-sensitive)
• Check KMS user/role has permissions to access the key
• Verify database OID if using database-level keys: SELECT datoid FROM pg_database WHERE datname = current_database();
• Check $PGDATA/pg_tde/ directory permissions

4. Performance Degradation After Enabling TDE¶

Problem: Slower queries, high CPU usage

Diagnostic:

-- Check cache hit ratio
SELECT sum(heap_blks_read) / (sum(heap_blks_read) +
        sum(heap_blks_hit)) AS cache_hit_ratio
FROM pg_stat_user_tables;

-- Check query plans
EXPLAIN ANALYZE SELECT * FROM sensitive_data LIMIT 1000;

Solutions:

• Increase shared_buffers to reduce disk I/O
• Check CPU supports AES-NI (hardware acceleration):

grep -o 'aes' /proc/cpuinfo | head -1

• Monitor I/O performance with iostat -x 1
• Note: Percona reports ~10% overhead in most cases[4]

Certificate Management Best Practices¶

# 1. Organize certificate files
mkdir -p /etc/postgresql/kmip-certs
sudo cp /path/to/*.pem /etc/postgresql/kmip-certs/
sudo chmod 400 /etc/postgresql/kmip-certs/*.pem
sudo chown postgres:postgres /etc/postgresql/kmip-certs/

# 2. Set correct paths in pg_tde configuration
SELECT pg_tde_add_global_key_provider_kmip(
  'kms_provider',
  'kms-host.example.com',
  5696,
  '/etc/postgresql/kmip-certs/client_cert.pem',
  '/etc/postgresql/kmip-certs/client_key.pem',
  '/etc/postgresql/kmip-certs/ca_cert.pem'
);

# 3. Monitor certificate expiration
openssl x509 -in /etc/postgresql/kmip-certs/client_cert.pem -noout -dates | \
  grep notAfter

# 4. Plan certificate rotation before expiration
# Test with new certificates before cutover

Operational Considerations¶

Migration from Non-TDE to TDE Tables¶

flowchart TB
    start["Migration: non-TDE → TDE table"]
    m1["Method 1: CREATE TABLE AS"]
    m2["Method 2: ALTER TABLE"]
    m1a["CREATE TABLE t_new USING tde_heap
AS SELECT * FROM t_old"]
    m2a["ALTER TABLE t_old
SET ACCESS METHOD tde_heap"]
    m1b["DROP TABLE t_old
ALTER TABLE t_new RENAME TO t_old"]
    m2b["Recreates index, constraints, foreign keys"]
    done["✓ TDE table created (exclusive lock)"]
    start --> m1 & m2
    m1 --> m1a --> m1b --> done
    m2 --> m2a --> m2b --> done

Impact:

• Exclusive lock on table during migration
• Complete data rewrite
• Time proportional to table size
• Disk usage x2 during operation

Recommendation: Perform during maintenance window on production systems.

Key Rotation¶

Principal Key Rotation¶

-- 1. Create new key on Cosmian KMS
SELECT pg_tde_create_key_using_global_key_provider(
  'key_02', 'kms_provider'
);

-- 2. Switch to new key
-- (Re-encrypts all internal keys)
SELECT pg_tde_set_default_key_using_global_key_provider(
  'key_02', 'kms_provider'
);

-- 3. Verify rotation is complete
SELECT pg_tde_default_key_info();

-- 4. Old key can be archived/destroyed after verification
-- (Cosmian KMS: key revoke/destroy operations)

Notes:

• Internal keys are re-encrypted (non-blocking operation)
• Old key retained for recovery purposes
• Does not re-encrypt user data (only wraps internal keys)
• Minimal performance impact

Internal Key Rotation (Existing Tables)¶

No direct in-place internal key rotation. Use one of these workarounds:

-- Method 1: VACUUM FULL (simpler but locks table)
VACUUM FULL sensitive_data;
-- Note: Table remains in memory during operation

-- Method 2: CREATE TABLE AS (more controlled)
CREATE TABLE sensitive_data_new USING tde_heap AS
  SELECT * FROM sensitive_data;

-- Recreate indexes
CREATE INDEX idx_sensitive_data_id ON sensitive_data_new(id);

-- Swap tables
DROP TABLE sensitive_data;
ALTER TABLE sensitive_data_new RENAME TO sensitive_data;

-- Verify new encryption
SELECT pg_tde_is_encrypted('public.sensitive_data'::regclass);

Performance Comparison:

Backup and Recovery¶

Physical Backup with Encrypted WAL¶

# Full backup with streaming WAL
pg_tde_basebackup -D /path/to/backup \
  --wal-method=stream \
  -R \
  -v

# Or with archived WAL (requires WAL archiving configured)
pg_tde_basebackup -D /path/to/backup \
  --wal-method=none \
  -R

# WAL archiving setup (in postgresql.conf)
archive_mode = on
archive_command = 'pg_tde_archive_decrypt %f %p | pgbackrest archive-push %p'

# WAL restore setup (in recovery.conf or postgresql.conf)
restore_command = 'pgbackrest archive-get %f %p'

Recovery Point Objective (RPO)¶

flowchart TB
    base["pg_tde_basebackup (baseline)"]
    wal["WAL segments (archived via archive_command)"]
    pitr["Allows PITR up to last WAL segment"]
    rpo["RPO = 1 WAL segment (16 MB by default)
TO = Time to replay WAL"]
    base --> wal --> pitr --> rpo

Failover and Standby Setup¶

Standby Configuration with Patroni¶

# patroni.yml for pg_tde with Cosmian KMS
postgresql:
  # Use pg_tde_rewind, NOT standard pg_rewind if WAL encrypted
  pg_rewind: pg_tde_rewind

  parameters:
    shared_preload_libraries: pg_tde
    pg_tde.wal_encrypt: on
    pg_tde.enforce_encryption: on

    # Archive settings
    archive_mode: on
    archive_command: "pg_tde_archive_decrypt %f %p | pgbackrest archive-push %p"
    restore_command: "pgbackrest archive-get %f %p"

Failover Considerations¶

Important: If using encrypted WAL, pg_rewind is incompatible. Use pg_tde_rewind instead.[5]

Cosmian KMS Compatibility¶

Supported features with pg_tde:[2]

• KMIP 1.x and 2.x protocols
• Baseline Server profile (fully compliant)
• AES-128-CBC, AES-128-CTR, AES-128-GCM algorithms
• Key creation, retrieval, destruction, rotation
• TLS 1.2+ for secure communication

Backup and Replication Tools Compatibility:

Ongoing Maintenance¶

1. Monitor KMS connectivity:

SELECT pg_tde_verify_key();

1. Plan key rotation:
2. Principal keys: Via Cosmian KMS management

3. Internal keys: Via table recreation (VACUUM FULL or ALTER TABLE SET ACCESS METHOD)

4. Backup strategy:

5. Use pg_tde_basebackup or pgBackRest
6. Archive encrypted WAL files

7. Test recovery procedures regularly

8. Certificate rotation:

9. Plan before expiration
10. Test with new certificates before cutover

References¶

• [Percona pg_tde Documentation]https://percona.github.io/pg_tde/main/
• [Cosmian KMS KMIP Support]https://docs.cosmian.com/key_management_system/kmip/
• [Percona pg_tde Architecture]https://docs.percona.com/pg-tde/architecture/architecture.html
• [Percona WAL Encryption Blog (2025-09-01)]https://percona.community/blog/2025/09/01/pg_tde-can-now-encrypt-your-wal-on-prod/
• [Percona pg_tde Limitations]https://docs.percona.com/pg-tde/index/tde-limitations.html#currently-unsupported-wal-tools

Official Documentation Links¶

• Percona pg_tde Official Docs
• Cosmian KMS Percona Integration
• Percona Server for PostgreSQL 17