I have a hard time understanding why we need a new table that maps the users registered using Supabase built-in Auth with their role and then uses RLS according to the roles in that table.

From what I understand you need something like

create table user_roles (
  id uuid references auth.users on delete cascade,
  role text check (role in ('user', 'admin')) default 'user',
  primary key (id)
);

and then you need to create policies to attach roles to users like

create policy "Users can view their own role" on user_roles for select
  using ( auth.uid() = id );

create policy "Only admins can update roles" on user_roles for update
  using ( auth.uid() in ( select id from user_roles where role = 'admin' ) );

Why can’t I directly use the role column of the user table inside the auth schema? It already stores the actual roles I need (user, admin, etc) and builds my login on top of that, checking what role a user has.