CODEWITHSUNDEEP
supabasesupabase-database
Yash
Yash

Reputation: 273

Role based authentication in Supabase

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!

Upvotes: 3

Views: 10046

Answers (1)

Andrew Smith
Andrew Smith

Reputation: 1841

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?

Upvotes: 6

Related Questions