From: Antonin Houska <ah@cybertec.at>
To: Robert Haas <robertmhaas@gmail.com>
cc: Toshi Harada <harada.toshi@po.ntt-tx.co.jp>,
    "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: "WIP: Data at rest encryption" patch and, PostgreSQL 11-beta3
In-reply-to: 
 <CA+TgmoYYrB+WQFVhSqkLmmY7G_wM734BUQ7aA4uMtO4bGFp-mA@mail.gmail.com>
References: <5907.1534140361@localhost>
 <201808150211.w7F2ApMj023097@ccmail04.silk.ntt-tx.co.jp>
 <19039.1536933944@localhost>
 <CA+TgmoawhAmdVaSYt3YLA5dsgoc-oXf72O3ybFAqiARfyTL4iA@mail.gmail.com>
 <14646.1552570130@localhost>
 <CA+Tgmoawc7hQW2SrcvVSfw8JyYV8UU4fyQ56nxKn_BtPhXfhdQ@mail.gmail.com>
 <11812.1554386232@localhost>
 <CA+TgmoaAkO0X8t_FeePfv86tysGw2BZivjFv+7=9cKyvVE9M5w@mail.gmail.com>
 <7567.1556117300@localhost>
 <CA+TgmoaUw3QaHoj_QnkyFVMFLnfsDX32ctOz_zLPReQ-RjAioQ@mail.gmail.com>
 <32714.1559057215@localhost>
 <CA+TgmoYYrB+WQFVhSqkLmmY7G_wM734BUQ7aA4uMtO4bGFp-mA@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
   message dated "Wed, 29 May 2019 17:51:37 -0400."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <18986.1559285981.1@localhost>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 31 May 2019 08:59:41 +0200
Message-ID: <18987.1559285981@localhost>
Precedence: bulk

Robert Haas <robertmhaas@gmail.com> wrote:

> On Tue, May 28, 2019 at 11:27 AM Antonin Houska <ah@cybertec.at> wrote:
> > We thought that it's more convenient for administrator to enter passwo=
rd. To
> > achieve that, we can instead tell the admin how to use the key derivat=
ion tool
> > (pg_keysetup) and send its output to postgres. So yes, it's possible t=
o 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 fro=
m
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 actu=
ally
"return" strings.

> > > +pg_upgrade. (Therefore the encrypted cluster does not support pg_up=
grade 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 see=
d) 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_ca=
talog.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 w=
as
no strong reason to do so by now.

-- =

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