# Row Level Security

> Protect data exposed through the Data API with Postgres Row Level Security: enabling it, writing policies with auth.uid() and auth.jwt(), team access, testing and performance.

Source: https://supahost.app/docs/database/row-level-security · Updated 2026-11-11

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

| Request made with | Postgres role | RLS |
|---|---|---|
| anon key, no user signed in | `anon` | Applies |
| anon key plus a user's access token | `authenticated` | Applies |
| service_role key | `service_role` | Bypassed |
| Studio, `supahost-psql`, the SSH tunnel | `postgres` | Bypassed |

`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

```sql
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](/docs/api/exposed-schemas), 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.

```sql
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:

```sql
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:

```sql
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](/kb/infinite-recursion-policy) and [security definer functions and RLS](/kb/security-definer-functions).

## 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.

```sql
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](/docs/auth/custom-claims-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](/kb/deprecated-auth-role).

## 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:

```sql
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](/kb/rls-performance).

## 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:

```sql
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](/docs/database/functions).

## Related

- [Row Level Security, simplified](/kb/rls-simplified)
- [Securing your API](/docs/api/securing-your-api)
- [Storage access control](/docs/storage/access-control)
- [select returns an empty array but the table has data](/kb/empty-data-array)