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
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 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 ...
_10create policy "Individuals can view their own todos."_10on todos for select_10using ( auth.uid() = user_id );
.. would translate to this whenever a user tries to select from the todos table:
_10select *_10from todos_10where 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)
_11create policy "Profiles are viewable by everyone"_11on profiles for select_11to authenticated, anon_11using ( true );_11_11-- OR_11_11create policy "Public profiles are viewable only by authenticated users"_11on profiles for select_11to authenticated_11using ( true );
Supabase provides some helper functions that make it easier to write Policies.
Returns the ID of the user making the request.
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.
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:
_10create policy "User is in team"_10on my_table_10to authenticated_10using ( team_id in (select auth.jwt() -> 'app_metadata' -> 'teams'));
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.
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.
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
_10create policy "Allow public access"_10on my_table for select_10using ( 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:
_10create schema private;_10_10create table private.employees (_10id serial primary key,_10name text_10);
If you do put anything in the
public schema, make sure to enable RLS (you don't need to add any policies):
_10create table profiles (_10id serial primary key,_10email text_10);_10_10alter table profiles enable row level security;
This makes the tables inaccessible via the APIs.
Row Level Security is extremely versatile, since it simply uses SQL to express access rules for your data.
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
profilesin the public schema (default schema).
- Enables RLS.
- Creates a policy which allows logged in users to update their own data, using the
_16-- 1. Create table_16create table profiles (_16id uuid references auth.users,_16avatar_url text_16);_16_16-- 2. Enable RLS_16alter table profiles enable row level security;_16_16-- 3. Create Policy_16create policy "Users can update their own profiles"_16on profiles for update_16to authenticated_16using (_16auth.uid() = id_16);
Note: If you want to use upsert operations, the user needs to have
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_24create table teams (_24id serial primary key,_24name text_24);_24_24-- 2. Create many to many join_24create table members (_24team_id bigint references teams,_24user_id uuid references auth.users_24);_24_24-- 3. Enable RLS_24alter table teams enable row level security;_24_24-- 4. Create Policy_24create policy "Team members can update team details if they belong to the team"_24on teams_24for update using (_24auth.uid() in (_24select user_id from members_24where 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
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
_37-- 1. Create a table of teams_37create table teams (_37id serial primary key,_37name text_37);_37_37-- 2. Create many to many join_37create table members (_37team_id bigint references teams,_37user_id uuid references auth.users_37);_37_37-- 2. Enable RLS_37alter table teams enable row level security;_37alter table members enable row level security;_37_37-- 3. Create security definer function, which should be run as "postgres"_37create function private.get_teams_for_authenticated_user()_37returns setof bigint_37language sql_37security definer_37set search_path = public_37stable_37as $$_37select team_id_37from members_37where user_id = auth.uid()_37$$;_37_37-- 4. Create Policy_37create policy "Team members can update team members if they belong to the team."_37on members_37for all using (_37team_id in (_37select 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
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:
_10create policy "Only Supabase staff can update the leaderboard"_10on leaderboard_10to authenticated_10for update using (_10right(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):
_10create policy "Restrict updates."_10on profiles_10as restrictive_10for update_10to authenticated using (_10auth.jwt()->>'aal' = 'aal2'_10);