Allow exception created by a user inside of a trigger function to bubble up when using signUp/signIn

[silentworks]

Feature request

Is your feature request related to a problem? Please describe.

It would be good if an exception created by a user inside of a Postgres Trigger function would bubble all the way up to the client code. Currently, I have created an exception but I keep on getting the same default "Database error saving new user" error although I know the signUp process is failing due to my exception. I would prefer to see the message from my exception here.

Another related issue is that the confirmation email is sent before the record gets inserted into the database, not sure why this happens, because by raising an exception the database normally rollback all transactions so the email being sent shouldn't happen before the transaction is completed and successful.

[kiwicopple]

Transferring this to our GoTrue server as it will need to be handled there first.

Some code for testing from silentworks:

CREATE OR REPLACE FUNCTION public.username_exists()
    RETURNS TRIGGER 
    LANGUAGE plpgsql 
    SECURITY DEFINER SET search_path = public
    AS
$$
BEGIN
    IF EXISTS(SELECT 1 FROM profiles WHERE username = new.raw_user_meta_data->>'username' LIMIT 1) THEN
        RAISE EXCEPTION 'Username already exists';
    END IF;
    RETURN new;
END
$$;

/* Trigger itself */
CREATE TRIGGER on_auth_user_creation
    BEFORE INSERT ON auth.users
    FOR EACH ROW EXECUTE PROCEDURE public.username_exists();

However, I think we wouldn't want anything leaking from the database so we should only bubble up the error under specific instances.

PostgREST has a good way of handling this - we should use the same so that it's consistent everywhere:

https://postgrest.org/en/v8.0/api.html?highlight=exceptions#raise-errors-with-http-status-codes

eg:

RAISE sqlstate 'PT409' using
  message = 'Username conflict',
  detail = 'Username already exists.',
  hint = 'Try another username';

[chipilov]

I think there is a second, related issue here - if a BEFORE trigger modifies the record to be inserted into the table, the API still returns the data that was passed in the request, NOT the data which was actually saved in the auth.users table.

@kiwicopple Do you want a separate issue for that or can it be handled with this one?

[silentworks]

@chipilov that should be raised as a separate issue as it's not directly related to this one.

[chipilov]

@chipilov that should be raised as a separate issue as it's not directly related to this one.

Ok, opened a separate issue: #288

[github-actions]

🎉 This issue has been resolved in version 2.6.34 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀