Database error finding user (when creating one), supabase-auth accessing public.users not auth.users

[sroussey]

Bug report

Describe the bug

I setup local dev a while ago, and now implementing auth. However when I call supabaseClient.auth.signUp(...) and look at the results in the net panel of dev tools, I see a result code error 500 and contents {code: 500, msg: "Database error finding user", ...} when posting to http://localhost:8000/auth/v1/signup

System information

• OS: MacOS (latest)
• Browser: Chrome (latest)
• Version of supabase-js: 1.11.9
• Version of Node.js: v14.16.1

[sroussey]

Updated to @supabase/supabase-js@1.11.10 but no change.

[sroussey]

BTW: how to we update the local supabase docker suite? The supabase command line does not have an update command.

Edit: I tried supabase stop && cd .supabase/docker && docker-compose pull && cd ../.. && supabase start, but no effect on this bug.

[sroussey]

Note: my postgress for this is on a different port as i have a lot of local projects. FYI.

Edit: I reset everything to stock and redid init but having issues.

[sroussey]

Looking in the auth docker log:

time="2021-05-08T18:29:10Z" level=error msg="500: Database error finding user" component=api error="error finding user: ERROR: column users.aud does not exist (SQLSTATE 42703)" method=POST path=/token referer="http://localhost:4000/" remote_addr="172.20.0.2:53928" request_id=86482026-2b48-48fb-9197-a2fc00f06e93

Which is weird, as I can see auth.users.aud in the db. I do have a public.users that does not have aud however.

[sroussey]

OK, that is the issue. The auth code is asking for users not auth.users and so it is seeing the public.users. I have this because I am trying to run the sample https://github.com/vercel/nextjs-subscription-payments

[MaxAltena]

It seems that when I use the example from the documentation for creating users in public.users table from auth.users on create I get a similar error.

Steps

• Follow example from documentation
• Invite user through Authentication tab inside supabase
• Receive the following error: Inviting user failed: Database error saving new user.

Might this be somewhat relatable?

[jasonho-lynx]

@sroussey did you manage to get supabase.auth to point to the auth.users table instead?

[stevenroussey-privicy]

No. I had to rename the public.users to public.accounts.

[jasonho-lynx]

Alright. I did manage to find a temporary workaround by rearranging the entries in search_path to let auth come first before public.

Ideally supabase.auth should only focus on the auth.users table instead of relying on the search path, but I haven't managed to find the section that specifies it in gotrue-js yet.

[jasonho-lynx]

It seems that when I use the example from the documentation for creating users in public.users table from auth.users on create I get a similar error.

Steps

• Follow example from documentation
• Invite user through Authentication tab inside supabase
• Receive the following error: Inviting user failed: Database error saving new user.

Might this be somewhat relatable?

@MaxAltena I'm not sure that it's the same since you're using the Supabase hosted version. Your problem sounds more similar to the one specified here.

[stevenroussey-privicy]

How do you upgrade those?

[jasonho-lynx]

How do you upgrade those?

Apologies, I realised even the new version still did not work right away (deleted my comment). However, my earlier comment about editing search_path (see the attached link there) still works.

tldr: on this line

change ALTER ROLE postgres SET search_path = "$user", public, auth;

to ALTER ROLE postgres SET search_path = "$user", auth, public;

Note that this will have implications on your db's searching behaviour as described in the postgres docs

[wiwatsrt]

How do you upgrade those?

Apologies, I realised even the new version still did not work right away (deleted my comment). However, my earlier comment about editing search_path (see the attached link there) still works.

tldr: on this line

change ALTER ROLE postgres SET search_path = "$user", public, auth;

to ALTER ROLE postgres SET search_path = "$user", auth, public;

Note that this will have implications on your db's searching behaviour as described in the postgres docs

It's Work!
Thank You.