Do I understand correctly that the Postgres views ...
# helpl
Lavka
12/15/2021, 2:53 PMDo I understand correctly that the Postgres views are the answer to CLS (column level security)? That implies that I need to create a lot of views for every table if I need to restrict read access to columns in the row based on the role (user itself, team administrator, global administrator, anonymous...)
s
silentworks
12/15/2021, 4:02 PMNo this is not the answer, this is one workaround. Column Level Security shouldn't be necessary if your database is modelled correctly. The right solution is to model your data correctly.
l
Lavka
12/15/2021, 4:10 PMOk, then how would I ideally model the following situation (I'm new to Supabase and coming from Laravel/backend ecosystem):
Table profiles with realname, username, varified_at columns.
- User can read all and write realname and username
- Admin can read all and write all
- Anonymous can read username and varified_at and write nothing
The only way I can think of it is to have multiple tables representing individual entity information like public_profiles and private_profiles
Lavka
12/15/2021, 6:33 PMI have gone thru the new Egghead course and it is what I thought. You need to create one table per cluster of data associated with one entity and manage their consistency with triggers to be compliant with the way of doing things.
So for my example:
- table: profiles
- profile_id (or user_id)
- realname
-table: public_profiles
- profile_id (or user_id)
- username
- varified_at
s
Steve
12/16/2021, 6:19 PMRegular GRANTs should work for CLS, see https://github.com/supabase/supabase/discussions/3158#discussioncomment-1311387