Multi-tenant Supabase RLS: permissions and executable tests

1. Identify who operates the backend

With Lovable Cloud, Lovable manages the backend. Do not assume the project appears in your Supabase account or gives you the same administrative access as your own project. Use the database, authentication and security controls available in Cloud to review its configuration.

With your own Supabase project connected to Lovable, you administer that project, its migrations, grants and policies. With a custom backend, your API must verify identity and enforce authorization on every route. Table owners normally bypass RLS, but FORCE ROW LEVEL SECURITY subjects them to policies. Superusers and BYPASSRLS roles still bypass RLS even with FORCE.

I separate three questions: who controls the account and infrastructure; which grants, policies and secrets are configured; and which business rules each role must follow. Platform incidents belong with platform support; access rules for your operation need their own criteria and tests.

2. Contract: identity, membership and action

auth.uid() identifies the authenticated user, not the active company. A browser may send company_id to select a tenant, but that value does not grant access. Authorization checks memberships(company_id, user_id, permission) in the database. Its composite key prevents duplicate memberships; permission accepts only viewer or editor.

In this example, a viewer reads company documents; an editor reads, creates, changes the title and deletes any document in that company. created_by records authorship; it is not the authorization rule. On insert it must match auth.uid(). Clients cannot update company_id or created_by, even when they are editors in both companies.

Clients can only read their own memberships. authenticated has neither write grants nor write policies on memberships: users cannot promote themselves, join B or edit someone else’s membership. Invitations and membership administration require a separate server workflow with administrative authorization and auditing. That workflow is outside this demo.

A user with multiple memberships receives permissions per company, without relying on a global tenant in user-editable metadata. Removing membership in A hides A in subsequent READ COMMITTED queries while preserving B. Older transaction snapshots, downloaded results and caches require additional handling; revocation cannot erase data already delivered to a client.

3. USING, WITH CHECK and column privileges

USING restricts which existing rows participate in SELECT, UPDATE and DELETE. WITH CHECK validates new values for INSERT and UPDATE. This example uses both conditions on UPDATE and the SELECT policy needed to find the row. Denied reads return zero rows; filtered updates and deletes may also affect zero rows without raising an error.

WITH CHECK alone does not compare OLD with NEW: an editor of A and B could satisfy both checks and transfer a document. The UPDATE grant therefore permits only title. The transfer test expects a 42501 rejection. This is a column privilege restriction, not evidence that WITH CHECK detected the transfer.

The schema also applies RLS to companies and memberships. Document policies consult only the user’s visible memberships, without a SECURITY DEFINER function. Indexes on memberships(user_id, company_id) and documents(company_id) support those lookups. Other existing permissive policies may widen access: review the complete set instead of pasting this example into an existing database.

4. Company A/B × action matrix

Each cell below becomes an SQL assertion. “CRUD” means read, create, update title and delete. An editor may modify documents authored by other users in the same company, but cannot change authorship or tenant. Access for an A+B editor is intentional and tested separately from cross-company leakage.

5. Run it and check the result

Download the four files below into one folder and run bash run-local.sh. Requirements: Bash and PostgreSQL with initdb, pg_ctl and psql on PATH, running as a regular OS user. The runner creates a disposable cluster with no TCP listener in a private temporary directory. It accepts no database URL and removes the cluster on exit.

bootstrap-local.sql simulates auth.users, auth.uid() and API roles. The harness injects a synthetic UUID with set_config; this does not verify JWTs. Never install this bootstrap on Supabase. schema.sql defines the demo contract; tests.sql seeds data as an administrator, then runs assertions with SET ROLE authenticated, without table ownership, superuser or BYPASSRLS privileges.

Successful output ends with RLS_DEMO_ALL_ASSERTIONS_PASSED. Failures exit with a nonzero status. Cases assert affected row counts and SQLSTATE 42501, covering allowed and denied CRUD, forged authorship, tenant/author transfers, membership writes, a non-member, null identity, anon, downgrade and revocation. A separate case demonstrates that BYPASSRLS sees both tenants.

Executed on October 13, 2026: PostgreSQL 18.6 WASM through PGlite 0.5.9, with 94 passing assertions and simulated Supabase. Supabase integration, JWTs, the HTTP API and production were not tested. The public runner uses the same schema and assertions; a build or browser test does not replace SQL execution.

Executable files

• run-local.sh
• bootstrap-local.sql
• schema.sql
• tests.sql

bash run-local.sh

PostgreSQL WASM alternative: run-wasm.mjs

Instructions and WASM alternative (PT/EN)

-- Contract: synthetic demo, new empty database only. Requires auth.uid(),
-- auth.users(id), authenticated and anon from Supabase or local bootstrap.
-- Ordinary clients can never manage memberships, tenant_id or created_by.
create table public.companies (
  id uuid primary key,
  name text not null
);
create table public.memberships (
  company_id uuid not null references public.companies(id),
  user_id uuid not null references auth.users(id),
  permission text not null check (permission in ('viewer', 'editor')),
  primary key (company_id, user_id)
);
create index memberships_user_company on public.memberships(user_id, company_id);
create table public.documents (
  id uuid primary key,
  company_id uuid not null references public.companies(id),
  created_by uuid not null references auth.users(id) default auth.uid(),
  title text not null
);
create index documents_company on public.documents(company_id);
alter table public.companies enable row level security;
alter table public.memberships enable row level security;
alter table public.documents enable row level security;
revoke all on public.companies, public.memberships, public.documents from public, anon, authenticated;
grant select on public.companies, public.memberships, public.documents to authenticated;
grant insert (id, company_id, created_by, title), update (title), delete on public.documents to authenticated;
-- No INSERT/UPDATE/DELETE grants or policies on memberships for clients.
create policy membership_read on public.memberships for select to authenticated
  using (user_id = (select auth.uid()));
create policy company_read on public.companies for select to authenticated
  using (exists (select 1 from public.memberships m
    where m.company_id = companies.id and m.user_id = (select auth.uid())));
create policy document_read on public.documents for select to authenticated
  using (exists (select 1 from public.memberships m
    where m.company_id = documents.company_id and m.user_id = (select auth.uid())));
create policy document_insert on public.documents for insert to authenticated
  with check (created_by = (select auth.uid()) and exists (
    select 1 from public.memberships m where m.company_id = documents.company_id
      and m.user_id = (select auth.uid()) and m.permission = 'editor'));
create policy document_update on public.documents for update to authenticated
  using (exists (select 1 from public.memberships m
    where m.company_id = documents.company_id and m.user_id = (select auth.uid()) and m.permission = 'editor'))
  with check (exists (select 1 from public.memberships m
    where m.company_id = documents.company_id and m.user_id = (select auth.uid()) and m.permission = 'editor'));
create policy document_delete on public.documents for delete to authenticated
  using (exists (select 1 from public.memberships m
    where m.company_id = documents.company_id and m.user_id = (select auth.uid()) and m.permission = 'editor'));
-- WITH CHECK checks destination authorization, not equality to the OLD row.
-- Column grants above prevent transfers even for an editor of BOTH companies.

6. What still needs production validation

service_role and BYPASSRLS roles bypass RLS. Secret keys belong on the server; requests running as a service should not rely on policies for protection. With legacy-key clients, inspect the effective Authorization header: a user token can make the query run with that user’s permissions. Do not infer authorization context solely from the SDK initialization key.

After SQL testing, I would repeat the matrix through the real API with separate sessions, including applicable privileged endpoints, functions, views, Storage, Realtime, jobs and caches. I would also review inherited grants, exposed schemas, secrets, migrations, backups, logs without sensitive data and concurrent revocation. This SQL alone does not establish security or production readiness.

Lovable already provides Quick scan, Deep scan and eligible automatic fixes. Quick checks database configuration and access, dependencies and MCP exposure; Deep includes application logic, permissions, data, authentication and payments. I use these tools and review their findings. The executed matrix adds evidence for this specific A/B rule; it does not assume Lovable cannot detect it.

Official sources consulted on October 13, 2026

• Supabase — Row Level Security
• PostgreSQL — Row Security Policies
• Lovable — Security overview
• Supabase — Identifying Lovable Cloud or Supabase
• PGlite — PostgreSQL WASM