auth.uid() returning null within trigger

[tsh356]

I have the following function and trigger to add a user the public.users after insert into auth.users:

CREATE FUNCTION
  public.create_public_user_for_new_user()
  RETURNS TRIGGER AS
  $pga$
  BEGIN
    INSERT INTO public.users (id, active, email, created_by)
    values (new.id, false, new.email, auth.uid());
    RETURN NEW;
  END;
  $pga$ LANGUAGE plpgsql SECURITY definer;

CREATE TRIGGER
  create_user_on_signup
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE PROCEDURE
    public.create_public_user_for_new_user();

auth.uid() is always null in this function. How an I access auth.uid() in this context?

[GaryAustin1]

Isn't new.id the user's uid from the auth user table?

[tsh356]

new.id is the new users id. I'm needing the UUID of the user that created this new user.

[steve-chavez]

How are you calling the create_public_user_for_new_user function? Are you using supabase-js?

[tsh356]

It’s a trigger executed when a row is inserted into auth.users, but yes that row is getting inserted via auth.signUp() in the js library.

[GaryAustin1]

Not sure then what you are looking for. The row is inserted by the "auth" process, not a specific user. The user that signed up in JS client has no uid until processed by "auth" (gotrue) and inserted in the auth table. There is no creating uid.

[tsh356]

The use case is a multi tenant app where a user belonging to tenant A creates accounts for other users who need to belong to tenant A as well. Auth.signUp() is being called from an authenticated user and it appears the jwt is being passed to the server as well. I hoped auth.uid would reference this jwt, but apparently not.

Supabase is proving difficult to make work for the multi tenant apps. Any suggestions?

[GaryAustin1]

You might check out the user metadata and see if that works. signUp and update seem to allow it, maybe the trigger/function can get that from what ever field it is stored in the user table and store it the public one. I've not messed with it though.

[wadclapp]

[tsh356]

Actually, for this use case, you should be able to use NEW.id inside the trigger function which is the new users id.

[wadclapp]

You're right. Sorry didn't fully read your question.

[jasonho-lynx]

Perhaps when signUp is called, you can pass in an extra param options: {data: {created_by: <CURRENT_USER_ID>}} (ref: https://github.com/supabase/gotrue-js/blob/master/src/GoTrueClient.ts#L115) which I believe will get saved into auth.users under the raw_user_meta_data JSONB column. Then in your trigger, instead of auth.uid(), you query the raw_user_meta_data column with something like raw_user_meta_data -> 'created_by'.