I was able to figure it out: I put the above sql code in a function and then used the advanced settings to set the security to DEFINER instead of INVOKER

It is a common thing to do. https://supabase.com/docs/guides/auth/row-level-security#policies-with-security-definer-functions I'm not sure about your actual code but if it is returning a "constant" that won't change for each row in the table the RLS is on, then you should use stable instead of volatile so it all has to run once instead on each row. Also what you return you don't want to be a security issue because as that rpc function can be called thru the api directly. I have a separate schema "security" I put those rpc functions so I don't have to think about it.

This is great, thank you @garyaustin . Exactly what I needed