Re: BUG #1937: Parts of information_schema only accessible to owner - Mailing list pgsql-bugs

Peter Eisentraut <peter_e@gmx.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