Re: "WIP: Data at rest encryption" patch and, PostgreSQL 11-beta3

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, May 28, 2019 at 11:27 AM Antonin Houska <ah(at)cybertec(dot)at> wrote:
> > We thought that it's more convenient for administrator to enter password. To
> > achieve that, we can instead tell the admin how to use the key derivation tool
> > (pg_keysetup) and send its output to postgres. So yes, it's possible to always
> > receive the key from the "encryption key command". I'll change this.
>
> Sounds good. I'm not quite sure how this is going to work. Ideally
> you'd like the encryption key command to fetch the key from something
> like ssh-agent, or maybe pop up a window on the user's terminal with a
> key prompt. Just reading from stdin and writing to stdout is not
> going to be very convenient...

When I mentioned writing to stdout in my previous email, I viewed it from the
perspective of postmaster: whichever way the command gets the password from
the DBA, it'll always write it to stdout and postmaster will read it from
there. This was to address your objection that the executables do not actually
"return" strings.

> > > +pg_upgrade. (Therefore the encrypted cluster does not support pg_upgrade with
> > > +the --link option.)
> > >
> > > That seems pretty unfortunate. Any way to do better?
> >
> >
> > Currently I'm thinking of making the IV less dependent on RelFileNode
> > (e.g. generate a random number for which RelFileNode serves as the seed) and
> > storing it in pg_class as a storage parameter.
>
> I don't think it's a very good idea. For one thing, you can't store
> the IV for pg_class in pg_class; that has a circularity problem. For
> another thing, I'm not sure we can or should try to do catalog access
> from every place where an IV might be needed. In fact, I'd go so far
> as to say that sounds like a recipe for a lot of pain and fragility.
>
> One potential approach is to modify pg_upgrade to preserve the dbnode
> and relfilenode. I don't know of any fundamental reason why such a
> change shouldn't be possible, although it is probably a bunch of work,
> and there may be problems with which I am not acquainted.

Actually it doesn't seem to be that tricky. In the --binary-upgrade mode,
pg_dump does record pg_class.oid:

-- For binary upgrade, must preserve pg_class oids
SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('57465'::pg_catalog.oid);

The header comment in pg_upgrade.c indicates that this is because of TOAST. So
I think that dbnode and relfilenode are not preserved just because there was
no strong reason to do so by now.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com