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
