Database

Row Level Security

Secure your data using Postgres Row Level Security.

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

Row Level Security in Supabase

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


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

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


_10
select *
_10
from todos
_10
where auth.uid() = todos.user_id;
_10
-- Policy is implicitly added.

Enabling Row Level Security

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


_10
alter table "table_name" enable row level security;

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

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 actually Postgres Roles. You can use these roles within your Policies using the TO clause:


_11
create policy "Profiles are viewable by everyone"
_11
on profiles for select
_11
to authenticated, anon
_11
using ( true );
_11
_11
-- OR
_11
_11
create policy "Public profiles are viewable only by authenticated users"
_11
on profiles for select
_11
to authenticated
_11
using ( true );

Creating policies

Policies are simply 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.

Let's say you have a table called profiles in the public schema and you want to enable read access to everyone.


_15
-- 1. Create table
_15
create table profiles (
_15
id uuid primary key,
_15
user_id references auth.users,
_15
avatar_url text
_15
);
_15
_15
-- 2. Enable RLS
_15
alter table profiles enable row level security;
_15
_15
-- 3. Create Policy
_15
create policy "Public profiles are visible to everyone."
_15
on profiles for select
_15
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:


_10
create policy "User can see their own profile only."
_10
on profiles
_10
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.

Let's say you have a table called profiles in the public schema and you only want users to be able 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:


_15
-- 1. Create table
_15
create table profiles (
_15
id uuid primary key,
_15
user_id uuid references auth.users,
_15
avatar_url text
_15
);
_15
_15
-- 2. Enable RLS
_15
alter table profiles enable row level security;
_15
_15
-- 3. Create Policy
_15
create policy "Users can create a profile."
_15
on profiles for insert
_15
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.

Let's say you have a table called profiles in the public schema and you only want users to be able 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.


_16
-- 1. Create table
_16
create table profiles (
_16
id uuid primary key,
_16
user_id uuid references auth.users,
_16
avatar_url text
_16
);
_16
_16
-- 2. Enable RLS
_16
alter table profiles enable row level security;
_16
_16
-- 3. Create Policy
_16
create policy "Users can update their own profile."
_16
on profiles for update
_16
to authenticated -- the Postgres Role (recommended)
_16
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.

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


_15
-- 1. Create table
_15
create table profiles (
_15
id uuid primary key,
_15
user_id uuid references auth.users,
_15
avatar_url text
_15
);
_15
_15
-- 2. Enable RLS
_15
alter table profiles enable row level security;
_15
_15
-- 3. Create Policy
_15
create policy "Users can delete a profile."
_15
on profiles for delete
_15
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.


_10
create view <VIEW_NAME>
_10
with(security_invoker = true)
_10
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:


_10
create policy "User is in team"
_10
on my_table
_10
to authenticated
_10
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):


_10
create policy "Restrict updates."
_10
on profiles
_10
as restrictive
_10
for update
_10
to authenticated using (
_10
(select auth.jwt()->>'aal') = 'aal2'
_10
);

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:


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

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

Make sure you've added indexes on any columns used within the Policies which are not already indexed (or primary keys). For a Policy like this:


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

You can add an index like:


_10
create index userid
_10
on test_table
_10
using btree (user_id);

Benchmarks

TestBefore (ms)After (ms)% ImprovementChange
test1-indexed171< 0.199.94%
Before:
No index

After:
user_id indexed

Call functions with select

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


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

You can do:


_10
create policy "rls_test_select" on test_table
_10
to authenticated
_10
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

TestBefore (ms)After (ms)% ImprovementChange
test2a-wrappedSQL-uid179994.97%
Before:
auth.uid() = user_id

After:
(select auth.uid()) = user_id
test2b-wrappedSQL-isadmin11,000799.94%
Before:
is_admin() table join

After:
(select is_admin()) table join
test2c-wrappedSQL-two-functions11,0001099.91%
Before:
is_admin() OR auth.uid() = user_id

After:
(select is_admin()) OR (select auth.uid() = user_id)
test2d-wrappedSQL-sd-fun178,0001299.993%
Before:
has_role() = role

After:
(select has_role()) = role
test2e-wrappedSQL-sd-fun-array1730001699.991%
Before:
team_id=any(user_teams())

After:
team_id=any(array(select user_teams()))

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


_10
const { data } = supabase
_10
.from('table')
_10
.select()

You should always add a filter:


_10
const { data } = supabase
_10
.from('table')
_10
.select()
_10
.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

TestBefore (ms)After (ms)% ImprovementChange
test3-addfilter171994.74%
Before:
auth.uid() = user_id

After:
add .eq or where on user_id

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:


_10
create policy "rls_test_select" on test_table
_10
to authenticated
_10
using (
_10
exists (
_10
select 1 from roles_table
_10
where (select auth.uid()) = user_id and role = 'good_role'
_10
)
_10
);

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


_18
create function private.has_good_role()
_18
returns boolean
_18
language plpgsql
_18
security definer -- will run as the creator
_18
as $$
_18
begin
_18
return exists (
_18
select 1 from roles_table
_18
where (select auth.uid()) = user_id and role = 'good_role'
_18
);
_18
end;
_18
$$;
_18
_18
-- Update our policy to use this function:
_18
create policy "rls_test_select"
_18
on test_table
_18
to authenticated
_18
using ( 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:


_10
create policy "rls_test_select" on test_table
_10
to authenticated
_10
using (
_10
(select auth.uid()) in (
_10
select user_id
_10
from team_user
_10
where team_user.team_id = team_id -- joins to the source "test_table.team_id"
_10
)
_10
);

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


_10
create policy "rls_test_select" on test_table
_10
to authenticated
_10
using (
_10
team_id in (
_10
select team_id
_10
from team_user
_10
where user_id = (select auth.uid()) -- no join
_10
)
_10
);

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

Benchmarks

TestBefore (ms)After (ms)% ImprovementChange
test5-fixed-join9,0002099.78%
Before:
auth.uid() in table join on col

After:
col in table join on auth.uid()

Specify roles in your policies

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


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

Use:


_10
create policy "rls_test_select" on rls_test
_10
to authenticated
_10
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

TestBefore (ms)After (ms)% ImprovementChange
test6-To-role170< 0.199.78%
Before:
No TO policy

After:
TO authenticated (anon accessing)

Resources