Re: Adding pipe support to pg_dump and pg_restore - Mailing list pgsql-hackers

Hi,

On Sat, Sep 29, 2018 at 7:01 PM, Stephen Frost <sfrost@snowman.net> wrote:
> 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.
>

You are right of course. But I don't see how it's more difficult to
pass the secret to the piped commands than it is to pass it to
postgres.

You wouldn't want to pass the secrets as options to the commands of
course. In the case of gpg you would probably let gpg store and handle
them, which seems to me about the same as letting postgres store them.

>> On Sat, Sep 29, 2018 at 5:03 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> 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.
>

I can maybe see the problem here, but I apologize if I'm missing the point.

Since all the files are individually passed through separate instances
of the pipe, they can also be individually restored. I guess the
--list option could be (adopted to be) used to produce a clear text
TOC to further use in selective decryption of the rest of the archive?
Possibly combined with an option to not apply the pipeline commands to
the TOC during dump and/or restore, if there's any need for that.

I do think that I understand the advantages of having a TOC that
describes the exact format of the dump and how to restore it, and I am
in no way arguing against having encryption included natively in the
format as a default option.

But I think the pipe option, or one like it, could be used to easily
extend the format. Easily supporting a different compression
algorithm, a different encryption method or even a different storage
method like uploading the files directly to a bucket in S3. In this
way I think that it's similar to be able to write the other formats to
stdout; there are probably many different usages of it out there,
including custom compression or encryption.

If this is simply outside the scope of the directory or the custom
format, that is certainly understandable (and, to me, somewhat
regrettable :-) ).


Thank you the answers,
David