Search Postgresql Archives

Type Ownership-change vs. Grant/Revoke

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux