Managing Row Level Security when using a Roles table

Ask us anything How do I?
1

How can I create RLS policies when roles are not directly stored in the auth.users table?

I’m using Supabase as my backend and have set up the following tables as recommended by WeWeb and Supabase:

  • roles
  • user_roles (a join table)
  • profiles

However, this setup complicates the creation of policies in Supabase.

What was once as simple as this:

CREATE POLICY "Allow owners and admins to manage profiles"
  ON public.profiles
  FOR ALL
  USING (
    auth.uid() = userId
    OR (
      SELECT role 
      FROM auth.users 
      WHERE id = auth.uid()
    ) = 'admin'
  );

Now becomes significantly more complex:

CREATE POLICY "Allow owners and admins to manage profiles"
  ON public.profiles
  FOR ALL
  USING (
    auth.uid() = userId
    OR EXISTS (
      SELECT 1
      FROM public.user_roles ur
      JOIN public.roles r ON ur.roleId = r.id
      WHERE ur.userId = auth.uid()
        AND r.name = 'admin'
    )
  );

Is there a better way to create these policies?

2

One thing that I usually tend to go for, when I discuss this with people on larger projects is Custom Claims in Supabase. This way you reduce the load of many JOINs and you reduce the complexity of your policies. I usually tend to not lean towards WeWeb’s implementation of Roles, because it can become expensive if you wanna use groups (you need to upgrade).

1 Like
3

That’s very interesting. You only filter in the backend, without using the roles features in WeWeb?

4

Frontend filtering for frontend, backend for backend. I do both, but I don’t use WeWeb’s roles most of the time.

1 Like