another roles related question

Started by Joe Conwayover 20 years ago4 messages
#1Joe Conway
mail@joeconway.com

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

#2Stephen Frost
sfrost@snowman.net
In reply to: Joe Conway (#1)
Re: another roles related question

* 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

#3Joe Conway
mail@joeconway.com
In reply to: Stephen Frost (#2)
Re: another roles related question

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

#4Stephen Frost
sfrost@snowman.net
In reply to: Joe Conway (#3)
Re: another roles related question

* 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