31

How can I encrypt the PostgreSQL database at rest.

I could not find a good documentation on how can I achieve this ?

K.Pil
  • 786
  • 2
  • 10
  • 24

4 Answers4

31

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.

intika
  • 8,448
  • 5
  • 36
  • 55
  • 2
    A word of caution for 2nd solution: TDE is targetted for PG-14. https://wiki.postgresql.org/wiki/Transparent_Data_Encryption#Scope_for_the_first_release_of_TDE – ogirginc May 18 '20 at 09:13
  • @ogirginc good to know :), i knew that it was a new feature but not as "alpha" as that... – intika May 18 '20 at 09:21
  • 1
    TDE is being offered by Cybertec - https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/ – sharadov Oct 15 '20 at 17:47
24

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.

Falieson
  • 2,198
  • 3
  • 24
  • 35
  • 8
    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? – Richard Huxton Jan 11 '19 at 08:36
  • 1
    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. – Falieson Jan 11 '19 at 17:06
  • 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. – E.Serra Jun 21 '19 at 13:09
9

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.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • 1
    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 – K.Pil Aug 23 '17 at 22:11
  • 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. – Richard Huxton Aug 24 '17 at 06:14
1
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 ;
Fedor
  • 17,146
  • 13
  • 40
  • 131
user1997628
  • 11
  • 1