Re: Application Level Encryption

On Sun, Jul 5, 2020 at 10:14 AM Zahir Lalani <ZahirLalani(at)oliver(dot)agency>
wrote:

>
>
>
>
> So what Michael has posted above is actually the target. We are hosted in
> Google Cloud and have been told that we need to use a key manager outside
> of PG (Google have KMS) and that it must have a master key which is rotated
> regularly. We are having a debate about what to encrypt – “it must encrypt
> our data” – we are multi-tenanted and also we have data that is not client
> facing in each tenant. I worry about applying app level to all data for
> sheer performance reasons.
>
>
>
> We have suggested we only encrypt what is truly client data so that we do
> not have to refactor everything.
>
>
>
> The other challenge we have is the external reporting tools we use – none
> of these will work as, and we cannot pass them the unencrypted data.
>
>
>
> So I wanted to understand approaches that could be taken and how to
> minimise performance impacts and how to manage the use of 3rd party tools
>

pgosodium currently supports the following approach: you store your Data
Encryption Key (DEK) on the local filesystem that is encrypted by the Key
Encryption Key (KEK) stored in google's KMS. When the server boots,
pgsodium calls a script "pgsodium_getkey" that can decrypt the DEK via
googles REST API (you would have to supply this script), this decrypted key
is then stored in server memory, but is not accessible to SQL
non-superusers (it is possible with contortions for a superuser to get the
key depending on how you set it up). The decrypted DEK is then used to
"derive" keys used by the application by key id. You only ever store the
key ids, never the keys. You can now encrypt and decrypt data with the
given key id. Keys also have an 8 byte "context" so key 1 in context
"tenant01" is different from key 1 in "tenant02". You can derive up to
bigint keys per context, so you can use a different key id for every row of
data, if you want to go that far. There's an example of that in the docs.

Further steps can include deleting the getkey script and the stored
encrypted DEK after server startup. If the server reboots you must
orchestrate how to place those back for startup to procede.

Note that any extension can always access all server memory, so be careful
what you install.

I'm working on an approach where the decrypted DEK only lives for the
lifetime of a transaction, this means hitting the kms on every transaction
that uses keys. It will be slower, but the time the decrypted key stays in
memory would be minimized.

-Michel

>
>
> Hope that makes sense
>
>
>
> Z
>
>
>