Auth, Authorization & Multi-Tenancy's own domain taught three isolation patterns — separate databases, separate schemas, shared schema with careful discipline — and named RLS as the stronger version of the third. This closing lesson is that promise, made fully concrete: the actual policies for a real multi-tenant app, end to end.
4 min read
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL
);
CREATE TABLE memberships (
user_id UUID REFERENCES auth.users(id),
organization_id UUID REFERENCES organizations(id),
role TEXT CHECK (role IN ('member', 'admin')),
PRIMARY KEY (user_id, organization_id)
);
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID REFERENCES organizations(id),
title TEXT NOT NULL,
owner_id UUID REFERENCES auth.users(id)
);This is exactly Auth & Multi-Tenancy's own "shared schema with an organization_id column" pattern — the schema alone is identical to what that domain already taught. What changes here is how the isolation is enforced: not application code remembering to filter every query, but RLS policies making it structurally impossible to forget.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
CREATE POLICY "users see documents in their own organizations"
ON documents FOR SELECT
USING (
organization_id IN (
SELECT organization_id FROM memberships WHERE user_id = auth.uid()
)
);This single policy replaces every hand-written WHERE organization_id = ? clause Auth & Multi-Tenancy's own domain showed scattered across application code — this exact check now runs for every query, from every code path, automatically, with the membership lookup itself happening inside the policy.
CREATE POLICY "only admins can delete documents in their org"
ON documents FOR DELETE
USING (
organization_id IN (
SELECT organization_id FROM memberships
WHERE user_id = auth.uid() AND role = 'admin'
)
);This demonstrates lesson 2's per-command policy granularity concretely: the same table's SELECT policy (any member can read) and DELETE policy (only an admin can delete) express genuinely different rules, each enforced independently — exactly the "different rules for different operations" case lesson 2 introduced abstractly, now solving a real, specific authorization requirement.
The documents policies above query the memberships table from inside a policy — this works correctly because memberships has its own, separate RLS policy (a user can see their own membership rows) that doesn't itself reference documents, so there's no circular dependency. Designing multi-table RLS requires this explicit discipline: each table's policies should be checkable independently, without needing another table's policy evaluation to somehow depend back on the first table again.
supabase start
# Insert two orgs, two users, memberships for each, and documents for each org
# Log in AS each user via the Auth API locally, and confirm:
# - User A sees only Org A's documents, never Org B's
# - A member (non-admin) can read but gets denied on delete
# - An admin can delete documents within their OWN org onlyThis is Auth & Multi-Tenancy's own tenant-isolation testing lesson, made concrete for RLS specifically — the same test intent (prove a user from one tenant genuinely cannot reach another tenant's data), run locally against a real Postgres instance with real policies, rather than mocking an application-layer filter that the test itself might give too much credit for correctness it doesn't actually have.
Every lesson in this domain — RLS's mechanics, how Auth plugs into it via auth.uid(), how the generated API and Realtime both respect it automatically, how Storage reuses the identical mechanism — converges on exactly this capstone: a real multi-tenant application's entire access-control surface, expressed as a small number of declarative SQL policies, enforced identically regardless of which part of the stack (the REST API, a Realtime subscription, an Edge Function, a direct SQL connection) touches the data. This is the concrete version of the structural-enforcement argument lesson 2 opened with — not a claim, but a fully worked example.
Check your understanding
A quick comprehension check — not tracked, not graded, just for you.
1. What does the capstone's single SELECT policy on documents replace?
2. Why can the documents table's policy safely query the memberships table without creating a circular dependency?
3. What does this capstone lesson argue is the actual conclusion of the whole Supabase domain?
Supabase