4

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?

0

2 Answers 2

2

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.

0

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.

3
  • 1
    If the application doesn't have select privilege on the table. then you can't run the function that has access to that table from the application. Moreover, if the database is compromised, then someone can still "view the entire table " Nov 6, 2017 at 23:04
  • @EvanCarrollQWERHJKL I created the get_pswd(pkey) as "admin" with security definer and another "application user" which has execute privileges on get_pswd(pkey). And totally agree with the compromised solution.
    – amacvar
    Nov 6, 2017 at 23:27
  • I did what I didn't want to do at first and got our application developers involved. Most likely our solution will be encryption at the app level. I appreciate the input.
    – Jason
    Nov 7, 2017 at 22:33

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.