Thread
-
BUG #17511: Inconsistent permissions on some information_schema tables
PG Bug reporting form <noreply@postgresql.org> — 2022-06-06T18:10:43Z
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
David G. Johnston <david.g.johnston@gmail.com> — 2022-06-06T19:59:32Z
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.
-
Re: BUG #17511: Inconsistent permissions on some information_schema tables
Tom Lane <tgl@sss.pgh.pa.us> — 2022-06-06T21:18:22Z
"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 -
Re: BUG #17511: Inconsistent permissions on some information_schema tables
Kirk Parker <khp@equatoria.us> — 2022-06-06T22:12:05Z
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 > -
Re: BUG #17511: Inconsistent permissions on some information_schema tables
Tom Lane <tgl@sss.pgh.pa.us> — 2022-06-07T00:20:11Z
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
-
Re: BUG #17511: Inconsistent permissions on some information_schema tables
Kirk Parker <khp@equatoria.us> — 2022-06-07T00:40:45Z
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 >