BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website: Bug reference: 19703 Logged by: Qifan Liu Email address: imchifan@163.com PostgreSQL version: 18.6 Operating system: Linux/amd64 Description: For a newly created sequence, has_sequence_privilege reports that the owner has USAGE, but information_schema.usage_privileges omits that privilege. The view reports the owner privilege only after the same USAGE privilege is redundantly granted explicitly. Steps to reproduce ------------------ \pset tuples_only on \pset format unaligned DROP SEQUENCE IF EXISTS bugseer_postgres_00004_seq_acl; CREATE SEQUENCE bugseer_postgres_00004_seq_acl; SELECT 'implicit_privilege=' || has_sequence_privilege( current_user, 'bugseer_postgres_00004_seq_acl', 'USAGE'); SELECT 'implicit_view_rows=' || count(*) FROM information_schema.usage_privileges WHERE object_schema = current_schema AND object_name = 'bugseer_postgres_00004_seq_acl' AND object_type = 'SEQUENCE' AND grantee = current_user AND privilege_type = 'USAGE'; GRANT USAGE ON SEQUENCE bugseer_postgres_00004_seq_acl TO CURRENT_USER; SELECT 'explicit_view_rows=' || count(*) FROM information_schema.usage_privileges WHERE object_schema = current_schema AND object_name = 'bugseer_postgres_00004_seq_acl' AND object_type = 'SEQUENCE' AND grantee = current_user AND privilege_type = 'USAGE'; Actual result ------------- implicit_privilege=true implicit_view_rows=0 GRANT explicit_view_rows=1 Expected result --------------- information_schema.usage_privileges should report one owner USAGE row before the redundant explicit grant, and the row count should remain one afterward. Additional information ---------------------- The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and PostgreSQL 17.11.
Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
PG Bug reporting form writes:
> For a newly created sequence, has_sequence_privilege reports that the owner
> has USAGE, but information_schema.usage_privileges omits that privilege. The
> view reports the owner privilege only after the same USAGE privilege is
> redundantly granted explicitly.
I think the problem is that the "sequences" arm of usage_privileges
writes
SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
but the acldefault code for sequences is 's' not 'r', so the wrong
set of default ACL bits is injected. We would see a bunch of
obviously-inapplicable privileges reported, except that the query
then applies a filter:
AND c.prtype IN ('USAGE')
and we end up reporting nothing.
This appears to go clear back to 82e83f46a.
regards, tom lane
Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
Hi,
> For a newly created sequence, has_sequence_privilege reports that the owner
> has USAGE, but information_schema.usage_privileges omits that privilege. I reproduced this locally. The impact looks low to me. It is a view
error, not a permission error, so users see the wrong result in
information_schema but they can still use the sequence.
error, not a permission error, so users see the wrong result in
information_schema but they can still use the sequence.
Patch attached for the acldefault('r') to acldefault('s') fix Tom
described.
0002 adds a regression test next to the has_sequence_privilege tests.
It checks a sequence with no explicit grants, which is the case that
goes through acldefault, and the same sequence after a grant, which
goes through relacl. With 0001 reverted the first query returns zero
rows.
described.
0002 adds a regression test next to the has_sequence_privilege tests.
It checks a sequence with no explicit grants, which is the case that
goes through acldefault, and the same sequence after a grant, which
goes through relacl. With 0001 reverted the first query returns zero
rows.
This may needs a catversion bump and backport to 14.
The older version requires manual drop and recreate the view.
Thanks,
Shihao
Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
shihao zhong writes: > This may needs a catversion bump and backport to 14. > The older version requires manual drop and recreate the view. In principle we don't need a catversion bump here, since there's no C-code-versus-catalogs compatibility issue. But we have usually done one for information_schema changes. I think I'd vote against back-patching. The fact that this went unnoticed for 14 years demonstrates what low impact it has. I don't foresee people wanting to manually replace that view in order to fix it in existing installations. regards, tom lane
Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
От:
shihao zhong <zhong950419@gmail.com>
Дата:
> I think I'd vote against back-patching. The fact that this went
> unnoticed for 14 years demonstrates what low impact it has.
Agreed, master only then.
> I don't foresee people wanting to manually replace that view in
> order to fix it in existing installations.
Right, and most users would not even know the view is wrong.
> unnoticed for 14 years demonstrates what low impact it has.
Agreed, master only then.
> I don't foresee people wanting to manually replace that view in
> order to fix it in existing installations.
Right, and most users would not even know the view is wrong.
Thanks,
Shihao
Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
От:
Joao Rito <joaovictor.rito@gmail.com>
Дата:
Hi Shihao, I tested v1 0001 and 0002 on current master (81b2179b492, built with cassert and tap tests). Both apply cleanly. On master, for a sequence with no explicit grants, has_sequence_privilege says the owner has USAGE but the view has no row for it. The same happens for the new owner after ALTER SEQUENCE ... OWNER TO. Sequences with an explicit GRANT already showed the owner row. A table owner shows up in table_privileges without any grant, so sequences were the odd one out. The reproducer from the report gives implicit_view_rows=0 on master. With the patches, the owner row appears for a sequence with no grants (is_grantable YES), for one with an explicit GRANT, for one with GRANT ... WITH GRANT OPTION, and for the new owner after ALTER OWNER TO. A role with no privileges sees no rows, and a sequence where the owner revoked its own USAGE has no owner row. For three roles and five sequences the view and has_sequence_privilege agree (0 mismatches, 2 before the patch). The reproducer now gives implicit_view_rows=1 and explicit_view_rows=1. make check passes (243 tests). With 0001 reverted and 0002 kept, privileges fails because the first new query returns 0 rows instead of 1, so the test does catch the bug. What I did not test: the isolation and tap suites, pgindent, the docs, other platforms (I ran one Linux build), upgrades from an existing cluster, and the back branches. I agree with Tom's vote against back-patching, so I checked only master. Small suggestions: the commit message of 0001 could mention that catversion is bumped or not, since Tom noted that information_schema changes have usually come with one, and the patch does not include one. The test could also cover a sequence whose owner is not the current user, but I have no objection to the change as it is. Regards, Joao Rito
Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Joao,
Thanks for reviewing my patch.
> Small suggestions: the commit message of 0001 could mention that catversion is bumped or not
I think the community standard is commiter bump the catversion, so no
need to write that into the commit message.
Thanks,
Shihao