Re: Adding pipe support to pg_dump and pg_restore

Greetings,

* David Hedberg (david.hedberg@gmail.com) wrote:
> On Sat, Sep 29, 2018 at 5:56 PM, David Fetter <david@fetter.org> wrote:
> > On Sat, Sep 29, 2018 at 11:42:40AM -0400, Tom Lane wrote:
> > As I understand it, those are the options for providing secrets in
> > general. At least in the case of encryption, one good solution would
> > be to use an asymmetric encryption scheme, i.e. one where encrypting
> > doesn't expose a secret in any way.
> >
> > As to decryption, that's generally done with more caution in
> > environments where things are being routinely encrypted in the first
> > place.
>
> Yes; in my specific case the idea is to use public key encryption with
> gpg. In that scenario the secret does not need to be on the server at
> all.

Using public key encryption doesn't mean you get to entirely avoid the
question around how to handle secrets- you'll presumably want to
actually restore the dump at some point.

> On Sat, Sep 29, 2018 at 5:03 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > At least for my 2c, I'm not completely against it, but I'd much rather
> > see us providing encryption directly and for all of the formats we
> > support, doing intelligent things like encrypting the TOC for a custom
> > format dump independently so we can still support fast restore of
> > individual objects and such.  I'm also not entirely sure about how well
> > this proposed approach would work on Windows..
>
> I haven't tested it in windows, but I did see that there's already a
> popen function in src/port/system.c so my guess was going to be that
> it can work..

Perhaps, though these things tend to be trickier on Windows, at least
from what I've seen (I'm no Windows dev myself tho, to be clear).

> Generally, my thinking is that this can be pretty useful in general
> besides encryption. For other formats the dumps can already be written
> to standard output and piped through for example gpg or a custom
> compression application of the administrators choice, so in a sense
> this functionality would merely add the same feature to the directory
> format.

That's certainly not the same though.  One of the great advantages of
custom and directory format dumps is the TOC and the ability to
selectively extract data from them without having to read the entire
dump file.  You end up losing that if you have to pass the entire dump
through something else because you're using the pipe.

> My main wish here is to be able combine a parallel dump/restore with
> encryption without having to first write the dump encrypted and then
> loop over and rewrite the files encrypted in an extra step. This can
> surely be quite a large win as the size of the dumps grow larger..

That's great, and I think we agree that it'd be a very nice feature for
pg_dump/restore to support encryption, but done intelligently, across
the formats that pg_dump supports, with a secure way to pass the
secrets.

Thanks!

Stephen