Re: "Re: Question about grant create on database and pg_dump/pg_dumpall

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Thu, Sep 29, 2016 at 4:04 AM, Haribabu Kommi
> <kommi(dot)haribabu(at)gmail(dot)com> wrote:
>> I am also not sure whether pg_dumpall -g and then individual pg_dump
>> is the more widely used approach or not?

> That's the approach I normally recommend.

The fundamental thing we have to do in order to move forward on this is
to rethink what's the division of labor between pg_dump and pg_dumpall.
I find the patch as presented quite unacceptable because it's made no
effort to do that (or even to touch the documentation).

What do people think of this sketch:

1. pg_dump without --create continues to do what it does today, ie it just
dumps objects within the database, assuming that database-level properties
will already be set correctly for the target database.

2. pg_dump with --create creates the target database and also sets all
database-level properties (ownership, ACLs, ALTER DATABASE SET, etc etc).

3. pg_dumpall loses all code relating to individual-database creation
and property setting and instead relies on pg_dump --create to do that.
This would leave only the code relating to "pg_dumpall -g" (ie, dump roles
and tablespaces) within pg_dumpall itself.

One thing that would still be messy is that presumably "pg_dumpall -g"
would issue ALTER ROLE SET commands, but it's unclear what to do with
ALTER ROLE IN DATABASE SET commands. Should those become part of
"pg_dump --create"'s charter? It seems like not, but I'm not certain.

Another thing that requires some thought is that pg_dumpall is currently
willing to dump ACLs and other properties for template1/template0, though
it does not invoke pg_dump on them. If we wanted to preserve that
behavior while still moving the code that does those things to pg_dump,
pg_dump would have to grow an option that would let it do that. But
I'm not sure how much of that behavior is actually sensible.

This would probably take a pg_dump archive version bump, since I think
we don't currently record enough information for --create to do this
(and we can't just cram the extra commands into the DATABASE entry,
since we don't know whether --create will be specified to pg_restore).
But we've done those before.

Thoughts? Is there a better way to look at this?

regards, tom lane