My profiles table has displayname, avatarurl and role. RLS policy for update: A friend who tested for me sent a PATCH with {"role":"admin"} from the console and

RLS decides which rows someone can touch, not which columns. Two ways out: 1. Column privileges quickest: Now an update that includes role fails with permission

Demo, all content is generated

Question

Users can set their own role to admin through the profile update

Solved · 1241 views · asked last month by yara_h · edited

My profiles table has display_name, avatar_url and role. RLS policy for update:

create policy "update own profile" on profiles
for update using (id = auth.uid());

A friend who tested for me sent a PATCH with {"role":"admin"} from the console and it worked. How do I let users edit their name but not their role? Same table.

What I’ve tried

Added a check in the edit form that role can't be changed. But he didn't use the form.

Comment

Good friend to have. Most people find this out from a stranger. hannah_reyes last month · edited

TIL column-level grants work with RLS. Nice. dmitri_v last month · edited

3 answers

Marked as helpful by the asker

hannah_reyeslast month · edited

RLS decides which rows someone can touch, not which columns. Two ways out:

1. Column privileges (quickest):

revoke update on profiles from authenticated;
grant update (display_name, avatar_url) on profiles to authenticated;

Now an update that includes role fails with permission denied for table profiles, while name/avatar updates still go through your RLS policy.

2. Move role to its own table that users can only read. Cleaner if roles will grow.

Also add with check (id = auth.uid()) to that policy, so nobody can change id to someone else's.

Comment

Option 1 worked in one minute. My friend tried again: permission denied. Thanks! yara_h last month · edited

Remember to add new columns to the grant when you add them later, otherwise users suddenly can't save them and you'll wonder why. jb_supa last month · edited

mira_devlast month · edited

+1 to column grants. A trigger that raises when new.role is distinct from old.role also works, but grants are simpler and easier to read in a year.

Comment

anjali_plast month · edited

Check your INSERT policy the same way. If profiles are created from the client (not a trigger), a user can insert their own row with role = 'admin' right away. Grant insert on the safe columns only, or create the row in the handle_new_user trigger.

Comment

It's created by a trigger, but good to know, thanks. yara_h last month · edited

Keyboard shortcuts

Users can set their own role to admin through the profile update

What I’ve tried

3 answers

Your answer

Users can set their own role to admin through the profile update

What I’ve tried

3 answers

Related

Your answer