1

I have a table called user_roles with three columns: id, user_id, role. enter image description here

With RLS disabled, I am able to update rows in this table with the call:

 const { data, error } = await supabase
    .from('user_roles')
    .update({ role: role })
    .eq('user_id', id);

Now I would like to add RLS so only admins can update the roles of users. I am aware that this needs to be done in a security definer thanks to these two posts:

Role based authentication in Supabase

https://github.com/supabase/supabase/discussions/3328

So I have created an is_admin() security definer function as follows:

SELECT EXISTS (
  SELECT 1
  FROM user_roles
  WHERE user_roles.user_id = _user_id
  AND user_roles.role = 'admin'
);
$$ LANGUAGE sql SECURITY DEFINER;

and then I have a policy for updates with USING expression is_admin(uid()) enter image description here enter image description here

However when I enable RLS and make my call I get the response:

error { message: '' }

There are no errors in my logs so I am unsure how to proceed.

Does anyone have any ideas of what could be wrong or what to investigate next? Many thanks

jopfre
  • 530
  • 3
  • 12

1 Answers1

1

It seems the problem was because Update also does a Select in supabase so you need a policy for select too or specify { returning: "minimal"} in the update call

https://github.com/supabase/supabase/discussions/5097

in my case I set the Select policy to (uid() = user_id) OR is_admin(uid())

jopfre
  • 530
  • 3
  • 12