another roles related question

Поиск
Список
Период
Сортировка
От Joe Conway
Тема another roles related question
Дата
Msg-id 42D06807.6070908@joeconway.com
обсуждение исходный текст
Ответы Re: another roles related question  (Stephen Frost <sfrost@snowman.net>)
Список pgsql-hackers
Roles cause a problem for the information schema view table_privileges. 
For example:


CREATE TABLE tbl_1
(    f1    int,    f2    text
);

INSERT INTO tbl_1 VALUES(1, 'a');
REVOKE ALL ON tbl_1 FROM public;
CREATE USER user1;
CREATE USER user2;
CREATE ROLE role1;

GRANT ALL ON tbl_1 TO role1;
GRANT ALL ON tbl_1 TO user1;
GRANT role1 TO user2;

-- information_schema.table_privileges is correct for user1
SET SESSION AUTHORIZATION user1;
select * from information_schema.table_privileges
where table_name = 'tbl_1' and privilege_type = 'SELECT'; grantor  | grantee | table_catalog | table_schema |
table_name| 
 
privilege_type | is_grantable | with_hierarchy
----------+---------+---------------+--------------+------------+----------------+--------------+----------------
postgres| user1   | regression    | public       | tbl_1      | 
 
SELECT         | NO           | NO
(1 row)

SELECT * FROM tbl_1; f1 | f2
----+----  1 | a
(1 row)

-- information_schema.table_privileges is incorrect for user2
SET SESSION AUTHORIZATION user2;
select * from information_schema.table_privileges
where table_name = 'tbl_1' and privilege_type = 'SELECT'; grantor | grantee | table_catalog | table_schema | table_name
|
 
privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)

SELECT * FROM tbl_1; f1 | f2
----+----  1 | a
(1 row)


I think the problem lies with the fact that user2 lies a level down from 
that which is actually granted access. And since roles/users are 
hierarchical, it is possible to go more than 1 level deep -- hence a 
recursive join is really needed to fix this AFAICS.

Is this something we should worry about? Or do we just put a warning in 
the docs?

Joe


В списке pgsql-hackers по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: Must be owner to truncate?
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: another roles related question