Column level encryption (PostgreSQL)

Questions / Help
,
#1

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

  1. How can I invoke those using Ecto syntax for a particular column?
  2. 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.

#2

We’ve been using https://hexdocs.pm/cloak_ecto/readme.html for this purpose. Has been working well for a couple of years now.

5 Likes
#3

Thanks @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

#4

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.

#5

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.

#6

Ok, fair.

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.

#7

In a multiple tenant situation can Cloak be configured at runtime to use a different key unique to that tenant?