Fwd: Type Ownership-change vs. Grant/Revoke: Grantors not updated with Ownership Changes

Hi,

One of the consequence of the behaviour described in the forwarded email is
that, restoring from pg_dump may fail.
This is because pg_dump first revokes all privileges on objects and then
Grants them in the context of the Grantors.
If the Grantor (previously an Owner for example) no longer has permissions
to Grant, the operation fails.

In the setup below, I get the following state after trying to inject the
pg_dump fails:

postgres=#  \dT+ test.type
                                List of data types
 Schema | Name | Internal name | Size | Elements | Access privileges |
Description
--------+------+---------------+------+----------+-------------------+-------------
 test   | type | type          | 4    | a        |                   |


Not updating the Grantor info for Types is inconsistent with what is done
in the case of Tables. This may lead to unrecoverable pg_dumps.

--Evans


---------- Forwarded message ----------
From: Bekoe, Evans Akai <evans.akai.bekoe@zalando.de>
Date: Mon, Jun 23, 2014 at 2:23 PM
Subject: Type Ownership-change vs. Grant/Revoke
To: pgsql-general@postgresql.org
Cc: Team Eng Platform Database <tech-eng-platform-database@zalando.de>


Hi,

I have here a question concerning how ACLs are handled for Types.
For Tables, when Ownership changes, Grantor info is also changed; hence
changing Ownership and Grants does not lead to the same privilege but from
different Grantors.
For Types though, you can easily end up with an ACL like {user=U/grantor1,
user=U/grantor2}.
Why are ACLs implemented like this for Types?
What is the standard way to Revoke privileges from users since this only
takes place in the context of the case when the Grantor is the current
Owner? (i.e. seems there's the need for something like: Revoke Usage on
Type type1  from user1 FOR ALL GRANTORS)

DEMO:

-- SOME SETUPS
postgres=# create schema test;
CREATE SCHEMA
postgres=# create table test.tab();
CREATE TABLE
postgres=# create role owner1;
CREATE ROLE
postgres=# create role owner2;
CREATE ROLE
postgres=# create role role1;
CREATE ROLE
postgres=# grant usage on schema test to owner1, owner2;
GRANT

-- Table Ownership-change vs. GRANT/REVOKE
postgres=# alter table test.tab owner to owner1;
ALTER TABLE
postgres=> grant select on test.tab to role1;
GRANT
postgres=> reset role;
RESET
postgres=#  alter table test.tab owner to owner2;
ALTER TABLE
postgres=# set role owner2;
SET
postgres=> grant select on test.tab to role1;
GRANT
postgres=> \dp+ test.tab
                            Access privileges
 Schema | Name | Type  |   Access privileges   | Column access privileges
--------+------+-------+-----------------------+--------------------------
 test   | tab  | table | owner2=arwdDxt/owner2+|
        |      |       | role1=r/owner2        |


-- Type Ownership-change vs. GRANT/REVOKE
postgres=> reset role;
RESET
postgres=# create type test.type as enum ('a');
CREATE TYPE
postgres=# alter type test.type owner to owner1;
ALTER TYPE
postgres=# set role to owner1;
SET
postgres=> grant usage on type test.type to role1;
GRANT
postgres=> reset role;
RESET
postgres=# alter type test.type owner to owner2;
ALTER TYPE
postgres=# set role to owner2;
SET
postgres=> grant usage on type test.type to role1;
GRANT
postgres=> \dT+ test.type
                                   List of data types
 Schema |   Name    | Internal name | Size | Elements | Access privileges |
Description
--------+-----------+---------------+------+----------+-------------------+-------------
 test   | test.type | type          | 4    | a        | =U/owner1        +|
        |           |               |      |          | role1=U/owner1  +|
        |           |               |      |          | role1=U/owner2    |


postgres=> select version();

version
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.4 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit


--Evans