Re: BUG #1937: Parts of information_schema only accessible to owner

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> The information schema currently follows SQL 1999. Interestingly, the
> requirement to "blank out" the column defaults of non-owned tables was
> apparently dropped in SQL 2003. Clearly, we need to review the
> information schema for SQL 2003 conformance.

Yeah. Not only that, but they changed the WHERE clause: where formerly
it restricted you to seeing tables you own, it now allows you to see
anything you have any granted privileges on. SQL99 reads

WHERE (C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME) IN
(SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES
WHERE (SCHEMA_OWNER IN ( 'PUBLIC', CURRENT_USER )
OR
SCHEMA_OWNER IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )))

but what I see in 2003 is

WHERE (C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME) IN
(SELECT CP.TABLE_CATALOG, CP.TABLE_SCHEMA, CP.TABLE_NAME, CP.COLUMN_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES AS CP
WHERE (CP.GRANTEE IN ( 'PUBLIC', CURRENT_USER )
OR
CP.GRANTEE IN ( SELECT ROLE_NAME FROM ENABLED_ROLES )))

Probably there are similar changes in other views.

Not sure if there's time to do this for 8.1 ... I don't really have time
to grovel through it, do you?

regards, tom lane