pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
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.in index 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.c index 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/Makefile index 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.out new file mode 100644 index 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.sql new file mode 100644 index 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;

view thread (6+ messages)  latest in thread

Message-ID: <gh-reorg-pg_repack-487@github.com>
Permalink:  ../gh-reorg-pg_repack-487@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox