Repository: supabase/gotrue
Pull request #829 (2022-11-21 17:53:09)

feat: use account linking algorithm
Uses the account linking algorithm described in #792 for linking accounts via external identities. This change produces equivalent results as the previous algorithm, while also supporting the SSO case.

(Depends on #826.)


Patch:
diff --git a/api/admin.go b/api/admin.go
index dd860121..9d2e7ee4 100644
--- a/api/admin.go
+++ b/api/admin.go
@@ -275,9 +275,9 @@ func (a *API) adminUserCreate(w http.ResponseWriter, r *http.Request) error {
 		if err != nil {
 			return err
 		}
-		if exists, err := models.IsDuplicatedEmail(db, params.Email, aud); err != nil {
+		if user, err := models.IsDuplicatedEmail(db, params.Email, aud); err != nil {
 			return internalServerError("Database error checking email").WithInternalError(err)
-		} else if exists {
+		} else if user != nil {
 			return unprocessableEntityError("Email address already registered by another user")
 		}
 	}
diff --git a/api/external.go b/api/external.go
index 09ae5984..c9ea0455 100644
--- a/api/external.go
+++ b/api/external.go
@@ -13,7 +13,6 @@ import (
 	"github.com/gofrs/uuid"
 	jwt "github.com/golang-jwt/jwt"
 	"github.com/netlify/gotrue/api/provider"
-	"github.com/netlify/gotrue/conf"
 	"github.com/netlify/gotrue/models"
 	"github.com/netlify/gotrue/observability"
 	"github.com/netlify/gotrue/storage"
@@ -158,7 +157,7 @@ func (a *API) internalExternalProviderCallback(w http.ResponseWriter, r *http.Re
 				return terr
 			}
 		} else {
-			if user, terr = a.createAccountFromExternalIdentity(tx, r, userData, providerType, user); terr != nil {
+			if user, terr = a.createAccountFromExternalIdentity(tx, r, userData, providerType); terr != nil {
 				if errors.Is(terr, errReturnNil) {
 					return nil
 				}
@@ -200,75 +199,89 @@ func (a *API) internalExternalProviderCallback(w http.ResponseWriter, r *http.Re
 	return nil
 }
 
-func (a *API) createAccountFromExternalIdentity(tx *storage.Connection, r *http.Request, userData *provider.UserProvidedData, providerType string, user *models.User) (*models.User, error) {
+func (a *API) createAccountFromExternalIdentity(tx *storage.Connection, r *http.Request, userData *provider.UserProvidedData, providerType string) (*models.User, error) {
 	ctx := r.Context()
 	aud := a.requestAud(ctx, r)
 	config := a.config
 
 	var terr error
 
+	var user *models.User
+	var identity *models.Identity
+
 	var emailData provider.Email
 	var identityData map[string]interface{}
 	if userData.Metadata != nil {
 		identityData = structs.Map(userData.Metadata)
 	}
 
-	var identity *models.Identity
-	// check if identity exists
-	if identity, terr = models.FindIdentityByIdAndProvider(tx, userData.Metadata.Subject, providerType); terr != nil {
-		if models.IsNotFoundError(terr) {
-			user, emailData, terr = a.getUserByVerifiedEmail(tx, config, userData.Emails, aud)
-			if terr != nil && !models.IsNotFoundError(terr) {
-				return nil, internalServerError("Error checking for existing users").WithInternalError(terr)
-			}
-			if user != nil {
-				if identity, terr = a.createNewIdentity(tx, user, providerType, identityData); terr != nil {
-					return nil, terr
-				}
-				if terr = user.UpdateAppMetaDataProviders(tx); terr != nil {
-					return nil, terr
-				}
-			} else {
-				if config.DisableSignup {
-					return nil, forbiddenError("Signups not allowed for this instance")
-				}
+	var emails []string
 
-				// prefer primary email for new signups
-				emailData = userData.Emails[0]
-				for _, e := range userData.Emails {
-					if e.Primary {
-						emailData = e
-						break
-					}
-				}
+	for _, email := range userData.Emails {
+		if email.Verified || config.Mailer.Autoconfirm {
+			emails = append(emails, email.Email)
+		}
+	}
 
-				params := &SignupParams{
-					Provider: providerType,
-					Email:    emailData.Email,
-					Aud:      aud,
-					Data:     identityData,
-				}
+	decision, terr := models.DetermineAccountLinking(tx, providerType, userData.Metadata.Subject, emails)
+	if terr != nil {
+		return nil, terr
+	}
 
-				user, terr = a.signupNewUser(ctx, tx, params)
-				if terr != nil {
-					return nil, terr
-				}
+	switch decision.Decision {
+	case models.LinkAccount:
+		user = decision.User
 
-				if identity, terr = a.createNewIdentity(tx, user, providerType, identityData); terr != nil {
-					return nil, terr
-				}
+		emailData = userData.Emails[0]
+		for _, e := range userData.Emails {
+			if e.Primary || e.Verified {
+				emailData = e
+				break
 			}
-		} else {
+		}
+
+		if _, terr = a.createNewIdentity(tx, user, providerType, identityData); terr != nil {
 			return nil, terr
 		}
-	}
 
-	if identity != nil && user == nil {
-		// get user associated with identity
-		user, terr = models.FindUserByID(tx, identity.UserID)
+		if terr = user.UpdateAppMetaDataProviders(tx); terr != nil {
+			return nil, terr
+		}
+
+	case models.CreateAccount:
+		if config.DisableSignup {
+			return nil, forbiddenError("Signups not allowed for this instance")
+		}
+
+		// prefer primary email for new signups
+		emailData = userData.Emails[0]
+		for _, e := range userData.Emails {
+			if e.Primary {
+				emailData = e
+				break
+			}
+		}
+
+		params := &SignupParams{
+			Provider: providerType,
+			Email:    emailData.Email,
+			Aud:      aud,
+			Data:     identityData,
+		}
+
+		user, terr = a.signupNewUser(ctx, tx, params)
 		if terr != nil {
 			return nil, terr
 		}
+
+		if _, terr = a.createNewIdentity(tx, user, providerType, identityData); terr != nil {
+			return nil, terr
+		}
+
+	case models.AccountExists:
+		user = decision.User
+		identity = decision.Identities[0]
+
 		identity.IdentityData = identityData
 		if terr = tx.UpdateOnly(identity, "identity_data", "last_sign_in_at"); terr != nil {
 			return nil, terr
@@ -284,6 +297,12 @@ func (a *API) createAccountFromExternalIdentity(tx *storage.Connection, r *http.
 		if terr = user.UpdateAppMetaDataProviders(tx); terr != nil {
 			return nil, terr
 		}
+
+	case models.MultipleAccounts:
+		return nil, internalServerError(fmt.Sprintf("Multiple accounts with the same email address in the same linking domain detected: %v", decision.LinkingDomain))
+
+	default:
+		return nil, internalServerError(fmt.Sprintf("Unknown automatic linking decision: %v", decision.Decision))
 	}
 
 	if user.IsBanned() {
@@ -541,24 +560,3 @@ func (a *API) createNewIdentity(tx *storage.Connection, user *models.User, provi
 
 	return identity, nil
 }
-
-// getUserByVerifiedEmail checks if one of the verified emails already belongs to a user
-func (a *API) getUserByVerifiedEmail(tx *storage.Connection, config *conf.GlobalConfiguration, emails []provider.Email, aud string) (*models.User, provider.Email, error) {
-	var user *models.User
-	var emailData provider.Email
-	var err error
-
-	for _, e := range emails {
-		if e.Verified || config.Mailer.Autoconfirm {
-			user, err = models.FindUserByEmailAndAudience(tx, e.Email, aud)
-			if err != nil && !models.IsNotFoundError(err) {
-				return user, emailData, err
-			}
-			if user != nil {
-				emailData = e
-				break
-			}
-		}
-	}
-	return user, emailData, err
-}
diff --git a/api/mail.go b/api/mail.go
index 4cf24f8a..33dd5dba 100644
--- a/api/mail.go
+++ b/api/mail.go
@@ -182,9 +182,9 @@ func (a *API) GenerateLink(w http.ResponseWriter, r *http.Request) error {
 			if terr != nil {
 				return unprocessableEntityError("The new email address provided is invalid")
 			}
-			if exists, terr := models.IsDuplicatedEmail(tx, params.NewEmail, user.Aud); terr != nil {
+			if duplicateUser, terr := models.IsDuplicatedEmail(tx, params.NewEmail, user.Aud); terr != nil {
 				return internalServerError("Database error checking email").WithInternalError(terr)
-			} else if exists {
+			} else if duplicateUser != nil {
 				return unprocessableEntityError(DuplicateEmailMsg)
 			}
 			now := time.Now()
diff --git a/api/samlacs.go b/api/samlacs.go
index c6f7827c..06419a60 100644
--- a/api/samlacs.go
+++ b/api/samlacs.go
@@ -242,7 +242,7 @@ func (a *API) SAMLACS(w http.ResponseWriter, r *http.Request) error {
 		var terr error
 		var user *models.User
 
-		if user, terr = a.createAccountFromExternalIdentity(tx, r, &userProvidedData, "sso:"+ssoProvider.ID.String(), user); terr != nil {
+		if user, terr = a.createAccountFromExternalIdentity(tx, r, &userProvidedData, "sso:"+ssoProvider.ID.String()); terr != nil {
 			return terr
 		}
 
diff --git a/api/signup.go b/api/signup.go
index 00cc65a1..9715e4ee 100644
--- a/api/signup.go
+++ b/api/signup.go
@@ -79,7 +79,7 @@ func (a *API) Signup(w http.ResponseWriter, r *http.Request) error {
 		if err != nil {
 			return err
 		}
-		user, err = models.FindUserByEmailAndAudience(db, params.Email, params.Aud)
+		user, err = models.IsDuplicatedEmail(db, params.Email, params.Aud)
 	case "phone":
 		if !config.External.Phone.Enabled {
 			return badRequestError("Phone signups are disabled")
diff --git a/api/user.go b/api/user.go
index b56c819b..03a555d4 100644
--- a/api/user.go
+++ b/api/user.go
@@ -123,10 +123,10 @@ func (a *API) UserUpdate(w http.ResponseWriter, r *http.Request) error {
 				return terr
 			}
 
-			var exists bool
-			if exists, terr = models.IsDuplicatedEmail(tx, params.Email, user.Aud); terr != nil {
+			var duplicateUser *models.User
+			if duplicateUser, terr = models.IsDuplicatedEmail(tx, params.Email, user.Aud); terr != nil {
 				return internalServerError("Database error checking email").WithInternalError(terr)
-			} else if exists {
+			} else if duplicateUser != nil {
 				return unprocessableEntityError(DuplicateEmailMsg)
 			}
 
diff --git a/cmd/admin_cmd.go b/cmd/admin_cmd.go
index 70719837..599fc842 100644
--- a/cmd/admin_cmd.go
+++ b/cmd/admin_cmd.go
@@ -73,7 +73,7 @@ func adminCreateUser(config *conf.GlobalConfiguration, args []string) {
 	defer db.Close()
 
 	aud := getAudience(config)
-	if exists, err := models.IsDuplicatedEmail(db, args[0], aud); exists {
+	if user, err := models.IsDuplicatedEmail(db, args[0], aud); user != nil {
 		logrus.Fatalf("Error creating new user: user already exists")
 	} else if err != nil {
 		logrus.Fatalf("Error checking user email: %+v", err)
diff --git a/migrations/20221003041349_add_mfa_schema.up.sql b/migrations/20221003041349_add_mfa_schema.up.sql
index 49851f52..88c6421c 100644
--- a/migrations/20221003041349_add_mfa_schema.up.sql
+++ b/migrations/20221003041349_add_mfa_schema.up.sql
@@ -22,7 +22,7 @@ create table if not exists {{ index .Options "Namespace" }}.mfa_factors(
 );
 comment on table {{ index .Options "Namespace" }}.mfa_factors is 'auth: stores metadata about factors';
 
-create unique index mfa_factors_user_friendly_name_unique on {{ index .Options "Namespace" }}.mfa_factors (friendly_name, user_id) where trim(friendly_name) <> '';
+create unique index if not exists mfa_factors_user_friendly_name_unique on {{ index .Options "Namespace" }}.mfa_factors (friendly_name, user_id) where trim(friendly_name) <> '';
 
 -- auth.mfa_challenges definition
 create table if not exists {{ index .Options "Namespace" }}.mfa_challenges(
diff --git a/migrations/20221011041400_add_mfa_indexes.up.sql b/migrations/20221011041400_add_mfa_indexes.up.sql
index cecdcba5..25d3a863 100644
--- a/migrations/20221011041400_add_mfa_indexes.up.sql
+++ b/migrations/20221011041400_add_mfa_indexes.up.sql
@@ -1,8 +1,17 @@
-create index if not exists refresh_token_session_id on {{ index .Options "Namespace" }}.refresh_tokens using btree(session_id);
-
 alter table {{ index .Options "Namespace" }}.mfa_amr_claims
-  add column if not exists id uuid not null,
-  add constraint amr_id_pk primary key(id);
+  add column if not exists id uuid not null;
+
+do $$
+begin
+  if not exists
+     (select constraint_name
+      from information_schema.check_constraints
+      where constraint_schema = '{{ index .Options "Namespace" }}'
+      and constraint_name = 'amr_id_pk')
+  then
+    alter table {{ index .Options "Namespace" }}.mfa_amr_claims add constraint amr_id_pk primary key(id);
+  end if;
+end $$;
 
 create index if not exists user_id_created_at_idx on {{ index .Options "Namespace" }}.sessions (user_id, created_at);
 create index if not exists factor_id_created_at_idx on {{ index .Options "Namespace" }}.mfa_factors (user_id, created_at);
diff --git a/migrations/20221120114718_add_identities_email_column.up.sql b/migrations/20221120114718_add_identities_email_column.up.sql
new file mode 100644
index 00000000..f47e31cf
--- /dev/null
+++ b/migrations/20221120114718_add_identities_email_column.up.sql
@@ -0,0 +1,15 @@
+update
+  {{ index .Options "Namespace" }}.identities as identities
+set
+  identity_data = identity_data || jsonb_build_object('email', (select email from {{ index .Options "Namespace" }}.users where id = identities.user_id)),
+  updated_at = now()
+where identities.provider = 'email' and identity_data->>'email' is null;
+
+alter table only {{ index .Options "Namespace" }}.identities
+  add column if not exists email text generated always as (lower(identity_data->>'email')) stored;
+
+comment on column {{ index .Options "Namespace" }}.identities.email is 'Auth: Email is a generated column that references the optional email property in the identity_data';
+
+create index if not exists identities_email_idx on {{ index .Options "Namespace" }}.identities (email text_pattern_ops);
+
+comment on index {{ index .Options "Namespace" }}.identities_email_idx is 'Auth: Ensures indexed queries on the email column';
diff --git a/migrations/20221124140122_add_identities_email_column.up.sql b/migrations/20221124140122_add_identities_email_column.up.sql
new file mode 100644
index 00000000..a935e9c2
--- /dev/null
+++ b/migrations/20221124140122_add_identities_email_column.up.sql
@@ -0,0 +1,15 @@
+update
+  {{ index .Options "Namespace" }}.identities as identities
+set
+  identity_data = identity_data || jsonb_build_object('email', (select email from {{ index .Options "Namespace" }}.users where id = identities.user_id)),
+  updated_at = '2022-11-24'
+where identities.provider = 'email' and identity_data->>'email' is null;
+
+alter table only {{ index .Options "Namespace" }}.identities
+  add column if not exists email text generated always as (lower(identity_data->>'email')) stored;
+
+comment on column {{ index .Options "Namespace" }}.identities.email is 'Auth: Email is a generated column that references the optional email property in the identity_data';
+
+create index if not exists identities_email_idx on {{ index .Options "Namespace" }}.identities (email text_pattern_ops);
+
+comment on index {{ index .Options "Namespace" }}.identities_email_idx is 'Auth: Ensures indexed queries on the email column';
diff --git a/migrations/20221125140132_backfill_email_identity.up.sql b/migrations/20221125140132_backfill_email_identity.up.sql
new file mode 100644
index 00000000..313d9fb3
--- /dev/null
+++ b/migrations/20221125140132_backfill_email_identity.up.sql
@@ -0,0 +1,7 @@
+-- backfill the auth.identities column by adding an email identity 
+-- for all auth.users with an email and password 
+
+insert into auth.identities (id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
+select id, id as user_id, jsonb_build_object('sub', id, 'email', email) as identity_data, 'email' as provider, null as last_sign_in_at, '2022-11-25' as created_at, '2022-11-25' as updated_at
+from auth.users as users
+where encrypted_password != '' and email is not null and not exists(select user_id from auth.identities where user_id = users.id);
diff --git a/migrations/20221125141029_add_identities_email_column.up.sql b/migrations/20221125141029_add_identities_email_column.up.sql
new file mode 100644
index 00000000..b66a9a2f
--- /dev/null
+++ b/migrations/20221125141029_add_identities_email_column.up.sql
@@ -0,0 +1,15 @@
+update
+  {{ index .Options "Namespace" }}.identities as identities
+set
+  identity_data = identity_data || jsonb_build_object('email', (select email from {{ index .Options "Namespace" }}.users where id = identities.user_id)),
+  updated_at = '2022-11-25'
+where identities.provider = 'email' and identity_data->>'email' is null;
+
+alter table only {{ index .Options "Namespace" }}.identities
+  add column if not exists email text generated always as (lower(identity_data->>'email')) stored;
+
+comment on column {{ index .Options "Namespace" }}.identities.email is 'Auth: Email is a generated column that references the optional email property in the identity_data';
+
+create index if not exists identities_email_idx on {{ index .Options "Namespace" }}.identities (email text_pattern_ops);
+
+comment on index {{ index .Options "Namespace" }}.identities_email_idx is 'Auth: Ensures indexed queries on the email column';
diff --git a/models/identity.go b/models/identity.go
index fc391f08..fb5f3a3d 100644
--- a/models/identity.go
+++ b/models/identity.go
@@ -56,6 +56,10 @@ func (i *Identity) BeforeUpdate(tx *pop.Connection) error {
 	return nil
 }
 
+func (i *Identity) IsForSSOProvider() bool {
+	return strings.HasPrefix(i.Provider, "sso:")
+}
+
 // FindIdentityById searches for an identity with the matching provider_id and provider given.
 func FindIdentityByIdAndProvider(tx *storage.Connection, providerId, provider string) (*Identity, error) {
 	identity := &Identity{}
diff --git a/models/linking.go b/models/linking.go
new file mode 100644
index 00000000..07b48cdd
--- /dev/null
+++ b/models/linking.go
@@ -0,0 +1,155 @@
+package models
+
+import (
+	"fmt"
+	"strings"
+
+	"github.com/netlify/gotrue/storage"
+)
+
+// GetAccountLinkingDomain returns a string that describes the account linking
+// domain. An account linking domain describes a set of Identity entities that
+// _should_ generally fall under the same User entity. It's just a runtime
+// string, and is not typically persisted in the database. This value can vary
+// across time.
+func GetAccountLinkingDomain(provider string) string {
+	if strings.HasPrefix(provider, "sso:") {
+		// when the provider ID is a SSO provider, then the linking
+		// domain is the provider itself i.e. there can only be one
+		// user + identity per identity provider
+		return provider
+	}
+
+	// otherwise, the linking domain is the default linking domain that
+	// links all accounts
+	return "default"
+}
+
+type AccountLinkingDecision = int
+
+const (
+	AccountExists AccountLinkingDecision = iota
+	CreateAccount
+	LinkAccount
+	MultipleAccounts
+)
+
+type AccountLinkingResult struct {
+	Decision AccountLinkingDecision
+
+	User       *User
+	Identities []*Identity
+
+	LinkingDomain string
+}
+
+// DetermineAccountLinking uses the provided data and database state to compute a decision on whether:
+// - A new User should be created (CreateAccount)
+// - A new Identity should be created (LinkAccount) with a UserID pointing to an existing user account
+// - Nothing should be done (AccountExists)
+// - It's not possible to decide due to data inconsistency (MultipleAccounts) and the caller should decide
+//
+// Errors signal failure in processing only, like database access errors.
+func DetermineAccountLinking(tx *storage.Connection, provider, sub string, emails []string) (AccountLinkingResult, error) {
+	if identity, terr := FindIdentityByIdAndProvider(tx, sub, provider); terr == nil {
+		// account exists
+
+		var user *User
+		if user, terr = FindUserByID(tx, identity.UserID); terr != nil {
+			return AccountLinkingResult{}, terr
+		}
+
+		return AccountLinkingResult{
+			Decision:      AccountExists,
+			User:          user,
+			Identities:    []*Identity{identity},
+			LinkingDomain: GetAccountLinkingDomain(provider),
+		}, nil
+	} else if !IsNotFoundError(terr) {
+		return AccountLinkingResult{}, terr
+	}
+
+	// account does not exist, identity and user not immediately
+	// identifiable, look for similar identities based on email
+	var similarIdentities []*Identity
+
+	if len(emails) > 0 {
+		if terr := tx.Q().Eager().Where("email in (?)", emails).All(&similarIdentities); terr != nil {
+			return AccountLinkingResult{}, terr
+		}
+	}
+
+	fmt.Printf("similar identities %v\n", similarIdentities)
+
+	// TODO: determine linking behavior over phone too
+
+	if len(similarIdentities) == 0 {
+		// there are no similar identities, clearly we have to create a new account
+
+		return AccountLinkingResult{
+			Decision:      CreateAccount,
+			LinkingDomain: GetAccountLinkingDomain(provider),
+		}, nil
+	}
+
+	// there are some similar identities, we now need to proceed in
+	// identifying whether this supposed new identity should be assigned to
+	// an existing user or to create a new user, according to the automatic
+	// linking rules
+
+	// this is the linking domain for the new identity
+	newAccountLinkingDomain := GetAccountLinkingDomain(provider)
+
+	var linkingIdentities []*Identity
+
+	// now let's see if there are any existing and similar identities in
+	// the same linking domain
+	for _, identity := range similarIdentities {
+		if GetAccountLinkingDomain(identity.Provider) == newAccountLinkingDomain {
+			linkingIdentities = append(linkingIdentities, identity)
+		}
+	}
+
+	if len(linkingIdentities) == 0 {
+		// there are no identities in the linking domain, we have to
+		// create a new identity and new user
+		return AccountLinkingResult{
+			Decision:      CreateAccount,
+			LinkingDomain: newAccountLinkingDomain,
+		}, nil
+	}
+
+	// there is at least one identity in the linking domain let's do a
+	// sanity check to see if all of the identities in the domain share the
+	// same user ID
+
+	for _, identity := range linkingIdentities {
+		if identity.UserID != linkingIdentities[0].UserID {
+			// ok this linking domain has more than one user account
+			// caller should decide what to do
+
+			return AccountLinkingResult{
+				Decision:      MultipleAccounts,
+				Identities:    linkingIdentities,
+				LinkingDomain: newAccountLinkingDomain,
+			}, nil
+		}
+	}
+
+	// there's only one user ID in this linking domain, we can go on and
+	// create a new identity and link it to the existing account
+
+	var user *User
+	var terr error
+
+	if user, terr = FindUserByID(tx, linkingIdentities[0].UserID); terr != nil {
+		return AccountLinkingResult{}, terr
+	}
+
+	return AccountLinkingResult{
+		Decision:      LinkAccount,
+		User:          user,
+		Identities:    linkingIdentities,
+		LinkingDomain: newAccountLinkingDomain,
+	}, nil
+}
diff --git a/models/user.go b/models/user.go
index d7856347..6d836850 100644
--- a/models/user.go
+++ b/models/user.go
@@ -270,29 +270,85 @@ func (u *User) UpdateLastSignInAt(tx *storage.Connection) error {
 
 // ConfirmEmailChange confirm the change of email for a user
 func (u *User) ConfirmEmailChange(tx *storage.Connection, status int) error {
-	u.Email = storage.NullString(u.EmailChange)
+	email := u.EmailChange
+
+	u.Email = storage.NullString(email)
 	u.EmailChange = ""
 	u.EmailChangeTokenCurrent = ""
 	u.EmailChangeTokenNew = ""
 	u.EmailChangeConfirmStatus = status
-	return tx.UpdateOnly(
+
+	if err := tx.UpdateOnly(
 		u,
 		"email",
 		"email_change",
 		"email_change_token_current",
 		"email_change_token_new",
 		"email_change_confirm_status",
-	)
+	); err != nil {
+		return err
+	}
+
+	identity, err := FindIdentityByIdAndProvider(tx, u.ID.String(), "email")
+	if err != nil {
+		if IsNotFoundError(err) {
+			// no email identity, not an error
+			return nil
+		}
+
+		return err
+	}
+
+	if _, ok := identity.IdentityData["email"]; ok {
+		identity.IdentityData["email"] = email
+	}
+
+	if err := tx.UpdateOnly(identity, "identity_data"); err != nil {
+		return err
+	}
+
+	return nil
 }
 
 // ConfirmPhoneChange confirms the change of phone for a user
 func (u *User) ConfirmPhoneChange(tx *storage.Connection) error {
-	u.Phone = storage.NullString(u.PhoneChange)
+	now := time.Now()
+	phone := u.PhoneChange
+
+	u.Phone = storage.NullString(phone)
 	u.PhoneChange = ""
 	u.PhoneChangeToken = ""
-	now := time.Now()
 	u.PhoneConfirmedAt = &now
-	return tx.UpdateOnly(u, "phone", "phone_change", "phone_change_token", "phone_confirmed_at")
+
+	if err := tx.UpdateOnly(
+		u,
+		"phone",
+		"phone_change",
+		"phone_change_token",
+		"phone_confirmed_at",
+	); err != nil {
+		return err
+	}
+
+	identity, err := FindIdentityByIdAndProvider(tx, u.ID.String(), "phone")
+	if err != nil {
+		if IsNotFoundError(err) {
+			// no phone identity, not an error
+			return nil
+		}
+
+		return err
+	}
+
+	if _, ok := identity.IdentityData["phone"]; ok {
+		identity.IdentityData["phone"] = phone
+	}
+
+	if err := tx.UpdateOnly(identity, "identity_data"); err != nil {
+		return err
+	}
+
+	return nil
 }
 
 // Recover resets the recovery token
@@ -452,15 +508,43 @@ func FindUserByPhoneChangeAndAudience(tx *storage.Connection, phone, aud string)
 }
 
 // IsDuplicatedEmail returns whether a user exists with a matching email and audience.
-func IsDuplicatedEmail(tx *storage.Connection, email, aud string) (bool, error) {
-	_, err := FindUserByEmailAndAudience(tx, email, aud)
-	if err != nil {
-		if IsNotFoundError(err) {
-			return false, nil
+func IsDuplicatedEmail(tx *storage.Connection, email, aud string) (*User, error) {
+	var identities []Identity
+
+	if err := tx.Eager().Q().Where("email = ?", strings.ToLower(email)).All(&identities); err != nil {
+		if errors.Cause(err) == sql.ErrNoRows {
+			return nil, nil
 		}
-		return false, err
+
+		return nil, errors.Wrap(err, "unable to find identity by email for duplicates")
 	}
-	return true, nil
+
+	userIDs := make(map[string]uuid.UUID)
+	for _, identity := range identities {
+		if !identity.IsForSSOProvider() {
+			userIDs[identity.UserID.String()] = identity.UserID
+		}
+	}
+
+	for _, userID := range userIDs {
+		user, err := FindUserByID(tx, userID)
+		if err != nil && !IsNotFoundError(err) {
+			return nil, errors.Wrap(err, "unable to find user from email identity for duplicates")
+		}
+
+		if user.Aud == aud {
+			return user, nil
+		}
+	}
+
+	// out of an abundance of caution, if nothing was found via the
+	// identities table we also do a final check on the users table
+	user, err := FindUserByEmailAndAudience(tx, email, aud)
+	if err != nil && !IsNotFoundError(err) {
+		return nil, errors.Wrap(err, "unable to find user email addres for duplicates")
+	}
+
+	return user, nil
 }
 
 // IsDuplicatedPhone checks if the phone number already exists in the users table


Test patch:
diff --git a/api/external_test.go b/api/external_test.go
index daeadcb9..c0ec4d90 100644
--- a/api/external_test.go
+++ b/api/external_test.go
@@ -52,6 +52,13 @@ func (ts *ExternalTestSuite) createUser(providerId string, email string, name st
 	ts.Require().NoError(err, "Error making new user")
 	ts.Require().NoError(ts.API.db.Create(u), "Error creating user")
 
+	i, err := models.NewIdentity(u, "email", map[string]interface{}{
+		"sub":   u.ID.String(),
+		"email": email,
+	})
+	ts.Require().NoError(err)
+	ts.Require().NoError(ts.API.db.Create(i), "Error creating identity")
+
 	return u, err
 }
 
diff --git a/models/linking_test.go b/models/linking_test.go
new file mode 100644
index 00000000..a24d4ece
--- /dev/null
+++ b/models/linking_test.go
@@ -0,0 +1,180 @@
+package models
+
+import (
+	"testing"
+
+	"github.com/netlify/gotrue/conf"
+	"github.com/netlify/gotrue/storage"
+	"github.com/netlify/gotrue/storage/test"
+	"github.com/stretchr/testify/require"
+	"github.com/stretchr/testify/suite"
+)
+
+type AccountLinkingTestSuite struct {
+	suite.Suite
+
+	db *storage.Connection
+}
+
+func (ts *AccountLinkingTestSuite) SetupTest() {
+	TruncateAll(ts.db)
+}
+
+func TestAccountLinking(t *testing.T) {
+	globalConfig, err := conf.LoadGlobal(modelsTestConfig)
+	require.NoError(t, err)
+
+	conn, err := test.SetupDBConnection(globalConfig)
+	require.NoError(t, err)
+
+	ts := &AccountLinkingTestSuite{
+		db: conn,
+	}
+	defer ts.db.Close()
+
+	suite.Run(t, ts)
+}
+
+func (ts *AccountLinkingTestSuite) TestCreateAccountDecisionNoAccounts() {
+	// when there are no accounts in the system -- conventional provider
+	decision, err := DetermineAccountLinking(ts.db, "provider", "abcdefgh", []string{"test@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, CreateAccount)
+
+	// when there are no accounts in the system -- SSO provider
+	decision, err = DetermineAccountLinking(ts.db, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", "abcdefgh", []string{"test@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, CreateAccount)
+}
+
+func (ts *AccountLinkingTestSuite) TestCreateAccountDecisionWithAccounts() {
+	userA, err := NewUser("", "test@example.com", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userA))
+	identityA, err := NewIdentity(userA, "provider", map[string]interface{}{
+		"sub":   userA.ID.String(),
+		"email": "test@example.com",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityA))
+
+	userB, err := NewUser("", "test@samltest.id", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userB))
+
+	identityB, err := NewIdentity(userB, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", map[string]interface{}{
+		"sub":   userB.ID.String(),
+		"email": "test@samltest.id",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityB))
+
+	// when there are no accounts in the system -- conventional provider
+	decision, err := DetermineAccountLinking(ts.db, "provider", "abcdefgh", []string{"other@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, CreateAccount)
+
+	// when looking for an email that doesn't exist in the SSO linking domain
+	decision, err = DetermineAccountLinking(ts.db, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", "abcdefgh", []string{"other@samltest.id"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, CreateAccount)
+
+	// when looking for an email that doesn't exist at all
+	decision, err = DetermineAccountLinking(ts.db, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", "abcdefgh", []string{"other@samltest.id"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, CreateAccount)
+
+	// when looking for an email that doesn't exist in the SSO linking domain
+	decision, err = DetermineAccountLinking(ts.db, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", "abcdefgh", []string{"text@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, CreateAccount)
+}
+
+func (ts *AccountLinkingTestSuite) TestAccountExists() {
+	userA, err := NewUser("", "test@example.com", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userA))
+	identityA, err := NewIdentity(userA, "provider", map[string]interface{}{
+		"sub":   userA.ID.String(),
+		"email": "test@example.com",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityA))
+
+	decision, err := DetermineAccountLinking(ts.db, "provider", userA.ID.String(), []string{"test@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, AccountExists)
+	require.Equal(ts.T(), decision.User.ID, userA.ID)
+}
+
+func (ts *AccountLinkingTestSuite) TestLinkAccountExists() {
+	userA, err := NewUser("", "test@example.com", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userA))
+	identityA, err := NewIdentity(userA, "provider", map[string]interface{}{
+		"sub":   userA.ID.String(),
+		"email": "test@example.com",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityA))
+
+	// link decision because the below described identity is in the default linking domain but uses "other-provider" instead of "provder"
+	decision, err := DetermineAccountLinking(ts.db, "other-provider", userA.ID.String(), []string{"test@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, LinkAccount)
+
+	userB, err := NewUser("", "test@samltest.id", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userB))
+
+	identityB, err := NewIdentity(userB, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", map[string]interface{}{
+		"sub":   userB.ID.String(),
+		"email": "test@samltest.id",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityB))
+
+	// no link decision because the SSO linking domain is scoped to the provider unique ID
+	decision, err = DetermineAccountLinking(ts.db, "sso:f06f9e3d-ff92-4c47-a179-7acf1fda6387", userB.ID.String(), []string{"test@samltest.id"})
+	require.NoError(ts.T(), err)
+
+	require.NotEqual(ts.T(), decision.Decision, LinkAccount)
+}
+
+func (ts *AccountLinkingTestSuite) TestMultipleAccounts() {
+	userA, err := NewUser("", "test@example.com", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userA))
+	identityA, err := NewIdentity(userA, "provider", map[string]interface{}{
+		"sub":   userA.ID.String(),
+		"email": "test@example.com",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityA))
+
+	userB, err := NewUser("", "test-b@example.com", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(userB))
+	identityB, err := NewIdentity(userB, "provider", map[string]interface{}{
+		"sub":   userB.ID.String(),
+		"email": "test@example.com", // intentionally same as userA
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identityB))
+
+	// decision is multiple accounts because there are two distinct
+	// identities in the same "default" linking domain with the same email
+	// address pointing to two different user accounts
+	decision, err := DetermineAccountLinking(ts.db, "provider", "abcdefgh", []string{"test@example.com"})
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), decision.Decision, MultipleAccounts)
+}
diff --git a/models/user_test.go b/models/user_test.go
index db664faf..66369d28 100644
--- a/models/user_test.go
+++ b/models/user_test.go
@@ -164,19 +164,19 @@ func (ts *UserTestSuite) TestIsDuplicatedEmail() {
 
 	e, err := IsDuplicatedEmail(ts.db, "david.calavera@netlify.com", "test")
 	require.NoError(ts.T(), err)
-	require.True(ts.T(), e, "expected email to be duplicated")
+	require.NotNil(ts.T(), e, "expected email to be duplicated")
 
 	e, err = IsDuplicatedEmail(ts.db, "davidcalavera@netlify.com", "test")
 	require.NoError(ts.T(), err)
-	require.False(ts.T(), e, "expected email to not be duplicated")
+	require.Nil(ts.T(), e, "expected email to not be duplicated")
 
 	e, err = IsDuplicatedEmail(ts.db, "david@netlify.com", "test")
 	require.NoError(ts.T(), err)
-	require.False(ts.T(), e, "expected same email to not be duplicated")
+	require.Nil(ts.T(), e, "expected same email to not be duplicated")
 
 	e, err = IsDuplicatedEmail(ts.db, "david.calavera@netlify.com", "other-aud")
 	require.NoError(ts.T(), err)
-	require.False(ts.T(), e, "expected same email to not be duplicated")
+	require.Nil(ts.T(), e, "expected same email to not be duplicated")
 }
 
 func (ts *UserTestSuite) createUser() *User {
@@ -186,9 +186,14 @@ func (ts *UserTestSuite) createUser() *User {
 func (ts *UserTestSuite) createUserWithEmail(email string) *User {
 	user, err := NewUser("", email, "secret", "test", nil)
 	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(user))
 
-	err = ts.db.Create(user)
+	identity, err := NewIdentity(user, "email", map[string]interface{}{
+		"sub":   user.ID.String(),
+		"email": email,
+	})
 	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identity))
 
 	return user
 }
@@ -245,3 +250,59 @@ func (ts *UserTestSuite) TestRemoveUnconfirmedIdentities() {
 	require.Equal(ts.T(), user.AppMetaData["provider"], "phone")
 	require.Equal(ts.T(), user.AppMetaData["providers"], []string{"phone", "twitter"})
 }
+
+func (ts *UserTestSuite) TestConfirmEmailChange() {
+	user, err := NewUser("", "test@example.com", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(user))
+
+	identity, err := NewIdentity(user, "email", map[string]interface{}{
+		"sub":   user.ID.String(),
+		"email": "test@example.com",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identity))
+
+	user.EmailChange = "new@example.com"
+	require.NoError(ts.T(), ts.db.UpdateOnly(user, "email_change"))
+
+	require.NoError(ts.T(), user.ConfirmEmailChange(ts.db, 0))
+
+	require.NoError(ts.T(), ts.db.Eager().Load(user))
+	identity, err = FindIdentityByIdAndProvider(ts.db, user.ID.String(), "email")
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), user.Email, storage.NullString("new@example.com"))
+	require.Equal(ts.T(), user.EmailChange, "")
+
+	require.NotNil(ts.T(), identity.IdentityData)
+	require.Equal(ts.T(), identity.IdentityData["email"], "new@example.com")
+}
+
+func (ts *UserTestSuite) TestConfirmPhoneChange() {
+	user, err := NewUser("123456789", "", "", "authenticated", nil)
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(user))
+
+	identity, err := NewIdentity(user, "phone", map[string]interface{}{
+		"sub":   user.ID.String(),
+		"phone": "123456789",
+	})
+	require.NoError(ts.T(), err)
+	require.NoError(ts.T(), ts.db.Create(identity))
+
+	user.PhoneChange = "987654321"
+	require.NoError(ts.T(), ts.db.UpdateOnly(user, "phone_change"))
+
+	require.NoError(ts.T(), user.ConfirmPhoneChange(ts.db))
+
+	require.NoError(ts.T(), ts.db.Eager().Load(user))
+	identity, err = FindIdentityByIdAndProvider(ts.db, user.ID.String(), "phone")
+	require.NoError(ts.T(), err)
+
+	require.Equal(ts.T(), user.Phone, storage.NullString("987654321"))
+	require.Equal(ts.T(), user.PhoneChange, "")
+
+	require.NotNil(ts.T(), identity.IdentityData)
+	require.Equal(ts.T(), identity.IdentityData["phone"], "987654321")
+}