How can I encrypt the PostgreSQL database at rest.
I could not find a good documentation on how can I achieve this ?
Add a comment
|
5 Answers
The threat model is very important in this case as encrypting a database efficiently is not an easy task, this can be done at 3 different layers (client-application, storage-device, data-base-itself) note that in all cases if the client application is compromised the encryption is useless, self data base encryption solution cover the least threat models as listed bellow.
- Malicious user steals storage devices and reads database files directly.
- Malicious backup operator takes backup.
- Protecting data at rest (persistent data)
Database encryption solution 1:
System disk encryption (cryptsetup/luks)... no modification are needed on the Postgres setup in this case. This also makes clog and textual log encrypted (at rest). If you are using a google cloud VM this guide may be useful.
Database encryption solution 2:
PostgreSQL TDE (transparent data encryption) this postgres feature implement transparent data encryption at rest for the whole database. an example is demonstrated here.
Database encryption solution 3:
Pgcrypto can be used to encrypt part of the database instead of a solution that would encrypt everything. an example is demonstrated here.
4 Comments
ogirginc
A word of caution for 2nd solution: TDE is targetted for PG-14. wiki.postgresql.org/wiki/…
intika
@ogirginc good to know :), i knew that it was a new feature but not as "alpha" as that...
sharadov
TDE is being offered by Cybertec - cybertec-postgresql.com/en/products/…
TomDogg
Percona oversees the development of the open-source TDE extension called "pg_tde", see: github.com/Percona-Lab/postgres-tde-ext
I also haven't found documentation about EncryptionAtRest for Postgres. People arriving here to learn more about EncrAtRest should check out AWS RDS or MongoDB Enterprise which offer this feature.
My reply is a warning for those following the "approved" answer! Saying "just use filesystem encryption" is ignoring the purpose of encrypting a database at rest. When you encrypt a filesystem this protects you from someone copying the drive backup or stealing the physical drive. Nothing else.
An attacker over the network has gained access to your mounted filesystem, and therefore it has already been decrypted to make it accessible to the OS, applications, etc.
4 Comments
Richard Huxton
An attacker with access to the filesystem your database is running on presumably has access to your database system itself already. What attack are you thinking of where that wouldn't be the case?
Falieson
The purpose of EncryptionAtRest is to protect against an attacker cloning your database. At Rest means that every field in the database is encrypted which defends against a database admin attack.
E.Serra
agree with your thoughts, though don't think you really provide an answer, also, the question is clear, How can I encrypt the PostgreSQL database at rest. Not should I encrypt the entire filesystem.
saolof
In enterprise or cloud environments it is quite common to run DBs on storage that may be outside of the server itself. Having the database encrypt data and loading the key into RAM from an external vault avoids the concern of the underlying filesystem/disk not being encrypted or its encryption keys not being shredded.
If you want to encrypt the entire database, just use filesystem encryption. You will want to encrypt transaction logs and database logs too presumably, so just encrypt the filesystems these reside on.
If you just want to encrypt a few columns then the pgcrypto module is the standard way to do this.
2 Comments
K.Pil
If I use pgcrypto , that means I would have to change the application for reading and writing . Wouldn't that be encryption at transit as I am encrypting and decrypt the data on the fly
Richard Huxton
You're encrypting and decrypting on the fly whatever way you do it (unless you only encrypt backups). Your choice is where you do that which can be anywhere between the server hardware and client application.
You can now download the open-source extension "pg_tde" being developed by Percona.
Comments
CREATE EXTENSION pgcrypto;
CREATE TABLE userinfo (username varchar(20), password bytea);
Inserting the data in an encrypted format:
INSERT INTO userinfo VALUES(' suman ',encrypt('111222','password','aes'));
select * from userinfo ;
1 Comment
Newt
This doesn't answer OP's question at all. And, I really hope whatever systems you are working on, you are not doing this for your user password storage. I hope that I'm not a user in your database :')