Auth is using postgres user, which looks at public.users before auth.users

[awalias]

need to re-order the schema-path for postgres user, or make a new auth user instead

Discussed in supabase/supabase#3440

^{Originally posted by sebtoombs October 7, 2021}
Hey!

Description

I'm trying to use supabase locally for dev, and finding that having a public.users table causes issues. Namely, the queries for auth related actions (sign in etc) run e.g. "select xyz from users", and the schema path seems to be "$user", public, auth. This means auth actions find the public.users table instead and throw the following error: "Database error finding user".

This doesn't seem to be an issue in supabase hosted (worth noting the public.users table I'm using is identical to the "Stripe subscriptions" starter sample).

I suspect there's something going on with roles/users etc here that differs between the two environments.

I really hope I'm just making a silly mistake!

Steps to reproduce

Start local supabase env

Init a local supabase enviroment (assumes docker & supabase CLI are installed)

supabase init
supabase start

Create public.users table

I'm using the "supabase" user to run this. Is that appropriate?

psql -h localhost -p 5432 -U postgres -W

create table public.users (
  id uuid references auth.users not null primary key,
  full_name text,
  billing_address jsonb,
  payment_method jsonb
);

Attempt sign in

Via app, attempt sign in (supabase.auth.signIn)

Response:

{"code":500,"msg":"Database error finding user","error_id":"xxxxxxxxx"}

[soedirgo]

We now use supabase_auth_admin for GoTrue.

[Vinzent03]

I'm again facing this issue after running supabase db reset. After stopping the cli and restarting with supabase start everything works.