Supported way to move a Google identity between two existing Supabase Auth users? #53053
Replies: 4 comments
|
In Supabase Auth (GoTrue), OAuth provider resolution during sign-in looks up the row in Because the client-side 1. Prerequisites Before Re-Pointing the Identity
2. Transactional Procedure with Built-In Rollback Snapshot Run the following in a single transaction so you have an exact snapshot of the pre-migration rows and so BEGIN;
-- 1. Create a rollback backup table (once) and snapshot both users + the Google identity row
CREATE SCHEMA IF NOT EXISTS private;
CREATE TABLE IF NOT EXISTS private.auth_identity_migration_backup AS
SELECT
now() AS backed_up_at,
i.id AS identity_row_id,
i.user_id AS old_user_id,
i.provider,
i.provider_id,
u_orig.id AS orig_user_id,
u_orig.raw_app_meta_data AS orig_app_meta,
u_dest.raw_app_meta_data AS dest_app_meta
FROM auth.identities i
JOIN auth.users u_dest ON u_dest.id = i.user_id
CROSS JOIN auth.users u_orig
WHERE i.provider = 'google'
AND i.user_id = '00000000-0000-0000-0000-DESTINATION_UUID'::uuid
AND u_orig.id = '00000000-0000-0000-0000-ORIGINAL_UUID'::uuid;
-- 2. Re-point the Google identity row to the original user ID
UPDATE auth.identities
SET
user_id = '00000000-0000-0000-0000-ORIGINAL_UUID'::uuid,
updated_at = now()
WHERE provider = 'google'
AND user_id = '00000000-0000-0000-0000-DESTINATION_UUID'::uuid;
-- 3. Ensure raw_app_meta_data.providers on the original user includes 'google'
UPDATE auth.users
SET
raw_app_meta_data = jsonb_set(
COALESCE(raw_app_meta_data, '{}'::jsonb),
'{providers}',
(
SELECT jsonb_agg(DISTINCT elem)
FROM jsonb_array_elements_text(
COALESCE(raw_app_meta_data->'providers', '[]'::jsonb) || '["google"]'::jsonb
) AS t(elem)
)
),
updated_at = now()
WHERE id = '00000000-0000-0000-0000-ORIGINAL_UUID'::uuid;
-- 4. Revoke active sessions & refresh tokens on the destination user so stale tokens cannot refresh
DELETE FROM auth.refresh_tokens
WHERE user_id = '00000000-0000-0000-0000-DESTINATION_UUID';
DELETE FROM auth.sessions
WHERE user_id = '00000000-0000-0000-0000-DESTINATION_UUID'::uuid;
COMMIT;3. Handling Existing Sessions & Access Tokens
4. Instant Reversal if Verification Fails BEGIN;
UPDATE auth.identities i
SET user_id = b.old_user_id, updated_at = now()
FROM private.auth_identity_migration_backup b
WHERE i.id = b.identity_row_id;
UPDATE auth.users u
SET raw_app_meta_data = b.orig_app_meta, updated_at = now()
FROM private.auth_identity_migration_backup b
WHERE u.id = b.orig_user_id;
COMMIT; |
|
There's no admin call that moves an identity between users, so the supported way is the user-level link/unlink flow, in two steps. First, the destination account needs a second way to sign in, because unlinkIdentity won't remove someone's only identity. While signed in as that user, add email/password with updateUser({ password: '...' }), then unlink Google: const { data } = await supabase.auth.getUserIdentities()
const google = data.identities.find((i) => i.provider === 'google')
await supabase.auth.unlinkIdentity(google)Then sign in as the original user and link the same Google account, with manual linking turned on in your Auth settings: await supabase.auth.linkIdentity({ provider: 'google' })That runs the Google OAuth flow and attaches the identity to the original user, so the next Google sign-in lands on the original user id. Check in the SQL editor first that the original user doesn't already have a google row in auth.identities, or the link will error out. One thing I'd avoid is updating auth.identities directly with SQL, it skips the bookkeeping Auth does when it links and unlinks an identity. Sessions don't move with the identity, they're per user, so both accounts keep whatever sessions they already have. If you want the destination user signed out everywhere, revoke their refresh tokens server-side with signOut using their JWT. Already-issued access tokens still work until they expire, there's no way around that. Reversing it is the same two steps in the other direction, though the original user will need a second identity of its own before it can unlink Google again. |
|
For a Lovable → hosted Supabase move where the same Google identity already points at two Auth users, I would treat merge as last resort. First: private window + Google sign-in on the destination project only, and confirm which auth.users id you actually land on. If both users must survive, keep them separate and map app data with your own legacy_user_id column rather than forcing GoTrue to move the identity. Before any Admin API linking: export both users, revoke refresh tokens on a test account, and have a rollback note for Site URL / redirect URLs. I work on AppFirstAid — happy to sanity-check the post-migration Google path from the outside if useful: https://appfirstaid.com/?ref=gh-53053 |
|
Adding to @jaharoni answer - one thing worth checking before you fix this user by user: If the same Google identity ended up with two different user IDs, it usually means auth.users wasn't migrated with the original IDs (or users signed in on the new project before the import finished). In that case it's rarely just one account. Quick check on the destination: select count(*) from auth.identities i
join auth.users u on u.id = i.user_id
where i.provider = 'google'
and u.created_at > '<your migration date>';If that's more than a handful, I'd fix it at the migration level instead of patching identities one by one: import auth.users and auth.identities from the source with their original IDs, then re-point or merge any rows in your public tables that were created under the new IDs. Otherwise anything keyed on auth.uid() - RLS policies, foreign keys, storage paths like Two questions that decide the approach:
Happy to look over the migration plan or the SQL before you run it on prod - we do Lovable to own Supabase migrations. Either way, take a snapshot first and test on a branch/copy. |
Uh oh!
There was an error while loading. Please reload this page.
We’re migrating an app from Lovable to hosted Supabase. The same Google identity is linked to the original user ID in the source and a different user ID in the destination. We want Google sign-in on the destination to resolve to the original user ID while preserving both accounts and the destination credentials.
We reviewed the Auth migration and identity linking docs. Is there a supported procedure for this case? If so, what are the prerequisites, how should we handle existing sessions and access tokens, and how can we reverse the change if sign-in verification fails?
All reactions