Supabase http_get triggered but failed on creating user

Question

Trigger Function below would trigger if there's a new record inserted into auth users table in supabase

declare
api_url text;

begin
SET search_path = extensions;

api_url := concat('https://cccccxzxxt?nme=',new.id);

SELECT * FROM http_get(api_url);

SET search_path = none;

return new;

end;

The http_get does get triggered all ok on this but I'm getting {"code":500,"msg":"Database error saving new user","error_id":"xxx"} when trying to create a new user and there's no user created in the auth users table.

chipilov · Accepted Answer · 2022-06-14 16:39:10Z

Most likely, the problem is that you are setting the search_path to none at the end of your trigger. This means that any SQL statements that run AFTER your trigger will fail to execute UNLESS they use ONLY fully-qualified names (e.g. auth.users vs users).

Indeed, when I reproduce your trigger, I see the following error in the PostgreSQL logs:

{
   "query": "UPDATE \"users\" SET \"role\" = $1, \"updated_at\" = $2 WHERE users.id = $3",
   "event_message": relation "users" does not exist
}

This is most likely the update that Supabase's GoTrue server executes right after inserting the new user (i.e. right after your trigger). See: https://github.com/supabase/gotrue/blob/bfaa68ec2412abb44b76838dcfb817e68eb49aed/api/signup.go#L311).

To solve the issue, you can either:

Avoid manipulating the search_path altogether and instead use the fully qualified name of function you are calling, i.e. extensions.http_get
Use PostgreSQL's special syntax for changing the values of configuration parameters (e.g. search_path) ONLY for the duration of a function and then reverting them back. See: https://www.postgresql.org/docs/current/sql-createfunction.html, SET configuration_parameter { TO value | = value | FROM CURRENT })

Collectives™ on Stack Overflow

Supabase http_get triggered but failed on creating user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related