From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | "Hackers (PostgreSQL)" <pgsql-hackers(at)postgresql(dot)org>, Stephen Frost <sfrost(at)snowman(dot)net> |
Subject: | another roles related question |
Date: | 2005-07-10 00:12:55 |
Message-ID: | 42D06807.6070908@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2005-07-10 00:17:18 | Re: another roles related question |
Previous Message | Alvaro Herrera | 2005-07-09 20:50:52 | Re: Must be owner to truncate? |