Postgres RLS Done Right: The 10 Mistakes That Exposed 170+ Apps

On May 29, 2025, security researcher Matt Palmer disclosed CVE-2025-48757: Lovable-generated apps could expose Supabase-backed data through missing or insufficient Row-Level Security on client-controlled database requests. Palmer's disclosure listed a CVSS v3.1 base score of 8.26. The NVD record shows MITRE's CNA score as 9.3 and marks the record disputed because Lovable argues each customer app owner shares responsibility for protecting application data.

The public scale numbers come from follow-on scanner reports, not from Palmer's disclosure itself. Vibe App Scanner reports 170 affected apps and 303 vulnerable endpoints across 1,645 scanned Lovable apps. Superblocks reports exposed usernames, emails, phone numbers, payment status, subscription data, Gemini and Google Maps API keys, developer credentials, and Stripe-related override paths.

The root cause was not exotic. It was Postgres Row-Level Security configured the way an LLM tends to configure it: tables created, RLS sometimes enabled, policies shaped like the documentation example, anon key treated like a security boundary. Lovable announced a "security scan" feature with Lovable 2.0 on April 24, 2025; Superblocks later reported that scanner only checked whether RLS existed. It did not prove the policies matched the product's tenant model.

This post is the schema we wish those 170 founders had started with. It is opinionated, concrete, and built around a single rule: never trust a policy that you have not read, indexed, and tested.

Why AI tools generate RLS that looks right and isn't

RLS in Postgres is unforgiving in a specific way. A table with enable row level security and zero policies silently denies all reads, so the app appears broken. The fastest fix that makes the test pass, and the fix Cursor, Bolt, and Lovable reach for repeatedly, is using (true). Every row, every role, fully open. The migration runs, the test passes, and the anon key in the browser can now read the entire table.

We have seen ten variants of this same pattern in production audits. Each one looks like a small detail.

1. RLS off entirely. The migration creates the table; enable row level security is never issued. On an exposed Supabase table with permissive grants, the anon key can read or write every row. **2. using (true) everywhere. Often copy-pasted from documentation examples that were never meant for production. 3. SELECT-only policy, no INSERT/UPDATE/DELETE.** Reads check auth.uid(), but writes fail under default-deny. The dangerous part usually comes next: someone adds a broad write policy to make the app work without testing tenant boundaries. **4. No to authenticated.** Policies default to public, which includes anon. Postgres still evaluates the policy on every anon request. **5. Authorization based on raw_user_meta_data.** This column is user-mutable through the public auth API. A user can grant themselves role = 'admin' with a single client call. **6. The service_role key in a Next.js client component. A service key exposed to the browser can bypass RLS and must be treated as compromised. 7. Public storage buckets for private files. Public buckets make reads public by URL. Writes still depend on storage policies, but the private-file boundary is already gone. 8. enable row level security with no policies.** Default-deny, app breaks, junior dev "fixes" it with using (true). We are back to mistake two. **9. No force row level security.** Background jobs and migrations often run as the table owner. The owner bypasses policies by default unless you force RLS; superusers and roles with BYPASSRLS still bypass either way. 10. Junction tables left unprotected. team_members, account_memberships, organization_users: the table that defines who can see what is itself open. Anyone can insert themselves as an owner.

Read the list once and notice how mundane each item is. None of these require a sophisticated attacker. A curl command and a browser-extracted anon key are enough.

The canonical schema is the actual fix

For multi-tenant SaaS up to a few hundred tenants, shared-schema is the right shape. One Postgres database, an account_id column on every tenant-scoped row, RLS enforcing the partition. Schema-per-tenant becomes defensible above that scale, but it breaks Supabase's auto-generated PostgREST API and migrations get expensive fast.

Here is the starter we hand to clients. Two tables for tenants and membership, two for the actual product surface. Treat it as the floor, not the ceiling.

-- 1. Tenants and membership
create table public.accounts (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text unique not null,
  primary_owner_user_id uuid not null references auth.users(id),
  created_at timestamptz not null default now()
);

create type public.account_role as enum ('owner', 'admin', 'member');

create table public.account_memberships (
  account_id uuid not null references public.accounts(id) on delete cascade,
  user_id    uuid not null references auth.users(id)      on delete cascade,
  role       public.account_role not null default 'member',
  created_at timestamptz not null default now(),
  primary key (account_id, user_id)
);

-- 2. Tenant-scoped data
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id) on delete cascade,
  name text not null,
  created_by uuid not null references auth.users(id),
  created_at timestamptz not null default now(),
  deleted_at timestamptz,
  unique (account_id, id)
);

create table public.tasks (
  id uuid primary key default gen_random_uuid(),
  account_id uuid not null references public.accounts(id) on delete cascade,
  project_id uuid not null,
  title text not null,
  assignee_id uuid references auth.users(id),
  status text not null default 'open',
  created_at timestamptz not null default now(),
  foreign key (account_id, project_id)
    references public.projects(account_id, id)
    on delete cascade
);

-- 3. Indexes for RLS, not optional
create index account_memberships_user_id_idx on public.account_memberships (user_id);
create index projects_account_id_idx          on public.projects (account_id);
create index tasks_account_id_idx             on public.tasks    (account_id);
create index tasks_project_id_idx             on public.tasks    (project_id);
create index tasks_assignee_id_idx            on public.tasks    (assignee_id);
create index projects_active_idx              on public.projects (account_id) where deleted_at is null;

The composite foreign key on tasks is not decoration. If tasks.account_id says account A but project_id points to account B, an RLS policy that only checks tasks.account_id has a hole. The database should reject that row before policy code even runs.

The indexes are the single biggest performance lever. Supabase's own RLS performance benchmarks show a query on an unindexed user_id column dropping from 171ms to under 0.1ms once a btree is added. On a busy tasks table, that is the difference between a policy check that feels invisible and one that dominates the dashboard query.

Now lock the doors. Two passes: enable for the policy machinery, force so that even the table owner runs through it. Then explicit grants, because Supabase exposes everything the authenticated role can touch through PostgREST.

alter table public.accounts            enable row level security;
alter table public.account_memberships enable row level security;
alter table public.projects            enable row level security;
alter table public.tasks               enable row level security;

alter table public.accounts            force row level security;
alter table public.account_memberships force row level security;
alter table public.projects            force row level security;
alter table public.tasks               force row level security;

grant select, update, delete         on public.accounts            to authenticated;
grant select, insert, update, delete on public.account_memberships to authenticated;
grant select, insert, update, delete on public.projects            to authenticated;
grant select, insert, update, delete on public.tasks               to authenticated;

Do not grant direct client inserts on accounts. Account creation needs a bootstrap path that creates the account and the first owner membership in the same transaction. We will add that RPC after the membership helper.

The force line is the one Lovable never writes. Without it, your nightly job that runs as the table owner, backfills, rollups, the script you ran from psql to fix one customer's data, sees every row in every tenant. FORCE ROW LEVEL SECURITY does not constrain superusers or roles with BYPASSRLS, so those roles still belong on a short, audited list.

The one function every policy must call

Every tenant table policy needs to ask the same question: is the current user a member of this account? If you answer that question with a join through account_memberships from inside a policy, Postgres re-evaluates RLS on account_memberships on every check, which itself joins account_memberships, which itself has RLS. The Supabase performance benchmarks measured this exact pattern at 178,000ms before the fix and 12ms after.

The fix is a security definer function in a non-exposed schema, pinned with set search_path = ''. SECURITY DEFINER is not magic by itself: it runs as the function owner, so the owner and grants matter. In Supabase migrations, create it as a trusted role that can read account_memberships without recursive RLS penalties, then test it under the authenticated role.

create schema if not exists private;
revoke all on schema private from public, anon, authenticated;
grant usage on schema private to authenticated;

create or replace function private.is_account_member(target_account_id uuid)
returns boolean
language sql
security definer
stable
set search_path = ''
as $$
  select exists (
    select 1
    from public.account_memberships m
    where m.account_id = target_account_id
      and m.user_id   = (select auth.uid())
  );
$$;

create or replace function private.has_account_role(
  target_account_id uuid,
  required_roles    public.account_role[]
)
returns boolean
language sql
security definer
stable
set search_path = ''
as $$
  select exists (
    select 1
    from public.account_memberships m
    where m.account_id = target_account_id
      and m.user_id   = (select auth.uid())
      and m.role      = any (required_roles)
  );
$$;

revoke execute on function private.is_account_member(uuid) from public, anon;
revoke execute on function private.has_account_role(uuid, public.account_role[]) from public, anon;
grant execute on function private.is_account_member(uuid) to authenticated;
grant execute on function private.has_account_role(uuid, public.account_role[]) to authenticated;

Two details that look cosmetic and are not. The private schema must not appear in your Supabase API "Exposed schemas" setting. The set search_path = '' line forces every object reference in the function body to be schema-qualified, which removes writable-schema and temp-schema surprises from privileged code.

Account creation needs a bootstrap path

Most RLS tutorials cheat here. They let the user insert an accounts row, then expect the same user to insert the first account_memberships row. That cannot work if the membership insert policy requires the user to already be an owner or admin.

Use an RPC that creates both rows in one transaction. The client calls public.create_account('Acme', 'acme'); direct inserts into public.accounts stay unavailable to authenticated.

create or replace function public.create_account(account_name text, account_slug text)
returns uuid
language plpgsql
security definer
set search_path = ''
as $$
declare
  new_account_id uuid;
  current_user_id uuid := (select auth.uid());
begin
  if current_user_id is null then
    raise exception 'not authenticated';
  end if;

  insert into public.accounts (name, slug, primary_owner_user_id)
  values (account_name, account_slug, current_user_id)
  returning id into new_account_id;

  insert into public.account_memberships (account_id, user_id, role)
  values (new_account_id, current_user_id, 'owner');

  return new_account_id;
end;
$$;

revoke execute on function public.create_account(text, text) from public, anon;
grant execute on function public.create_account(text, text) to authenticated;

This is the part that makes the starter schema usable on day one. No service key in the browser. No "temporarily open memberships, then close them later." No manual SQL in production for the first customer.

The four-policy pattern, applied per table

Supabase's own rule of thumb, which is a good one: SELECT uses using only, INSERT uses with check only, UPDATE needs both, DELETE uses using only. to authenticated is mandatory on every policy. Wrap every call to auth.uid() and to your security-definer helpers in (select ...) so the planner caches the value once per statement instead of per row, that single change took an admin check from 11,000ms to 7ms in Supabase's benchmark.

-- accounts
create policy accounts_select on public.accounts
  for select to authenticated
  using ( (select private.is_account_member(id)) );

create policy accounts_update on public.accounts
  for update to authenticated
  using      ( (select private.has_account_role(id, array['owner','admin']::public.account_role[])) )
  with check ( (select private.has_account_role(id, array['owner','admin']::public.account_role[])) );

create policy accounts_delete on public.accounts
  for delete to authenticated
  using ( primary_owner_user_id = (select auth.uid()) );

-- account_memberships: the junction table everyone forgets
create policy memberships_select on public.account_memberships
  for select to authenticated
  using ( (select private.is_account_member(account_id)) );

create policy memberships_insert on public.account_memberships
  for insert to authenticated
  with check (
    (
      role = 'member'
      and (select private.has_account_role(account_id, array['owner','admin']::public.account_role[]))
    )
    or (
      role in ('owner','admin')
      and (select private.has_account_role(account_id, array['owner']::public.account_role[]))
    )
  );

create policy memberships_update on public.account_memberships
  for update to authenticated
  using (
    (select private.has_account_role(account_id, array['owner']::public.account_role[]))
    or (
      role = 'member'
      and (select private.has_account_role(account_id, array['admin']::public.account_role[]))
    )
  )
  with check (
    (select private.has_account_role(account_id, array['owner']::public.account_role[]))
    or (
      role = 'member'
      and (select private.has_account_role(account_id, array['admin']::public.account_role[]))
    )
  );

create policy memberships_delete on public.account_memberships
  for delete to authenticated
  using (
    (select private.has_account_role(account_id, array['owner']::public.account_role[]))
    or (
      role = 'member'
      and (select private.has_account_role(account_id, array['admin']::public.account_role[]))
    )
  );

-- projects (tenant-scoped, soft-deleted)
create policy projects_select on public.projects
  for select to authenticated
  using ( (select private.is_account_member(account_id)) and deleted_at is null );

create policy projects_insert on public.projects
  for insert to authenticated
  with check (
    (select private.is_account_member(account_id))
    and created_by = (select auth.uid())
  );

create policy projects_update on public.projects
  for update to authenticated
  using      ( (select private.is_account_member(account_id)) )
  with check ( (select private.is_account_member(account_id)) );

create policy projects_delete on public.projects
  for delete to authenticated
  using ( (select private.has_account_role(account_id, array['owner','admin']::public.account_role[])) );

-- tasks
create policy tasks_select on public.tasks
  for select to authenticated using ( (select private.is_account_member(account_id)) );

create policy tasks_insert on public.tasks
  for insert to authenticated with check ( (select private.is_account_member(account_id)) );

create policy tasks_update on public.tasks
  for update to authenticated
  using      ( (select private.is_account_member(account_id)) )
  with check ( (select private.is_account_member(account_id)) );

create policy tasks_delete on public.tasks
  for delete to authenticated
  using ( (select private.has_account_role(account_id, array['owner','admin']::public.account_role[])) );

Notice the asymmetry on account_memberships. Reading the membership list is open to any member of the account, you need to see your teammates. Writing it is restricted to owners and admins. This is the policy that prevents the most common privilege-escalation path in vibe-coded SaaS: a member user inserting a row that flips themselves to owner.

The policy is stricter than "admins can do everything." Owners can create or change owners and admins; admins can manage members. That keeps a delegated admin from promoting themselves into the role that owns the account.

Notice also with check on UPDATE for projects and tasks. Without it, a user could update a row they own and set account_id to a different tenant: a row they do not own moves out of their reach and into someone else's database view. The with check clause re-runs the membership test against the new row, and the composite FK on tasks enforces that project_id belongs to the same account_id.

Audit your own database before the next deploy

Imagine you're the CTO of a Series A SaaS that started life as a Lovable prototype. You inherited 47 tables. You do not know which have RLS, which have wide-open policies, and which are silently default-deny. Run these eight queries against the production database before the next push.

-- 1. Tables in `public` with RLS DISABLED.
-- Hard fail for tenant or private-data tables; allowlist intentional public read models.
select schemaname, tablename, rowsecurity
from pg_tables
where schemaname = 'public' and rowsecurity = false
order by tablename;

-- 2. RLS enabled but ZERO policies (silent default-deny).
select t.schemaname, t.tablename
from pg_tables t
left join pg_policies p
  on p.schemaname = t.schemaname and p.tablename = t.tablename
where t.schemaname = 'public' and t.rowsecurity = true and p.policyname is null
order by t.tablename;

-- 3. Wide-open USING (true) policies.
select schemaname, tablename, policyname, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public' and (qual = 'true' or with_check = 'true')
order by tablename;

-- 4. Policies that target PUBLIC (i.e. include anon).
select schemaname, tablename, policyname, roles, cmd
from pg_policies
where schemaname = 'public' and 'public' = any (roles)
order by tablename;

-- 5. Public storage buckets, review with an allowlist.
select id, name, public from storage.buckets where public = true;

-- 6. Tables NOT under FORCE ROW LEVEL SECURITY, review with an allowlist.
select n.nspname as schema, c.relname as table,
       c.relrowsecurity as rls_enabled,
       c.relforcerowsecurity as rls_forced
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r'
  and c.relrowsecurity = true and c.relforcerowsecurity = false
order by c.relname;

-- 7. Roles with BYPASSRLS, review with an allowlist.
select rolname, rolbypassrls, rolsuper
from pg_roles
where rolbypassrls = true or rolsuper = true
order by rolname;

-- 8. Full policy dump, read every line.
select schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
from pg_policies
where schemaname = 'public'
order by tablename, cmd;

These run in seconds against any database. Fail CI for tenant tables with RLS disabled, tenant tables with zero policies, private-data policies that are effectively true, and unexpected public or anon policy roles. Treat public buckets, BYPASSRLS roles, and missing FORCE ROW LEVEL SECURITY as allowlist reviews; normal Supabase projects will have a few expected rows there, but they should never be surprises.

The Supabase Database Linter ships related checks such as 0013_rls_disabled_in_public and 0003_auth_rls_initplan. Use the Advisor output as a supplement, not a replacement. It can tell you RLS is missing or slow; it cannot know whether tasks.project_id belongs to the same tenant as tasks.account_id.

Test policies the way you test endpoints

A policy you have not tested is a wish. The canonical Postgres test framework is pgTAP, and the Basejump team's basejump-supabase_test_helpers extension adds the helpers that matter for Supabase: tests.create_supabase_user, tests.authenticate_as, tests.rls_enabled, tests.clear_authentication. Together they let you write a test that creates two users in two tenants and asserts that one cannot see the other's rows: the assertion those apps needed before publish.

Wire supabase test db into CI on every pull request. If a developer adds a new tenant table without enabling RLS, the test that asserts tests.rls_enabled('public', 'new_table') fails before review. Add regression tests for the two boring bugs above: create_account() creates the owner membership, and Bob cannot insert a task with Bob's account_id and Alice's project_id. That second test should fail at the database constraint, not in React.

What this changes for the team building the app

For a multi-tenant Postgres app, this is the starting point we expect to see: accounts, memberships, security-definer helpers, the four-policy pattern, the audit script in CI, and pgTAP tests on every pull request. The fix is mechanical, not heroic. It just has to actually be applied.

If you are running on Supabase and have not read your pg_policies output line by line in the last month, do it today. Run the eight queries in the audit section. If the hard-fail checks return rows, or if the allowlist makes you uneasy. book a call and we will walk through it with your team. If you want the broader engineering picture of how we build production-grade backends from the schema up, the engineering practice page has the rest.

The lesson of CVE-2025-48757 is not that Postgres RLS is dangerous. It is that the default the AI tools generate is dangerous. The version with force row level security, a real account bootstrap path, tenant-scoped foreign keys, security-definer membership functions, indexed policy columns, and pgTAP tests in CI is not exotic. It is a few hundred lines of SQL you adapt, run, and keep under test before someone else scans your app for a blog post.