how to use supabase RLS with "in" and "select" without having to provide unrestricted RLS access to the joined tables

Question

I am trying to set up simple RLS on my supabase tables. I enabled RLS on all tables, then added this policy to a table I wanted to select from:

(project_id IN ( SELECT projects.id FROM projects))

However, it always fails because the table projects also has RLS on.

In reality, the policies would be more complicated and involve multiple tables.

How do I work with RLS without having to expose all the tables required to make the RLS policy?

Answer 1

Okay, figured it out, turns out it's relatively simple but I'll post an answer for people coming across the same issues with no guidance.

Solution was to use security definer functions which can query RLS-protected tables without RLS penalties:

https://supabase.com/docs/guides/database/postgres/row-level-security#use-security-definer-functions

https://supabase.com/docs/guides/auth/row-level-security#using-security-definer-functions

You can create a new schema, private, and add the functions you need using the SQL editor or the GUI. The function can take arguments, as shown in the postgres documentation, which also explains some other options:

https://www.postgresql.org/docs/current/sql-createfunction.html