Create User with AppMetadata

[mosnicholas]

App metadata seems to be the recommended way to pass app-specific information to the user create flow. If it is specified in the createUser api call, it should be used in the create user GoTrue function.

I'm trying to pass the following flag to app_metadata: { is_invited: true } to be able to use in a Postgres trigger and use different logic based on whether a user was invited or not.

I'll be using user_metadata for now, but feels like app_metadata is more appropriate?

/**
   * A custom data object to store the user's application specific metadata. This maps to the `auth.users.app_metadata` column.
   *
   * Only a service role can modify.
   *
   * The `app_metadata` should be a JSON object that includes app-specific info, such as identity providers, roles, and other
   * access control information.
   */
  app_metadata?: object

https://github.com/supabase/gotrue/blob/40aed622f24066b2718e4509a001026fe7d4b76d/internal/api/admin.go#L352-L362

[J0]

Hey @mosnicholas,

Yes, It is possible to create a user and set app_metadata as you wish when creating a user.

const { data, error } = await supabase.auth.admin.createUser({
  email: '[email protected]',
  password: 'password',
  app_metadata: { is_invited: 'true' }
})

You can choose between using user_metadata and app_metadata as needed. Please let us know if you run into any issues while using the flow or if there are any doubt/concerns that we haven't addressed.

Let us know! Joel

[mosnicholas]

@J0 -- I am using a Postgres trigger to check whether users are allowed to sign up / sign in. If a user is invited, I want to short circuit that logic, and always allow the user to be created in our db. I'm passing the is_invited flag through the API call to ensure that my postgres trigger can act on the different logic paths based on whether a user was invited or is signing up from the app.

My understanding from the docs is that the best place to put this flag (is_invited) is in the app_metadata (ie. app-specific access control info). However, the user is being created in the DB before the app_metadata object is passed into the user row, and as a result, I have to use the user_metadata object to pass the flag back to the Postgres trigger.

This is why I raised the issue -- essentially, we're creating the app_metadata object as a fixed object, and then updating the user object after creation. Feels like maybe we should create the app_metadata object with the values passed to the API on the first pass instead of updating it after the user row is created?

For reference, here's my Postgres trigger:

CREATE OR REPLACE FUNCTION public.check_user_email_signup()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
  email_domain text;
  domain_exists boolean;
  all_domains text[];
BEGIN
    -- Check if the current user was invited
    IF NEW.raw_user_meta_data->>'is_invited' = 'true' THEN
      return NEW;
    END IF;

    -- If not, check that user is allowed to sign up
    ...

    -- If not, raise an exception
    RAISE EXCEPTION 'Email domain is not allowed';
END;
$function$
;

[J0]

Hey @mosnicholas,

Thanks for the comprehensive overview - I may be missing something but I agree that we should probably set the params.AppMetadata together with the provider fields before creating a user.

At the same time, an issue with changing the behaviour is that people may have update triggers or similar depend on the existing behaviour where the update is performed after the user object is created.

I'll discuss with the team and get back. Thanks!

[mosnicholas]

Hey @J0 any progress on your conversations here? :) I've had to hijack the raw_user_meta_data json which has resulted in a bit of data loss :(

[mosnicholas]

@J0 this set up is causing a separate problem all together. Because app_metadata is updated after creating a user row in the database, we can't listen to the user insert call to update app metadata. Eg. if you have a trigger like so:

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

and that function updates the app_metadata field. There's now a race condition because after the user is created, my trigger function is called, and app metadata is updated separately.

This trigger would be helpful to set up multitenant mapping and ensure that we have the right tenant ids set up properly. Curious what you think!

[GaryAustin1]

Without digging into this in detail, it seems that if the update gotrue does on raw_app_meta_data just merged in existing keys from the start then it would not care if the extra metadata was added on the insert trigger or the update trigger.

[craxrev]

I have a similar situation where I was following a guide on how to integrate picket, seems to be not working so far, the issue is when I add custom app_metadata and user_metadata using supabase.auth.admin.createdUser() method from the sdk it does not get reflected when inserting in a "profiles" table through an AFTER INSERT trigger, as in both app_metadata and user_metadata have default values and not the ones I provided, so I had to also add an AFTER UPDATE trigger, here's how I did it:

-- inserts a row into public.profiles
CREATE OR REPLACE function public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER set search_path = public
AS $$
BEGIN
  IF (TG_OP = 'INSERT') THEN
    INSERT INTO public.profiles (user_id, email)
    VALUES (NEW.id, NEW.email);
  END IF;
  IF (TG_OP = 'UPDATE') THEN
    UPDATE public.profiles
    SET (wallet_address, username) = (NEW.raw_app_meta_data->>'walletAddress', NEW.raw_user_meta_data->>'username')
    WHERE user_id = NEW.id;
  END IF;
  RETURN NEW;
END;
$$;

-- trigger the function every time a user is created
CREATE OR REPLACE trigger on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();

-- trigger the function every time a app/user metadata is updated
CREATE OR REPLACE trigger on_auth_user_metadata_updated
  AFTER UPDATE ON auth.users
  FOR EACH ROW
  WHEN (OLD.raw_app_meta_data <> NEW.raw_app_meta_data OR OLD.raw_user_meta_data <> NEW.raw_user_meta_data)
  EXECUTE PROCEDURE public.handle_new_user()

But still not sure if this will cause any problems down the line..

Edit: fix inserting null values

[activenode]

Wanted to push this one. For someone implementing triggers based on app_metadata, this isn't possible right now. I also had to write a weird workaround as not even the AFTER UPDATE worked as it STILL didn't have the app_metadata - so I wrote an SQL logic that runs on update but also checks if the values are set or not - if not, it just continues. That's kinda weird because it allows for orphans.

[J0]

Hey @mosnicholas,

Sorry, I missed the past messages. I've forgotten a large part of the context but you might be able to use custom claims auth hooks to achieve your use case. With respect to this particular issue, I'll check again if it's possible to create the user with app_metadata directly instead of creating and updating.

Hope to circle back with updates soon

[activenode]

With respect to this particular issue, I'll check again if it's possible to create the user with app_metadata directly instead of creating and updating.

That'd be helpful I think as it would allow to not wait for 2 Updates (I had to bypass an INSERT and another UPDATE and then the following UPDATE did contain the data). Thanks!

[J0]

Short update here - we're doing a quick scan for update triggers to see how we can move ahead

[J0]

Notes for Testing

1. After this change an update trigger on auth.user should continue to receive the same app_metadata
2. After this change any app_metadata passed in via createUser should also be passed into the payload of an insert trigger on auth.users.

[J0]

More notes:

I'm not sure that 1. will continue to hold. It looks like if we do the update prior to creation:

if params.AppMetaData != nil {
	if terr := user.UpdateAppMetaDataWithoutCommit(params.AppMetaData); terr != nil {
 		return terr
	}
	}
    ...
	err = db.Transaction(func(tx *storage.Connection) error {

The update doesn't seem to fire on my end. The overarching concern is that it might affect someone who is using the app_metadata field in the update trigger.

Waiting for internal data to check impact.

[activenode]

Thanks for the update. Here's one thought of mine. When I talked to @hf about hooks in general, we were discussing maybe not having so much of triggers either in auth schema or in storage and going more of the hook route.

And since i mentioned storage we might need to have more generalized Database | Hooks (UI-wise) instead of just Auth | Hooks.

What I'm trying to say is:

Yes, it would be helpful to have this resolved to work with triggers but if we just would have generalized hooks to call like hook.onUserCreated or even storage.onFileUploaded we could abstract some of the complexity of allowing generic triggers in those schemas away.

HOWEVER, and that's a big one to consider: This MUST work in self-hosted/local development or else this is kinda useless as one won't be able to develop locally and this currently doesn't even work with the existing hooks.

Hence I'm saying: Maybe general hooks are the solution which can be triggered by whatever Supabase internal pg function but they MUST work locally and currently they don't.

[J0]

This MUST work in self-hosted/local development or else this is kinda useless as one won't be able to develop locally and this currently doesn't even work with the existing hooks.

Thanks for flagging this - I'll look into what's going on there and get back. For now could you try configuring the Hooks via config.toml ? It should work there- feel free to tag me if it doesn't.

[J0]

@activenode can I check where you're seeing the above-mentioned screen? Trying to replicate the issue but realized I'm not sure where it's from as it doesn't look like expose the page on CLI studio.

Are the screenshots from the self-hosted stck on supabase/supabase? Wondering if you could share more on what your Auth config / yml file looks like there if so.

Lmk

[activenode]

Sorry, that one was on me. I just opened the respective URL on local. Mistaken thinking.

[vyas-meet]

I wrote an SQL logic that runs on update but also checks if the values are set or not - if not, it just continues

Hey, can I get an example of this? Just getting started with supabase and I've got a similar use case where I want to pass on app_data on user creation to trigger insertion into other tables. I got it working with user_metadata only to learn that users can edit it, and since I'm assigning a permission role to users I don't want that.

[activenode]

I don't have it at hand but I also don't recommend it, it's rather hacky. But basically all I am doing is:

1. I set app_metadata { checkValue: true, WHATEVER_OTHER_CONFIG_YOU_NEED: 123 } (this is Supabase client, not SQL)
2. Then, within the SQL (a trigger that runs on update on auth.users) I check if auth.jwt() -> app_metadata -> 'checkValue' is defined. Because if it is, it means the metadata is ready.

Cheers - activeno.de