6

Looking over the docs, I didn't see any mention of row-level encryption.

Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with row-level encryption in postgres? Failing row-level, what's the next best thing I can do, and what are the drawbacks?

4
  • What do you want to achieve with row-level encryption? For joins, etc. to work PostgreSQL needs to see the key columns. You can very well encrypt the data columns though (if the inter-table relations are not so secret) – jkj Jun 30 '11 at 16:06
  • 1
    Not that it would make a difference in this case, but you are linking to the PostgreSQL 8.0 documentation, which is more than 6 years old. – Peter Eisentraut Jul 2 '11 at 16:21
  • What do you mean by "row-level encryption"? – Jack Douglas Jul 3 '11 at 19:00
  • @Jack Douglas technet.microsoft.com/en-us/library/… – Incognito Jul 7 '11 at 13:52
5
+50

Usually, you don't want to do this. Have you given second thoughts to this?

Else, pg_crypto is your friend. But as the above-mentioned slides suggest, it isn't necessarily a panacea.

0
4

Things that come to my mind:

  1. use the contrib module pgcrypto to encrypt the column contents manually (will require a change to your application)
  2. Store the data on an encrypted volume/partition (this can be done on table level using tablespaces that are located on the encrypted volume)
1

You'll probably be better off encrypting and decrypting the data in the application tier not in the database directly. All you need to do at that point it make sure that the database columns are wide enough to support the new longer values.

Not to be a sales pitch, but I talk about the concepts in Chapter 2 of my book Securing SQL Server. There's sample code in the book for .NET on how to encrypt and decrypt data. If you aren't using .NET the samples won't be of much use, but the concepts will be the same.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.