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
