# Supabase Row Level Security: a practical guide with SQL

Row Level Security (RLS) is a Postgres feature that decides, row by row, what each database role can read or change. In Supabase it is the main access control, because the browser calls the database API directly with a public key. Enable RLS on every table in an exposed schema, write one policy per operation that compares the row's owner to auth.uid(), and test each policy as an anonymous user.

By Shai Goldenberg. Updated: 2026-11-13.

## What Row Level Security does

Postgres normally controls access per table: a role can select from `orders` or it cannot. Row Level Security adds a filter per row. A policy is a SQL expression attached to a table. Postgres evaluates it for every row a query touches and hides or rejects rows where it is not true.

In a traditional app, your server checks permissions before it queries the database. In Supabase, the browser or mobile app calls the auto-generated API directly using a publishable key (the anon key in older projects). That key is public. RLS is the layer that stands between that key and your data, which is why missing policies are the first item in our list of [vibe coding security risks](https://goldenroz.com/blog/vibe-coding-security-risks/).

Supabase maps each request to a Postgres role:

| Role | When it is used | RLS applies |
|---|---|---|
| `anon` | Request with the publishable key and no user token | Yes |
| `authenticated` | Request with a logged-in user's token | Yes |
| `service_role` | Request with the secret key | No, it bypasses RLS |

## Enable RLS on every table

```sql
create table public.documents (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null default auth.uid() references auth.users (id),
  title text not null,
  created_at timestamptz not null default now()
);

alter table public.documents enable row level security;
```

Once RLS is enabled and no policy exists, the API returns nothing for this table. That is the safe starting state. Tables created through a SQL migration do not get RLS unless the migration says so, so do not assume it is on. This query lists the tables that are missing it:

```sql
select tablename
from pg_tables
where schemaname = 'public' and rowsecurity = false;
```

## Write policies with auth.uid()

`auth.uid()` returns the ID of the user making the request, taken from their token. A policy compares it to an owner column.

```sql
create policy "Users read own documents"
on public.documents for select
to authenticated
using ( (select auth.uid()) = user_id );

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

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

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

Four details in that SQL matter.

- **`using` and `with check` do different jobs.** `using` filters existing rows for select, update and delete. `with check` validates the new row for insert and update. An update policy without `with check` reuses the `using` expression for both.
- **`to authenticated` names the role.** Without it, the policy also runs for `anon`. Naming the role is clearer and lets Postgres skip the policy for other roles.
- **`(select auth.uid())` is a performance pattern.** Wrapping the call in a select lets the planner evaluate it once per statement instead of once per row. Supabase recommends this for functions whose result does not depend on the row.
- **Update needs a select policy.** Postgres has to read a row to update it, so an update policy without a matching select policy will not behave as expected.

Add an index on the column the policy filters on:

```sql
create index documents_user_id_idx on public.documents (user_id);
```

## Common mistakes

### Forgetting that auth.uid() can be null

For a request with no user token, `auth.uid()` returns null. In SQL, `null = user_id` is never true, so the row is filtered out and the caller gets an empty result with no error.

That behavior is safe, but it confuses people. The app shows no data, the builder or an AI agent "fixes" it by loosening the policy to `using (true)`, and the table is now open to everyone. The right fix is to find out why the request has no session. To make the intent explicit, Supabase suggests writing the null check into the policy:

```sql
using ( auth.uid() is not null and auth.uid() = user_id )
```

The related mistake is a policy with `to anon using (true)`. That grants every visitor read access to every row. Use it only for data that is public on purpose.

### Views without security_invoker

A view runs with the privileges of the role that created it, usually `postgres`, which bypasses RLS. A view over a protected table can return every row even though the table's policies are correct. On Postgres 15 and later:

```sql
create view public.document_titles
with (security_invoker = true)
as select id, title from public.documents;
```

For an existing view, `alter view public.document_titles set (security_invoker = true);` does the same. On older Postgres versions, revoke access to the view from `anon` and `authenticated` or move it to a schema the API does not expose.

### Service role misuse

The secret key bypasses RLS. Three ways this goes wrong:

1. The key is used in frontend code. Anyone can extract it and read the whole database.
2. A server function creates a service-role client for every request, including ones made on behalf of a user. The function now has to re-implement every access rule by hand, and one missed check is a leak.
3. The key sits in a public environment variable or in git history.

In server functions, create the Supabase client with the caller's token so RLS still applies, and use the secret key only for specific administrative steps.

### SECURITY DEFINER functions open to everyone

A `security definer` function runs as its owner and bypasses RLS. Postgres grants execute on new functions to all roles by default, and functions in the public schema are callable through the API. Lock them down:

```sql
revoke execute on function public.admin_report from public;
revoke execute on function public.admin_report from anon, authenticated;
grant execute on function public.admin_report to service_role;
```

Always add `set search_path = ''` to definer functions and schema-qualify every table name inside them.

### Authorization data in user metadata

A logged-in user can edit their own `user_metadata`. A policy that reads a role or plan from it lets users promote themselves. Store authorization data in `app_metadata`, which users cannot change, or in a table.

## Multi-tenant isolation

In a multi-tenant app, rows belong to an organization, and users belong to one or more organizations. Put a `tenant_id` on every tenant-owned table and check membership in the policy.

```sql
create table public.memberships (
  tenant_id uuid not null,
  user_id uuid not null references auth.users (id),
  role text not null default 'member',
  primary key (tenant_id, user_id)
);
alter table public.memberships enable row level security;

create schema if not exists private;

create function private.user_tenant_ids()
returns setof uuid
language sql
stable
security definer
set search_path = ''
as $$
  select tenant_id
  from public.memberships
  where user_id = (select auth.uid());
$$;

grant usage on schema private to authenticated;
grant execute on function private.user_tenant_ids to authenticated;

create policy "Members read tenant projects"
on public.projects for select
to authenticated
using ( tenant_id in (select private.user_tenant_ids()) );
```

The helper function lives in a `private` schema that the API does not expose, so nobody can call it as an endpoint. It is `security definer` so that it can read `memberships` without triggering that table's own policies, which avoids recursive policy errors.

You can also read tenant IDs from the token with `auth.jwt() -> 'app_metadata'`. It is faster, but a token is not always current: removing a user from a tenant does not take effect until their token refreshes. For anything that handles money, a table lookup is the safer choice. Our post on [multi-tenant architecture](https://goldenroz.com/blog/multi-tenant-architecture/) compares this shared-schema model with schema-per-tenant and database-per-tenant.

## Test policies as an anonymous user

A policy you have not tested is a guess. Test from two places.

**In SQL.** Switch role inside a transaction and run the query the attacker would run:

```sql
begin;
set local role anon;
select count(*) from public.documents;   -- expect 0
rollback;

begin;
set local role authenticated;
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
select count(*) from public.documents;   -- expect only this user's rows
rollback;
```

**Over HTTP.** Call the API with only the publishable key, the way a stranger would:

```bash
curl "https://YOUR-PROJECT-REF.supabase.co/rest/v1/documents?select=*" \
  -H "apikey: YOUR_PUBLISHABLE_KEY"
```

An empty array is a pass. Rows are a finding. Then log in as user A and request user B's rows by ID.

Turn these into automated tests. Supabase supports pgTAP tests run with `supabase test db`. In the marketplace platform Shai runs, with about 145 tables under RLS and 155 policies, an automated job probes sensitive functions as an anonymous user every day and closes any permission that should not be open. Manual testing catches today's mistakes. A scheduled probe catches next month's.

## What to do next

Run the missing-RLS query and the anonymous curl test on your project today. If either returns something unexpected, fix that table before anything else ships. If your app holds payment or personal data and you want every table, policy, function and view reviewed, with fixes delivered as pull requests, see our [security audit](https://goldenroz.com/security-audit/). For new products, our [SaaS development](https://goldenroz.com/saas-development/) work writes policies in the same migration that creates each table.

## Questions buyers ask

### What happens if I enable RLS without adding any policies?

Every request through the API with the publishable key returns no rows and every write is rejected. RLS is deny by default. Roles that bypass RLS, such as the service role and the table owner, are not affected.

### Does the service role key respect RLS?

No. The secret key, called the service role key in older projects, maps to a Postgres role with the bypassrls attribute. Use it only in trusted server code and never ship it to a browser or mobile app.

### Why does my Supabase query return an empty array instead of an error?

RLS filters rows silently. If no policy grants the current role access to a row, the row is left out of the result and no error is raised. Check which role the request runs as and whether auth.uid() is null because the request carried no user token.

### Do views respect Row Level Security?

Not by default. A view runs with the privileges of its creator, which usually bypasses RLS. On Postgres 15 and later, create the view with security_invoker = true so that the caller's policies apply.

### Does RLS slow down queries?

It can if policies are written carelessly. Index the columns your policies filter on, wrap auth.uid() in a select so it is evaluated once per statement, and name the role with a to clause so the policy is skipped for other roles.

---
Page: https://goldenroz.com/blog/supabase-row-level-security/
Book a free one-hour call: https://calendly.com/daniarozin/daniel-rozin-consultation-1-hour-clone
