another roles related question
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
* Joe Conway (mail@joeconway.com) wrote:
Roles cause a problem for the information schema view table_privileges.
Right.
[...]
Is this something we should worry about? Or do we just put a warning in
the docs?
I've already submitted a patch which should correct this. It also adds
a new SQL function which determines if a given user is in a specific
role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER.
Hopefully it'll get applied in some form (I havn't had any response to
it yet at all but I'm guessing people are just busy atm...).
Thanks,
Stephen
Stephen Frost wrote:
Is this something we should worry about? Or do we just put a warning in
the docs?I've already submitted a patch which should correct this. It also adds
a new SQL function which determines if a given user is in a specific
role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER.Hopefully it'll get applied in some form (I havn't had any response to
it yet at all but I'm guessing people are just busy atm...).
Oh, cool. Sorry for the noise. I'll rummage through the archives and
have a look.
Thanks (again),
Joe
* Joe Conway (mail@joeconway.com) wrote:
Stephen Frost wrote:
I've already submitted a patch which should correct this. It also adds
a new SQL function which determines if a given user is in a specific
role. It also implements SET ROLE, CURRENT_ROLE and SYSTEM_USER.Oh, cool. Sorry for the noise. I'll rummage through the archives and
have a look.
What you want to look for is, specifically:
Subject: Re: [PATCHES] Roles - SET ROLE Updated
Message-ID: <20050703183407.GM24207@ns.snowman.net>
It went through a couple revisions.
Thanks,
Stephen