Обсуждение: BUG #17511: Inconsistent permissions on some information_schema tables

The following bug has been logged on the website:

Bug reference:      17511
Logged by:          Kirk Parker
Email address:      khp@equatoria.us
PostgreSQL version: 13.7
Operating system:   AWS Linux 2 -- 4.14.276-211.499.amzn2.x86_64
Description:

In attempting to write a schema-documenting template, I ran across what
might be a minor bug.  Given this list of roles in the relevant database:

                                       List of roles
   Role name |                         Attributes                         |
Member of

-----------+------------------------------------------------------------+-----------
   apache    |                                                            |
{}
   p3dev     | Superuser                                                  |
{}
   postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS |
{}

The following query produces the expected output (list of foreign tables
referenced by foreign-key-columns) for a role with the Superuser attribute,
but shows zero results for an non-Superuser role:

  select kcu.column_name as my_column, ccu.table_name as foreign_table,
ccu.column_name as foreign_column
  from information_schema.table_constraints AS tc
    join information_schema.key_column_usage AS kcu ON tc.constraint_name =
kcu.constraint_name
    join information_schema.constraint_column_usage AS ccu ON
ccu.constraint_name = tc.constraint_name
  where constraint_type = 'FOREIGN KEY' AND tc.table_name='some_table';

The table at issue is constraint_column_usage--the ordinary role 'apache'
does not have SELECT rights to that table, though it does to the other two
catalog tables used by this query.

Yes, there's an easy workaround by just GRANTing SELECT on that table to
'apache', but it seems like an odd inconsistency. Interestingly, the same
limitation does not apply to pg_catalog.pg_get_constraintdef(), which is
used by psql's \dt command, but that query does not produce the local column
name as a separate result column (which is more useful for my immediate
purpose here.)

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jun 6, 2022 at 11:50 AM PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> The table at issue is constraint_column_usage--the ordinary role 'apache'
>> does not have SELECT rights to that table, though it does to the other two
>> catalog tables used by this query.

> Haven't tried to duplicate but I'm not following.

constraint_column_usage certainly does/should have public read access:

postgres=# \z information_schema.constraint_column_usage
                                               Access privileges
       Schema       |          Name           | Type |     Access privileges     | Column privileges | Policies
--------------------+-------------------------+------+---------------------------+-------------------+----------
 information_schema | constraint_column_usage | view | postgres=arwdDxt/postgres+|                   |
                    |                         |      | =r/postgres               |                   |
(1 row)

I think what the OP is complaining about is that its *contents*
are filtered, ie you can't see rows about tables you don't have
any privileges on.  However, that behavior is mandated by the
SQL standard, and in our view the sole reason for existence
of the information_schema views is to be standard-conforming.
So we won't be removing that filter unless you can talk the
SQL committee into dropping that requirement.  If you want
an unfiltered view, look directly at the system catalogs.

            regards, tom lane

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