Be careful when editing Supabase auth table

I suddenly couldn't log in to one of my test apps. Supabase was returning "Database error granting user".

I discovered the issue when I checked the authentication logs for my user, which gave a more specific error message than what was returned in the network request.

"error":"error creating refresh token: failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: duplicate key value violates unique constraint \"refresh_tokens_pkey\" (SQLSTATE 23505)"

I realized it was because I had dumped another project's database for a test (which included the auth table) and that inserted stale rows into auth.refresh_tokens. This table uses a bigint auto-increment as its primary key. The dump inserted rows with large IDs but didn't update PostgreSQL's sequence counter (which was non-obvious), so on every login GoTrue asked the sequence for the next ID, got a value already taken by the imported rows, and hit a duplicate key violation every time.

Deleting all rows from auth.refresh_tokens and auth.sessions fixed the issue. These are temporary login states and are recreated on the next successful login, so no actual data is affected.

A lesson is to check the authentication logs first to debug issues with logging in. But the bigger takeaway is to be careful when editing Supabase's auth table because there could be unintended and non-obvious consequences. When in doubt, stick to --schema=public when dumping data for testing.