pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17511: Inconsistent permissions on some information_schema tables
6+ messages / 4 participants
[nested] [flat]
* BUG #17511: Inconsistent permissions on some information_schema tables
@ 2022-06-06 18:10 PG Bug reporting form <noreply@postgresql.org>
2022-06-06 19:59 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2022-06-06 18:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: khp@equatoria.us
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.)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17511: Inconsistent permissions on some information_schema tables
2022-06-06 18:10 BUG #17511: Inconsistent permissions on some information_schema tables PG Bug reporting form <noreply@postgresql.org>
@ 2022-06-06 19:59 ` David G. Johnston <david.g.johnston@gmail.com>
2022-06-06 21:18 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2022-06-06 19:59 UTC (permalink / raw)
To: khp@equatoria.us, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17511: Inconsistent permissions on some information_schema tables
2022-06-06 18:10 BUG #17511: Inconsistent permissions on some information_schema tables PG Bug reporting form <noreply@postgresql.org>
2022-06-06 19:59 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables David G. Johnston <david.g.johnston@gmail.com>
@ 2022-06-06 21:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-06 22:12 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Kirk Parker <khp@equatoria.us>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-06-06 21:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: khp@equatoria.us, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17511: Inconsistent permissions on some information_schema tables
2022-06-06 18:10 BUG #17511: Inconsistent permissions on some information_schema tables PG Bug reporting form <noreply@postgresql.org>
2022-06-06 19:59 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables David G. Johnston <david.g.johnston@gmail.com>
2022-06-06 21:18 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-06-06 22:12 ` Kirk Parker <khp@equatoria.us>
2022-06-07 00:20 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kirk Parker @ 2022-06-06 22:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17511: Inconsistent permissions on some information_schema tables
2022-06-06 18:10 BUG #17511: Inconsistent permissions on some information_schema tables PG Bug reporting form <noreply@postgresql.org>
2022-06-06 19:59 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables David G. Johnston <david.g.johnston@gmail.com>
2022-06-06 21:18 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Tom Lane <tgl@sss.pgh.pa.us>
2022-06-06 22:12 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Kirk Parker <khp@equatoria.us>
@ 2022-06-07 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-07 00:40 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Kirk Parker <khp@equatoria.us>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2022-06-07 00:20 UTC (permalink / raw)
To: Kirk Parker <khp@equatoria.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #17511: Inconsistent permissions on some information_schema tables
2022-06-06 18:10 BUG #17511: Inconsistent permissions on some information_schema tables PG Bug reporting form <noreply@postgresql.org>
2022-06-06 19:59 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables David G. Johnston <david.g.johnston@gmail.com>
2022-06-06 21:18 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Tom Lane <tgl@sss.pgh.pa.us>
2022-06-06 22:12 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Kirk Parker <khp@equatoria.us>
2022-06-07 00:20 ` Re: BUG #17511: Inconsistent permissions on some information_schema tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-06-07 00:40 ` Kirk Parker <khp@equatoria.us>
0 siblings, 0 replies; 6+ messages in thread
From: Kirk Parker @ 2022-06-07 00:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-06-07 00:40 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-06 18:10 BUG #17511: Inconsistent permissions on some information_schema tables PG Bug reporting form <noreply@postgresql.org>
2022-06-06 19:59 ` David G. Johnston <david.g.johnston@gmail.com>
2022-06-06 21:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-06 22:12 ` Kirk Parker <khp@equatoria.us>
2022-06-07 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-06-07 00:40 ` Kirk Parker <khp@equatoria.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox