public inbox for [email protected]help / color / mirror / Atom feed
Fix column privileges for pg_subscription.subwalrcvtimeout 7+ messages / 3 participants [nested] [flat]
* Fix column privileges for pg_subscription.subwalrcvtimeout @ 2026-06-01 13:44 Nisha Moond <[email protected]> 2026-06-02 02:46 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Nisha Moond @ 2026-06-01 13:44 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]>; Fujii Masao <[email protected]> Hi Hackers, IIUC, all columns of pg_subscription, except subconninfo, are intended to be readable by non-superusers as well. A comment in system_views.sql also states: "-- All columns of pg_subscription except subconninfo are publicly readable." However, 'subwalrcvtimeout' is currently not accessible: Test: postgres=# CREATE ROLE nisha LOGIN PASSWORD 'testpass'; CREATE ROLE postgres=# SET SESSION AUTHORIZATION nisha; SET postgres=> select subwalrcvtimeout from pg_subscription; ERROR: permission denied for table pg_subscription It appears the column-level privileges for pg_subscription were not updated when subwalrcvtimeout was added. Attached is a small fix patch to grant public access to this column, consistent with the existing behavior of the other pg_subscription columns. CC: Fujii-san (subwalrcvtimeout was introduced by commit fb80f38). -- Thanks, Nisha Attachments: [application/octet-stream] v1-0001-Fix-pg_subscription-column-privileges-for-subwalr.patch (1.4K, 2-v1-0001-Fix-pg_subscription-column-privileges-for-subwalr.patch) download | inline diff: From c0329c344d4be52f0c931fd13ac3e1a02c13cd7a Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Mon, 1 Jun 2026 18:43:42 +0530 Subject: [PATCH v1] Fix pg_subscription column privileges for subwalrcvtimeout The subwalrcvtimeout column was added by commit fb80f38, but the column-level privileges on pg_subscription were not updated. As a result, non-superusers cannot read the column, unlike the other publicly readable pg_subscription columns. This patch grant SELECT privilege on subwalrcvtimeout to PUBLIC. --- src/backend/catalog/system_views.sql | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 73a1c1c4670..8f129baec90 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1527,7 +1527,8 @@ GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled, subbinary, substream, subtwophasestate, subdisableonerr, subpasswordrequired, subrunasowner, subfailover, subretaindeadtuples, submaxretention, subretentionactive, - subserver, subslotname, subsynccommit, subpublications, suborigin) + subserver, subslotname, subsynccommit, subwalrcvtimeout, + subpublications, suborigin) ON pg_subscription TO public; CREATE VIEW pg_stat_subscription_stats AS -- 2.50.1 (Apple Git-155) ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fix column privileges for pg_subscription.subwalrcvtimeout 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> @ 2026-06-02 02:46 ` Fujii Masao <[email protected]> 2026-06-02 02:56 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Amit Kapila <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Fujii Masao @ 2026-06-02 02:46 UTC (permalink / raw) To: Nisha Moond <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, Jun 1, 2026 at 10:44 PM Nisha Moond <[email protected]> wrote: > > Hi Hackers, > > IIUC, all columns of pg_subscription, except subconninfo, are intended > to be readable by non-superusers as well. A comment in > system_views.sql also states: > "-- All columns of pg_subscription except subconninfo are publicly readable." > > However, 'subwalrcvtimeout' is currently not accessible: > Test: > postgres=# CREATE ROLE nisha LOGIN PASSWORD 'testpass'; > CREATE ROLE > postgres=# SET SESSION AUTHORIZATION nisha; > SET > postgres=> select subwalrcvtimeout from pg_subscription; > ERROR: permission denied for table pg_subscription > > It appears the column-level privileges for pg_subscription were not > updated when subwalrcvtimeout was added. > > Attached is a small fix patch to grant public access to this column, > consistent with the existing behavior of the other pg_subscription > columns. Thanks for the report and the patch! It looks good to me. Barring any objections, I'll commit it. For my own reference, since this changes the catalog, I'll need to update the catalog version when committing. BTW, should we add a regression test for column privileges on pg_subscription to help catch similar issues in the future? For example, the test could verify that subconninfo remains unreadable to non-superusers, while all other existing columns remain publicly readable. That would make it easier to detect omissions when new columns are added to pg_subscription. For example, SELECT count(*) = 0 AS ok FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_subscription'::regclass AND attnum > 0 AND NOT attisdropped AND ((attname = 'subconninfo' AND has_column_privilege('regress_subscription_user_dummy', 'pg_catalog.pg_subscription', attname, 'SELECT')) OR (attname <> 'subconninfo' AND NOT has_column_privilege('regress_subscription_user_dummy', 'pg_catalog.pg_subscription', attname, 'SELECT'))); Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fix column privileges for pg_subscription.subwalrcvtimeout 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-02 02:46 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> @ 2026-06-02 02:56 ` Amit Kapila <[email protected]> 2026-06-04 02:05 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Amit Kapila @ 2026-06-02 02:56 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Nisha Moond <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, Jun 2, 2026 at 8:16 AM Fujii Masao <[email protected]> wrote: > > On Mon, Jun 1, 2026 at 10:44 PM Nisha Moond <[email protected]> wrote: > > > > Hi Hackers, > > > > IIUC, all columns of pg_subscription, except subconninfo, are intended > > to be readable by non-superusers as well. A comment in > > system_views.sql also states: > > "-- All columns of pg_subscription except subconninfo are publicly readable." > > > > However, 'subwalrcvtimeout' is currently not accessible: > > Test: > > postgres=# CREATE ROLE nisha LOGIN PASSWORD 'testpass'; > > CREATE ROLE > > postgres=# SET SESSION AUTHORIZATION nisha; > > SET > > postgres=> select subwalrcvtimeout from pg_subscription; > > ERROR: permission denied for table pg_subscription > > > > It appears the column-level privileges for pg_subscription were not > > updated when subwalrcvtimeout was added. > > > > Attached is a small fix patch to grant public access to this column, > > consistent with the existing behavior of the other pg_subscription > > columns. > > Thanks for the report and the patch! It looks good to me. > > Barring any objections, I'll commit it. For my own reference, since this > changes the catalog, I'll need to update the catalog version when committing. > > BTW, should we add a regression test for column privileges on pg_subscription > to help catch similar issues in the future? > +1. It makes sense because I noticed that patch authors previously also omitted this part though in most cases those are caught in review. -- With Regards, Amit Kapila. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fix column privileges for pg_subscription.subwalrcvtimeout 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-02 02:46 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 2026-06-02 02:56 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Amit Kapila <[email protected]> @ 2026-06-04 02:05 ` Fujii Masao <[email protected]> 2026-06-04 05:39 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Fujii Masao @ 2026-06-04 02:05 UTC (permalink / raw) To: Amit Kapila <[email protected]>; +Cc: Nisha Moond <[email protected]>; PostgreSQL Hackers <[email protected]> On Tue, Jun 2, 2026 at 11:56 AM Amit Kapila <[email protected]> wrote: > > BTW, should we add a regression test for column privileges on pg_subscription > > to help catch similar issues in the future? > > > > +1. It makes sense because I noticed that patch authors previously > also omitted this part though in most cases those are caught in > review. Agreed. I've added the regression test to the patch. Attached is the updated version. Regards, -- Fujii Masao Attachments: [application/octet-stream] v2-0001-Fix-pg_subscription-column-privileges-for-subwalr.patch (4.2K, 2-v2-0001-Fix-pg_subscription-column-privileges-for-subwalr.patch) download | inline diff: From e9167c9027017a053311a66df395b914e2e2b11c Mon Sep 17 00:00:00 2001 From: Nisha Moond <[email protected]> Date: Mon, 1 Jun 2026 18:43:42 +0530 Subject: [PATCH v2] Fix pg_subscription column privileges for subwalrcvtimeout The subwalrcvtimeout column was added by commit fb80f38, but the column-level privileges on pg_subscription were not updated. As a result, non-superusers cannot read the column, unlike the other publicly readable pg_subscription columns. This commit grants SELECT privilege on subwalrcvtimeout to PUBLIC. Author: Nisha Moond <[email protected]> Reviewed-by: Amit Kapila <[email protected]> Reviewed-by: Fujii Masao <[email protected]> Discussion: https://postgr.es/m/CABdArM4uA=6nA0BunJwudiEoY1BcWUS_oj_2pkEq_d-YdiBJhw@mail.gmail.com --- src/backend/catalog/system_views.sql | 3 ++- src/test/regress/expected/subscription.out | 15 +++++++++++++++ src/test/regress/sql/subscription.sql | 11 +++++++++++ 3 files changed, 28 insertions(+), 1 deletion(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 73a1c1c4670..8f129baec90 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -1527,7 +1527,8 @@ GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled, subbinary, substream, subtwophasestate, subdisableonerr, subpasswordrequired, subrunasowner, subfailover, subretaindeadtuples, submaxretention, subretentionactive, - subserver, subslotname, subsynccommit, subpublications, suborigin) + subserver, subslotname, subsynccommit, subwalrcvtimeout, + subpublications, suborigin) ON pg_subscription TO public; CREATE VIEW pg_stat_subscription_stats AS diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out index 7e3cabdb93f..8481056a702 100644 --- a/src/test/regress/expected/subscription.out +++ b/src/test/regress/expected/subscription.out @@ -47,6 +47,21 @@ SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s; test subscription (1 row) +-- Check that only subconninfo is not publicly readable in pg_subscription. +SELECT count(*) = 0 AS ok + FROM pg_attribute + WHERE attrelid = 'pg_catalog.pg_subscription'::regclass AND attnum > 0 AND NOT attisdropped + AND ((attname = 'subconninfo' + AND has_column_privilege('regress_subscription_user_dummy', + 'pg_catalog.pg_subscription', attname, 'SELECT')) + OR (attname <> 'subconninfo' + AND NOT has_column_privilege('regress_subscription_user_dummy', + 'pg_catalog.pg_subscription', attname, 'SELECT'))); + ok +---- + t +(1 row) + -- Check if the subscription stats are created and stats_reset is updated -- by pg_stat_reset_subscription_stats(). SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub'; diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql index 6c3d9632e8a..374fad6aa7b 100644 --- a/src/test/regress/sql/subscription.sql +++ b/src/test/regress/sql/subscription.sql @@ -42,6 +42,17 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB COMMENT ON SUBSCRIPTION regress_testsub IS 'test subscription'; SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s; +-- Check that only subconninfo is not publicly readable in pg_subscription. +SELECT count(*) = 0 AS ok + FROM pg_attribute + WHERE attrelid = 'pg_catalog.pg_subscription'::regclass AND attnum > 0 AND NOT attisdropped + AND ((attname = 'subconninfo' + AND has_column_privilege('regress_subscription_user_dummy', + 'pg_catalog.pg_subscription', attname, 'SELECT')) + OR (attname <> 'subconninfo' + AND NOT has_column_privilege('regress_subscription_user_dummy', + 'pg_catalog.pg_subscription', attname, 'SELECT'))); + -- Check if the subscription stats are created and stats_reset is updated -- by pg_stat_reset_subscription_stats(). SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub'; -- 2.53.0 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fix column privileges for pg_subscription.subwalrcvtimeout 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-02 02:46 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 2026-06-02 02:56 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Amit Kapila <[email protected]> 2026-06-04 02:05 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> @ 2026-06-04 05:39 ` Nisha Moond <[email protected]> 2026-06-05 00:53 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Nisha Moond @ 2026-06-04 05:39 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Jun 4, 2026 at 7:35 AM Fujii Masao <[email protected]> wrote: > > On Tue, Jun 2, 2026 at 11:56 AM Amit Kapila <[email protected]> wrote: > > > BTW, should we add a regression test for column privileges on pg_subscription > > > to help catch similar issues in the future? > > > > > > > +1. It makes sense because I noticed that patch authors previously > > also omitted this part though in most cases those are caught in > > review. > > Agreed. I've added the regression test to the patch. > Attached is the updated version. > Thank you, Fujii-san, for the updated patch. I tested both the success and failure cases with the SQL query, and it worked as expected. The patch LGTM. -- Thanks, Nisha ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fix column privileges for pg_subscription.subwalrcvtimeout 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-02 02:46 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 2026-06-02 02:56 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Amit Kapila <[email protected]> 2026-06-04 02:05 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 2026-06-04 05:39 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> @ 2026-06-05 00:53 ` Fujii Masao <[email protected]> 2026-06-05 05:42 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Fujii Masao @ 2026-06-05 00:53 UTC (permalink / raw) To: Nisha Moond <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Jun 4, 2026 at 2:39 PM Nisha Moond <[email protected]> wrote: > Thank you, Fujii-san, for the updated patch. I tested both the success > and failure cases with the SQL query, and it worked as expected. > > The patch LGTM. Thanks for the test and review! I've pushed the patch. Regards, -- Fujii Masao ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Fix column privileges for pg_subscription.subwalrcvtimeout 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-02 02:46 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 2026-06-02 02:56 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Amit Kapila <[email protected]> 2026-06-04 02:05 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> 2026-06-04 05:39 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-05 00:53 ` Re: Fix column privileges for pg_subscription.subwalrcvtimeout Fujii Masao <[email protected]> @ 2026-06-05 05:42 ` Nisha Moond <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Nisha Moond @ 2026-06-05 05:42 UTC (permalink / raw) To: Fujii Masao <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]> On Fri, Jun 5, 2026 at 6:24 AM Fujii Masao <[email protected]> wrote: > > On Thu, Jun 4, 2026 at 2:39 PM Nisha Moond <[email protected]> wrote: > > Thank you, Fujii-san, for the updated patch. I tested both the success > > and failure cases with the SQL query, and it worked as expected. > > > > The patch LGTM. > > Thanks for the test and review! I've pushed the patch. > Thanks for pushing! -- Nisha ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-06-05 05:42 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-06-01 13:44 Fix column privileges for pg_subscription.subwalrcvtimeout Nisha Moond <[email protected]> 2026-06-02 02:46 ` Fujii Masao <[email protected]> 2026-06-02 02:56 ` Amit Kapila <[email protected]> 2026-06-04 02:05 ` Fujii Masao <[email protected]> 2026-06-04 05:39 ` Nisha Moond <[email protected]> 2026-06-05 00:53 ` Fujii Masao <[email protected]> 2026-06-05 05:42 ` Nisha Moond <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox