Storing encrypted data in Postgres [closed]

Question

I have a requirement to store certain data in an encrypted form in Postgres. Obviously, I need to encrypt it, store it, and be able to read and decrypt it. What is the best way to do this?

Procyon

Your question is too broad. You can either encrypt/decrypt outside of postgres, or use the pgcrypto module to do the encryption in-server. In both cases you might want to use bytea columns or large objects for storage. – Melyndamem

I think the pgcrypto module is probably what I want. – Procyon

Too broad a question. – Desmoid

Answer 1

The best way is to do the crypto on the client or application server, so the database has no idea what the keys are and cannot decrypt the data. If the client / appserver are on a different host, all the better.

If your database is encrypting and decrypting the data for you, then it's vulnerable to having the keys stolen along with the database.

If you use pgcrypto's in-database crypto functions you can have the application send the key along with the data, which is at least somewhat helpful. It still risks having the keys exposed in the logs if a helpful sysadmin turns on aggressive statement logging or automatic plan dumping, though, and in the end if the keys are going to the database machine they're more vulnerable than if they're not. An attacker who takes control of the database machine can also change log settings, replace the postgresql binaries, or sniff traffic to capture keys and data this way.

If the appserver and db are on the same machine and managed by the same role(s) there's less point worrying about isolating them, and it may be sensible to just use pgcrypto.

Either way, remember to salt!

Antefix

Keep it on the server? A lot of people say don't put keys in the code? Do you mean just in a file some where? – Procyon

Yep, I'd keep it in a file. For extra paranoia you can keep the file on removable storage and have your app load it during app server startup, then remove the file so the key is only in RAM and not accessible on the HDD. The downside is that that requires admin intervention for app server restarts and recovery. For bonus frothing paranoid points you can use JNI / JNA to mlock() the memory the key is in to stop it getting swapped out ;-) and use a SecurityManager to limit inintended property access etc. Frankly, if an attacker is at that point you're already screwed, though. – Antefix

A slightly simpler approach is to keep the key in JECKS keystore and have the password for the keystore entered by an admin (preferable but requires admin intervention to restart), in the code (ick) or hidden away in a non-obvious file somewhere (weak but better than an unencrypted key). – Antefix

Actually, I think a better approach is to have the key in stored procedures. From there give your app (or whatever you are afraid will be stolen or read or decompiled) a database login that can run the procedure but can't view it. (You must remove remove SELECT permission on pg_proc.) – Procyon

I don't agree with that one Joe - if it's in a stored proc it'll appear in cleartext in your backups. Backup loss or theft is one of your big points of vulnerability. That also won't help with unauthorized access to the DB via a privileged account (superuser) that can see pg_proc anyway. – Antefix

For later searchers: Also asked on the PostgreSQL mailing list archives.postgresql.org/message-id/… – Antefix