RLS verification

The CRM relies on Postgres Row-Level Security (RLS) for tenant isolation. The subdomain is UX scoping; RLS is the actual security boundary. A buggy query asking for someone else's tenant returns zero rows from the database, regardless of what the application code does.

This article documents the checklist used to verify that, and gives you the SQL to re-run it after any schema or policy change.

How it works in one paragraph

Every tenant-scoped table has RLS enabled and a SELECT policy gated by a SECURITY DEFINER function is_tenant_member(tenant_id). The function checks tenant_members for a row matching the request's auth.uid() and the target tenant. If there's no membership row, the policy returns false and Postgres filters the row out before the query result is built. The user-facing app uses the Supabase anon key plus the user's JWT, so RLS does the work. The service-role key bypasses RLS and is only used from trusted server-side paths (lead intake, invite acceptance, provisioning scripts).

See supabase/migrations/0001_initial_schema.sql for the policy definitions.

When to run this verification

After any schema change (new table, altered policy, dropped column).
After upgrading Supabase or Postgres.
Periodically, just to be sure (quarterly is fine).
Before onboarding a new client whose data must stay isolated.

The checklist

You need two tenants and at least one member in each. The example below assumes acme and beta. Substitute your slugs if different.

1. Provision two tenants

npm run provision-tenant -- alpha "Alpha Co" alpha-admin@example.com
npm run provision-tenant -- beta  "Beta Co"  beta-admin@example.com

2. Seed a member in each via the dev script

npm run seed-tenant-member -- alpha alpha-admin@example.com alpha-pwd-12345 admin
npm run seed-tenant-member -- beta  beta-admin@example.com  beta-pwd-12345  admin

3. Seed a lead in each (via the intake API or the UI)

curl -X POST http://alpha.localtest.me:3001/api/leads/intake \
  -H "X-API-Key: <alpha-key>" -H "Content-Type: application/json" \
  -d '{"first_name":"Alpha","email":"a@example.com","source":"rls-test"}'

curl -X POST http://beta.localtest.me:3001/api/leads/intake \
  -H "X-API-Key: <beta-key>" -H "Content-Type: application/json" \
  -d '{"first_name":"Beta","email":"b@example.com","source":"rls-test"}'

4. Run the cross-tenant query block

Open a psql session inside the Postgres container (or use Studio's SQL editor) and run, substituting the alpha user id and the beta tenant id:

SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" TO '{"sub": "<alpha-user-id>", "role": "authenticated"}';

SELECT 'Alpha leads visible to Alpha user' AS check, count(*)
  FROM leads
  WHERE tenant_id = (SELECT id FROM tenants WHERE slug = 'alpha')
UNION ALL
SELECT 'Beta leads visible to Alpha user (must be 0)', count(*)
  FROM leads
  WHERE tenant_id = (SELECT id FROM tenants WHERE slug = 'beta')
UNION ALL
SELECT 'Beta tasks visible to Alpha user (must be 0)', count(*)
  FROM tasks
  WHERE tenant_id = (SELECT id FROM tenants WHERE slug = 'beta')
UNION ALL
SELECT 'Beta pipeline_stages visible to Alpha user (must be 0)', count(*)
  FROM pipeline_stages
  WHERE tenant_id = (SELECT id FROM tenants WHERE slug = 'beta')
UNION ALL
SELECT 'Beta tenant row visible to Alpha user (must be 0)', count(*)
  FROM tenants
  WHERE slug = 'beta'
UNION ALL
SELECT 'Beta tenant_members visible to Alpha user (must be 0)', count(*)
  FROM tenant_members
  WHERE tenant_id = (SELECT id FROM tenants WHERE slug = 'beta')
UNION ALL
SELECT 'Beta api_keys visible to Alpha user (must be 0)', count(*)
  FROM api_keys
  WHERE tenant_id = (SELECT id FROM tenants WHERE slug = 'beta');

5. Read the result

The first row (Alpha leads visible to Alpha user) should be >= 1. Every other row must be 0. If anything is non-zero, you have a policy regression.

A passing run looks like:

                         check                         | count
-------------------------------------------------------+-------
 Alpha leads visible to Alpha user                     |     3
 Beta leads visible to Alpha user (must be 0)          |     0
 Beta tasks visible to Alpha user (must be 0)          |     0
 Beta pipeline_stages visible to Alpha user (must be 0)|     0
 Beta tenant row visible to Alpha user (must be 0)     |     0
 Beta tenant_members visible to Alpha user (must be 0) |     0
 Beta api_keys visible to Alpha user (must be 0)       |     0

6. Also check the realtime publication

SELECT pubname, schemaname, tablename FROM pg_publication_tables
WHERE pubname = 'supabase_realtime';

You should see leads, lead_activities, lead_notes, and tasks. Realtime broadcasts respect RLS, so an Alpha client subscribed to tenant:<alpha-id> won't receive any Beta changes.

What to do if a check fails

Don't ship. A non-zero result means cross-tenant data is reachable.

Verify the policy exists:

SELECT * FROM pg_policies WHERE tablename = '<offending-table>';

Verify the policy text matches the canonical pattern in 0001_initial_schema.sql:
```
USING (is_tenant_member(tenant_id))
```
Verify the is_tenant_member() function is SECURITY DEFINER and has a locked-down search_path:
```
SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'is_tenant_member';
```
If you added a new tenant-scoped table, did you ENABLE ROW LEVEL SECURITY on it and write the four CRUD policies?
Service-role usage in app code can mask RLS issues. Run the check above against the anon role with a real JWT, not the service-role key.

Automated tests, v2

v1 verification is manual. v2 will add pgTAP tests living next to supabase/migrations/ so this checklist runs in CI on every migration. Until then, run the checklist after any policy or schema change.