Re: Adding pipe support to pg_dump and pg_restore

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:
>> Stephen Frost <sfrost@snowman.net> writes:
>> > * David Hedberg (david.hedberg@gmail.com) wrote:
>> >> The idea is to add a --pipe option to pg_dump / pg_restore where
>> >> you can specify a custom shell command that is used to write /
>> >> read each .dat-file. Usage examples include encryption with pgp
>> >> and/or custom compression pipelines. %p in the command is
>> >> expanded to the path to write to / read from. The pipe command is
>> >> not applied to the toc.
>>
>> > I would certainly think that we'd want to have support for custom
>> > format dumps too..
>>
>> This seems like rather a kluge :-(.  In the context of encrypted
>> dumps in particular, I see no really safe way to pass an encryption
>> key down to the custom command --- either you put it in the command
>> line to be exec'd, or you put it in the process environment, and
>> neither of those are secure on all platforms.
>
> 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.

>> The assumption that the TOC doesn't need encryption seems pretty
>> shaky as well.
>
> That it does.
>

I don't think there's any inherent reason it can't be applied to the
TOC as well. It's mostly an accident of me following the the existing
compression code.

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..

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.

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..

/ David