Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
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
}