Application Level Encryption

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

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 ?  

Sent from Yahoo Mail on Android

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 

Hi Zahir,

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

https://github.com/michelp/pgsodium

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:

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.

https://github.com/michelp/pgsodium

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

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

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.

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

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<mailto:sgendler@ideasculptor.com>> wrote:

On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier <pelletier.michel@gmail.com<mailto: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