Postgres Pro
Downloads
Home   >   mailing lists

Re: Application Level Encryption - Mailing list pgsql-general

From Michel Pelletier
Subject Re: Application Level Encryption
Date
Msg-id CACxu=vLuHL10NLTQ1LfEmyqGXq4AbxBR8-2Q+CevMrFT7aDc5Q@mail.gmail.com
Whole thread Raw
In response to RE: Application Level Encryption  (Zahir Lalani <ZahirLalani@oliver.agency>)
Responses Re: Application Level Encryption
List pgsql-general
Tree view
On Sun, Jul 5, 2020 at 10:14 AM Zahir Lalani <ZahirLalani@oliver.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

 

pgsql-general by date:

Previous
From: Zahir Lalani
Date:
Subject: RE: Application Level Encryption
Next
From: Sam Gendler
Date:
Subject: Re: Application Level Encryption