Fwd: Type Ownership-change vs. Grant/Revoke: Grantors not updated with Ownership Changes
От | Bekoe, Evans Akai |
---|---|
Тема | Fwd: Type Ownership-change vs. Grant/Revoke: Grantors not updated with Ownership Changes |
Дата | |
Msg-id | CAJjk24v56r5hRzQ3ni1W24VX-reHus=9v+J3eFHndsnpA4JYZw@mail.gmail.com обсуждение исходный текст |
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: [HACKERS] BUG #10728: json_to_recordset with nested json objects NULLs columns