Postgres RLS — optional, not required

TL;DR

RLS is not needed for the current architecture. Tenant isolation is enforced at the application layer: every query against a scoped table (see notes/scopes.md) is routed through the scope adapter which ANDs scope_id = <current scope> into every read and stamps it on every write. Only the worker talks to the DB; clients authenticate to the worker via WorkOS JWT, never to Postgres directly. One DB role (postgres) owns every row. RLS would be a second lock on a door that's already locked.

The only reason to turn RLS on is defense-in-depth — a belt against a future bug where a drizzle query forgets to filter by scope_id. Without RLS, such a query silently returns cross-tenant rows. With RLS, it returns zero rows.

When RLS matters

RLS earns its weight when untrusted clients connect directly to Postgres with per-user credentials (the Supabase / PostgREST model). Not our shape.

Consider turning it on if any of these change:

• Clients start hitting Postgres directly (JWT-authed session, row-level policies as the only authorisation layer).
• We run multiple DB roles that need row-level isolation between them.
• We decide the cost of a missed scope_id filter in app code is high enough to want a DB-level backstop.

How it would work with scope merging

Scope merging (ScopeStack in notes/scopes.md) means reads fan out across multiple scopes while writes target exactly one. RLS policies mirror that asymmetry by reading two settings per request:

CREATE POLICY scope_read ON source FOR SELECT
  USING (scope_id = ANY(current_setting('app.scope_chain', true)::text[]));

CREATE POLICY scope_write ON source FOR INSERT
  WITH CHECK (scope_id = current_setting('app.write_scope', true));

CREATE POLICY scope_update ON source FOR UPDATE
  USING (scope_id = current_setting('app.write_scope', true))
  WITH CHECK (scope_id = current_setting('app.write_scope', true));

ALTER TABLE source ENABLE ROW LEVEL SECURITY;

And app-side, once per request inside a transaction (so SET LOCAL takes):

yield * sql`SET LOCAL app.scope_chain = ${toPgArray(chain)}`;
yield * sql`SET LOCAL app.write_scope = ${writeScope}`;

current_setting('…', true) returns null when the setting is missing, which is what you want — a connection that forgets to configure the scope sees zero rows, not an error that might be handled into a 500.

Rollout sketch (if we ever decide to enable it)

1. Add a migration that ENABLE ROW LEVEL SECURITY on every scoped table and installs the read/write policies above. The list of scoped tables is whatever executor-schema.ts exports with a scope_id column — same set the scope adapter already knows about.
2. Add an Effect layer that wraps the DbService to issue the two SET LOCALs at connection acquire. postgres.js's sql.begin already gives us the transaction boundary.
3. Leave BYPASSRLS off the worker's role so we actually get the enforcement; grant it on the role used by the migration / admin scripts.

Ballpark effort: a day including the migration + the Effect layer + tests. Worth doing only if we hit a scope-leak bug in prod or change the access model.