Supabase RLS + Next.js App Router: End-to-End Authorization Without Footguns

# What You’ll Build and Why It Matters#

This guide shows a secure, end-to-end authorization model using Supabase Row Level Security and Next.js App Router. The goal is simple: no matter what a user does in the browser, they can only read and write rows they are allowed to access.

“Supabase RLS with Next.js” works best when you treat the database as the enforcement point and Next.js as the orchestration layer. If you only filter data in React components or API routes, a user can bypass you by calling Supabase directly with the same session.

For deeper multi-tenant specifics and additional examples, also read:

• Multi-tenant RLS in Next.js + Supabase
• RBAC and ABAC patterns in the App Router
• Web application security checklist

# Threat Model: The Footguns You Must Design Against#

Authorization bugs are expensive. IBM’s Cost of a Data Breach report consistently shows average breach costs in the millions of USD, and broken access control remains a top OWASP risk category. In multi-tenant SaaS, a single missing tenant check can turn into cross-tenant data exposure.

In Supabase plus Next.js, most incidents come from a few predictable sources:

• Client-side filters that hide data in UI but do not prevent queries.
• Service role key leaks that instantly bypass RLS.
• Unsafe RPC where a function uses SECURITY DEFINER or forgets tenant checks.
• Server-side “shortcuts” where developers use admin clients for convenience and forget to re-check authorization.

🎯 Key Takeaway: If the database can be reached with a user session, the database must enforce authorization. RLS is the enforcement boundary.

# Prerequisites#

# The Secure Baseline Architecture#

Your app should have three “roles” in practice:

The safest default is: never use service role in a request path that originates from a user click. If you must, treat it like writing your own authorization layer and expect mistakes.

⚠️ Warning: A service role key in NEXT_PUBLIC_*, client bundles, logs, or error telemetry is effectively a full database compromise. Rotate it immediately if exposed.

# Data Model for a Multi-Tenant App#

A reliable multi-tenant model avoids “magic” claims and keeps everything explicit in tables. Use:

• tenants as the top-level workspace or organization.
• tenant_members linking users to tenants and roles.
• Tenant-scoped tables that include a tenant_id.

Example schema#

Use tenant_id on every tenant-scoped table even if you can derive it via project_id. This simplifies policies, improves performance, and reduces policy bugs.

SQL: schema and constraints#

-- tenants
create table public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);
 
-- membership
create table public.tenant_members (
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null check (role in ('owner','admin','member')),
  created_at timestamptz not null default now(),
  primary key (tenant_id, user_id)
);
 
-- projects
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  name text not null,
  created_by uuid not null references auth.users(id),
  created_at timestamptz not null default now()
);
 
-- tasks
create table public.tasks (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  project_id uuid not null references public.projects(id) on delete cascade,
  title text not null,
  created_by uuid not null references auth.users(id),
  created_at timestamptz not null default now()
);
 
create index on public.tenant_members (user_id, tenant_id);
create index on public.projects (tenant_id);
create index on public.tasks (tenant_id, project_id);

# Enable RLS and Define a Reusable Membership Check#

Turn on RLS for every tenant-scoped table, then centralize membership logic. In Postgres, the cleanest pattern is a small stable SQL function that checks membership using auth.uid().

SQL: enable RLS#

alter table public.tenants enable row level security;
alter table public.tenant_members enable row level security;
alter table public.projects enable row level security;
alter table public.tasks enable row level security;

SQL: helper function for tenant access#

create or replace function public.is_tenant_member(p_tenant_id uuid)
returns boolean
language sql
stable
as $$
  select exists (
    select 1
    from public.tenant_members tm
    where tm.tenant_id = p_tenant_id
      and tm.user_id = auth.uid()
  );
$$;

This function executes under the caller’s context and still respects RLS on tenant_members unless you explicitly bypass it. That’s a good thing, but it means you must also write correct policies for tenant_members.

ℹ️ Note: If membership checks feel “circular”, it usually means the tenant_members table policy is missing or too restrictive. Start by getting membership policies right, then build on them.

# Policy Design: Least Privilege by Operation#

Design policies per operation, not per table. You want separate policies for select, insert, update, and delete. This reduces accidental write access when you only intended read access.

tenant_members policies#

Typical rules:

• A user can see their own membership rows.
• Owners and admins can manage memberships inside their tenant.
• Only owners can delete another owner.

Here is a pragmatic baseline to start with.

-- read your own memberships
create policy "tenant_members_read_own"
on public.tenant_members
for select
using (user_id = auth.uid());
 
-- owners and admins can read all members in their tenant
create policy "tenant_members_read_tenant"
on public.tenant_members
for select
using (
  public.is_tenant_member(tenant_id)
);
 
-- only owners can insert members, and only into their tenant
create or replace function public.is_tenant_owner(p_tenant_id uuid)
returns boolean
language sql
stable
as $$
  select exists (
    select 1 from public.tenant_members tm
    where tm.tenant_id = p_tenant_id
      and tm.user_id = auth.uid()
      and tm.role = 'owner'
  );
$$;
 
create policy "tenant_members_insert_owner_only"
on public.tenant_members
for insert
with check (
  public.is_tenant_owner(tenant_id)
);
 
-- only owners can update roles, inside their tenant
create policy "tenant_members_update_owner_only"
on public.tenant_members
for update
using (public.is_tenant_owner(tenant_id))
with check (public.is_tenant_owner(tenant_id));

This is not a full “enterprise” policy set, but it’s secure enough to iterate from, and it forces you to be explicit about who can do what.

projects policies#

Rules:

• Any tenant member can read projects in their tenant.
• Any tenant member can create a project in their tenant.
• Only members can update, optionally restrict updates to admins.
• Deletion typically restricted to admins or owners.

create policy "projects_select_member"
on public.projects
for select
using (public.is_tenant_member(tenant_id));
 
create policy "projects_insert_member"
on public.projects
for insert
with check (
  public.is_tenant_member(tenant_id)
  and created_by = auth.uid()
);
 
create policy "projects_update_member"
on public.projects
for update
using (public.is_tenant_member(tenant_id))
with check (public.is_tenant_member(tenant_id));

tasks policies with parent consistency#

A common bug is allowing tenant_id to be set arbitrarily on child rows. Always ensure the child row belongs to the same tenant as its parent.

create or replace function public.project_belongs_to_tenant(p_project_id uuid, p_tenant_id uuid)
returns boolean
language sql
stable
as $$
  select exists (
    select 1 from public.projects p
    where p.id = p_project_id
      and p.tenant_id = p_tenant_id
  );
$$;
 
create policy "tasks_select_member"
on public.tasks
for select
using (public.is_tenant_member(tenant_id));
 
create policy "tasks_insert_member_with_parent_check"
on public.tasks
for insert
with check (
  public.is_tenant_member(tenant_id)
  and created_by = auth.uid()
  and public.project_belongs_to_tenant(project_id, tenant_id)
);

# Next.js App Router: Server-Side Enforcement Patterns#

RLS already enforces access at the data layer. The App Router’s job is to ensure:

• You do not accidentally bypass RLS with service role.
• You structure reads and writes so they run under the user session.
• You avoid leaking tenant identifiers or trusting user-provided tenant input.

Pattern 1: Server Components for reads, Route Handlers for writes#

Reads often fit well in Server Components since they run on the server and can use the user’s cookies-based session. Writes should usually go through Route Handlers where you can validate input and return clear errors.

Use a single tenant selection mechanism, typically:

• active_tenant_id stored in a profiles table under RLS, or
• a tenant slug in the URL and membership enforced by RLS.

If you use URL slugs, treat them as routing identifiers, not authorization checks. Authorization still happens in RLS.

Pattern 2: Always create a “user-scoped” Supabase client on the server#

In App Router, you want a server client that reads the Supabase session from cookies and uses the anon key. That ensures RLS applies.

// lib/supabase/server.ts
import { cookies } from "next/headers";
import { createServerClient } from "@supabase/ssr";
 
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) {
          cookiesToSet.forEach((c) => cookieStore.set(c.name, c.value, c.options));
        },
      },
    }
  );
}

This is intentionally anon-key based. The session makes it “act as the user”, and RLS is enforced.

💡 Tip: Create a second helper that requires a user session and throws if not logged in. You will remove a lot of “optional auth” edge cases and reduce unauthorized states.

// lib/auth/require-user.ts
import { createSupabaseServerClient } from "@/lib/supabase/server";
 
export async function requireUser() {
  const supabase = createSupabaseServerClient();
  const { data, error } = await supabase.auth.getUser();
 
  if (error || !data.user) throw new Error("Unauthorized");
  return { supabase, user: data.user };
}

Pattern 3: Do not accept tenant_id from the client unless you validate it#

A classic multi-tenant bug is an API route that accepts tenant_id and inserts rows with it. The insert might pass if policies are too permissive, or if the code accidentally uses service role.

Instead:

• derive tenant_id from the URL and trust RLS to block non-members, or
• fetch the user’s allowed tenants and verify membership before proceeding.

Here is a safe Route Handler pattern that reads the tenant slug from params, resolves it, then inserts.

// app/api/tenants/[tenantId]/projects/route.ts
import { NextResponse } from "next/server";
import { requireUser } from "@/lib/auth/require-user";
 
export async function POST(req: Request, ctx: { params: Promise<{ tenantId: string }> }) {
  const { supabase, user } = await requireUser();
  const { tenantId } = await ctx.params;
 
  const body = await req.json();
  const name = String(body?.name ?? "").trim();
 
  if (name.length < 2) return NextResponse.json({ error: "Invalid name" }, { status: 400 });
 
  const { data, error } = await supabase
    .from("projects")
    .insert({ tenant_id: tenantId, name, created_by: user.id })
    .select("id, tenant_id, name")
    .single();
 
  if (error) return NextResponse.json({ error: error.message }, { status: 403 });
  return NextResponse.json({ project: data });
}

This still accepts tenantId from the URL, but it never trusts it for authorization. If the user is not a member, RLS denies the insert.

Pattern 4: Avoid service-role in Route Handlers#

If you use the service role in the handler above, the insert will always succeed, even for non-members, unless you manually implement checks. That is the most common “it worked in dev” authorization regression we see during audits.

If you need admin-level operations:

• isolate them in separate endpoints
• protect with admin auth
• implement explicit checks
• log every call
• consider moving it to background jobs

# RPC and “Unsafe Functions”: The High-Risk Zone#

RPC is useful for complex operations, but it’s also where teams accidentally disable RLS.

Here is the safe baseline:

If you use RPC for a multi-table write, prefer:

• invoker mode
• explicit tenant membership checks inside the function
• minimal surface area

Example: safe-ish invoker RPC#

create or replace function public.create_task(p_tenant_id uuid, p_project_id uuid, p_title text)
returns public.tasks
language plpgsql
as $$
declare
  v_task public.tasks;
begin
  if not public.is_tenant_member(p_tenant_id) then
    raise exception 'not a tenant member';
  end if;
 
  if not public.project_belongs_to_tenant(p_project_id, p_tenant_id) then
    raise exception 'project not in tenant';
  end if;
 
  insert into public.tasks (tenant_id, project_id, title, created_by)
  values (p_tenant_id, p_project_id, p_title, auth.uid())
  returning * into v_task;
 
  return v_task;
end;
$$;

This still depends on RLS for any table reads done elsewhere, but it forces tenant checks up front.

⚠️ Warning: Avoid SECURITY DEFINER unless you have a strong reason and a thorough review process. Many production leaks happen because a definer function allows cross-tenant reads without any policy involvement.

# End-to-End Flow: A Secure Multi-Tenant Read and Write#

A good “E2E authorization” test is: can a user with a valid session read another tenant’s data by changing an identifier.

Read flow in a Server Component#

• The component receives tenantId from route params.
• It queries projects by tenant_id.
• RLS ensures only member rows are visible.

// app/(app)/tenants/[tenantId]/projects/page.tsx
import { createSupabaseServerClient } from "@/lib/supabase/server";
 
export default async function ProjectsPage(props: { params: Promise<{ tenantId: string }> }) {
  const { tenantId } = await props.params;
  const supabase = createSupabaseServerClient();
 
  const { data: projects, error } = await supabase
    .from("projects")
    .select("id, name, created_at")
    .eq("tenant_id", tenantId)
    .order("created_at", { ascending: false });
 
  if (error) throw new Error("Not authorized or query failed");
 
  return (
    <main>
      <h1>Projects</h1>
      <ul>
        {(projects ?? []).map((p) => (
          <li key={p.id}>{p.name}</li>
        ))}
      </ul>
    </main>
  );
}

Even if the user changes the URL to another tenant ID, the query returns an empty set or an authorization error depending on your policy and PostgREST behavior.

Write flow in a Route Handler#

You already saw the insert example. The key point is: the server enforces authentication and input validation, the database enforces authorization.

# Policy Hardening for Real SaaS Use#

The baseline policies above are correct but not complete. Here are common hardening additions that prevent subtle multi-tenant bugs.

Add immutable tenant_id constraints#

Prevent updates that move rows between tenants. Even if your UI never does it, attackers will try.

create policy "projects_prevent_tenant_move"
on public.projects
for update
using (public.is_tenant_member(tenant_id))
with check (tenant_id = (select tenant_id from public.projects p where p.id = id));

This pattern can be clunky. Many teams handle this with:

• restricting updates to specific columns using views, or
• using triggers to block tenant_id changes

Ensure child rows cannot drift across tenants#

You already added project_belongs_to_tenant. Apply it to updates too, not only inserts.

create policy "tasks_update_member_with_parent_check"
on public.tasks
for update
using (public.is_tenant_member(tenant_id))
with check (
  public.is_tenant_member(tenant_id)
  and public.project_belongs_to_tenant(project_id, tenant_id)
);

Avoid “policy sprawl” with consistent naming and templates#

In multi-tenant apps, you will create dozens of policies. Use a naming convention like:

• {table}_{op}_{who}_{constraints}
  Example: tasks_insert_member_parent_check

This makes audits and PR reviews faster.

# The Checklist: Common Mistakes That Break Authorization#

Use this as a pre-release gate. It catches most real-world issues we see in Supabase plus Next.js projects.

Service role leaks and misuse#

• Service role key is not in any NEXT_PUBLIC_* env var.
• Service role key is not used in any Server Component or Route Handler that runs on behalf of an end user.
• Service role key is only used in isolated admin jobs, webhooks, or background processing.
• Logs and error trackers do not capture environment variables or headers containing secrets.

Unsafe RPC and definer functions#

• No SECURITY DEFINER functions exist without a documented reason and review.
• Every RPC that accepts tenant_id checks membership inside the function.
• RPC functions do not return cross-tenant aggregates unless explicitly intended.
• Policies still apply to tables touched by the RPC, or checks fully replace them.

Client-side filtering and “UI-only authorization”#

• No page relies on hiding UI elements as the only authorization.
• Every query that includes a tenant identifier is safe even if the identifier is changed.
• There is no “fetch all then filter in React” for tenant-scoped data.
• API routes validate inputs but do not attempt to re-implement row authorization logic.

RLS coverage and policy correctness#

• RLS is enabled on every tenant-scoped table.
• Each table has policies for select, insert, update, and delete as needed.
• Policies check both membership and parent consistency for child tables.
• There are indexes supporting the policy checks, especially on tenant_members(user_id, tenant_id).

# Testing: Prove Cross-Tenant Access Is Impossible#

Do not ship without at least these tests:

1. 1
   User A creates a tenant, project, and task.
2. 2
   User B is in a different tenant.
3. 3
   Verify User B cannot:

   • select User A’s projects by changing tenant_id
   • insert a task into User A’s project
   • call RPC with User A’s tenant_id successfully
4. 4
   Verify expected admin behaviors, such as an owner managing members.

A simple automated approach is to run integration tests that:

• sign in using two test users
• perform the same requests with different cookies
• assert empty results or 403 errors

# Key Takeaways#

• Treat RLS as the authorization boundary and Next.js as orchestration, not enforcement.
• Use a multi-tenant schema with explicit tenant_id on every tenant-scoped table and enforce membership via tenant_members.
• Use server-side Supabase clients with anon key plus user session to keep RLS enforced in Server Components and Route Handlers.
• Avoid service role in user request paths, and isolate admin operations behind explicit checks and logging.
• Be extremely cautious with RPC, especially anything resembling SECURITY DEFINER, and validate tenant access inside functions.
• Use the checklist to catch the three big failures: service-role leaks, unsafe RPC, and client-side filters.

# Conclusion#

Supabase RLS plus Next.js App Router is a strong combination when you consistently run queries under the user session and let the database enforce tenant boundaries. The fastest way to ship securely is to start with a clean multi-tenant schema, implement least-privilege policies per operation, and forbid service-role shortcuts in user-driven endpoints.

If you want us to review your policies, tighten your App Router server patterns, or design a multi-tenant authorization model that scales without surprises, contact Samioda via our web development page or continue with our guides on multi-tenant RLS, App Router authz patterns, and the broader security checklist.