On 2026-Apr-20, Antonin Houska wrote: > Antonin Houska <[email protected]> wrote: > > > It was discussed earlier [1] and the concerns about possibly excessive > > resource consumptions were addressed by [2]. So I think it the fix was just > > forgotten. Attached here. > > Sorry, I attached wrong patch. This is what I meant.
Yeah, I had also written the same patch a couple of days ago. BTW I ran into a small problem after adding some tests in cluster.sql that would exercise this -- that test would die more or less randomly but frequently in CI (which it never did in my laptop) because of the size of the snapshot, ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key; REPACK (CONCURRENTLY) ptnowner1; +ERROR: initial slot snapshot too large +CONTEXT: REPACK decoding worker RESET SESSION AUTHORIZATION; I think the solution for this is to move cluster to a separate parallel test. The one where it is now is a bit too crowded. Maybe the one for compression is okay? I'll test and push if I see it passing CI. Another obvious thing after adding tests is that the LOGIN privilege is required, which is also quite bogus IMO. 0002 here should solve that. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "If you want to have good ideas, you must have many ideas. Most of them will be wrong, and what you have to learn is which ones to throw away." (Linus Pauling)
>From b3d4158356f4914d2b0cba86eef6994c0ee50ab9 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]> Date: Mon, 20 Apr 2026 11:38:48 +0200 Subject: [PATCH 1/2] REPACK: do not require the user to have REPLICATION Although REPACK (CONCURRENTLY) uses replication slots, there is no concern that the slot will leak data of other users, because the MAINTAIN privilege on the table is required anyway; requiring REPLICATION is user-unfriendly without providing any actual protection. A related aspect is that the REPLICATION attribute is not needed to prevent REPACK from stealing slots from logical replication, since commit e76d8c749c31 made REPACK use a separate pool of replication slots. Because there are now successful concurrent repack runs in the regression tests, we're forced to run test_plan_advice under wal_level=replica. Author: Antonin Houska <[email protected]> Reported-by: Justin Pryzby <[email protected]> Reviewed-by: Chao Li <[email protected]> Discussion: https://postgr.es/m/aeJHPNmL4vVy3oPw@pryzbyj2023 --- src/backend/commands/repack_worker.c | 1 - .../test_plan_advice/t/001_replan_regress.pl | 1 + src/test/regress/expected/cluster.out | 20 +++++++++++++++++-- src/test/regress/sql/cluster.sql | 11 ++++++++-- 4 files changed, 28 insertions(+), 5 deletions(-) diff --git a/src/backend/commands/repack_worker.c b/src/backend/commands/repack_worker.c index b17edd771e2..e4a4860805b 100644 --- a/src/backend/commands/repack_worker.c +++ b/src/backend/commands/repack_worker.c @@ -214,7 +214,6 @@ repack_setup_logical_decoding(Oid relid) /* * Make sure we can use logical decoding. */ - CheckSlotPermissions(); CheckLogicalDecodingRequirements(true); /* diff --git a/src/test/modules/test_plan_advice/t/001_replan_regress.pl b/src/test/modules/test_plan_advice/t/001_replan_regress.pl index 38ffa4d11ae..452b179a665 100644 --- a/src/test/modules/test_plan_advice/t/001_replan_regress.pl +++ b/src/test/modules/test_plan_advice/t/001_replan_regress.pl @@ -18,6 +18,7 @@ $node->init(); # Set up our desired configuration. $node->append_conf('postgresql.conf', <<EOM); shared_preload_libraries='test_plan_advice' +wal_level=replica pg_plan_advice.always_explain_supplied_advice=false pg_plan_advice.feedback_warnings=true EOM diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 6127b215a86..e17bc91fae1 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -543,15 +543,17 @@ ERROR: REPACK (CONCURRENTLY) is not supported for partitioned tables HINT: Consider running the command on individual partitions. DROP TABLE clstrpart; -- Ownership of partitions is checked -CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i); +CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i); CREATE INDEX ptnowner_i_idx ON ptnowner(i); CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1); -CREATE ROLE regress_ptnowner; +CREATE ROLE regress_ptnowner LOGIN; CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2); ALTER TABLE ptnowner1 OWNER TO regress_ptnowner; SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; ERROR: permission denied for table ptnowner +ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key; +REPACK (CONCURRENTLY) ptnowner1; RESET SESSION AUTHORIZATION; ALTER TABLE ptnowner OWNER TO regress_ptnowner; CREATE TEMP TABLE ptnowner_oldnodes AS @@ -560,6 +562,11 @@ CREATE TEMP TABLE ptnowner_oldnodes AS SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; WARNING: permission denied to execute CLUSTER on "ptnowner2", skipping it +-- still can't repack without a replica identity +ALTER TABLE ptnowner1 REPLICA IDENTITY DEFAULT; +REPACK (CONCURRENTLY) ptnowner1; +ERROR: cannot process relation "ptnowner1" +HINT: Relation "ptnowner1" has no identity index. RESET SESSION AUTHORIZATION; SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; @@ -570,6 +577,15 @@ SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a ptnowner2 | t (3 rows) +SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a + JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; + relname | ?column? +-----------+---------- + ptnowner | t + ptnowner1 | f + ptnowner2 | t +(3 rows) + DROP TABLE ptnowner; DROP ROLE regress_ptnowner; -- Test CLUSTER with external tuplesorting diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql index d14063a9683..1f471a8821a 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -254,14 +254,16 @@ REPACK (CONCURRENTLY) clstrpart; DROP TABLE clstrpart; -- Ownership of partitions is checked -CREATE TABLE ptnowner(i int unique) PARTITION BY LIST (i); +CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i); CREATE INDEX ptnowner_i_idx ON ptnowner(i); CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1); -CREATE ROLE regress_ptnowner; +CREATE ROLE regress_ptnowner LOGIN; CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2); ALTER TABLE ptnowner1 OWNER TO regress_ptnowner; SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; +ALTER TABLE ptnowner1 REPLICA IDENTITY USING INDEX ptnowner1_i_key; +REPACK (CONCURRENTLY) ptnowner1; RESET SESSION AUTHORIZATION; ALTER TABLE ptnowner OWNER TO regress_ptnowner; CREATE TEMP TABLE ptnowner_oldnodes AS @@ -269,7 +271,12 @@ CREATE TEMP TABLE ptnowner_oldnodes AS JOIN pg_class AS c ON c.oid=tree.relid; SET SESSION AUTHORIZATION regress_ptnowner; CLUSTER ptnowner USING ptnowner_i_idx; +-- still can't repack without a replica identity +ALTER TABLE ptnowner1 REPLICA IDENTITY DEFAULT; +REPACK (CONCURRENTLY) ptnowner1; RESET SESSION AUTHORIZATION; +SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a + JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; SELECT a.relname, a.relfilenode=b.relfilenode FROM pg_class a JOIN ptnowner_oldnodes b USING (oid) ORDER BY a.relname COLLATE "C"; DROP TABLE ptnowner; -- 2.47.3
>From 99259ca5410389867228d15bd37ab88e6c8844f2 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]> Date: Mon, 20 Apr 2026 13:19:49 +0200 Subject: [PATCH 2/2] REPACK: do not require LOGIN privileges Normally, starting a background worker does require LOGIN, which is fine. However, the bgworker used for REPACK has no business requiring it. It's just user-unfriendly and prevents repacking tables comfortably. --- src/backend/commands/repack_worker.c | 5 +++-- src/test/regress/expected/cluster.out | 2 +- src/test/regress/sql/cluster.sql | 2 +- 3 files changed, 5 insertions(+), 4 deletions(-) diff --git a/src/backend/commands/repack_worker.c b/src/backend/commands/repack_worker.c index e4a4860805b..c40f8c98e06 100644 --- a/src/backend/commands/repack_worker.c +++ b/src/backend/commands/repack_worker.c @@ -106,8 +106,9 @@ RepackWorkerMain(Datum main_arg) pq_set_parallel_leader(shared->backend_pid, shared->backend_proc_number); - /* Connect to the database. */ - BackgroundWorkerInitializeConnectionByOid(shared->dbid, shared->roleid, 0); + /* Connect to the database. LOGIN is not required. */ + BackgroundWorkerInitializeConnectionByOid(shared->dbid, shared->roleid, + BGWORKER_BYPASS_ROLELOGINCHECK); /* * Transaction is needed to open relation, and it also provides us with a diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index e17bc91fae1..71270134985 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -546,7 +546,7 @@ DROP TABLE clstrpart; CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i); CREATE INDEX ptnowner_i_idx ON ptnowner(i); CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1); -CREATE ROLE regress_ptnowner LOGIN; +CREATE ROLE regress_ptnowner; CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2); ALTER TABLE ptnowner1 OWNER TO regress_ptnowner; SET SESSION AUTHORIZATION regress_ptnowner; diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql index 1f471a8821a..6746236ffec 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -257,7 +257,7 @@ DROP TABLE clstrpart; CREATE TABLE ptnowner(i int unique not null) PARTITION BY LIST (i); CREATE INDEX ptnowner_i_idx ON ptnowner(i); CREATE TABLE ptnowner1 PARTITION OF ptnowner FOR VALUES IN (1); -CREATE ROLE regress_ptnowner LOGIN; +CREATE ROLE regress_ptnowner; CREATE TABLE ptnowner2 PARTITION OF ptnowner FOR VALUES IN (2); ALTER TABLE ptnowner1 OWNER TO regress_ptnowner; SET SESSION AUTHORIZATION regress_ptnowner; -- 2.47.3
