S
Supabase•2y ago
Ricardo de Paula

Help with Implementing Supabase RLS Policy for Specific Field Update

I'm encountering a challenge in setting up a Row Level Security (RLS) policy on my Supabase database. My goal is to allow all users (authenticated or not) to update only the access_count field in my ads table, while ensuring that no other fields can be modified. My objective is to find a way to restrict updates strictly to the access_count field for all users.
9 Replies
garyaustin
garyaustin•2y ago
You can't do that with RLS. Postgres has column privileges or you could use a trigger function. The trigger function would need to set all the other NEW.col values to their OLD.col value except the one you want to change. Supabase is/has added the column level privileges to the Dashboard UI, https://github.com/orgs/supabase/discussions/20295 Note there is a warning that they will not be migrated so have to be set up each time.
AC
AC•2y ago
GRANT UPDATE (access_count) ON TABLE ... TO anon;
GRANT UPDATE (access_count) ON TABLE ... TO authenticated;
GRANT UPDATE (access_count) ON TABLE ... TO anon;
GRANT UPDATE (access_count) ON TABLE ... TO authenticated;
make sure that with rls or check constraint that the access_count cant be less then the previous one and also only +1 from the previous (OLD) column I think supabase grants update to all columns to anon and authenticated by default and I am not sure if u need to reset (REVOKE UPDATE (...all columns from table) ON TABLE ... FROM anon/authenticated;) the GRANT before setting it to the above for it to work
garyaustin
garyaustin•2y ago
@AC Supabase grants update on the tables. You have to revoke the table grant to the users in order for the column grants to then take effect. Supabase by default does nothing with column grants. The new UI feature handles all of that.
AC
AC•2y ago
Exactly. The default is GRANT ALL for all operations on all columns. Example:
GRANT ALL ON TABLE "public"."total_storage_usages" TO "anon";
GRANT ALL ON TABLE "public"."total_storage_usages" TO "authenticated";
GRANT ALL ON TABLE "public"."total_storage_usages" TO "anon";
GRANT ALL ON TABLE "public"."total_storage_usages" TO "authenticated";
Ricardo de Paula
Ricardo de PaulaOP•2y ago
So, this code is valid? 
REVOKE UPDATE ON TABLE ads FROM anon;
REVOKE UPDATE ON TABLE ads FROM authenticated;

GRANT UPDATE ON TABLE ads TO authenticated;

GRANT UPDATE (access_count) ON TABLE ads TO anon;
REVOKE UPDATE ON TABLE ads FROM anon;
REVOKE UPDATE ON TABLE ads FROM authenticated;

GRANT UPDATE ON TABLE ads TO authenticated;

GRANT UPDATE (access_count) ON TABLE ads TO anon;
Do I have to delete my RSLs before running this code?
garyaustin
garyaustin•2y ago
RLS will take priority.
Also you said authenticated was only to update the single column but show turning back on table access in your code.
Ricardo de Paula
Ricardo de PaulaOP•2y ago
I want access for all users to update the access_count column
garyaustin
garyaustin•2y ago
Yes. But your code allows the authenticated user to update the whole table (of course depending on RLS). If they are supposed to just update that one column then it needs to be just like the anon user.
AC
AC•2y ago
for enforcing the authenticated user to only update the access_count column, u should also do the same as u did with the anon user also rls also needs to be updated but not deleted completely. I dont think u want to allow either user to just put any value into the column

Did you find this page helpful?