v35-0004-Review-comment-fixes-for-transfer-ownership-patc.patch

From 1573d1ab377d6308acd45556fb3e191d0f346999 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Mon, 18 May 2026 10:48:35 +0000
Subject: [PATCH v35 4/9] Review comment fixes for transfer ownership patch

Review comment fixes for transfer ownership patch
---
 src/bin/initdb/initdb.c                    |  5 ++++
 src/test/regress/expected/subscription.out | 35 ++++++++++++++++++++++
 src/test/regress/sql/subscription.sql      | 31 +++++++++++++++++++
 3 files changed, 71 insertions(+)

diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index cda05676a79..803ca4112d4 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1839,6 +1839,11 @@ setup_privileges(FILE *cmdfd)
 				  "  AND relacl IS NULL;\n\n",
 				  escape_quotes(username));
 	PG_CMD_PUTS("GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n");
+
+	/*
+	 * Allow non-superuser subscription owners to access their associated
+	 * conflict log tables in the pg_conflict schema.
+	 */
 	PG_CMD_PUTS("GRANT USAGE ON SCHEMA pg_conflict TO PUBLIC;\n\n");
 	PG_CMD_PUTS("REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n");
 	PG_CMD_PUTS("INSERT INTO pg_init_privs "
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 86debcea23d..6eadfa59253 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -653,6 +653,41 @@ WHERE s.subname = 'regress_conflict_test1' AND a.attnum > 0
      11 | local_conflicts
 (11 rows)
 
+-- Changing the subscription owner should also update the owner
+-- of the associated conflict log table.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user2;
+SELECT pg_catalog.pg_get_userbyid(c.relowner) AS owner
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_subscription s
+        ON c.relname = 'pg_conflict_log_for_subid_' || s.oid
+WHERE s.subname = 'regress_conflict_test1';
+           owner            
+----------------------------
+ regress_subscription_user2
+(1 row)
+
+-- Verify that a non-superuser subscription owner can truncate,
+-- delete from, and select from the associated conflict log table.
+SET ROLE 'regress_subscription_user2';
+SELECT format('%I.%I', n.nspname, c.relname) AS conflict_log_table
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_namespace n
+	ON n.oid = c.relnamespace
+JOIN pg_catalog.pg_subscription s
+	ON c.relname = 'pg_conflict_log_for_subid_' || s.oid
+WHERE s.subname = 'regress_conflict_test1'
+\gset
+TRUNCATE TABLE :conflict_log_table;
+DELETE FROM :conflict_log_table;
+SELECT COUNT(*) FROM :conflict_log_table;
+ count 
+-------
+     0
+(1 row)
+
+RESET ROLE;
+-- Restore the original subscription owner.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user;
 --
 -- ALTER SUBSCRIPTION - conflict_log_destination state transitions
 --
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ecba5557030..1cf38c31988 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -475,6 +475,37 @@ JOIN pg_subscription s ON c.relname = 'pg_conflict_log_for_subid_' || s.oid
 WHERE s.subname = 'regress_conflict_test1' AND a.attnum > 0
     ORDER BY a.attnum;
 
+-- Changing the subscription owner should also update the owner
+-- of the associated conflict log table.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user2;
+SELECT pg_catalog.pg_get_userbyid(c.relowner) AS owner
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_subscription s
+        ON c.relname = 'pg_conflict_log_for_subid_' || s.oid
+WHERE s.subname = 'regress_conflict_test1';
+
+-- Verify that a non-superuser subscription owner can truncate,
+-- delete from, and select from the associated conflict log table.
+SET ROLE 'regress_subscription_user2';
+
+SELECT format('%I.%I', n.nspname, c.relname) AS conflict_log_table
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_namespace n
+	ON n.oid = c.relnamespace
+JOIN pg_catalog.pg_subscription s
+	ON c.relname = 'pg_conflict_log_for_subid_' || s.oid
+WHERE s.subname = 'regress_conflict_test1'
+\gset
+
+TRUNCATE TABLE :conflict_log_table;
+DELETE FROM :conflict_log_table;
+SELECT COUNT(*) FROM :conflict_log_table;
+
+RESET ROLE;
+
+-- Restore the original subscription owner.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user;
+
 --
 -- ALTER SUBSCRIPTION - conflict_log_destination state transitions
 --
-- 
2.53.0