{"id":3860,"date":"2026-02-08T22:08:00","date_gmt":"2026-02-08T21:08:00","guid":{"rendered":"https:\/\/kudzia.eu\/b\/?p=3860"},"modified":"2026-03-21T18:55:25","modified_gmt":"2026-03-21T17:55:25","slug":"pykmip-postgresql-with-pg_tde-percona-server-for-mysql","status":"publish","type":"post","link":"https:\/\/kudzia.eu\/b\/2026\/02\/pykmip-postgresql-with-pg_tde-percona-server-for-mysql\/","title":{"rendered":"PyKMIP + PostgreSQL with pg_tde, Percona Server for MySQL"},"content":{"rendered":"\n

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. <\/p>\n\n\n\n

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

PyKMIP installation, generating keys<\/h2>\n\n\n\n
apt-get install python3-pykmip\nmkdir \/etc\/pykmip\ncd \/etc\/pykmip\n\n# generate CA, keys for KMS sever [pykmip] and client [ pg or mysql ]\n# nkeyUsage was cruicial, without it - i could not get databases to talk with pykmip\n\nopenssl genrsa -out ca.key 2048\nopenssl 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\"\n\nopenssl req -newkey rsa:2048 -nodes -keyout server.key -out server.csr -subj \"\/CN=server\" -addext \"keyUsage = digitalSignature, keyEncipherment\" -addext \"extendedKeyUsage = clientAuth, serverAuth\"\nopenssl 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\n\nopenssl req -newkey rsa:2048 -nodes -keyout client.key -out client.csr -subj \"\/CN=client\" -addext \"keyUsage = digitalSignature, keyEncipherment\" -addext \"extendedKeyUsage = clientAuth, clientAuth\"\nopenssl 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\n\nchmod go-rwx *<\/code><\/pre>\n\n\n\n
create \/etc\/pykmip\/server.conf with:<\/p>\n\n\n\n
[server]\n# for my tests communication over the loopback is enough\nhostname = 127.0.0.1\nport = 5696\ncertificate_path = \/etc\/pykmip\/server.key\nkey_path = \/etc\/pykmip\/server.crt\nca_path = \/etc\/pykmip\/ca.crt\nenable_tls_client_auth = True\ndatabase_path=\/etc\/pykmip\/pykmip.db\nauth_suite=TLS1.2\nlogging_level=DEBUG\n<\/code><\/pre>\n\n\n\n
start the pykmip server:<\/p>\n\n\n\n
pykmip-server -f \/etc\/pykmip\/server.conf<\/code><\/pre>\n\n\n\n
Percona Server for PostgreSQL 17 + pg_tde<\/h2>\n\n\n\n
i’m using Percona’s distribution of PostgreSQL and their Transparent Data Encryption extension<\/p>\n\n\n\n
cd ~\napt install curl\nwget https:\/\/repo.percona.com\/apt\/percona-release_latest.$(lsb_release -sc)_all.deb\ndpkg -i percona-release_latest.$(lsb_release -sc)_all.deb\napt update\npercona-release setup ppg-17\napt install percona-postgresql-17 percona-postgresql-17-pgvector percona-pg-tde17\n\n# might be helpful to clean up after earlier experiments:\n# that's guaranteed data loss!\n# systemctl stop postgresql@17-main.service ; rm -rf \/var\/lib\/postgresql\/17\/\n# pg_dropcluster --stop 17 main ; pg_createcluster --start 17 main\nsystemctl restart postgresql.service<\/code><\/pre>\n\n\n\n
create a config file instructing pg to load pg_tde<\/em> extension – \/etc\/postgresql\/17\/main\/conf.d\/tde.conf:<\/p>\n\n\n\n
shared_preload_libraries = 'pg_tde'<\/code><\/pre>\n\n\n\n
then<\/p>\n\n\n\n
systemctl restart postgresql.service\necho 'CREATE EXTENSION pg_tde;' |su - postgres -c psql\necho 'CREATE EXTENSION pg_tde;' |su - postgres -c \"psql -d template1\"\n<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
make keys available to postgresql which will act as KMS client:<\/p>\n\n\n\n
cp \/etc\/pykmip\/client.crt \/etc\/postgresql\/17\/main\/kmip\/\ncp \/etc\/pykmip\/client.key \/etc\/postgresql\/17\/main\/kmip\/\ncp \/etc\/pykmip\/ca.crt \/etc\/postgresql\/17\/main\/kmip\/\nchown postgres:postgres -R \/etc\/postgresql\/17\/main\/kmip\/\n<\/code><\/pre>\n\n\n\n
in psql – configure details of the KMS server – run psql<\/em> and there:<\/p>\n\n\n\n
SELECT pg_tde_add_global_key_provider_kmip('pykmip','127.0.0.1',5696,'\/etc\/postgresql\/17\/main\/kmip\/client.crt','\/etc\/postgresql\/17\/main\/kmip\/client.key','\/etc\/postgresql\/17\/main\/kmip\/ca.crt');\nSELECT pg_tde_create_key_using_global_key_provider( 'key-name', 'pykmip' );\nSELECT pg_tde_set_default_key_using_global_key_provider( 'key-name', 'pykmip' );\nSELECT pg_tde_set_key_using_global_key_provider('key-name', 'pykmip');\n\n\n\n-- creating encrypted table\nCREATE TABLE my_table (\n  id integer,\n  v TEXT\n) USING tde_heap;\n\nINSERT INTO my_table (id,v) VALUES (1,'aaa'),(2,'bbb');\nSELECT pg_tde_is_encrypted('my_table');\n\n-- key rotation\nselect pg_tde_key_info();\nSELECT pg_tde_set_server_key_using_global_key_provider('key-name', 'pykmip');\nSELECT pg_tde_set_default_key_using_global_key_provider('key-name', 'pykmip');\nselect pg_tde_key_info();\n\n-- or maybe\nSELECT pg_tde_create_key_using_global_key_provider('key2','pykmip');\nSELECT pg_tde_set_default_key_using_global_key_provider('key2','pykmip');\n<\/code><\/pre>\n\n\n\n
Percona Server for MySQL 8.4 + TDE<\/h2>\n\n\n\n
apt install curl\ncurl -O https:\/\/repo.percona.com\/apt\/percona-release_latest.generic_all.deb\napt install gnupg2 lsb-release .\/percona-release_latest.generic_all.deb\napt update\npercona-release enable-only ps-84-lts release\npercona-release enable tools release\napt install percona-server-server<\/code><\/pre>\n\n\n\n
in \/usr\/sbin\/mysqld.my put:<\/p>\n\n\n\n
{\n      \"components\": \"file:\/\/component_keyring_kmip\"\n}<\/code><\/pre>\n\n\n\n
in \/usr\/lib\/mysql\/plugin\/component_keyring_kmip.cnf:<\/p>\n\n\n\n
{\n  \"path\": \"\/var\/lib\/mysql-keyring\",\n  \"server_addr\": \"127.0.0.1\",\n  \"server_port\":\"5696\",\n  \"client_ca\":\"\/var\/lib\/mysql-keyring\/client.crt\",\n  \"client_key\":\"\/var\/lib\/mysql-keyring\/client.key\",\n  \"server_ca\":\"\/var\/lib\/mysql-keyring\/ca.crt\"\n}<\/code><\/pre>\n\n\n\n
# clean up mysql\nsystemctl disable mysql\nsystemctl stop mysql\n# yes, below means total data loss\nrm -rf \/var\/lib\/mysql\/*\ncd \/etc\/pykmip\/\nrm -rf \/var\/lib\/mysql-keyring\nmkdir \/var\/lib\/mysql-keyring\ncp \/etc\/pykmip\/client.crt \/var\/lib\/mysql-keyring\/\ncp \/etc\/pykmip\/client.key \/var\/lib\/mysql-keyring\/\ncp \/etc\/pykmip\/ca.crt \/var\/lib\/mysql-keyring\/\nchown mysql:mysql -R \/var\/lib\/mysql-keyring\/\n\n\n<\/code><\/pre>\n\n\n\n
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<\/p>\n\n\n\n
CREATE DATABASE test;\nCREATE TABLE test.t1 (\n  id int NOT NULL,\n  data varchar(255) DEFAULT NULL,\n  PRIMARY KEY (id)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y';\nINSERT INTO test.t1 VALUES(1,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');\n<\/code><\/pre>\n\n\n\n
it’s also worth encrypting other ‘moving parts’ where plain text data might be stored:<\/p>\n\n\n\n
scraps of information that helped with above: \/etc\/mysql\/mysql.conf.d\/tde.cnf<\/p>\n\n\n\n
[mysqld]\ndefault_table_encryption=ON\ninnodb_temp_tablespace_encrypt=ON\ndefault_table_encryption=ON\ninnodb_redo_log_encrypt=ON\ninnodb_undo_log_encrypt=ON\n<\/code><\/pre>\n\n\n\n