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 cases4

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
• Cosmian KMS KMIP Support
• Percona pg_tde Architecture
• Percona WAL Encryption Blog (2025-09-01)
• Percona pg_tde Limitations

Official Documentation Links¶

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