Re: security labels on databases are bad for dump & restore

On Thu, Jul 23, 2015 at 12:14:14PM -0400, Robert Haas wrote:
> On Wed, Jul 22, 2015 at 3:42 PM, Adam Brightwell <adam(dot)brightwell(at)crunchydatasolutions(dot)com> wrote:
> >> I like Noah's proposal of having pg_dump --create reproduce all
> >> database-level state.
> >
> > Should it be enabled by default? If so, then wouldn't it make more
> > sense to call it --no-create and do the opposite? So, --no-create
> > would exclude rather than include database-level information? Would
> > enabling it by default cause issues with the current expected use of
> > the tool by end users?
>
> This seems a bit hairy to me. If we want to transfer responsibility
> for dumping this stuff from pg_dumpall to pg_dump, I have no problem
> with that at all. But doing it only when --create is specified seems
> odd. Then, does pg_dumpall -g dump it or not?

The principle I had in mind was to dump ACLs, pg_db_role_setting entries,
comments and security labels if and only if we emit a CREATE statement for the
object they modify. That is already the rule for objects located inside
databases. Since "pg_dumpall -g" does not emit CREATE DATABASE statements[1],
it would not dump these attributes of databases.

> If it does, then we're
> sorta dumping it in two places when --create is used. If it doesn't,
> then when --create is not used we're doing it nowhere.

Yep. Plain "pg_dump" dumps the contents of a database without dumping the
database itself. I don't like that as a default, but we're stuck with it.

[1] "pg_dumpall -g --binary-upgrade" _does_ emit CREATE DATABASE statements,
so _it_ would dump these attributes.