Postgres trigger update set_claim function access denied

Hi,

I have a problem with my trigger and function. I have set a trigger to execute on Update when the public users table updates:

BEGIN
  -- Update the custom claim for onboarding
  PERFORM public.set_claim(
    NEW.id, 
    'onboarding'::text, 
    to_jsonb(NEW.onboarding)
  );

  RETURN NULL;
END;

BEGIN
  -- Update the custom claim for onboarding
  PERFORM public.set_claim(
    NEW.id, 
    'onboarding'::text, 
    to_jsonb(NEW.onboarding)
  );

  RETURN NULL;
END;

CREATE OR REPLACE TRIGGER trigger_users_onboarding_updated
AFTER UPDATE OF onboarding ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.update_user_onboarding_claim();

CREATE OR REPLACE TRIGGER trigger_users_onboarding_updated
AFTER UPDATE OF onboarding ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.update_user_onboarding_claim();

The onboarding is set by default to True, however the JS client updates this value to false and this is working totally fine. The problem starts when the onboarding switches from true to false as the trigger does not execute the function. The idea is that when the public.users table gets modified and the onboarding changes to false, its updating in the auth.users tables app_metadata onboarding claim from true to false, however its not doing that.

I set the security to definer and the weird thing is that its working for the subscriptions table totally fine. The subscriptions trigger and functions get executed correctly but not not for the onboarding.

If I manually update the value inside the public.users table inside the supabase dashboard from onboarding true to false inside the row field, its getting updated but not when executed by the JS client.

The only difference between the public.subscriptions and public.users table is that the .users table has 2 RLS policies (Insert and an additional Update).

Bildschirmfoto_2024-01-09_um_21.24.43.png

Bildschirmfoto_2024-01-09_um_21.23.21.png

Bildschirmfoto_2024-01-09_um_21.22.57.png

Bildschirmfoto_2024-01-09_um_21.30.43.png

Postgres trigger update set_claim function access denied

Hi,

I have a problem with my trigger and function. I have set a trigger to execute on Update when the public users table updates:

BEGIN
  -- Update the custom claim for onboarding
  PERFORM public.set_claim(
    NEW.id, 
    'onboarding'::text, 
    to_jsonb(NEW.onboarding)
  );

  RETURN NULL;
END;

BEGIN
  -- Update the custom claim for onboarding
  PERFORM public.set_claim(
    NEW.id, 
    'onboarding'::text, 
    to_jsonb(NEW.onboarding)
  );

  RETURN NULL;
END;

CREATE OR REPLACE TRIGGER trigger_users_onboarding_updated
AFTER UPDATE OF onboarding ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.update_user_onboarding_claim();

CREATE OR REPLACE TRIGGER trigger_users_onboarding_updated
AFTER UPDATE OF onboarding ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.update_user_onboarding_claim();

Postgres trigger update set_claim function access denied

Postgres trigger update set_claim function access denied

Similar Threads

Similar Threads

Similar Threads