Is there a better way to integrate with custom phone auth provider?

[gerynugrh]

For some users in ID, integrating phone auth with existing provider (Twilio, Message Bird) is not preferable given the high price per sms compared to other provider. Hence, currently we do this by using the following trigger

DROP TRIGGER IF EXISTS trigger_otp ON auth.users;

CREATE OR REPLACE FUNCTION trigger_otp()
RETURNS TRIGGER
AS $$
DECLARE otp bigint;
BEGIN
    IF OLD.confirmation_token = NEW.confirmation_token THEN
      RETURN NEW;
    END IF;
    IF NEW.confirmation_token = '' THEN
      RETURN NEW;
    END IF;
    otp := FLOOR(RANDOM() * 900000 + 100000);
    INSERT INTO public.tmp_otp_trigger (code, user_id, phone, confirmation_token) values (otp, NEW.id, NEW.phone, NEW.confirmation_token);
    NEW.confirmation_token := encode(sha224(concat(NEW.phone,otp)::bytea), 'hex');
    NEW.confirmation_sent_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY definer;

CREATE TRIGGER trigger_otp BEFORE INSERT OR UPDATE ON auth.users FOR EACH ROW EXECUTE PROCEDURE trigger_otp();

It works by generating another random OTP, hash, and then update auth.users while also inserting row into another temporary table for OTPs. Later, there will be an edge function that triggers when a record is inserted into tmp_otp_trigger.

We use this in our apps and it's working well, but is there a better way to do this that we haven't notice yet? Also side question, do you happen to know how to generate otps other than using pgsql random() function (given that this function is not cryptographically secure)?

[ZelimDamian]

@gerynugrh We are having a similar issue and have been struggling to make it work with a different SMS gateway. Could you provide more information about how the trigger above fits into the Supabase auth so we could maybe replicate?

[ZelimDamian]

Thank you for the explanation @gerynugrh ! Will try to implement the same as our current approach to treat signed phonenumbers as passwords feels quite off.

[nadirbelarouci]

@gerynugrh WHy don't you trigger the edge function from the auth.users table right away ? why the tmp table is necessary ?

[gerynugrh]

Hi @nadirbelarouci I would need to modify the confirmation token expected from auth.users. This requires me to modify auth.users after an update is happening. As far as I'm aware, I can't modify auth schema in supabase from an edge function, hence I need to do it via db trigger.

[nadirbelarouci]

Hi @gerynugrh, I am still missing some context, why would you change the confirmation token ? isn't this done by the trigger: NEW.confirmation_token := encode(sha224(concat(NEW.phone,otp)::bytea), 'hex');
Another question 😄
Will this prevent sending the sms from default providers ?

[gerynugrh]

Hi, I haven't worked on this problem for some time. But if my memory servers correctly it was because supabase only store the hased confirmation token. Hence there is no way for us to know the OTP that supabase sent to the SMS provider from the db record.

If we want to send an SMS OTP to another provider, we need to know the OTP code that supabase expect, but this impossible by reading the newly inserted auth.users record alone. Hence what we're doing is generate a new OTP, update auth.users to force supabase to acknowledge the newly generated OTP and send the OTP to another SMS provider.

[lucaspeyrin]

Any updates on this ? Is there a way to have a custom SMS provider ?

[4lirexa]

check out https://medium.com/@lavisht22/optimizing-sms-authentication-costs-with-supabase-a-deep-dive-4a2f1b4a1a68

[AliKales]

https://medium.com/@lavisht22/optimizing-sms-authentication-costs-with-supabase-a-deep-dive-4a2f1b4a1a68
this is working very well but do not use Twilio as your provider otherwise it will fail just choose any other one