Thread: BUG #17511: Inconsistent permissions on some information_schema tables
BUG #17511: Inconsistent permissions on some information_schema tables
From
PG Bug reporting form
Date:
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.)
Re: BUG #17511: Inconsistent permissions on some information_schema tables
From
"David G. Johnston"
Date:
On Mon, Jun 6, 2022 at 11:50 AM PG Bug reporting form <noreply@postgresql.org> wrote:
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:
[...]
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.)
Haven't tried to duplicate but I'm not following.
information_schema provides a view of the database that is filtered by user permissions. pg_catalog does not take into consideration permissions. This would be on the contents. All users can select from either without getting a permission denied error.
David J.
"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
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
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
Thanks, Tom.
As I hinted at the beginning, it wasn't *difficult* to just use the pg_catalog-based query and use regexp_match() to pull out the relevant parts I needed from the foreign-key description. It's just that I started with the other query since it seemed to already offer the columns I wanted; and when I started digging into why it wasn't working, the inconsistency rubbed me the wrong way.
For sure, though, it's not our/your job to fix inconsistencies in the SQL spec itself.
On Mon, Jun 6, 2022 at 5:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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