Supabase for micro-SaaS: authentication, data and RLS

In this multi-tenant teaching model, every tenant-owned row carries the organisation identifier and cross-tenant denial is an explicit test case. A policy that looks reasonable is not proof that the boundary works.

Scope: this is a minimal teaching example, not a security guarantee, audit, or production-ready schema. It omits invitations, billing, deletion, file policies, audit logs, administrator workflows, and other controls your product may require.

A small organisation and membership model

The example has organisations, memberships, and projects. A user may belong to more than one organisation. Projects carry organization_id directly so the policy does not have to infer tenancy through a long relationship chain.

create table public.organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null
);

create table public.memberships (
  organization_id uuid not null references public.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')),
  primary key (organization_id, user_id)
);

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  name text not null,
  created_by uuid not null references auth.users(id),
  created_at timestamptz not null default now()
);

create index memberships_user_org_idx
  on public.memberships (user_id, organization_id);

create index projects_organization_idx
  on public.projects (organization_id);

The indexes are included because the policy filters on membership and organisation columns. They still need representative query testing; their presence does not establish acceptable performance.

RLS policies for the teaching example

Supabase’s RLS guide says RLS must be enabled on tables in an exposed schema and notes that tables created in raw SQL need explicit enabling. It also distinguishes using, which controls existing rows, from with check, which controls proposed rows for writes.

alter table public.organizations enable row level security;
alter table public.memberships enable row level security;
alter table public.projects enable row level security;

create policy "members can read their own memberships"
on public.memberships
for select
to authenticated
using ((select auth.uid()) = user_id);

create policy "members can read their organizations"
on public.organizations
for select
to authenticated
using (
  exists (
    select 1
    from public.memberships m
    where m.organization_id = organizations.id
      and m.user_id = (select auth.uid())
  )
);

create policy "members can read organization projects"
on public.projects
for select
to authenticated
using (
  exists (
    select 1
    from public.memberships m
    where m.organization_id = projects.organization_id
      and m.user_id = (select auth.uid())
  )
);

create policy "members can create organization projects"
on public.projects
for insert
to authenticated
with check (
  created_by = (select auth.uid())
  and exists (
    select 1
    from public.memberships m
    where m.organization_id = projects.organization_id
      and m.user_id = (select auth.uid())
  )
);

create policy "members can update organization projects"
on public.projects
for update
to authenticated
using (
  exists (
    select 1
    from public.memberships m
    where m.organization_id = projects.organization_id
      and m.user_id = (select auth.uid())
  )
)
with check (
  exists (
    select 1
    from public.memberships m
    where m.organization_id = projects.organization_id
      and m.user_id = (select auth.uid())
  )
);

This example deliberately does not let ordinary clients create or edit memberships. A controlled server-side path would need its own authorization checks for invitation, role change, and removal. Do not expose a privileged key to the browser to make those operations convenient.

The bypass that changes the test

Supabase’s official RLS documentation says service keys can bypass RLS and must not be exposed to customers or used in the browser. It also explains that a client initialized with a service key can still follow the signed-in user’s RLS if the client replaces the authorization header with that user’s session. Test the role and token that each request actually uses rather than assuming the key name determines behavior.

Views also deserve an explicit check. Supabase documents that views may bypass underlying RLS by default because they are commonly created with a definer context; the current guide describes security_invoker = true for supported PostgreSQL versions. Any view exposed to clients belongs in the access test matrix.

Negative tests for two tenants

Use two users and two organisations. Seed one project in each organisation through an administrative test fixture, then run the application requests with the same publishable key and signed-in sessions used by the product.

A pgTAP test shape

Supabase’s testing guide documents pgTAP for RLS policies and shows tests that set the authenticated role and JWT subject. Adapt that pattern to the IDs created by your fixture. The comments below are assertions to implement, not claimed test results.

begin;
select plan(5);

set local role authenticated;
set local request.jwt.claim.sub = :'user_a_id';

-- Assert: selecting projects returns only organization A rows.
-- Assert: selecting project B by id returns zero rows.
-- Assert: inserting with organization B id fails.
-- Assert: updating organization_id from A to B changes zero rows or errors.
-- Assert: updating project A's name succeeds.

select * from finish();
rollback;

Run database tests and application-level tests. Database tests exercise policies directly; application tests catch mistakes in session handling, API wrappers, views, and server routes. A passing set reduces uncertainty but does not certify the product as secure.

What to review before customer data arrives

• Every exposed tenant-owned table has RLS enabled and explicit policies for each permitted operation.
• Every write policy checks the resulting tenant boundary, not only the row before the change.
• Service and secret keys stay in controlled server-side paths with documented reasons for privilege.
• Views, storage objects, functions, and server endpoints have their own access tests.
• Cross-tenant negative tests run in the same release process as schema migrations.
• Representative policy queries have been measured against data shaped like the expected workload.

For stack-level trade-offs beyond the tenant model, see the Bubble or WeWeb + Xano for a SaaS MVP?.

Official documentation used

• Supabase, Row Level Security, reviewed 13 July 2026.
• Supabase, Testing overview, reviewed 13 July 2026.
• Supabase, RLS performance and best practices, reviewed 13 July 2026.