Clerk + Supabase RLS: Tenant Isolation

A user can sign in successfully and still see or change another tenant's data. Authentication proves who the user is; it does not tell Postgres which organization rows that user may access.

The risky shortcut is to trust an organization ID sent by the browser. A caller can change that value. Another common mistake is to use auth.uid(), which represents a Supabase Auth user UUID rather than Clerk's string user ID. Membership lookups inside RLS policies can also become recursive and slow.

This tutorial shows how to make the verified Clerk session token the root of the authorization decision:

• Clerk authenticates the user and supplies the active organization context.
• Supabase verifies the Clerk token and makes its claims available to Postgres.
• Postgres derives the user and tenant from those claims.
• Row-Level Security applies indexed, non-recursive policies to every query.

By the end, you will have a reusable schema, JWT helper functions, a controlled tenant-bootstrap function, explicit read/write policies, and tests for personal accounts, organizations, role boundaries, and cross-tenant attacks. The integration uses Clerk and Supabase's native third-party authentication—without a Clerk JWT template, a shared Supabase JWT secret, or auth.uid().

The complete runnable implementation is in the supaflow-labs/clerk-supabase-demo repository. The snippets below are intentionally small enough to study; use the repository migration and tests when building the complete example.

What you will build

The browser supplies proof of identity, not an authoritative tenant ID. The verified claims move through the system like this:

+-----------------------------+
| Browser                     |
| session.getToken()          |
+--------------+--------------+
               | Clerk session token
               v
+--------------+--------------+
| Supabase Data API           |
| verifies the Clerk token    |
+--------------+--------------+
               | verified JWT
               v
+--------------+--------------+
| Postgres auth.jwt()         |
| reads sub, o.id, and o.rol  |
+--------------+--------------+
               |
               v
+--------------+--------------+
| Claim helpers + bootstrap   |
+--------------+--------------+
               |
               v
+--------------+--------------+
| Access closures + RLS       |
+--------------+--------------+
               |
               v
+--------------+--------------+
| Tenant rows are returned    |
| or rejected                 |
+-----------------------------+

Postgres—not the browser—turns those claims into a tenant boundary. The following sections build each part of this flow and then test the boundary from both sides.

Required reading before you implement this

Read these official guides in order before copying the schema or policies. Together they define the authentication, claim, authorization, and performance assumptions used throughout this tutorial.

1. Integrate Supabase with Clerk — understand the native third-party authentication flow, the Supabase accessToken callback, and why the older Supabase JWT template is not used here.
2. Configure Clerk as a Supabase third-party authentication provider — understand which system verifies the Clerk token before its claims reach Postgres.
3. Clerk session tokens — study sub and the compact o organization claim. In a version 2 token, o.rol contains the active organization role without the org: prefix.
4. Clerk organization roles and permissions — understand Clerk's default admin/member roles and decide how those roles should map to database operations.
5. Supabase Row Level Security — understand TO authenticated, USING, WITH CHECK, and why every table exposed through the Data API needs RLS.
6. Supabase RLS performance and best practices — understand policy indexes, (select ...) init plans, explicit query filters, and carefully scoped SECURITY DEFINER helpers before adopting the closure pattern below.

If any of those concepts are unfamiliar, pause here and read the linked guide first. The SQL below is easier to audit when the trust boundary and PostgreSQL policy semantics are already clear.

The current integration path

Supabase supports Clerk as a native third-party authentication provider. Configure Clerk's Supabase integration in the Clerk Dashboard, then add the Clerk issuer/domain as a third-party auth provider in Supabase.

The application client passes the normal Clerk session token through Supabase's accessToken callback:

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY!,
  {
    accessToken: async () => session?.getToken() ?? null,
  },
)

There is no getToken({ template: 'supabase' }) call in this integration.

See the official Clerk Supabase guide and Supabase third-party Clerk guide.

Do not use auth.uid() for Clerk users

Many Supabase examples use:

auth.uid()

That function represents a Supabase Auth user UUID. A Clerk user ID is normally a string such as user_..., so it should be read from the verified JWT's sub claim:

(select auth.jwt()->>'sub')

The important claims are:

Claim	Meaning	Use
sub	Clerk user ID	User ownership and audit columns
role	Supabase database role, normally authenticated	TO authenticated policies
o.id	Active Clerk organization ID	Organization/tenant context
o.rol	Active Clerk organization role, such as admin	Role checks
aud	JWT audience	Optional metadata; unused by this authorization model

The o organization claim is present when the user has an active Clerk organization. A user may belong to organizations without currently having one active in the session.

A small tenant-aware data model

Four tables are enough to demonstrate the model. Every project and document belongs to an organization, and the composite document foreign key prevents a document from pointing to a project in another organization.

organizations 1 -----< memberships
              1 -----< projects
              1 -----< documents
projects      1 -----< documents

organizations
  PK  id text
      name text

memberships
  PK, FK  organization_id -> organizations.id
  PK      user_id
          role

projects
  PK      id uuid
  FK      organization_id -> organizations.id
          name
          created_by
  UNIQUE  (id, organization_id)

documents
  PK      id uuid
  FK      organization_id -> organizations.id
  FK      (project_id, organization_id)
          -> projects.(id, organization_id)
          body
          created_by

The demo uses the Clerk organization key directly as the local tenant key:

create table organizations (
  id text primary key,
  name text not null
);

create table memberships (
  organization_id text not null references organizations(id),
  user_id text not null,
  role text not null,
  primary key (organization_id, user_id)
);

create table projects (
  id uuid primary key default gen_random_uuid(),
  organization_id text not null references organizations(id),
  name text not null,
  created_by text not null
);

create table documents (
  id uuid primary key default gen_random_uuid(),
  organization_id text not null references organizations(id),
  project_id uuid not null,
  body text not null,
  created_by text not null
);

alter table projects add constraint projects_id_organization_key
  unique (id, organization_id);

alter table documents add constraint documents_project_tenant_fkey
  foreign key (project_id, organization_id)
  references projects(id, organization_id);

Every tenant-owned table carries organization_id. That makes the isolation boundary visible, indexable, and easy to inspect.

When a user is using a personal organization rather than an active Clerk organization, the database derives a deterministic, system-generated organization ID:

org_<clerk_user_id>

This is an application-owned personal organization ID. It lets personal organizations use the same relational and RLS model as Clerk organizations without asking the browser to choose an organization ID.

Larger systems may instead use an internal UUID tenant table:

Clerk organization ID → tenants.organization_id → tenants.id → child.tenant_id

That is appropriate when tenants have billing, region, lifecycle, migration, or product metadata. It adds a lookup layer, but the child rows retain a stable internal foreign key. The demo uses the direct text key so the security model is easier to learn.

RLS and relational integrity protect different boundaries. RLS answers whether this caller may write a row. The composite foreign key answers whether the document and project can ever form an invalid tenant relationship, including during owner-level maintenance or migration work.

The demo also derives created_by in a database trigger from the verified JWT subject and makes it immutable. This is an audit-integrity control, separate from tenant isolation.

Derive audit identity in the database

Do not require the browser to author created_by. A BEFORE INSERT trigger runs before the RLS WITH CHECK expression, so it can derive the value from the verified request and make later changes fail:

create or replace function public.protect_created_by()
returns trigger
language plpgsql
security invoker
set search_path = ''
as $$
begin
  if tg_op = 'INSERT' then
    new.created_by := public.requesting_user_id();
  elsif new.created_by is distinct from old.created_by then
    raise exception 'created_by is immutable'
      using errcode = '42501';
  end if;
  return new;
end;
$$;

create trigger projects_protect_created_by
before insert or update on public.projects
for each row execute function public.protect_created_by();

create trigger documents_protect_created_by
before insert or update on public.documents
for each row execute function public.protect_created_by();

An insert that supplies another user's ID is normalized to the verified sub; an update that tries to change the stored audit identity is rejected. This protects audit integrity, while RLS independently protects tenant authorization.

Centralize JWT claim extraction

Do not repeat JSON expressions throughout every policy. Define small, stable helpers:

create or replace function requesting_user_id()
returns text
language sql
stable
security invoker
set search_path = public
as $$
  select nullif(auth.jwt()->>'sub', '');
$$;

create or replace function requesting_organization_id()
returns text
language sql
stable
security invoker
set search_path = public
as $$
  select case
    when nullif(auth.jwt()->>'sub', '') is null then null
    else coalesce(
      nullif(auth.jwt()->'o'->>'id', ''),
      'org_' || (auth.jwt()->>'sub')
    )
  end;
$$;

create or replace function requesting_organization_role()
returns text
language sql
stable
security invoker
set search_path = public
as $$
  select nullif(auth.jwt()->'o'->>'rol', '');
$$;

The personal-account fallback is deliberately deterministic. It must never be based on a browser-supplied value.

The RLS recursion problem

A tempting policy looks like this:

using (
  exists (
    select 1
    from memberships
    where memberships.organization_id = projects.organization_id
      and memberships.user_id = requesting_user_id()
  )
)

That becomes dangerous when memberships has its own RLS policy. Reading projects causes a membership lookup; the membership lookup invokes membership RLS; membership RLS may invoke another lookup, and the chain becomes recursive or extremely difficult to reason about.

The fix is to separate the authorization calculation from the policy that consumes it.

Use non-recursive access closures

The demo defines an access closure that returns only the organizations authorized for the verified request:

create or replace function public.accessible_organization_ids()
returns setof text
language sql
stable
security definer
set search_path = ''
set row_security = off
as $$
  select m.organization_id
  from public.memberships as m
  where m.user_id = (select public.requesting_user_id())
    and m.organization_id = (select public.requesting_organization_id())
    and m.role in ('admin', 'member');
$$;

The function is intentionally narrow:

• SECURITY DEFINER lets it read the membership relation without re-entering membership RLS.
• SET search_path = '' prevents unqualified objects from being shadowed by objects created in a trusted schema; every relation and function call must therefore be schema-qualified.
• row_security = off makes the no-recursion boundary explicit.
• The function returns IDs, not arbitrary rows or secrets.

Restrict execution to the intended database role:

revoke all on function public.accessible_organization_ids() from public;
grant execute on function public.accessible_organization_ids() to authenticated;

The function owner and deployment role must also be controlled. SECURITY DEFINER is not a shortcut around security review.

Hoist scalars and closures out of per-row work

Call stable scalar helpers as scalar subqueries:

(select requesting_user_id())
(select requesting_organization_id())

For a set of authorized IDs, use an IN (SELECT ...) closure:

organization_id in (
  select public.accessible_organization_ids()
)

This gives PostgreSQL an init-plan or hashed-subplan shape that can evaluate request context once per statement instead of repeatedly performing JSON extraction or membership work for every row.

The policy columns still need indexes:

create index memberships_user_org_idx
  on memberships (user_id, organization_id);

create index projects_organization_idx
  on projects (organization_id);

create index documents_organization_idx
  on documents (organization_id);

create index documents_project_idx
  on documents (project_id);

Always verify the result with EXPLAIN (ANALYZE, BUFFERS) against representative data. An RLS policy can be logically correct and still become expensive when a table grows.

Split policies by command

Avoid hiding all behavior behind FOR ALL. Read and write operations frequently have different authorization requirements, and a FOR ALL policy can unexpectedly run a write-oriented predicate during reads.

A project table can use:

alter table projects enable row level security;

create policy projects_select
on projects
for select to authenticated
using (
  organization_id in (select public.accessible_organization_ids())
);

create policy projects_insert
on projects
for insert to authenticated
with check (
  organization_id = (select requesting_organization_id())
  and organization_id in (select public.accessible_organization_ids())
  and created_by = (select requesting_user_id())
);

create policy projects_update
on projects
for update to authenticated
using (
  organization_id in (select public.accessible_organization_ids())
  and (select requesting_is_organization_admin())
)
with check (
  organization_id = (select requesting_organization_id())
  and organization_id in (select public.accessible_organization_ids())
);

create policy projects_delete
on projects
for delete to authenticated
using (
  organization_id in (select public.accessible_organization_ids())
  and (select requesting_is_organization_admin())
);

This gives the project table an explicit role boundary: organization members can read and create projects, while organization admins can also update and delete them. Personal organizations use the system-managed personal-admin role, so the owner keeps the admin capabilities without requiring an active Clerk organization.

The WITH CHECK expressions are essential. A SELECT policy can hide another tenant's rows, but only WITH CHECK prevents a caller from inserting or updating a row into another tenant.

Keep related rows in the same tenant

A document should not be able to point at a project in another organization. For write policies, expose an efficient accessible-project closure rather than querying the projects table through its RLS policy for every inserted document:

create function public.accessible_project_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
set row_security = off
as $$
  select p.id
  from public.projects as p
  where p.organization_id in (
    select public.accessible_organization_ids()
  );
$$;

Then a document insert must satisfy both conditions:

organization_id = (select requesting_organization_id())
and project_id in (select public.accessible_project_ids())

This preserves referential integrity and prevents cross-tenant object references.

The browser client

The client does not send a tenant ID as an authority claim. It sends the Clerk session token, and the database derives the tenant from that token:

const supabase = createClient(url, publishableKey, {
  accessToken: async () => session?.getToken() ?? null,
  auth: {
    persistSession: false,
    autoRefreshToken: false,
    detectSessionInUrl: false,
  },
})

await supabase.rpc('bootstrap_current_context')

const { data, error } = await supabase
  .from('projects')
  .select('*')
  .eq('organization_id', organizationId)
  .order('created_at', { ascending: false })

The tenant filter helps PostgreSQL use the organization index and reduces work. RLS remains authoritative; this filter is a performance hint, not a security boundary.

The bootstrap RPC is useful when the application maintains a local membership mirror. It creates or updates the current local organization and membership from verified JWT claims. It should never accept organization_id from a request body.

The organization and membership mirrors are read-only through the Data API. Clerk remains the authority for organization membership; the bootstrap RPC is the controlled synchronization path. SECURITY DEFINER provides the function's controlled owner privileges, while row_security = off makes unexpected RLS filtering fail loudly inside the function. It is not itself an authorization bypass.

Test the security boundary

A useful RLS test matrix includes:

• Signed-out request: no protected data.
• Personal user: only their personal organization with its system-generated organization ID.
• Organization admin: current organization data and admin operations.
• Organization member: current organization data; can create projects but cannot update or delete them.
• Project role check: admin update/delete succeeds; member update/delete is rejected.
• Switch from Organization A to Organization B: visible rows change.
• Insert with another organization's ID: rejected by WITH CHECK.
• Insert with another user's ID: overwritten with the verified sub; later audit-field changes are rejected.
• Document referencing another tenant's project: rejected.
• Direct organization or membership mutation: rejected; bootstrap is the mutation path.
• Forged created_by: derived from sub on insert and immutable afterward.
• Direct anonymous table access: rejected.

The demo's document policies currently allow any tenant member to read, create, update, and delete documents within the active tenant. That is an intentional separate role matrix from projects, where update/delete are admin-only. If documents should be admin-managed, narrow their update and delete policies accordingly.

Also add catalog-level tests that verify:

• RLS is enabled on every tenant-owned table.
• No broad FOR ALL policies remain where command splitting is required.
• Access closures are SECURITY DEFINER.
• Definer functions have an explicit search_path.
• Policy columns have supporting indexes.

The demo includes catalog-level checks in supabase/tests/001_rls_contract.sql, rollback-safe behavioral SQL checks in supabase/tests/002_rls_behavior.sql, and the manual browser matrix in docs/manual-test-matrix.md. The SQL harness simulates request.jwt.claims, so it tests policy behavior rather than Clerk token verification; the browser test covers the complete integration.

The layers prove different things: catalog SQL proves configuration, behavioral SQL proves policy outcomes with simulated claims, browser testing proves the real Clerk-to-Supabase token path, and query-plan inspection proves that tenant filtering and closures remain efficient.

What this pattern does not do

This design does not synchronize every Clerk user or organization field into Supabase automatically. If the application needs profile names, billing state, or organization metadata, synchronize those fields through a controlled server-side flow or Clerk webhooks. The JWT remains the source of request identity; the local tables remain the source of application data and authorization relationships.

It also does not make a service-role client safe. A Supabase service-role key bypasses RLS and must remain server-only. The demo intentionally uses the publishable key and the authenticated Clerk session token so RLS is exercised on every request.

Summary

The secure division of responsibility is straightforward:

Clerk authenticates the user
        ↓
Supabase verifies the Clerk session token
        ↓
Postgres reads sub and active organization claims
        ↓
RLS evaluates indexed, non-recursive access closures
        ↓
Only rows in the verified tenant context are visible

The key details are easy to miss: use sub, not auth.uid(); distinguish aud from identity; derive tenant context in SQL; keep membership checks out of recursive policy chains; hoist stable request values; use indexed closures for read paths; and split read/write policies so the query plan remains understandable.