auth: Error 500: Database error querying schema when logging in created user - (with cause and proposed fix)

Bug report

  • I confirm this is a bug with Supabase, not with my own application.
  • I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

After creating a user and an identity, and then attempting to authenticate them, Supabase returns the following error: 500: Database error querying schema In the log there is more detail: error finding user: sql: Scan error on column index 3, name "confirmation_token": converting NULL to string is unsupported

To Reproduce

  1. as described here, new users can be created with an sql function
CREATE OR REPLACE FUNCTION public.create_user(
    email text,
    password text
) RETURNS void AS $$
  declare
  user_id uuid;
  encrypted_pw text;
BEGIN
  user_id := 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, 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', '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;
  1. however, when trying to authenticate one of these users via /auth/v1/token?grant_type=password, the error is triggered

Expected behavior

token returned

Screenshots

N/A

System information

N/A

Additional context

This error is triggered because the following four columns must not be null in auth.users: confirmation_token, email_change, email_change_token_new, recovery_token, i.e. they must at least be set to empty. I propose that the table definition for auth.users be updated to default each of those columns to ''. ** Note that in the docs, none of these columns are mentioned.

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Reactions: 4
  • Comments: 16

Commits related to this issue

Most upvoted comments

Thank you for posting this. Big issue when seeding data until i found this

The problem, even with the PR is that Supabase would need to adopt that plan on all additions to the table they may make in the future. Note they could even remove columns…

the PR is intended to fix what appears to be a bug in the auth.users schema. after running into this issue, reading the schema for the table, and the auth source code, this seems like it was an oversight.