How to make sound policies for a table with is_admin
k
Koen
05/25/2023, 2:24 PMI 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 PMThose 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 AMGary that post is amazing. Good work