Bug causing OAuthAccountNotLinked (identified in a recent commit)

[kevinpiac]

Environment

System:
OS: macOS 13.5
CPU: (12) arm64 Apple M2 Pro
Memory: 86.23 MB / 32.00 GB
Shell: 5.9 - /bin/zsh
Binaries:
Node: 20.5.1 - ~/.nvm/versions/node/v20.5.1/bin/node
npm: 9.8.0 - ~/.nvm/versions/node/v20.5.1/bin/npm
pnpm: 8.7.0 - ~/.nvm/versions/node/v20.5.1/bin/pnpm
Browsers:
Chrome: 121.0.6167.139
Safari: 16.6

Reproduction URL

https://github.com/kevinpiac/next-auth-providerAccountId-issue

Describe the issue

As a user, when I try to sign in for the second time using an OAuth or OIDC provider such as Google for example, I keep getting the OAuthAccountNotLinked error even if I'm using the same Provider and the same account.

It seems this bug was introduced by this recent fix: #9793

This fix always overrides the user.id to crypto.randomUUID()
Then it sets the providerAccountId to user.id instead of using the id returned by the profile callback as specified in the documentation (see below).

This causes the providerAccountId to be randomly generated every time someone tries to login and will always cause OAuthAccountNotLinked error.

The docs say:

 /**
   * This value depends on the type of the provider being used to create the account.
   * - oauth/oidc: The OAuth account's id, returned from the `profile()` callback.
   * - email: The user's email address.
   * - credentials: `id` returned from the `authorize()` callback
   */

---

For more context, see the two incriminated lines below:

// core/src/lib/actions/callback/oauth/callback.ts


async function getUserAndAccount(
  OAuthProfile: Profile,
  provider: OAuthConfigInternal<any>,
  tokens: TokenSet,
  logger: LoggerInstance
) {
  try {
    const userFromProfile = await provider.profile(OAuthProfile, tokens)
    const user = {
      ...userFromProfile,
      id: crypto.randomUUID(), << INCRIMINATED LINE
      email: userFromProfile.email?.toLowerCase(),
    } satisfies User

    return {
      user,
      account: {
        ...tokens,
        provider: provider.id,
        type: provider.type,
        providerAccountId: user.id.toString(), << INCRIMINATED LINE
      },
    }
  } catch (e) {
    // If we didn't get a response either there was a problem with the provider
    // response *or* the user cancelled the action with the provider.
    //
    // Unfortunately, we can't tell which - at least not in a way that works for
    // all providers, so we return an empty object; the user should then be
    // redirected back to the sign up page. We log the error to help developers
    // who might be trying to debug this when configuring a new provider.
    logger.debug("getProfile error details", OAuthProfile)
    logger.error(
      new OAuthProfileParseError(e as Error, { provider: provider.id })
    )
  }
}

How to reproduce

In summary :

1. Use a database adapter such as Prisma.
2. Use any OIDC provider such as Google.
3. Login twice with the same account.

More detail :

• On the first sign-in an Account is persisted using the adapter with a providerAccountId that is not corresponding to the id field returned by the profile callback. It uses a random UUID instead.

• So, the second time the user tries to sign in, it triggers an OAuthAccountNotLinked error because the adapter tries to find the matching Account using a new random UUID, causing the OAuthAccountNotLinked to throw.

Expected behavior

It should follow the documentation, and the providerAccountId should be equal to the result of the user.id returned by the profile callback.

 /**
   * This value depends on the type of the provider being used to create the account.
   * - oauth/oidc: The OAuth account's id, returned from the `profile()` callback.
   * - email: The user's email address.
   * - credentials: `id` returned from the `authorize()` callback
   */

[robinbraemer]

I can confirm my users who used GitHub can no longer sign-in again.
This is a terrible issue and should never happen to an auth library to break this.

[balazsorban44]

@robinbraemer this only happened in the experimental/beta version of the libs, which are not yet production ready as the release tag suggests, and you are using them in production at your own risk.

Every update you do is your own responsibility. We have extensive changelogs where you can check the changes before upgrading and take an evaluation, and reach out if something doesn't look right to you. "same issue" messages are of little value as comments, the rest of your message is also unnecessary. You can 👍 the original post to show that you are having a same/similar issue, unless you can provide more context.

Luckily, the OP did an amazing job here and took the time to dig deep, making it fairly easy to see the problem 💚. Will look into this as soon as my time allows it. I'm sorry if our free beta library caused issues for your users, make sure to sponsor the project if you need quicker support in the future. PRs are of course always welcome!

[AMR-21]

Having the same problem, I am using Google and GitHub providers. When I set allowDangerousEmailAccountLinking: true it works, I do not how, Any reasons?

Update 1:
Just for documenting when adding the emailVerified timestamp, multiple logins with same provider work fine without the need for allowDangerousEmailAccountLinking: true

[kevinpiac]

@balazsorban44 thanks for your answer, I will send a PR asap.

I understand that this issue being on the experimental branch does not make it the top priority.

However I'm using the svelkit authjs library which seem to rely on the authjs core experimental version (let me know if I'm wrong).

Since this sveltekit lib is now used by a bunch of folks I wonder if there is any plan to make it rely on the production ready branch of authjs core instead.

Since the sveltekit package is simple a client around the authjs core, it would be nice to be able to choose the core version we wanna use or at least base it on the production ready version.

Happy to open another issue / PR for this if it makes sense to you.

[balazsorban44]

Both libraries (core and sveltekit) are experimental, it's implied on the docs. There's no "stable" core yet. As you see from this issue, we still have things to figure out.

It is top priority though. My time is just limited.

[robinbraemer]

Attention to Those Affected:

If you're experiencing issues, revert to the last stable release by running: pnpm install @auth/sveltekit@0.8.0.

Steps to Fix Broken User Accounts

"broken user accounts" can no longer re-login after signing out.
Logging in while having an active session will work, and create a second correct account entry in the database (tested it). You might want to delete the duplicate rows with the invalid providerAccountId.

1. Identify Affected Accounts

To locate accounts created during the period when the faulty version was active, execute the following SQL query (assuming you used postgres/prisma):

SELECT t.*
FROM public."Account" t
WHERE "providerAccountId" LIKE '%-%'

This query targets providerAccountIds containing a dash, characteristic of the flawed version that utilized crypto.randomUUID().

Warning: This method is verified for GitHub and Discord OAuth providers, whose account IDs comprise only integers, lacking dashes. If your account IDs from other providers normally include dashes, this approach may not be suitable, and an alternative method to identify affected accounts will be necessary.

2. Update the providerAccountId

Once you've identified the accounts with incorrect providerAccountId, the next step is to retrieve the valid ID from the respective provider, either manually or using my script provided below.

For GitHub accounts: Extract the user ID from the image column in the User table. For example, in https://avatars.githubusercontent.com/u/22003767?v=4, the correct ID is 22003767. Update the providerAccountId with this ID.

For Discord accounts: Apply a similar method. For instance, in the URL https://cdn.discordapp.com/avatars/375720875967774721/7c73a714d389293877e2cc4d17558f58.png, 375720875967774721 is the valid ID.

This technique might also work for other providers using the image URL, or alternatively, you could use the email or name column for further ID fetching steps.

---

After updating the IDs, users should be able to log in again (this has been confirmed to work). It's advisable to wait for a resolution to this issue before upgrading to any version above @auth/sveltekit@0.8.0.

Important: In your package.json, ensure that the version "@auth/sveltekit": "0.8.0" does not have an up arrow ^ preceding it. This will help lock the version and prevent automatic updates.

I hope this solution is helpful to you.

---

I made a small script that will do the job for me because I had 40 users affected.

Quick Usage Guide for the Account Update Script

import { Pool } from 'pg';

// Database connection parameters
const pool = new Pool({
    connectionString: 'XXXXXXXX',
});

// Function to extract provider ID from the image URL
function extractProviderId(imageUrl) {
    let providerType = '';
    let providerId = '';

    if (imageUrl.includes('githubusercontent.com')) {
        providerType = 'GitHub';
        providerId = imageUrl.split('/u/')[1].split('?')[0];
    } else if (imageUrl.includes('discordapp.com')) {
        providerType = 'Discord';
        providerId = imageUrl.split('/avatars/')[1].split('/')[0];
    }

    if (!providerId.match(/^\d+$/)) {
        throw new Error(`Invalid ID extracted for ${providerType}: ${providerId}`);
    }

    return { providerId, providerType };
}

let isListening = false;

function waitForEnter() {
    return new Promise(resolve => {
        if (!isListening) {
            process.stdin.resume();
            process.stdin.setEncoding('utf-8');
            isListening = true;
        }

        const onDataHandler = function(data) {
            if (data.trim() === '') {
                process.stdin.removeListener('data', onDataHandler); // Remove the listener to avoid multiple triggers
                isListening = false; // Reset the listening state
                resolve();
            }
        };

        process.stdin.on('data', onDataHandler);
    });
}



// Main function to update providerAccountId
async function updateProviderAccountId() {
    try {
        const client = await pool.connect();
        const query = `
            SELECT a.id, u.id as userid,
            a."providerAccountId" as provideraccountid,
            u.image, u.name, u.email
            FROM public."Account" a
            JOIN public."User" u ON a."userId" = u.id
            WHERE a."providerAccountId" LIKE '%-%'
        `;

        const result = await client.query(query);

				// Pint number of rows (affected accounts)
				console.log(`Found ${result.rowCount} affected accounts to update, will ask for confirmation before updating providerAccountId for each one`);
        for (let row of result.rows) {
            const { providerId, providerType } = extractProviderId(row.image);
            console.log(`Updating providerAccountId for user (${row.userid} ${row.name} ${row.email}) from ${row.provideraccountid} to ${providerId} (Provider: ${providerType})`);
            console.log("Press Enter to confirm or Ctrl+C to cancel...");

            await waitForEnter();

            // Update query
            const updateQuery = `
                UPDATE public."Account"
                SET "providerAccountId" = $1
                WHERE id = $2
            `;
            try {
                await client.query(updateQuery, [providerId, row.id]);
                console.log(`Updated providerAccountId for user ${row.userid}`);
            } catch (err) {
                if (err.code === '23505') {
                    console.error(`Error in updating providerAccountId for account ${row.id}:`, err);
                    console.error('Safe to ignore this because the user may already linked his account with the new providerAccountId. You might want to delete the duplicate broken account row for that user');
                    continue;
                }
                throw err;
            }
        }

    } catch (err) {
        console.error('Error in updating providerAccountId:', err);
        process.exit(1);
    }
}

// Start the update process
await updateProviderAccountId();

Overview:

This script is designed to update providerAccountId fields in a PostgreSQL database for user accounts that have been affected by a specific issue causing incorrect account IDs. It is particularly useful for systems that use OAuth providers like GitHub and Discord, where the providerAccountId may have been incorrectly generated.

What It Does:

1. Identifies Affected Accounts:
   The script runs a SQL query to find user accounts in the Account table with a providerAccountId that contains a dash (-), indicating they were likely generated by a faulty method.

2. Extracts Correct Provider ID:
   For each affected account, the script uses the user's image URL stored in the User table to determine the correct providerAccountId. It supports URLs from GitHub and Discord, extracting numeric IDs from these URLs.

3. Confirmation Step:
   Before updating each account, the script prompts the user to press Enter to confirm the update. This manual confirmation step ensures that you have control over each update operation.

4. Updates the Database:
   Upon confirmation, the script updates the providerAccountId for each affected account with the correct ID extracted from the image URL.

5. Error Handling:
   The script includes basic error handling, such as validation to ensure extracted IDs are numeric and catching potential errors during database updates.

How to Use:

1. Prerequisites:

   • Ensure Node.js and PostgreSQL are installed in your environment.
   • Install the pg package in your Node.js project using npm install pg.

2. Configuration:

   • Replace XXXXXXXX in the connectionString with your actual PostgreSQL connection string.

3. Running the Script:

   • Save the provided code in a .js file, for example, updateAccounts.js.
   • Run the script with Node.js using the command node updateAccounts.js.

4. Confirmation:

   • When prompted, review the account details and proposed changes.
   • Press Enter to confirm and apply the update for each account, or use Ctrl+C to abort the script.

Important Notes:

• Backup Your Data: Always back up your database before running scripts that modify data.
• Test Environment: Initially run the script in a test environment to ensure it behaves as expected.
• Customization: You may need to adjust the script if your database schema differs from the assumed structure in the code.

By following these steps and precautions, you can effectively use this script to correct affected providerAccountId values in your user accounts database.

[jamtdev]

For those using next-auth, the last working version before the bug release is 5.0.0-beta.5.

[balazsorban44]

@machak while I appreciate feedback (even negative one), your comment has nothing to do with this issue, and helps nobody solving the reported issue. Please keep future discussions to the case in point. As such I'm marking it as off-topic.

This is open-source, people helping you and your company for free. Not a competition. Consider sponsoring the projects you want to see succeed to accelerate their development, if you are not satisfied with the development speed. Also, we welcome PRs!

That said, everyone, please use whatever tools make you happy.

[ndom91]

Should be fixed in @auth/core@0.26.3, we're using the provider provided ID for account.providerAccountId again.

[demenskyi]

Unfortunately is not fixed. Still have issues after clean install.
Had to switch back to these versions:

"@auth/prisma-adapter": "1.0.14",
"@prisma/client": "5.7.1",
"next-auth": "5.0.0-beta.4",
"prisma": "5.7.1",

[balazsorban44]

@demenskyi open a new issue with a reproduction. gonna lock this one, as the OP's issue should be resolved. Please generally don't comment to already closed issues, it's only by chance that I read this again.