effect-sql-rlslisted
Install: claude install-skill vantionlabs/saas-starter
# Tenant data, and the two things that keep it apart
Every tenant-owned table is protected **twice**: a row-level security policy in Postgres,
and `withOrgScope` around the query. Neither is trusted alone. Unit tests cover each; only
`e2e/tests/tenancy.spec.ts` shows two real users, session to SQL, failing to see each
other's rows.
## The connection is the boundary
`packages/database/src/roles/init.sql` makes two roles:
- **`vantion`** — what every application process connects as, and `NOBYPASSRLS`. A handler
that forgets `withOrgScope` therefore reads **nothing** rather than reading everybody.
- **`admin`** — holds `BYPASSRLS`, for the cross-tenant surface only. Which process may
cross tenants is decided by Postgres, not by a reviewer.
This was not true until recently. `docker compose` set `POSTGRES_USER: vantion`, making it
the bootstrap **superuser** — so every policy in this schema was inert while
`pg_class.relrowsecurity` read true. `packages/database/test/Role.test.ts` asserts the
connection cannot bypass, because a suite pointed at a superuser passes every tenancy test
while proving nothing.
Switching the role found two production bugs, both the same shape: a worker escape on
`using` but not on `with check`, and an UPDATE is checked by both.
## Which scope
| Function | Who uses it | What it sets |
| --------------------------- | ------------------------------------------------