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

[macMikey]

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;

2. 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.

[GaryAustin1]

The code you show is a user's code. There have been versions of this in the past that no longer work. Supabase does not document how to create users without doing auth.admin.createUser() as the auth tables can change at any point and most columns should not be relied on. The documents show using id,email,raw_app_meta_data and raw_user_meta_data as far as I know. I could be missing a couple.

[macMikey]

for applications and flows where relying on client libraries is an option, that's great.
for applications and flows where users have to be created, manually, that's not great.

[Chrischuck]

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

[macMikey]

@Chrischuck let me know if you struggle with it. i think i've got a handle on making it work, since i am creating ephemeral users on-the-fly.

[angelodias-appno]

@macMikey how did you solve this?

[macMikey]

i submitted a PR to fix this
#1941

it's actually trivial. there are two ways:

1. when creating a user record, manually, set all the tokens to '', i.e. empty, OR
2. change the schema of the auth.users table: make all the token default values empty, instead of null (that's what the PR does)

BEGIN;

ALTER TABLE auth.users
  ALTER COLUMN confirmation_token SET DEFAULT '';
ALTER TABLE auth.users
  ALTER COLUMN recovery_token SET DEFAULT '';
ALTER TABLE auth.users
  ALTER COLUMN email_change_token_new SET DEFAULT '';
ALTER TABLE auth.users
  ALTER COLUMN email_change SET DEFAULT '';

COMMIT;

[angelodias-appno]

@macMikey I did something similar and it didn't work. I'll try your way. See: supabase/cli#3678

[angelodias-appno]

@macMikey this kinda worked. I need to add more fields to it:

BEGIN;

ALTER TABLE auth.users ALTER COLUMN confirmation_token DROP NOT NULL;
ALTER TABLE auth.users ALTER COLUMN recovery_token DROP NOT NULL;
ALTER TABLE auth.users ALTER COLUMN email_change_token_new DROP NOT NULL;
ALTER TABLE auth.users ALTER COLUMN email_change_token_current DROP NOT NULL;
ALTER TABLE auth.users ALTER COLUMN email_change_sent_at DROP NOT NULL;
ALTER TABLE auth.users ALTER COLUMN email_change DROP NOT NULL;

ALTER TABLE auth.users ALTER COLUMN confirmation_token SET DEFAULT '';
ALTER TABLE auth.users ALTER COLUMN recovery_token SET DEFAULT '';
ALTER TABLE auth.users ALTER COLUMN email_change_token_new SET DEFAULT '';
ALTER TABLE auth.users ALTER COLUMN email_change_token_current SET DEFAULT '';
ALTER TABLE auth.users ALTER COLUMN email_change SET DEFAULT '';

COMMIT;

[GaryAustin1]

It is a really bad idea to alter auth.users or any table in the auth schema.
Not sure that operation survives this change but it might: https://github.com/orgs/supabase/discussions/34270

[angelodias-appno]

@GaryAustin1 , hmmm yeah, that's bad. How am I supposed to seed auth then? Because I was doing an ugly workaround that does the db reset without seeding, run a .js file that adds the auth users, then seed with psql. That's ugly and bad because it makes supabase start fail (as it tries to seed without auth users).

[macMikey]

i don't disagree that altering the table is a hack. #34270 does not mention altering a table, though.
@angelodias-appno maybe you should append the PR with your additions. maybe some other folks will jump in and make some noise about it.
in the meantime, at least you can manually assign those fields when you create the record, and that will solve the issue.