Skip to content

Attribution patterns compared

The third deep page in the trust arc. Three ways to attribute a database write to the end user; each buys you something different at a different cost. The surface intro taught the default (audit column). This page compares it against the other two — connect-as-user (OBO / delegated) and row-level security — and gives a decision guide for when to reach for each.

The three patterns at a glance

# Pattern Who the DB sees as the caller Where the end-user id lives Pool-friendly?
1 Audit column (default) Workload identity actor_oid column filled by trigger from set_config('chiron.actor_oid', …, true) ✅ yes
2 Connect-as-user (OBO / delegated) The end user (a real DB principal) Is the DB session (current_user) ❌ no — one connection per user session
3 Row-level security (RLS) Workload identity actor_oid column + RLS policy reading current_setting('chiron.actor_oid', true) ✅ yes

Each of these is a Postgres pattern; each ports across Azure PG Flexible / Cloud SQL / RDS unchanged. All three compose with the passwordless connect story from TA.1 and the verified oid/sub from TA.2.

Tradeoffs table

Dimension Audit column Connect-as-user (OBO) Row-level security
DB principal count 1 (workload) 1 per end user 1 (workload)
Connection pool works Yes — pooled connections stay valid No — cache per user, or reconnect per request Yes
Attribution recorded Yes, in actor_oid column via trigger Yes, current_user / server log Yes, in actor_oid column via trigger
Row-level filtering App must do it Standard GRANT on tables + views Enforced by the DB (RLS predicates)
Bypassable if app code is wrong Column can be forged if trigger not FORCE ROW LEVEL SECURITY-style enforced No — DB is the enforcer No — DB is the enforcer
New-user friction None (no DB principal to create) Provision a DB role per user (or per group) None
Ops complexity Trigger + set_config OBO token exchange, per-user grants, session churn Policies per table
Works for AI agents proposing writes Yes — actor_oid = agent SA, approver_oid = human Awkward — agent needs its own DB principal + delegated grants Yes — same as audit column
Best for Every audited-write recipe; the E3 RAG service; anything with an agent-in-the-loop Human-only analyst tooling; workloads with per-user resource quotas Multi-tenant reviewer / triage / operator queues

Decision guide (which when)

Ask three questions in order:

  1. Does the DB itself need to enforce which rows a user can read? → Yes → RLS. Even if you also want the audit column (you probably do), RLS is what stops a bug in the app from returning other users' rows. → No → go to (2).

  2. Is every writer a real named person on your identity provider, with a small enough churn rate that per-user DB roles are practical? (Analyst tools, ops consoles, DBA-adjacent workflows — think tens or low hundreds of users.) → Yes and you want the DB to be able to enforce per-user quotas / grants / audit-log entries against the actual user — → connect-as-user. Accept the pooling loss. → No → go to (3).

  3. Are agents / batch jobs / AI models ever the writer, and you need the human approval attribution? → Yes → audit column (with the AI-anchored variant — trigger requires both actor_oid and approver_oid). → No → audit column still, it's the default.

Rule of thumb: audit column is the default; RLS composes on top when the reader/writer needs DB-enforced isolation; connect-as-user is a specialty tool for the small set of workloads that genuinely benefit from a per-user DB principal — nearly always worth NOT.

Pattern 1 — Audit column (the default)

Covered end-to-end in the surface intro: Attributed writes. The app connects as the workload identity, stamps SELECT set_config('chiron.actor_oid', …, true) at transaction start, a BEFORE INSERT trigger reads the setting into the actor_oid column, and refuses the row if the setting is absent.

Verified 2026-08-08: current_setting(name, true) returns NULL when the setting is unset — that's what the guard trigger keys on to refuse anonymous writes (PostgreSQL system-info functions).

Pattern 2 — Connect-as-user (OBO / delegated)

The middle-tier app takes the user's access token and exchanges it for a downstream token whose subject is still the user. On Azure that's the OAuth 2.0 On-Behalf-Of flow at POST https://login.microsoftonline.com/<tenant>/oauth2/v2.0/token with:

grant_type          = urn:ietf:params:oauth:grant-type:jwt-bearer
client_id           = <middle-tier app registration>
client_secret       = <or client_assertion / cert>
assertion           = <user's access token>
scope               = https://ossrdbms-aad.database.windows.net/.default
requested_token_use = on_behalf_of

Verified 2026-08-08 against Microsoft Entra OAuth 2.0 On-Behalf-Of flow. The returned token has an aud claim for ossrdbms-aad and the original user's sub; use it as the psycopg password. The user must already exist as a Postgres role.

GCP: Cloud SQL IAM authentication supports IAM user + IAM group principals — the user must exist in your Google Workspace/Cloud Identity domain and have roles/cloudsql.instanceUser on the instance. The connector authenticates as that user's SA when their ADC is on the request path.

AWS: RDS IAM authentication supports one DB user per IAM principal — the IAM principal calls generate_db_auth_token(DBUsername=<per-user>) with the user's own AWS credentials in scope. In practice this only works when the caller is on the user's laptop, not a shared workload.

The pooling cost

Every one of these breaks connection pooling: a pooled connection is authenticated as one identity for its lifetime. If your app pool has 10 users and 10 pooled connections, each connection is bound to one user; you can't route "user A" queries onto a connection that was authenticated as user B.

Options — all painful:

  • Reconnect per request. Physical connect at the top of every handler, disconnect at the bottom. Latency doubles for read-heavy workloads; connection storm risk under load.
  • Per-user pool. One pool per active user. Memory grows with concurrent users; connection cap on the DB is the ceiling.
  • Session-level SET SESSION AUTHORIZATION. Requires the workload role to have SUPERUSER-adjacent privilege, defeats the point (workload can impersonate any user).

Because of this cost, connect-as-user only makes sense when:

  • The user set is small (tens, not thousands).
  • Every user is a real named human on your IdP.
  • You want the DB — not just app code — to enforce per-user grants (GRANT SELECT ON <table> TO alice).
  • You're OK giving up connection pooling for the workloads that use it.

Everywhere else, audit column + RLS is the answer.

Pattern 3 — Row-level security (RLS)

Same workload identity as audit column, same set_config('chiron.actor_oid', …, true) at the top of every txn — but now Postgres itself filters rows by policy. The app can't accidentally return other users' rows because there's no way to write a query that would.

Deep dive with policies and enforcement details: RLS with the audit column.

What all three share

All three land the verified end-user oid/sub from TA.2 into the row. All three connect via the passwordless flow from TA.1. The differences are entirely about who the DB sees and how enforcement is spelled:

  • Audit column: app enforces via trigger.
  • Connect-as-user: DB enforces via GRANT (and current_user).
  • RLS: DB enforces via USING predicates.

Compose freely: audit column + RLS is the sweet spot for most multi-tenant workloads. Connect-as-user is a specialty tool for the small set of workloads that genuinely benefit from a per-user DB principal.

Docs verified 2026-08-09

Where the arc goes next

  • RLS with the audit column — the concrete policy DDL + FORCE ROW LEVEL SECURITY + how policies read the same chiron.actor_oid setting.
  • Later — cross-account / cross-tenant federation, composed with the pattern you picked here.