Row Level Security

When you need granular authorization rules, nothing beats Postgres's Row Level Security (RLS).

Row Level Security in Supabase#

1
-- Take back the privileges granted automatically to client roles.
2
revoke all on table <schema_name>.<table_name> from anon, authenticated;
3
4
-- Grant back only what each role needs.
5
grant select on table <schema_name>.<table_name> to anon, authenticated;
6
grant insert, update, delete on table <schema_name>.<table_name> to authenticated;
7
8
alter table <schema_name>.<table_name>
9
enable row level security;

RLS is incredibly powerful and flexible, allowing you to write complex SQL rules that fit your unique business needs. RLS can be combined with Supabase Auth for end-to-end user security from the browser to the database.

RLS is a Postgres primitive and can provide "defense in depth" to protect your data from malicious actors even when accessed through third-party tooling.

Policies#

Policies are Postgres's rule engine. Policies are easy to understand once you get the hang of them. Each policy is attached to a table, and the policy is executed every time a table is accessed.

You can just think of them as adding a WHERE clause to every query. For example a policy like this ...

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

.. would translate to this whenever a user tries to select from the todos table:

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

Enabling Row Level Security#

You can enable RLS for any table using the enable row level security clause:

1
alter table "table_name" enable row level security;

Once you have enabled RLS, no data will be accessible via the API when using a publishable key, until you create policies.

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.

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.

Set the grants for a 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.

Set the grants to match what each role does in your app:

1. Revoke the automatic grants from both client roles.

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

2. 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;

3. Enable RLS on the table and write policies that decide which rows each role reaches.

For data that clients read but never write, such as a feed a backend job populates, grant no writes in step 2:

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

To stop new tables from receiving the automatic grants in the first place, see Revoke default privileges.

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

Auto-enable RLS for new tables#

If you want RLS enabled automatically for new tables, you can create an event trigger that runs after table creation. This uses a Postgres event trigger to call ALTER TABLE ... ENABLE ROW LEVEL SECURITY on each newly created table.

1
CREATE OR REPLACE FUNCTION rls_auto_enable()
2
RETURNS EVENT_TRIGGER
3
LANGUAGE plpgsql
4
SECURITY DEFINER
5
SET search_path = pg_catalog
6
AS $$
7
DECLARE
8
  cmd record;
9
BEGIN
10
  FOR cmd IN
11
    SELECT *
12
    FROM pg_event_trigger_ddl_commands()
13
    WHERE command_tag IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
14
      AND object_type IN ('table','partitioned table')
15
  LOOP
16
     IF cmd.schema_name IS NOT NULL AND cmd.schema_name IN ('public') AND cmd.schema_name NOT IN ('pg_catalog','information_schema') AND cmd.schema_name NOT LIKE 'pg_toast%' AND cmd.schema_name NOT LIKE 'pg_temp%' THEN
17
      BEGIN
18
        EXECUTE format('alter table if exists %s enable row level security', cmd.object_identity);
19
        RAISE LOG 'rls_auto_enable: enabled RLS on %', cmd.object_identity;
20
      EXCEPTION
21
        WHEN OTHERS THEN
22
          RAISE LOG 'rls_auto_enable: failed to enable RLS on %', cmd.object_identity;
23
      END;
24
     ELSE
25
        RAISE LOG 'rls_auto_enable: skip % (either system schema or not in enforced list: %.)', cmd.object_identity, cmd.schema_name;
26
     END IF;
27
  END LOOP;
28
END;
29
$$;
30
31
DROP EVENT TRIGGER IF EXISTS ensure_rls;
32
CREATE EVENT TRIGGER ensure_rls
33
ON ddl_command_end
34
WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO')
35
EXECUTE FUNCTION rls_auto_enable();

Note that this applies to tables created after the trigger is installed. Existing tables still need RLS enabled manually.

1
USING (auth.uid() = user_id)

1
null = user_id

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

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 );

Creating policies#

Policies are SQL logic that you attach to a Postgres table. You can attach as many policies as you want to each table.

Supabase provides some helpers that simplify RLS if you're using Supabase Auth. We'll use these helpers to illustrate some basic policies:

SELECT policies#

You can specify select policies with the using clause.

Say you have a table called profiles in the public schema and you want to enable read access to everyone.

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Public profiles are visible to everyone."
13
on profiles for select
14
to anon         -- the Postgres Role (recommended)
15
using ( true ); -- the actual Policy

Alternatively, if you only wanted users to be able to see their own profiles:

1
create policy "User can see their own profile only."
2
on profiles
3
for select 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 data adheres to the policy constraints.

Say you have a table called profiles in the public schema and you only want users to create a profile for themselves. In that case, we want to check their User ID matches the value that they are trying to insert:

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can create a profile."
13
on profiles for insert
14
to authenticated                          -- the Postgres Role (recommended)
15
with check ( (select auth.uid()) = user_id );      -- the actual Policy

UPDATE policies#

You can specify update policies by combining both the using and with check expressions.

The using clause represents the condition that must be true for the update to be allowed, and with check clause ensures that the updates made adhere to the policy constraints.

Say you have a table called profiles in the public schema and you only want users to update their own profile.

You can create a policy where the using clause checks if the user owns the profile being updated. And the with check clause ensures that, in the resultant row, users do not change the user_id to a value that is not equal to their User ID, maintaining that the modified profile still meets the ownership condition.

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can update their own profile."
13
on profiles for update
14
to authenticated                    -- the Postgres Role (recommended)
15
using ( (select auth.uid()) = user_id )       -- checks if the existing row complies with the policy expression
16
with check ( (select auth.uid()) = user_id ); -- checks if the new row complies with the policy expression

If no with check expression is defined, then the using expression will be used both to determine which rows are visible (normal USING case) and which new rows will be allowed to be added (WITH CHECK case).

DELETE policies#

You can specify delete policies with the using clause.

Say you have a table called profiles in the public schema and you only want users to be able to delete their own profile:

1
-- 1. Create table
2
create table profiles (
3
  id uuid primary key,
4
  user_id uuid references auth.users,
5
  avatar_url text
6
);
7
8
-- 2. Enable RLS
9
alter table profiles enable row level security;
10
11
-- 3. Create Policy
12
create policy "Users can delete a profile."
13
on profiles for delete
14
to authenticated                     -- the Postgres Role (recommended)
15
using ( (select auth.uid()) = user_id );      -- the actual Policy

Views#

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.

In Postgres 15 and above, you can make a view obey the RLS policies of the underlying tables when invoked by anon and authenticated roles 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.

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.

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
);

Bypassing Row Level Security#

Supabase provides special "Service" keys, which can be used to bypass RLS. These should never be used in the browser or exposed to customers, but they are useful for administrative tasks.

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. You should never share login credentials for any Postgres Role with this privilege.

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 the tests directory and a test file:

   1
   mkdir -p supabase/tests
   2
   touch supabase/tests/profiles_rls.test.sql

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. Cover anon as well as authenticated.

3. Run the suite:

   1
   supabase test db

This example tests a profiles table where authenticated holds every privilege, anon holds none, and each user reads and writes only their own row:

1
begin;
2
select plan(11);
3
4
-- Seed two users. The rows come later, through the policies under test.
5
insert into auth.users (id, email)
6
values
7
  ('11111111-1111-1111-1111-111111111111', 'owner@example.com'),
8
  ('22222222-2222-2222-2222-222222222222', 'other@example.com');
9
10
-- Signed-out visitors hold no grant, so the request stops before any policy runs.
11
set local role anon;
12
select throws_ok(
13
  $$select * from profiles$$,
14
  '42501',
15
  null,
16
  'anon cannot read profiles'
17
);
18
select throws_ok(
19
  $$insert into profiles (id, user_id)
20
    values (gen_random_uuid(), '11111111-1111-1111-1111-111111111111')$$,
21
  '42501',
22
  null,
23
  'anon cannot insert a profile'
24
);
25
26
-- The owner reads and writes their own row.
27
set local role authenticated;
28
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
29
select results_eq(
30
  $$insert into profiles (id, user_id, avatar_url)
31
    values (
32
      gen_random_uuid(),
33
      '11111111-1111-1111-1111-111111111111',
34
      'owner.png'
35
    )
36
    returning avatar_url$$,
37
  array['owner.png'],
38
  'the owner creates their own profile'
39
);
40
select results_eq(
41
  $$select avatar_url from profiles$$,
42
  array['owner.png'],
43
  'the owner reads their own profile'
44
);
45
select results_eq(
46
  $$update profiles set avatar_url = 'updated.png' returning avatar_url$$,
47
  array['updated.png'],
48
  'the owner updates their own profile'
49
);
50
51
-- The with check clause rejects the row, which raises.
52
select throws_ok(
53
  $$insert into profiles (id, user_id)
54
    values (gen_random_uuid(), '22222222-2222-2222-2222-222222222222')$$,
55
  '42501',
56
  null,
57
  'the owner cannot create a profile for someone else'
58
);
59
60
-- A signed-in stranger holds the grant, so the policy is what stops them. The
61
-- using clause filters the row out, so these match nothing and raise nothing.
62
set local request.jwt.claim.sub = '22222222-2222-2222-2222-222222222222';
63
select is_empty(
64
  $$select * from profiles$$,
65
  'another user reads no profiles'
66
);
67
select is_empty(
68
  $$update profiles set avatar_url = 'stolen.png' returning avatar_url$$,
69
  'another user updates no profiles'
70
);
71
select is_empty(
72
  $$delete from profiles returning id$$,
73
  'another user deletes no profiles'
74
);
75
76
-- The row is still there, still holding the owner's value.
77
set local request.jwt.claim.sub = '11111111-1111-1111-1111-111111111111';
78
select results_eq(
79
  $$select avatar_url from profiles$$,
80
  array['updated.png'],
81
  'the other user changed nothing'
82
);
83
select results_eq(
84
  $$delete from profiles returning avatar_url$$,
85
  array['updated.png'],
86
  'the owner deletes their own profile'
87
);
88
89
select * from finish();
90
rollback;

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

RLS performance recommendations#

Every authorization system has an impact on performance. While row level security is powerful, the performance impact is important to keep in mind. This is especially true for queries that scan every row in a table - like many select operations, including those using limit, offset, and ordering.

Based on a series of tests, we have a few recommendations for RLS:

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);

Benchmarks#

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.

Benchmarks#

Add filters to every query#

Policies are "implicit where clauses," so it's common to run select statements without any filters. This is a bad pattern for performance. Instead of doing this (JS client example):

1
const { data } = supabase
2
  .from('table')
3
  .select()

You should always add a filter:

1
const { data } = supabase
2
  .from('table')
3
  .select()
4
  .eq('user_id', userId)

Even though this duplicates the contents of the Policy, Postgres can use the filter to construct a better query plan.

Benchmarks#

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
as $$
6
begin
7
  return exists (
8
    select 1 from roles_table
9
    where (select auth.uid()) = user_id and role = 'good_role'
10
  );
11
end;
12
$$;
13
14
-- Update our policy to use this function:
15
create policy "rls_test_select"
16
on test_table
17
to authenticated
18
using ( (select private.has_good_role()) );

Minimize joins#

You can often rewrite your Policies to avoid joins between the source and the target table. Instead, try to organize your policy to fetch all the relevant data from the target table into an array or set, then you can use an IN or ANY operation in your filter.

For example, this is an example of a slow policy which joins the source test_table to the target team_user:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  (select auth.uid()) in (
5
    select user_id
6
    from team_user
7
    where team_user.team_id = team_id -- joins to the source "test_table.team_id"
8
  )
9
);

We can rewrite this to avoid this join, and instead select the filter criteria into a set:

1
create policy "rls_test_select" on test_table
2
to authenticated
3
using (
4
  team_id in (
5
    select team_id
6
    from team_user
7
    where user_id = (select auth.uid()) -- no join
8
  )
9
);

In this case you can also consider using a security definer function to bypass RLS on the join table:

Benchmarks#

Specify roles in your policies#

Always use the Role of inside your policies, specified by the TO operator. For example, instead of this query:

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.

Benchmarks#

More resources#

• Testing your database
• RLS Guide and Best Practices
• Community repo on testing RLS using pgTAP and dbdev