PostgreSQL: Encrypt Column With pgcrypto

Question

I need to encrypt some columns in a PostgreSQL 9.6 database. The data being encrypted is inherently sensitive; however, the data are not passwords or other authentication credentials. This data will need to be decrypted for statistical analysis and consumption by users.

After reading several questions and answers:

• Storing encrypted data in Postgres
• https://dba.stackexchange.com/questions/24370/how-to-use-aes-encryption-in-postgresql
• https://dba.stackexchange.com/questions/59942/secure-postgresql-database-encryption

... and considering these comments:

... it seems the biggest problem with using the pgcrypto module is the storage of keys in the same database.

This begs the question:

Is it consistent with best practices to store the key in a different database and access it via a foreign data wrapper, such as Postgresql_FDW?

Answer 1

Secret storage is a common issue when using crypto mecanisms.

pgcrypto does not povide key storage, you are free to store the key where you want and protect it as you can.

Storing the key in another database, if managed by the same DBA does not provide much security as DBA may access it the same way.

Ideally, you would store the key in a secure vault and request it from your application in order to construct the queries. It will still be visible from DBA while the request is running through select * from pg_stat_activity.

You may set the key for a SQL session wide use through set session my.vars.cryptokey = 'secret'; then use it into your queries with the following syntax : current_setting('my.vars.cryptokey')::text

To be (almost) transparent from the application point of view, PostgreSQL rules may help for translating secure_column to the call to decrypt function with the session stored key. For inserting, a pre-insert trigger would be required.