[Durham] (no subject)

[Oliver Burnett-Hall]

David,

We kept on getting distracted when you were asking about databases and encryption at this week's meeting. So I thought I'd try and get my thoughts together in an email.

Please note, I'm not an expert at any of this, so take this as general, possibly flawed, advice. And I did mention that I've got a prejudice against MySQL that is probaly ten years out of date, but means that I always recommend using Postgres instead.

Encryption of certain columns within a database table is possible using the pgcrypto module. Alternatively you can use client side encryption/decryption. Either of these will theoretically mean that the data is more secure in that someone with access to the database server would not  be able to read the sensitive data.

In practice things are less clear. Storing encrypted data makes backups more fragile, and increases the potential for massive data loss if the encryption keys are lost. More importantly you will end up with difficulties making sure that the keys are secured adequately on the client side, if you want your application to be usable.

I'd suggest that you instead consider not encrypting the data and instead rely on database security -- roles and users -- to limit who can access the sensitive data. You can restrict access by  table, column and row. A malicious db admin will be able to read the data, but other than that you should be able to keep the critical data away from people who should not be able to see it.

-- 
— olly