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