https://supabase.com/ logo
How to make sound policies for a table with is_admin
k

Koen

05/25/2023, 2:24 PM
I have created this table which links users to communities. These users have specific fields for communities, like whether they are admin, which are in the same table as well.

https://cdn.discordapp.com/attachments/1111298821050335313/1111298821201338388/image.png

However, I am unsure how I should set up my RLS. What I want: For INSERT rights: 1. Any user is free to insert a new row if is_admin and is_accepted are FALSE. 2. A row can be inserted with is_admin = TRUE and is_accepted = TRUE only when the created_by field in the Community table is the same id as the user For UPDATE rights: 1. The is_accepted and is_admin field can only be updated when the user updating is admin
I am wondering if these are things that should be combined in one table or split up across multiple small tables. I am also wondering if views are the solution
g

garyaustin

05/25/2023, 10:00 PM
Those policies seem pretty straight forward as col=val AND col=val for most of it. For 2 with community table lookup you could use something like 5 here... https://github.com/orgs/supabase/discussions/14576 I believe. Also https://supabase.com/docs/guides/auth/row-level-security#policies-with-joins has join examples on another table.
k

Koen

05/27/2023, 7:40 AM
Gary that post is amazing. Good work