12 ms·
I’m using Supabase for similar reasons but there’s one specific situation I’m trying to sort out. Say you have a user “profile” which includes their privileges
by Sai_ 3y ago
I’m using Supabase for similar reasons but there’s one specific situation I’m trying to sort out.
Say you have a user “profile” which includes their privileges - like say a column named “privileges” which is some JSON object denoting what they can/can’t do.
Even with RLS, how do you ensure that a user can’t simply make a curl call with their own JWT to elevate their own privileges?
Basically, how to enforce column level security?
The best thing I can think of is to place “privileges” in a child table and only let the service account update that table.
- teho 3y agoYou could create a trigger that always keeps the value the same unless user has privileges to change it. Or alternatively the RLS rule could check if the column is being updated and abort the call if it is. I’m using a different table that is read-only to regular users to accomplish this.
- kbar13 3y agoi think i would have permissions in a different table
- Sai_ 3y agoSupabase is alpha testing column level security as a Feature Preview that you have to enable in your project. I’m using it now. Works well.
- encima 3y agoHave you checked out this repo: https://github.com/supabase-community/supabase-custom-claims https://github.com/supabase-community/supabase-custom-claims? The "raw_app_meta_data" stored for a user is not writeable by the user, so you can store roles and/or privileges in there.
- Sai_ 3y agoThanks for sharing. Wasn’t aware of this. Will check it out today. For now, I figured I’d have an BEFORE UPDATE trigger which compares the md5(NEW.privileges::text) with md5(OLD.privileges::text) and raises an error if they don’t match. Not sure how to bypass the trigger for service accounts.