Row Level Security

Your project's anon key is public: it ships inside your web and mobile apps. What stops someone with that key from reading every row is Row Level Security (RLS). With RLS enabled on a table, Postgres only returns or changes the rows that a policy allows for the current role and user.

This is standard Postgres, and it works on SupaHost exactly as it does on any Supabase project.

How requests map to roles

auth.uid() returns the signed-in user's id (the JWT's sub claim), and auth.jwt() returns all the claims. For anon requests both are null.

Turning it on

alter table public.bookings enable row level security;

Once enabled, a table with no policies is closed to anon and authenticated: selects return an empty list, and writes fail. You then open exactly what you need with policies. Enable RLS on every table in an exposed schema, including the ones your app doesn't call yet.

Writing policies: a bookings app

Say users book rooms, and each booking belongs to one user.

create table public.bookings (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  room text not null,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  check (ends_at > starts_at)
);

alter table public.bookings enable row level security;
create index on public.bookings (user_id);

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

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

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

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

What the parts mean:

• for select | insert | update | delete | all: which commands the policy covers.
• to authenticated: which roles it applies to. Always name the role; a policy without to applies to every role, including anon.
• using (...): which existing rows the user can see or act on. Rows that fail it are filtered out silently.
• with check (...): what new or changed rows must satisfy. A write that fails it is rejected with an error.

An update needs both: using picks the rows you may change, with check stops you from changing user_id to someone else. Several policies for the same command are combined with OR.

A public read policy, for example a list of rooms anyone can see:

create policy "Rooms are public"
  on public.rooms for select
  to anon, authenticated
  using (true);

Team and organisation access

For data shared by a team (here, a team_bookings table with a team_id column), check membership in a separate table. Doing the membership lookup inside a security definer function avoids policies that query their own table recursively:

create table public.team_members (
  team_id bigint not null,
  user_id uuid not null references auth.users (id) on delete cascade,
  primary key (team_id, user_id)
);
alter table public.team_members enable row level security;

create schema if not exists private;

create function private.my_team_ids()
returns setof bigint
language sql
stable
security definer
set search_path = ''
as $$
  select team_id from public.team_members where user_id = (select auth.uid());
$$;

-- the policy runs as the caller, so it needs to be able to reach the function
grant usage on schema private to authenticated;
grant execute on function private.my_team_ids() to authenticated;

create policy "Members read their team's bookings"
  on public.team_bookings for select
  to authenticated
  using (team_id in (select private.my_team_ids()));

The function lives in private, a schema the Data API doesn't expose, so nobody can call it directly over HTTP even though authenticated may execute it. See infinite recursion detected in policy and security definer functions and RLS.

Using JWT claims

auth.jwt() gives you the token's claims. Put anything used for authorisation in app_metadata, which only the server can change. Users can edit their own user_metadata, so never trust it in a policy.

create policy "Staff see every booking"
  on public.bookings for select
  to authenticated
  using ((select auth.jwt() -> 'app_metadata' ->> 'role') = 'staff');

For a fuller role system, see Roles, custom claims and RBAC. Avoid auth.role() and auth.email(), which are deprecated; use to authenticated and auth.jwt() ->> 'email' instead. See auth.role() and auth.email() are deprecated.

Testing policies

Studio runs as postgres, which bypasses RLS, so a query in the SQL editor shows every row. To test as a user, switch role inside a transaction:

begin;
set local role authenticated;
select set_config('request.jwt.claims',
  '{"sub":"6f1c0f7e-2b1d-4c55-9a59-0d7f4c3b8e21","role":"authenticated"}', true);

select * from public.bookings;               -- only this user's rows
insert into public.bookings (user_id, room, starts_at, ends_at)
values ('00000000-0000-0000-0000-000000000000', 'Blue', now(), now() + interval '1 hour');
-- fails: new row violates row-level security policy
rollback;

The MCP server's get_advisors tool also reports tables in public with RLS disabled, or with RLS enabled and no policies.

Keeping policies fast

Policies run for every row a query touches, so a slow policy makes every query slow.

• Wrap auth.uid() and auth.jwt() in (select ...), as in the examples. Postgres then evaluates them once per query instead of once per row.
• Index the columns policies filter on (user_id, team_id).
• Always add to authenticated (or the right role) so policies are skipped entirely for other roles.
• Add the same condition as a filter in your query too (.eq('user_id', user.id)), which helps the planner.

More in RLS performance best practices.

Views and functions

Views run with their owner's rights by default, which usually means RLS is bypassed. Create views that respect the caller's policies:

create view public.upcoming_bookings
with (security_invoker = true) as
  select * from public.bookings where starts_at > now();

Functions are security invoker by default and respect RLS. A security definer function runs as its owner and skips it; see Database functions.

Related

• Row Level Security, simplified
• Securing your API
• Storage access control
• select returns an empty array but the table has data