RLS with Security Definer function returns error with no message, and no error in logs in Supabase / Postgresql

Question

I have a table called user_roles with three columns: id, user_id, role.

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())

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 · Accepted Answer · 2022-11-10 15:46:15Z

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())

answered Nov 10, 2022 at 15:46

jopfre

5033 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

RLS with Security Definer function returns error with no message, and no error in logs in Supabase / Postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related