pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedEncryption in pg_dump
27+ messages / 11 participants
[nested] [flat]
* Encryption in pg_dump
@ 2020-07-20 12:40 vinod kale <vinod16895@gmail.com>
0 siblings, 1 reply; 27+ messages in thread
From: vinod kale @ 2020-07-20 12:40 UTC (permalink / raw)
To: pgsql-admin
Hello Team,
I am using postgresql 10.8 version in our prod environment. We take dump
backup on daily basis of full database. And this dump backup creates in
human readable format.
My requirement is backup should creates in encrypted format at the time of
pg_dump running.
Is there any way or any option available in pg_dump that will creates the
backup in encrypted format or any tool available for same.
Version :- postgres 10.8
pg_dump command :-
pg_dump -d dbname -Fd -v -j16 /backup_location
Please let me know if anything more information required from my side.
Thanks in advance.
--
Thanks & Regards
Vinod Kale.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-20 13:24 Paul Förster <paul.foerster@gmail.com>
parent: vinod kale <vinod16895@gmail.com>
0 siblings, 1 reply; 27+ messages in thread
From: Paul Förster @ 2020-07-20 13:24 UTC (permalink / raw)
To: vinod kale <vinod16895@gmail.com>; +Cc: pgsql-admin
Hi Vinod,
> On 20. Jul, 2020, at 14:40, vinod kale <vinod16895@gmail.com> wrote:
>
> Hello Team,
>
> I am using postgresql 10.8 version in our prod environment. We take dump backup on daily basis of full database. And this dump backup creates in human readable format.
> My requirement is backup should creates in encrypted format at the time of pg_dump running.
> Is there any way or any option available in pg_dump that will creates the backup in encrypted format or any tool available for same.
sorry, no way. The only way you have is to pipe the output of pg_dump through some encryption tool. pg_dump does not offer to create encrypted dumps directly using some command line option.
Cheers,
Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-22 18:55 Bruce Momjian <bruce@momjian.us>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 3 replies; 27+ messages in thread
From: Bruce Momjian @ 2020-07-22 18:55 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: vinod kale <vinod16895@gmail.com>; pgsql-admin
On Mon, Jul 20, 2020 at 03:24:16PM +0200, Paul Förster wrote:
> Hi Vinod,
>
> > On 20. Jul, 2020, at 14:40, vinod kale <vinod16895@gmail.com> wrote:
> >
> > Hello Team,
> >
> > I am using postgresql 10.8 version in our prod environment. We take dump backup on daily basis of full database. And this dump backup creates in human readable format.
> > My requirement is backup should creates in encrypted format at the time of pg_dump running.
> > Is there any way or any option available in pg_dump that will creates the backup in encrypted format or any tool available for same.
>
> sorry, no way. The only way you have is to pipe the output of pg_dump through some encryption tool. pg_dump does not offer to create encrypted dumps directly using some command line option.
Does anyone know why we are getting so many requests for encrypting
dumps all of a sudden?
July 20 CAD7GeHRZ3tu8y9VG_0juVK3RQq6r+t6HciguEz_C8Eh85Hmq3A@mail.gmail.com
July 10 CALLD-SGTbu0HCW8NGVg+ycC8PKn9opcbN7aw4CvLj3OVC-hw=Q@mail.gmail.com
July 8 CAFiui6G0sgLoURsK61vmiPR1Amd7PQt82cvgdmZ7+ukdsbV1QQ@mail.gmail.com
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-22 20:05 Ron <ronljohnsonjr@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
2 siblings, 1 reply; 27+ messages in thread
From: Ron @ 2020-07-22 20:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/22/20 1:55 PM, Bruce Momjian wrote:
> On Mon, Jul 20, 2020 at 03:24:16PM +0200, Paul Förster wrote:
>> Hi Vinod,
>>
>>> On 20. Jul, 2020, at 14:40, vinod kale <vinod16895@gmail.com> wrote:
>>>
>>> Hello Team,
>>>
>>> I am using postgresql 10.8 version in our prod environment. We take dump backup on daily basis of full database. And this dump backup creates in human readable format.
>>> My requirement is backup should creates in encrypted format at the time of pg_dump running.
>>> Is there any way or any option available in pg_dump that will creates the backup in encrypted format or any tool available for same.
>> sorry, no way. The only way you have is to pipe the output of pg_dump through some encryption tool. pg_dump does not offer to create encrypted dumps directly using some command line option.
> Does anyone know why we are getting so many requests for encrypting
> dumps all of a sudden?
>
> July 20 CAD7GeHRZ3tu8y9VG_0juVK3RQq6r+t6HciguEz_C8Eh85Hmq3A@mail.gmail.com
> July 10 CALLD-SGTbu0HCW8NGVg+ycC8PKn9opcbN7aw4CvLj3OVC-hw=Q@mail.gmail.com
> July 8 CAFiui6G0sgLoURsK61vmiPR1Amd7PQt82cvgdmZ7+ukdsbV1QQ@mail.gmail.com
Audit season?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-22 21:31 Bruce Momjian <bruce@momjian.us>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 27+ messages in thread
From: Bruce Momjian @ 2020-07-22 21:31 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, Jul 22, 2020 at 03:05:35PM -0500, Ron wrote:
> On 7/22/20 1:55 PM, Bruce Momjian wrote:
> > On Mon, Jul 20, 2020 at 03:24:16PM +0200, Paul Förster wrote:
> > > Hi Vinod,
> > >
> > > > On 20. Jul, 2020, at 14:40, vinod kale <vinod16895@gmail.com> wrote:
> > > >
> > > > Hello Team,
> > > >
> > > > I am using postgresql 10.8 version in our prod environment. We take dump backup on daily basis of full database. And this dump backup creates in human readable format.
> > > > My requirement is backup should creates in encrypted format at the time of pg_dump running.
> > > > Is there any way or any option available in pg_dump that will creates the backup in encrypted format or any tool available for same.
> > > sorry, no way. The only way you have is to pipe the output of pg_dump through some encryption tool. pg_dump does not offer to create encrypted dumps directly using some command line option.
> > Does anyone know why we are getting so many requests for encrypting
> > dumps all of a sudden?
> >
> > July 20 CAD7GeHRZ3tu8y9VG_0juVK3RQq6r+t6HciguEz_C8Eh85Hmq3A@mail.gmail.com
> > July 10 CALLD-SGTbu0HCW8NGVg+ycC8PKn9opcbN7aw4CvLj3OVC-hw=Q@mail.gmail.com
> > July 8 CAFiui6G0sgLoURsK61vmiPR1Amd7PQt82cvgdmZ7+ukdsbV1QQ@mail.gmail.com
>
> Audit season?
LOL, maybe. ;-)
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 05:10 Paul Förster <paul.foerster@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
2 siblings, 2 replies; 27+ messages in thread
From: Paul Förster @ 2020-07-23 05:10 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: vinod kale <vinod16895@gmail.com>; pgsql-admin
Hi Bruce,
> On 22. Jul, 2020, at 20:55, Bruce Momjian <bruce@momjian.us> wrote:
>
> Does anyone know why we are getting so many requests for encrypting
> dumps all of a sudden?
probably because a) people don't read past posts and b) more and more IT heads decide that *everything*, be it internal to the company or not, has to be encrypted.
Cheers,
Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 05:29 vinod kale <vinod16895@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
1 sibling, 1 reply; 27+ messages in thread
From: vinod kale @ 2020-07-23 05:29 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: Bruce Momjian <bruce@momjian.us>; pgsql-admin
Actually our requirement is neither any of the data in readable format on
server. I searched too regarding this. Also i didnt find any option for
encryption while pg_dump.
I agree with you Paul. But i just need to confirm with community, so i
raised.
And in past few days i googled so many on other issues and finally i get
the answer from community only.
So... Thanks for your valuable time.
--
Thanks and Regards
Vinod Kale
On Thu, 23 Jul 2020, 10:40 Paul Förster, <paul.foerster@gmail.com> wrote:
> Hi Bruce,
>
> > On 22. Jul, 2020, at 20:55, Bruce Momjian <bruce@momjian.us> wrote:
> >
> > Does anyone know why we are getting so many requests for encrypting
> > dumps all of a sudden?
>
> probably because a) people don't read past posts and b) more and more IT
> heads decide that *everything*, be it internal to the company or not, has
> to be encrypted.
>
> Cheers,
> Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 05:34 Tim Cross <theophilusx@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
1 sibling, 2 replies; 27+ messages in thread
From: Tim Cross @ 2020-07-23 05:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Paul Förster <paul.foerster@gmail.com> writes:
> Hi Bruce,
>
>> On 22. Jul, 2020, at 20:55, Bruce Momjian <bruce@momjian.us> wrote:
>>
>> Does anyone know why we are getting so many requests for encrypting
>> dumps all of a sudden?
>
> probably because a) people don't read past posts and b) more and more IT heads decide that *everything*, be it internal to the company or not, has to be encrypted.
>
Yes, I think the IT heads issue is the primary driver - combined with
very poor understanding of information security at senior levels and a
huge growth of poor quality and 'snake oil salesmen' in the IT security space
due to the amount of money ill informed senior managers are throwing at
what they think is a technical problem which usually is in fact a
business process problem.
--
Tim Cross
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 05:48 Paul Förster <paul.foerster@gmail.com>
parent: Tim Cross <theophilusx@gmail.com>
1 sibling, 0 replies; 27+ messages in thread
From: Paul Förster @ 2020-07-23 05:48 UTC (permalink / raw)
To: Tim Cross <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Tim,
> On 23. Jul, 2020, at 07:34, Tim Cross <theophilusx@gmail.com> wrote:
>
> Yes, I think the IT heads issue is the primary driver - combined with
> very poor understanding of information security at senior levels and a
> huge growth of poor quality and 'snake oil salesmen' in the IT security space
> due to the amount of money ill informed senior managers are throwing at
> what they think is a technical problem which usually is in fact a
> business process problem.
and the best solution to that nonsense comes from the big red O. They use PKCS#12 wallets. Good, but as soon as you need auto-open wallets, which you do for every automated job like backups, you put the key right next to it. So it's like having a big safe in your house but the combination of the lock is written all over it. So much for security by obscurity.
For PostgreSQL we use a umask of 077 in our profile. That's why I keep telling my IT head that this is enough and it does not make sense to put the key next to the safe.
Cheers,
Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 06:50 Olivier Gautherot <ogautherot@gautherot.net>
parent: Bruce Momjian <bruce@momjian.us>
2 siblings, 1 reply; 27+ messages in thread
From: Olivier Gautherot @ 2020-07-23 06:50 UTC (permalink / raw)
To: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>; +Cc: pgsql-admin
Hi Anton,
Le jeu. 23 juil. 2020 à 08:32, Dischner, Anton <
Anton.Dischner@med.uni-muenchen.de> a écrit :
> Hi,
>
> i had to do this on the fly compression 30 years ago in Oracle.
> You had to use a pipe construct to do this.
> Oracle was not able to do this as it is concept in Unix/Linux.
>
> I am looking for an on the fly compression for a year now for PG
> I didnt't dig very deep as i spent most of the time with MySQL/MariaDB.
>
Do you mean for dumps? Pg_dump supports it natively with gzip, see option
-Z. You can always recompress later (that's what I did for a project at my
last job).
So i guess this is really a FAQ.
>
> If you have a solution please share,
>
> best,
>
> Anton
>
>
> -----Ursprüngliche Nachricht-----
> Von: Bruce Momjian <bruce@momjian.us>
> Gesendet: Mittwoch, 22. Juli 2020 20:56
> An: Paul Förster <paul.foerster@gmail.com>
> Cc: vinod kale <vinod16895@gmail.com>; pgsql-admin@postgresql.org
> Betreff: Re: Encryption in pg_dump
>
> On Mon, Jul 20, 2020 at 03:24:16PM +0200, Paul Förster wrote:
> > Hi Vinod,
> >
> > > On 20. Jul, 2020, at 14:40, vinod kale <vinod16895@gmail.com> wrote:
> > >
> > > Hello Team,
> > >
> > > I am using postgresql 10.8 version in our prod environment. We take
> dump backup on daily basis of full database. And this dump backup creates
> in human readable format.
> > > My requirement is backup should creates in encrypted format at the
> time of pg_dump running.
> > > Is there any way or any option available in pg_dump that will creates
> the backup in encrypted format or any tool available for same.
> >
> > sorry, no way. The only way you have is to pipe the output of pg_dump
> through some encryption tool. pg_dump does not offer to create encrypted
> dumps directly using some command line option.
>
> Does anyone know why we are getting so many requests for encrypting dumps
> all of a sudden?
>
> July 20
> CAD7GeHRZ3tu8y9VG_0juVK3RQq6r+t6HciguEz_C8Eh85Hmq3A@mail.gmail.com
> July 10 CALLD-SGTbu0HCW8NGVg+ycC8PKn9opcbN7aw4CvLj3OVC-hw=
> Q@mail.gmail.com
> July 8
> CAFiui6G0sgLoURsK61vmiPR1Amd7PQt82cvgdmZ7+ukdsbV1QQ@mail.gmail.com
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EnterpriseDB https://enterprisedb.com
>
> The usefulness of a cup is in its emptiness, Bruce Lee
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 06:58 Olivier Gautherot <ogautherot@gautherot.net>
parent: Tim Cross <theophilusx@gmail.com>
1 sibling, 1 reply; 27+ messages in thread
From: Olivier Gautherot @ 2020-07-23 06:58 UTC (permalink / raw)
To: Tim Cross <theophilusx@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi all,
Le jeu. 23 juil. 2020 à 07:34, Tim Cross <theophilusx@gmail.com> a écrit :
>
> Paul Förster <paul.foerster@gmail.com> writes:
>
> > Hi Bruce,
> >
> >> On 22. Jul, 2020, at 20:55, Bruce Momjian <bruce@momjian.us> wrote:
> >>
> >> Does anyone know why we are getting so many requests for encrypting
> >> dumps all of a sudden?
> >
> > probably because a) people don't read past posts and b) more and more IT
> heads decide that *everything*, be it internal to the company or not, has
> to be encrypted.
> >
>
> Yes, I think the IT heads issue is the primary driver - combined with
> very poor understanding of information security at senior levels and a
> huge growth of poor quality and 'snake oil salesmen' in the IT security
> space
> due to the amount of money ill informed senior managers are throwing at
> what they think is a technical problem which usually is in fact a
> business process problem.
>
The root cause is probably that, if you can't separate sensitive
information, you must encrypt everything - hence the dump. It may be a sign
of bad design to start with, with data at rest not protected in the first
place. It may also be a sign of "encrypt everything to be safe" as a false
perception of security, increasing the attack surface instead of reducing
it. And we could carry on with this list.
Put it on the account of GDPR, as compliance is not an easy job.
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 07:17 Ron <ronljohnsonjr@gmail.com>
parent: Olivier Gautherot <ogautherot@gautherot.net>
0 siblings, 1 reply; 27+ messages in thread
From: Ron @ 2020-07-23 07:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/23/20 1:58 AM, Olivier Gautherot wrote:
> Hi all,
>
> Le jeu. 23 juil. 2020 à 07:34, Tim Cross <theophilusx@gmail.com
> <mailto:theophilusx@gmail.com>> a écrit :
>
>
> Paul Förster <paul.foerster@gmail.com
> <mailto:paul.foerster@gmail.com>> writes:
>
> > Hi Bruce,
> >
> >> On 22. Jul, 2020, at 20:55, Bruce Momjian <bruce@momjian.us
> <mailto:bruce@momjian.us>> wrote:
> >>
> >> Does anyone know why we are getting so many requests for encrypting
> >> dumps all of a sudden?
> >
> > probably because a) people don't read past posts and b) more and
> more IT heads decide that *everything*, be it internal to the company
> or not, has to be encrypted.
> >
>
> Yes, I think the IT heads issue is the primary driver - combined with
> very poor understanding of information security at senior levels and a
> huge growth of poor quality and 'snake oil salesmen' in the IT
> security space
> due to the amount of money ill informed senior managers are throwing at
> what they think is a technical problem which usually is in fact a
> business process problem.
>
>
> The root cause is probably that, if you can't separate sensitive
> information, you must encrypt everything - hence the dump. It may be a
> sign of bad design to start with, with data at rest not protected in the
> first place. It may also be a sign of "encrypt everything to be safe" as a
> false perception of security, increasing the attack surface instead of
> reducing it. And we could carry on with this list.
>
> Put it on the account of GDPR, as compliance is not an easy job.
>
When Oracle, SQL Server and MySQL Enterprise all have Transparent Data
Encryption, the expectation is that all databases should have TDE.
Auditors and IT upper management don't really understand the limitations of
TDE, but that does not matter. The magic word *Encryption* is in there, and
that means they like it
Adding hooks into libgpgme from pg_dump and pg_restore (needed for
--format=directory) would be *Very Helpful*.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 07:23 Paul Förster <paul.foerster@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 27+ messages in thread
From: Paul Förster @ 2020-07-23 07:23 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
> On 23. Jul, 2020, at 09:17, Ron <ronljohnsonjr@gmail.com> wrote:
> Adding hooks into libgpgme from pg_dump and pg_restore (needed for --format=directory) would be Very Helpful.
I guess, replacing stupid IT heads with competent ones makes more sense.
Cheers,
Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 08:27 Tal Glo <glozmantal@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
0 siblings, 2 replies; 27+ messages in thread
From: Tal Glo @ 2020-07-23 08:27 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Hi Paul,
There is a way to implement full homomorphic encryption (FHE) with
Postgres. The decision which attributes to encrypt must be done at database
planning step. It worth mentioning that FHE is good to implement, if one
wants to be able to do server side supported mathematical operations on the
encrypted data. If the data just needs to be stored in an encrypted form
then using FHE might be a storage overkill. A dump of the database with FHE
encrypted attributes preserves the encryption of the encrypted attributes.
I've used a relatively old version (2.3.1) of Microsoft's SEAL library in
my University project for that. At the time of doing that there were some
"issues" that I had to address:
1. The encrypted attributes were big in size and Postgres cannot store the
FHE cypher texts data type. 20,858 encrypted values used 3,6GB. ~33% of
that size is because of base64 and the rest of the size ist because of my
chosen encryption parameters.
2. Handling queries related to FHE encrypted attributes on the server side
requires an implementation of own C language functions.
The were some other FHE schema (BFV) limitation that were addressed in
newer versions of SEAL, by introducing a more suitable (for my needs) FHE
schema (CKKS).
It's not always a good Idea to say that something cannot be done or that
some one needs to be replaced. Sometimes it's worth to develop some new
process, based on a mixture of available technologies out there.
Best regards,
Tal
Paul Förster <paul.foerster@gmail.com> schrieb am Do., 23. Juli 2020, 09:23:
> Hi Ron,
>
> > On 23. Jul, 2020, at 09:17, Ron <ronljohnsonjr@gmail.com> wrote:
> > Adding hooks into libgpgme from pg_dump and pg_restore (needed for
> --format=directory) would be Very Helpful.
>
> I guess, replacing stupid IT heads with competent ones makes more sense.
>
> Cheers,
> Paul
>
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 09:05 Paul Förster <paul.foerster@gmail.com>
parent: Tal Glo <glozmantal@gmail.com>
1 sibling, 2 replies; 27+ messages in thread
From: Paul Förster @ 2020-07-23 09:05 UTC (permalink / raw)
To: Tal Glo <glozmantal@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Hi Tal,
> On 23. Jul, 2020, at 10:27, Tal Glo <glozmantal@gmail.com> wrote:
> There is a way to implement full homomorphic encryption (FHE) with Postgres.
ok.
> I've used a relatively old version (2.3.1) of Microsoft's SEAL library in my University project for that.
I don't know about Windows but I assume, Linux would be similar?
> 2. Handling queries related to FHE encrypted attributes on the server side requires an implementation of own C language functions.
we don't do that. Also, we have third party applications. In case they need to be modified, this is impossible.
> It's not always a good Idea to say that something cannot be done or that some one needs to be replaced. Sometimes it's worth to develop some new process, based on a mixture of available technologies out there.
right, but only if it makes sense. To put a wallet in the file system and its key right next to it does not make sense but only serves to make IT heads and security "gurus" go quiet. In these cases, only replacing them by competent ones helps.
Cheers,
Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 09:12 Ron <ronljohnsonjr@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
1 sibling, 1 reply; 27+ messages in thread
From: Ron @ 2020-07-23 09:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/23/20 4:05 AM, Paul Förster wrote:
> Hi Tal,
>
>> On 23. Jul, 2020, at 10:27, Tal Glo <glozmantal@gmail.com> wrote:
>> There is a way to implement full homomorphic encryption (FHE) with Postgres.
> ok.
>
>> I've used a relatively old version (2.3.1) of Microsoft's SEAL library in my University project for that.
> I don't know about Windows but I assume, Linux would be similar?
>
>> 2. Handling queries related to FHE encrypted attributes on the server side requires an implementation of own C language functions.
> we don't do that. Also, we have third party applications. In case they need to be modified, this is impossible.
That was my first thought, too. We mostly run 3rd party applications, with
very little home-grown. TDE (transparent data encryption) is the only
reasonable work-around.
>> It's not always a good Idea to say that something cannot be done or that some one needs to be replaced. Sometimes it's worth to develop some new process, based on a mixture of available technologies out there.
> right, but only if it makes sense. To put a wallet in the file system and its key right next to it does not make sense but only serves to make IT heads and security "gurus" go quiet. In these cases, only replacing them by competent ones helps.
Which is as unlikely as getting all 3rd party vendors to implement
application-level encryption.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 10:20 Tal Glo <glozmantal@gmail.com>
parent: Paul Förster <paul.foerster@gmail.com>
1 sibling, 1 reply; 27+ messages in thread
From: Tal Glo @ 2020-07-23 10:20 UTC (permalink / raw)
To: Paul Förster <paul.foerster@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Hello Paul,
You are correct. If third party applications rely on the FHE encrypted
data, then they will need to change the way they quire it. But if it's
companies sensitive internal data stored outside (e.g. in a cloud) and the
company ist the only one that calculates on it, then it may be worth
looking into that.
As of 2018 I didn't see (maybe I've missed some) any commercial
implementation of FHE, but that might have been changed by now, since it's
a rapidly growing wide supported subject.
I didn't use Windows at all, everything ran on Linux.
Best regards,
Tal
Paul Förster <paul.foerster@gmail.com> schrieb am Do., 23. Juli 2020, 11:05:
> Hi Tal,
>
> > On 23. Jul, 2020, at 10:27, Tal Glo <glozmantal@gmail.com> wrote:
> > There is a way to implement full homomorphic encryption (FHE) with
> Postgres.
>
> ok.
>
> > I've used a relatively old version (2.3.1) of Microsoft's SEAL library
> in my University project for that.
>
> I don't know about Windows but I assume, Linux would be similar?
>
> > 2. Handling queries related to FHE encrypted attributes on the server
> side requires an implementation of own C language functions.
>
> we don't do that. Also, we have third party applications. In case they
> need to be modified, this is impossible.
>
> > It's not always a good Idea to say that something cannot be done or that
> some one needs to be replaced. Sometimes it's worth to develop some new
> process, based on a mixture of available technologies out there.
>
> right, but only if it makes sense. To put a wallet in the file system and
> its key right next to it does not make sense but only serves to make IT
> heads and security "gurus" go quiet. In these cases, only replacing them by
> competent ones helps.
>
> Cheers,
> Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 10:32 Paul Förster <paul.foerster@gmail.com>
parent: Tal Glo <glozmantal@gmail.com>
0 siblings, 0 replies; 27+ messages in thread
From: Paul Förster @ 2020-07-23 10:32 UTC (permalink / raw)
To: Tal Glo <glozmantal@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Hi Tal,
please don't top-post.
> On 23. Jul, 2020, at 12:20, Tal Glo <glozmantal@gmail.com> wrote:
>
> You are correct. If third party applications rely on the FHE encrypted data, then they will need to change the way they quire it. But if it's companies sensitive internal data stored outside (e.g. in a cloud) and the company ist the only one that calculates on it, then it may be worth looking into that.
as I said, that's impossible. And we don't have anything in the cloud and never will.
> I didn't use Windows at all, everything ran on Linux.
ok.
Cheers,
Paul
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 13:52 Scott Ribe <scott_ribe@elevated-dev.com>
parent: vinod kale <vinod16895@gmail.com>
0 siblings, 1 reply; 27+ messages in thread
From: Scott Ribe @ 2020-07-23 13:52 UTC (permalink / raw)
To: vinod kale <vinod16895@gmail.com>; +Cc: Paul Förster <paul.foerster@gmail.com>; Bruce Momjian <bruce@momjian.us>; pgsql-admin
> On Jul 22, 2020, at 11:29 PM, vinod kale <vinod16895@gmail.com> wrote:
>
> Actually our requirement is neither any of the data in readable format on server.
Sounds like you need encryption at the disk or volume level.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 13:58 Stephen Frost <sfrost@snowman.net>
parent: Tal Glo <glozmantal@gmail.com>
1 sibling, 0 replies; 27+ messages in thread
From: Stephen Frost @ 2020-07-23 13:58 UTC (permalink / raw)
To: Tal Glo <glozmantal@gmail.com>; +Cc: Paul Förster <paul.foerster@gmail.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Greetings,
* Tal Glo (glozmantal@gmail.com) wrote:
> There is a way to implement full homomorphic encryption (FHE) with
> Postgres. The decision which attributes to encrypt must be done at database
> planning step. It worth mentioning that FHE is good to implement, if one
> wants to be able to do server side supported mathematical operations on the
> encrypted data. If the data just needs to be stored in an encrypted form
> then using FHE might be a storage overkill. A dump of the database with FHE
> encrypted attributes preserves the encryption of the encrypted attributes.
Wasn't it relatively recently pointed out that having the ability to do
mathematical operations on the encrypted data ends up leading someone to
be able to deduce a fair bit of information much more easily than
actually breaking the encryption would involve..?
https://eprint.iacr.org/2016/775.pdf
Not finding it right now but I recall there being another paper which
basically pointed out that if you have context information about the
encrypted data (if it's credit card numbers, or SSNs, or such), along
with the ability to perform some operations (addition, ordering, etc)
that you could derive the real values without that much difficulty.
> 1. The encrypted attributes were big in size and Postgres cannot store the
> FHE cypher texts data type. 20,858 encrypted values used 3,6GB. ~33% of
> that size is because of base64 and the rest of the size ist because of my
> chosen encryption parameters.
A bit confused as to why you'd use base64 to store the data- would make
a lot more sense to use bytea.
> 2. Handling queries related to FHE encrypted attributes on the server side
> requires an implementation of own C language functions.
This could certainly be done in an extension, of course.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 14:30 Ron <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 27+ messages in thread
From: Ron @ 2020-07-23 14:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 7/23/20 8:52 AM, Scott Ribe wrote:
>> On Jul 22, 2020, at 11:29 PM, vinod kale <vinod16895@gmail.com> wrote:
>>
>> Actually our requirement is neither any of the data in readable format on server.
> Sounds like you need encryption at the disk or volume level.
Volume encryption only protects against stolen disks, which is... highly
unlikely. (The only scenarios I can think of are USB drives which get lost
in transit -- think "station wagon full of tapes hurtling down the highway
-- and law enforcement seizing the computers.)
TDE, though, protects against hackers who break in and try to slurp out as
much data as possible. Still pretty unlikely, but within the realm of reason.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 14:56 Stephen Frost <sfrost@snowman.net>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 27+ messages in thread
From: Stephen Frost @ 2020-07-23 14:56 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Greetings,
* Ron (ronljohnsonjr@gmail.com) wrote:
> On 7/23/20 4:05 AM, Paul Förster wrote:
> >>On 23. Jul, 2020, at 10:27, Tal Glo <glozmantal@gmail.com> wrote:
> >>There is a way to implement full homomorphic encryption (FHE) with Postgres.
> >ok.
> >
> >>I've used a relatively old version (2.3.1) of Microsoft's SEAL library in my University project for that.
> >I don't know about Windows but I assume, Linux would be similar?
> >
> >>2. Handling queries related to FHE encrypted attributes on the server side requires an implementation of own C language functions.
> >we don't do that. Also, we have third party applications. In case they need to be modified, this is impossible.
>
> That was my first thought, too. We mostly run 3rd party applications, with
> very little home-grown. TDE (transparent data encryption) is the only
> reasonable work-around.
TDE and pg_dump are pretty different things. Encryption of backups
certainly makes sense (though pg_dump is really an export and not a
backup...) when they're being shipped off to somewhere else. Of course,
you can do that with gpg or similar for pg_dump, though I wouldn't be
against adding support directly. Ideally, we'd do that in a way that
leverages the code we already have for encryption, but to do that would
require moving more code into the common lib.
As for TDE, I agree that it's that same problem that using it means
that, while the data is encrypted on the disk, the key is available in
memory, and therefore the attack vectors that it actually helps with are
pretty few... If the concern is an online compromise of the system,
then the attacker will have the encrypted data and the key, so TDE isn't
helpful in that case. If the attack vector is an offline data-at-rest
attack, then encryption lower down the stack (either filesystem
encryption with fscrypto, or block-level encryption) can be used to
address it. Filesystem or block-level encryption also addresses the
attack vector of "the attacker broke into our SAN", at least if the keys
aren't ever stored anywhere on the SAN.
> >>It's not always a good Idea to say that something cannot be done or that some one needs to be replaced. Sometimes it's worth to develop some new process, based on a mixture of available technologies out there.
> >right, but only if it makes sense. To put a wallet in the file system and its key right next to it does not make sense but only serves to make IT heads and security "gurus" go quiet. In these cases, only replacing them by competent ones helps.
>
> Which is as unlikely as getting all 3rd party vendors to implement
> application-level encryption.
That is a bit unfortuante. I do like the idea that some have had of
adding options into the library (eg: libpq) to support client side
encryption of the data before it's sent to the database server, to
address this. That makes the encryption happen as early on as possible
(at least, for us) while ideally making it work with 3rd party
applications- the issue here is that for such things to work you can't
actually encrypt *everything* otherwise indexes and such aren't any
good, so you have to have enough knowledge to be able to define what
should and shouldn't be encrypted, etc...
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 15:02 Stephen Frost <sfrost@snowman.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 27+ messages in thread
From: Stephen Frost @ 2020-07-23 15:02 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Greetings,
* Ron (ronljohnsonjr@gmail.com) wrote:
> TDE, though, protects against hackers who break in and try to slurp out as
> much data as possible. Still pretty unlikely, but within the realm of
> reason.
That's an unfortunate misconception- TDE won't help with that as the key
will be in the server's memory and therefore accessible to the attacker.
Certainly, if the server is doing the decryption and is compromised in
an online fashion, then the attacker is going to have access to that
data.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 15:34 Rui DeSousa <rui@crazybean.net>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 27+ messages in thread
From: Rui DeSousa @ 2020-07-23 15:34 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Jul 23, 2020, at 10:30 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Volume encryption only protects against stolen disks, which is... highly unlikely. (The only scenarios I can think of are USB drives which get lost in transit -- think "station wagon full of tapes hurtling down the highway -- and law enforcement seizing the computers.)
Encryption at rest also protects from warrantee service where you’re sending a failed drive back to a vendor. I could see the drive being refurbished and send out with readable data on it if not encrypted. =
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 15:40 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 27+ messages in thread
From: Scott Ribe @ 2020-07-23 15:40 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On Jul 23, 2020, at 9:34 AM, Rui DeSousa <rui@crazybean.net> wrote:
>
> Encryption at rest also protects from warrantee service where you’re sending a failed drive back to a vendor. I could see the drive being refurbished and send out with readable data on it if not encrypted.
And potentially allows you to reuse the drive. And *if* the server/drive is stolen, in the US at least, means you don't have a breach and don't have to notify customers.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-23 15:42 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 27+ messages in thread
From: Scott Ribe @ 2020-07-23 15:42 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
> On Jul 23, 2020, at 8:56 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> Filesystem or block-level encryption also addresses the
> attack vector...
They also fix the "oops we were creating temp/log files locally which were not encrypted"
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Encryption in pg_dump
@ 2020-07-24 17:12 Jeff Janes <jeff.janes@gmail.com>
parent: Olivier Gautherot <ogautherot@gautherot.net>
0 siblings, 0 replies; 27+ messages in thread
From: Jeff Janes @ 2020-07-24 17:12 UTC (permalink / raw)
To: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>; +Cc: pgsql-admin
On Thu, Jul 23, 2020 at 3:05 AM Dischner, Anton <
Anton.Dischner@med.uni-muenchen.de> wrote:
> Hi Olivier,
>
>
>
> thanks for your hint which is better than my approach.
>
>
>
> I did somthing like:
>
>
>
> /usr/bin/pg_dumpall --username=postgres | bzip2 >
> /var/lib/postgresql/backup/"`date +%d-%m-%Y_%H:%M:%S`-"backup_all.sql.bz2
>
>
>
> To use encryption: [DRAFT]
>
>
>
> tar -czf - ttt | openssl enc -e -aes-256-cbc -pass pass:test -out ttt.tgz
>
> openssl enc -d -aes-256-cbc -pass pass:test -in ttt.tgz | tar xz
>
>
>
> As mentioned obove it makes no sense to store the PW in the script.
>
It does make sense as long as the script is not stored with the encrypted
file, but rather only with the unencrypted server (plus backed up someplace
else, like on a piece of paper in a safe offsite)
But you can use asymmetric (public key) encryption if you don't want the
backing-up user to be able to decrypt at all:
pg_dumpall | gpg --encrypt --recipient ciso@example.com > backup.gpg
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2020-07-24 17:12 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-20 12:40 Encryption in pg_dump vinod kale <vinod16895@gmail.com>
2020-07-20 13:24 ` Paul Förster <paul.foerster@gmail.com>
2020-07-22 18:55 ` Bruce Momjian <bruce@momjian.us>
2020-07-22 20:05 ` Ron <ronljohnsonjr@gmail.com>
2020-07-22 21:31 ` Bruce Momjian <bruce@momjian.us>
2020-07-23 05:10 ` Paul Förster <paul.foerster@gmail.com>
2020-07-23 05:29 ` vinod kale <vinod16895@gmail.com>
2020-07-23 13:52 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-07-23 14:30 ` Ron <ronljohnsonjr@gmail.com>
2020-07-23 15:02 ` Stephen Frost <sfrost@snowman.net>
2020-07-23 15:34 ` Rui DeSousa <rui@crazybean.net>
2020-07-23 15:40 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-07-23 05:34 ` Tim Cross <theophilusx@gmail.com>
2020-07-23 05:48 ` Paul Förster <paul.foerster@gmail.com>
2020-07-23 06:58 ` Olivier Gautherot <ogautherot@gautherot.net>
2020-07-23 07:17 ` Ron <ronljohnsonjr@gmail.com>
2020-07-23 07:23 ` Paul Förster <paul.foerster@gmail.com>
2020-07-23 08:27 ` Tal Glo <glozmantal@gmail.com>
2020-07-23 09:05 ` Paul Förster <paul.foerster@gmail.com>
2020-07-23 09:12 ` Ron <ronljohnsonjr@gmail.com>
2020-07-23 14:56 ` Stephen Frost <sfrost@snowman.net>
2020-07-23 15:42 ` Scott Ribe <scott_ribe@elevated-dev.com>
2020-07-23 10:20 ` Tal Glo <glozmantal@gmail.com>
2020-07-23 10:32 ` Paul Förster <paul.foerster@gmail.com>
2020-07-23 13:58 ` Stephen Frost <sfrost@snowman.net>
2020-07-23 06:50 ` Olivier Gautherot <ogautherot@gautherot.net>
2020-07-24 17:12 ` Jeff Janes <jeff.janes@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox