Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

31.2 KB
Raw Blame
package store

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"
	"time"

	"github.com/pkg/errors"
	"google.golang.org/protobuf/encoding/protojson"
	"google.golang.org/protobuf/types/known/timestamppb"

	"github.com/bytebase/bytebase/backend/common"
	storepb "github.com/bytebase/bytebase/backend/generated-go/store"
	"github.com/bytebase/bytebase/backend/store/qb"
)

// FindUserMessage is the message for finding users.
type FindUserMessage struct {
	ID          *int
	Email       *string
	ShowDeleted bool
	Limit       *int
	Offset      *int
	FilterQ     *qb.Query
	ProjectID   *string
	// Workspace scopes user listing to workspace IAM policy members.
	// When ProjectID is also set, includes project-level members too.
	Workspace string
}

// UpdateUserMessage is the message to update a user.
type UpdateUserMessage struct {
	Email        *string
	Name         *string
	PasswordHash *string
	Delete       *bool
	MFAConfig    *storepb.MFAConfig
	Profile      *storepb.UserProfile
	Phone        *string
}

// UserMessage is the message for an end user (principal table).
type UserMessage struct {
	ID int
	// Email must be lower case.
	Email         string
	Name          string
	Type          storepb.PrincipalType
	PasswordHash  string
	MemberDeleted bool
	MFAConfig     *storepb.MFAConfig
	Profile       *storepb.UserProfile
	// Phone conforms E.164 format.
	Phone string
	// output only
	CreatedAt time.Time
}

// GetUserByID gets the user by ID.
func (s *Store) GetUserByID(ctx context.Context, id int) (*UserMessage, error) {
	users, err := s.ListUsers(ctx, &FindUserMessage{ID: &id})
	if err != nil {
		return nil, err
	}
	if len(users) == 0 {
		return nil, nil
	}
	return users[0], nil
}

// GetUserByEmail gets the user by email.
func (s *Store) GetUserByEmail(ctx context.Context, email string) (*UserMessage, error) {
	if v, ok := s.userEmailCache.Get(email); ok && s.enableCache {
		return v, nil
	}

	if err := s.listAndCacheAllUsers(ctx); err != nil {
		return nil, err
	}

	user, _ := s.userEmailCache.Get(email)
	return user, nil
}

// BatchGetUsersByEmails gets users (of any type) by emails in batch.
func (s *Store) BatchGetUsersByEmails(ctx context.Context, workspace string, emails []string) ([]*UserMessage, error) {
	if len(emails) == 0 {
		return nil, nil
	}

	normalizedEmails := make([]string, len(emails))
	for i, email := range emails {
		normalizedEmails[i] = strings.ToLower(email)
	}

	var users []*UserMessage

	var q *qb.Query
	if workspace != "" {
		// SaaS mode: scope to workspace IAM members only.
		q = qb.Q().Space(`
			SELECT p.id, p.deleted, p.email, p.name, p.password_hash, p.mfa_config, p.phone, p.profile, p.created_at
			FROM principal p
			JOIN policy pol ON pol.workspace = ? AND pol.resource_type = 'WORKSPACE' AND pol.type = 'IAM'
			WHERE p.email = ANY(?)
			  AND EXISTS (
				SELECT 1
				FROM jsonb_array_elements(pol.payload->'bindings') AS binding,
				     jsonb_array_elements_text(binding->'members') AS member
				WHERE member = 'users/' || p.email OR member = ?
			  )
			ORDER BY p.created_at ASC
		`, workspace, normalizedEmails, common.AllUsers)
	} else {
		// Non-SaaS mode: no workspace scoping.
		q = qb.Q().Space(`
			SELECT p.id, p.deleted, p.email, p.name, p.password_hash, p.mfa_config, p.phone, p.profile, p.created_at
			FROM principal p
			WHERE p.email = ANY(?)
			ORDER BY p.created_at ASC
		`, normalizedEmails)
	}
	sqlStr, args, err := q.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build sql")
	}
	rows, err := s.GetDB().QueryContext(ctx, sqlStr, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var user UserMessage
		var mfaConfigBytes, profileBytes []byte
		if err := rows.Scan(&user.ID, &user.MemberDeleted, &user.Email, &user.Name, &user.PasswordHash, &mfaConfigBytes, &user.Phone, &profileBytes, &user.CreatedAt); err != nil {
			return nil, err
		}
		user.Type = storepb.PrincipalType_END_USER
		mfaConfig := storepb.MFAConfig{}
		if err := common.ProtojsonUnmarshaler.Unmarshal(mfaConfigBytes, &mfaConfig); err != nil {
			return nil, err
		}
		user.MFAConfig = &mfaConfig
		profile := storepb.UserProfile{}
		if err := common.ProtojsonUnmarshaler.Unmarshal(profileBytes, &profile); err != nil {
			return nil, err
		}
		user.Profile = &profile
		users = append(users, &user)
	}
	if err := rows.Err(); err != nil {
		return nil, errors.Wrapf(err, "failed to scan rows")
	}

	for _, user := range users {
		s.userEmailCache.Add(user.Email, user)
	}
	return users, nil
}

// ListUsers list users.
func (s *Store) ListUsers(ctx context.Context, find *FindUserMessage) ([]*UserMessage, error) {
	tx, err := s.GetDB().BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	users, err := listUserImpl(ctx, tx, find)
	if err != nil {
		return nil, err
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}

	for _, user := range users {
		s.userEmailCache.Add(user.Email, user)
	}
	return users, nil
}

// listAndCacheAllUsers is used for caching all users.
func (s *Store) listAndCacheAllUsers(ctx context.Context) error {
	tx, err := s.GetDB().BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	users, err := listUserImpl(ctx, tx, &FindUserMessage{ShowDeleted: true})
	if err != nil {
		return err
	}

	if err := tx.Commit(); err != nil {
		return err
	}

	for _, user := range users {
		s.userEmailCache.Add(user.Email, user)
	}
	return nil
}

func listUserImpl(ctx context.Context, txn *sql.Tx, find *FindUserMessage) ([]*UserMessage, error) {
	with := qb.Q()
	from := qb.Q().Space("principal")
	where := qb.Q().Space("TRUE")

	// Scope users by workspace or project membership.
	if v := find.ProjectID; v != nil {
		// Project filter: include users who are members of the project or workspace.
		with.Space(`WITH all_members AS (
			SELECT
				jsonb_array_elements_text(jsonb_array_elements(policy.payload->'bindings')->'members') AS member,
				jsonb_array_elements(policy.payload->'bindings')->>'role' AS role
			FROM policy
			WHERE ((resource_type = ? AND resource = ?) OR resource_type = ?) AND type = ? AND policy.workspace = ?
		),
		project_members AS (
			SELECT ARRAY_AGG(member) AS members FROM all_members WHERE role NOT LIKE 'roles/workspace%'
		)`, storepb.Policy_PROJECT.String(), "projects/"+*v, storepb.Policy_WORKSPACE.String(), storepb.Policy_IAM.String(), find.Workspace)
		from.Space(`INNER JOIN project_members ON (CONCAT('users/', principal.email) = ANY(project_members.members) OR ? = ANY(project_members.members))`, common.AllUsers)
	} else if find.Workspace != "" {
		// Workspace IAM filter (no project): include only users who are members of the workspace IAM policy.
		// Used in SaaS mode to prevent cross-workspace user listing.
		with.Space(`WITH workspace_members AS (
			SELECT ARRAY_AGG(DISTINCT member) AS members
			FROM (
				SELECT jsonb_array_elements_text(jsonb_array_elements(policy.payload->'bindings')->'members') AS member
				FROM policy
				WHERE resource_type = ? AND type = ? AND policy.workspace = ?
			) sub
		)`, storepb.Policy_WORKSPACE.String(), storepb.Policy_IAM.String(), find.Workspace)
		from.Space(`INNER JOIN workspace_members ON (CONCAT('users/', principal.email) = ANY(workspace_members.members) OR ? = ANY(workspace_members.members))`, common.AllUsers)
	}

	if filterQ := find.FilterQ; filterQ != nil {
		where.And("?", filterQ)
	}
	if v := find.ID; v != nil {
		where.And("principal.id = ?", *v)
	}
	if v := find.Email; v != nil {
		if *v == common.AllUsers {
			where.And("principal.email = ?", *v)
		} else {
			where.And("principal.email = ?", strings.ToLower(*v))
		}
	}
	if !find.ShowDeleted {
		where.And("principal.deleted = ?", false)
	}

	q := qb.Q().Space("?", with)
	q.Space(`
		SELECT
			principal.id AS user_id,
			principal.deleted,
			principal.email,
			principal.name,
			principal.password_hash,
			principal.mfa_config,
			principal.phone,
			principal.profile,
			principal.created_at
		FROM ?
		WHERE ?
	`, from, where)

	// created_at defaults to now(), so users provisioned by one SCIM sync
	// share it; principal.id is the primary key, and stays unique in the result
	// because the project and workspace filters join single-row ARRAY_AGG CTEs
	// rather than the member rows themselves.
	q.Space("ORDER BY principal.created_at ASC, principal.id ASC")

	if v := find.Limit; v != nil {
		q.Space("LIMIT ?", *v)
	}
	if v := find.Offset; v != nil {
		q.Space("OFFSET ?", *v)
	}

	sql, args, err := q.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build sql")
	}

	var userMessages []*UserMessage
	rows, err := txn.QueryContext(ctx, sql, args...) // NOSONAR: query is parameterized via qb.Query
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var userMessage UserMessage
		var mfaConfigBytes []byte
		var profileBytes []byte
		if err := rows.Scan(
			&userMessage.ID,
			&userMessage.MemberDeleted,
			&userMessage.Email,
			&userMessage.Name,
			&userMessage.PasswordHash,
			&mfaConfigBytes,
			&userMessage.Phone,
			&profileBytes,
			&userMessage.CreatedAt,
		); err != nil {
			return nil, err
		}
		userMessage.Type = storepb.PrincipalType_END_USER

		mfaConfig := storepb.MFAConfig{}
		if err := common.ProtojsonUnmarshaler.Unmarshal(mfaConfigBytes, &mfaConfig); err != nil {
			return nil, err
		}
		userMessage.MFAConfig = &mfaConfig
		profile := storepb.UserProfile{}
		if err := common.ProtojsonUnmarshaler.Unmarshal(profileBytes, &profile); err != nil {
			return nil, err
		}
		userMessage.Profile = &profile

		userMessages = append(userMessages, &userMessage)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return userMessages, nil
}

// ConsumeRecoveryCode removes one live recovery code from the account, and
// reports whether this call is the one that removed it. The removal is a
// predicate on the row rather than a read-modify-write: two requests spending
// the same code race here and exactly one sees consumed=true, and the other
// live fields are never rewritten from whatever the caller happened to read.
//
// Callers must treat consumed=false as an invalid code. Verifying against a
// list read earlier is not enough — that list can be a spend behind.
func (s *Store) ConsumeRecoveryCode(ctx context.Context, userID int, code string) (bool, error) {
	// protojson's spelling for MFAConfig.recovery_codes; this JSON is read
	// back through protojson.Unmarshal.
	q := qb.Q().Space(`
		UPDATE principal
		SET mfa_config = jsonb_set(
			mfa_config,
			'{recoveryCodes}',
			COALESCE((
				SELECT jsonb_agg(remaining)
				FROM jsonb_array_elements(mfa_config->'recoveryCodes') AS remaining
				WHERE remaining <> to_jsonb(?::text)
			), '[]'::jsonb))
		WHERE id = ? AND COALESCE(mfa_config->'recoveryCodes', '[]'::jsonb) @> to_jsonb(?::text)
		RETURNING email
	`, code, userID, code)
	query, args, err := q.ToSQL()
	if err != nil {
		return false, errors.Wrapf(err, "failed to build sql")
	}
	var email string
	if err := s.GetDB().QueryRowContext(ctx, query, args...).Scan(&email); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return false, nil
		}
		return false, errors.Wrapf(err, "failed to consume recovery code")
	}
	s.userEmailCache.Remove(email)
	return true, nil
}

// SetPendingMFAState writes the three pending enrollment fields and nothing
// else. The live factor is left exactly as the row has it rather than copied
// forward from whatever the caller read: a mint that rewrites the whole config
// resurrects the factor an administrator disabled, or the recovery codes a
// confirmation just rotated away, whenever either commits while the mint is in
// flight.
//
// All three are always written, so a regeneration clears any pending secret an
// abandoned enrollment left behind — otherwise confirming those codes would
// promote a secret nobody is holding.
func (s *Store) SetPendingMFAState(ctx context.Context, userID int, tempOtpSecret string, tempRecoveryCodes []string, createdTime time.Time) error {
	// Truncated because UpdateUserMFAConfigIfPending matches this value as a
	// timestamptz, and that type is microsecond-resolution: a nanosecond tail
	// stored here would be rounded by the cast in that predicate and never
	// match the value handed back to the caller.
	createdTime = createdTime.Truncate(time.Microsecond)
	codesJSON, err := json.Marshal(tempRecoveryCodes)
	if err != nil {
		return err
	}
	// protojson's spelling for the MFAConfig fields, and its timestamp format:
	// this JSON is read back through protojson.Unmarshal.
	q := qb.Q().Space(`
		UPDATE principal
		SET mfa_config = jsonb_set(
			jsonb_set(
				jsonb_set(mfa_config, '{tempOtpSecret}', to_jsonb(?::text)),
				'{tempRecoveryCodes}', ?::jsonb),
			'{tempOtpSecretCreatedTime}', to_jsonb(?::text))
		WHERE id = ?
		RETURNING email
	`, tempOtpSecret, codesJSON, createdTime.UTC().Format(time.RFC3339Nano), userID)
	query, args, err := q.ToSQL()
	if err != nil {
		return errors.Wrapf(err, "failed to build sql")
	}
	var email string
	if err := s.GetDB().QueryRowContext(ctx, query, args...).Scan(&email); err != nil {
		return errors.Wrapf(err, "failed to set pending mfa state")
	}
	s.userEmailCache.Remove(email)
	return nil
}

// UpdateUserMFAConfigIfPending replaces the account's MFA config, but only
// while the pending enrollment recorded on the row is still the one the caller
// verified. Returns nil when it is not: another tab minted a fresh enrollment,
// or an administrator disabled MFA, in between.
//
// The comparison has to happen in the same statement as the write. Checking a
// user read moments earlier and then writing unconditionally leaves a window
// where a confirmation promotes an enrollment nobody is waiting for — or
// re-enables MFA the administrator just cleared for a locked-out user.
func (s *Store) UpdateUserMFAConfigIfPending(ctx context.Context, userID int, expectedPendingVersion time.Time, mfaConfig *storepb.MFAConfig) (*UserMessage, error) {
	mfaConfigBytes, err := protojson.Marshal(mfaConfig)
	if err != nil {
		return nil, err
	}
	// tempOtpSecretCreatedTime is protojson's camelCase spelling, and comparing
	// as timestamptz rather than text keeps the match independent of how many
	// fractional digits the value was serialized with. A cleared config has no
	// such key, so the NULL comparison fails and the write is refused.
	//
	// timestamptz is microsecond-resolution, so both sides are truncated to
	// microseconds: the cast rounds a nanosecond tail while the bound
	// parameter does not, and the two then disagree for any instant whose
	// sub-microsecond remainder is large enough to round up. Go's clock is
	// microsecond-granular on macOS and nanosecond-granular on Linux, so
	// without this the mismatch appears only on Linux — and there, for roughly
	// half of all enrollments.
	q := qb.Q().Space(`
		UPDATE principal SET mfa_config = ?
		WHERE id = ? AND (mfa_config->>'tempOtpSecretCreatedTime')::timestamptz = ?
		RETURNING id, deleted, email, name, password_hash, mfa_config, phone, profile, created_at
	`, mfaConfigBytes, userID, expectedPendingVersion.Truncate(time.Microsecond))
	sqlStr, args, err := q.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build sql")
	}

	tx, err := s.GetDB().BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	updatedUser, err := scanPrincipalRow(ctx, tx, sqlStr, args)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, errors.Wrapf(err, "failed to update mfa config")
	}
	if err := tx.Commit(); err != nil {
		return nil, err
	}

	s.userEmailCache.Add(updatedUser.Email, updatedUser)
	return updatedUser, nil
}

// scanPrincipalRow scans a principal row into a UserMessage (without groups).
func scanPrincipalRow(ctx context.Context, tx *sql.Tx, sqlStr string, args []any) (*UserMessage, error) {
	var user UserMessage
	var mfaConfigBytes []byte
	var profileBytes []byte
	if err := tx.QueryRowContext(ctx, sqlStr, args...).Scan( // NOSONAR: query is parameterized via qb.Query
		&user.ID,
		&user.MemberDeleted,
		&user.Email,
		&user.Name,
		&user.PasswordHash,
		&mfaConfigBytes,
		&user.Phone,
		&profileBytes,
		&user.CreatedAt,
	); err != nil {
		return nil, err
	}
	user.Type = storepb.PrincipalType_END_USER

	mfaConfig := storepb.MFAConfig{}
	if err := common.ProtojsonUnmarshaler.Unmarshal(mfaConfigBytes, &mfaConfig); err != nil {
		return nil, err
	}
	user.MFAConfig = &mfaConfig

	profile := storepb.UserProfile{}
	if err := common.ProtojsonUnmarshaler.Unmarshal(profileBytes, &profile); err != nil {
		return nil, err
	}
	user.Profile = &profile

	return &user, nil
}

// CreateUser creates an user.
func (s *Store) CreateUser(ctx context.Context, create *UserMessage) (*UserMessage, error) {
	// Double check the passing-in emails.
	// We use lower-case for emails.
	if create.Email != strings.ToLower(create.Email) {
		return nil, errors.Errorf("emails must be lower-case when they are passed into store")
	}

	if create.Profile == nil {
		create.Profile = &storepb.UserProfile{}
	}
	profileBytes, err := protojson.Marshal(create.Profile)
	if err != nil {
		return nil, err
	}

	q := qb.Q().Space(`
		INSERT INTO principal (
			email,
			name,
			password_hash,
			phone,
			profile
		)
		VALUES (?, ?, ?, ?, ?)
		RETURNING id, created_at
	`, create.Email, create.Name, create.PasswordHash, create.Phone, profileBytes)

	sqlStr, args, err := q.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build sql")
	}

	user := &UserMessage{
		Email:        create.Email,
		Name:         create.Name,
		Type:         storepb.PrincipalType_END_USER,
		PasswordHash: create.PasswordHash,
		Phone:        create.Phone,
		CreatedAt:    create.CreatedAt,
		Profile:      create.Profile,
		MFAConfig:    &storepb.MFAConfig{},
	}

	if err := s.GetDB().QueryRowContext(ctx, sqlStr, args...).Scan(&user.ID, &user.CreatedAt); err != nil {
		return nil, err
	}

	s.userEmailCache.Add(user.Email, user)
	return user, nil
}

// UpdateUser updates a user.
func (s *Store) UpdateUser(ctx context.Context, currentUser *UserMessage, patch *UpdateUserMessage) (*UserMessage, error) {
	set := qb.Q()
	if v := patch.Delete; v != nil {
		set.Comma("deleted = ?", *v)
	}
	if v := patch.Name; v != nil {
		set.Comma("name = ?", *v)
	}
	if v := patch.PasswordHash; v != nil {
		set.Comma("password_hash = ?", *v)
		if patch.Profile == nil {
			patch.Profile = currentUser.Profile
			patch.Profile.LastChangePasswordTime = timestamppb.New(time.Now())
		}
	}
	if v := patch.Phone; v != nil {
		set.Comma("phone = ?", *v)
	}
	if v := patch.MFAConfig; v != nil {
		mfaConfigBytes, err := protojson.Marshal(v)
		if err != nil {
			return nil, err
		}
		set.Comma("mfa_config = ?", mfaConfigBytes)
	}
	if v := patch.Profile; v != nil {
		profileBytes, err := protojson.Marshal(v)
		if err != nil {
			return nil, err
		}
		set.Comma("profile = ?", profileBytes)
	}

	if set.Len() == 0 {
		return currentUser, nil
	}

	sql, args, err := qb.Q().Space(`UPDATE principal SET ? WHERE id = ?
		RETURNING id, deleted, email, name, password_hash, mfa_config, phone, profile, created_at`,
		set, currentUser.ID).ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build sql")
	}

	tx, err := s.GetDB().BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	updatedUser, err := scanPrincipalRow(ctx, tx, sql, args)
	if err != nil {
		return nil, err
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}

	s.userEmailCache.Remove(currentUser.Email)
	s.userEmailCache.Add(updatedUser.Email, updatedUser)
	return updatedUser, nil
}

// UpdateUserEmail updates a user's email and all related references.
func (s *Store) UpdateUserEmail(ctx context.Context, user *UserMessage, newEmail string) (*UserMessage, error) {
	newEmail = strings.ToLower(newEmail)

	tx, err := s.GetDB().BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	// 1. Update Principal table
	query := qb.Q().Space(`UPDATE principal SET email = ? WHERE id = ?
		RETURNING id, deleted, email, name, password_hash, mfa_config, phone, profile, created_at`,
		newEmail, user.ID)
	sqlStr, args, err := query.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build update principal sql")
	}

	updatedUser, err := scanPrincipalRow(ctx, tx, sqlStr, args)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to update principal email")
	}

	oldUserRef := common.FormatUserEmail(user.Email)
	newUserRef := common.FormatUserEmail(newEmail)

	updateReference := func(stmt string) error {
		if _, err := tx.ExecContext(ctx, stmt, newEmail, user.Email); err != nil {
			return errors.Wrapf(err, "failed to update creator/deleter references")
		}
		return nil
	}

	if err := updateReference("UPDATE query_history SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}

	// 2. Update Policies
	// Update IAM policies: bindings->members array contains user references
	// Update MASKING_EXEMPTION policies: exemptions->members field contains user references
	var invalidatedPolicies []struct {
		Workspace    string
		ResourceType storepb.Policy_Resource
		Resource     string
		Type         storepb.Policy_Type
	}

	// 3a. Update IAM policy bindings
	iamPolicySQL := `
		UPDATE policy
		SET payload = (
			SELECT jsonb_set(
				policy.payload,
				'{bindings}',
				COALESCE(
					(
						SELECT jsonb_agg(
							jsonb_set(
								binding,
								'{members}',
								COALESCE(
									(
										SELECT jsonb_agg(
											CASE WHEN member = $1 THEN $2::text ELSE member END
										)
										FROM jsonb_array_elements_text(binding->'members') AS member
									),
									'[]'::jsonb
								)
							)
						)
						FROM jsonb_array_elements(policy.payload->'bindings') AS binding
					),
					'[]'::jsonb
				)
			)
		)
		WHERE type = $3
		  AND payload ? 'bindings'
		  AND EXISTS (
			  SELECT 1
			  FROM jsonb_array_elements(payload->'bindings') AS binding,
				   jsonb_array_elements_text(binding->'members') AS member
			  WHERE member = $1
		  )
		RETURNING workspace, resource_type, resource, type`

	rows, err := tx.QueryContext(ctx, iamPolicySQL, oldUserRef, newUserRef, storepb.Policy_IAM.String())
	if err != nil {
		return nil, errors.Wrapf(err, "failed to update IAM policies")
	}
	defer rows.Close()

	for rows.Next() {
		var workspace, resourceTypeStr, resource, typeStr string
		if err := rows.Scan(&workspace, &resourceTypeStr, &resource, &typeStr); err != nil {
			return nil, errors.Wrapf(err, "failed to scan updated IAM policy")
		}

		var invalidation struct {
			Workspace    string
			ResourceType storepb.Policy_Resource
			Resource     string
			Type         storepb.Policy_Type
		}
		invalidation.Workspace = workspace
		invalidation.Resource = resource

		if val, ok := storepb.Policy_Resource_value[resourceTypeStr]; ok {
			invalidation.ResourceType = storepb.Policy_Resource(val)
		}
		if val, ok := storepb.Policy_Type_value[typeStr]; ok {
			invalidation.Type = storepb.Policy_Type(val)
		}
		invalidatedPolicies = append(invalidatedPolicies, invalidation)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	rows.Close()

	// 3b. Update MASKING_EXEMPTION policy exemptions
	maskingPolicySQL := `
		UPDATE policy
		SET payload = (
			SELECT jsonb_set(
				policy.payload,
				'{exemptions}',
				COALESCE(
					(
						SELECT jsonb_agg(
							CASE
								WHEN $1 = ANY(SELECT jsonb_array_elements_text(exemption->'members')) THEN
									jsonb_set(
										exemption, 
										'{members}', 
										COALESCE(
											(
												SELECT jsonb_agg(
													CASE WHEN member = $1 THEN $2::text ELSE member END
												)
												FROM jsonb_array_elements_text(exemption->'members') AS member
											),
											'[]'::jsonb
										)
									)
								ELSE exemption
							END
						)
						FROM jsonb_array_elements(policy.payload->'exemptions') AS exemption
					),
					'[]'::jsonb
				)
			)
		)
		WHERE type = $3
		  AND payload ? 'exemptions'
		  AND EXISTS (
			  SELECT 1
			  FROM jsonb_array_elements(payload->'exemptions') AS exemption,
			       jsonb_array_elements_text(exemption->'members') AS member
			  WHERE member = $1
		  )
		RETURNING workspace, resource_type, resource, type`

	rows, err = tx.QueryContext(ctx, maskingPolicySQL, oldUserRef, newUserRef, storepb.Policy_MASKING_EXEMPTION.String())
	if err != nil {
		return nil, errors.Wrapf(err, "failed to update MASKING_EXCEPTION policies")
	}
	defer rows.Close()

	for rows.Next() {
		var workspace, resourceTypeStr, resource, typeStr string
		if err := rows.Scan(&workspace, &resourceTypeStr, &resource, &typeStr); err != nil {
			return nil, errors.Wrapf(err, "failed to scan updated MASKING_EXEMPTION policy")
		}

		var invalidation struct {
			Workspace    string
			ResourceType storepb.Policy_Resource
			Resource     string
			Type         storepb.Policy_Type
		}
		invalidation.Workspace = workspace
		invalidation.Resource = resource

		if val, ok := storepb.Policy_Resource_value[resourceTypeStr]; ok {
			invalidation.ResourceType = storepb.Policy_Resource(val)
		}
		if val, ok := storepb.Policy_Type_value[typeStr]; ok {
			invalidation.Type = storepb.Policy_Type(val)
		}
		invalidatedPolicies = append(invalidatedPolicies, invalidation)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	rows.Close()

	if err := updateReference("UPDATE saved_query_star SET principal = $1 WHERE principal = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE saved_query SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}
	// Saved-query grants name their principals by email as well, so a rename
	// has to rewrite the members inside the bindings jsonb in the same
	// transaction — otherwise every grant made to this address silently
	// orphans. Both needles are whole JSON strings built here rather than
	// assembled in SQL, so an email's own % or _ cannot act as a wildcard, and
	// the quotes keep the match exact: "user:[email protected]" cannot match inside
	// "user:[email protected]".
	oldMember := fmt.Sprintf("%q", common.FormatUserEmail(user.Email))
	newMember := fmt.Sprintf("%q", common.FormatUserEmail(newEmail))
	if _, err := tx.ExecContext(ctx, `
		UPDATE saved_query
		SET bindings = replace(bindings::text, $1, $2)::jsonb
		WHERE strpos(bindings::text, $1) > 0
	`, oldMember, newMember); err != nil {
		return nil, errors.Wrapf(err, "failed to rewrite saved query grants for %s", user.Email)
	}
	if err := updateReference("UPDATE issue_comment SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE issue SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}

	// Update RoleGrant in issue payload.
	// The user in RoleGrant is stored as "users/{email}" within the JSON payload.
	// We use text replacement for the specific path.
	// 'roleGrant' is the json key for role_grant field in Issue proto.
	query = qb.Q().Space(`
		UPDATE issue
		SET payload = jsonb_set(payload, '{roleGrant,user}', to_jsonb(?::text))
		WHERE payload->'roleGrant'->>'user' = ?`,
		newUserRef, oldUserRef)
	sqlStr, args, err = query.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build update role grant sql")
	}
	if _, err := tx.ExecContext(ctx, sqlStr, args...); err != nil {
		return nil, errors.Wrapf(err, "failed to update issue role grant")
	}

	// Update Approval approvers in Issue payload.
	// The principal in approvers is stored as "users/{email}" within the JSON payload.
	// We need to update each approver's principal field if it matches the old user reference.
	approverSQL := `
		UPDATE issue
		SET payload = (
			SELECT jsonb_set(
				issue.payload,
				'{approval,approvers}',
				COALESCE(
					(
						SELECT jsonb_agg(
							CASE
								WHEN approver->>'principal' = $1 THEN
									jsonb_set(approver, '{principal}', to_jsonb($2::text))
								ELSE approver
							END
						)
						FROM jsonb_array_elements(issue.payload->'approval'->'approvers') AS approver
					),
					'[]'::jsonb
				)
			)
		)
		WHERE payload->'approval' ? 'approvers'
		  AND EXISTS (
			  SELECT 1
			  FROM jsonb_array_elements(payload->'approval'->'approvers') AS approver
			  WHERE approver->>'principal' = $1
		  )`

	if _, err := tx.ExecContext(ctx, approverSQL, oldUserRef, newUserRef); err != nil {
		return nil, errors.Wrapf(err, "failed to update issue approval approvers")
	}

	if err := updateReference("UPDATE task_run SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE plan SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE plan SET last_plan_editor = $1 WHERE last_plan_editor = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE access_grant SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE release SET creator = $1 WHERE creator = $2"); err != nil {
		return nil, err
	}
	if err := updateReference("UPDATE revision SET deleter = $1 WHERE deleter = $2"); err != nil {
		return nil, err
	}

	// Update User Groups.
	// Update user_group.payload to replace old user reference with new one in members array.
	// Members are stored as GroupMember objects with member field in "users/{email}" format.
	userGroupSQL := `
		UPDATE user_group
		SET payload = (
			SELECT jsonb_set(
				user_group.payload,
				'{members}',
				COALESCE(
					(
						SELECT jsonb_agg(
							CASE
								WHEN member->>'member' = $1 THEN
									jsonb_set(member, '{member}', to_jsonb($2::text))
								ELSE member
							END
						)
						FROM jsonb_array_elements(user_group.payload->'members') AS member
					),
					'[]'::jsonb
				)
			)
		)
		WHERE payload ? 'members'
		  AND EXISTS (
			  SELECT 1
			  FROM jsonb_array_elements(payload->'members') AS member
			  WHERE member->>'member' = $1
		  )
		RETURNING email`

	rows, err = tx.QueryContext(ctx, userGroupSQL, oldUserRef, newUserRef)
	if err != nil {
		return nil, errors.Wrapf(err, "failed to update user_group memberships")
	}
	defer rows.Close()

	var invalidatedGroupEmails []string
	for rows.Next() {
		var email sql.NullString
		if err := rows.Scan(&email); err != nil {
			return nil, errors.Wrapf(err, "failed to scan updated group")
		}
		if email.Valid {
			invalidatedGroupEmails = append(invalidatedGroupEmails, email.String)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	rows.Close()

	// 5. Update Audit Logs
	// Update the audit log principal reference.
	// AuditLog.User is stored in the 'user' field in "users/{email}" format.
	query = qb.Q().Space(`
		UPDATE audit_log
		SET payload = jsonb_set(payload, '{user}', to_jsonb(?::text))
		WHERE payload->>'user' = ?`,
		newUserRef, oldUserRef)

	sqlStr, args, err = query.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build update audit_log sql")
	}

	if _, err := tx.ExecContext(ctx, sqlStr, args...); err != nil {
		return nil, errors.Wrapf(err, "failed to update audit_log user references")
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}

	// 6. Update caches
	s.userEmailCache.Remove(user.Email)

	// Invalidate policy cache for updated policies
	for _, p := range invalidatedPolicies {
		s.policyCache.Remove(getPolicyCacheKey(p.Workspace, p.ResourceType, p.Resource, p.Type))
		if p.Type == storepb.Policy_IAM {
			s.iamPolicyCache.Remove(getIamPolicyCacheKey(p.Workspace, p.ResourceType, p.Resource))
		}
	}

	// Purge all group caches — email change is rare and affects groups cross-workspace.
	if len(invalidatedGroupEmails) > 0 {
		s.PurgeGroupCaches()
	}

	// Re-populate user cache
	s.userEmailCache.Add(updatedUser.Email, updatedUser)

	return updatedUser, nil
}