Creating a Postgres function for a Supabase RLS policy
published
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.
erDiagram
PERMISSIONS ||--o{ GROUPS : "belongs to"
PERMISSIONS ||--o{ USERS : "has"
PERMISSIONS {
uuid group_id
text user_email
}
create or replace function get_groups_for_authenticated_user()returns setof uuidlanguage sqlset search_path = public -- set which schemas to search unqualified table names fromstable -- optimizer hint that this function cannot modify the databaseas $$ 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() ) );