Moving Auth users between projects

Auth users are rows in two tables: auth.users (one row per user, including the bcrypt password hash) and auth.identities (one row per sign-in method). Copy both, keep the ids, and users keep their passwords and their links to your own tables. They'll need to sign in once afterwards, because each project has its own JWT secret and sessions aren't copied.

Pick a route

Copy users before any of your own tables that reference auth.users, such as profiles.

Over the SSH tunnel (Business, Enterprise)

Get each project's connection string from Dashboard → your project → Connect → Database → Show connection string, then:

# Terminal 1: open the tunnel
ssh -N -L 5432:127.0.0.1:5432 [email protected]

# Terminal 2: copy the rows from app-one to app-two
pg_dump "postgresql://postgres:<pw-one>@127.0.0.1:5432/p_app_one" \
  --data-only --table=auth.users --table=auth.identities > auth-users.sql

psql "postgresql://postgres:<pw-two>@127.0.0.1:5432/p_app_two" -f auth-users.sql

If the projects are on different instances, open a second tunnel on another local port (for example -L 5433:127.0.0.1:5432).

With SQL on any plan

1. In the source project's Studio SQL editor, run each query and copy its result:

   select jsonb_agg(u) from auth.users u;
   select jsonb_agg(i) from auth.identities i;
   

2. In the target project's SQL editor, stage the two results. Dollar quotes keep names like O'Brien from breaking the SQL:

   create table public.qa_user_import (kind text primary key, payload jsonb);
   insert into public.qa_user_import values
     ('users', $json$ PASTE_USERS_RESULT $json$),
     ('identities', $json$ PASTE_IDENTITIES_RESULT $json$);
   

3. Insert them, skipping generated columns such as confirmed_at, which Postgres won't accept values for:

   do $$
   declare
     t text;
     cols text;
   begin
     foreach t in array array['users', 'identities'] loop
       select string_agg(quote_ident(column_name), ', ' order by ordinal_position) into cols
       from information_schema.columns
       where table_schema = 'auth' and table_name = t and is_generated = 'NEVER';
   
       execute format(
         'insert into auth.%1$I (%2$s) select %2$s from jsonb_populate_recordset(null::auth.%1$I,
            (select payload from public.qa_user_import where kind = %1$L))',
         t, cols);
     end loop;
   end $$;
   
   drop table public.qa_user_import;
   

   If an email already exists in the target, the insert fails as a whole. Remove or rename the duplicate first.

After moving

• Recreate Auth settings in the target: Site URL, Redirect URLs, OAuth providers, email templates and toggles. They aren't stored with the users.
• OAuth users sign in with the same provider. Register the target's callback URL (https://app-two.acme.supahost.space/auth/v1/callback) with each provider.
• Check for NULLs if the rows came from another system rather than another project. See Database error querying schema.
• Move dependent data (profiles, uploads) after the users, keeping the same user ids.

Related

• Users and the auth schema
• Importing from Supabase Cloud
• SSH access and direct Postgres
• Auth error 500: Database error querying schema