Infinite Recursion RLS policy issues
Hi team,
I thought this would be a very simple issue but it has taken me a long time to try and work out, and its still not working!
My app essentially allows users to join and use my platform as a company. On sign up, they create a company with x number of seats and via invite links, members can join the company and fill those seats. Users can have 2 roles, either 'admin' or 'member'. Admin roles should be able to change the role of other users in their company as well as remove users from the company. In order to do this, I want to make 2 RLS policies on my public.users table. A select policy that allows admins to see other users in the same company as theirs, and an update policy which allows admins to change the role of other users in their company. In my public.users table, there is a role and company_id column. Of course, when trying to get the users company_id in the policy, I am creating an infinite recursion by referencing the table. I have tried 'USING' statements, JWT, external functions that fetch user information seperately, none of which have worked.
What is the industry standard for this problem as it must be a problem that people come across a lot? What steps would you recommend I take? Any help would be greatly appreciated!
Thanks
I thought this would be a very simple issue but it has taken me a long time to try and work out, and its still not working!
My app essentially allows users to join and use my platform as a company. On sign up, they create a company with x number of seats and via invite links, members can join the company and fill those seats. Users can have 2 roles, either 'admin' or 'member'. Admin roles should be able to change the role of other users in their company as well as remove users from the company. In order to do this, I want to make 2 RLS policies on my public.users table. A select policy that allows admins to see other users in the same company as theirs, and an update policy which allows admins to change the role of other users in their company. In my public.users table, there is a role and company_id column. Of course, when trying to get the users company_id in the policy, I am creating an infinite recursion by referencing the table. I have tried 'USING' statements, JWT, external functions that fetch user information seperately, none of which have worked.
What is the industry standard for this problem as it must be a problem that people come across a lot? What steps would you recommend I take? Any help would be greatly appreciated!
Thanks
