Supabase Auth Migration Scope — Which Tables to Migrate (and Which to Skip)

Why both tables are required, even for email + password

The most common misconception is that auth.identities is just for OAuth — Google, GitHub, Apple sign-ins. It used to be. Since GoTrue v2 (Supabase's auth service), every sign-in path looks up auth.identities to discover which providers a user has linked, including email and phone. When you create a password user via the Admin API or the signup flow, Supabase quietly inserts an identity row with provider='email' and a JSON blob pointing back to auth.users(id).

If you migrate auth.users but skip auth.identities, every sign-in fails — even though the bcrypt hash is sitting right there in auth.users.encrypted_password. GoTrue refuses to authenticate a user whose identity row it can't find. This is also why the "create a fake user and sign in as them" trick requires you to insert an auth.identities row by hand — without it, Supabase doesn't believe the user can authenticate at all.

So the minimum-viable auth migration is exactly the two tables forum posts converge on — but the framing "users for the hash, identities for OAuth" is wrong. Both are load-bearing for both sign-in types.

The OAuth control-plane gap

Copying auth.identities perfectly still doesn't get your Google sign-in users back. Provider client IDs, secrets, redirect URIs, and JWT secrets all live in Supabase's auth control plane (the settings you configure under Authentication → Providers), not in any table you can pg_dump. The target project has none of this until you re-enter it manually.

Result: an OAuth user lands at the new project, the identity row exists, but the login fails with "Unsupported provider" because Google isn't enabled on the target. The fix is dashboard work, not SQL work — open the target's auth settings, enable each provider used by the source, paste the client ID and secret. If the source used a custom redirect URL, update it in the Google Cloud Console too, since the target lives on a different Supabase URL.

MFA: a third table, only if anyone enrolled

If users have enabled TOTP or WebAuthn factors, their factor records live in auth.mfa_factors. Skip it and those users get prompted to re-enroll their authenticator app on first sign-in (which is mildly painful but not catastrophic). For a paid SaaS where security-minded users opted into MFA, migrating this table is worth the extra five lines of SQL.

The two siblings — auth.mfa_challenges (pending challenges) and auth.mfa_amr_claims (session-bound claims) — are session-scoped, short-lived, and safe to skip. They regenerate on the next sign-in attempt.

Tables to explicitly NOT migrate

Column-parity drift — the bug that doesn't show up in dry-runs cleanly

Supabase periodically adds columns to auth.users and auth.identities as GoTrue (the underlying auth service) ships new features. If source and target are on different GoTrue versions — which they almost always are when migrating from a long-lived Lovable Cloud project to a freshly-provisioned Supabase project — an INSERT INTO ... SELECT * can break in either direction: the source may have columns the target doesn't recognise, or the target may have NOT NULL columns the source dump never populated.

Two ways to avoid surprises:

• pg_dump --column-inserts emits explicitly-named column lists per row, so Postgres handles missing columns on the target side with defaults instead of blowing up.
• Before the production run, do a \d auth.users and \d auth.identities on both source and target. Diff the column lists. Resolve any required-not-null columns explicitly (most can default to NULL, false, or a sane filler).

A clean dry-run against a freshly-provisioned target project catches this — but only if the dry-run target is on the same GoTrue version as the eventual production target. If you dry-run against a project created last month and provision the prod target the day of the cutover, you can hit drift that didn't show up in the dry-run.

The sequence that actually works

1. Apply schema (your tracked migrations) into a fresh target.
2. Restore auth.users with encrypted_password intact.
3. Restore auth.identities — FK depends on step 2, so order matters.
4. (Optional) Restore auth.mfa_factors.
5. Reconfigure OAuth providers in the target dashboard.
6. Delete any seeded/default public rows the fresh target created (e.g. demo data inserted by your migrations).
7. Restore public data in FK-safe order. Public tables with created_by / owner_id / user_id columns reference auth.users(id) — they'd FK-violate if auth wasn't first.

This is the same ordering Staticbot's pipeline runs internally. The "public FK to auth.users" detection is universal — almost every Supabase app has at least one such column, and it's why "import data first, fix auth later" never works.