supabase RLS policy using "in" always fails

Question

I'm setting up RLS for a table in supabase, and querying it from my React app.

Using this policy works:

(project_id = 8)

However, this always fails:

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

Even though I've confirmed that the projects table has a record with id 8:

select id from projects where id = 8
| id |
| -- |
| 8  |

Because the first one works, I'm pretty confident it doesn't have to do with how I set up the RLS, how I connect to the db, any jwt issues, or how I query for the data. What else could be the issue? Could it be that the maker of the request is not allowed to query the projects table so the policy always fails? If so, how do I work around that? It doesn't seem to make sense to allow access to other tables just because I need them for RLS.

Answer 1

This policy always returns false as RLS on the projects table prevents this one from querying it (this RLS policy is for another table, not projects), thus SELECT projects.id FROM projects always results in no records being returned.

I had to use a security definer function instead, which can bypass RLS on the other tables:

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