Date: Thu, 23 Jul 2020 10:56:04 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Ron <ronljohnsonjr@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Encryption in pg_dump
Message-ID: <20200723145604.GS12375@tamriel.snowman.net>
References: <2BCA8D4B-E94D-4F2F-99A5-D783912A1CDF@gmail.com>
 <20200722185541.GE3899@momjian.us>
 <6D40812C-3F42-41A2-A548-4EC7754DF473@gmail.com>
 <87zh7qbxqk.fsf@gmail.com>
 <CAJ7S9TW2j7Dk3k62_b_oZg9X=7jKMyLuebcBgtj0aWjSjzGJTw@mail.gmail.com>
 <a91e4430-6e74-2980-b914-7a045d3636ad@gmail.com>
 <4DCA85A3-B307-48E8-9B30-0D8E2E4911D2@gmail.com>
 <CAMreHJLzv9xM2z6vcxq6KxV37C0RGuDSatsa0Fho1u==9i2+Zg@mail.gmail.com>
 <0EF8B591-3363-4D66-AEB8-CC1F34A53DD7@gmail.com>
 <16c2a4f1-5167-a4f8-ab61-b0e083c94ee5@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="It8I9WCLqdSMcwR+"
Content-Disposition: inline
In-Reply-To: <16c2a4f1-5167-a4f8-ab61-b0e083c94ee5@gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--It8I9WCLqdSMcwR+
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Ron (ronljohnsonjr@gmail.com) wrote:
> On 7/23/20 4:05 AM, Paul F=F6rster 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 Post=
gres.
> >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 s=
ide requires an implementation of own C language functions.
> >we don't do that. Also, we have third party applications. In case they n=
eed to be modified, this is impossible.
>=20
> That was my first thought, too.=A0 We mostly run 3rd party applications, =
with
> very little home-grown.=A0 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 tha=
t some one needs to be replaced. Sometimes it's worth to develop some new p=
rocess, based on a mixture of available technologies out there.
> >right, but only if it makes sense. To put a wallet in the file system an=
d its key right next to it does not make sense but only serves to make IT h=
eads and security "gurus" go quiet. In these cases, only replacing them by =
competent ones helps.
>=20
> 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

--It8I9WCLqdSMcwR+
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJfGaUEAAoJEO1sijiDR2RViXsP/jWrVSVELCKi0Wt2ixyuQW7W
BKf558PwvJR9vucd6ecPVepRA5Q99ATTZQzHcawUNlOLOTkgGjYa6hma/ZvpEYwc
IEWEXjDE56qnD+Gn0fXhveLgzfSjSTZpxfSbDbIWR0LIDHdT99drKijzIhxnTLBC
zELNpaI3h3mjlL/JEQsgKS5ESpNKSKqvKoNUjRX2pjkK8xJMH7KvegsmRLbld6p0
RHBQPvAjEYbFigpaEENadrJU0MLDuRoPZU52zYOMFj77uSTSEtjqY8yIYk7yXtDe
WPXT6WiluWwgjQBWOov9j4G4n9fFBPDiLG73++55A3c6XQTQWP14NB6fwPsylO9h
gJ298AwGTw7sYy6uFNs5cx3+V+Vk9j1FUEuxDVcLYfHGQCcKRJn9vOTmNHu5VBMe
lh0+9SbG0uvLzJw2onRsfMnSIY7dkQE788lZ8pI3YZDHvwIxYWoxmKMdjEvFwW8u
MrKoECyJX6+I0una+1a3UHFtcsVjWBTC8hEKs23NgBAZDYzBTPsEnOW1VkRxbKt/
LLZrtAC30T2RHUlXr5PV9/8s5VDrWsZgYNEpoR2i9C/btLNowxp6V2aUQPLY1ad5
3TjjlV/6YzUPE2Py4mQYG3GnWcggvXNBGpbEm5Gb8oW2xMRuXMpeOCPiS1+74Kd+
rIRHVR63GGseXYjgQjAm
=ClH1
-----END PGP SIGNATURE-----

--It8I9WCLqdSMcwR+--