Supabase Row Level Security checklist for AI-built apps (with SQL)

Why does Row Level Security matter so much in AI-built apps?#

Because tools like Lovable and Bolt generate apps where the browser queries Supabase directly. There is no backend in between deciding who may see what. The request carries a public key and, if the user is logged in, their JWT. Postgres then applies Row Level Security policies. If RLS is off or a policy says true, anyone with the public key — which is in your JavaScript — can read or change the table.

This has already happened at scale. Missing RLS exposed data in more than 170 Lovable-built apps, tracked as CVE-2025-48757.

The good news: RLS is plain SQL, it is easy to audit, and fixing it rarely requires touching the frontend. Work through the sections below in order.

Anon key vs service_role key: which one goes where?#

The anon key (called the publishable key in newer projects) goes in the client; the service_role key (the secret key) stays on the server. The anon key maps to the Postgres anon role, or authenticated once a user signs in, and both are subject to RLS. The service_role key maps to a role that bypasses RLS entirely.

A common AI-generated shortcut: a query fails because of RLS, and the "fix" swaps in the service role key on the client. The error disappears, and so does all of your access control. Search your repo and your production bundle for the service role key or any env var named like SERVICE_ROLE. If it was ever shipped to a browser, rotate it.

Server code that acts for a user

Edge Functions and API routes do not automatically need the secret key. When a function acts on behalf of the signed-in user, create the client with the public key and forward the user's Authorization header. Postgres then sees the user's JWT and applies RLS exactly as it would for a browser request. Reserve the secret key for work that genuinely has no user, such as webhooks and scheduled jobs.

// Supabase Edge Function: query as the caller, so RLS still applies
import { createClient } from "npm:@supabase/supabase-js@2";

Deno.serve(async (req) => {
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_ANON_KEY")!,
    { global: { headers: { Authorization: req.headers.get("Authorization") ?? "" } } },
  );
  const { data, error } = await supabase.from("projects").select("id, name");
  if (error) return new Response(error.message, { status: 400 });
  return Response.json(data);
});

Step 1: enable RLS on every exposed table#

Every table in a schema exposed through the Data API (by default public) needs RLS enabled. Tables created in the dashboard's table editor get it by default; tables created with raw SQL or migrations — which is how most AI tools create them — do not unless the SQL says so. Start by listing the gaps:

-- Tables in the public schema with RLS switched off
select schemaname, tablename
from pg_tables
where schemaname = 'public'
  and rowsecurity = false;

-- Tables with RLS on but no policies at all (everything is denied — often a sign
-- someone "fixed" it later with the service_role key)
select c.relname as table_name
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public'
  and c.relkind = 'r'
  and c.relrowsecurity
  and not exists (select 1 from pg_policies p
                  where p.schemaname = 'public' and p.tablename = c.relname);

alter table public.projects enable row level security;

With RLS enabled and no policies, the table denies everything to anon and authenticated. That is the safe default; now you open it up deliberately. The Security Advisor in the Supabase dashboard also flags tables without RLS — run it, but do not treat a clean report as proof that your policies are correct.

Step 2: write one policy per operation#

Write separate policies for select, insert, update and delete, each scoped to a role, instead of one for all policy. Separate policies are easier to review, and they force you to decide whether a user may delete rows at all. using filters which existing rows a user can see or touch; with check validates the new row being written.

-- Assumes an ownership column that defaults to the signed-in user:
--   owner_id uuid not null default auth.uid() references auth.users (id) on delete cascade
create index if not exists projects_owner_id_idx on public.projects (owner_id);

create policy "projects: owners can read"
on public.projects for select
to authenticated
using ( (select auth.uid()) = owner_id );

create policy "projects: owners can create"
on public.projects for insert
to authenticated
with check ( (select auth.uid()) = owner_id );

create policy "projects: owners can update"
on public.projects for update
to authenticated
using ( (select auth.uid()) = owner_id )
with check ( (select auth.uid()) = owner_id );

create policy "projects: owners can delete"
on public.projects for delete
to authenticated
using ( (select auth.uid()) = owner_id );

Two details are worth copying. to authenticated means the policy is not even evaluated for anonymous requests. And wrapping auth.uid() in (select ...) lets Postgres evaluate it once per query instead of once per row, which matters on large tables. Index every column your policies filter on.

-- Only allow users to change specific columns on their profile
revoke update on public.profiles from authenticated;
grant update (display_name, avatar_url) on public.profiles to authenticated;

auth.uid() patterns: ownership, teams and roles#

Almost every policy is one of three shapes: the row belongs to the user, the row belongs to a team the user is in, or the user has a role. Ownership is the owner_id example above. Teams and roles need a little more care.

Team membership

Put the membership check in a security definer function in a schema that is not exposed through the API. That avoids recursive policies on the membership table and keeps the lookup fast.

create schema if not exists private;
grant usage on schema private to authenticated;

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

revoke execute on function private.is_team_member(uuid) from public, anon;
grant execute on function private.is_team_member(uuid) to authenticated;

create policy "documents: team members can read"
on public.documents for select
to authenticated
using ( (select private.is_team_member(team_id)) );

Roles

Read roles from app_metadata, which only the server can set. A frequent AI-generated mistake is checking user_metadata — but signed-in users can update their own user metadata from the client, so they can make themselves an admin.

-- Safe: app_metadata is set server-side only
create policy "invoices: admins can read all"
on public.invoices for select
to authenticated
using ( (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' );

-- Unsafe: user_metadata is writable by the user
-- using ( (auth.jwt() -> 'user_metadata' ->> 'role') = 'admin' )

When a policy is the wrong tool

RLS answers "may this user touch this row?" well. It answers "may this user publish a fourth project on the free plan, and should that send an email?" badly. When a rule depends on quotas, billing state or several tables changing together, keep the table locked down and expose a server endpoint or a narrowly scoped Postgres function that checks the rule and performs the write. Policies that try to encode a whole business process become slow, hard to test and easy to get subtly wrong.

Storage buckets need policies too#

Supabase Storage keeps file metadata in storage.objects, and access is controlled by RLS policies on that table. A public bucket serves any file to anyone who has or guesses the URL, so use public buckets only for genuinely public assets. For user files, use a private bucket, a per-user folder convention, and signed URLs for downloads.

-- Files live at documents/<user-id>/<filename>
create policy "documents bucket: owners can read"
on storage.objects for select
to authenticated
using (
  bucket_id = 'documents'
  and (storage.foldername(name))[1] = (select auth.uid())::text
);

create policy "documents bucket: owners can upload"
on storage.objects for insert
to authenticated
with check (
  bucket_id = 'documents'
  and (storage.foldername(name))[1] = (select auth.uid())::text
);

create policy "documents bucket: owners can delete"
on storage.objects for delete
to authenticated
using (
  bucket_id = 'documents'
  and (storage.foldername(name))[1] = (select auth.uid())::text
);

Also set an allowed MIME type list and a file size limit on each bucket. Without them, an upload form for avatars can be used to host arbitrary files on your domain.

Common RLS mistakes in AI-generated code#

The pattern behind most of these is the same: the AI hit a permissions error and made it go away instead of making it correct. When reviewing an AI-built Supabase project, search for these first.

• `using (true)` or `with check (true)` on tables that hold user data. Sometimes added "temporarily" to unblock a feature.
• `to public` or no role on write policies, so anonymous visitors can insert or update.
• "Any logged-in user" policies such as auth.role() = 'authenticated' on private data: every customer can read every other customer's rows.
• Insert policies without `with check` on ownership, letting users create rows owned by someone else.
• Views that bypass RLS. Views run with the owner's rights by default. On Postgres 15+ create them with (security_invoker = true) so the caller's policies apply.
• `security definer` functions in `public`. They are callable through the API as RPC and run with elevated rights. Keep them in a private schema or check auth.uid() inside them.
• Service role key in client code to get around a policy error.
• Roles read from `user_metadata` instead of app_metadata.

How to test RLS policies#

Test policies by impersonating the roles that will actually hit them: anon, user A and user B. Clicking through the app as yourself proves only that you can see your data. In the SQL editor you can switch role and fake a JWT inside a transaction that you roll back:

begin;
  -- act as an anonymous visitor
  set local role anon;
  select count(*) from public.projects;          -- expect 0

  -- act as user A
  set local role authenticated;
  select set_config('request.jwt.claims',
    '{"sub":"00000000-0000-0000-0000-00000000000a","role":"authenticated"}', true);
  select count(*) from public.projects;          -- expect only A's rows
  update public.projects set name = 'changed'
    where owner_id = '00000000-0000-0000-0000-00000000000b';  -- expect 0 rows updated
rollback;

Then test from the outside, the way an attacker would, using only the public key from your bundle:

curl "https://<project-ref>.supabase.co/rest/v1/projects?select=*" \
  -H "apikey: <anon-or-publishable-key>"
# A correctly protected table returns []

Once the policies are right, freeze them with automated tests. The Supabase CLI runs pgTAP tests with supabase test db; a handful of assertions per table (anon sees nothing, A cannot read B, A cannot reassign ownership) will catch the next AI edit that loosens a policy.

• [ ]RLS enabled on every table in every exposed schema.
• [ ]No service_role / secret key in client code, bundles or public repos.
• [ ]Separate policies per operation, each with an explicit to role.
• [ ]Insert and update policies use with check on ownership.
• [ ]Privileged columns not writable by users.
• [ ]Roles come from app_metadata.
• [ ]Views use security_invoker; definer functions live in a private schema.
• [ ]Private buckets with folder-scoped policies, MIME and size limits.
• [ ]Policies tested as anon, user A and user B — and covered by pgTAP tests.

How Backloo can help#

Everything above can be done in-house with the SQL on this page. If you would like a second pair of eyes, RLS and storage policies are a core part of our vibe code rescue audit: we map every table and bucket, test each policy as anon and as competing users, and hand back migrations and pgTAP tests you own. For apps that have outgrown direct-from-browser queries, our backend systems work moves sensitive logic server-side. For the rest of the go-live list — webhooks, env vars, monitoring — see why AI-built apps break in production.