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

On Tue, Jul 28, 2015 at 3:10 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
> On 2015-07-28 15:05:01 -0400, Robert Haas wrote:
>> On Tue, Jul 28, 2015 at 3:03 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> > On 2015-07-28 14:58:26 -0400, Robert Haas wrote:
>> >> Yes, I think we should make restoring the database's properties the
>> >> job of pg_dump and remove it completely from pg_dumpall, unless we can
>> >> find a case where that's really going to break things.
>> >
>> > CREATE DATABASE blarg;
>> > SECURITY LABEL ON blarg IS 'noaccess';
>> > ALTER DATABASE blarg SET default_tablespace = space_with_storage;
>> > pg_restore
>> > -> SECURITY LABEL ON blarg IS 'allow_access';
>> > -> ALTER DATABASE blarg SET default_tablespace = space_without_storage;
>> >
>> > That's probably not sufficient reasons not to go that way, but I do
>> > think there's a bunch more issues like that.
>>
>> Could you use some complete sentences to describe what the actual
>> issue is? I can't make heads or tails of what you wrote there.
>
> 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.

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