How do you manage table access based on user profile (supabase)?
Do you know what is more performant for managing access to supabase tables ? I 'm curios if having one single RLS to manage all SELECT based on each user profile (admin , manager , etc ) or having separate RLS . I have in my user table a column with this profile type , do you recommend to do something like (SELECT profile FROM users WHERE id = auth .uid ( ) ) = "manager " or do you recommend to add at login this information in the JWT and manage the select based on the JWT select . What would be faster , selecting from JWT or from the tables ? Do you have a better alternative ? Like not directly using auth .uid ( ) etc Thank you @Lucas G . Do you have views without RLS activated ? I created a view because I want to grant access based on the view ’s "WHERE " select , which works like RLS . However , I can ’t activate RLS because it applies the table ’s RLS policy , and that policy only allows access for the owner (I don 't want to change that ) , but that red dot is really frustrating . Should I find a solution to views with RLS only activated ? I have this visual blockage 😅 Is the main thing If the table doesn ’t have data that should only be viewed by certain people then it doesn ’t matter as much How would you proceed in this situation ? You have users table and users _jobs , both could be selected only by owner , but in some case if owner want 's to share part of the info , you have a view with a select of the users columns and users _jobs columns where users .public = true . Why that where shouldn 't be secure ? I can 't use RLS because you don 't want to grant visibility on all columns , maybe just first and last name . And a worker , yes , I could use a worker , but it has the same select as I have in the view . On the other hand , a view allows me to add a "foreign key " (not a real foreign key , just a simulation ) , whereas you can 't do that with a worker , a view is much more practical . It 's unsafe in the sense that if the underlying table is known then somebody can just query that table directly since it has no policies . I don 't know what the full setup here is obviously so I don 't know how "unsafe " it really is . I wouldn 't say a worker is restricted though . You can pull whatever data you need and return only that , the endpoint is public but it would only return limited data . What I mean si that the table has RLS only for owners , the view doesn 't have because there I use where and is a very good select . Adding rls on the view would limit the select to owners only , but without RLS gives me flexibility and help me to keep users table safe in the same time I 'm sharing some data in a safe way too but without RLS using a view . @Erik Beuschau what do you think on such case , it worth using views without rls because the view select where is enoght , but the table has rls ? They don 't have their own RLS @Lucas G
They don 't have their own RLS So you do have RLS then enabled Here , jobs has RLS (restricted to organization ) but if you taggle public and is approved , the view (jobs _published ) does a select of all jobs .published = true , and shows only part of necessary tables (jobs , jobs _skills , etc . . ) , not full data of the table (I need just title maybe ) . jobs is table , jobs _publiched is a view . jobs has rls , jobs _published doesn 't because is using a where that is limiting access . Like where user .name = 'Lucas ' . Only you can view data from jobs _published (the view , not table ) , and I don 't get how you could trick that where from the view . Again , the "unsafe " thing is relevant . It doesn 't sound like the view exposes sensitive data or data that shouldn 't be access by unauthorized users so it probably doesn 't matter . That Unrestricted tag is there on the UI to let people know that views don 't have policies on their own
.jpeg/public)