Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Home

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


_10
create policy "Individuals can view their own todos."
_10
on todos for select
_10
using ( 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.

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:


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

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:


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

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.

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:


_10
create policy "Allow public access"
_10
on my_table for select
_10
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:


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

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


_10
create table profiles (
_10
id serial primary key,
_10
email text
_10
);
_10
_10
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:

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

_16
-- 1. Create table
_16
create table profiles (
_16
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 profiles"
_16
on profiles for update
_16
to authenticated
_16
using (
_16
auth.uid() = id
_16
);

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.


_24
-- 1. Create a table of teams
_24
create table teams (
_24
id serial primary key,
_24
name text
_24
);
_24
_24
-- 2. Create many to many join
_24
create table members (
_24
team_id bigint references teams,
_24
user_id uuid references auth.users
_24
);
_24
_24
-- 3. Enable RLS
_24
alter table teams enable row level security;
_24
_24
-- 4. Create Policy
_24
create policy "Team members can update team details if they belong to the team"
_24
on teams
_24
for update using (
_24
auth.uid() in (
_24
select user_id from members
_24
where team_id = id
_24
)
_24
);

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.


_37
-- 1. Create a table of teams
_37
create table teams (
_37
id serial primary key,
_37
name text
_37
);
_37
_37
-- 2. Create many to many join
_37
create table members (
_37
team_id bigint references teams,
_37
user_id uuid references auth.users
_37
);
_37
_37
-- 2. Enable RLS
_37
alter table teams enable row level security;
_37
alter table members enable row level security;
_37
_37
-- 3. Create security definer function, which should be run as "postgres"
_37
create function private.get_teams_for_authenticated_user()
_37
returns setof bigint
_37
language sql
_37
security definer
_37
set search_path = public
_37
stable
_37
as $$
_37
select team_id
_37
from members
_37
where user_id = auth.uid()
_37
$$;
_37
_37
-- 4. Create Policy
_37
create policy "Team members can update team members if they belong to the team."
_37
on members
_37
for all using (
_37
team_id in (
_37
select private.get_teams_for_authenticated_user()
_37
)
_37
);

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:


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

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


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

More resources