Skip to content

Row-level security with the audit column

Compose RLS on top of the audit-column pattern. Same chiron.actor_oid setting, same workload identity as the DB principal — now Postgres enforces that a reviewer only sees rows keyed to them.

Verified 2026-08-09

The pattern

  1. Every audited table already has actor_oid text NOT NULL from the attributed-write DDL.
  2. Enable RLS on the table: ALTER TABLE <t> ENABLE ROW LEVEL SECURITY.
  3. Force it so the workload owner is also subject: ALTER TABLE <t> FORCE ROW LEVEL SECURITY — otherwise the owner (usually the workload identity that created the table) bypasses policies, defeating the enforcement.
  4. Write the policies — read/write predicates keyed to current_setting('chiron.actor_oid', true).
  5. Keep the guard trigger from the surface intro — RLS gates visibility, the trigger gates stamping. Both matter.

Concrete DDL

Extend the audited_thing table from examples/trust/sql/schema.sql with reviewer-scoped RLS:

-- Turn RLS on. Without FORCE, the table owner bypasses policies; with
-- FORCE, even the workload identity that owns the table has to satisfy them.
ALTER TABLE audited_thing ENABLE ROW LEVEL SECURITY;
ALTER TABLE audited_thing FORCE  ROW LEVEL SECURITY;

-- Read policy: a reviewer sees only rows they authored OR approved.
-- current_setting('chiron.actor_oid', true) returns NULL when the app
-- forgot to stamp — an unstamped session sees ZERO rows (safer than the
-- default-deny would suggest, because no row's actor_oid or approver_oid
-- equals NULL).
CREATE POLICY reviewer_read ON audited_thing
    FOR SELECT
    USING (
        actor_oid    = current_setting('chiron.actor_oid', true)
     OR approver_oid = current_setting('chiron.actor_oid', true)
    );

-- Write policy: stamp only rows the caller will own. Combined with the
-- BEFORE INSERT trigger from attributed-writes.md, this makes it
-- impossible to insert a row attributed to another user.
CREATE POLICY reviewer_write ON audited_thing
    FOR INSERT
    WITH CHECK (
        actor_oid = current_setting('chiron.actor_oid', true)
    );

-- Optional: separate policy for a reviewer-manager role that sees
-- everyone's rows. Bind the role at connect time, not per-user.
CREATE POLICY reviewer_manager_read ON audited_thing
    FOR SELECT
    TO reviewer_manager
    USING (true);

The syntax is the standard Postgres RLS grammar per ddl-rowsecurity: CREATE POLICY name ON table [FOR ALL|SELECT|INSERT|UPDATE|DELETE] [TO role] USING (predicate) [WITH CHECK (predicate)]. Multiple permissive policies (the default) combine with OR; restrictive policies combine with AND.

The application side

Zero code change from the audit-column pattern. The app still opens a transaction, stamps chiron.actor_oid, and inserts:

def write_and_query(actor_oid: str, payload: dict) -> list[dict]:
    with psycopg.connect(DATABASE_URL) as conn, conn.transaction():
        with conn.cursor() as cur:
            cur.execute("SELECT set_config('chiron.actor_oid', %s, true)",
                        (actor_oid,))
            cur.execute(
                "INSERT INTO audited_thing (payload) VALUES (%s) RETURNING id",
                (psycopg.types.json.Json(payload),))
            new_id = cur.fetchone()[0]

            # This SELECT is filtered by the reviewer_read policy —
            # the app can't accidentally see other reviewers' rows even
            # if the WHERE clause is missing.
            cur.execute("SELECT id, payload FROM audited_thing")
            cols = [c.name for c in cur.description]
            return [dict(zip(cols, row)) for row in cur.fetchall()]

The load-bearing property: the app can't leak even if the developer forgets a WHERE. Postgres wraps every SELECT on the table with the USING predicate.

Enforcement corners

  • Owner bypass without FORCE. Without FORCE ROW LEVEL SECURITY, the table owner (the role that ran CREATE TABLE) skips all policies. Since the workload identity usually created the tables via migrations, forgetting FORCE means the app sees every row. Always run both ENABLE and FORCE.
  • NULL-on-unset. current_setting('chiron.actor_oid', true) returns NULL when the app forgot to stamp. NULL = NULL is NULL in SQL, which behaves as "false" in a policy predicate — so an unstamped session sees zero rows. That's a safe default, but if you want an error instead, keep the guard trigger from the surface intro that raises when actor_oid is missing on write.
  • BYPASSRLS role attribute. A DB role marked BYPASSRLS sees everything regardless of policies. Reserve this for one-off admin queries; don't grant it to the workload role.
  • Superuser bypass. Postgres superusers always bypass RLS. Never grant SUPERUSER to the workload identity; use the least-privilege role per passwordless deep.
  • RLS + INSERT ... SELECT. The SELECT reads through USING, the INSERT checks against WITH CHECK. Both apply. This is usually what you want, but be explicit in policy design.
  • Materialized views + RLS. A materialized view snapshots the underlying rows at refresh time; the RLS predicates on the base table do not carry over. Either don't use materialized views for RLS-protected data, or grant SELECT only to trusted roles.

Where RLS is NOT the right tool

  • Public read tables. If everyone reads everything, RLS just adds cost.
  • Non-user-attributed data. Reference data, catalog, config tables — no session variable maps to a per-row predicate, so RLS is noise.
  • Admin dashboards that legitimately need cross-user visibility. Give the dashboard's workload identity a separate admin_dashboard role and grant it SELECT with a TO admin_dashboard USING (true) policy — or, if the dashboard has DBA-scale authority, BYPASSRLS. Keep this scope narrow and audited.

Verify (local, no cloud)

docker run --rm -d --name pg -e POSTGRES_PASSWORD=chiron -p 5432:5432 pgvector/pgvector:pg16
psql postgresql://postgres:chiron@127.0.0.1/postgres -f examples/trust/sql/schema.sql
psql postgresql://postgres:chiron@127.0.0.1/postgres -f examples/trust/patterns/rls.sql

# Verify: alice can't see bob's row.
psql <<'SQL'
BEGIN;
SELECT set_config('chiron.actor_oid', 'oid-alice', true);
INSERT INTO audited_thing (payload) VALUES ('{"note":"alice write"}'::jsonb);
COMMIT;

BEGIN;
SELECT set_config('chiron.actor_oid', 'oid-bob', true);
INSERT INTO audited_thing (payload) VALUES ('{"note":"bob write"}'::jsonb);
SELECT id, actor_oid, payload FROM audited_thing;
-- Returns only bob's row.
COMMIT;

BEGIN;
-- No set_config: NULL comparison — zero rows.
SELECT id FROM audited_thing;
COMMIT;
SQL

Where the arc goes next

  • Compose the RLS pattern with the passwordless connect and the verified edge claim into a single working stack.
  • Later — multi-tenant workspace isolation (RLS keyed to workspace_id instead of actor_oid), quotas, and cross-account federation.