Designing Multi-Tenant Authorization With Supabase RLS and Role Boundaries

The tenant label can be correct in the browser while the database is still prepared to return the wrong row. That is the kind of bug a tidy admin screen conceals rather than prevents.

Problem

The access model has to cover several audiences:

• Venue staff who should only see their own venue data.
• Organisation-level users who can manage several venues.
• Platform admins who need support access without turning the app into a free-for-all.
• Guest-facing settings that must stay separate from internal support data and menu data.

The hard part is not describing those roles. The hard part is keeping them separated through every query path.

Constraints and risks

The main risk is accidental cross-tenant access through a query that looks harmless in application code. That can happen when a join skips a tenant filter, a policy is too broad, or a helper function trusts user input instead of authenticated claims.

I also have to keep admin access useful without turning it into a hidden backdoor that bypasses all tenant rules. A platform admin should have explicit access, not implied access.

Design / Investigation

I model the system around a small set of entities:

Entity	Purpose
organisations	Top-level business account
venues	A specific pub or trading location
users	Authenticated people
roles	What a user can do
feature_flags	Tenant-scoped behaviour toggles

The key idea is that membership and scope are separate concerns. A user can belong to an organisation, but that does not mean they can read every table unless the row-level policy says so.

Sanitised helper pattern:

create or replace function app.current_org_id()
returns uuid
language sql
stable
as $$
  select nullif(auth.jwt() ->> 'org_id', '')::uuid
$$;

create or replace function app.is_platform_admin()
returns boolean
language sql
stable
as $$
  select coalesce((auth.jwt() ->> 'role') = 'platform_admin', false)
$$;

Those helpers are intentionally small. They read authenticated context and return a value that RLS policies can use without repeating the same extraction logic everywhere.

Implementation approach

I use policies that read like business rules:

create policy "venue users can read their own venues"
on venues
for select
using (
  organisation_id = app.current_org_id()
  or app.is_platform_admin()
);

That style keeps the logic close to the data. I also test the negative cases, because the blocked path is what protects the boundary.

Practical checks I use:

• Can a venue user read another venue's rows?
• Can an organisation admin see support data only for their organisation?
• Can a platform admin do support work without seeing unrelated secrets?
• Do feature flags stay scoped to the correct tenant?

What I would do differently

I would formalise these policies earlier in the schema design instead of leaving them until the application is already using the tables. It is easier to start with a strict boundary than to add one after queries have spread everywhere.

I would also keep policy tests closer to the migrations so tenant mistakes are caught in the same place they are introduced.

What the audit changed

The tenant SQL audit found that public.tables.pub_id was nullable and that table names were globally unique, even though tables belong to individual pubs. There were no null rows in either audited environment, but the schema still described the wrong system. The migration work made the boundary stricter and left legacy fallback policies as an explicit follow-up risk instead of calling the job complete.

I now treat a frontend tenant check as a convenience, never as the security model. The real test is whether a missing check can become another venue’s data; if it can, the boundary is in the wrong place.