Hi in pg_tde, is it possible to set up eviction so keys are evicted from memory after a certain amount of time (or maybe inactivity or no connections) and required to be reloaded to use that db again? Or even not cache keys at all and every query requires the key be sent.
My use case is I need a different db with a different key per tenant, but if a tenant revokes their key from the application, the db for that tenant should be unusable. The problem is the db remains ‘unlocked’ until restart, so tenant data is still accessible, even if they revoke their key in my application.
Additionally, is it possible to not use a kms vault or a local file, and instead unlock db’s using an sql admin function over a db connection? Or even a key in the connection string? I figure I can revoke the key from the vault in my application, but it would be a little easier if I could just unlock db’s from my application directly with the key, so I don’t need to use a vault at all (I get each tenants key from another service).
1. Evicting keys or requiring a key on every query
Each database has a principal key, which lives in the key provider. That key encrypts the internal relation keys, which are stored under $PGDATA/pg_tde/. When the server needs a principal key, it fetches it once from the provider and then keeps it in memory. There’s currently no time-to-live, idle timeout or “evict key” function. You also can’t make pg_tde fetch the key on every query.
That means revoking a key in Vault or KMIP doesn’t lock a running database. It only takes effect the next time the server has to fetch the key again, which in practice means after a restart. Once restarted, a database whose key was revoked can’t be decrypted, and its data on disk is effectively shredded.
Keep in mind what TDE protects against. It’s encryption at rest: it guards the data files, backups and stolen disks. It isn’t access control on a live server. Anyone who can connect, including a superuser, reads plaintext.
2. Unlocking with a key passed over SQL or in the connection string
This isn’t supported. Keys have to come from a configured key provider: a local file, HashiCorp Vault or OpenBao (KV v2), or a KMIP server. There’s no SQL function that takes the raw key material, and no connection-string parameter for it.
You could point a file provider at a file your application writes. But the key still gets cached once it’s loaded, so this doesn’t fix the revocation problem. It also means keeping key material on the database host, which we don’t recommend for production. Using Vault or KMIP and revoking keys there is the supported approach.