P9 G6 Trigger Guard Repair Option A Log

Date: 2026-04-28
Executor: Codex CLI via SSH contabo
Scope: patch public.fn_evt_trigger_guard() on directus and incomex_metadata
Verdict: PASS

0. Boundary

Hard exclusions observed:

• No event trigger disable/drop.
• No trigger_guard_alerts drop/create.
• No whitelist or hardcoded exception for p9_g6_dryrun.
• No G6 retry.
• No public.tac_*, Directus collection/role/permission, labels, issues, or dot log writes.
• No git commit/push.

Hostname:

vmi3080463

1. 3 câu Tuyên ngôn

Câu	Answer
Vĩnh viễn?	Root fix makes guard function independent from caller search_path.
Nhầm được không?	Internal alert table is schema-qualified and function has deterministic search_path.
100% tự động?	Event trigger remains enabled and smoke test proves automatic alert insert on trigger DDL.

2. Pre-checks

2.1 Connection and role evidence

workflow_admin is available over local Docker socket and is the function owner/superuser:

workflow_admin

directus is not the function owner/member:

current_user_member_of_owner=f

No role named postgres exists in the cluster:

FATAL: role "postgres" does not exist

Therefore mutation was executed as workflow_admin, preserving owner workflow_admin.

2.2 directus pre-check

current_database=directus
current_user=directus
public.trigger_guard_alerts exists
evt_trigger_guard_ddl  ddl_command_end O public.fn_evt_trigger_guard
evt_trigger_guard_drop sql_drop        O public.fn_evt_trigger_guard_drop
owner=workflow_admin
security_definer=f
proconfig=NULL
before_sha256=31473118ce6fc1fb92a5e050093a58cd04479dd6bfeeeca03e6255e46c751b35
qualified_ref=0
unqualified_insert=468
invoker_can_insert_alerts=t
invoker_can_select_alerts=t
directus_can_create_schema=t
directus_can_create_in_public=t
pre_alert_rows=73

SECURITY decision: keep SECURITY INVOKER for directus, because the actual invoker role directus has INSERT privilege on public.trigger_guard_alerts.

2.3 incomex_metadata pre-check

current_database=incomex_metadata
current_user=directus
public.trigger_guard_alerts exists
evt_trigger_guard_ddl  ddl_command_end O public.fn_evt_trigger_guard
evt_trigger_guard_drop sql_drop        O public.fn_evt_trigger_guard_drop
owner=workflow_admin
security_definer=f
proconfig=NULL
before_sha256=31473118ce6fc1fb92a5e050093a58cd04479dd6bfeeeca03e6255e46c751b35
qualified_ref=0
unqualified_insert=468
invoker_can_insert_alerts=f
invoker_can_select_alerts=t
directus_can_create_schema=f
directus_can_create_in_public=f
directus_can_temp=t
pre_alert_rows=2

SECURITY decision: upgrade to SECURITY DEFINER for incomex_metadata, because role directus lacks INSERT privilege on public.trigger_guard_alerts. The DB also does not allow directus to create persistent smoke schemas, so smoke there was executed as owner workflow_admin; the SECURITY DEFINER decision is based on privilege evidence.

3. Mutation

Executed with docker exec postgres psql -U workflow_admin.

3.1 directus

### PATCH DB=directus security=SECURITY INVOKER
BEGIN
CREATE FUNCTION
ALTER FUNCTION
COMMIT

3.2 incomex_metadata

### PATCH DB=incomex_metadata security=SECURITY DEFINER
BEGIN
CREATE FUNCTION
ALTER FUNCTION
COMMIT

Function diff summary:

- INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
+ INSERT INTO public.trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)

- proconfig=NULL
+ SET search_path TO 'pg_catalog', 'public'

directus:
  SECURITY INVOKER retained

incomex_metadata:
  SECURITY INVOKER -> SECURITY DEFINER

Owner after mutation remains workflow_admin.

4. Post-verify

4.1 directus post-verify

owner=workflow_admin
security_definer=f
proconfig={"search_path=pg_catalog, public"}
after_sha256=99f194785bb86ca13469cd0559127f1cab040e614269aa66ded99a2ea09cc567
qualified_ref=523
unqualified_insert=0
evt_trigger_guard_ddl=O
evt_trigger_guard_drop=O

Boolean verification:

has_qualified_alerts=t
no_unqualified_insert=t
smoke_schema_residue=0

4.2 incomex_metadata post-verify

owner=workflow_admin
security_definer=t
proconfig={"search_path=pg_catalog, public"}
after_sha256=429543fcb33d9f06767e1e021ab56376c4aabb493d672c9834c945bc9e29ecc1
qualified_ref=541
unqualified_insert=0
evt_trigger_guard_ddl=O
evt_trigger_guard_drop=O

Boolean verification:

has_qualified_alerts=t
no_unqualified_insert=t
smoke_schema_residue=0

5. Smoke tests

Smoke object names were timestamped and unique. Test alert rows were kept as audit evidence, as allowed by the gate.

5.1 directus smoke

Executed as role directus.

DB=directus
SMOKE_SCHEMA=tg_repair_smoke_directus_20260428032550
SMOKE_TRIGGER=trg_tg_repair_smoke_directus_20260428032550
pre_matching_alert_rows=0
SET search_path = p9_g6_dryrun, pg_catalog
CREATE SCHEMA
CREATE TABLE
CREATE FUNCTION
WARNING: [TRIGGER-GUARD] DDL detected: CREATE TRIGGER on trg_tg_repair_smoke_directus_20260428032550 on tg_repair_smoke_directus_20260428032550.t
CREATE TRIGGER
DROP SCHEMA
smoke_schema_after_drop=NULL

Audit alert row retained:

id=130
trigger_name=trg_tg_repair_smoke_directus_20260428032550 on tg_repair_smoke_directus_20260428032550.t
table_name=unknown
expected_enabled=t
actual_enabled=t
detected_at=2026-04-28 03:25:51.023283+00

5.2 incomex_metadata smoke

Executed as role workflow_admin because role directus has no persistent schema/table creation privilege in incomex_metadata.

DB=incomex_metadata
SMOKE_SCHEMA=tg_repair_smoke_incomex_metadata_20260428032550
SMOKE_TRIGGER=trg_tg_repair_smoke_incomex_metadata_20260428032550
pre_matching_alert_rows=0
SET search_path = p9_g6_dryrun, pg_catalog
CREATE SCHEMA
CREATE TABLE
CREATE FUNCTION
WARNING: [TRIGGER-GUARD] DDL detected: CREATE TRIGGER on trg_tg_repair_smoke_incomex_metadata_20260428032550 on tg_repair_smoke_incomex_metadata_20260428032550.t
CREATE TRIGGER
DROP SCHEMA
smoke_schema_after_drop=NULL

Audit alert row retained:

id=6
trigger_name=trg_tg_repair_smoke_incomex_metadata_20260428032550 on tg_repair_smoke_incomex_metadata_20260428032550.t
table_name=unknown
expected_enabled=t
actual_enabled=t
detected_at=2026-04-28 03:25:51.358838+00

6. Rollback

Rollback was not executed because post-verify and smoke tests passed.

Rollback snapshot available:

before_sha256=31473118ce6fc1fb92a5e050093a58cd04479dd6bfeeeca03e6255e46c751b35
owner=workflow_admin
security_definer=f
proconfig=NULL
body contained unqualified INSERT INTO trigger_guard_alerts

Rollback method, if later authorized:

CREATE OR REPLACE FUNCTION public.fn_evt_trigger_guard()
RETURNS event_trigger
LANGUAGE plpgsql
SECURITY INVOKER
AS $function$
DECLARE
  r RECORD;
  v_cmd TEXT;
  v_obj_type TEXT;
  v_schema TEXT;
  v_obj_name TEXT;
BEGIN
  FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    v_cmd := r.command_tag;
    v_obj_type := r.object_type;
    v_schema := r.schema_name;
    v_obj_name := r.object_identity;

    -- Log all trigger DDL
    IF v_obj_type = 'trigger' THEN
      INSERT INTO trigger_guard_alerts (trigger_name, table_name, expected_enabled, actual_enabled)
      VALUES (
        v_obj_name,
        COALESCE(v_schema, 'unknown'),
        TRUE,
        CASE WHEN v_cmd = 'DROP TRIGGER' THEN FALSE ELSE TRUE END
      );
      RAISE WARNING '[TRIGGER-GUARD] DDL detected: % on %', v_cmd, v_obj_name;
    END IF;
  END LOOP;
END;
$function$;
ALTER FUNCTION public.fn_evt_trigger_guard() OWNER TO workflow_admin;

7. Final verdict

PASS.

public.fn_evt_trigger_guard() is now schema-qualified and deterministic on both DBs:

• directus: SECURITY INVOKER, search_path=pg_catalog, public, smoke PASS as directus.
• incomex_metadata: SECURITY DEFINER, search_path=pg_catalog, public, smoke PASS as workflow_admin; DEFINER justified by directus lacking INSERT on alerts.

No G6 retry was executed.

8. Secret hygiene scan

LEAK_SCAN_PASS targets=1 patterns=9 hex_pattern=1 disallowed_matches=0 checksum_hex_allowlisted=yes