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+TgmoYs-TPJXdpLpM1qcib-M_CAMO-7wMxBy3yuuMxs8H4L8Q@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>
 <18987.1559285981@localhost>
 <CA+TgmoYs-TPJXdpLpM1qcib-M_CAMO-7wMxBy3yuuMxs8H4L8Q@mail.gmail.com>
Comments: In-reply-to Robert Haas <robertmhaas@gmail.com>
   message dated "Mon, 03 Jun 2019 12:04:54 -0400."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <31112.1559737923.1@localhost>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 05 Jun 2019 14:32:03 +0200
Message-ID: <31113.1559737923@localhost>
Precedence: bulk

Robert Haas <robertmhaas@gmail.com> wrote:

> On Fri, May 31, 2019 at 2:59 AM Antonin Houska <ah@cybertec.at> wrote:
> > > 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 somethin=
g
> > > 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 f=
rom 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 f=
rom
> > there. This was to address your objection that the executables do not =
actually
> > "return" strings.
> =

> So the part about returning strings is really just a wording issue:
> the documentation needs to talk about data sent to stdout or wherever,
> because that's what really happens, and somebody writing such a
> command from scratch needs to know what it must do.
> =

> What I'm talking about here is that it also has to be reasonably
> possible to write an encryption key command that does something
> useful.  I don't have a really clear vision for how that's going to
> work.  Nobody wants the server, which is probably being launched by
> pg_ctl or systemd or both, to prompt using its own stdin/stderr, but
> the we need to think about how the prompting is actually going to
> work.

Since you mentioned ssh-agent above, I think that postmaster can, instead =
of
running a command, create an unix socket and read the key from there. (I r=
efer
only to the socket as a communication channel, not to the protocol - ssh-a=
gent
does not seem to actually send key over the socket.) Unlike the socket for
backend connections, this one would only be readable by the user that runs
postmaster, and would only exist during the encryption initialization.

The simplest approach from the perspective of the DBA is that pg_ctl can w=
rite
the key to the socket. Besides that we can also implement a separate utili=
ty
to send the key, to be used in other special cases such as starting postgr=
es
via systemd.

(If the unix socket is a problem on windows, we might need to use named pi=
pe
instead.)

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

> Maybe you want to propose an independent patch making that change?

I think of a separate diff in the encryption patch series. As the encrypti=
on
is the only reason for this enhancement, I'm not sure if it deserves a
separate CF entry. (Although it might be considered refactoring because
eventually pg_upgrade won't need to handle the different relnodes, and thu=
s it
might become a little bit simpler.)

-- =

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