Creating a Postgres function for a Supabase RLS policy

Row Level Security policies are a very core part of managing Supabase databases due to clientside queries with supabase-js and realtime updates. Therefore, creating efficient and simple to understand RLS policies is a relatively important part of managing a Supabase database.

Postgres functions are a good way to simplify RLS policies by extracting repetitive policy checks in a common function. This article goes through a few common function templates and how they can be used.

Postgres RLS function to check inclusion in a join table

We’re assuming the following structure for the database. We’ll create a function to check which groups a user has access to.

create or replace function get_groups_for_authenticated_user()
returns setof uuid
language sql
set search_path = public -- set which schemas to search unqualified table names from
stable -- optimizer hint that this function cannot modify the database
as $$
  select group_id
  from permissions
  where user_email = auth.jwt() ->> 'email'
$$;

Assuming there exists a table items that belongs to a group (via group_id), this function can then be used in a policy as such:

create policy "User can see their group's items"
  on items
  for select using (
    group_id in (
      select get_groups_for_authenticated_user()
    )
  );