PyKMIP + PostgreSQL with pg_tde, Percona Server for MySQL

i wanted to try applying transparent data encryption in PostgreSQL 17, MySQL 8.4 with key obtained via KMIP from KMS. i’ve used PyKMIP as Key Management System. i could not find any detailed step by step guide, even Percona’s own documents were pretty minimal. below – is what i’ve done step by step. my setup barely works, it’s definitively not secure or production ready.

PyKMIP seems to be quite stale, at the time of writing it does not work with Python 3.12 and newer; fortunately python3-pykmip package in Debian Trixie is patched, allowing it to work just fine.

PyKMIP installation, generating keys

apt-get install python3-pykmip
mkdir /etc/pykmip
cd /etc/pykmip

# generate CA, keys for KMS sever [pykmip] and client [ pg or mysql ]
# nkeyUsage was cruicial, without it - i could not get databases to talk with pykmip

openssl genrsa -out ca.key 2048
openssl req -x509 -nodes -days 3650   -new -key ca.key   -out ca.crt     -extensions v3_ca   -subj "/C=PL/ST=Mazowieckie/L=Warsaw/O=pQd/OU=pQd/CN=pQdca"

openssl req -newkey rsa:2048 -nodes -keyout server.key -out server.csr -subj "/CN=server" -addext "keyUsage = digitalSignature, keyEncipherment" -addext "extendedKeyUsage = clientAuth, serverAuth"
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 365 -extfile <(printf "[req_ext]\nkeyUsage = digitalSignature,keyEncipherment\nextendedKeyUsage = clientAuth,serverAuth\n") -extensions req_ext

openssl req -newkey rsa:2048 -nodes -keyout client.key -out client.csr -subj "/CN=client" -addext "keyUsage = digitalSignature, keyEncipherment" -addext "extendedKeyUsage = clientAuth, clientAuth"
openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt -days 365 -extfile <(printf "[req_ext]\nkeyUsage = digitalSignature,keyEncipherment\nextendedKeyUsage = clientAuth,clientAuth\n") -extensions req_ext

chmod go-rwx *

create /etc/pykmip/server.conf with:

[server]
# for my tests communication over the loopback is enough
hostname = 127.0.0.1
port = 5696
certificate_path = /etc/pykmip/server.key
key_path = /etc/pykmip/server.crt
ca_path = /etc/pykmip/ca.crt
enable_tls_client_auth = True
database_path=/etc/pykmip/pykmip.db
auth_suite=TLS1.2
logging_level=DEBUG

start the pykmip server:

pykmip-server -f /etc/pykmip/server.conf

Percona Server for PostgreSQL 17 + pg_tde

i’m using Percona’s distribution of PostgreSQL and their Transparent Data Encryption extension

cd ~
apt install curl
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
apt update
percona-release setup ppg-17
apt install percona-postgresql-17 percona-postgresql-17-pgvector percona-pg-tde17

# might be helpful to clean up after earlier experiments:
# that's guaranteed data loss!
# systemctl stop postgresql@17-main.service ; rm -rf /var/lib/postgresql/17/
# pg_dropcluster --stop 17 main ; pg_createcluster --start 17 main
systemctl restart postgresql.service

create a config file instructing pg to load pg_tde extension – /etc/postgresql/17/main/conf.d/tde.conf:

shared_preload_libraries = 'pg_tde'

then

systemctl restart postgresql.service
echo 'CREATE EXTENSION pg_tde;' |su - postgres -c psql
echo 'CREATE EXTENSION pg_tde;' |su - postgres -c "psql -d template1"

make keys available to postgresql which will act as KMS client:

cp /etc/pykmip/client.crt /var/lib/postgresql/ssl
cp /etc/pykmip/client.key /var/lib/postgresql/ssl
cp /etc/pykmip/ca.crt /var/lib/postgresql/ssl
chown postgres:postgres -R /var/lib/postgresql/ssl/

in psql – configure details of the KMS server – run psql and there:

SELECT pg_tde_add_global_key_provider_kmip('pykmip','127.0.0.1',5696,'/var/lib/postgresql/ssl/client.crt','/var/lib/postgresql/ssl/client.key','/var/lib/postgresql/ssl/ca.crt');
SELECT pg_tde_create_key_using_global_key_provider( 'key-name', 'pykmip' );
SELECT pg_tde_set_default_key_using_global_key_provider( 'key-name', 'pykmip' );
SELECT pg_tde_set_key_using_global_key_provider('key-name', 'pykmip');



-- creating encrypted table
CREATE TABLE my_table (
  id integer,
  v TEXT
) USING tde_heap;

INSERT INTO my_table (id,v) VALUES (1,'aaa'),(2,'bbb');
SELECT pg_tde_is_encrypted('my_table');

-- key rotation
select pg_tde_key_info();
SELECT pg_tde_set_server_key_using_global_key_provider('key-name', 'pykmip');
SELECT pg_tde_set_default_key_using_global_key_provider('key-name', 'pykmip');
select pg_tde_key_info();

-- or maybe
SELECT pg_tde_create_key_using_global_key_provider('key2','pykmip');
SELECT pg_tde_set_default_key_using_global_key_provider('key2','pykmip');

Percona Server for MySQL 8.4 + TDE

apt install curl
curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
apt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
apt update
percona-release enable-only ps-84-lts release
percona-release enable tools release
apt install percona-server-server

in /usr/sbin/mysqld.my put:

{
      "components": "file://component_keyring_kmip"
}

in /usr/lib/mysql/plugin/component_keyring_kmip.cnf:

{
  "path": "/var/lib/mysql-keyring",
  "server_addr": "127.0.0.1",
  "server_port":"5696",
  "client_ca":"/var/lib/mysql-keyring/client.crt",
  "client_key":"/var/lib/mysql-keyring/client.key",
  "server_ca":"/var/lib/mysql-keyring/ca.crt"
}

# clean up mysql
systemctl disable mysql
systemctl stop mysql
# yes, below means total data loss
rm -rf /var/lib/mysql/*
cd /etc/pykmip/
rm -rf /var/lib/mysql-keyring
mkdir /var/lib/mysql-keyring
cp /etc/pykmip/client.crt /var/lib/mysql-keyring/
cp /etc/pykmip/client.key /var/lib/mysql-keyring/
cp /etc/pykmip/ca.crt /var/lib/mysql-keyring/
chown mysql:mysql -R /var/lib/mysql-keyring/

i have a lot of headaches with MySQL – it does not get up properly after it’s stopped; each time i have to restart also pykmip to allow clean MySQL restart, or reboot the whole server

CREATE DATABASE test;
CREATE TABLE test.t1 (
  id int NOT NULL,
  data varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y';
INSERT INTO test.t1 VALUES(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

it’s also worth encrypting other ‘moving parts’ where plain text data might be stored:

scraps of information that helped with above: /etc/mysql/mysql.conf.d/tde.cnf

[mysqld]
default_table_encryption=ON
innodb_temp_tablespace_encrypt=ON
default_table_encryption=ON
innodb_redo_log_encrypt=ON
innodb_undo_log_encrypt=ON

• PyKMIP
  • https://blog.masterwayz.nl/setting-up-a-free-kms-server-for-vcenter-with-pykmip-with-database/
  • https://github.com/blink1073/PyKMIP/tree/remove-ssl-wrap-socket
  • https://github.com/altmannmarcelo/docker-kmip/tree/main
• PostgreSQL + pg_tde:
  • https://docs.percona.com/postgresql/17/apt.html
  • https://percona.community/blog/2025/09/01/encrypting-postgresql-tables-with-pg_tde-step-by-step-guide-for-beginners/
  • https://docs.percona.com/pg-tde/functions.html#principal-key-management
• Percona Server for MySQL + TDE:
  • https://docs.percona.com/percona-server/8.4/using-kmip.html?h=pykmip#component-installation
  • https://support.fortanix.com/docs/using-fortanix-data-security-manager-for-percona-mysql-encryption-at-rest#30-configuring-encryption-in-percona-mysql