Re: BUG #17511: Inconsistent permissions on some information_schema tables

Thanks, Tom.

As I hinted at the beginning, it wasn't *difficult* to just use the
pg_catalog-based query and use regexp_match() to pull out the relevant
parts I needed from the foreign-key description. It's just that I started
with the other query since it seemed to already offer the columns I wanted;
and when I started digging into why it wasn't working, the
inconsistency rubbed me the wrong way.

For sure, though, it's not our/your job to fix inconsistencies in the SQL
spec itself.

On Mon, Jun 6, 2022 at 5:20 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Kirk Parker <khp(at)equatoria(dot)us> writes:
> > Tom Lane's answer makes sense, but I can't see where the permissions are
> > lacking--the user seems to have all needed rights on all the relevant
> > tables (and the same as the DB owner, for that matter.)
>
> [ looks closer... ] constraint_column_usage has a tighter filter than
> I would have guessed:
>
> \d+ information_schema.constraint_column_usage
> ...
> View definition:
> ...
> WHERE pg_has_role(x.tblowner, 'USAGE'::text);
>
> So you have to actually *be* the table owner, or at least have been
> GRANTed that role, in order to see entries about the table in it.
> This seems to match what it says in the spec, but I have to confess
> bafflement as to why they made this one more restrictive than
> either table_constraints or key_column_usage.
>
> regards, tom lane
>