Security & Multi-tenant Isolation

Race Platform stores every customer's data in a single shared Postgres database. This page explains how a query for Account A can never return rows for Account B, even if a route handler forgets its WHERE account_id = … clause.

What you can rely on today

Postgres Row Level Security (RLS) policies are enabled on every account-scoped table. The policy compares the row's account_id against a per-request session GUC named app.account_id and returns zero rows when they don't match.
The API auth middleware sets app.account_id to auth.accountId via SET LOCAL inside a per-request transaction. Routes don't have to know this — the global db export is a Proxy that delegates to the request-scoped tx via AsyncLocalStorage.
Admin / system queries (migrations, seed scripts, the API-key resolver that runs before auth resolves) connect as a separate Postgres role race_admin with the BYPASSRLS attribute. Routes must never reach for sysDb() — it would silently leak across tenants.
The webhook_deliveries table has no account_id column of its own; its policy joins through webhooks.account_id so a tenant only sees the delivery rows for their own webhooks.

The list of protected tables (one ALL-policy each) is in apps/api/src/db/migrations/0007_add_row_level_security.sql. As of v0.4.0 this covers 38 tables — every account-scoped table in the schema plus accounts itself (matched on id) plus webhook_deliveries (matched via parent).

How the GUC gets there

// apps/api/src/middleware/auth.ts
async function runRequestInTransaction(accountId, next) {
  await db.transaction(async (tx) => {
    await tx.execute(
      sql`SELECT set_config('app.account_id', ${accountId}, true)`
    );
    await runWithTransaction(tx, next);
  });
}

set_config(name, value, is_local=true) is the parameterised form of SET LOCAL. The true for is_local resets the value at COMMIT/ ROLLBACK, so the GUC can never leak to another request even when the underlying connection is recycled by the pool.

runWithTransaction stashes the tx in a Node AsyncLocalStorage context for the duration of next(). Inside the storage context, the global db Proxy from apps/api/src/db/index.ts resolves every property access through the tx instead of the root drizzle instance — so db.select().from(...) inside a route handler runs on the same connection, in the same transaction, as the SET LOCAL.

Public routes (/health, /auth/login, /auth/register) skip the middleware entirely. Their queries either don't touch account-scoped tables (/health) or run before any account context exists, in which case they hit sysDb() (which uses race_admin, BYPASSRLS).

Verification

# Direct SQL — proves policy filtering is real, independent of the API.
docker exec race-postgres psql -U race_test -d race \
  -c "SELECT count(*) FROM laps;"
# → 0 (no GUC set)

docker exec race-postgres psql -U race_test -d race -c "
  BEGIN;
  SET LOCAL app.account_id = 'AAAA-...';
  SELECT count(*) FROM laps;
  COMMIT;
"
# → only account A's count

The integration test at apps/api/src/__tests__/rls.test.ts codifies this against two real test accounts.

Production hardening (next-step work)

In dev, the application connects as the race Postgres role, which the docker-compose stack creates as SUPERUSER. Postgres superusers always bypass RLS regardless of policy state. This means that the policies are in place but not actually enforcing isolation for the running API in dev — the safety net activates once the role is demoted.

To enforce in production:

-- Demote race so RLS actually filters its queries.
ALTER ROLE race NOSUPERUSER NOBYPASSRLS;

…and update both make migrate and any boot-time seed step to use race_admin instead. The migration runner needs SUPERUSER-level privileges (CREATE TABLE, CREATE ROLE, ALTER TABLE … FORCE ROW LEVEL SECURITY) which the demoted race role can't perform.

Concretely:

# Set on the migration / seed pod, NOT on the API pod:
DATABASE_URL_ADMIN=postgres://race_admin:${RACE_ADMIN_PASS}@db:5432/race

The API pod keeps its regular DATABASE_URL pointing at race. The auth middleware's transaction-with-GUC pattern then begins enforcing isolation for every request.

Test role

Migration 0007 also creates a third role race_test with the NOSUPERUSER NOBYPASSRLS attributes — RLS-active by construction. The integration test connects as this role to prove the policies filter correctly, regardless of whether race has been demoted yet in the local environment.

What's not covered

users has no account_id column (it's a global identity table). RLS would need a JOIN-through-memberships policy. For now, application-level filtering is the only guard. Plan: add a policy using EXISTS (SELECT 1 FROM memberships WHERE user_id = users.id AND account_id = current_account_id()) once the auth/register flow has been audited for cross-tenant edge cases.
The application currently still connects as race (SUPERUSER in dev). See "Production hardening" above.
Plugin runtime data that lives off-table (in object storage or KV) isn't gated by RLS — the storage adapter checks accountId on the path prefix. That's a separate review item tracked in .context/progress.md.

What you can rely on today​

How the GUC gets there​

Verification​

Production hardening (next-step work)​

Test role​

What's not covered​

See also​