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

On Thu, Sep 29, 2016 at 11:29:09AM -0400, Tom Lane wrote:
> 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:

This looks good so far. It covers most of the problems from TODO item
"Refactor handling of database attributes between pg_dump and pg_dumpall".

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

dumpDatabase() isn't fully committed to that doctrine today; it skips most
database properties, but it does dump COMMENT ON DATABASE and any
pg_shseclabel entry for the database. Would you make it stop doing that?

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

"pg_dump --create" should emit ALTER ROLE IN DATABASE SET. Regeardless,
pg_dump needs the roles in place for object ownership and ALTER DEFAULT
PRIVILEGES. The latter is conceptually similar to ALTER ROLE IN DATABASE SET.

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

It's appropriate to restore the template1/template0 attributes one can cover
without connecting to the database (pg_database, pg_db_role_setting,
pg_shseclabel, pg_shdescription). template0 modifications that require a
connection are essentially unsupported, so ignore those. I think, ideally, a
full-cluster dump should include template1 contents. For template1, then, one
would want pg_dump to emit everything except the CREATE DATABASE statement.
That's a regrettable wart.

Thanks,
nm