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
