Hello pgdevs,

I'm trying to use the information_schema, and I'm looking at the grant
tables. ISTM that some views do not show all expected permissions.

 psql> CREATE TABLE foo();
 psql> CREATE USER calvin NOLOGIN;
 psql> GRANT SELECT ON TABLE foo TO calvin;
 psql> GRANT INSERT ON TABLE foo TO PUBLIC; -- not really a good idea

 psql> \dp
                            Access privileges
 Schema | Name | Type  |   Access privileges   | Column access privileges
--------+------+-------+-----------------------+--------------------------
 public | foo  | table | fabien=arwdDxt/fabien |
                       : calvin=r/fabien
                       : =a/fabien

INSERT to PUBLIC is shown on the last line of the access privileges column. However, when looking at the information_schema:

 psql> SELECT grantor, grantee, privilege_type
       FROM information_schema.role_table_grants
       WHERE table_name = 'foo';
 grantor | grantee | privilege_type
---------+---------+----------------
 fabien  | fabien  | SELECT
 fabien  | fabien  | INSERT
 fabien  | fabien  | UPDATE
 fabien  | fabien  | DELETE
 fabien  | fabien  | TRUNCATE
 fabien  | fabien  | REFERENCES
 fabien  | fabien  | TRIGGER
 fabien  | calvin  | SELECT
(8 rows)

My point is that the grant to "PUBLIC" does not show in the information schema. However, it appears in the table_privileges view:

psql> SELECT grantor, grantee, privilege_type FROM information_schema.table_privileges WHERE table_name='foo';
 grantor | grantee | privilege_type
---------+---------+----------------
 ... same as previous query ...
 fabien  | PUBLIC  | INSERT

(1) Would you agree that it is a "bug"? That is, if the grantee is PUBLIC, it is an enabled role for the current user, so it should appear in the role_table_grants view...

(2) If yes is the answer to the previous question, and in order to fix it, would it be acceptable to drop the view definitions of role_table_grants based on the pg_catalog and rely on the table_privileges view instead, if possible (it looks so, but there may be some issues)? Or should the current view definition be simply reworked?

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to