Thread: BUG #10588: inconsistent permissions for InformationSchema tables..
BUG #10588: inconsistent permissions for InformationSchema tables..
From
tysonzwicker@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 10588 Logged by: Tyson Zwicker Email address: tysonzwicker@gmail.com PostgreSQL version: 9.1.9 Operating system: Win 7 x64 Description: In InformationSchema the "constraint_column_usage" table is only accessible via the super user account, despite other tables (I tested table_constraints and key_column_usage) being accessible to any account that was GRANTed ALL permissions. Furthermore, attempting to select from "constraint_column_usage" did not throw an error of any kind, it just returned 0 rows. Once I did the same query logged in as "postgres" the query returned rows. I feel that a> An error should be thrown rather than returning 0 rows and that b> the access of tables in InformationSchema should be consistent across ALL of the tables.
tysonzwicker@gmail.com writes: > In InformationSchema the "constraint_column_usage" table is only accessible > via the super user account, despite other tables (I tested table_constraints > and key_column_usage) being accessible to any account that was GRANTed ALL > permissions. It's accessible enough, unless you changed the default: GRANT SELECT ON constraint_column_usage TO PUBLIC; > Furthermore, attempting to select from "constraint_column_usage" did not > throw an error of any kind, it just returned 0 rows. Once I did the same > query logged in as "postgres" the query returned rows. This view follows the SQL standard, which says you can only see rows that describe tables you own. A superuser is considered to belong to any owning role, which is why "postgres" can always see all rows in this view. > I feel that a> An error should be thrown rather than returning 0 rows and > that b> the access of tables in InformationSchema should be consistent > across ALL of the tables. A pretty large fraction of the information_schema views have comparable visibility restrictions. In any case, since the entire point of information_schema is to present a standards-compliant view of the database, we're not going to go against the clear requirement of the standard. You're welcome to define your own views that behave however you like, of course; the underlying pg_catalog tables are all world-readable. (Which is something we've gotten flak about from other parties, but whatever ...) regards, tom lane