Second Brain

postgres-force-rls-security-definer-trigger-pattern

3 min read

Postgres FORCE RLS + SECURITY DEFINER trigger pattern

Postgres FORCE RLS + SECURITY DEFINER trigger pattern

Problem

Enabling FORCE ROW LEVEL SECURITY on a table fed by SECURITY DEFINER triggers (e.g., audit_log written by an audit_log_trigger function) breaks the trigger’s INSERT — the trigger function is also subject to FORCE RLS, even though it runs as the table owner.

Naïve fix that fails

Drop FORCE and use only ENABLE — owner bypasses RLS, trigger works. Bad: in production where role separation isn’t always enforced (single PG role for app + owner), the app code now also bypasses RLS via the same owner-context. Privilege escalation surface.

Canonical solution

Annotate the trigger function with a per-function row_security override:

ALTER FUNCTION audit_log_trigger() SET row_security TO OFF;

Table stays FORCE RLS for everything else; only this one function bypasses. App code running under non-owner roles is still filtered by the SELECT policy. The audit table becomes a true append-only log: only the trigger writes (with row_security OFF for that function), only admins read (via SELECT policy with is_admin()), and there’s no INSERT/UPDATE/DELETE policy so application code cannot touch it directly.

Discovery context

R-Plan migration 0006 (Wave 6 RLS hardening). Initial migration enabled FORCE RLS on audit_log without an INSERT policy. Every UPDATE on a different table triggered its audit_log_trigger which then failed FORCE RLS check → entire UPDATE transaction rolled back → cascading test failures.

Apply pattern to

Any audit-table / event-log / soft-delete-history table with SECURITY DEFINER trigger writers. Common at:

  • audit_log (every project that captures audited mutations)
  • event_log / domain_events (event sourcing)
  • soft_delete_archive (compliance retention tables)
  • session_log (auth tracking)

Verify it works

-- Non-admin attempts SELECT — should return zero rows
SET LOCAL r_plan.user_id = '<non-admin-user-id>';
SELECT COUNT(*) FROM audit_log;  -- 0
 
-- Trigger-fed INSERT (via UPDATE on audited table) succeeds
UPDATE projects SET name = 'test' WHERE id = '<project-id>';
 
-- Admin sees the new audit row
SET LOCAL r_plan.user_id = '<admin-user-id>';
SELECT COUNT(*) FROM audit_log;  -- 1+

Anti-patterns to avoid

  • ❌ ENABLE ROW LEVEL SECURITY without FORCE — owner-context (potentially app code) bypasses
  • ❌ INSERT policy WITH CHECK (true) — anyone can write directly
  • ❌ INSERT policy keyed on application-level session var that the trigger context doesn’t set — trigger fails silently
  • ❌ Dropping the trigger and writing audit rows from app code — loses the SECURITY DEFINER guarantee that audit can’t be skipped
  • ADR-0003 in R-Plan (3-layer authz model: Server Action helpers + RLS + audit log)
  • Postgres docs: https://www.postgresql.org/docs/current/sql-alterfunction.html (per-function row_security)
  • pg_policies view for inspecting policies on a table

Graph View

Backlinks