This Cybertec patch to PostgreSQL is currently the only implement out there to fully support transparent and cryptographically safe instance (cluster) level encryption, independent of operating system or file system encryption.
How does the encryption work?
The idea behind the patch is to store all the files making up a PostgreSQL cluster securely on disk in encrypted format (data-at-rest encryption) and then decrypt blocks as they are read from disk. This only requires that the database is initialized with encryption in mind and that the key used for initializing the database is accessible to the server during startup. The encryption-key can be provided in two ways – through an environment variable or through a special configuration parameter specifying a custom key setup command for implementing special security requirements.
For encryption 128-bit AES algorithm in XTS mode is used, sometimes called also XTS-AES. It’s a block cipher with a “tweak” for extra security and adheres to IEEE P1619 standard. The key needs to be provided to the server during every startup and when it doesn’t match, the server will refuse to start. Encrypted will be more or less everything – heap files (tables, indexes, sequences), xlog (as they also contain data), clog, temporary files being generated during execution of a larger query.
Performance penalty incurred by encryption/decryption depends heavily on concrete use cases. For cases where working set fits well into PostgreSQL shared buffers, it is practically negligible though.
PostgreSQL instance level encryption Download
Download this tool for free.postgresql-9.6.0-fde.tar
Q: What is actually being encrypted?
A: Everything except two things, pg_stat_statements extension data and temporary buffers for logical replication (when in use) – that is tables, indexes, transaction log (xlog), clog, temporary and unlogged tables, freespace and visibility map and TOAST. The two things left out are also possible but require some tinkering and were left out of the initial version as they’re not active by default anyways.
Q: What is the encryption method used?
A: Industry standard 128-bit XTS-AES block cipher.
Q: Can I use another encryption method?
A: Not out of the box. One should implement a couple of C routines for that. The interface itself is simple enough though.
Q: What is the expected performance penalty?
A: On a typical server system (if there is such), when database fits into shared buffers, then negligible (<3%), otherwise IO operations could be up to 2-3x slower. Future work will add hardware encryption support (using AES-NI) and is expected to reduce the overhead by almost an order of magnitude.
Q: Can I upgrade an encrypted database to a new major version?
A: Yes, given that the new cluster is initialized with the old encryption key.
Q: Is it possible to encrypt only certain tables/tablespaces to win on performance?
A: Currently no. It could be theoretically added later, but as XLOG will be anyways fully encrypted and all changes normally go through there, it probably wouldn’t be a silver bullet.
Q: Is it possible to change the encryption key for cases when it gets compromised.
A: Currently not, one should re-initialize a new cluster and dump/restore. However you can use the key setup command interface to implement an encrypted key store for the master where you can change the keys used to unlock the master key.
Q: Does it integrate with my HSM?
A: Not out of the box. There are two ways to integrate with a HSM. When caching the key in the database is acceptable, use the key setup interface to feed PostgreSQL the key from the HSM. For better protection against key theft from a working database server it’s possible to delegate all encryption and decryption to the HSM. A custom HSM specific C extension needs to be written for this.