Row Level Security

Using Row Level Security with Supabase Auth.

Postgres Row Level Security (RLS) is a feature of Postgres that allows you to control which users are permitted to perform SELECT/INSERT/UPDATE/DELETE statements on specific rows within tables and views. For example, you could restrict a blog_post table such that the current user is only allowed to UPDATE rows where their user id is set in the table's author_id column.

Supabase Auth is designed to work perfectly with RLS.

You can use RLS to create Policies that are incredibly powerful and flexible, allowing you to write complex SQL rules which fit your unique business needs.

Policies#

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

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

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

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

Authenticated and anonymous roles#

Supabase Auth maps every request to one of the roles:

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

These are actually Postgres Roles, and so they have significant value for the performance of your RLS Policies. You can use these roles within your Policies using the TO clause:

create policy "Profiles are viewable by everyone"
on profiles for select
to authenticated, anon
using ( true );

-- OR 

create policy "Public profiles are viewable only by authenticated users"
on profiles for select
to authenticated
using ( true );

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 app_metadata column or the user_metadata column will be accessible using this function. It's important to know the distinction between these two:

user_metadata - can be updated by the authenticated user using the supabase.auth.update() function. It is not a good place to store authorization data.
app_metadata - 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:

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

caution

Keep in mind that a JWT is not always "fresh". In the example above, even if you remove a user from a team and update the app_metadata field, that will not be reflected using auth.jwt() until the user's JWT is refreshed.

Also, if you are using Cookies for Auth, then you must be mindful of the JWT size. Some browsers are limited to 4096 bytes for each cookie, and so the total size of your JWT should be small enough to fit inside this limitation.

Important considerations#

We recommend reading the Row Level Security guide in the database section to learn more about Postgres RLS. When using Postgres on Supabase there are some important things to keep in mind to maintain data security.

Never use a service key on the client#

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.

note

Supabase will adhere to the RLS policy of the signed-in user, even if the client library is initialized with a Service Key.

Always enable RLS on public tables#

You should always enable RLS on tables created in a public schema. This is considered "default safe". Unfortunately this is not enabled by default on Postgres, so you will need to keep this in mind - especially if you are using the SQL Editor or database migrations. RLS is already enabled by default if you create a table using the Table Editor. If you want to allow public access to a table, just add a Policy with true:

create policy "Allow public access"
on my_table for select
using ( true );

Using external authorization systems#

If you want to use another authorization method for your applications that's also fine. Supabase is "just Postgres", so if your application works with Postgres, then it also works with Supabase. If you take this path, don't put your tables in the public schema - instead create a new schema for your tables and functions:

create schema private;

create table private.employees (
  id serial primary key,
  name text
);

If you do put anything in the public schema, make sure to enable RLS (you don't need to add any policies):

create table profiles (
  id serial primary key,
  email text
);

alter table profiles enable row level security;

This makes the tables inaccessible via the APIs.

Usage#

Row Level Security is extremely versatile, since it simply uses SQL to express access rules for your data.

Using functions#

You can use any Postgres function inside a Policy. The Helper Functions above are simply Postgres functions we've made available in the auth schema. This is an example which:

Creates a table called profiles in the public schema (default schema).
Enables RLS.
Creates a policy which allows logged in users to update their own data, using the auth.uid() function.

-- 1. Create table
create table profiles (
  id uuid references auth.users,
  avatar_url text
);

-- 2. Enable RLS
alter table profiles enable row level security;

-- 3. Create Policy
create policy "Users can update their own profiles"
on profiles for update
to authenticated
using (
  auth.uid() = id
);

Note: If you want to use upsert operations, the user needs to have INSERT, UPDATE, and SELECT permissions.

Using joins#

Policies can include table joins. This example shows how you can query "external" tables to build more advanced rules. RLS policies are executed on every access of the table, so be careful to make sure that policies are efficient.

-- 1. Create a table of teams
create table teams (
  id serial primary key,
  name text
);

-- 2. Create many to many join
create table members (
  team_id bigint references teams,
  user_id uuid references auth.users
);

-- 3. Enable RLS
alter table teams enable row level security;

-- 4. Create Policy
create policy "Team members can update team details if they belong to the team"
  on teams
  for update using (
    auth.uid() in (
      select user_id from members
      where team_id = id
    )
  );

An important note here: if RLS is also enabled for members, the user must also have read (select) access to members. Otherwise the joined query will not yield any results. Another alternative is to use a "security definer" function which is created by a user with bypassrls privileges.

Using security definer functions#

You can use security definer functions inside Policies. This is useful in a many-to-many relationships, and important for performance. Following the teams and members example from above, this example shows how you can use the security definer function in combination with a policy to control access to the members table.

-- 1. Create a table of teams
create table teams (
  id serial primary key,
  name text
);

-- 2. Create many to many join
create table members (
  team_id bigint references teams,
  user_id uuid references auth.users
);

-- 2.  Enable RLS
alter table teams enable row level security;
alter table members enable row level security;

-- 3.  Create security definer function, which should be run as "postgres"
create function private.get_teams_for_authenticated_user()
returns setof bigint
language sql
security definer
set search_path = public
stable
as $$
  select team_id
  from members
  where user_id = auth.uid()
$$;

-- 4. Create Policy
create policy "Team members can update team members if they belong to the team."
on members
for all using (
  team_id in (
    select private.get_teams_for_authenticated_user()
  )
);

Using built-in functions#

Postgres has a number of built-in functions. Most commonly you'll use in() and any() which will match a column's value to a list of values.

You can use any Postgres functions inside Policies. For example, we can use the right(string, n) function to match email domains:

create policy "Only Supabase staff can update the leaderboard"
on leaderboard
to authenticated
for update using (
  right(auth.jwt() ->> 'email', 13) = '@supabase.com'
);

Using Multi-factor Authentication#

RLS can be combined with Multi-Factor Authentication in Supabase Auth. For example, you could restrict a user from updating their profile unless they have at least 2 levels of authentication (Assurance Level 2):

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

Edit this page on GitHub