Re: BUG #17511: Inconsistent permissions on some information_schema tables - Mailing list pgsql-bugs
| From | Kirk Parker |
|---|---|
| Subject | Re: BUG #17511: Inconsistent permissions on some information_schema tables |
| Date | |
| Msg-id | CANwZ8rnHv_Mj6QRVXCnRxKMiqFtR1U4HsYj2qZXwmAY-TzucqQ@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #17511: Inconsistent permissions on some information_schema tables (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: BUG #17511: Inconsistent permissions on some information_schema tables
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
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
\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
pgsql-bugs by date: