Problem with RLS and roles/userRoles table

Ask us anything
,
1

Good morning,
I am interfacing with RLSs in Supabase, after following entirely and correctly implementing users and roles I need to put policies in the “roles” and “userroles” tables as well.

Now, I enter the policies that I attach below but as soon as I enter them, WeWeb reports an error with the Supabase authentication plugin. I don’t understand though, I entered the Service Role Key which is just to bypass all the RLSs within the database, why doesn’t this one override it? Is there some special implementation that needs to be implemented?

I am attaching here the policies that I am trying to insert in the userroles table:

CREATE POLICY admin_select_policy
ON public.userroles
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM public.userroles ur
JOIN public.roles r ON ur.role_id = r.id
WHERE ur.user_id = auth.uid() AND r.role_name = ‘Admin’
)
);

CREATE POLICY admin_insert_policy
ON public.userroles
FOR INSERT
USING (
EXISTS (
SELECT 1
FROM public.userroles ur
JOIN public.roles r ON ur.role_id = r.id
WHERE ur.user_id = auth.uid() AND r.role_name = ‘Admin’
)
);

I attach here the policies I am trying to insert in the roles table:

CREATE POLICY admin_select_policy
ON public.roles
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM public.userroles ur
JOIN public.roles r ON ur.role_id = r.id
WHERE ur.user_id = auth.uid() AND r.role_name = ‘Admin’
)
);

Can any of you help me?

Thank you very much,
Naike

2

I think more details about the WeWeb error might be beneficial. I’d also suggest testing if it works while impersonating users in the Supabase dashboard. Imo it is a plugin misconfiguration, but we shall see the errors.

3

Roles are fetched with your connected user when you login (or enter the page and was already logged in)
This step does not use the service role key as it happen on the front end.

The user should be able to retrieve his own roles from the userRoles and Roles tables. (We fetch the userRoles table and ask to return the associated roles information in the query)