3

I am trying to make policy in supabase where a user with admin role can only get list of employees whose role are "agent"

There is a "users" table and I am trying to add following policy

"(auth.email() in (select users.email from users where users.role = 'admin')) and (role = 'agent')

User table has following columns

firstname | lastname | role | email | password

However I am getting Infinite recursion on users table mesage.

How can I create a role based policy here? Thanks in advance!

Yash
  • 223
  • 2
  • 11
  • 1
    I've see 2 sources claim that the way to achieve this is to extract your first clause into a SECURITY DEFINER function which is exempt from RLS. See: https://stackoverflow.com/questions/72369134/infinite-recursion-rls and https://github.com/supabase/supabase/discussions/3328 – chipilov Jun 19 '22 at 19:18

1 Answers1

5

This is a known issue when doing a query on a table that the RLS will be set on because the policy lookup is subject to the policy too. You will need to move the query into a security definer function and call the function in the policy instead to avoid infinite recursion.

CREATE OR REPLACE FUNCTION admin_only(email string)
  returns boolean AS
$$
  EXISTS (select users.email from users 
  where users.role = 'admin'
  and users.email = email)
$$ stable language sql security definer;

Then in your policy add

admin_only(auth.email())

I am a little confused by the policy you are trying to apply as you are checking if the users.role is admin but at the same time you are checking if the role is agent too, does this mean a user can be assigned multiple roles?

Andrew Smith
  • 1,224
  • 6
  • 9
  • I was checking if the role in request JWT is admin which means Only admin can query this resource and the api should only return row where "role" was agent. Sounds odd but I am new to supabase and was trying to work this one out. – Yash Jun 21 '22 at 08:34
  • Also, where is security definer function in supabase? – Yash Jun 21 '22 at 08:35
  • @Yash You can learn about how to create a security definer function in Supabase on this video https://youtu.be/0N6M5BBe9AE?t=356 You can also create functions using SQL as Andrew has explained! – dshukertjr Jun 22 '22 at 05:44
  • I am getting an error in supabase: `failed to run sql query: syntax error at or near "EXISTS"` – jopfre Nov 09 '22 at 19:15
  • @jopfre I was getting the same issue and this answer helped me. Turned out to be a lot simpler than I thought it would be. https://stackoverflow.com/questions/73264248/update-rls-in-supabase-seems-broken – Coderslang Master Nov 10 '22 at 22:56