Attributed writes — the SQL pattern¶

The app connects as the workload identity. Every row it writes carries the end-user immutable id (Entra oid / Google sub / IdP sub) alongside — set via a transaction-local SET LOCAL and read back by a BEFORE trigger. The end user is never a database principal; the audit trail is still airtight.

Reads from Trust — overview

The two-identity rule is set on the overview page; this page is the concrete pattern.

The three pieces¶

A SET LOCAL at the start of the transaction carrying the end-user immutable id.
A trigger on every audited table that reads the setting into an actor_oid column.
A refusal to commit if the setting isn't present — no anonymous writes.

Everything below is pure Postgres — the same DDL runs on Azure Postgres Flexible Server, Cloud SQL Postgres, RDS Postgres, and pgvector/pgvector:pg16 on your laptop.

Schema¶

CREATE SCHEMA IF NOT EXISTS trust;

-- Read the end-user immutable id set by the app for this txn. Returns NULL
-- when not set (used by the guard trigger to refuse anonymous writes).
CREATE OR REPLACE FUNCTION trust.current_actor() RETURNS text
LANGUAGE sql STABLE AS $$
    SELECT NULLIF(current_setting('chiron.actor_oid', true), '');
$$;

-- One column on every audited row: who asked for this write.
-- Use immutable id (oid / sub) — NEVER email or display name.
CREATE TABLE IF NOT EXISTS audited_thing (
    id           bigserial PRIMARY KEY,
    payload      jsonb NOT NULL,
    actor_oid    text NOT NULL,          -- filled by the trigger below
    approver_oid text,                    -- for AI-anchored writes; see below
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- Guard trigger — refuse writes if the app didn't stamp an actor.
CREATE OR REPLACE FUNCTION trust.stamp_actor() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.actor_oid := trust.current_actor();
    IF NEW.actor_oid IS NULL THEN
        RAISE EXCEPTION 'chiron.actor_oid not set; anonymous writes are refused';
    END IF;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE TRIGGER stamp_actor_before_insert
    BEFORE INSERT ON audited_thing
    FOR EACH ROW EXECUTE FUNCTION trust.stamp_actor();

The trigger runs on every insert. If your code forgets to set the config, the row is refused — fail-closed by construction.

The Python side¶

import psycopg

def write_with_actor(actor_oid: str, payload: dict) -> int:
    """`actor_oid` MUST come from the verified edge-auth claim (oid/sub)."""
    with psycopg.connect(DATABASE_URL) as conn, conn.transaction():
        with conn.cursor() as cur:
            # Transaction-local — cleared on COMMIT/ROLLBACK. The 'true'
            # flag makes it session-scoped so no future statement inherits.
            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),))
            return cur.fetchone()[0]

Three things worth noticing:

set_config(..., true) is transaction-local. The next txn on the same pooled connection starts clean — no leakage of one user's id into another user's write.
The only thing crossing from app-code into SQL is the immutable id read from the verified edge-auth header (X-MS-CLIENT-PRINCIPAL-ID on Azure, the verified sub from x-goog-iap-jwt-assertion on GCP, the verified sub from x-amzn-oidc-data on AWS).
The DB user in DATABASE_URL is the workload identity (Managed Identity, GCP SA, or IRSA role). It has INSERT on audited_thing; the end user has no DB principal at all.

AI-anchored writes — agent proposes, human approves¶

For agentic flows: the model drafts, but only a named human's approval commits. Both identities land in the row — the actor (whoever executed the SQL — usually the same workload identity that ran the agent), and the approver (the human who clicked "yes").

-- Extend the guard: for AI-anchored writes, require approver_oid too.
CREATE OR REPLACE FUNCTION trust.stamp_actor_and_approver() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.actor_oid    := trust.current_actor();
    NEW.approver_oid := NULLIF(current_setting('chiron.approver_oid', true), '');
    IF NEW.actor_oid IS NULL THEN
        RAISE EXCEPTION 'chiron.actor_oid not set';
    END IF;
    IF NEW.approver_oid IS NULL THEN
        RAISE EXCEPTION 'chiron.approver_oid not set — AI-anchored writes require a named human approval';
    END IF;
    RETURN NEW;
END;
$$;

-- Use the extended trigger on tables where every write is AI-anchored.
CREATE OR REPLACE TRIGGER stamp_actor_and_approver_before_insert
    BEFORE INSERT ON audited_thing
    FOR EACH ROW EXECUTE FUNCTION trust.stamp_actor_and_approver();

def ai_anchored_write(actor_oid: str, approver_oid: str, payload: dict) -> int:
    """actor_oid == workload/service; approver_oid == verified human that clicked OK."""
    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("SELECT set_config('chiron.approver_oid', %s, true)", (approver_oid,))
            cur.execute("INSERT INTO audited_thing (payload) VALUES (%s) RETURNING id",
                        (psycopg.types.json.Json(payload),))
            return cur.fetchone()[0]

If the approver claim is missing, the write is refused — no agent-only commits.

What this pattern buys you¶

Attribution survives without giving end users DB rights. The end user can never issue arbitrary SQL, but every audited row still names them by immutable id.
AI-generated writes are never anonymous. Every agentic commit is chained to a named human approver via approver_oid — the trigger refuses the write otherwise.
The DB user rotates on its own cadence. Managed identity / workload federation / IRSA never appear in end-user churn.
Fail-closed by construction. Forget the set_config call and the trigger refuses the insert. No compile-time discipline needed.

What this pattern does NOT give you¶

Row-level authorization. This layer records who — not whether they were allowed. Use RLS or app-level checks for that.
Verification of the edge claim. The pattern trusts that actor_oid came from a verified header. Verify at the edge (IAP / ALB / Easy Auth) and only trust the verified claim — never a raw x-goog-authenticated-user-* or x-amzn-oidc-identity if you didn't verify the JWT signature above it. See the overview.

Verify (local, no cloud)¶

docker run --rm -d --name pg -e POSTGRES_PASSWORD=chiron -p 5432:5432 pgvector/pgvector:pg16

# Apply the DDL
psql postgresql://postgres:chiron@127.0.0.1/postgres -f examples/trust/sql/schema.sql

# Refuse anonymous write
psql -c "INSERT INTO audited_thing (payload) VALUES ('{}'::jsonb);"
# ERROR: chiron.actor_oid not set; anonymous writes are refused

# Attributed write — set-config then insert in one txn
psql <<'SQL'
BEGIN;
SELECT set_config('chiron.actor_oid', 'oid-alice-immutable-id', true);
INSERT INTO audited_thing (payload) VALUES ('{"note":"hello"}'::jsonb) RETURNING id, actor_oid;
COMMIT;
SQL

Where the arc goes next¶

Deep pages per cloud that walk the full edge-auth → verified claim → workload IAM → passwordless DB IAM → this pattern chain, with real Terraform.
Row-level security compose (RLS + this pattern) — after the deep pages.