CODEWITHSUNDEEP
postgresqlencryption
K.Pil
K.Pil

Reputation: 986

PostgreSQL Database encryption at rest

How can I encrypt the PostgreSQL database at rest.

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

Upvotes: 47

Views: 72428

Answers (5)

TomDogg
TomDogg

Reputation: 3937

You can now download the open-source extension "pg_tde" being developed by Percona.

Upvotes: 4

user1997628
user1997628

Reputation: 7

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 ;

Upvotes: -1

intika
intika

Reputation: 9742

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.

Upvotes: 44

Falieson
Falieson

Reputation: 2556

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.

Upvotes: 31

Richard Huxton
Richard Huxton

Reputation: 22893

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.

Upvotes: 13

Related Questions