Who Can See Your Invoices? Fast, Readable RBAC for Supabase

You can build Supabase RBAC that a founder can read in business words and that checks permissions in 0.2 ms on 11 million rows. Here is the design, the code, and the benchmark that proves it.

Image by Kingsley Mkpandiok on Unsplash

The question nobody can answer

Ask a founder: "Who can approve invoices at Acme right now?"

In most Supabase projects, the honest answer is: "Give me a day. I need to read 300 migrations." Row Level Security works. It is also invisible. Policies are SQL expressions scattered across files, half written by people who left, half written last month by an AI agent that was told to "fix the empty list".

That agent's favourite fix is using (true). It passes code review, because it looks like one harmless line.

This post is my answer to that problem. It is a role-based access design that does three things at once:

• Clean architecture: the business owns the rules, the database only enforces them.

• Visibility: you can print who can do what, explain why, and see every change in a pull request.

• Speed: I benchmarked it. Spoiler: the naive version took 24 seconds for one screen; this one takes 1 second, and 2.4 ms with one extra index.

RBAC in one minute

Role-based access control has one rule that most implementations quietly break. NIST, the US standards institute that published the reference model for RBAC, puts it plainly: all access goes through roles, and permissions are connected only to roles, never directly to users (NIST RBAC). NIST's reasoning: roles change rarely, while people join, leave and switch teams every week. That gap is why RBAC is cheaper to run and easier to review.

In a SaaS product there is a second rule: a role only means something inside a tenant. Oso, a company that sells authorization as a product, models these as roles assigned on an organization. For example, Alice can be a Finance Approver at Acme and nobody at Globex (Oso, RBAC docs). So the role lives on the membership, not on the user.

Now the mistake I see in almost every Supabase audit: policies that name roles.

using (exists (select 1 from members where user_id = auth.uid() and role = 'admin'))

Every new role becomes a migration. Every renamed role becomes a hunt through policies. The fix is one sentence, and the rest of this post builds on it: policies check permissions, never roles.

The DDD move: access is its own bounded context

Domain-Driven Design gives us the cleanest way to stop the mess: treat access control as its own bounded context, with its own small model and its own language. Every capability of the business needs it, none of the business is defined by it.

Three principles carry the design:

1. The domain depends on nothing. Microsoft's official .NET architecture guide, a widely used DDD reference, is blunt: the domain layer "must completely ignore data persistence details", and dependencies point toward the domain, never away from it (Microsoft Learn). RLS is infrastructure. It enforces decisions; it does not own them.

2. Permissions speak business, not SQL. Not update on invoices, but invoices.approve: "Approve an invoice for payment". That is the ubiquitous language, and it is what a founder can read.

3. Invariants stay in aggregates. "An approved invoice cannot be approved again" is a business rule, not an access rule. Microsoft's Azure Architecture Center defines aggregates as consistency boundaries (Microsoft, tactical DDD), so the rule lives in the Invoice aggregate and never touches RLS.

In code, the domain owns one list, the permission catalog:

// src/domain/permissions.ts  (no Supabase import anywhere in the domain)
export const PERMISSIONS = {
  "invoices.read":    "See invoices",
  "invoices.create":  "Issue invoices",
  "invoices.approve": "Approve an invoice for payment",
  "projects.write":   "Create and edit projects",
} as const;
export type Permission = keyof typeof PERMISSIONS;

A tiny script turns that list into a SQL migration. The database receives the catalog as data. The domain and the policies now use the exact same strings, and the TypeScript compiler catches a typo before Postgres ever sees it. AWS Prescriptive Guidance, Amazon's architecture guidance, calls this shape ports and adapters, with the domain at the core and adapters at the edge (AWS, hexagonal architectures overview), and recommends testing the domain in isolation (AWS, best practices).

The model: five small tables, all private

Image by Liam Scotchmer on Unsplash

The whole access model is five small tables, one of which is the generated catalog, in a private schema that Supabase's API does not expose. Users cannot read it, let alone edit it.

create table private.permissions (name text primary key, description text not null);   -- generated from the domain

create table private.tenants (id uuid primary key, name text not null);
create table private.roles (
  id uuid primary key,
  tenant_id uuid references private.tenants,  -- null = system role, set = a tenant's own custom role
  name text not null);

create table private.role_permissions (role_id uuid, permission text references private.permissions,
  primary key (role_id, permission));

create table private.memberships (tenant_id uuid, user_id uuid, role_id uuid,
  primary key (tenant_id, user_id));

create index on private.memberships (user_id, tenant_id);
create index on private.role_permissions (permission, role_id);

Changing what a role may do is now a data change, not a migration. A customer can even define its own roles. The two indexes matter later: every permission check starts from "this user" and "this permission".

One small public function, my_permissions(tenant), returns the caller's own permissions. The app builds its Actor from it and the UI hides buttons with it. It reads the same rows as the policies, so the app and the database can never disagree about who holds what.

Two policy shapes. Nothing else.

Every policy in the database has one of exactly two shapes. That single constraint is what makes access fast and readable at the same time.

-- The question "in which tenants does the caller hold this permission?", answered as a set
create function private.tenants_with(p_permission text) returns setof uuid
language sql stable security definer set search_path = pg_catalog, pg_temp as $$
  select m.tenant_id
  from private.memberships m
  join private.role_permissions rp on rp.role_id = m.role_id
  where m.user_id = (select auth.uid()) and rp.permission = p_permission
$$;

-- The caller's tenants, for the boundary
create function private.my_tenants() returns setof uuid
language sql stable security definer set search_path = pg_catalog, pg_temp as $$
  select m.tenant_id from private.memberships m where m.user_id = (select auth.uid())
$$;

-- Shape 1, the boundary (restrictive): you never leave your own tenants
create policy "invoices: tenant boundary" on public.invoices as restrictive for all to authenticated
  using (tenant_id = any (array(select private.my_tenants())));

-- Shape 2, the capability (permissive): one named permission per operation
create policy "invoices: approve" on public.invoices for update to authenticated
  using      (tenant_id = any (array(select private.tenants_with('invoices.approve'))))
  with check (tenant_id = any (array(select private.tenants_with('invoices.approve'))));

The capability shape answers "may this user approve invoices here?". The boundary is the safety net. As the official PostgreSQL documentation puts it, restrictive policies are combined with AND, permissive ones with OR (PostgreSQL docs). In my tests, a simulated AI-agent fix added using (true) to "fix" an empty list. The boundary still kept every user inside their own tenant. To prove that boundary holds end to end, see how to prove one tenant can't read another tenant's data in Supabase.

Because the shapes are fixed, a script can read pg_policies and know exactly which permission each operation needs. Any policy that breaks the shape is flagged as opaque: access that nobody can explain automatically, so a human must review it.

Proof of speed: 11 million rows, two designs

"Clean" usually means "slow". So I measured it. I loaded PostgreSQL 16 with 10,000 tenants, 100,000 users, 2,003 roles and 11 million invoices. One tenant got a million invoices of its own, and one "agency" user got access to 1,000 tenants. Every query ran as Supabase's authenticated role with real JWT claims, exactly like the Supabase API runs it.

I compared the obvious design, private.can(tenant_id, 'invoices.read') in every policy, with the set-based shapes above.

Median of 7 warm runs (EXPLAIN ANALYZE) · PostgreSQL 16, 2 vCPU, 7 GB RAM · queries run as Supabase's authenticated role with JWT claims

The per-row design is fine on a small, filtered page. It collapses as soon as a query touches many rows: 11 seconds to count one big tenant, and 24 seconds for the agency screen. The set-based design counts a million rows in 86 ms, only 24 ms more than with no security at all.

Why the difference is so large. can(tenant_id, …) depends on the row, so Postgres must call it once per row. tenants_with('invoices.read') does not, so Postgres runs it once per statement as an InitPlan, a subquery that, in the words of pgMustard, a tool built to explain Postgres query plans, "only needs to run once" (pgMustard), and then uses the answer as an index condition:

InitPlan 1 (returns $0)                       -- the permission question, asked once
->  Bitmap Index Scan on invoices_tenant_created
      Index Cond: (tenant_id = ANY ($0))         -- the answer, used by the index

Supabase's performance guide recommends the same = ANY(ARRAY(select …)) pattern (Supabase, RLS performance).

One more detail explains why your app should still filter by tenant. RLS normally evaluates policy expressions before the conditions in the user's own query; only leakproof functions, mostly B-tree comparisons on base types, can be pushed ahead of the policy, as Cybertec, a long-standing PostgreSQL consultancy, explains alongside the official docs (Cybertec, PostgreSQL docs). That is why the app's own .eq('tenant_id', …) filter still helps.

Under load the gap holds. With 8 concurrent clients, each request a random user loading their newest 50 invoices, the per-row design served 228 requests per second at 35 ms average. The recommended design served 3,538 requests per second at 2.3 ms, on two CPU cores.

How much data is fine?

The size of your tables barely matters. What matters is how many rows one query has to touch and how many tenants one user belongs to.

What grows	Measured	What it means for you
Rows in the table	11,000,000 rows: list page in 0.2 ms	Not your bottleneck, as long as tenant_id leads the index
Rows one query touches	+24 nanoseconds per row (per-row design: +11 microseconds)	1M rows cost ~25 ms of security; per-row would cost ~11 s
Tenants per user	1,000 tenants: 1.8 ms to resolve; 10,000: 16.7 ms	Fine up to ~1,000; at 10,000 every query pays ~17 ms
Roles and permission rows	2,003 roles, 6,011 rows, in every test above	Indexed lookups; thousands of custom roles are fine
Concurrent users	3,538 requests/s at 2.3 ms on 2 vCPU	The database CPU, not the permission check, sets your ceiling

Three rules keep you inside those numbers:

1. Always send the active tenant from the app. RLS is the guard, not the filter. The domain already knows actor.tenantId; pass it as .eq('tenant_id', …). Supabase's own performance guide recommends the same (Supabase, RLS performance).

2. Index what policies filter on. (tenant_id, created_at desc) served every list page in this test.

3. Treat the agency screen as its own query. A user in 1,000 tenants asking "newest 50 everywhere" read 2 million rows. An extra index on created_at cut it to 2.4 ms, but slowed unfiltered queries for normal users to 166 ms. Query shape, not permissions, decides that trade-off.

Honest fine print: this ran on my container, not on a hosted Supabase instance, and excludes network time. Run the same tests on a Supabase branch before you quote my numbers to your board. For a real-world example of RLS that worked in testing but not at scale, read Your Supabase RLS works. But will it scale?.

Visibility: the part founders actually feel

Fast and clean is good engineering. Visibility is what changes the conversation with a founder. Because every policy has a known shape, three things become possible.

1. An access map, in business words. A small tool, run on a throwaway copy of the database, signs in as a fixed cast of test users (one per role, an outsider, an anonymous visitor). Each user tries every operation on every table, with every attempt rolled back. The result is a plain text file:

public.invoices | UPDATE | alice (Acme, Finance Approver) -> Acme
public.invoices | UPDATE | bob (Acme, Member) -> -
public.invoices | UPDATE | "invoices: approve" requires invoices.approve -> held by: Finance Approver, Owner

That answers "who can approve invoices at Acme?" in one line, from the live database, not from 300 migrations.

2. An answer to "why?". explain(person, table, action, tenant) prints the chain: identity, grant, tenant boundary, the permission required, the role that grants it, and what actually happened when the tool tried. No more guessing why a list is empty.

3. An access diff on every pull request. The access map is committed to git. CI rebuilds it after every migration and blocks the merge when access changed or a high-risk finding appears. Here is what it printed for a test migration with three typical AI-agent "fixes":

+public.invoice_totals | SELECT | visitor (anonymous) -> Acme, Globex
+[HIGH] RLS DISABLED: public.attachments is reachable through the API without row level security
BLOCKED: 12 high-severity finding(s)

A dashboard view had quietly exposed every tenant's totals to anonymous visitors. No reviewer would spot that in the SQL; nobody can miss it in the diff.

This is what Thoughtworks, the consultancy behind the widely followed Technology Radar, calls an architectural fitness function (Technology Radar, 2018): an automated check that keeps an architectural characteristic true, continuously, instead of trusting people to remember it (Thoughtworks Radar). Thoughtworks engineers Paula Paul and Rosemary Wang, as reported by the software news site InfoQ, put fitness functions into delivery pipelines "as a natural extension of continuous integration" (InfoQ, on Thoughtworks' Paula Paul and Rosemary Wang). Access stops being a one-off audit and becomes a property the architecture keeps while it evolves, the core idea of Thoughtworks' book on the subject (Building Evolutionary Architectures).

The small print that prevents the big breach

Image by Tom Tvr on Unsplash

The helper functions run as security definer: with the owner's rights, so they can read the private tables the caller cannot. That is a hole in the wall you cut on purpose. Keep it exactly the size you planned:

• Pin the search path to pg_catalog, pg_temp. An empty path still searches the caller's temporary tables first. Supabase changed two functions in its Vault extension for secrets (create_secret and update_secret) from '' to pg_catalog, pg_temp in October 2026 for exactly this reason (supabase/vault #69). Supabase's database-functions guide still shows search_path = '' in its example; the Vault fix is the newer practice.

• Schema-qualify every table, such as private.memberships. The path protects built-in types; qualified names protect yours.

• Close functions by default. Postgres lets everyone execute a new function. Revoke that globally and grant each function to authenticated only (Supabase, database functions).

• Never accept a user id as a parameter. Read the caller from auth.uid(), so a function can only ever answer about the caller.

• Return the same answer for "no such tenant" and "not your tenant", so nobody can probe which tenants exist.

I keep 31 automated checks for these rules, including attempts to hijack the functions with fake tables, fake types and a malicious = operator. All pass.

One documented Postgres side channel remains: unique constraints see rows that RLS hides (PostgreSQL docs), so never let clients choose primary keys.

When you outgrow it

Zanzibar, Google's in-house authorization system for many of its services, described in a peer-reviewed paper, stores over 2 trillion access rules and answers more than 95% of checks within 10 milliseconds, with peaks of about 4.2 million checks per second (Zanzibar, USENIX ATC 2019). One of its tricks, an index called Leopard, flattens deeply nested group membership into sets so checks on those groups avoid chasing pointers. tenants_with() is the same idea at founder scale: turn "who may do this?" into a set, once.

You are probably not Google. Move to a dedicated engine such as SpiceDB or OpenFGA (both open-source engines modelled on Zanzibar) when you hit deep hierarchies (folders inside folders, groups inside groups), checks shared across many services, or users who belong to tens of thousands of tenants. Until then, Postgres does this job in a fraction of a millisecond, and the domain model you built here carries over unchanged: the permission catalog stays, only the adapter moves.

Want to know what your RLS actually allows? I run Supabase access audits: the access map for your database, the risky policies and functions, and a speed check on your real queries. The first call is free: book it here.

See the Supabase Security Audit

References

1. NIST: Role-Based Access Control, FAQ

2. Google: Zanzibar, Google's Consistent, Global Authorization System (USENIX ATC 2019)

3. Oso: Role-based access control (RBAC) docs

4. Microsoft: Designing a DDD-oriented microservice

5. Microsoft: Tactical DDD patterns

6. AWS: Hexagonal architectures, overview

7. AWS: Hexagonal architectures, best practices

8. Supabase: RLS Performance and Best Practices

9. Supabase: Database functions

10. Supabase: Vault PR #69, pg_temp last in search_path

11. PostgreSQL: Row Security Policies

12. Cybertec: When is a function leakproof?

13. pgMustard: InitPlan

14. Thoughtworks Technology Radar: Architectural fitness function

15. Thoughtworks: Building Evolutionary Architectures, 2nd edition

16. InfoQ: Fitness functions, with Thoughtworks' Paula Paul and Rosemary Wang