There are a couple things to consider when encrypting data, whether it is important to encrypt data at rest or data in motion. Then there is more than one way to address either of those situations. Some open source and PostgreSQL specific solutions are highlighted here.
There are different considerations when encrypting data depending on whether the data is at rest or in motion. Depending on your requirement, you may need to implement multiple solutions to cover each scenario as PostgreSQL does not natively support transparent data encryption.
Data at Rest is when information is stored on media. Encrypting data at rest lowers the risk of data being accessed from theft of the media.
Data in Motion is when information is being transferred to and from the media.
The primary technologies for handling data at rest are encryption at the block or filesystem level. Both of these technologies protect the data when the file system or block device are not mounted by the operating system, or while power to the devices is off. When the devices and filesystems are mounted by the OS, anyone with access to the system have access to the data unencrypted if there are no other measures in place. The following options are primarily for Linux based operating systems, although some of the following may work with other OSs.
The main advantage of LUKS is transparency (no changes to application needed) and minimal overhead. It only protects against theft of media (or whatever the equivalent of that is in cloud environments). Based on our experience, transparent filesystem-level encryption (using LUKS) should have very limited impact (perhaps 1-5%) as long as two conditions are met:
- The CPU supports AES-NI
- The kernel supports multi-threaded encryption.
This is similar to LUKS, but is implemented by the filesystem and the key is stored in a keyring specific to session or user. This addresses the 'copy data directory' threat (the data will be encrypted, unless the user has access to the right keyring). Performance-wise it should be very close to LUKS, and it's about as transparent for the database. It's however a fairly recent kernel feature (added in 2015/2016), so it would require additional testing and it's specific to ext4.
- primarily tested on EXT3, EXT4, and XFS
- allows per-file encryption policies
- no support for very long filenames
- works on network filesystems
There are a few different techniques for protecting data in motion. The following options are readily available with PostgreSQL.
Data can be encrypted across the network by using SSL connections between a database client and the database itself. PostgreSQL can be configured to only allow SSL connections.
PostgreSQL has an extension called pgcrypto that provides user-defined functions that can encrypt and decrypt data. While this extension protects data that is inserted into a table, the data was still initially sent unencrypted to the database. Similarly, if the data needs to be viewed unencrypted by the client, it will be decrypted on the database system before being sent back to the database client.
Another option that is not dependent on PostgreSQL is to have the database application or client encrypt data before sending sensitive information to the database. Similarly, the application would also manage decrypting data in the scenario.
Some systems (particularly those aiming for PCI-DSS and HIPAA) choose a very different path by encrypting sensitive data at the application level, so that the database never sees the plaintext version. This is similar to pgcrypto in many ways, but it eliminates the risk of leaking the data into server log as part of error messages, the database does not need to care about managing encryption keys at all and so on. It still has performance impact because the encrypted data has to be stored as bytea, which may be more expensive than the native data type (e.g. integer). It also affects indexing, similarly to pgcrypto.
- Additional considerations are covered in this 2ndQuadrant blog about Databases vs. encryption.
- PostgreSQL documentation notes on encryption.