Hello everyone, I am having some trouble with the js client

signIn()

method. I've added the users into my `auth.users`table using a sql dump from another PostgreSQL db (modified it a bit so I could add the required fields), now when I try running

supabase.auth.signIn()

passing the users' `email`and

password

I get the following error:

json
"error": {
    "message": "Database error querying schema",
    "status": 500
}

Yet, If I add a new user using the

signUp()

method I am able to login the newly created user without any problems. Is there any thing that I might have done wrong on during the migration process? > I looked into this discussion https://github.com/supabase/supabase/discussions/3440 and I am unsure if they are related issues as I am not self hosting the db. Thanks in advance 🙂

Others seem to be having issues just populating the user table... https://github.com/supabase/supabase/discussions/5418 is one discussion, there are others.

When I tried doing a proof of concept I run this query on the supabase sql editor

sql
insert into auth.users (id, instance_id, aud, role, email, encrypted_password, email_confirmed_at, confirmation_token, recovery_token,  email_change, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, email_change_token_new, invited_at, confirmation_sent_at)
values ('<uuid on old db>',  '00000000-0000-0000-0000-000000000000', 'authenticated', 'authenticated', '<my email>', '<my hashed pw>', '2021-11-17T12:21:32.447Z', '', '', '',  '2021-11-16T12:21:32.447Z', '{"provider":"email","providers":["email"]}', '{}', false, '2021-11-16T12:21:32.447Z', '2021-11-16T12:21:32.447Z', '', '2021-11-17T12:21:32.447Z', '2021-11-17T12:21:32.447Z');

I was able to sign in without any problem, 🤔 I wonder what would be difference.

I just know several have run into errors just trying to copy in from another database to the user table only. Sorry, I've not looked into myself to figure out the reason.

may be this function is related to the issue

sql
CREATE OR REPLACE FUNCTION auth.uid()
 RETURNS uuid
 LANGUAGE sql
 STABLE
AS $function$
  select
  nullif(
    coalesce(
      current_setting('request.jwt.claim.sub', true),
      (current_setting('request.jwt.claims', true)::jsonb ->> 'sub')
    ),
    ''
  )::uuid
$function$

I more curious how your test case worked. auth.uid() is normally used for RLS and such, not sure if signIn would call it.

I'll re-run it shortly.

There is also a database log now under settings that might give more info on the actual failure...

unfortunately the error is not quite informative.

what is main one?

🤔 I am thinking, does changing the db password has any thing to do with the issue I am facing?

how old is that instance just out of curiosity?

we created the instance in Sept last year, but we there was no users added to it. We just had a single table until yesterday.

I tested on my test instance which is a month old and it worked. Wonder if somehow the user table was different then and did not get updated...

I've changed the db password on Monday, this the only odd thing I did.

The code the error is in is also checking for a password match.

go
// IsNotFoundError returns whether an error represents a "not found" error.
func IsNotFoundError(err error) bool {
    switch err.(type) {
    case UserNotFoundError:
        return true
    case RefreshTokenNotFoundError:
        return true
    case InstanceNotFoundError:
        return true
    }
    return false
}

based on https://github.com/netlify/gotrue/blob/919c35ee31af28780e92b57c91ec1fe97f6b8e1f/models/errors.go#L4 none of these is the error I am getting.

That code may actually be looking for email match. Are all of your emails lower case?

100%

func FindUserByEmailAndAudience(tx *storage.Connection, instanceID uuid.UUID, email, aud string) (*User, error) { return findUser(tx, "instance_id = ? and LOWER(email) = ? and aud = ?", instanceID, strings.ToLower(email), aud) is the email call gotrue makes to db that is failing, ALTHOUGH maybe that error is somewhere else and i did not find it. Might be something similar in fetching password.

makes sense.

No ideas at this point, but the password check occurs after, if that error does not occur. So some basic access to aud,instance,email is failing on your auth.user table....

that's super weird, I was checking some ppl have this issue when they drop the auth.users table which isn't my case