another roles related question

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

Responses

Browse pgsql-hackers by date

  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?