DEV Community

01kg
01kg

Posted on

Supabase | How to add a user in SQL Editor

TL;DR:

CREATE OR REPLACE FUNCTION public.create_user(
    email text,
    password text,
    uuid uuid DEFAULT NULL
) RETURNS void AS $$
  declare
  user_id uuid;
  encrypted_pw text;
BEGIN
  user_id := COALESCE(uuid, gen_random_uuid());
  encrypted_pw := crypt(password, gen_salt('bf'));

  INSERT INTO auth.users
    (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, recovery_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token, email_change, email_change_token_new, recovery_token)
  VALUES
    ('00000000-0000-0000-0000-000000000000', user_id, 'authenticated', 'authenticated', email, encrypted_pw, '2023-05-03 19:41:43.585805+00', '2023-04-22 13:10:03.275387+00', '2023-04-22 13:10:31.458239+00', '{"provider":"email","providers":["email"]}', '{}', '2023-05-03 19:41:43.580424+00', '2023-05-03 19:41:43.585948+00', '', '', '', '');

  INSERT INTO auth.identities (id, user_id, identity_data, provider,provider_id,last_sign_in_at, created_at, updated_at)
  VALUES
    (gen_random_uuid(), user_id, format('{"sub":"%s","email":"%s"}', user_id::text, email)::jsonb, 'email','eefcbf79-28b0-4070-acf4-54f3a941f932', '2023-05-03 19:41:43.582456+00', '2023-05-03 19:41:43.582497+00', '2023-05-03 19:41:43.582497+00');
END;
$$ LANGUAGE plpgsql;

-- If no uuid is given, function would create a random one
select public.create_user('YOUR@EMAIL.com', 'YOUR_PASSWORD', 'THIS UUID IS OPTIONAL');

-- If you do not wanna use this function again, drop it:
drop function if exists public.create_user cascade;

Enter fullscreen mode Exit fullscreen mode

Top comments (0)