Why Database-Level Isolation Beats Application Filters
Multi-tenant SaaS applications must prevent users from seeing or modifying another organization's data. Implementing these checks only in Next.js route handlers or server actions creates risk. A single missing where clause, a cache bug, or a direct Supabase client call from the browser can expose sensitive records.
Row Level Security (RLS) in PostgreSQL, available natively in Supabase, evaluates access rules inside the database engine on every query. The database returns only authorized rows or rejects the operation entirely. This guarantee holds even if application code is bypassed through API abuse, compromised service keys, or future refactoring mistakes.
Supabase makes RLS practical by providing a dashboard for policy management, integration with its Auth system via auth.uid(), and excellent TypeScript support. This article demonstrates a complete, production-ready pattern using organizations as the tenant boundary.
Recommended Database Schema for Organization-Based Tenancy
A robust multi-tenant schema separates users from organizations through a membership table. Every business object then references the organization.
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE organization_members (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
role TEXT NOT NULL CHECK (role IN ('owner', 'member')) DEFAULT 'member',
created_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE(organization_id, user_id)
);
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
title TEXT NOT NULL,
description TEXT,
status TEXT DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done')),
assigned_to UUID REFERENCES auth.users(id),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);After creating tables, enable RLS immediately:
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;The ON DELETE CASCADE clauses keep data clean when organizations are removed. Add similar tables for invoices, projects, or documents using the same organization_id foreign key pattern.
Writing Precise RLS Policies
Policies are created with the CREATE POLICY command. Use FOR SELECT, FOR INSERT, or FOR ALL. The USING expression filters readable rows. The WITH CHECK expression validates rows being written.
Start with membership visibility:
CREATE POLICY "Users see their own memberships"
ON organization_members FOR SELECT
USING (user_id = auth.uid());Organization visibility depends on membership:
CREATE POLICY "Members can view their organizations"
ON organizations FOR SELECT
USING (
EXISTS (
SELECT 1 FROM organization_members
WHERE organization_members.organization_id = organizations.id
AND organization_members.user_id = auth.uid()
)
);For tasks, apply the same membership check for all operations:
CREATE POLICY "Organization members can manage tasks"
ON tasks FOR ALL
USING (
EXISTS (
SELECT 1 FROM organization_members
WHERE organization_members.organization_id = tasks.organization_id
AND organization_members.user_id = auth.uid()
)
)
WITH CHECK (
EXISTS (
SELECT 1 FROM organization_members
WHERE organization_members.organization_id = tasks.organization_id
AND organization_members.user_id = auth.uid()
)
);The FOR ALL policy covers SELECT, INSERT, UPDATE, and DELETE. Repeating the EXISTS subquery in both USING and WITH CHECK prevents users from creating tasks in organizations they do not belong to.
Next.js App Router Integration with @supabase/ssr
Version 0.5+ of @supabase/ssr handles cookie-based auth correctly across Server Components, Route Handlers, and middleware. Install the packages:
npm install @supabase/supabase-js@2.45.4 @supabase/ssr@0.5.2Create a reusable client factory in lib/supabase.ts:
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';
export function createSupabaseServerClient() {
const cookieStore = cookies();
return createServerClient(
process.env.NEXT_PUBLIC_SUPABASE_URL!,
process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
{
cookies: {
getAll() {
return cookieStore.getAll();
},
setAll(cookiesToSet) {
try {
cookiesToSet.forEach(({ name, value, options }) => {
cookieStore.set(name, value, options);
});
} catch (error) {
// Ignore cookie setting errors in Server Components
}
},
},
}
);
}This client automatically attaches the user's JWT, allowing RLS to evaluate auth.uid() correctly.
Fetching Data in Server Components
With RLS active, queries become simpler because the database does the filtering:
import { createSupabaseServerClient } from '@/lib/supabase';
export default async function Dashboard() {
const supabase = createSupabaseServerClient();
const { data: organizations } = await supabase
.from('organizations')
.select('id, name');
const { data: tasks } = await supabase
.from('tasks')
.select(`
id,
title,
status,
organizations (name)
`)
.order('created_at', { ascending: false });
return (
<div>
<h2>Your Organizations</h2>
<ul>
{organizations?.map((org) => (
<li key={org.id}>{org.name}</li>
))}
</ul>
<h2>Recent Tasks</h2>
<ul>
{tasks?.map((task) => (
<li key={task.id}>
{task.title} — {task.organizations?.name}
</li>
))}
</ul>
</div>
);
}No manual eq('organization_id', currentOrgId) filters are required. A user belonging to three organizations will see only tasks from those three, enforced by the database.
Protected Server Actions and Mutations
Server Actions inherit the same authenticated client:
'use server';
import { createSupabaseServerClient } from '@/lib/supabase';
import { revalidatePath } from 'next/cache';
export async function createTask(formData: FormData) {
const supabase = createSupabaseServerClient();
const title = formData.get('title') as string;
const organizationId = formData.get('organization_id') as string;
const { error } = await supabase
.from('tasks')
.insert({
title,
organization_id: organizationId,
status: 'todo'
});
if (error) {
if (error.code === '42501') {
throw new Error('You do not have permission to create tasks in this organization');
}
throw error;
}
revalidatePath('/dashboard');
}If the RLS policy rejects the insert, Supabase returns PostgreSQL error code 42501 (insufficient_privilege). Handle this explicitly for good UX.
Authentication Middleware and Protected Routes
Protect dashboard routes with middleware that verifies the Supabase session:
import { createMiddlewareClient } from '@supabase/ssr';
import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/server';
export async function middleware(request: NextRequest) {
const res = NextResponse.next();
const supabase = createMiddlewareClient({ req: request, res });
const { data: { session }} = await supabase.auth.getSession();
if (!session && request.nextUrl.pathname.startsWith('/dashboard')) {
return NextResponse.redirect(new URL('/login', request.url));
}
return res;
}
export const config = {
matcher: ['/dashboard/:path*'],
};After login, Supabase stores the session in an httpOnly cookie that middleware and Server Components can read.
Common RLS Anti-Patterns and Fixes
Avoid creating a single broad policy USING (true) for development convenience and forgetting to replace it. Always start with the strictest policy.
Do not rely on a client-side current_organization_id stored in localStorage. Malicious users can change it. Policies must validate membership every time using the EXISTS subquery shown earlier.
When users belong to multiple organizations, avoid simple user_id = auth.uid() policies on business tables. This pattern only works for single-tenant user-owned data, not organization-scoped resources.
For role-specific actions like deleting any task in an organization, add a role check inside the policy or use a security definer function.
Performance, Indexing, and Scaling
RLS policies run for every row considered by a query. Create indexes on columns used in policy expressions:
CREATE INDEX CONCURRENTLY idx_org_members_user
ON organization_members(user_id);
CREATE INDEX CONCURRENTLY idx_tasks_org_id
ON tasks(organization_id);
CREATE INDEX CONCURRENTLY idx_org_members_org_user
ON organization_members(organization_id, user_id);These indexes prevent sequential scans when the EXISTS subquery executes. On tables larger than 100,000 rows, monitor query performance in the Supabase dashboard under Database > Query Performance.
Complex policies with multiple joins can add 2-8ms of latency per query. For high-throughput endpoints, consider caching frequent reads in Redis while still using RLS for authoritative writes.
Using Security Definer Functions for Complex Rules
When policy expressions grow beyond simple EXISTS checks, move logic into a function marked SECURITY DEFINER:
CREATE OR REPLACE FUNCTION member_can_access_org(org_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (
SELECT 1 FROM organization_members
WHERE organization_id = org_id
AND user_id = auth.uid()
AND role IN ('owner', 'member')
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;Then simplify the policy to:
CREATE POLICY "Access via membership function"
ON tasks FOR ALL
USING (member_can_access_org(organization_id))
WITH CHECK (member_can_access_org(organization_id));Because the function runs with the table owner's privileges, it can bypass RLS on the membership table if needed, but keep the function code minimal and auditable.
Debugging Policies Effectively
Use the Supabase SQL editor with the following to simulate an authenticated request:
SET request.jwt.claims TO '{"sub": "user-uuid-here"}';
Then run your SELECT statements to see what RLS returns. Enable the pg_audit extension on paid plans for detailed policy evaluation logs.
Common errors include policies referencing columns that do not exist, missing ENABLE ROW LEVEL SECURITY, or using auth.uid() when the user is authenticated through a service key (which has no uid).
Trade-offs: RLS vs Pure Application Authorization
Advantages of RLS:
- Impossible for developers to accidentally omit tenant filters
- Enforced even when using the Supabase JS client directly from browser with a user token
- Consistent security model across Next.js, mobile apps, and future desktop clients
- Compliance friendly because data access is controlled at the data layer
Disadvantages of RLS:
- Policy logic lives in SQL, requiring different debugging skills than TypeScript
- Performance cost on very large tables without proper indexes
- Complex business rules (date-based access, feature flags) can become unwieldy
- Harder to unit test than plain JavaScript authorization functions
Most mature teams combine both: RLS provides hard tenant isolation while application code enforces UI permissions, soft deletes, and workflow rules. For example, RLS ensures users only see their organization's tasks, while the Next.js UI decides which buttons appear based on the user's role fetched from the membership record.
Complete Worked Example: Team Task Manager SaaS
Consider a task management product where small businesses create organizations and invite teammates.
1. On signup, create an organization and add the user as owner using a database trigger or server action.
2. Implement an invite flow that creates a new auth user and adds a row to organization_members.
3. All task CRUD uses the policies shown above. A user attempting to create a task with another organization's ID receives a permission error.
4. Add a comments table with the same organization_id and identical membership policy. Comments automatically become isolated without additional application code.
5. For analytics dashboards, create a view that aggregates only within the user's organizations and apply an RLS policy on the view.
This pattern scales to hundreds of organizations with thousands of tasks each when proper indexes exist. The same structure works for customer portals, internal tools, or B2B platforms.
Managing Policies in Production with Migrations
Never edit policies through the Supabase dashboard in production. Use the Supabase CLI to create version-controlled migrations:
npx supabase migration new add_task_policies
Place the CREATE POLICY statements inside the generated SQL file and deploy with:
npx supabase db push
This keeps your RLS configuration synchronized with your Next.js codebase and enables rollbacks through previous migrations.
Review policies regularly during security audits. A single overly permissive policy can undermine the entire isolation model.
By combining these concrete policies, indexing strategy, and Supabase SSR integration, you gain strong data isolation guarantees while keeping your Next.js application code clean and focused on business logic. The database becomes the ultimate source of truth for tenant boundaries.