Encrypt data in postgresql

[seamustuohy]

We need to be encrypting data we store in the SQL database. Because we currently use postgresql we can use the pgcrypto module to do this. Some points on the roll-out.

• We should start with a more immediate implementation where the API server uses symmetric encryption using an encryption password that is stored as a env-variable on the API server. This would force an adversary to have to compromise both the API and the database to get access to the data.
• A longer term option we discussed was leveraging pgcrypto's support for public key encryption to limit the APIs ability to examine data being stored in the database. The private key could be provided to the user in a variety of ways (i.e. within the users auth-token). The public keys for users could be provided to the web-app by the API based upon the users actions. This could protect the database against privilege escalation attacks as the API and database would not have access to the private PGP keys required to decrypt the requested data. This type of implementation still needs to have its design examined as it would require restructuring the current design of the tool. This restructuring also impacts the security model because it would remove the APIs ability to inspect data submitted by the user.

Sorry for taking so long in getting this moved into the issue queue. When I was telling @mrphs that we didn't currently encrypt the database I tried to point him to the issue that showed it in our roadmap. It was only then that I realized that I never moved it to the public issue queue!

[mrphs]

Any update on this? I don't feel comfortable suggesting use of this platform if the data at rest isn't being encrypted, while it's hosted on 3rd party servers and backup/data-retention policies are not exactly clear.

[AurangZ]

We are working on it. We'll update this ticket and close it once it's fixed.

[MayaAvaz]

The database itself is now encrypted via the solution offered by host service.

[mrphs]

Hey @MayaAvaz can you be a little more specific about this solution?