Row Level Security

Postgres Row Level Security (RLS) gives you granular authorization rules that run inside the database.

Use the guide in three parts:

• Understand Row Level Security explains how grants and policies combine to control access.
• Secure a table with RLS is the procedure to follow for every table in an exposed schema.
• RLS reference documents the helper functions and patterns you use inside a policy expression.

Read the first section when you're deciding how to model access. Go directly to the second section when you're ready to secure a table.

Understand Row Level Security#

What a policy does#

Policies are Postgres's rule engine. Each policy is attached to a table, and the policy is executed every time a table is accessed.

Think of a policy as adding a WHERE clause to every query. A policy like this:

1
create policy "Individuals can view their own todos."
2
on todos for select
3
to authenticated
4
using ( (select auth.uid()) = user_id );

That policy translates to this whenever a user selects from the todos table:

1
select *
2
from todos
3
where auth.uid() = todos.user_id;
4
-- Policy is implicitly added.

You write RLS rules in SQL, so a rule can express whatever access logic your app needs. Because RLS is a Postgres primitive, it also protects your data when it is reached through third-party tooling, which is what makes it "defense in depth". Combine RLS with Supabase Auth for end-to-end user security from the browser to the database.

Grants and policies#

Postgres runs two checks before a client touches a table. Grants decide whether a role can run an operation on the table at all. Policies decide which rows that operation applies to. Set both for every table you expose.

On existing projects, a new table in public starts with every privilege already granted to all three roles:

Adding policies doesn't take those grants back. A table protected only by policies still hands anon an insert path if you never revoke the grant.

Not every project grants these automatically. See Default privileges. Grant each role only the operations it needs.

A missing grant raises a 42501 error before any policy runs. When a request fails that your policy should allow, check the grants before you change the policy. To set them, see Lock down the table.

Authenticated and unauthenticated roles#

Supabase maps every request to one of the roles:

• anon: an unauthenticated request (the user is not logged in)
• authenticated: an authenticated request (the user is logged in)

These are Postgres Roles. You can use these roles within your Policies using the TO clause:

1
create policy "Profiles are viewable by everyone"
2
on profiles for select
3
to authenticated, anon
4
using ( true );
5
6
-- OR
7
8
create policy "Public profiles are viewable only by authenticated users"
9
on profiles for select
10
to authenticated
11
using ( true );

A policy that reads to anon using ( true ) grants every unauthenticated visitor read access to every row the role can already reach through grants. Use it only for data that is meant to be public.

Views and RLS#

Views bypass RLS by default because they are usually created with the postgres user. This is a feature of Postgres, which automatically creates views with security definer. A view over a protected table hands out every row its policies were meant to withhold, so a view needs the same attention as a table. To create one safely, see Expose a view safely.

Secure a table with RLS#

Follow these steps for every table in an exposed schema.

Lock down the table#

Run these statements in the SQL Editor for a one-off change, or in a migration to keep the change reproducible across environments. Grants and RLS belong in the same migration.

Enable RLS, then set the grants to match what each role does in your app:

1. Enable RLS on the table.

   1
   alter table public.reports enable row level security;

   Once RLS is enabled, no data is accessible through the API when using a publishable key, until you create policies.

2. Revoke any existing grants from both client roles.

   1
   revoke all on table public.reports from anon, authenticated;

3. Grant back only the privileges the role needs.

   1
   -- Signed-in users manage reports. Signed-out visitors get nothing.
   2
   grant select, insert, update, delete on table public.reports to authenticated;

Data that clients read but never write, such as a feed a backend job populates, gets no write grant at all:

1
revoke all on table public.weather_readings from anon, authenticated;
2
grant select on table public.weather_readings to anon, authenticated;

If new tables still receive automatic grants, see Revoke default privileges. To enable RLS automatically on every new table, see Event triggers.

Write the tests for this table in the same change. See Test your policies.

Write a policy for each operation#

Write a separate policy for select, insert, update, and delete. Postgres does not accept multiple operations in one for clause, and a for all policy hides which operation each rule was meant to cover.

These examples use a profiles table where each user manages only their own row:

1
create table profiles (
2
  id uuid primary key,
3
  user_id uuid references auth.users,
4
  avatar_url text
5
);
6
7
alter table profiles enable row level security;
8
9
revoke all on table profiles from anon, authenticated;
10
grant select, insert, update, delete on table profiles to authenticated;

Supabase provides helper functions that simplify RLS if you are using Supabase Auth. auth.uid() returns the ID of the user making the request.

SELECT policies#

You can specify select policies with the using clause.

1
create policy "Users can view their own profile."
2
on profiles for select
3
to authenticated
4
using ( (select auth.uid()) = user_id );

INSERT policies#

You can specify insert policies with the with check clause. The with check expression ensures that any new row adheres to the policy constraints, so a user cannot create a row that belongs to someone else.

1
create policy "Users can create their own profile."
2
on profiles for insert
3
to authenticated
4
with check ( (select auth.uid()) = user_id );

UPDATE policies#

You can specify update policies by combining the using and with check expressions. The using clause decides which existing rows can be updated. The with check clause decides what the resulting row is allowed to look like, which stops a user from reassigning user_id to someone else.

1
create policy "Users can update their own profile."
2
on profiles for update
3
to authenticated
4
using ( (select auth.uid()) = user_id )       -- checks the existing row
5
with check ( (select auth.uid()) = user_id ); -- checks the resulting row

If no with check expression is defined, the using expression decides both which rows are visible and which new rows are allowed.

DELETE policies#

You can specify delete policies with the using clause.

1
create policy "Users can delete their own profile."
2
on profiles for delete
3
to authenticated
4
using ( (select auth.uid()) = user_id );

Specify roles in your policies#

Always name the role a policy applies to, using the to clause. Instead of this:

1
create policy "rls_test_select" on rls_test
2
using ( auth.uid() = user_id );

Use:

1
create policy "rls_test_select" on rls_test
2
to authenticated
3
using ( (select auth.uid()) = user_id );

This prevents the policy ( (select auth.uid()) = user_id ) from running for any anon users, since the execution stops at the to authenticated step.

These three rules keep policies correct as a table grows. For the measured impact and for tuning beyond them, see Row Level Security performance.

Add indexes#

Add an index on every column your policies filter on. Postgres evaluates the policy against each candidate row, so an unindexed filter column turns a read into a sequential scan. For a policy like this:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( (select auth.uid()) = user_id );

You can add an index like:

1
create index userid
2
on test_table
3
using btree (user_id);

A column counts as indexed only when it comes first in a btree index. Postgres can't use a multi-column index to filter on a column that isn't the leading one, so a composite primary key indexes its first column and no others. A membership table keyed on (team_id, user_id) has no index on user_id:

1
create table team_members (
2
  team_id uuid references teams (id),
3
  user_id uuid references auth.users (id),
4
  primary key (team_id, user_id)
5
);
6
7
-- The primary key covers team_id. A policy filtering on user_id needs its own index.
8
create index team_members_user_id_idx
9
on team_members
10
using btree (user_id);

Call functions with select#

You can use select statement to improve policies that use functions. For example, instead of this:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( auth.uid() = user_id );

You can do:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using ( (select auth.uid()) = user_id );

This method works well for JWT functions like auth.uid() and auth.jwt() as well as security definer Functions. Wrapping the function causes an initPlan to be run by the Postgres optimizer, which allows it to "cache" the results per-statement, rather than calling the function on each row.

Expose a view safely#

In Postgres 15 and above, make a view obey the RLS policies of its underlying tables when invoked by anon and authenticated by setting security_invoker = true.

1
create view <VIEW_NAME>
2
with(security_invoker = true)
3
as select <QUERY>

In older versions of Postgres, protect your views by revoking access from the anon and authenticated roles, or by putting them in an unexposed schema.

Test your policies#

We recommend writing tests for every policy, in the same change that sets the grants and creates the policies. Tests are a fundamental part of a secure setup, and they give you a repeatable way to prove a policy behaves the way you intended.

A wrong policy fails quietly. Too permissive, and a query returns rows it shouldn't. Too strict, and it returns nothing and raises no error. Neither case surfaces as an error, so tests are how you find out.

Supabase runs database tests with pgTAP through the CLI. Test files are .sql files under supabase/tests/.

Anatomy of a policy test#

Each case sets an identity, runs one statement as that identity, and asserts the outcome. Three things decide whether the assertion means anything.

Identity. Switch role and identity between cases with set local role and set local request.jwt.claim.sub, so each assertion runs as the user it describes. Without the switch, every case runs as the same role and proves nothing about access.

Denials. A denied request doesn't always raise an error, so match the assertion to the way the denial happens:

• A missing grant raises 42501. Assert it with throws_ok.
• A with check violation raises 42501. Assert it with throws_ok.
• A using clause that filters the target row out raises nothing. The update or delete matches zero rows instead. Assert that no row changed.

Allowed writes. The absence of an error doesn't prove that anything changed. Add returning to the statement so one assertion covers both directions. An allowed write returns the changed row, and a write the policy filters out returns nothing.

Write and run the tests#

1. Create a test file:

   1
   supabase test new profiles_rls

2. Write the tests. Cover select, insert, update, and delete twice each, once for a request the policy allows and once for a request it denies, for anon as well as authenticated.

   supabase-test-helpers removes most of the setup below. It adds tests.create_supabase_user(), tests.authenticate_as(), and tests.rls_enabled(), so you don't hand-roll user seeding or role switching. See Advanced pgTAP testing for schema-wide assertions and a worked multi-tenant example.

3. Run the suite:

   1
   supabase test db

This example shows each of those techniques against a profiles table where authenticated holds every privilege, anon holds none, and each user reads and writes only their own row. Extend it to the remaining operations:

1
begin;
2
select plan(4);
3
4
insert into auth.users (id, email)
5
values
6
  ('11111111-1111-1111-1111-111111111111', 'owner@example.com'),
7
  ('22222222-2222-2222-2222-222222222222', 'other@example.com');
8
9
-- anon holds no grant, so the request stops before any policy runs.
10
set local role anon;
11
select throws_ok(
12
  $$select * from profiles$$,
13
  '42501',
14
  null,
15
  'anon cannot read profiles'
16
);
17
18
-- The owner writes their own row. returning proves the row changed.
19
set local role authenticated;
20
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
21
select results_eq(
22
  $$insert into profiles (id, user_id, avatar_url)
23
    values (
24
      gen_random_uuid(),
25
      '11111111-1111-1111-1111-111111111111',
26
      'owner.png'
27
    )
28
    returning avatar_url$$,
29
  array['owner.png'],
30
  'the owner creates their own profile'
31
);
32
33
-- A signed-in stranger holds the grant, so the policy is what stops them. The
34
-- using clause filters the row out, so these match nothing and raise nothing.
35
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
36
select is_empty(
37
  $$select * from profiles$$,
38
  'another user reads no profiles'
39
);
40
select is_empty(
41
  $$update profiles set avatar_url = 'stolen.png' returning avatar_url$$,
42
  'another user updates no profiles'
43
);
44
45
select * from finish();
46
rollback;

For CLI setup and more pgTAP helpers, see Testing your database.

RLS reference#

These are the functions and patterns available inside a policy expression.

Helper functions#

Supabase provides some helper functions that make it easier to write policies.

auth.uid()#

Returns the ID of the user making the request.

1
USING (auth.uid() = user_id)

1
null = user_id

1
USING (auth.uid() IS NOT NULL AND auth.uid() = user_id)

auth.jwt()#

Returns the JWT of the user making the request. Anything that you store in the user's raw_app_meta_data column or the raw_user_meta_data column will be accessible using this function. It's important to know the distinction between these two:

• raw_user_meta_data - can be updated by the authenticated user using the supabase.auth.update() function. It is not a good place to store authorization data.
• raw_app_meta_data - cannot be updated by the user, so it's a good place to store authorization data.

The auth.jwt() function is extremely versatile. For example, if you store some team data inside app_metadata, you can use it to determine whether a particular user belongs to a team. For example, if this was an array of IDs:

1
create policy "User is in team"
2
on my_table
3
to authenticated
4
using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams'));

MFA#

The auth.jwt() function can be used to check for Multi-Factor Authentication. For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2):

1
create policy "Restrict updates."
2
on profiles
3
as restrictive
4
for update
5
to authenticated using (
6
  (select auth.jwt()->>'aal') = 'aal2'
7
);

Use security definer functions#

A "security definer" function runs using the same role that created the function. This means that if you create a role with a superuser (like postgres), then that function will have bypassrls privileges. For example, if you had a policy like this:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  exists (
5
    select 1 from roles_table
6
    where (select auth.uid()) = user_id and role = 'good_role'
7
  )
8
);

We can instead create a security definer function which can scan roles_table without any RLS penalties:

1
create function private.has_good_role()
2
returns boolean
3
language plpgsql
4
security definer -- will run as the creator
5
set search_path = '' -- every name inside must be schema-qualified
6
as $$
7
begin
8
  return exists (
9
    select 1 from public.roles_table
10
    where (select auth.uid()) = user_id and role = 'good_role'
11
  );
12
end;
13
$$;
14
15
-- Update our policy to use this function:
16
create policy "rls_test_select"
17
on test_table
18
to authenticated
19
using ( (select private.has_good_role()) );

Set search_path = '' on every security definer function and schema-qualify the names inside it. Without a pinned search_path, a caller can point an unqualified name at their own object and run it with the function owner's privileges.

Bypassing Row Level Security#

Use a secret key for administrative tasks that need to bypass RLS. A secret key authorizes access through the service_role Postgres role, which has the bypassrls attribute. Never use a secret key in the browser or expose it to customers.

The JWT-based service_role key is a legacy alternative. Prefer a secret key where possible.

You can also create new Postgres Roles which can bypass Row Level Security using the "bypass RLS" privilege:

1
alter role "role_name" with bypassrls;

This can be useful for system-level access. Never share login credentials for any Postgres Role with this privilege.

Related content#

• Row Level Security performance: diagnose whether policies are your bottleneck, and tune ones that are already correct.
• Advanced pgTAP testing: schema-wide RLS test helpers and a worked multi-tenant example.
• Testing your database: the CLI test workflow that supabase test db runs.
• Securing your API: grants, dedicated schemas, and pre-request checks around the Data API.
• Column Level Security: restrict access to individual columns.
• supabase-test-helpers: a community extension that adds user creation and role impersonation helpers to pgTAP.