Row Level Security

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

Policies are PostgreSQL's rule engine. They 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.

Helper Functions#

Supabase provides you with a few easy functions that you can use with your policies.

`auth.uid()`#

Returns the ID of the user making the request.

`auth.jwt()`#

Returns the JWT of the user making the request.

Examples#

Here are some examples to show you the power of PostgreSQL's RLS.

Allow read access#

-- 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 "Public profiles are viewable by everyone."
  on profiles for select using (
    true
  );

Creates a table called profiles in the public schema (default schema).
Enables Row Level Security.
Creates a policy which allows all select queries to run.

Restrict updates#

-- 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 using (
    auth.uid() = id
  );

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.

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

Only anon or authenticated access#

You can add a Postgres role

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

Policies with joins#

Policies can even include table joins. This example shows how you can query "external" tables to build more advanced rules.

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

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

Policies with security definer functions#

Policies can also make use of security definer functions. This is useful in a many-to-many relationship where you want to restrict access to the linking table. 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. Follow example for 'Policies with joins' above
-- 2.  Enable RLS
alter table members
  enable row level security
-- 3.  Create security definer function
create or replace function 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 get_teams_for_authenticated_user()
    )
  );

Verifying email domains#

Postgres has a function right(string, n) that returns the rightmost n characters of a string. You could use this to match staff member's email domains.

-- 1. Create table
create table leaderboard (
  id uuid references auth.users,
  high_score bigint
);
-- 2. Enable RLS
alter table leaderboard
  enable row level security;
-- 3. Create Policy
create policy "Only Blizzard staff can update leaderboard"
  on leaderboard
  for update using (
    right(auth.jwt() ->> 'email', 13) = '@blizzard.com'
  );

Time to live for rows#

Policies can also be used to implement TTL or time to live feature that you see in Instagram stories or Snapchat. In the following example, rows of stories table are available only if they have been created within the last 24 hours.

-- 1. Create table
create table if not exists stories (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
  content text not null
);
-- 2. Enable RLS
alter table stories
  enable row level security;
-- 3. Create Policy
create policy "Stories are live for a day"
  on stories
  for select using (
    created_at > (current_timestamp - interval '1 day')
  );

Advanced policies#

Use the full power of SQL to build extremely advanced rules.

In this example, we will create a posts and comments tables and then create a policy that depends on another policy. (In this case, the comments policy depends on the posts policy.)

create table posts (
  id            serial    primary key,
  creator_id    uuid      not null     references auth.users(id),
  title         text      not null,
  body          text      not null,
  publish_date  date      not null     default now(),
  audience      uuid[]    null -- many to many table omitted for brevity
);
create table comments (
  id            serial    primary key,
  post_id       int       not null     references posts(id)  on delete cascade,
  user_id       uuid      not null     references auth.users(id),
  body          text      not null,
  comment_date  date      not null     default now()
);
create policy "Creator can see their own posts"
on posts
for select
using (
  auth.uid() = posts.creator_id
);
create policy "Logged in users can see the posts if they belong to the post 'audience'."
on posts
for select
using (
  auth.uid() = any (posts.audience)
);
create policy "Users can see all comments for posts they have access to."
on comments
for select
using (
  exists (
    select 1 from posts
    where posts.id = comments.post_id
  )
);

Tips#

You don't have to use policies#

You can also put your authorization rules in your middleware, similar to how you would create security rules with any other backend <-> middleware <-> frontend architecture. You can use Edge Functions to run this architecture, or you can use your favorite server framework, like Rails, Django, Node.js, Phoenix, or Laravel.

Policies are a tool. In the case of "serverless/Jamstack" setups, they are especially effective because you don't have to deploy any middleware at all and can use the javascript libraries directly from the browser.

That said, 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 plan to use this approach make sure to enable RLS for your tables. Then use the service_role key (for our client libraries) or the postgres role - both of these can bypass RLS. You don't need to create any policies with this approach, simply enabling RLS is sufficient:

create table profiles (
  id serial primary key,
  email text
);
alter table profiles enable row level security;

Never use a service key on the client#

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

caution

Initializing a client with a Service Key will not override RLS if a user token is set. If a user is signed in with a client, Supabase will adhere to the RLS policy of the user.

Testing policies#

To test policies on the database itself (i.e., from the SQL Editor or from psql) without switching to your frontend and logging in as different users, you can utilize the following helper SQL procedures (credits):

grant anon, authenticated to postgres;
create or replace procedure auth.login_as_user (user_email text)
    language plpgsql
    as $$
declare
    auth_user auth.users;
begin
    select
        * into auth_user
    from
        auth.users
    where
        email = user_email;
    execute format('set request.jwt.claim.sub=%L', (auth_user).id::text);
    execute format('set request.jwt.claim.role=%I', (auth_user).role);
    execute format('set request.jwt.claim.email=%L', (auth_user).email);
    execute format('set request.jwt.claims=%L', json_strip_nulls(json_build_object('app_metadata', (auth_user).raw_app_meta_data))::text);
    raise notice '%', format( 'set role %I; -- logging in as %L (%L)', (auth_user).role, (auth_user).id, (auth_user).email);
    execute format('set role %I', (auth_user).role);
end;
$$;
create or replace procedure auth.login_as_anon ()
    language plpgsql
    as $$
begin
    set request.jwt.claim.sub='';
    set request.jwt.claim.role='';
    set request.jwt.claim.email='';
    set request.jwt.claims='';
    set role anon;
end;
$$;
create or replace procedure auth.logout ()
    language plpgsql
    as $$
begin
    set request.jwt.claim.sub='';
    set request.jwt.claim.role='';
    set request.jwt.claim.email='';
    set request.jwt.claims='';
    set role postgres;
end;
$$;

To switch to a given user (by email), use call auth.login_as_user('my@email.com');. You can also switch to the anon role using call auth.login_as_anon();. When you are done, use call auth.logout(); to return yourself to the postgres role.

These procedures can also be used for writing pgTAP unit tests for policies.

Click here to see an example psql interaction using this.

This example shows that the public.profiles table from the tutorial example can indeed be updated by the postgres role and the owner of the row but not from anon connections:

postgres=> select id, email from auth.users;
                  id                  |       email
--------------------------------------+-------------------
 d4f0aa86-e6f6-41d1-bd32-391f077cf1b9 | user1@example.com
 15d6811a-16ee-4fa2-9b18-b63085688be4 | user2@example.com
 4e1010bb-eb37-4a4d-a05a-b0ee315c9d56 | user3@example.com
(3 rows)
postgres=> table public.profiles;
                  id                  | updated_at | username | full_name | avatar_url | website
--------------------------------------+------------+----------+-----------+------------+---------
 d4f0aa86-e6f6-41d1-bd32-391f077cf1b9 |            | user1    | User 1    |            |
 15d6811a-16ee-4fa2-9b18-b63085688be4 |            | user2    | User 2    |            |
 4e1010bb-eb37-4a4d-a05a-b0ee315c9d56 |            | user3    | User 3    |            |
(3 rows)
postgres=> call auth.login_as_anon();
CALL
postgres=> update public.profiles set updated_at=now();
UPDATE 0 -- anon users cannot update any profile but see all of them
postgres=> table public.profiles;
                  id                  | updated_at | username | full_name | avatar_url | website
--------------------------------------+------------+----------+-----------+------------+---------
 d4f0aa86-e6f6-41d1-bd32-391f077cf1b9 |            | user1    | User 1    |            |
 15d6811a-16ee-4fa2-9b18-b63085688be4 |            | user2    | User 2    |            |
 4e1010bb-eb37-4a4d-a05a-b0ee315c9d56 |            | user3    | User 3    |            |
(3 rows)
postgres=> call auth.logout();
CALL
postgres=> call auth.login_as_user('user1@example.com');
NOTICE:  set role authenticated; -- logging in as 'd4f0aa86-e6f6-41d1-bd32-391f077cf1b9' ('user1@example.com')
CALL
postgres=> update public.profiles set updated_at=now();
UPDATE 1 -- authenticated users can update their own profile and see all of them
postgres=> table public.profiles;
                  id                  |          updated_at           | username | full_name | avatar_url | website
--------------------------------------+-------------------------------+----------+-----------+------------+---------
 15d6811a-16ee-4fa2-9b18-b63085688be4 |                               | user1    | User 1    |            |
 4e1010bb-eb37-4a4d-a05a-b0ee315c9d56 |                               | user2    | User 2    |            |
 d4f0aa86-e6f6-41d1-bd32-391f077cf1b9 | 2023-02-18 21:39:16.204612+00 | user3    | User 3    |            |
(3 rows)
postgres=> call auth.logout();
CALL
postgres=> update public.profiles set updated_at=now();
UPDATE 3 -- the 'postgres' role can update and see all profiles
postgres=> table public.profiles;
                  id                  |          updated_at           | username | full_name | avatar_url | website
--------------------------------------+-------------------------------+----------+-----------+------------+---------
 15d6811a-16ee-4fa2-9b18-b63085688be4 | 2023-02-18 21:40:08.216324+00 | user1    | User 1    |            |
 4e1010bb-eb37-4a4d-a05a-b0ee315c9d56 | 2023-02-18 21:40:08.216324+00 | user2    | User 2    |            |
 d4f0aa86-e6f6-41d1-bd32-391f077cf1b9 | 2023-02-18 21:40:08.216324+00 | user3    | User 3    |            |
(3 rows)

Deprecated features#

We have deprecate some functions to ensure better performance and extensibilty of RLS policies.

`auth.role()`#

caution

Deprecated: The auth.role() function has been deprecated in favour of using the TO field, natively supported within Postgres.

-- DEPRECATED
create policy "Public profiles are viewable by everyone."
on profiles for select using (
  auth.role() = 'authenticated' or auth.role() = 'anon'
);
-- RECOMMENDED
create policy "Public profiles are viewable by everyone."
on profiles for select
to authenticated, anon
using (
  true
);

`auth.email()`#

caution

Deprecated. Use auth.jwt() ->> 'email' instead.

Returns the email of the user making the request.

Edit this page on GitHub

Row Level Security

Policies#

Helper Functions#

auth.uid()#

auth.jwt()#

Examples#

Allow read access#

Restrict updates#

Only anon or authenticated access#

Policies with joins#

Policies with security definer functions#

Verifying email domains#

Time to live for rows#

Advanced policies#

Tips#

You don't have to use policies#

Never use a service key on the client#

Testing policies#

Deprecated features#

auth.role()#

auth.email()#

`auth.uid()`#

`auth.jwt()`#

`auth.role()`#

`auth.email()`#