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
