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

On Tue, Jul 28, 2015 at 3:33 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2015-07-28 15:27:51 -0400, Robert Haas wrote:
>> On Tue, Jul 28, 2015 at 3:16 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> > On 2015-07-28 15:14:11 -0400, Robert Haas wrote:
>> >> On Tue, Jul 28, 2015 at 3:10 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> >> > DBA creates a database and sets some properties (security labels, gucs,
>> >> > acls) on it. Then goes on to restore a backup. Unfortunately that backup
>> >> > might, or might not, overwrite the properties he configured depending on
>> >> > whether the restored database already contains them and from which
>> >> > version the backup originates.
>> >>
>> >> Well, I think that's just a potential incompatibility between 9.6 and
>> >> previous versions, and a relatively minor one at that. We can't and
>> >> don't guarantee that a dump taken using the 9.3 version of pg_dump
>> >> will restore correctly on any server version except 9.3. It might
>> >> work OK on a newer or older version, but then again it might not.
>> >
>> > Even within a single major version it'll be a bit confusing that one
>> > time a restore yielded the desired result (previously set property
>> > survives) and the next restore it doesn't, because now the backup does
>> > contain the property.
>>
>> How would that happen? We're not gonna back-patch this.
>
> 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".

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