Supabase RLS: securing your AI-coded app

Photo by Kevin Ache on Unsplash

What leaks when RLS isn't enabled

You just generated an app with Lovable, Bolt or Claude Code. Your agent created a profiles table, a todos table, a projects table, and wired up Supabase. The app works, so you put it online.

The problem is that security doesn't show in the interface. A table created in Supabase's public schema can receive, depending on the project's configuration, all privileges by default: select, insert, update and delete are granted to the anon and authenticated roles. anon is the role of visitors who aren't logged in. authenticated is the role of logged-in users.

In practice, that means a stranger can, without logging in, send a request and read your entire profiles table: emails, names, sometimes more. And depending on the privileges granted, they can write to it too. This is the most common flaw in AI-generated apps: the agent writes code that works, but it doesn't think about locking down the database.

RLS fixes this at the root. Instead of counting on your frontend not to display certain data, the rule lives in the database itself. Even if someone bypasses your interface, the database refuses.

The three concepts to understand before writing SQL

RLS relies on three concepts that stack. If you only master one of them, your database stays open.

Grants (privileges). They decide whether a role can run an operation on a table: read (select), insert (insert), update (update), delete (delete). A missing grant returns a 42501 error before any policy is even evaluated.

Policies. These are the fine-grained rules, written in SQL, that decide which rows the operation applies to. A read policy on todos can say "you only see the rows where user_id equals your identifier". Technically, it's a where clause that PostgreSQL adds automatically to every query.

Roles. Supabase maps each request to a role: anon (not logged in), authenticated (logged in) and service_role (full server-side access, which bypasses RLS).

The classic mistake is to create policies without touching the grants. But the two stack: PostgreSQL checks the grant first, then the policy. A table protected only by policies still leaves a write door open if the default grants haven't been revoked. Adding a policy doesn't remove existing grants.

Enabling RLS on a table

The first step is to enable RLS on every table in the public schema:

alter table public.todos enable row level security;

Enable it on all tables, not just the ones that seem sensitive. An analytics or preferences table can contain data you don't want freely accessible.

Then remove the default privileges, and grant back only what your app actually uses:

revoke all on public.todos from anon, authenticated;

grant select, insert, update, delete on public.todos to authenticated;

Here, anon can no longer do anything. Only logged-in users can read and write, and even then subject to the policies you're about to write. If your app needs a logged-out visitor to read some public data (a public profile page, for example), grant them select explicitly, nothing more.

Writing your first policies

A basic policy ties each row to the user who owns it. The auth.uid() function returns the identifier of the logged-in user, extracted from the authentication token.

create policy "select own todos"
  on public.todos
  for select
  to authenticated
  using ( (select auth.uid()) = user_id );

create policy "insert own todos"
  on public.todos
  for insert
  to authenticated
  with check ( (select auth.uid()) = user_id );

create policy "update own todos"
  on public.todos
  for update
  to authenticated
  using ( (select auth.uid()) = user_id );

create policy "delete own todos"
  on public.todos
  for delete
  to authenticated
  using ( (select auth.uid()) = user_id );

Two clauses to remember:

• using filters the already existing rows that the query can see, modify or delete. It's the read condition.
• with check validates the new or modified rows at insert or update time. Without it, a user could insert a row with another user's user_id.

Photo by Gorilla ROI Data Connector on Unsplash

A select policy without with check is normal: you don't "check" a read. An insert policy without with check is almost always a mistake.

For performance, add an index on the column your policies filter on first, user_id in this example:

create index todos_user_id_idx on public.todos (user_id);

Without an index, every query scans the entire table to apply the policy, and it gets slow within a few thousand rows.

Anon key, logged-in user, service_role: who sees what

You've probably noticed two keys in your project settings: the anon key and the service_role key. Many vibecoders confuse the two, and it's a major source of leaks.

The anon key is designed to be embedded in the frontend. It's public by nature: anyone can read it in your site's JavaScript. It isn't dangerous in itself, as long as your database is properly protected by RLS. RLS does the real security work, not the secrecy of the key.

The service_role key bypasses RLS. It reads and writes everything, on every table. It must only exist server-side: in a serverless function, a protected API route, a background job. If you paste it into a NEXT_PUBLIC_ variable, into a file sent to the browser or into the code of a Lovable app, you hand everyone the keys to the entire database.

Between the two, the authenticated role corresponds to a user logged in through Supabase Auth. Their rights go through your policies. That's the core of the model: anon for the strict public minimum, authenticated for per-user data, service_role for the server only.

The 6 mistakes that leave your database open

1. Creating policies without revoking the grants. As seen above, the two stack. If anon still has insert on a table, a read policy won't stop it from writing to it. Review the privileges first, then the policies.

2. Using using (true) without thinking. using (true) means "everyone sees every row". That's fine for genuinely public data, like a blog post. It's a disaster on a table of accounts, orders or messages.

3. Letting service_role leak into the frontend. This is the worst leak there is. A single request is enough to wipe or overwrite a database. Check old commits too: if the key was pushed to Git, it stays in the history even after you delete it.

4. Forgetting storage buckets. The files you host on Supabase Storage are protected by the same principles, through the storage.objects table. A public bucket exposes its files to everyone. A private bucket with no policy blocks everyone, including your legitimate users. Treat buckets exactly like tables.

5. Not indexing the filter columns. RLS adds a condition to every query. Without an index on user_id or tenant_id, your queries slow down and your app looks slow when it's really just poorly secured.

6. Thinking the frontend is enough. Hiding a "delete" button in the interface protects nothing. A stranger doesn't go through your interface: they send a request straight to Supabase's API. The only reliable defense is in the database.

The prompt to paste into your agent

You don't need to write all of this by hand. Paste this prompt into Cursor, Claude Code or Lovable, replacing the table names with yours:

Tu es un expert PostgreSQL et Supabase. Sur mon projet, sécurise l'accès aux données avec Row Level Security (RLS).

Pour chaque table du schéma `public` (profiles, todos, projects, ...) :
1. Active RLS : `alter table public.<table> enable row level security;`
2. Révoque les privilèges par défaut : `revoke all on public.<table> from anon, authenticated;`
3. Réaccorde uniquement ce que l'app utilise : `grant select, insert, update, delete on public.<table> to authenticated;`
4. Crée une policy par opération qui relie chaque ligne à l'utilisateur connecté :
   - select / update / delete : `using ( (select auth.uid()) = user_id )`
   - insert : `with check ( (select auth.uid()) = user_id )`
5. Ajoute un index sur la colonne de filtre (`user_id` ou `tenant_id`) pour chaque table.
6. Garde la clé `service_role` uniquement côté serveur. Ne l'ajoute jamais dans du code frontend ni dans une variable d'environnement publique.

Vérifie ensuite que :
- un utilisateur connecté ne peut lire et modifier que ses propres lignes,
- un visiteur non connecté (`anon`) n'accède à aucune ligne sensible,
- aucune table n'utilise `using (true)` sans que ce soit une donnée réellement publique,
- les buckets de stockage ont eux aussi des policies sur la table `storage.objects`.

Photo by Emile Perron on Unsplash

If you'd rather let your agent do the work without copy-pasting, Rustine's MCP server plugs straight into your coding agent: it scans your project and fixes the overly open access itself.

FAQ

Do I need to enable RLS on every table?

Yes. RLS must be enabled on every table in the public schema, even the ones that look harmless. An unprotected table can be read and modified by any role that holds privileges on it, and secondary data is often the data people forget about.

Is the anon key dangerous?

No, if your database is properly protected by RLS. The anon key is designed to be public and embedded in the frontend. What protects your data is RLS, not the secrecy of the key. The service_role key, on the other hand, which bypasses RLS, must stay strictly server-side.

Why does my query return a 42501 error?

The 42501 error means a privilege (grant) is missing. It occurs before policies are evaluated. If a query fails even though your policy should allow it, check the grants on the table before touching the policy: the role probably isn't allowed to perform the operation.

Does RLS slow down queries?

RLS adds a condition to every query, which has a cost. It's easy to keep under control: index the columns used in your policies (user_id, tenant_id), and avoid complex joins inside policy expressions. Without an index, filtering scans the whole table and gets slow.

Does a SQL view protect my data?

No. By default, views bypass RLS, because they are often created with the postgres role. If you expose a view on a sensitive table, protect it explicitly or check that it doesn't leak more than the underlying table.

Close the door before launch

RLS is the most cost-effective protection you can put in place on Supabase: it lives in the database, it doesn't depend on your frontend, and it boils down to three steps (enable, revoke the grants, write policies). It's also the thing coding agents forget most often, because it doesn't show in the interface.

Before you put your app online, scan it with Rustine. It reads your site the way a stranger would, shows you what leaks along with the proof — the request and what it returns — and gives you the fix to paste. The first scan is free, at rustine.io.