Recently at Stormatics, we implemented a PostgreSQL cluster for a client that was set up with EDB’s Transparent Data Encryption (TDE). There was quite a learning curve involved, and so we thought others might find some discussion of this topic worthwhile.
It is worth noting that TDE from EDB is similar but different from the TDE patches that have been proposed to mainline Postgres. There are similarities but also differences, particularly regarding key management. This post covers EDB’s solution and also provides a few notes about what has been proposed for community Postgres, as I have been involved in advocating for the inclusion of TDE in Postgres and have been involved in review of these patches.
This document aims to give a high-level overview of what TDE is, how it is implemented in EDB products as well as the pending mainline patch set, as well the high-level process pieces that need to be in place to use TDE effectively. This is a document to read during the planning phase rather than a walk-through of installation. Here planning is far more complex than the setup.
This post assumes a general mastery of PostgreSQL, that you know the general parts of the system, the tools for initializing and backing up databases, and that you are comfortable administrating midsized PostgreSQL instances.
What is Transparent Data Encryption and What Problem Does It Solve?
By default, PostgreSQL stores data (tables, indexes, etc) in files which are unencrypted. These files can then be read or written without special considerations. This also means that if the physical storage or the database backups are compromised, or copies of backups taken, that sensitive data could be compromised and stolen. This could include sensitive personally identifying information (PII) or sensitive financial information (such as that required for periodic credit card charges). In many cases, companies have a legal duty to protect this data against compromise.
With TDE enabled, PostgreSQL writes these files by first encrypting the blocks, and decrypts them when reading using symmetric encryption. The encryption key is held in memory when the database is running and stored somewhere in an inaccessible form for retrieval when the database starts up. Because the key is stored in a way that is inaccessible, copying the data files is insufficient to access the information in them. Similarly copying a full backup of the database is not enough because one also needs the key which requires additional intervention to open.
TDE is available on EDB’s Postgres Extended and Advanced Server products, and there is work ongoing in getting Community Postgres to work with it. The implementations are subtly different in terms of key management.
More from the Blog: Unlocking Secure Connections: A Guide to PostgreSQL Authentication Methods
How Does Transparent Data Encryption Work?
All current implementations of TDE for PostgreSQL work on the same basic principal though they differ in key details. We will discuss the EDB patches first and then the proposed patch set to Postgres. Understanding these implementations is important because the community patch set provides some best practices which EDB’s implementation does not support out of the box (though these are possible to implement with some work).
EDB’s TDE Implementation
When EDB’s TDE products are used, a key is created at initdb time, and then this is “wrapped” to make it inaccessible to Postgres. This process can include storing the key elsewhere, such as via Hashicorp Vault, or encrypting it with a password using AES encryption.
The key is then “unwrapped” to make it accessible, by reversing the “wrapping” process. This could be done by fetching the key from the key management system or by decrypting it from the filesystem. The key is then loaded into memory and used.
The data encryption key cannot be rotated without dumping and restoring the database.
The key, once loaded, is used to decrypt file pages on read and encrypt the same on write as noted above. Since the key is inaccessible, copying a backup to another machine will not allow PostgreSQL to read the backup.
TDE here is difficult to set up and reason about. Key management is a complex field and if a password is needed to start PostgreSQL, this has significant implications in terms of automation and PostgreSQL restarts.
The Commitfest Submission
The commitfest submission provides more key management out of the box. Here a data encryption key is generated, and so is a key encrypting key. The Key Encrypting Key is expected to be stored outside of where PostgreSQL can access the file directly. This abstraction makes it far easier to manage keys in a secure fashion, although with some effort a similar approach could be used in EDB’s solution above.
There is one important addition in this community submission which is not reproduced in EDB’s current version and that is the use of a separate WAL key and data relation file key. The separation of these keys in the community submission is intended to allow an eventual data key rotation by allowing replicas to share a WAL key and have different file relation keys. In a way, key rotation could be done with database switchovers.
The community patch set, while not available yet, promises to be more complete and allow better key management than the products currently on the market. However, if you cannot wait, the commercial options such as EDB’s implementation are available for use today.
Another significant difference is that the community submission has no concept of key unwrapping. Instead this is handled through the key encryption key framework and as such it is far more seamless, and doesn’t require as much detailed shell knowledge to make work. The tradeoff is that key management itself is less flexible but allows for fewer oversights.
The community submission optionally allows for the key encrypting keys to be generated from passphrases but this is optional.
The commitfest submission is still in review as of the time of this writing without a clear timeline for being included. This will get into PostgreSQL no earlier than version 17, and could take even longer.
What Kinds of New Problems does TDE Pose?
While TDE is a very powerful tool in the protection of personal and financial data, it also poses significant risks and challenges in implementation and operation. These problems either involve key management, including provisions for missing keys, and how operational tasks are complicated by the need to have a key separated from the data.
Key Management Problems
In both EDB’s approach and the pending patch set, some sort of encryption key needs to be stored separately from PostgreSQL. This poses a number of additional operational requirements in terms of managing the keys themselves in order to ensure that disaster recovery and other emergencies are survivable.
The first problem is that it is important that the relevant keys are properly backed up somewhere if a key management system is used and it goes down. If the key management system goes down, then the database will not be able to be started without an additional key. Additionally, this means the key has to be backed up separately since otherwise backups may not be usable.
The second problem is that key rotation may be required in some high security environments and understanding how to rotate keys is a complex topic in both implementations. This requires planning. For EDB’s approach this requires changing a wrapping encryption mechanism, since the underlying data storage keys cannot be rotated. If this is required, then migration to a new instance, possibly through logical replication, is required to rotate keys.
Key management is a complex field and no PostgreSQL TDE implementation covers this. This is something that requires careful planning by any organization using TDE in production.
More from the Blog: The Beginner’s Guide to PostgreSQL Database Security Hardening
Operational Problems
TDE also provides significant operational overhead in ways which cannot be easily eliminated. These boil down to the fact that this complicates the infrastructure and/or process components needed to start the database, and that these complicate backup and restore procedures.
Starting and Restarting PostgreSQL
If passwords are used, then starting PostgreSQL requires human intervention. This often makes it undesirable or impossible to start PostgreSQL at system start. This further means that when a database system restarts, someone actually has to log in and start PostgreSQL, presenting the password to unlock the key.
Similarly if a key management system is used, then PostgreSQL cannot be started unless that key management system is operational. Key management systems often have additional operational requirements also such as proper SSL certificates simply because they are security-critical pieces of infrastructure. These additional requirements can add important failure cases to consider (such as PostgreSQL being unable to start when the key management system’s X.509 certificate expires). Many organizations which require TDE have infrastructure already for managing these sorts of problems but some do not and here the burden may be significant.
Backup and Restore of Databases
TDE separates the keys needed to read data files from the data itself, meaning that backups are no longer sufficient to recover from a disaster. The keys must be backed up separately and must be protected separately. This can be done through backups of the key management system or with a proper password manager if passwords are used, or it can be done by taking a key encrypting key, encrypting it with the public keys of designated recovery agents and handing it to them for proper storage.
In the case of EDB’s implementation the approach I will advocate below suggests storing the password-protected key in a backup location before loading the unwrapped key to a key management system. This allows one to reconfigure and start PostgreSQL with a backup, password-encrypted key in the case of an emergency.
Loss of access to the key will mean that PostgreSQL can no longer start if shut off, and that backups can no longer be restored. Consequently, backups of the key are critical.
If a key is lost, the only remedy is to take a logical backup of the running database and load it into another database.
The Alternative to EDB TDE
TDE is not the only approach to protecting data at rest, but it is probably the easiest to work with. If one is not using EDB’s software, the best bet right now is to manually encrypt everything at rest. In practice, this usually means:
- Use of LUKS on Linux to encrypt the underlying storage at the block device level. Tools on other operating systems may vary
- Use of encrypted storage on the backups. This could also be a LUKS-encrypted volume on Linux or another encrypted filesystem in another operating system. One could also use a tool like pg_backrest which can encrypt backups.
- Use of enforced SSL for all backup processes. While SSL is usually a requirement for logical interactions with the database (including logical replication and executing queries), backups in a TDE environment don’t benefit as much since the data files and write-ahead logs are already encrypted.
This approach works, but it is a lot more complex, and you end up with even more operational headaches than you do with TDE approaches described here. Consequently I have been an advocate for getting TDE into mainline Postgres as this eliminates some of the operational complexity that is involved in running encrypted database systems.
Some General Guidelines For Key Management
I would like to finish this article by offering some general guidelines for key management simply because the strategies taken for this are not necessarily straightforward. These recommendations only apply to the use of EDB’s implementation simply because the mainline patchset hasn’t been committed yet.
My first recommendation is to set up TDE with passphrase encryption first, then back up the key and move to a key management system. Document the configuration file changes needed so that in an emergency you can start with a passphrase-protected key. This process should be documented and designed around making it as easy to do as possible in an emergency.
Use a key management system for production so that you can safely automate failovers. This means unwrapping the key and storing it in a key management system, such as Hashicorp Vault.
Keep the backup key in a safe place. Optionally you can encrypt the wrapped key by encrypting the wrapped (i.e. passphrase-encrypted) key with a designated recovery agent’s public key. This can help protect the encryption key, and by including the wrapped key, recovery doesn’t require re-encrypting the key.
These guidelines will save you a lot of operational headaches. And in the end, you will likely find TDE to be a useful tool in protecting databases that have sensitive data.
