Mystery with REVOKE PRIVILEGE
| От | Konstantin Knizhnik |
|---|---|
| Тема | Mystery with REVOKE PRIVILEGE |
| Дата | |
| Msg-id | 85cd06c6-7b2e-483e-b05d-d5ff87b0168d@garret.ru обсуждение |
| Ответы |
Re: Mystery with REVOKE PRIVILEGE
|
| Список | pgsql-hackers |
Hi hackers! I found some very confusing behaviour of REVOKE PRIVILEGE. I wonder whether it is bug or feature: create role reader; create role somebody; grant reader to somebody; grant <ADMIN_ROLE> to somebody; create table t(x integer); grant select on table t to somebody with grant option; begin; set local role somebody; grant select on table t to reader; commit; SELECT * FROM information_schema.role_table_grants AS rtg WHERE grantee='reader'; begin; set local role somebody; revoke all privileges on table t from reader granted by somebody; commit; SELECT * FROM information_schema.role_table_grants AS rtg WHERE grantee='reader'; where <ADMIN_ROLE> is any role with admin permissions under which you logged in. The strange thing is output of last command: postgres=# SELECT * FROM information_schema.role_table_grants AS rtg WHERE grantee='reader'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ----------+---------+---------------+--------------+------------+----------------+--------------+---------------- somebody | reader | postgres | public | t | SELECT | NO | YES (1 row) So granted read privilege was not revoked. But if instead of REVOKE ALL PRIVILEGES` I do `REVOKE SELECT`, then everything is ok: postgres=# begin; BEGIN postgres=*# set local role somebody; SET postgres=*> revoke select on table t from reader granted by somebody; REVOKE postgres=*> commit; COMMIT postgres=# SELECT * FROM information_schema.role_table_grants AS rtg WHERE grantee='reader'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------+----------------+--------------+---------------- (0 rows) The critical thing is this grant statement: grant <ADMIN_ROLE> to somebody; If it is skipped, then the problem is not reproduced.
В списке pgsql-hackers по дате отправления: