this is reminder, mostly for myself, how to issue certificates that later can be used with PostgreSQL, MySQL to access Key Management Service [KMS] via Key Management Interoperability Protocol [KMIP] to store & retrieve encryption keys used in Table Data Encryption [TDE].
whenever i refer to PostgreSQL or MySQL in this post – i actually refer to Percona Server for MySQL 8.4.6 and Percona Server for PostgreSQL 17.7 + pg_tde.
why + problems i have with this
i had to implement TDE for MySQL, PostgreSQL; for databases that are already stored on fully encrypted drives. i feel that in my particular context TDE was an overkill – introducing unnecessary complication and risk, without giving any significant security benefits.
TDE protects data in scenario where attacked has access to disk level data from given database server.
TDE does not protect against scenarios in which attacker:
- gains logical access to a running database server – then they can dump encryption keys from memory, or use local commands like pg_dump, mysqldump to extract decrypted data,
- gets hold of valid database credentials and can access server over the network – then they can use database clients or dump tools to extract the sensitive data.
nevertheless – i had to implement it anyway.
architecture
MySQL or PostgreSQL with the pg_tde extension can act as KMIP clients and communicate with the KMS:
- use two way TLS – authenticate, using locally available client certificate, client key files towards the KMS, at the same time ensure it’s the real KMS talking to us by checking response against certificate authority certificate file,
- initially store, later retrieve the secret from KMS; use that secret to decrypt/encrypt database files stored in the local file system,
- periodically rotate keys – store a new secret in KMS
Thales CipherTrust Manager – setting up access for KMIP client
disclaimer: based on instruction i’ve received from 3rd party company supplying the KMS service. i don’t know exact ‘whys’ behind those steps.
for every server / service that will act as KMIP client do those steps:
1. from the front page of the Thales CipherTrust Manager – go to access management > users, click + add user and create a new account. provide:
- username – i’ve used naming nameofserver-nameofservice e.g. swedb5-mysql
- complex password; in practice unused
- un-tick all types of access –
2. go to access management > users, find the newly created user, click on it and select group membership, click add group and add key users.
3. go to the root of the CipherTrust Manager > KMIP, client profile and click add profile. provide:
- profile name – i’ve used the same naming convention as in point 1, although this time its profile name and not user name
- certificate duration expressed in days; assign it to the desired KMIP certificate rotation frequency. let’s say 365 days,
- certificate details – common name – this one is complex. i was told to use name in format {name-of-cmt-domain}|{name-of-cmt-domain}||name-of-profile; for domain called c1234-kms567 and profile swedb5-mysql – it’ll be c1234-kms567|c1234-kms567||swedb5-mysql
4. go to CipherTrust Manager > KMIP > Registration token, click new registration token and click through the wizard:
- name prefix – i got one from KMS provider c1234, rest unchanged, select ca
- select ca – in my case it was CA provided by the KMS supplier,
- client profile – select one created in the step #3 above
- click create token, copy token to the clipboard
5. go to the CipherTrust Manager > KMIP > registered clients, click add client and provide:
- name – same as user name in the step #1, for me swedb5-mysql
- registration token – copied at the end of the step #4
- click save, fetch the certificate – client.crt, private key – client.key; we’ve got ca.crt provided by the 3rd party selling us KMS service.