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
