Migrate Supabase auth users without forcing a password reset

When you move a Supabase (or Lovable Cloud) project to a new Supabase project, the hard part is not the tables — it is the users. Do it wrong and every user resets their password on first login. Do it right and nobody notices the backend changed.

The reason this is tricky: Supabase stores passwords as bcrypt hashes in auth.users.encrypted_password, and the admin API that creates users does not let you supply a hash.

Why createUser() does not work

The documented way to create a user is:

const { data, error } = await supabase.auth.admin.createUser({
  email: 'user@example.com',
  password: 'plaintext-here'
})

It takes a plaintext password, hashes it server-side, and generates a new id. Neither is acceptable for a migration:

• You do not have the plaintext password. You have the bcrypt hash.
• A new id breaks every foreign key in public that references auth.users(id).

So the admin API is the wrong tool. You insert into auth.users directly over a Postgres connection.

What columns actually matter

auth.users has many columns, but a working login needs a specific subset populated correctly:

• id — copy verbatim. This is what your public foreign keys and RLS auth.uid() checks depend on. See foreign key.
• email
• encrypted_password — the bcrypt hash, copied verbatim. This is what makes the old password keep working.
• email_confirmed_at — if null, the user may be treated as unconfirmed and blocked from signing in.
• aud and role — normally 'authenticated'.
• instance_id — usually the all-zero UUID.
• created_at, updated_at.
• raw_app_meta_data, raw_user_meta_data — provider info and profile fields.

The insert

Read the source users, then insert them on the destination:

insert into auth.users (
  instance_id, id, aud, role, email,
  encrypted_password, email_confirmed_at,
  raw_app_meta_data, raw_user_meta_data,
  created_at, updated_at
) values (
  '00000000-0000-0000-0000-000000000000',
  '3f1d0e2a-...-c9',                 -- original id, preserved
  'authenticated', 'authenticated',
  'user@example.com',
  '$2a$10$3Q....',                   -- original bcrypt hash
  now(),
  '{"provider":"email","providers":["email"]}',
  '{}',
  '2025-11-02 09:14:00+00',
  now()
)
on conflict (id) do nothing;

on conflict (id) do nothing keeps the step idempotent — safe to retry after a partial failure.

Identities

For email/password users, Supabase also expects a row in auth.identities. Some Supabase versions enforce this for the sign-in flow. Copy identities alongside users, preserving user_id:

insert into auth.identities (
  id, user_id, identity_data, provider, provider_id,
  last_sign_in_at, created_at, updated_at
)
select ...
on conflict do nothing;

If a user exists in the destination auth.users but sign-in through the API returns nothing, a missing identity row is the usual cause.

Common failures

• permission denied for schema — you connected with a role that cannot write to auth. Use the connection that carries service privileges, not the anon path. See service role key.
• conflict on id — the user was already inserted on a previous run; the on conflict clause handles this.
• password authentication failed — this is the Postgres connection itself failing, not a user login. Check the connection string before blaming the migration.

The auth schema is documented under the Supabase Auth guides; the migrate auth doc walks the full step.

Verifying

After the insert, confirm counts match and spot-check a hash:

select count(*) from auth.users;

select id, email, left(encrypted_password, 7) as hash_prefix
from auth.users
order by created_at
limit 5;

A bcrypt hash prefix is $2a$, $2b$, or $2y$. If you see plaintext or an empty column, the copy did not carry the hash and those users are locked out.

FAQ

Will users have to reset their passwords after migration?

No, as long as encrypted_password is copied verbatim into the destination auth.users. Bcrypt hashes are portable — the same hash validates the same password on any Postgres. Users log in with their existing credentials.

Why preserve the user id instead of letting Supabase generate a new one?

Every foreign key in your public schema that points at auth.users(id), plus every RLS policy that calls auth.uid(), depends on the id staying the same. A new id orphans all of that data. Copy the id verbatim.

Do OAuth (Google, GitHub) users migrate the same way?

Their auth.users row moves the same way, but they have no password hash — they authenticate through the provider. You must reconfigure the OAuth provider (client id and secret) on the destination project, or their sign-in fails even though the user row exists.