Thread: 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