Encrypt columns data in postgresql database with pgcrypto

Question

I have a postgres database with some tables having columns containing some sensitive data, i used PGCRYPTO extension (pgp_pub_encrypt/pgp_pub_decrypt) to encrypt and gpg to generate public and private key pairs.

I have two questions :

• Where should i store this pair of keys knowing that i should use the same generated private key to decrypt a column ( i tried putting it in postgresql.conf file and that's not working)

• Can we generate key pairs for each session to make the system more secure?

Answer 1

The public key can be stored anywhere. You could store it in the database.

The private key belongs to your application. It should be stored on the application server, so that the application can supply it to decrypt the data in the database.