Using a trigger with a not null value in the profile table

[andyjamesn]

I am using this function and trigger from the docs to automatically add new users to a profile table when they sign up

https://supabase.com/docs/guides/auth/managing-user-data

The catch is I have a role field in my profile table that is set to not allow null.

The below code is giving the following error:

{message: 'null value in column "role" of relation "users" violates not-null constraint', status: 500}

How do I add the user role?

I am including the role at signup but I assume this won't work as the function triggered below does not have this data as it is copying from the auth.users which has no role

const { user, error } = await client.auth.signUp({
      email: "[email protected]",
      password: "password",
      role: 1,
    });

-- inserts a row into public.users
create function public.handle_new_user() 
returns trigger 
language plpgsql 
security definer set search_path = public
as $$
begin
  insert into public.profiles (id)
  values (new.id);
  return new;
end;
$$;

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

[GaryAustin1]

You have to pass any extra data in on signUp like this https://supabase.com/docs/reference/javascript/auth-signup#sign-up-with-additional-user-meta-data with the data object.
Then in your trigger you have to use the new.raw_user_meta_data json column to get at your role data. You can search for examples in discussions on doing that.

The bigger problem is if you are doing this from a client the user can technically put in any role he wants into the raw_user_meta_data with .update later....

[MattSolvesGames]

My 500 error when using very similar code to this was caused by the fact that new.raw_app_meta_data ->> 'custom_id' doesn't return anything except null even though I can see on the auth.users row that the user has the metadata set.

CREATE TABLE users (
    id UUID REFERENCES auth.users ON DELETE CASCADE NOT NULL PRIMARY KEY,
    custom_id VARCHAR(17)
);

CREATE FUNCTION handle_new_user() RETURNS TRIGGER LANGUAGE plpgsql SECURITY definer SET search_path = '' AS $$
BEGIN
    INSERT INTO public.users(id, custom_id) VALUES (new.id, new.raw_app_meta_data ->> 'custom_id');
    RETURN new;
END;
$$;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION handle_new_user();

Here's what I see on the auth.users entry:

  "raw_app_meta_data": {
    "provider": "email",
    "custom_id": "73849099371620888",
    "providers": [
      "email"
    ]
  },

Yet on the new row created in public.users, the column is null, which was giving me a 500 error since I had specified a NOT NULL constraint.

What ultimately solved the issue for me was moving custom_id to the user metadata rather than app metadata. It seems that users are being created first and later updated with the app metadata, even though I specified the metadata in the createUser method. It is bad that this behavior is inconsistent with that of the user metadata, which is created as expected with the rest of the user.

I can see now that I know the issue that this has been brought up before but I'm just leaving my comment here in case others are presented with troublesome 500 errors and find this thread instead.