From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kirk Parker <khp@equatoria.us>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17511: Inconsistent permissions on some information_schema
 tables
In-reply-to: 
 <CANwZ8rnHv_Mj6QRVXCnRxKMiqFtR1U4HsYj2qZXwmAY-TzucqQ@mail.gmail.com>
References: <17511-ee45925b30436a87@postgresql.org>
 <CAKFQuwY=7GALr6LJqT3U2uhSf1p_49dc2dyQmMgkXv1s-xM+0g@mail.gmail.com>
 <953810.1654550302@sss.pgh.pa.us>
 <CANwZ8rnHv_Mj6QRVXCnRxKMiqFtR1U4HsYj2qZXwmAY-TzucqQ@mail.gmail.com>
Comments: In-reply-to Kirk Parker <khp@equatoria.us>
	message dated "Mon, 06 Jun 2022 15:12:05 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <975365.1654561211.1@sss.pgh.pa.us>
Date: Mon, 06 Jun 2022 20:20:11 -0400
Message-ID: <975366.1654561211@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/975366.1654561211%40sss.pgh.pa.us>
Precedence: bulk

Kirk Parker <khp@equatoria.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