So I am trying to add a groupId to the auth.users table that I am going to use directly with the RLS. Initially this groupId is the same as the Id in auth.users. The default value is null. I have created the following function and trigger to set the groupId to be the Id when an insert happens on auth.users. To test this I was trying to delete a user and sign up with the same user to make sure it is working. However when I go the authentication tab and try to delete that user It says "Failed to delete user: property groupId should not exist". Should I have tried removing all users before implementing the function and trigger?? is there a better way to go about this?? *sql code was generated by the supabase AI

sql

-- Drop the function if it exists
DROP FUNCTION IF EXISTS public.initialize_group();

-- Drop the trigger if it exists
DROP TRIGGER IF EXISTS initialize_group_trigger ON auth.users;

-- Create the function
CREATE FUNCTION public.initialize_group()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  NEW."groupId" := NEW.id;
  RETURN NEW;
END;
$$;

-- Create the trigger
CREATE TRIGGER initialize_group_trigger
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.initialize_group();

don't mess with the system-provided tables. either keep your group in another table matched up by the user id, or add it to the meta data of the user.

hmm okay so I think I should create a new table that has some of the same info as auth.users. Lets say userId, name, phone number email. and then I could add the groupId to that. I can then also use the RLS for to match the group id from the tables I want to query data from and the new profiles table.

why would you want to duplicate the data? the only thing it is safe to do is add a trigger to the auth.users table and to make a foreign key reference to it. your new table should just make a foreign key reference to the id.

You can’t join auth.users from the API so Supabase recommends copying what you need in a trigger.

interesting. good to know.