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

[macMikey]

Bug report

• [X] I confirm this is a bug with Supabase, not with my own application.
• [X] 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 https://github.com/supabase/auth/pull/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: https://github.com/supabase/cli/issues/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.

[angelodias-appno]

@macMikey I added a suggestion to the PR (as I can't change it directly).

And I think we're in unison here: it's a hack. It's bad. If there's a better way, I'd love to use it... but if the hack is the only way, then this is the way.

[GaryAustin1]

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 only official way is to use REST API calls. Anything else you should try and pull off with setting all the values you need in your SQL to set AND know that without notice that plan could break if they add a new row...IMO.

[angelodias-appno]

So maybe suggesting a .js file that can be run before the seeds? I'd love to implement something like that but I have never touched go before.

[macMikey]

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.

[GaryAustin1]

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.

I'm sure Supabase will decide if it makes sense or not. Just commenting they need to fully adopt as they will likely in the future add more columns. I believe their GO code forces all the values so they don't need defaults and they have never that I know of have documented generating users with SQL directly. I've seen many users though fill in the two tables to seed. Most are doing it for testing though and don't necessarily need every thing like later logging in a user requires.