Skip site navigation (1) Skip section navigation (2)

missing data in information_schema grant_* tables?

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: missing data in information_schema grant_* tables?
Date: 2010-01-15 11:00:52
Message-ID: alpine.DEB.2.00.1001151130390.3006@localhost.localdomain (view raw or flat)
Thread:
Lists: 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 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.

Responses

pgsql-hackers by date

Next:From: Boszormenyi ZoltanDate: 2010-01-15 11:09:45
Subject: Re: Streaming replication, loose ends
Previous:From: Pavel StehuleDate: 2010-01-15 10:44:22
Subject: Re: Streaming replication, loose ends

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group