Re: Application Level Encryption
On Sun, Jul 5, 2020 at 1:22 AM Zahir Lalani 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
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 wrote: On Sun, Jul 5, 2020 at 1:22 AM Zahir Lalani 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
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 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: Michel Pelletier Sent: 05 July 2020 17:00 To: Zahir Lalani Cc: pgsql-gene...@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
Re: Application Level Encryption
On Sun, Jul 5, 2020 at 10:14 AM Zahir Lalani 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
On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier 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
On Sun, Jul 5, 2020 at 3:23 PM Sam Gendler wrote: > > > On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier < > pelletier.mic...@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: Michel Pelletier Sent: 05 July 2020 23:32 To: Sam Gendler Cc: Zahir Lalani ; pgsql-gene...@postgresql.org Subject: Re: Application Level Encryption On Sun, Jul 5, 2020 at 3:23 PM Sam Gendler mailto:sgend...@ideasculptor.com>> wrote: On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier mailto:pelletier.mic...@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
Re: survey: psql syntax errors abort my transactions
Jeremy Schneider wrote: > Survey for the user community here on the pgsql-general list: it would > be great if lots of people could chime in by answering two questions > about your very own production environment: > > question 1) are you worried about scripts in your production environment > where damage could be caused by a different default in a future new > major version of postgresql? (not aborting transactions in interactive > mode when syntax errors occur) No. Any production scripts would be a single transaction. I think anything else is a disaster waiting to happen (and waiting for me to clean up afterwards). > question 2) do you think the increased user-friendliness of changing > this default behavior would be worthwhile for specific users in your > organization who use postgresql? (including both yourself and others > you know of) No. In fact, I think aborting on error is more user-friendly than not doing so. I often send ad hoc sql to psql via vim key bindings. I think that counts as interactive from psql's point of view. The only problem I have with that is when I mistyped "begin transaction;" (or forgot the semi colon) and so an error half way through doesn't result in a full rollback. But I don't see what can be done about that (other than automating the insertion of "begin transaction;" and "commit transaction;" in my ad hoc sql vim buffers). What might be actually user-friendly is the ability, after such an error, of being able to edit the contents of the failed statement/transaction in the interactive session in an editor to fix the typo. But that's for real interactive use where stdin is a pty. That way, you wouldn't have to retype or copy and paste the good bits. That might already be possible. Yes, it's "\e". And it works after an error, bringing up the erroroneous sql in an editor, and it executes it when you save and exit the editor. I think that's probably what you want and it's already there. But it only edits the last statement, not the whole transaction. It might not be exactly what you need. > As someone working at a large company with an aweful lot of PostgreSQL, > thinking of the internal production systems I'm aware of, I'd personally > vote pretty strongly in favor of changing the default. > > -Jeremy > > Jeremy Schneider > Database Engineer > Amazon Web Services cheers, raf
