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

On Tue, Jul 28, 2015 at 03:36:13PM -0400, Robert Haas wrote:
> On Tue, Jul 28, 2015 at 3:33 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > Hm? Let me try again: If the admin does a ALTER DATABASE ... SET guc =
> > ... *before* restoring a backup and the backup does contain a setting
> > for the same guc, but with a different value it'll overwrite the
> > previous explicit action by the DBA without any warning. If the backup
> > does *not* contain that guc the previous action survives. That's
> > confusing, because you're more likely to be in the 'the backup does not
> > contain the guc' situation when testing where it thus will work.
>
> True. But I don't think modifying a database before restoring into it
> is terribly supported. Even pg_dump --clean, which is supposed to do
> this sort of thing, doesn't seem to work terribly reliably. We could
> try to fix this by having a command like ALTER DATABASE ... RESET ALL
> that we issue before restoring the settings, but I'm afraid that will
> take us into all sorts of unreasonable scenarios that are better just
> labeled as "don't do that".

Andres's example is a harbinger of the semantic morass ahead. Excepting
database objects and the "public" schema object, pg_dump and pg_dumpall mutate
only the objects they CREATE. They consistently restore object properties
(owner, ACLs, security label, etc.) if and only if issuing a CREATE statement
for the object. For example, restoring objects contained in a schema without
restoring the schema itself changes none of those schema properties. pg_dump
and pg_dumpall have mostly followed that rule for databases, too, but they
depart from it for comment and security label. That was a mistake. We can't
in general mutate an existing database to match, because we can't mutate the
encoding, datcollate or datctype. Even discounting that problem, I value
consistency with the rest of the dumpable object types.

I suppose many folks using pg_dump without --create think of it as a dump of a
database itself, not a dump of a database's contents. They would benefit from
a dump that mutates the target database to match the properties of the source
database. Helping those folks does not offset the aforementioned losses.

Thanks,
nm