Thread: BUG #3928: INFORMATION_SCHEMA does not give results if a user is allowed only access via ROLE

The following bug has been logged online:

Bug reference:      3928
Logged by:          Bart Heupers
Email address:      bart@sara.nl
PostgreSQL version: 8.2.4
Operating system:   Linux & Windows
Description:        INFORMATION_SCHEMA does not give results if a user is
allowed only access via ROLE
Details:

If a user wants to get data from the INFORMATION_SCHEMA   about a table and
he has access to the table via a ROLE
then the INFORMATION_SCHEMA will not return results.

If access is given directly to the user then the INFORMATION_SCHEMA will
give results.

For example for USER BART, ROLE RADAR_READ

CREATE ROLE RADAR_READ;
GRANT RADAR_READ TO BART;
GRANT USAGE ON SCHEMA RADAR TO RADAR_READ;
GRANT SELECT,REFERENCES
ON TABLE RADAR.RADAR
TO RADAR_READ;

This will NOT give results from the query to retrieve the primary key for
the RADAR.RADAR table:

select k.column_name, c.data_type
from information_schema.key_column_usage k
join information_schema.table_constraints t
using(constraint_schema, constraint_name)
join information_schema.columns c
on c.table_schema = t.table_schema
and c.table_name = t.table_name
and k.column_name = c.column_name
where t.table_schema = 'radar'
and t.table_name = 'track_object'
and t.constraint_type = 'PRIMARY KEY'
order by k.ordinal_position

But if access is given directly to the user with :

grant usage on schema radar to bart;

GRANT SELECT,REFERENCES
ON TABLE RADAR.RADAR
TO bart;

then the above query will give the required results.

This seems to me undesired behaviour of ROLES with the INFORMATION_SCHEMA

"Bart Heupers" <bart@sara.nl> writes:
> If a user wants to get data from the INFORMATION_SCHEMA   about a table and
> he has access to the table via a ROLE
> then the INFORMATION_SCHEMA will not return results.

There seems to be a mistake in your example.  You show granting privilege
to table radar.radar:

> GRANT SELECT,REFERENCES
> ON TABLE RADAR.RADAR
> TO RADAR_READ;

but the query is looking for radar.track_object:

> where t.table_schema = 'radar'
> and t.table_name = 'track_object'

With the query corrected to have t.table_name = 'radar' it works as
expected AFAICT.

            regards, tom lane