Local dev, same table name in public & auth schemas - breaks auth

[sebtoombs]

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"}

[awalias]

this is a valid bug so I will spin it into an issue on supabase/cli

the problem is gotrue is using the postgres user, and as you correctly identified the ordering of that user's schema search path matters:

a hot fix would be to reorder the search path so that auth comes first, or you could create a new auth user/role in postgres and plug it in here

[sebtoombs]

Thanks, I had a suspicion it would be something along these lines. I've already been using said hotfix (thanks again), but I prefer the approach of adding a user for auth 👍