35

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?

Improve this question
3
  • 8
    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. Commented Nov 8, 2011 at 17:36
  • I think the pgcrypto module is probably what I want. Commented Nov 8, 2011 at 17:40
  • 1
    Too broad a question. Commented Jul 16, 2014 at 11:13

1 Answer 1

Reset to default
47

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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.
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).
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.)
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.
For later searchers: Also asked on the PostgreSQL mailing list archives.postgresql.org/message-id/…
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.