From: CyberDem0n (@CyberDem0n) <noreply+CyberDem0n@github.com>
To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>
Subject: [reorg/pg_repack] PR #487: Remove SECURITY DEFINER from repack_trigger()
Date: Fri, 23 Oct 2026 10:42:38 +0000
Message-ID: <gh-reorg-pg_repack-487@github.com> (raw)
The repack_trigger() function was marked SECURITY DEFINER because it needs permission to INSERT INTO the `repack.log_%u` table, which the invoking user doesn't necessarily have.
Instead, use `SetUserIdAndSecContext()` to temporarily switch to the owner of the `repack.log_%u` table for the INSERT only. This avoids running the entire trigger function as a superuser and reduces attack surface when allowing pg_repack without superuser.
Verified by running `pgbench` concurrently with `pg_repack`.
Setup:
- user `t1` is a superuser and owner of pgbench tables
- user `t2` is granted access only to pgbench tables
- `track_functions` GUC is set to `all`
```bash
$ pgbench -U t2 -P 1 -T 1000 pgbench
pgbench (18.6)
starting vacuum...end.
progress: 1.0 s, 368.0 tps, lat 2.710 ms stddev 0.408, 0 failed
progress: 2.0 s, 353.0 tps, lat 2.821 ms stddev 0.439, 0 failed
progress: 3.0 s, 281.0 tps, lat 3.567 ms stddev 0.649, 0 failed
progress: 4.0 s, 294.0 tps, lat 3.404 ms stddev 2.491, 0 failed
progress: 5.0 s, 275.0 tps, lat 3.627 ms stddev 4.363, 0 failed
progress: 6.0 s, 99.0 tps, lat 10.066 ms stddev 12.911, 0 failed
progress: 7.0 s, 84.9 tps, lat 11.815 ms stddev 14.276, 0 failed
progress: 8.0 s, 117.2 tps, lat 8.526 ms stddev 10.038, 0 failed
progress: 9.0 s, 174.0 tps, lat 5.756 ms stddev 5.877, 0 failed
progress: 10.0 s, 334.0 tps, lat 2.996 ms stddev 0.474, 0 failed
progress: 11.0 s, 349.0 tps, lat 2.859 ms stddev 0.381, 0 failed
progress: 12.0 s, 340.0 tps, lat 2.944 ms stddev 0.475, 0 failed
^C
```
```bash
$ pg_repack -U t1 -d pgbench -t public.pgbench_accounts
INFO: repacking table "public.pgbench_accounts"
```
Verify that `repack_trigger()` function has some calls:
```sql
$ psql -U t1 pgbench
psql (18.6)
Type "help" for help.
pgbench=# select * from pg_stat_user_functions where funcname = 'repack_trigger';
funcid | schemaname | funcname | calls | total_time | self_time
--------+------------+----------------+-------+------------+-----------
37322 | repack | repack_trigger | 386 | 110.769 | 110.769
(1 row)
```
inline patch 1: lib/pg_repack.sql.in, lib/repack.c, regress/Makefile and 2 more (+336/-4 lines) download inline-1.patchdiff --git a/lib/pg_repack.sql.in b/lib/pg_repack.sql.inindex b059645..a977c17 100644--- a/lib/pg_repack.sql.in+++ b/lib/pg_repack.sql.in@@ -339,7 +339,7 @@ LANGUAGE C STABLE;
CREATE FUNCTION repack.repack_trigger() RETURNS trigger AS
'MODULE_PATHNAME', 'repack_trigger'
-LANGUAGE C VOLATILE STRICT SECURITY DEFINER+LANGUAGE C VOLATILE STRICT
SET search_path = pg_catalog, pg_temp;
CREATE FUNCTION repack.conflicted_triggers(oid) RETURNS SETOF name AS
diff --git a/lib/repack.c b/lib/repack.cindex ca2c91a..f3d5bd1 100644--- a/lib/repack.c+++ b/lib/repack.c@@ -179,6 +179,13 @@ repack_trigger(PG_FUNCTION_ARGS)
Oid argtypes[2];
Oid relid;
StringInfo sql;
+ Oid log_owner;+ HeapTuple classtup;+ Oid save_userid;+ int save_sec_context;+ int save_nestlevel;+ char log_relname[NAMEDATALEN];+ Oid repack_nsid;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo) ||
@@ -218,18 +225,48 @@ repack_trigger(PG_FUNCTION_ARGS)
values[1] = copy_tuple(tuple, desc);
}
+ /*+ * Look up the log table owner so we can switch to that user for the+ * INSERT. This allows the trigger to write to the log table even when+ * fired by a user who doesn't own it without requiring SECURITY DEFINER on+ * the entire function.+ */+ repack_nsid = get_namespace_oid("repack", false);+ snprintf(log_relname, sizeof(log_relname), "log_%u", relid);+ classtup = SearchSysCache2(RELNAMENSP,+ CStringGetDatum(log_relname),+ ObjectIdGetDatum(repack_nsid));+ if (!HeapTupleIsValid(classtup))+ elog(ERROR, "repack_trigger: log table repack.%s not found", log_relname);+ log_owner = ((Form_pg_class) GETSTRUCT(classtup))->relowner;+ ReleaseSysCache(classtup);+
/* prepare INSERT query */
sql = makeStringInfo();
- appendStringInfo(sql, "INSERT INTO repack.log_%u(pk, row) "- "VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW(", relid);+ appendStringInfo(sql, "INSERT INTO repack.%s(pk, row) "+ "VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW(", log_relname);
appendStringInfo(sql, "$1.%s", quote_identifier(trigdata->tg_trigger->tgargs[0]));
for (int i = 1; i < trigdata->tg_trigger->tgnargs; ++i)
appendStringInfo(sql, ", $1.%s", quote_identifier(trigdata->tg_trigger->tgargs[i]));
appendStringInfo(sql, ")::repack.pk_%u) END, $2)", relid);
+ /* Switch to the repack.log_%u table owner, it gives enough privileges to INSERT */+ GetUserIdAndSecContext(&save_userid, &save_sec_context);+ SetUserIdAndSecContext(log_owner,+ save_sec_context |+ SECURITY_LOCAL_USERID_CHANGE |+ SECURITY_RESTRICTED_OPERATION);++ save_nestlevel = NewGUCNestLevel();+
/* execute the INSERT query */
execute_with_args(SPI_OK_INSERT, sql->data, 2, argtypes, values, nulls);
+ /* Roll back any GUC changes performed by rules during the INSERT */+ AtEOXact_GUC(false, save_nestlevel);++ SetUserIdAndSecContext(save_userid, save_sec_context);+
SPI_finish();
PG_RETURN_POINTER(tuple);
diff --git a/regress/Makefile b/regress/Makefileindex b9462f7..43c4d33 100644--- a/regress/Makefile+++ b/regress/Makefile@@ -17,7 +17,7 @@ INTVERSION := $(shell echo $$(($$(echo $(VERSION).0 | sed 's/\([[:digit:]]\{1,\}
# Test suite
#
-REGRESS := init-extension repack-setup repack-run error-on-invalid-idx no-error-on-invalid-idx after-schema repack-check nosuper tablespace get_order_by trigger publication+REGRESS := init-extension repack-setup repack-run error-on-invalid-idx no-error-on-invalid-idx after-schema repack-check nosuper tablespace get_order_by trigger trigger_privs publication
USE_PGXS = 1 # use pgxs if not in contrib directory
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/regress/expected/trigger_privs.out b/regress/expected/trigger_privs.outnew file mode 100644index 0000000..09f61f1--- /dev/null+++ b/regress/expected/trigger_privs.out@@ -0,0 +1,169 @@+--+-- repack.repack_trigger privilege handling tests+--+\set VERBOSITY terse+SELECT session_user AS orig_user+\gset+SET client_min_messages = error;+DROP ROLE IF EXISTS trigger_dml_user;+DROP ROLE IF EXISTS trigger_log_owner;+SET client_min_messages = warning;+CREATE ROLE trigger_dml_user;+CREATE ROLE trigger_log_owner;+CREATE TABLE trigger_privs_t1 (a int, b int, primary key (a, b));+GRANT INSERT, UPDATE, DELETE ON trigger_privs_t1 TO trigger_dml_user;+SELECT oid AS p1_oid FROM pg_catalog.pg_class WHERE relname = 'trigger_privs_t1'+\gset+CREATE TYPE repack.pk_:p1_oid AS (a int, b int);+CREATE TABLE repack.log_:p1_oid (id bigserial PRIMARY KEY, pk repack.pk_:p1_oid, row public.trigger_privs_t1);+CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON trigger_privs_t1+ FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('a', 'b');+-- The log table is owned by a role that has no other privileges on the+-- table being modified+ALTER TABLE repack.log_:p1_oid OWNER TO trigger_log_owner;+\set log_seq 'log_' :p1_oid '_id_seq'+ALTER SEQUENCE repack.:"log_seq" OWNER TO trigger_log_owner;+GRANT USAGE ON SCHEMA repack TO trigger_log_owner;+-- 1. The DML role has no privileges on repack objects, but DML works and+-- rows are logged+SELECT has_schema_privilege('trigger_dml_user', 'repack', 'USAGE') AS schema_usage,+ has_table_privilege('trigger_dml_user', 'repack.log_' || :p1_oid, 'INSERT') AS log_insert;+ schema_usage | log_insert +--------------+------------+ f | f+(1 row)++SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (1, 2);+UPDATE trigger_privs_t1 SET a = 3, b = 4;+DELETE FROM trigger_privs_t1;+RESET ROLE;+SELECT pk, row FROM repack.log_:p1_oid ORDER BY id;+ pk | row +-------+-------+ | (1,2)+ (1,2) | (3,4)+ (3,4) | +(3 rows)++TRUNCATE repack.log_:p1_oid;+-- Trigger on the log table; its behavior is selected by a setting+CREATE FUNCTION trigger_log_fn() RETURNS trigger LANGUAGE plpgsql AS $$+DECLARE+ mode text := current_setting('trigger_privs.mode');+BEGIN+ IF mode = 'set_role' THEN+ EXECUTE 'SET ROLE trigger_dml_user';+ ELSIF mode = 'set_session_auth' THEN+ EXECUTE 'SET SESSION AUTHORIZATION trigger_dml_user';+ ELSIF mode = 'guc' THEN+ PERFORM set_config('search_path', 'pg_catalog', false);+ PERFORM set_config('work_mem', '123456kB', false);+ RAISE NOTICE 'inside: search_path=% work_mem=%',+ current_setting('search_path'), current_setting('work_mem');+ END IF;+ RETURN NEW;+END;+$$;+CREATE TRIGGER trigger_log_trg BEFORE INSERT ON repack.log_:p1_oid+ FOR EACH ROW EXECUTE PROCEDURE trigger_log_fn();+-- 2. Role changes inside the log table trigger fail+SET trigger_privs.mode = 'set_role';+INSERT INTO trigger_privs_t1 VALUES (12, 22);+ERROR: cannot set parameter "role" within security-definer function+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (12, 22);+ERROR: cannot set parameter "role" within security-definer function+RESET ROLE;+SET trigger_privs.mode = 'set_session_auth';+INSERT INTO trigger_privs_t1 VALUES (12, 22);+ERROR: cannot set parameter "session_authorization" within security-definer function+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (12, 22);+ERROR: cannot set parameter "session_authorization" within security-definer function+RESET ROLE;+-- 3. Setting changes made inside the log table trigger are reverted+SET client_min_messages = notice;+SET search_path = public;+SET work_mem = '4MB';+SET trigger_privs.mode = 'guc';+INSERT INTO trigger_privs_t1 VALUES (14, 24);+NOTICE: inside: search_path=pg_catalog work_mem=123456kB+SHOW search_path;+ search_path +-------------+ public+(1 row)++SHOW work_mem;+ work_mem +----------+ 4MB+(1 row)++SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (15, 25);+NOTICE: inside: search_path=pg_catalog work_mem=123456kB+RESET ROLE;+SHOW search_path;+ search_path +-------------+ public+(1 row)++SHOW work_mem;+ work_mem +----------+ 4MB+(1 row)++RESET search_path;+RESET work_mem;+-- 4. Users are correct after DML+SET trigger_privs.mode = 'none';+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (17, 27);+SELECT current_user AS cu, session_user = :'orig_user' AS session_is_orig;+ cu | session_is_orig +------------------+-----------------+ trigger_dml_user | t+(1 row)++RESET ROLE;+SELECT current_user = :'orig_user' AS current_is_orig, session_user = :'orig_user' AS session_is_orig;+ current_is_orig | session_is_orig +-----------------+-----------------+ t | t+(1 row)++SELECT pk, row FROM repack.log_:p1_oid ORDER BY id;+ pk | row +----+---------+ | (14,24)+ | (15,25)+ | (17,27)+(3 rows)++-- 5. A missing log table is reported+DROP TRIGGER trigger_log_trg ON repack.log_:p1_oid;+ALTER TABLE repack.log_:p1_oid RENAME TO log_renamed;+DO $$+BEGIN+ INSERT INTO trigger_privs_t1 VALUES (18, 28);+EXCEPTION WHEN OTHERS THEN+ RAISE NOTICE '%', regexp_replace(SQLERRM, '[0-9]+', '<oid>');+END;+$$;+NOTICE: repack_trigger: log table repack.log_<oid> not found+ALTER TABLE repack.log_renamed RENAME TO log_:p1_oid;+-- cleanup+DROP TABLE repack.log_:p1_oid;+DROP TYPE repack.pk_:p1_oid;+DROP TABLE trigger_privs_t1;+DROP FUNCTION trigger_log_fn();+RESET trigger_privs.mode;+SET client_min_messages = warning;+DROP OWNED BY trigger_dml_user;+DROP OWNED BY trigger_log_owner;+DROP ROLE trigger_dml_user;+DROP ROLE trigger_log_owner;diff --git a/regress/sql/trigger_privs.sql b/regress/sql/trigger_privs.sqlnew file mode 100644index 0000000..c6d6952--- /dev/null+++ b/regress/sql/trigger_privs.sql@@ -0,0 +1,126 @@+--+-- repack.repack_trigger privilege handling tests+--+\set VERBOSITY terse++SELECT session_user AS orig_user+\gset++SET client_min_messages = error;+DROP ROLE IF EXISTS trigger_dml_user;+DROP ROLE IF EXISTS trigger_log_owner;+SET client_min_messages = warning;+CREATE ROLE trigger_dml_user;+CREATE ROLE trigger_log_owner;++CREATE TABLE trigger_privs_t1 (a int, b int, primary key (a, b));+GRANT INSERT, UPDATE, DELETE ON trigger_privs_t1 TO trigger_dml_user;++SELECT oid AS p1_oid FROM pg_catalog.pg_class WHERE relname = 'trigger_privs_t1'+\gset++CREATE TYPE repack.pk_:p1_oid AS (a int, b int);+CREATE TABLE repack.log_:p1_oid (id bigserial PRIMARY KEY, pk repack.pk_:p1_oid, row public.trigger_privs_t1);+CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON trigger_privs_t1+ FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('a', 'b');++-- The log table is owned by a role that has no other privileges on the+-- table being modified+ALTER TABLE repack.log_:p1_oid OWNER TO trigger_log_owner;+\set log_seq 'log_' :p1_oid '_id_seq'+ALTER SEQUENCE repack.:"log_seq" OWNER TO trigger_log_owner;+GRANT USAGE ON SCHEMA repack TO trigger_log_owner;++-- 1. The DML role has no privileges on repack objects, but DML works and+-- rows are logged+SELECT has_schema_privilege('trigger_dml_user', 'repack', 'USAGE') AS schema_usage,+ has_table_privilege('trigger_dml_user', 'repack.log_' || :p1_oid, 'INSERT') AS log_insert;+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (1, 2);+UPDATE trigger_privs_t1 SET a = 3, b = 4;+DELETE FROM trigger_privs_t1;+RESET ROLE;+SELECT pk, row FROM repack.log_:p1_oid ORDER BY id;+TRUNCATE repack.log_:p1_oid;++-- Trigger on the log table; its behavior is selected by a setting+CREATE FUNCTION trigger_log_fn() RETURNS trigger LANGUAGE plpgsql AS $$+DECLARE+ mode text := current_setting('trigger_privs.mode');+BEGIN+ IF mode = 'set_role' THEN+ EXECUTE 'SET ROLE trigger_dml_user';+ ELSIF mode = 'set_session_auth' THEN+ EXECUTE 'SET SESSION AUTHORIZATION trigger_dml_user';+ ELSIF mode = 'guc' THEN+ PERFORM set_config('search_path', 'pg_catalog', false);+ PERFORM set_config('work_mem', '123456kB', false);+ RAISE NOTICE 'inside: search_path=% work_mem=%',+ current_setting('search_path'), current_setting('work_mem');+ END IF;+ RETURN NEW;+END;+$$;+CREATE TRIGGER trigger_log_trg BEFORE INSERT ON repack.log_:p1_oid+ FOR EACH ROW EXECUTE PROCEDURE trigger_log_fn();++-- 2. Role changes inside the log table trigger fail+SET trigger_privs.mode = 'set_role';+INSERT INTO trigger_privs_t1 VALUES (12, 22);+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (12, 22);+RESET ROLE;+SET trigger_privs.mode = 'set_session_auth';+INSERT INTO trigger_privs_t1 VALUES (12, 22);+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (12, 22);+RESET ROLE;++-- 3. Setting changes made inside the log table trigger are reverted+SET client_min_messages = notice;+SET search_path = public;+SET work_mem = '4MB';+SET trigger_privs.mode = 'guc';+INSERT INTO trigger_privs_t1 VALUES (14, 24);+SHOW search_path;+SHOW work_mem;+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (15, 25);+RESET ROLE;+SHOW search_path;+SHOW work_mem;+RESET search_path;+RESET work_mem;++-- 4. Users are correct after DML+SET trigger_privs.mode = 'none';+SET ROLE trigger_dml_user;+INSERT INTO trigger_privs_t1 VALUES (17, 27);+SELECT current_user AS cu, session_user = :'orig_user' AS session_is_orig;+RESET ROLE;+SELECT current_user = :'orig_user' AS current_is_orig, session_user = :'orig_user' AS session_is_orig;+SELECT pk, row FROM repack.log_:p1_oid ORDER BY id;++-- 5. A missing log table is reported+DROP TRIGGER trigger_log_trg ON repack.log_:p1_oid;+ALTER TABLE repack.log_:p1_oid RENAME TO log_renamed;+DO $$+BEGIN+ INSERT INTO trigger_privs_t1 VALUES (18, 28);+EXCEPTION WHEN OTHERS THEN+ RAISE NOTICE '%', regexp_replace(SQLERRM, '[0-9]+', '<oid>');+END;+$$;+ALTER TABLE repack.log_renamed RENAME TO log_:p1_oid;++-- cleanup+DROP TABLE repack.log_:p1_oid;+DROP TYPE repack.pk_:p1_oid;+DROP TABLE trigger_privs_t1;+DROP FUNCTION trigger_log_fn();+RESET trigger_privs.mode;+SET client_min_messages = warning;+DROP OWNED BY trigger_dml_user;+DROP OWNED BY trigger_log_owner;+DROP ROLE trigger_dml_user;+DROP ROLE trigger_log_owner;