The following bug has been logged online: Bug reference: 3928 Logged by: Bart Heupers Email address: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
