Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 31 ยท Security Rules and Row Level Security

Row Level Security Policies

Protect Supabase tables with Postgres Row Level Security: enable RLS, write select, insert, update and delete policies, using vs with check, and avoid leaks.

Supabase gives the browser direct access to your Postgres tables through an automatic API. Anyone can open your site and copy the public key, so the database itself must decide which rows each person may touch. Postgres has a built-in feature for exactly that: Row Level Security, or RLS.

With RLS on, every query is silently restricted by policies, small conditions attached to a table. This lesson shows the real SQL (read-only, it needs a project) and then runs a Python stand-in that applies a policy-like filter per user, so you can see the effect. The stand-in is ours; real policies run inside Postgres. You should know basic SQL from the SQL tutorial and the Supabase tables from Supabase and Postgres Fundamentals. The Firebase counterpart is Firestore Security Rules.

RLS in three moves

Enable Turn RLS on for the table.
Write policies One per action, with a condition on each row.
Identify auth.uid() says who is asking.

Enabled with no policies means nobody gets in.

1 / 2

Enabling RLS

SQL
-- read-only
create table public.notes (
  id bigint generated always as identity primary key,
  user_id uuid not null references auth.users (id) default auth.uid(),
  body text not null,
  created_at timestamptz not null default now()
);

alter table public.notes enable row level security;

Once enabled, the anon and authenticated roles (the roles the API uses for signed-out and signed-in users) can see no rows until a policy allows it. Tables created in the dashboard's table editor have RLS on by default, but tables created with raw SQL do not, so always run the alter table line yourself.

Who is asking: auth.uid()

Supabase sets the user's JWT in the database session for each request. Helper functions read it:

  • auth.uid() returns the signed-in user's id, or null for a guest.
  • auth.jwt() returns the token's claims as JSON, for example auth.jwt() ->> 'email' or auth.jwt() -> 'app_metadata' ->> 'role'.

Policies use these to compare against columns such as user_id. Roles that users can edit, like user_metadata, must never decide access; use app_metadata or a table only the server writes. See Users, Roles and Custom Claims.

Policies per action

A policy has a name, a table, an action (select, insert, update, delete or all), the roles it applies to, and up to two expressions.

SQL
-- read-only
create policy "Users read their own notes"
  on public.notes for select
  to authenticated
  using ( (select auth.uid()) = user_id );

create policy "Users add notes for themselves"
  on public.notes for insert
  to authenticated
  with check ( (select auth.uid()) = user_id );

create policy "Users edit their own notes"
  on public.notes for update
  to authenticated
  using ( (select auth.uid()) = user_id )
  with check ( (select auth.uid()) = user_id );

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

Wrapping the call as (select auth.uid()) is a recommended habit: Postgres can evaluate it once per query instead of once per row, which is much faster on big tables.

using versus with check

These two clauses confuse beginners, but the rule is simple:

  • using filters existing rows. It decides which rows you can see, update or delete. A row that fails it behaves as if it does not exist.
  • with check validates new row values. It decides whether the row you are inserting, or the row an update would produce, is acceptable. A failure raises an error.
Action using with check
select yes no
insert no yes
update yes (which rows) yes (what they may become)
delete yes no

The update policy above needs both. Without with check, a user could change user_id on their own note to someone else's id. With it, the new value must also satisfy user_id = auth.uid(). An update also needs a matching select policy to find the row, so you must grant that too.

Multiple policies for the same action and role are combined with OR: a row is allowed if any one passes. You can make a policy restrictive to combine with AND instead, but plain permissive policies are the norm.

The service role bypasses RLS

The service_role key, and the postgres role in the SQL editor, skip all policies. That is useful on a trusted server, such as an Edge Function, but it also means a leaked service key exposes everything. It must never be placed in a browser bundle or a public repository. Views also bypass RLS by default, because they run with the permissions of their owner. See the leaks list below.

Testing a policy

You can test from the SQL editor by switching role and claims inside a transaction:

SQL
-- read-only
begin;
set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111"}', true);
select * from public.notes;      -- only rows of that user
rollback;

Also write automated checks with the Supabase CLI's supabase test db (pgTAP). Test that user A cannot read, update or delete user B's rows, and that a guest gets nothing.

A stand-in row filter

Real RLS lives in Postgres. The Python stand-in below uses a list of rows and a function visible(user, row, action) per action. It reproduces the behavior: using hides rows, with check rejects bad new values, and a table with RLS on and no matching policy returns nothing.

Python Stand-in policies: using and with check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice "updated 0 rows" for Ben: with RLS the row is not forbidden, it simply is not visible to him, so no error appears. The attempt to hand a note to another user is blocked by the with check part.

A second stand-in shows how policies combine with OR and how a missing policy leaves a table closed.

Python Combining policies and the open-table mistake

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Common leaks

  • RLS never enabled. A table in public without RLS is readable and writable by anyone with your public key. Check every table, ideally with the dashboard's security advisor.
  • Views bypass RLS. A view runs as its owner. In recent Postgres (15+) create it with with (security_invoker = true), or keep it out of the exposed schema.
  • security definer functions. They run with the creator's rights and skip RLS. Keep them out of public, validate input inside them, and set a fixed search_path.
  • Policies on user_metadata. Users can edit that field themselves, so a role stored there is not trustworthy.
  • using (true). It makes the table public for that action. Sometimes right for a lookup table, usually a mistake.
  • Forgetting with check on update. Users can reassign rows or escalate fields.
  • Missing indexes. Policies run on every query; index the columns they compare, such as user_id.

Remember too that a policy does not validate data shape. Use column constraints, check constraints and server code for that; the checklist is continued in Security Pitfalls in Both Platforms.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You enable RLS on a table and add no policies. What can the anon and authenticated roles do?

    With RLS enabled, access is denied until a policy allows it.
  2. 2. Which clause validates the values of a row being inserted?

    with check tests new row values on insert and update. using filters existing rows.
  3. 3. Which key bypasses Row Level Security?

    The service role is meant for trusted servers. Never ship it to browsers.
  4. 4. Two permissive policies on the same table, action and role are present. How are they combined?

    Permissive policies are OR-ed. Restrictive policies are AND-ed with them.
  5. 5. Why is a Postgres view a possible RLS leak?

    By default the view reads the base table as its owner, who is not restricted by the policies meant for users.

Summary

  • Enable RLS on every exposed table; with no policies, nothing is accessible.
  • Use auth.uid() (wrapped as (select auth.uid())) to tie rows to the signed-in user.
  • using filters existing rows; with check validates new values; update needs both.
  • Permissive policies combine with OR; the service role and views can bypass RLS.
  • Test policies as different users and index the columns they check.

Next: Security Pitfalls in Both Platforms