Account linking and the email trap

Key points

• The temptation is to look users up by email, so that signing in with Google finds the existing password account. That is convenient and it is how account takeover happens.
• If you link on an unverified email, an attacker registers a provider account with the victim address and signs straight into their account.
• Always check email_verified, and understand it only means the provider verified it. It does not mean the person is the same one who owns your account.
• The safe model is to link on the provider sub, storing one row per (provider, sub) pointing at your account. Email is a display attribute, not an identity.
• When you do want to link an existing account, require the user to prove control of it first - sign in with the password, then link, from inside the session.
• Never let a user unlink their last sign-in method, or they lock themselves out permanently.

Example

-- One account, several sign-in identities. Link on (provider, subject).
CREATE TABLE account (
    id            BIGINT PRIMARY KEY AUTO_INCREMENT,
    email         VARCHAR(255) NOT NULL,      -- display, NOT identity
    display_name  VARCHAR(255),
    created_at    TIMESTAMP NOT NULL
);

CREATE TABLE account_identity (
    id           BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id   BIGINT       NOT NULL,
    provider     VARCHAR(32)  NOT NULL,       -- google, github, password
    subject      VARCHAR(255) NOT NULL,       -- the STABLE provider id
    linked_at    TIMESTAMP    NOT NULL,
    CONSTRAINT uq_provider_subject UNIQUE (provider, subject),
    FOREIGN KEY (account_id) REFERENCES account(id)
);

---
/*
 * THE TAKEOVER, step by step:
 *
 *   1. victim has a password account at victim@example.com
 *   2. attacker creates a provider account claiming victim@example.com
 *   3. attacker signs in with that provider
 *   4. your code does findByEmail(...) and finds the victim account
 *   5. attacker is now signed in as the victim
 *
 * Step 4 is the bug. Never resolve identity by email.
 */

// CORRECT: look up by (provider, subject); email is only a hint.
Account resolve(OidcUser google) {
    return identities.findByProviderAndSubject("google", google.getSubject())
            .map(AccountIdentity::account)
            .orElseGet(() -> createNewAccount(google));
}

// Linking to an EXISTING account requires proving control of it first:
//   1. user signs in normally (password, or an already-linked provider)
//   2. from INSIDE that session, they start the Google flow
//   3. on callback, attach (google, sub) to the session account
//
// Never link based on a matching email during a fresh sign-in.

// And never remove the last one:
void unlink(Account account, String provider) {
    if (identities.countByAccount(account) <= 1) {
        throw new IllegalStateException("Cannot remove the only sign-in method");
    }
    identities.deleteByAccountAndProvider(account, provider);
}

Identity is (provider, sub) - resolving an account by email is how attackers take over accounts they never owned.