Encryption with pgcrypto

Question

I am working on encrypting a column in a postgre table using pgcrypto. The postgre version is 9.5.1. I have figured out how to encrypt using pgp_sym_encrypt() and decrypt using pgp_sym_decrypt(). My problem is figuring out how to keep the key hidden.

I am trying to avoid having the application decrypt the data that is returned. The data in this table is only sensitive if someone can view the entire table (450,000 rows) and put the data together, so its ok for the data to return to the application decrypted.

In pgAdmin, I have this in my query

 SELECT pgp_sym_decrypt(column1,'password')

Obvisouly, I don't want the query template our application uses to have the password stored in it and then pass it in everytime a query is ran. Any ideas would be appreciated?

Answer 1

There is no way to avoid the drawbacks of symmetric encryption on the server.

PostgreSQL has to have access to the key and the encrypted data to decrypt it if you want to "avoid having the application decrypt". There are only two sources that can do it.

1. the consumer
2. the database (in which case it's also subject to being written in a log file)

Of course, if you have a single access point to the data like an webserver or the like, you could potentially decrypt there too and keep the keys off the server.

Answer 2

So, I'm curious, what did you end up doing?

Converting comment to answer, to satisfy said curiosity ... :)

---

Unless Ii misunderstood:

Assuming application has row level privileges on that table, you can use

SELECT pgp_sym_decrypt( column1, get_pswd(pkey) ) 

where get_pswd(pkey) would select the password for that particular pkey row.

And you can store each row's pkey and password in a separate table which the application doesn't have select privileges on.