How does one implement column level encryption using Ecto/Phoenix with Postgresql? I understand pgcrypto extension has functions to support encryption/decryption, my questions are
How can I invoke those using Ecto syntax for a particular column?
How and where to supply the decryption key? This is most important. I am assuming decryption key will need to be supplied on the db connection made over TLS
Would like to hear your thoughts on this. Thanks in advance.
We’ve been using
https://hexdocs.pm/cloak_ecto/readme.html for this purpose. Has been working well for a couple of years now.
@opsb that’s very helpful
For a multi-tenant setting though it seems cloak_ecto won’t work as only one key can be defined
You don’t. Well, you can, but you shouldn’t use
pgcrypto, it is (almost) never good idea.
In 99% of the cases it isn’t something you want. Describe your usecase exactly to check if you need such solution at all.
Describe your usecase
For healthcare focused companies seeking HIPAA/HITRUST certification, big clients (hospitals etc) demand column level encryption as a last defense against any breach to protect
Protected Health Information (PHI) or Personally Identifiable Information (PII) like patient condition and other identifiers.
In that case encrypt data in application and transfer it encrypted over the wire. However remember that it will use the same key for all entries, so it will protect you only in case if the DB itself is breached (without breaching application). If application is breached, then there is nothing that will prevent reading that data.
In a multiple tenant situation can Cloak be configured at runtime to use a different key unique to that tenant?