Postgres Pro
Downloads
Home   >   mailing lists

Thread: Application Level Encryption

Application Level Encryption

From
Zahir Lalani
Date:

Hello

 

Does anyone have any serious experience of implementing app level encryption with strong key management?

 

If so would like to arrange an offline chat please

 

Z

Re: Application Level Encryption

From
o1bigtenor
Date:


On Sun, Jul 5, 2020 at 1:22 AM Zahir Lalani <ZahirLalani@oliver.agency> wrote:

Hello

 

Does anyone have any serious experience of implementing app level encryption with strong key management?

 

If so would like to arrange an offline chat please


Others might be interested as well. 
Might be useful to have specific questions if a knowledgeable individual did appear. 

Regards 

Re: Application Level Encryption

From
"sivapostgres@yahoo.com"
Date:
HFSQL - Not a so popular database allow us to encrypt and password protect every tables [ they term Tables as Files ].  Without password those tables could not read even through HFSQL management centre [ like pgadmin]. 

May be such a facility you refer ?  


On Sun, Jul 5, 2020 at 5:16 PM, o1bigtenor
<o1bigtenor@gmail.com> wrote:


On Sun, Jul 5, 2020 at 1:22 AM Zahir Lalani <ZahirLalani@oliver.agency> wrote:

Hello

 

Does anyone have any serious experience of implementing app level encryption with strong key management?

 

If so would like to arrange an offline chat please

Others might be interested as well. 
Might be useful to have specific questions if a knowledgeable individual did appear. 

Regards 

Re: Application Level Encryption

From
Michel Pelletier
Date:
Hi Zahir,

pgsodium is a new-ish encyption extension built around the libsodium encryption API.


It supports calling a script to load a hidden key in memory and use that key to derive other keys.  There's an example shown in the documentation.  I'm working on support for the Zymkey hardware security module, as well as support for the AWS key management API.

-Michel

On Sat, Jul 4, 2020 at 11:22 PM Zahir Lalani <ZahirLalani@oliver.agency> wrote:

Hello

 

Does anyone have any serious experience of implementing app level encryption with strong key management?

 

If so would like to arrange an offline chat please

 

Z

RE: Application Level Encryption

From
Zahir Lalani
Date:

 

 

From: Michel Pelletier <pelletier.michel@gmail.com>
Sent: 05 July 2020 17:00
To: Zahir Lalani <ZahirLalani@oliver.agency>
Cc: pgsql-general@postgresql.org
Subject: Re: Application Level Encryption

 

Hi Zahir,

 

pgsodium is a new-ish encyption extension built around the libsodium encryption API.

 

 

It supports calling a script to load a hidden key in memory and use that key to derive other keys.  There's an example shown in the documentation.  I'm working on support for the Zymkey hardware security module, as well as support for the AWS key management API.

 

-Michel

 

 

Thx all

 

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

 

Hope that makes sense

 

Z

 

Re: Application Level Encryption

From
Michel Pelletier
Date:
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

 

Re: Application Level Encryption

From
Sam Gendler
Date:


On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier <pelletier.michel@gmail.com> wrote:


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.

Watch out for KMS api quotas if you go that route.  Their docs don't state what the default quotas are, so you have to go to your quotas page in the console to find out, but they likely aren't very high and might well be exceeded by the transaction rate on even a relatively small db instance.

 

Re: Application Level Encryption

From
Michel Pelletier
Date:


On Sun, Jul 5, 2020 at 3:23 PM Sam Gendler <sgendler@ideasculptor.com> wrote:


On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier <pelletier.michel@gmail.com> wrote:


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.

Watch out for KMS api quotas if you go that route.  Their docs don't state what the default quotas are, so you have to go to your quotas page in the console to find out, but they likely aren't very high and might well be exceeded by the transaction rate on even a relatively small db instance.

Thanks for pointing that out, it's true that it's a limited route with cloud KMS.   If you control the device like a Zymkey in a secure enclosure, the cost is minimal, although the key derivation rate is very slow.

-Michel


 

RE: Application Level Encryption

From
Zahir Lalani
Date:

From: Michel Pelletier <pelletier.michel@gmail.com>
Sent: 05 July 2020 23:32
To: Sam Gendler <sgendler@ideasculptor.com>
Cc: Zahir Lalani <ZahirLalani@oliver.agency>; pgsql-general@postgresql.org
Subject: Re: Application Level Encryption

 

 

 

On Sun, Jul 5, 2020 at 3:23 PM Sam Gendler <sgendler@ideasculptor.com> wrote:

 

 

On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier <pelletier.michel@gmail.com> wrote:

 

 

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.

 

Watch out for KMS api quotas if you go that route.  Their docs don't state what the default quotas are, so you have to go to your quotas page in the console to find out, but they likely aren't very high and might well be exceeded by the transaction rate on even a relatively small db instance.

 

Thanks for pointing that out, it's true that it's a limited route with cloud KMS.   If you control the device like a Zymkey in a secure enclosure, the cost is minimal, although the key derivation rate is very slow.

 

-Michel

 

**

Thank you for the explanation – that makes sense, but I need to read the docs to understand better. Any suggestions on how people usually deal with reporting in this scenario, considering off the shelf tools don’t usually have this mechanism?

 

Z