missing data in information_schema grant_* tables?
От | Fabien COELHO |
---|---|
Тема | missing data in information_schema grant_* tables? |
Дата | |
Msg-id | alpine.DEB.2.00.1001151130390.3006@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: missing data in information_schema grant_* tables?
|
Список | pgsql-hackers |
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 INSERTON 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.
В списке pgsql-hackers по дате отправления: