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

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.)

\d po_mast

 Column         | Type...
----------------+------------------
 id             | integer
 store_id       | integer
 emp_id         | integer
 ven_id         | integer
 ...
 po_stat        | integer
...
Foreign-key constraints:
    "po_mast_emp_id_fkey" FOREIGN KEY (emp_id) REFERENCES employee(id)
    "po_mast_po_stat_fkey" FOREIGN KEY (po_stat) REFERENCES po_status(id)
    "po_mast_store_id_fkey" FOREIGN KEY (store_id) REFERENCES stores(id)
    "po_mast_ven_id_fkey" FOREIGN KEY (ven_id) REFERENCES vendor(id)

\z po_mast
 Schema |  Name   | Type  |  Access privileges   ...
--------+---------+-------+----------------------
 public | po_mast | table | p3dev=arwdDxt/p3dev +
        |         |       | apache=arwdDxt/p3dev

\z employee
 Schema |   Name   | Type  |  Access privileges
--------+----------+-------+----------------------
 public | employee | table | p3dev=arwdDxt/p3dev +
        |          |       | apache=arwdDxt/p3dev

\z po_status
 Schema |   Name    | Type  |  Access privileges
--------+-----------+-------+----------------------
 public | po_status | table | p3dev=arwdDxt/p3dev +
        |           |       | apache=arwdDxt/p3dev

\z stores
 Schema |  Name  | Type  |  Access privileges
--------+--------+-------+----------------------
 public | stores | table | p3dev=arwdDxt/p3dev +
        |        |       | apache=arwdDxt/p3dev

\z vendor
 Schema |  Name  | Type  |  Access privileges
--------+--------+-------+----------------------
 public | vendor | table | p3dev=arwdDxt/p3dev +
        |        |       | apache=arwdDxt/p3dev


On Mon, Jun 6, 2022 at 2:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "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
>