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

From: Noah Misch <noah(at)leadboat(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: "Re: Question about grant create on database and pg_dump/pg_dumpall
Date: 2016-10-11 03:30:49
Message-ID: 20161011033049.GA1035080@tornado.leadboat.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2016-10-11 03:39:23 Re: multivariate statistics (v19)
Previous Message Tsunakawa, Takayuki 2016-10-11 02:36:06 Re: Supporting huge pages on Windows