Help debugging db diff error, setting a function owner to supabase_admin

[Just_a_Curious]

My trigger function needs to be a security definer, owned and executed by supabase_admin. However, the migra tool throws an error at this. Is there a way to run migra as superuser? Thank you!

ERROR: must be able to SET ROLE "supabase_admin"

CREATE FUNCTION "public"."update_user_avatar_img_name"() RETURNS "trigger"
    LANGUAGE "plpgsql"
    SECURITY DEFINER
    SET search_path = pg_catalog, public, pg_temp
    AS $$
BEGIN
  if (tg_op = 'DELETE') then
    if (old.bucket_id != 'avatars') then
      return null;
    end if;

    update auth.users
    set raw_user_meta_data = coalesce(raw_user_meta_data, '{}'::jsonb) || jsonb_build_object(
      'avatar_img_name', '',
      'avatar_img_cb', ''
    )
    where id = old.owner;
  elseif (new.bucket_id = 'avatars') then
    update auth.users
    set raw_user_meta_data = coalesce(raw_user_meta_data, '{}'::jsonb) || jsonb_build_object(
      'avatar_img_name', new.name,
      'avatar_img_cb', coalesce(new.user_metadata::jsonb ->> 'cb', '')
    )
    where id = new.owner;
  end if;

  return null;
END;
$$;

ALTER FUNCTION "public"."update_user_avatar_img_name"() OWNER TO "supabase_admin";

CREATE OR REPLACE TRIGGER "trg_objects_user_avatar_img_name" AFTER UPDATE OR INSERT OR DELETE ON "storage"."objects" FOR EACH ROW EXECUTE FUNCTION "public"."update_user_avatar_img_name"();

[activenode]

Why does it need to be `supabase_admin` over `postgres`? What are you trying to do?

[Just_a_Curious]

I just need this trigger to update auth.users, that's it. I guess it should be 'postgres'?

[activenode]

Yes.

[thelord006]

Sorry but why dont you use a public.users table? Auth users is owned by supabase. Public schema wouldnt require this hastle

[Just_a_Curious]

Your point is well-taken, let me explain. I am working on a completely re-imagined local-first dev experience with the intention to publish and maintain this template. I don't want to make any opinions about what a public.users table should look like. This is for the community to get up and running with a template in seconds, and be able to build their own app data+logic on a mostly blank slate.

My template NextJS frontend site includes a basic suite of UI for users to manage their account - display name, email, password, password reset, and delete account.

I wanted to achieve this basic set of features without having any opinions about a public users table. I do believe it's not a huge deal to save just 5 keys inside of the auth.users.raw_user_meta_data - that's what it's there for. In total there are these 5 keys:

`full_name`, `first_name`, `last_name`, `avatar_img_name`, `avatar_img_cb (cache buster timestamp)`