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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > On Mon, Jun 6, 2022 at 11:50 AM PG Bug reporting form <
> > noreply(at)postgresql(dot)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
>