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

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.