agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
5+ messages / 3 participants
[nested] [flat]
* BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
@ 2026-09-19 13:44 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2026-09-19 13:44 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
@ 2026-09-20 15:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-09-20 15:49 UTC (permalink / raw)
To: imchifan@163.com; +Cc: Peter Eisentraut <peter@eisentraut.org>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
@ 2026-09-20 18:33 shihao zhong <zhong950419@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: shihao zhong @ 2026-09-20 18:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: imchifan@163.com, Peter Eisentraut <peter@eisentraut.org>; pgsql-bugs@lists.postgresql.org
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:
[application/octet-stream] v1-0002-Add-tests-for-sequence-USAGE-privileges-in-inform.patch (3.1K, ../../CAGRkXqTssJ5ttoY_BATULMChBHQAAyO3sPNBMXGnCOws3ANRQQ@mail.gmail.com/3-v1-0002-Add-tests-for-sequence-USAGE-privileges-in-inform.patch)
download | inline diff:
From 4a28724b37fde5a40700190dc83b34e56a6f3751 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Sun, 20 Sep 2026 14:20:23 -0400
Subject: [PATCH v1 2/2] Add tests for sequence USAGE privileges in
information_schema
Covers both sides, a sequence with no explicit grants, where the
owner row comes from acldefault, and one with a grant, where it
comes from relacl.
---
src/test/regress/expected/privileges.out | 21 +++++++++++++++++++++
src/test/regress/sql/privileges.sql | 12 ++++++++++++
2 files changed, 33 insertions(+)
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 9b7153c9cd5..703bb5126aa 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2067,7 +2067,28 @@ REVOKE regress_priv_group2 FROM regress_priv_user5;
-- has_sequence_privilege tests
\c -
CREATE SEQUENCE x_seq;
+-- information_schema.usage_privileges must show the owner's implicit USAGE
+-- privilege on a sequence, even when nothing has been granted explicitly
+SELECT grantee = current_user AS grantee_is_owner, privilege_type, is_grantable
+ FROM information_schema.usage_privileges
+ WHERE object_name = 'x_seq' AND object_type = 'SEQUENCE'
+ ORDER BY 1;
+ grantee_is_owner | privilege_type | is_grantable
+------------------+----------------+--------------
+ t | USAGE | YES
+(1 row)
+
GRANT USAGE on x_seq to regress_priv_user2;
+SELECT grantee = current_user AS grantee_is_owner, privilege_type, is_grantable
+ FROM information_schema.usage_privileges
+ WHERE object_name = 'x_seq' AND object_type = 'SEQUENCE'
+ ORDER BY 1;
+ grantee_is_owner | privilege_type | is_grantable
+------------------+----------------+--------------
+ f | USAGE | NO
+ t | USAGE | YES
+(2 rows)
+
SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT');
ERROR: "atest1" is not a sequence
SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT');
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 0113dfa5dab..bb9e6e636b5 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1370,8 +1370,20 @@ REVOKE regress_priv_group2 FROM regress_priv_user5;
CREATE SEQUENCE x_seq;
+-- information_schema.usage_privileges must show the owner's implicit USAGE
+-- privilege on a sequence, even when nothing has been granted explicitly
+SELECT grantee = current_user AS grantee_is_owner, privilege_type, is_grantable
+ FROM information_schema.usage_privileges
+ WHERE object_name = 'x_seq' AND object_type = 'SEQUENCE'
+ ORDER BY 1;
+
GRANT USAGE on x_seq to regress_priv_user2;
+SELECT grantee = current_user AS grantee_is_owner, privilege_type, is_grantable
+ FROM information_schema.usage_privileges
+ WHERE object_name = 'x_seq' AND object_type = 'SEQUENCE'
+ ORDER BY 1;
+
SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT');
SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT');
SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT');
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v1-0001-Fix-information_schema.usage_privileges-for-seque.patch (1.5K, ../../CAGRkXqTssJ5ttoY_BATULMChBHQAAyO3sPNBMXGnCOws3ANRQQ@mail.gmail.com/4-v1-0001-Fix-information_schema.usage_privileges-for-seque.patch)
download | inline diff:
From 1187b09ddee61ca682faef080c55a81b3bb1d7cc Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Sun, 20 Sep 2026 14:20:23 -0400
Subject: [PATCH v1 1/2] Fix information_schema.usage_privileges for sequence
owners
The sequences arm of the view builds the default ACL with
acldefault('r', relowner), but sequences use 's'. A relation
default ACL has no USAGE bit, so the prtype filter dropped every
row and the owner's implicit USAGE privilege never showed up. It
only appeared once someone granted USAGE explicitly, which made
relacl non null and took acldefault out of the picture.
---
src/backend/catalog/information_schema.sql | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 49adf66ba9b..ec85ec1e7c8 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -2386,7 +2386,7 @@ CREATE VIEW usage_privileges AS
THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
FROM (
- SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+ SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('s', relowner)))).* FROM pg_class
) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
pg_namespace n,
pg_authid u_grantor,
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
@ 2026-09-20 19:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2026-09-20 19:18 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; +Cc: imchifan@163.com, Peter Eisentraut <peter@eisentraut.org>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege
@ 2026-09-20 19:29 shihao zhong <zhong950419@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: shihao zhong @ 2026-09-20 19:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: imchifan@163.com, Peter Eisentraut <peter@eisentraut.org>; pgsql-bugs@lists.postgresql.org
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-09-20 19:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-19 13:44 BUG #19703: information_schema.usage_privileges omits a sequence owner's implicit USAGE privilege PG Bug reporting form <noreply@postgresql.org>
2026-09-20 15:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-20 18:33 ` shihao zhong <zhong950419@gmail.com>
2026-09-20 19:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-09-20 19:29 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox