Structuring Postgres RLS for multi-tenant SaaS

RLS is ideal for multi-tenant SaaS where every table (or most) is scoped to an organization. It protects against bugs, SQL injection that omits the tenant filter, and insider access via direct DB connections. It is not a substitute for application-level authorization—use both. RLS handles "which tenant's data"; your app handles "which role can do what."

Alternatives exist: application-level filtering (add WHERE org_id = ? to every query), database-per-tenant (separate DB per customer), or schema-per-tenant. RLS sits between: one database, one schema, but row-level isolation. It's the right fit when you have many tenants, shared infrastructure, and a need for defense in depth.

Before any tenant-scoped query, set the current tenant in the session. Supabase does this via JWT claims ( auth.jwt() ->> 'app_metadata' ->> 'org_id'); with raw Postgres you use SET LOCAL app.current_tenant_id = 'uuid'. The value must come from the authenticated session, never from client input. If the setting is missing or invalid, policies will match no rows—fail closed.

-- Supabase: org_id in JWT app_metadata, set automatically
-- Raw Postgres / connection pool: set at request start
SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';

-- Optional: also set role for permission checks
SET LOCAL app.current_role = 'admin';

With connection pooling (e.g. PgBouncer in transaction mode), SET LOCAL is session-scoped and resets when the transaction ends. Set it at the start of every request. With Supabase, the client passes the JWT and RLS reads from it automatically.

Every tenant-scoped table needs a policy for SELECT, INSERT, UPDATE, and DELETE. The pattern: tenant_id = current_setting('app.current_tenant_id')::uuid. For INSERT, also set tenant_id via a trigger or default so users cannot override it.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Single policy for all operations (simplest)
CREATE POLICY tenant_isolation ON projects
  FOR ALL
  USING (org_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (org_id = current_setting('app.current_tenant_id')::uuid);

-- Or separate policies for finer control
CREATE POLICY tenant_select ON projects FOR SELECT
  USING (org_id = current_setting('app.current_tenant_id')::uuid);
CREATE POLICY tenant_insert ON projects FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_tenant_id')::uuid);
CREATE POLICY tenant_update ON projects FOR UPDATE
  USING (org_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (org_id = current_setting('app.current_tenant_id')::uuid);
CREATE POLICY tenant_delete ON projects FOR DELETE
  USING (org_id = current_setting('app.current_tenant_id')::uuid);

-- Trigger to enforce tenant_id on insert (prevents override)
CREATE OR REPLACE FUNCTION set_tenant_id()
RETURNS TRIGGER AS $$
BEGIN
  IF current_setting('app.current_tenant_id', true) IS NOT NULL THEN
    NEW.org_id := current_setting('app.current_tenant_id')::uuid;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER enforce_tenant_on_insert BEFORE INSERT ON projects
  FOR EACH ROW EXECUTE FUNCTION set_tenant_id();

Tables without a tenant column (e.g. global config, plans) either have no RLS or use a different policy (e.g. read-only for all authenticated users). Joins across tenant and non-tenant tables work—RLS applies per table.

Combine tenant isolation with role checks. For example: admins can delete, members can update, viewers can only select. Store role in JWT or session and reference it in policies.

-- Members can read; admins can also delete
CREATE POLICY tenant_select ON projects FOR SELECT
  USING (org_id = current_setting('app.current_tenant_id')::uuid);

CREATE POLICY tenant_delete ON projects FOR DELETE
  USING (
    org_id = current_setting('app.current_tenant_id')::uuid
    AND current_setting('app.current_role') IN ('admin', 'owner')
  );

RLS adds a filter to every query. If your index does not lead with the tenant column, Postgres may scan more rows than necessary or use a less efficient plan. Make org_id (or tenant_id) the first column in composite indexes.

-- Good: org_id first (RLS filter uses index)
CREATE INDEX idx_projects_org_created ON projects (org_id, created_at DESC);
CREATE INDEX idx_projects_org_status ON projects (org_id, status) WHERE status = 'active';

-- Avoid: org_id not first
CREATE INDEX idx_projects_created ON projects (created_at DESC);

-- Partial indexes for common tenant queries
CREATE INDEX idx_invoices_org_unpaid ON invoices (org_id, due_date)
  WHERE status = 'unpaid';

Use EXPLAIN ANALYZE on representative queries to verify the planner uses your indexes. Watch for sequential scans on large tables.

Background jobs, migrations, and admin tools often need to bypass RLS. Use a service role key (Supabase) or a superuser connection for those paths. Never use the service role for user-facing requests. Document every bypass clearly. Consider a separate "admin" schema or connection string for internal tools.

// Supabase: service role bypasses RLS
const { data } = await supabaseAdmin
  .from('subscriptions')
  .select('*')
  .eq('org_id', orgId);

// Raw Postgres: connect as superuser or use BYPASSRLS role
// Migrations typically run as superuser

Add tenant_id to all tenant-scoped tables if missing. Backfill from your auth/org model. Enable RLS with a permissive policy first; deploy; then tighten. Test thoroughly—RLS changes can break existing queries if the context is not set.

-- Step 1: Add column, backfill
ALTER TABLE projects ADD COLUMN org_id uuid REFERENCES orgs(id);
UPDATE projects p SET org_id = (SELECT org_id FROM users WHERE id = p.owner_id);
ALTER TABLE projects ALTER COLUMN org_id SET NOT NULL;

-- Step 2: Enable RLS with permissive policy
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects FOR ALL USING (true);  -- temporary

-- Step 3: Deploy, verify app sets context
-- Step 4: Replace with restrictive policy
DROP POLICY tenant_isolation ON projects;
CREATE POLICY tenant_isolation ON projects FOR ALL
  USING (org_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (org_id = current_setting('app.current_tenant_id')::uuid);

Write tests that switch tenant context and verify that queries return only the correct tenant's data. Attempt cross-tenant access (e.g. by forging a different org_id in the session) and assert it fails. Test with the anon/authenticated role, not the service role. RLS bugs are subtle; automated tests catch them before production.

// Jest: test RLS isolation
test('cannot access other tenant data', async () => {
  await setTenantContext(tenantA.id);
  const projects = await db.projects.findMany();
  expect(projects.every(p => p.org_id === tenantA.id)).toBe(true);

  await setTenantContext(tenantB.id);
  const projectB = await db.projects.findFirst({ where: { id: projectFromA.id } });
  expect(projectB).toBeNull();
});

When tables reference each other (e.g. projects → tasks), both need RLS. A join returns only rows that pass both tables' policies. If projects and tasks both have org_id, and both policies check current_setting, a query joining them will only return matching pairs. No special handling needed—RLS applies to each table independently.

For tables that derive tenant from a parent (e.g. tasks.org_id from projects.org_id), you can either store org_id on the child and enforce it, or use a subquery in the policy: USING (project_id IN (SELECT id FROM projects WHERE org_id = current_setting(...))). Storing org_id on the child is simpler and faster for large tables.

Forgetting to set context: queries return empty. Set it at the start of every request. Using the wrong role: service role bypasses RLS; use it only for jobs and migrations. Policy too permissive:USING (true) disables RLS for that operation. Missing WITH CHECK on INSERT/UPDATE: users can insert rows with another tenant's ID if USING allows it. Always include WITH CHECK for writes.

current_setting returns NULL if unset. Policies with = NULL::uuid match nothing. Use the two-argument form current_setting('app.current_tenant_id', true) to get NULL instead of error when missing; then handle NULL explicitly in your trigger.

RLS adds a filter to every query. With proper indexes (tenant column first), overhead is minimal. For very high QPS, consider connection pooling with session mode so SET LOCAL persists across requests in the same connection—but transaction mode is simpler and sufficient for most apps.

Avoid policies that call expensive functions or subqueries. Keep the USING clause simple: equality on tenant_id and maybe a role check. Complex logic belongs in the application or in a security definer function that you call from the policy.

Supabase sets request.jwt.claims automatically from the JWT. Your RLS policies reference auth.jwt() ->> 'app_metadata' ->> 'org_id'. Ensure your auth flow (Supabase Auth, custom JWT, etc.) includes org_id in the token. With Prisma, use a raw query or middleware to run SET LOCAL before each request when using a connection pool.

Prisma doesn't natively support RLS context—it sends queries as the database user. Use $queryRaw with SET LOCAL in the same connection, or use Prisma's middleware to run a setup query before each operation. Alternatively, use Supabase client for RLS-backed tables and Prisma for service-role operations.

RLS doesn't log who accessed what. For audit trails, add triggers or application-level logging. Postgres has pg_audit and similar extensions; you can also log at the app layer. RLS ensures isolation; auditing ensures you can prove it afterward. For SOC 2 or similar, document your RLS policies and test them.