# ADR-007: Trigger Functions Writing Privileged Tables Must Use SECURITY DEFINER

## Status

Accepted, 2026-06-25.

## Status History

```yaml
status_history:
  - date: 2026-06-25
    status: Proposed
    changed_by: hkl
    reason: Formalising pattern discovered after 42501 permission errors in production
    changed_via: adr-kit (360lm)
  - date: 2026-06-25
    status: Accepted
    changed_by: hkl
    reason: Pattern applied to all affected trigger functions; no open incidents
    changed_via: adr-kit (360lm)
```

## Context

PostgreSQL trigger functions run as the invoking user by default (i.e., the role that fired the triggering DML statement). In 360lm, all PostgREST API calls run as the `web_anon` role, which has limited grants — it can read and write application tables but is explicitly denied access to certain privileged tables (audit logs, grant tables, system metadata). When a trigger on an application table tries to write to a privileged table (e.g., inserting an audit record), PostgreSQL raises error `42501: permission denied for table <privileged_table>` because `web_anon` lacks that grant. The fix is `SECURITY DEFINER` on the trigger function, which causes it to run as the function owner (typically `postgres` or a superuser role) rather than as the caller.

## Decision

Any PostgreSQL trigger function that writes to a table not accessible by `web_anon` MUST be declared with `SECURITY DEFINER`. Add a comment in the function body explaining which privileged table requires it and why.

```sql
CREATE OR REPLACE FUNCTION audit.log_change()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- required: web_anon cannot write to audit.log
AS $$
BEGIN
  INSERT INTO audit.log(...) VALUES (...);
  RETURN NEW;
END;
$$;
```

**Decision Maker:** hkl

## Alternatives Considered

- **Grant web_anon write access to the privileged table.** Rejected: defeats the purpose of the privilege separation — audit tables and grant tables must not be writable by the API role to preserve integrity.
- **Move the privileged write to a separate RPC function called explicitly.** Rejected: triggers are the right tool for automatic audit/side-effect writes; requiring explicit RPC calls risks the side effect being skipped by future callers.
- **Use a separate database role for trigger execution.** Rejected: adds role management complexity with no benefit over SECURITY DEFINER on the specific function.

## Consequences

**Positive:**
- Trigger functions can write to privileged tables without relaxing web_anon grants.
- Privilege separation preserved — application code runs as web_anon; audit writes run as owner.

**Negative / Trade-offs:**
- SECURITY DEFINER functions must be written carefully — they run with elevated privileges, so SQL injection or logic bugs have higher impact.
- Easy to forget on new trigger functions; error only surfaces at runtime.

**Risks and mitigations:**
- SECURITY DEFINER function with injectable parameters: mitigated by using parameterised queries (`USING` / `$1` syntax) inside trigger functions, never string concatenation.
- Privilege escalation via trigger: mitigated by limiting SECURITY DEFINER to trigger functions only; no application-facing RPCs use SECURITY DEFINER.

## Related Decisions

- ADR-008 (PL/pgSQL RETURNS TABLE column shadowing) — related DB function pattern.

## References

- `memory/feedback_pg_trigger_security_definer.md` — original rule capture
- PostgreSQL docs: SECURITY DEFINER functions