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

On Wed, Jul 29, 2015 at 12:39 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> 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.

What we've proven so far (if Craig's comments are to be believed) is
that the oft-recommended formula of pg_dumpall -g plus pg_dump of each
database doesn't completely work. That's absolutely gotta be fixed.
I'm open to whatever ideas you or others may have about how to fix
that, but it doesn't seem to me that changing the behavior of pg_dump
only when -c is specified gets us any closer to that goal.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company