Problem with creation policy in Supabase

Ask us anything
,
1

Good evening everyone,

I am facing an issue with the RLS policy in Supabase.

Basically, I followed the entire tutorial to properly manage users and roles in Supabase, and these are working correctly (here is the tutorial: WeWeb | Works With Supabase + User Management | Supabase Docs).

Now, I would like to define some rules.

I have a table in public.user that contains user data, and I would like to define a rule so that only those with a specific roleId (contained in the public.userRoles table) can see all users.

The policy I am trying to implement is as follows:

CREATE POLICY read_public_user_by_role_id
ON public.user
FOR SELECT
USING (
  EXISTS (
    SELECT 1
    FROM "public"."userRoles"
    WHERE "public"."userRoles"."userId" = auth.uid()
      AND "public"."userRoles"."roleId" = 'cdce0c26-276e-427a-a289-ec609d86325e'::uuid
  )
);

But the result is No row returned.

2

Hello, I think you have set up some RLS rules (or no rules at all) on the public.userRoles table, this probably prevents the SELECT from that table, as it should. The operations over the DB when doing RLS checks are most probably run as the INVOKER, meaning the auth.user who you currently check the rules for. You can work around this with a SECURITY DEFINER view, or a function, just make sure it’s safe.

3

Hi Broberto,
thanks for the response, I check the RLS Policy but the function doesn’t work.

This is the new situation:

I create a function handle_new_user
begin
insert into public.users (id, email, first_name, last_name, user_type)
values (new.id, new.email, new.raw_user_meta_data ->> ‘name’, new.raw_user_meta_data ->> ‘family_name’, (SELECT id FROM public.roles WHERE name = ‘Studente’));

INSERT INTO public.userroles (userId, roleId)
VALUES (NEW.id, (SELECT id FROM public.roles WHERE name = ‘studente’));

return new;
end;

I insert the userroles part, all the text is correct but weweb debug say stack: "Error: Database error saving new user at Object.signUp (https://cdn.weweb.io/components/1fa0…)

Just for testing i deactivated the RLS Policy but nothing change.

Do you have any suggest for me?

1 Like
4

You probably need to check out the logs of Supabase, you set up a trigger function, and it throws an error blocking the signup of an new user. So something’s wrong with the function.

1 Like
5

Hi Broberto,
after banging my head about it for a whole day I found the solution: I renamed in the userroles table the two columns with the same name as before (but all lowercase) and it worked!

I generated the DB with the help of ChatGPT, not that I maybe put some invisible spaces and characters during the copy…

Thanks for the support!

6

Yeah, SQL causes all sorts of problems when you use uppercase. Nice to hear you solved it

1 Like