Using Postgres RLS for a team invite system with Supabase

I thought I knew enough about RLS but I made a few dangerous assumptions about it that turned out not to be true. RLS works great until you need to restrict anonymous access to a table. An invite system is a perfect example of this and I have used this to demonstrate the problem and provide a potential solution. This pattern can be applied to other similar situations that will inevitably crop up in your own app.

Let’s start with an important statement:

If you use Supabase JS on your client-side and you don’t use RLS then your database is open to the world.

Selects, inserts, updates, deletes. The whole thing.

So, you need RLS. But, it’s limited… or rather, annoying and non obvious.

Normally with anything Postgres, there are reams of docs, but with RLS there are only really two relevant doc pages here and here. Supabase have their own page, and it looks nice, but it doesn’t really say anything that isn’t on the official docs other than how to use auth.uid() which is easy and pretty much essential.

A simple use case: Team Invites

So, let’s get down to business. We have an invite system for inviting fellow board members that uses a simple table defined like this:

CREATE TABLE public.team_invites (
 id uuid NOT NULL DEFAULT uuid_generate_v4(),
 team_id uuid NOT NULL,
 inviter_id uuid NULL,
 first_name varchar NOT NULL,
 last_name varchar NULL,
 email varchar NULL,
 created_at timestamptz NULL DEFAULT now(),
 CONSTRAINT team_invites_pkey PRIMARY KEY (id)
);

Now, using RLS I want to restrict who has access to this. The requirement is:

• The intended recipient of the invite should be able to return the details in order to consume the invite.
• Team members (specifically team owners) should be able to return details or insert/update new invites.

Now, of these two requirements, the latter sounds more complicated. However, this is entirely what RLS is designed to do. Take the context of the calling user, determine if they have access and permit or deny access to the data. The rule is simple and looks like this:

create policy "Team owners can select and update invites"
  on team_invites
  for all using (
    team_id in (
      select get_teams_with_owner_privilege_for_authenticated_user()
    )
  );

With a supporting function:

get_teams_with_owner_privilege_for_authenticated_user()

— what a name 😂 defined as:

create or replace function get_teams_with_owner_privilege_for_authenticated_user()
returns setof uuid
language sql
security definer
set search_path = public
stable
as $$
    select team_id
    from team_members
    where user_id = auth.uid() and user_level = 1
$$;

So, with this rule in place, currently the table can only be read and updated by users that are part of the same team as the invite. At this point, the invite system is broken, we can’t access an invite as an anonymous user.

This is the problem, an anonymous user has no credentials to secure the query with, the only solution it seems is to give blanket SELECT * access to team_invites. This would mean anyone can adjust my query on the front end and pull back a complete list with every invite in the database, pull out an invite and then join a team they shouldn’t! 💥

I thought with RLS I’d be able to do the equivalent of following:

create policy "Anonymous users can select a specific team invite by ID"
  on team_invites
  for select using (
    id = id_of_query_parameter
  );

Where id_of_query_parameter (or some relevant syntax) would be the actual incoming query. This would work nice, because my IDs are UUID so not guessable — you wouldn’t be able to access any invite without knowing the ID. However, you can not access incoming query parameters with RLS.

Functions to the rescue

The key to this working is using UUIDs as you primary keys. I would go as far as to say:

RLS will not work for all authentication requirements on your Supabase site without the use of UUIDs.

So, the solution is a function that can skip RLS:

CREATE OR REPLACE FUNCTION public.get_invite(invite_id uuid)
 RETURNS SETOF team_invites
 LANGUAGE sql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
  select * from team_invites where id = invite_id;
$function$
;

This is a simple function, it takes an invite_id (which is a UUID) and returns any team_invites that match. Of course, this will either be zero or one. It will never be more than that.

The important part of this function is SECURITY DEFINER and this is mentioned in the Supabase docs about calling functions. It essentially means that the function is called with the security context of the user creating the function not the user calling the function and, as such, this means the RLS rules will be skipped.

All that is left to do is to replace our code on the front-end that selects the invite with our function call.

So this:

const { data, error } = await supabase
    .from('team_invites')
    .select()
    .eq('id', inviteId)
    .single()

Becomes this:

const { data, error } = 
  await supabase.rpc('get_invite', { invite_id: id }).single()

Pretty clean. Now, everything works, and there is no way to select anything from the database you shouldn’t be able to. 🥳

I hope this helps someone. I deliberately filled this post with some of the things I was trying to google without any luck.

We are building the best online board management solution for small businesses and startups. Want to run your board meetings better? Sign up for FREE and try us out.