Thread: missing data in information_schema grant_* tables?

missing data in information_schema grant_* tables?

From
Fabien COELHO
Date:
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.


Re: missing data in information_schema grant_* tables?

From
Peter Eisentraut
Date:
On fre, 2010-01-15 at 12:00 +0100, Fabien COELHO wrote:
> 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...

The whole point of role_table_grants is that it shows everything that
table_privileges shows except privileges granted to public.  So the
behavior you observe is correct.



Re: missing data in information_schema grant_* tables?

From
Fabien COELHO
Date:
Dear Peter,

>> (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...
>
> The whole point of role_table_grants is that it shows everything that
> table_privileges shows except privileges granted to public.  So the
> behavior you observe is correct.

This is not my understanding of ISO/IEC 9075-11:2003(E), page 57 :

"5.39 ROLE_TABLE_GRANTS view

Function

Identifies the privileges on tables defined in this catalog that are 
available or granted by the currently applicable roles."

From the definition above, ISTM that a privilege granted to PUBLIC should 
also appear, both because it is granted by me and available to me.

Moreover, if I execute the SELECT of the view definition provided in the 
standard (a little bit simplified, and executed on the information schema 
instead of the "definition schema"), the PUBLIC stuff is displayed :
  psql> SELECT grantor, grantee, table_name        FROM information_schema.table_privileges        WHERE grantee IN
(SELECTrole_name FROM information_schema.enabled_roles)           OR grantor IN (SELECT role_name FROM
information_schema.enabled_roles);
   ...   fabien   | calvin   | foo   fabien   | PUBLIC   | foo

I think that the view definition in postgresql could simply reuse the view 
defined in the standard.

-- 
Fabien.


Re: missing data in information_schema grant_* tables?

From
Peter Eisentraut
Date:
On fre, 2010-01-15 at 15:06 +0100, Fabien COELHO wrote:
> > The whole point of role_table_grants is that it shows everything that
> > table_privileges shows except privileges granted to public.  So the
> > behavior you observe is correct.
> 
> This is not my understanding of ISO/IEC 9075-11:2003(E), page 57 :

You're right, it's a bug, but it's already fixed in 8.5. :-)




Re: missing data in information_schema grant_* tables?

From
Fabien COELHO
Date:
>> This is not my understanding of ISO/IEC 9075-11:2003(E), page 57 :
>
> You're right, it's a bug, but it's already fixed in 8.5. :-)

Great! :-)

Thanks,

-- 
Fabien.