BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253863psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 20, 2026 at 06:44 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253863_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253863_3 && git checkout t253863_3Patchset v3 (message #3) is on t253863_3
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.
PG Bug reporting form <noreply@postgresql.org> 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
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.
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.
This may needs a catversion bump and backport to 14.
The older version requires manual drop and recreate the view.
Thanks,
Shihao
Attachments:
t253863_3v1-0002-Add-tests-for-sequence-USAGE-privileges-in-inform.patchapplication/octet-stream; name=v1-0002-Add-tests-for-sequence-USAGE-privileges-in-inform.patchDownload+33-1
v1-0001-Fix-information_schema.usage_privileges-for-seque.patchapplication/octet-stream; name=v1-0001-Fix-information_schema.usage_privileges-for-seque.patchDownload+1-2
shihao zhong <zhong950419@gmail.com> 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
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.
Thanks,
Shihao