github.com/haalcala/mattermost-server-change-repo@v0.0.0-20210713015153-16753fbeee5f/store/sqlstore/user_store.go (about)

     1  // Copyright (c) 2015-present Mattermost, Inc. All Rights Reserved.
     2  // See LICENSE.txt for license information.
     3  
     4  package sqlstore
     5  
     6  import (
     7  	"context"
     8  	"database/sql"
     9  	"encoding/json"
    10  	"fmt"
    11  	"sort"
    12  	"strings"
    13  
    14  	sq "github.com/Masterminds/squirrel"
    15  	"github.com/mattermost/gorp"
    16  	"github.com/pkg/errors"
    17  	"golang.org/x/sync/errgroup"
    18  
    19  	"github.com/mattermost/mattermost-server/v5/einterfaces"
    20  	"github.com/mattermost/mattermost-server/v5/model"
    21  	"github.com/mattermost/mattermost-server/v5/store"
    22  )
    23  
    24  const (
    25  	MaxGroupChannelsForProfiles = 50
    26  )
    27  
    28  var (
    29  	UserSearchTypeNames_NO_FULL_NAME = []string{"Username", "Nickname"}
    30  	UserSearchTypeNames              = []string{"Username", "FirstName", "LastName", "Nickname"}
    31  	UserSearchTypeAll_NO_FULL_NAME   = []string{"Username", "Nickname", "Email"}
    32  	UserSearchTypeAll                = []string{"Username", "FirstName", "LastName", "Nickname", "Email"}
    33  )
    34  
    35  type SqlUserStore struct {
    36  	*SqlStore
    37  	metrics einterfaces.MetricsInterface
    38  
    39  	// usersQuery is a starting point for all queries that return one or more Users.
    40  	usersQuery sq.SelectBuilder
    41  }
    42  
    43  func (us *SqlUserStore) ClearCaches() {}
    44  
    45  func (us SqlUserStore) InvalidateProfileCacheForUser(userId string) {}
    46  
    47  func newSqlUserStore(sqlStore *SqlStore, metrics einterfaces.MetricsInterface) store.UserStore {
    48  	us := &SqlUserStore{
    49  		SqlStore: sqlStore,
    50  		metrics:  metrics,
    51  	}
    52  
    53  	// note: we are providing field names explicitly here to maintain order of columns (needed when using raw queries)
    54  	us.usersQuery = us.getQueryBuilder().
    55  		Select("u.Id", "u.CreateAt", "u.UpdateAt", "u.DeleteAt", "u.Username", "u.Password", "u.AuthData", "u.AuthService", "u.Email", "u.EmailVerified", "u.Nickname", "u.FirstName", "u.LastName", "u.Position", "u.Roles", "u.AllowMarketing", "u.Props", "u.NotifyProps", "u.LastPasswordUpdate", "u.LastPictureUpdate", "u.FailedAttempts", "u.Locale", "u.Timezone", "u.MfaActive", "u.MfaSecret",
    56  			"b.UserId IS NOT NULL AS IsBot", "COALESCE(b.Description, '') AS BotDescription", "COALESCE(b.LastIconUpdate, 0) AS BotLastIconUpdate").
    57  		From("Users u").
    58  		LeftJoin("Bots b ON ( b.UserId = u.Id )")
    59  
    60  	for _, db := range sqlStore.GetAllConns() {
    61  		table := db.AddTableWithName(model.User{}, "Users").SetKeys(false, "Id")
    62  		table.ColMap("Id").SetMaxSize(26)
    63  		table.ColMap("Username").SetMaxSize(64).SetUnique(true)
    64  		table.ColMap("Password").SetMaxSize(128)
    65  		table.ColMap("AuthData").SetMaxSize(128).SetUnique(true)
    66  		table.ColMap("AuthService").SetMaxSize(32)
    67  		table.ColMap("Email").SetMaxSize(128).SetUnique(true)
    68  		table.ColMap("Nickname").SetMaxSize(64)
    69  		table.ColMap("FirstName").SetMaxSize(64)
    70  		table.ColMap("LastName").SetMaxSize(64)
    71  		table.ColMap("Roles").SetMaxSize(256)
    72  		table.ColMap("Props").SetMaxSize(4000)
    73  		table.ColMap("NotifyProps").SetMaxSize(2000)
    74  		table.ColMap("Locale").SetMaxSize(5)
    75  		table.ColMap("MfaSecret").SetMaxSize(128)
    76  		table.ColMap("Position").SetMaxSize(128)
    77  		table.ColMap("Timezone").SetMaxSize(256)
    78  	}
    79  
    80  	return us
    81  }
    82  
    83  func (us SqlUserStore) createIndexesIfNotExists() {
    84  	us.CreateIndexIfNotExists("idx_users_email", "Users", "Email")
    85  	us.CreateIndexIfNotExists("idx_users_update_at", "Users", "UpdateAt")
    86  	us.CreateIndexIfNotExists("idx_users_create_at", "Users", "CreateAt")
    87  	us.CreateIndexIfNotExists("idx_users_delete_at", "Users", "DeleteAt")
    88  
    89  	if us.DriverName() == model.DATABASE_DRIVER_POSTGRES {
    90  		us.CreateIndexIfNotExists("idx_users_email_lower_textpattern", "Users", "lower(Email) text_pattern_ops")
    91  		us.CreateIndexIfNotExists("idx_users_username_lower_textpattern", "Users", "lower(Username) text_pattern_ops")
    92  		us.CreateIndexIfNotExists("idx_users_nickname_lower_textpattern", "Users", "lower(Nickname) text_pattern_ops")
    93  		us.CreateIndexIfNotExists("idx_users_firstname_lower_textpattern", "Users", "lower(FirstName) text_pattern_ops")
    94  		us.CreateIndexIfNotExists("idx_users_lastname_lower_textpattern", "Users", "lower(LastName) text_pattern_ops")
    95  	}
    96  
    97  	us.CreateFullTextIndexIfNotExists("idx_users_all_txt", "Users", strings.Join(UserSearchTypeAll, ", "))
    98  	us.CreateFullTextIndexIfNotExists("idx_users_all_no_full_name_txt", "Users", strings.Join(UserSearchTypeAll_NO_FULL_NAME, ", "))
    99  	us.CreateFullTextIndexIfNotExists("idx_users_names_txt", "Users", strings.Join(UserSearchTypeNames, ", "))
   100  	us.CreateFullTextIndexIfNotExists("idx_users_names_no_full_name_txt", "Users", strings.Join(UserSearchTypeNames_NO_FULL_NAME, ", "))
   101  }
   102  
   103  func (us SqlUserStore) Save(user *model.User) (*model.User, error) {
   104  	if user.Id != "" {
   105  		return nil, store.NewErrInvalidInput("User", "id", user.Id)
   106  	}
   107  
   108  	user.PreSave()
   109  	if err := user.IsValid(); err != nil {
   110  		return nil, err
   111  	}
   112  
   113  	if err := us.GetMaster().Insert(user); err != nil {
   114  		if IsUniqueConstraintError(err, []string{"Email", "users_email_key", "idx_users_email_unique"}) {
   115  			return nil, store.NewErrInvalidInput("User", "email", user.Email)
   116  		}
   117  		if IsUniqueConstraintError(err, []string{"Username", "users_username_key", "idx_users_username_unique"}) {
   118  			return nil, store.NewErrInvalidInput("User", "username", user.Username)
   119  		}
   120  		return nil, errors.Wrapf(err, "failed to save User with userId=%s", user.Id)
   121  	}
   122  
   123  	return user, nil
   124  }
   125  
   126  func (us SqlUserStore) DeactivateGuests() ([]string, error) {
   127  	curTime := model.GetMillis()
   128  	updateQuery := us.getQueryBuilder().Update("Users").
   129  		Set("UpdateAt", curTime).
   130  		Set("DeleteAt", curTime).
   131  		Where(sq.Eq{"Roles": "system_guest"}).
   132  		Where(sq.Eq{"DeleteAt": 0})
   133  
   134  	queryString, args, err := updateQuery.ToSql()
   135  	if err != nil {
   136  		return nil, errors.Wrap(err, "deactivate_guests_tosql")
   137  	}
   138  
   139  	_, err = us.GetMaster().Exec(queryString, args...)
   140  	if err != nil {
   141  		return nil, errors.Wrap(err, "failed to update Users with roles=system_guest")
   142  	}
   143  
   144  	selectQuery := us.getQueryBuilder().Select("Id").From("Users").Where(sq.Eq{"DeleteAt": curTime})
   145  
   146  	queryString, args, err = selectQuery.ToSql()
   147  	if err != nil {
   148  		return nil, errors.Wrap(err, "deactivate_guests_tosql")
   149  	}
   150  
   151  	userIds := []string{}
   152  	_, err = us.GetMaster().Select(&userIds, queryString, args...)
   153  	if err != nil {
   154  		return nil, errors.Wrap(err, "failed to find Users")
   155  	}
   156  
   157  	return userIds, nil
   158  }
   159  
   160  func (us SqlUserStore) Update(user *model.User, trustedUpdateData bool) (*model.UserUpdate, error) {
   161  	user.PreUpdate()
   162  
   163  	if err := user.IsValid(); err != nil {
   164  		return nil, err
   165  	}
   166  
   167  	oldUserResult, err := us.GetMaster().Get(model.User{}, user.Id)
   168  	if err != nil {
   169  		return nil, errors.Wrapf(err, "failed to get User with userId=%s", user.Id)
   170  	}
   171  
   172  	if oldUserResult == nil {
   173  		return nil, store.NewErrInvalidInput("User", "id", user.Id)
   174  	}
   175  
   176  	oldUser := oldUserResult.(*model.User)
   177  	user.CreateAt = oldUser.CreateAt
   178  	user.AuthData = oldUser.AuthData
   179  	user.AuthService = oldUser.AuthService
   180  	user.Password = oldUser.Password
   181  	user.LastPasswordUpdate = oldUser.LastPasswordUpdate
   182  	user.LastPictureUpdate = oldUser.LastPictureUpdate
   183  	user.EmailVerified = oldUser.EmailVerified
   184  	user.FailedAttempts = oldUser.FailedAttempts
   185  	user.MfaSecret = oldUser.MfaSecret
   186  	user.MfaActive = oldUser.MfaActive
   187  
   188  	if !trustedUpdateData {
   189  		user.Roles = oldUser.Roles
   190  		user.DeleteAt = oldUser.DeleteAt
   191  	}
   192  
   193  	if user.IsOAuthUser() {
   194  		if !trustedUpdateData {
   195  			user.Email = oldUser.Email
   196  		}
   197  	} else if user.IsLDAPUser() && !trustedUpdateData {
   198  		if user.Username != oldUser.Username || user.Email != oldUser.Email {
   199  			return nil, store.NewErrInvalidInput("User", "id", user.Id)
   200  		}
   201  	} else if user.Email != oldUser.Email {
   202  		user.EmailVerified = false
   203  	}
   204  
   205  	if user.Username != oldUser.Username {
   206  		user.UpdateMentionKeysFromUsername(oldUser.Username)
   207  	}
   208  
   209  	count, err := us.GetMaster().Update(user)
   210  	if err != nil {
   211  		if IsUniqueConstraintError(err, []string{"Email", "users_email_key", "idx_users_email_unique"}) {
   212  			return nil, store.NewErrInvalidInput("User", "id", user.Id)
   213  		}
   214  		if IsUniqueConstraintError(err, []string{"Username", "users_username_key", "idx_users_username_unique"}) {
   215  			return nil, store.NewErrInvalidInput("User", "id", user.Id)
   216  		}
   217  		return nil, errors.Wrapf(err, "failed to update User with userId=%s", user.Id)
   218  	}
   219  
   220  	if count > 1 {
   221  		return nil, fmt.Errorf("multiple users were update: userId=%s, count=%d", user.Id, count)
   222  	}
   223  
   224  	user.Sanitize(map[string]bool{})
   225  	oldUser.Sanitize(map[string]bool{})
   226  	return &model.UserUpdate{New: user, Old: oldUser}, nil
   227  }
   228  
   229  func (us SqlUserStore) UpdateLastPictureUpdate(userId string) error {
   230  	curTime := model.GetMillis()
   231  
   232  	if _, err := us.GetMaster().Exec("UPDATE Users SET LastPictureUpdate = :Time, UpdateAt = :Time WHERE Id = :UserId", map[string]interface{}{"Time": curTime, "UserId": userId}); err != nil {
   233  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
   234  	}
   235  
   236  	return nil
   237  }
   238  
   239  func (us SqlUserStore) ResetLastPictureUpdate(userId string) error {
   240  	curTime := model.GetMillis()
   241  
   242  	if _, err := us.GetMaster().Exec("UPDATE Users SET LastPictureUpdate = :PictureUpdateTime, UpdateAt = :UpdateTime WHERE Id = :UserId", map[string]interface{}{"PictureUpdateTime": 0, "UpdateTime": curTime, "UserId": userId}); err != nil {
   243  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
   244  	}
   245  
   246  	return nil
   247  }
   248  
   249  func (us SqlUserStore) UpdateUpdateAt(userId string) (int64, error) {
   250  	curTime := model.GetMillis()
   251  
   252  	if _, err := us.GetMaster().Exec("UPDATE Users SET UpdateAt = :Time WHERE Id = :UserId", map[string]interface{}{"Time": curTime, "UserId": userId}); err != nil {
   253  		return curTime, errors.Wrapf(err, "failed to update User with userId=%s", userId)
   254  	}
   255  
   256  	return curTime, nil
   257  }
   258  
   259  func (us SqlUserStore) UpdatePassword(userId, hashedPassword string) error {
   260  	updateAt := model.GetMillis()
   261  
   262  	if _, err := us.GetMaster().Exec("UPDATE Users SET Password = :Password, LastPasswordUpdate = :LastPasswordUpdate, UpdateAt = :UpdateAt, AuthData = NULL, AuthService = '', FailedAttempts = 0 WHERE Id = :UserId", map[string]interface{}{"Password": hashedPassword, "LastPasswordUpdate": updateAt, "UpdateAt": updateAt, "UserId": userId}); err != nil {
   263  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
   264  	}
   265  
   266  	return nil
   267  }
   268  
   269  func (us SqlUserStore) UpdateFailedPasswordAttempts(userId string, attempts int) error {
   270  	if _, err := us.GetMaster().Exec("UPDATE Users SET FailedAttempts = :FailedAttempts WHERE Id = :UserId", map[string]interface{}{"FailedAttempts": attempts, "UserId": userId}); err != nil {
   271  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
   272  	}
   273  
   274  	return nil
   275  }
   276  
   277  func (us SqlUserStore) UpdateAuthData(userId string, service string, authData *string, email string, resetMfa bool) (string, error) {
   278  	updateAt := model.GetMillis()
   279  
   280  	query := `
   281  			UPDATE
   282  			     Users
   283  			SET
   284  			     Password = '',
   285  			     LastPasswordUpdate = :LastPasswordUpdate,
   286  			     UpdateAt = :UpdateAt,
   287  			     FailedAttempts = 0,
   288  			     AuthService = :AuthService,
   289  			     AuthData = :AuthData`
   290  
   291  	if email != "" {
   292  		query += ", Email = lower(:Email)"
   293  	}
   294  
   295  	if resetMfa {
   296  		query += ", MfaActive = false, MfaSecret = ''"
   297  	}
   298  
   299  	query += " WHERE Id = :UserId"
   300  
   301  	if _, err := us.GetMaster().Exec(query, map[string]interface{}{"LastPasswordUpdate": updateAt, "UpdateAt": updateAt, "UserId": userId, "AuthService": service, "AuthData": authData, "Email": email}); err != nil {
   302  		if IsUniqueConstraintError(err, []string{"Email", "users_email_key", "idx_users_email_unique", "AuthData", "users_authdata_key"}) {
   303  			return "", store.NewErrInvalidInput("User", "id", userId)
   304  		}
   305  		return "", errors.Wrapf(err, "failed to update User with userId=%s", userId)
   306  	}
   307  	return userId, nil
   308  }
   309  
   310  func (us SqlUserStore) UpdateMfaSecret(userId, secret string) error {
   311  	updateAt := model.GetMillis()
   312  
   313  	if _, err := us.GetMaster().Exec("UPDATE Users SET MfaSecret = :Secret, UpdateAt = :UpdateAt WHERE Id = :UserId", map[string]interface{}{"Secret": secret, "UpdateAt": updateAt, "UserId": userId}); err != nil {
   314  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
   315  	}
   316  
   317  	return nil
   318  }
   319  
   320  func (us SqlUserStore) UpdateMfaActive(userId string, active bool) error {
   321  	updateAt := model.GetMillis()
   322  
   323  	if _, err := us.GetMaster().Exec("UPDATE Users SET MfaActive = :Active, UpdateAt = :UpdateAt WHERE Id = :UserId", map[string]interface{}{"Active": active, "UpdateAt": updateAt, "UserId": userId}); err != nil {
   324  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
   325  	}
   326  
   327  	return nil
   328  }
   329  
   330  // GetMany returns a list of users for the provided list of ids
   331  func (us SqlUserStore) GetMany(ctx context.Context, ids []string) ([]*model.User, error) {
   332  	query := us.usersQuery.Where(sq.Eq{"Id": ids})
   333  	queryString, args, err := query.ToSql()
   334  	if err != nil {
   335  		return nil, errors.Wrap(err, "users_get_many_tosql")
   336  	}
   337  
   338  	var users []*model.User
   339  	if _, err := us.SqlStore.DBFromContext(ctx).Select(&users, queryString, args...); err != nil {
   340  		return nil, errors.Wrap(err, "users_get_many_select")
   341  	}
   342  
   343  	return users, nil
   344  }
   345  
   346  func (us SqlUserStore) Get(ctx context.Context, id string) (*model.User, error) {
   347  	query := us.usersQuery.Where("Id = ?", id)
   348  	queryString, args, err := query.ToSql()
   349  	if err != nil {
   350  		return nil, errors.Wrap(err, "users_get_tosql")
   351  	}
   352  	row := us.SqlStore.DBFromContext(ctx).Db.QueryRow(queryString, args...)
   353  
   354  	var user model.User
   355  	var props, notifyProps, timezone []byte
   356  	err = row.Scan(&user.Id, &user.CreateAt, &user.UpdateAt, &user.DeleteAt, &user.Username,
   357  		&user.Password, &user.AuthData, &user.AuthService, &user.Email, &user.EmailVerified,
   358  		&user.Nickname, &user.FirstName, &user.LastName, &user.Position, &user.Roles,
   359  		&user.AllowMarketing, &props, &notifyProps, &user.LastPasswordUpdate, &user.LastPictureUpdate,
   360  		&user.FailedAttempts, &user.Locale, &timezone, &user.MfaActive, &user.MfaSecret,
   361  		&user.IsBot, &user.BotDescription, &user.BotLastIconUpdate)
   362  	if err != nil {
   363  		if err == sql.ErrNoRows {
   364  			return nil, store.NewErrNotFound("User", id)
   365  		}
   366  		return nil, errors.Wrapf(err, "failed to get User with userId=%s", id)
   367  
   368  	}
   369  	if err = json.Unmarshal(props, &user.Props); err != nil {
   370  		return nil, errors.Wrap(err, "failed to unmarshal user props")
   371  	}
   372  	if err = json.Unmarshal(notifyProps, &user.NotifyProps); err != nil {
   373  		return nil, errors.Wrap(err, "failed to unmarshal user notify props")
   374  	}
   375  	if err = json.Unmarshal(timezone, &user.Timezone); err != nil {
   376  		return nil, errors.Wrap(err, "failed to unmarshal user timezone")
   377  	}
   378  
   379  	return &user, nil
   380  }
   381  
   382  func (us SqlUserStore) GetAll() ([]*model.User, error) {
   383  	query := us.usersQuery.OrderBy("Username ASC")
   384  
   385  	queryString, args, err := query.ToSql()
   386  	if err != nil {
   387  		return nil, errors.Wrap(err, "get_all_users_tosql")
   388  	}
   389  
   390  	var data []*model.User
   391  	if _, err := us.GetReplica().Select(&data, queryString, args...); err != nil {
   392  		return nil, errors.Wrap(err, "failed to find Users")
   393  	}
   394  	return data, nil
   395  }
   396  
   397  func (us SqlUserStore) GetAllAfter(limit int, afterId string) ([]*model.User, error) {
   398  	query := us.usersQuery.
   399  		Where("Id > ?", afterId).
   400  		OrderBy("Id ASC").
   401  		Limit(uint64(limit))
   402  
   403  	queryString, args, err := query.ToSql()
   404  	if err != nil {
   405  		return nil, errors.Wrap(err, "get_all_after_tosql")
   406  	}
   407  
   408  	var users []*model.User
   409  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   410  		return nil, errors.Wrap(err, "failed to find Users")
   411  	}
   412  
   413  	return users, nil
   414  }
   415  
   416  func (us SqlUserStore) GetEtagForAllProfiles() string {
   417  	updateAt, err := us.GetReplica().SelectInt("SELECT UpdateAt FROM Users ORDER BY UpdateAt DESC LIMIT 1")
   418  	if err != nil {
   419  		return fmt.Sprintf("%v.%v", model.CurrentVersion, model.GetMillis())
   420  	}
   421  	return fmt.Sprintf("%v.%v", model.CurrentVersion, updateAt)
   422  }
   423  
   424  func (us SqlUserStore) GetAllProfiles(options *model.UserGetOptions) ([]*model.User, error) {
   425  	isPostgreSQL := us.DriverName() == model.DATABASE_DRIVER_POSTGRES
   426  	query := us.usersQuery.
   427  		OrderBy("u.Username ASC").
   428  		Offset(uint64(options.Page * options.PerPage)).Limit(uint64(options.PerPage))
   429  
   430  	query = applyViewRestrictionsFilter(query, options.ViewRestrictions, true)
   431  
   432  	query = applyRoleFilter(query, options.Role, isPostgreSQL)
   433  	query = applyMultiRoleFilters(query, options.Roles, []string{}, []string{}, isPostgreSQL)
   434  
   435  	if options.Inactive {
   436  		query = query.Where("u.DeleteAt != 0")
   437  	} else if options.Active {
   438  		query = query.Where("u.DeleteAt = 0")
   439  	}
   440  
   441  	queryString, args, err := query.ToSql()
   442  	if err != nil {
   443  		return nil, errors.Wrap(err, "get_all_profiles_tosql")
   444  	}
   445  
   446  	var users []*model.User
   447  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   448  		return nil, errors.Wrap(err, "failed to get User profiles")
   449  	}
   450  
   451  	for _, u := range users {
   452  		u.Sanitize(map[string]bool{})
   453  	}
   454  
   455  	return users, nil
   456  }
   457  
   458  func applyRoleFilter(query sq.SelectBuilder, role string, isPostgreSQL bool) sq.SelectBuilder {
   459  	if role == "" {
   460  		return query
   461  	}
   462  
   463  	if isPostgreSQL {
   464  		roleParam := fmt.Sprintf("%%%s%%", sanitizeSearchTerm(role, "\\"))
   465  		return query.Where("u.Roles LIKE LOWER(?)", roleParam)
   466  	}
   467  
   468  	roleParam := fmt.Sprintf("%%%s%%", sanitizeSearchTerm(role, "*"))
   469  
   470  	return query.Where("u.Roles LIKE ? ESCAPE '*'", roleParam)
   471  }
   472  
   473  func applyMultiRoleFilters(query sq.SelectBuilder, systemRoles []string, teamRoles []string, channelRoles []string, isPostgreSQL bool) sq.SelectBuilder {
   474  	sqOr := sq.Or{}
   475  
   476  	if len(systemRoles) > 0 && systemRoles[0] != "" {
   477  		for _, role := range systemRoles {
   478  			queryRole := wildcardSearchTerm(role)
   479  			switch role {
   480  			case model.SYSTEM_USER_ROLE_ID:
   481  				// If querying for a `system_user` ensure that the user is only a system_user.
   482  				sqOr = append(sqOr, sq.Eq{"u.Roles": role})
   483  			case model.SYSTEM_GUEST_ROLE_ID, model.SYSTEM_ADMIN_ROLE_ID, model.SYSTEM_USER_MANAGER_ROLE_ID, model.SYSTEM_READ_ONLY_ADMIN_ROLE_ID, model.SYSTEM_MANAGER_ROLE_ID:
   484  				// If querying for any other roles search using a wildcard.
   485  				if isPostgreSQL {
   486  					sqOr = append(sqOr, sq.ILike{"u.Roles": queryRole})
   487  				} else {
   488  					sqOr = append(sqOr, sq.Like{"u.Roles": queryRole})
   489  				}
   490  			}
   491  
   492  		}
   493  	}
   494  
   495  	if len(channelRoles) > 0 && channelRoles[0] != "" {
   496  		for _, channelRole := range channelRoles {
   497  			switch channelRole {
   498  			case model.CHANNEL_ADMIN_ROLE_ID:
   499  				if isPostgreSQL {
   500  					sqOr = append(sqOr, sq.And{sq.Eq{"cm.SchemeAdmin": true}, sq.NotILike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   501  				} else {
   502  					sqOr = append(sqOr, sq.And{sq.Eq{"cm.SchemeAdmin": true}, sq.NotLike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   503  				}
   504  			case model.CHANNEL_USER_ROLE_ID:
   505  				if isPostgreSQL {
   506  					sqOr = append(sqOr, sq.And{sq.Eq{"cm.SchemeUser": true}, sq.Eq{"cm.SchemeAdmin": false}, sq.NotILike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   507  				} else {
   508  					sqOr = append(sqOr, sq.And{sq.Eq{"cm.SchemeUser": true}, sq.Eq{"cm.SchemeAdmin": false}, sq.NotLike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   509  				}
   510  			case model.CHANNEL_GUEST_ROLE_ID:
   511  				sqOr = append(sqOr, sq.Eq{"cm.SchemeGuest": true})
   512  			}
   513  		}
   514  	}
   515  
   516  	if len(teamRoles) > 0 && teamRoles[0] != "" {
   517  		for _, teamRole := range teamRoles {
   518  			switch teamRole {
   519  			case model.TEAM_ADMIN_ROLE_ID:
   520  				if isPostgreSQL {
   521  					sqOr = append(sqOr, sq.And{sq.Eq{"tm.SchemeAdmin": true}, sq.NotILike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   522  				} else {
   523  					sqOr = append(sqOr, sq.And{sq.Eq{"tm.SchemeAdmin": true}, sq.NotLike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   524  				}
   525  			case model.TEAM_USER_ROLE_ID:
   526  				if isPostgreSQL {
   527  					sqOr = append(sqOr, sq.And{sq.Eq{"tm.SchemeUser": true}, sq.Eq{"tm.SchemeAdmin": false}, sq.NotILike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   528  				} else {
   529  					sqOr = append(sqOr, sq.And{sq.Eq{"tm.SchemeUser": true}, sq.Eq{"tm.SchemeAdmin": false}, sq.NotLike{"u.Roles": wildcardSearchTerm(model.SYSTEM_ADMIN_ROLE_ID)}})
   530  				}
   531  			case model.TEAM_GUEST_ROLE_ID:
   532  				sqOr = append(sqOr, sq.Eq{"tm.SchemeGuest": true})
   533  			}
   534  		}
   535  	}
   536  
   537  	if len(sqOr) > 0 {
   538  		return query.Where(sqOr)
   539  	}
   540  	return query
   541  }
   542  
   543  func applyChannelGroupConstrainedFilter(query sq.SelectBuilder, channelId string) sq.SelectBuilder {
   544  	if channelId == "" {
   545  		return query
   546  	}
   547  
   548  	return query.
   549  		Where(`u.Id IN (
   550  				SELECT
   551  					GroupMembers.UserId
   552  				FROM
   553  					Channels
   554  					JOIN GroupChannels ON GroupChannels.ChannelId = Channels.Id
   555  					JOIN UserGroups ON UserGroups.Id = GroupChannels.GroupId
   556  					JOIN GroupMembers ON GroupMembers.GroupId = UserGroups.Id
   557  				WHERE
   558  					Channels.Id = ?
   559  					AND GroupChannels.DeleteAt = 0
   560  					AND UserGroups.DeleteAt = 0
   561  					AND GroupMembers.DeleteAt = 0
   562  				GROUP BY
   563  					GroupMembers.UserId
   564  			)`, channelId)
   565  }
   566  
   567  func applyTeamGroupConstrainedFilter(query sq.SelectBuilder, teamId string) sq.SelectBuilder {
   568  	if teamId == "" {
   569  		return query
   570  	}
   571  
   572  	return query.
   573  		Where(`u.Id IN (
   574  				SELECT
   575  					GroupMembers.UserId
   576  				FROM
   577  					Teams
   578  					JOIN GroupTeams ON GroupTeams.TeamId = Teams.Id
   579  					JOIN UserGroups ON UserGroups.Id = GroupTeams.GroupId
   580  					JOIN GroupMembers ON GroupMembers.GroupId = UserGroups.Id
   581  				WHERE
   582  					Teams.Id = ?
   583  					AND GroupTeams.DeleteAt = 0
   584  					AND UserGroups.DeleteAt = 0
   585  					AND GroupMembers.DeleteAt = 0
   586  				GROUP BY
   587  					GroupMembers.UserId
   588  			)`, teamId)
   589  }
   590  
   591  func (us SqlUserStore) GetEtagForProfiles(teamId string) string {
   592  	updateAt, err := us.GetReplica().SelectInt("SELECT UpdateAt FROM Users, TeamMembers WHERE TeamMembers.TeamId = :TeamId AND Users.Id = TeamMembers.UserId ORDER BY UpdateAt DESC LIMIT 1", map[string]interface{}{"TeamId": teamId})
   593  	if err != nil {
   594  		return fmt.Sprintf("%v.%v", model.CurrentVersion, model.GetMillis())
   595  	}
   596  	return fmt.Sprintf("%v.%v", model.CurrentVersion, updateAt)
   597  }
   598  
   599  func (us SqlUserStore) GetProfiles(options *model.UserGetOptions) ([]*model.User, error) {
   600  	isPostgreSQL := us.DriverName() == model.DATABASE_DRIVER_POSTGRES
   601  	query := us.usersQuery.
   602  		Join("TeamMembers tm ON ( tm.UserId = u.Id AND tm.DeleteAt = 0 )").
   603  		Where("tm.TeamId = ?", options.InTeamId).
   604  		OrderBy("u.Username ASC").
   605  		Offset(uint64(options.Page * options.PerPage)).Limit(uint64(options.PerPage))
   606  
   607  	query = applyViewRestrictionsFilter(query, options.ViewRestrictions, true)
   608  
   609  	query = applyRoleFilter(query, options.Role, isPostgreSQL)
   610  	query = applyMultiRoleFilters(query, options.Roles, options.TeamRoles, options.ChannelRoles, isPostgreSQL)
   611  
   612  	if options.Inactive {
   613  		query = query.Where("u.DeleteAt != 0")
   614  	} else if options.Active {
   615  		query = query.Where("u.DeleteAt = 0")
   616  	}
   617  
   618  	queryString, args, err := query.ToSql()
   619  	if err != nil {
   620  		return nil, errors.Wrap(err, "get_etag_for_profiles_tosql")
   621  	}
   622  
   623  	var users []*model.User
   624  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   625  		return nil, errors.Wrap(err, "failed to find Users")
   626  	}
   627  
   628  	for _, u := range users {
   629  		u.Sanitize(map[string]bool{})
   630  	}
   631  
   632  	return users, nil
   633  }
   634  
   635  func (us SqlUserStore) InvalidateProfilesInChannelCacheByUser(userId string) {}
   636  
   637  func (us SqlUserStore) InvalidateProfilesInChannelCache(channelId string) {}
   638  
   639  func (us SqlUserStore) GetProfilesInChannel(options *model.UserGetOptions) ([]*model.User, error) {
   640  	query := us.usersQuery.
   641  		Join("ChannelMembers cm ON ( cm.UserId = u.Id )").
   642  		Where("cm.ChannelId = ?", options.InChannelId).
   643  		OrderBy("u.Username ASC").
   644  		Offset(uint64(options.Page * options.PerPage)).Limit(uint64(options.PerPage))
   645  
   646  	if options.Inactive {
   647  		query = query.Where("u.DeleteAt != 0")
   648  	} else if options.Active {
   649  		query = query.Where("u.DeleteAt = 0")
   650  	}
   651  
   652  	queryString, args, err := query.ToSql()
   653  	if err != nil {
   654  		return nil, errors.Wrap(err, "get_profiles_in_channel_tosql")
   655  	}
   656  
   657  	var users []*model.User
   658  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   659  		return nil, errors.Wrap(err, "failed to find Users")
   660  	}
   661  
   662  	for _, u := range users {
   663  		u.Sanitize(map[string]bool{})
   664  	}
   665  
   666  	return users, nil
   667  }
   668  
   669  func (us SqlUserStore) GetProfilesInChannelByStatus(options *model.UserGetOptions) ([]*model.User, error) {
   670  	query := us.usersQuery.
   671  		Join("ChannelMembers cm ON ( cm.UserId = u.Id )").
   672  		LeftJoin("Status s ON ( s.UserId = u.Id )").
   673  		Where("cm.ChannelId = ?", options.InChannelId).
   674  		OrderBy(`
   675  			CASE s.Status
   676  				WHEN 'online' THEN 1
   677  				WHEN 'away' THEN 2
   678  				WHEN 'dnd' THEN 3
   679  				ELSE 4
   680  			END
   681  			`).
   682  		OrderBy("u.Username ASC").
   683  		Offset(uint64(options.Page * options.PerPage)).Limit(uint64(options.PerPage))
   684  
   685  	if options.Inactive && !options.Active {
   686  		query = query.Where("u.DeleteAt != 0")
   687  	} else if options.Active && !options.Inactive {
   688  		query = query.Where("u.DeleteAt = 0")
   689  	}
   690  
   691  	queryString, args, err := query.ToSql()
   692  	if err != nil {
   693  		return nil, errors.Wrap(err, "get_profiles_in_channel_by_status_tosql")
   694  	}
   695  
   696  	var users []*model.User
   697  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   698  		return nil, errors.Wrap(err, "failed to find Users")
   699  	}
   700  
   701  	for _, u := range users {
   702  		u.Sanitize(map[string]bool{})
   703  	}
   704  
   705  	return users, nil
   706  }
   707  
   708  func (us SqlUserStore) GetAllProfilesInChannel(ctx context.Context, channelID string, allowFromCache bool) (map[string]*model.User, error) {
   709  	query := us.usersQuery.
   710  		Join("ChannelMembers cm ON ( cm.UserId = u.Id )").
   711  		Where("cm.ChannelId = ?", channelID).
   712  		Where("u.DeleteAt = 0").
   713  		OrderBy("u.Username ASC")
   714  
   715  	queryString, args, err := query.ToSql()
   716  	if err != nil {
   717  		return nil, errors.Wrap(err, "get_all_profiles_in_channel_tosql")
   718  	}
   719  
   720  	var users []*model.User
   721  	rows, err := us.SqlStore.DBFromContext(ctx).Db.Query(queryString, args...)
   722  	if err != nil {
   723  		return nil, errors.Wrap(err, "failed to find Users")
   724  	}
   725  
   726  	defer rows.Close()
   727  	for rows.Next() {
   728  		var user model.User
   729  		var props, notifyProps, timezone []byte
   730  		if err = rows.Scan(&user.Id, &user.CreateAt, &user.UpdateAt, &user.DeleteAt, &user.Username, &user.Password, &user.AuthData, &user.AuthService, &user.Email, &user.EmailVerified, &user.Nickname, &user.FirstName, &user.LastName, &user.Position, &user.Roles, &user.AllowMarketing, &props, &notifyProps, &user.LastPasswordUpdate, &user.LastPictureUpdate, &user.FailedAttempts, &user.Locale, &timezone, &user.MfaActive, &user.MfaSecret, &user.IsBot, &user.BotDescription, &user.BotLastIconUpdate); err != nil {
   731  			return nil, errors.Wrap(err, "failed to scan values from rows into User entity")
   732  		}
   733  		if err = json.Unmarshal(props, &user.Props); err != nil {
   734  			return nil, errors.Wrap(err, "failed to unmarshal user props")
   735  		}
   736  		if err = json.Unmarshal(notifyProps, &user.NotifyProps); err != nil {
   737  			return nil, errors.Wrap(err, "failed to unmarshal user notify props")
   738  		}
   739  		if err = json.Unmarshal(timezone, &user.Timezone); err != nil {
   740  			return nil, errors.Wrap(err, "failed to unmarshal user timezone")
   741  		}
   742  		users = append(users, &user)
   743  	}
   744  	err = rows.Err()
   745  	if err != nil {
   746  		return nil, errors.Wrap(err, "error while iterating over rows")
   747  	}
   748  
   749  	userMap := make(map[string]*model.User)
   750  
   751  	for _, u := range users {
   752  		u.Sanitize(map[string]bool{})
   753  		userMap[u.Id] = u
   754  	}
   755  
   756  	return userMap, nil
   757  }
   758  
   759  func (us SqlUserStore) GetProfilesNotInChannel(teamId string, channelId string, groupConstrained bool, offset int, limit int, viewRestrictions *model.ViewUsersRestrictions) ([]*model.User, error) {
   760  	query := us.usersQuery.
   761  		Join("TeamMembers tm ON ( tm.UserId = u.Id AND tm.DeleteAt = 0 AND tm.TeamId = ? )", teamId).
   762  		LeftJoin("ChannelMembers cm ON ( cm.UserId = u.Id AND cm.ChannelId = ? )", channelId).
   763  		Where("cm.UserId IS NULL").
   764  		OrderBy("u.Username ASC").
   765  		Offset(uint64(offset)).Limit(uint64(limit))
   766  
   767  	query = applyViewRestrictionsFilter(query, viewRestrictions, true)
   768  
   769  	if groupConstrained {
   770  		query = applyChannelGroupConstrainedFilter(query, channelId)
   771  	}
   772  
   773  	queryString, args, err := query.ToSql()
   774  	if err != nil {
   775  		return nil, errors.Wrap(err, "get_profiles_not_in_channel_tosql")
   776  	}
   777  
   778  	var users []*model.User
   779  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   780  		return nil, errors.Wrap(err, "failed to find Users")
   781  	}
   782  
   783  	for _, u := range users {
   784  		u.Sanitize(map[string]bool{})
   785  	}
   786  
   787  	return users, nil
   788  }
   789  
   790  func (us SqlUserStore) GetProfilesWithoutTeam(options *model.UserGetOptions) ([]*model.User, error) {
   791  	isPostgreSQL := us.DriverName() == model.DATABASE_DRIVER_POSTGRES
   792  	query := us.usersQuery.
   793  		Where(`(
   794  			SELECT
   795  				COUNT(0)
   796  			FROM
   797  				TeamMembers
   798  			WHERE
   799  				TeamMembers.UserId = u.Id
   800  				AND TeamMembers.DeleteAt = 0
   801  		) = 0`).
   802  		OrderBy("u.Username ASC").
   803  		Offset(uint64(options.Page * options.PerPage)).Limit(uint64(options.PerPage))
   804  
   805  	query = applyViewRestrictionsFilter(query, options.ViewRestrictions, true)
   806  
   807  	query = applyRoleFilter(query, options.Role, isPostgreSQL)
   808  
   809  	if options.Inactive {
   810  		query = query.Where("u.DeleteAt != 0")
   811  	} else if options.Active {
   812  		query = query.Where("u.DeleteAt = 0")
   813  	}
   814  
   815  	queryString, args, err := query.ToSql()
   816  	if err != nil {
   817  		return nil, errors.Wrap(err, "get_profiles_without_team_tosql")
   818  	}
   819  
   820  	var users []*model.User
   821  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   822  		return nil, errors.Wrap(err, "failed to find Users")
   823  	}
   824  
   825  	for _, u := range users {
   826  		u.Sanitize(map[string]bool{})
   827  	}
   828  
   829  	return users, nil
   830  }
   831  
   832  func (us SqlUserStore) GetProfilesByUsernames(usernames []string, viewRestrictions *model.ViewUsersRestrictions) ([]*model.User, error) {
   833  	query := us.usersQuery
   834  
   835  	query = applyViewRestrictionsFilter(query, viewRestrictions, true)
   836  
   837  	query = query.
   838  		Where(map[string]interface{}{
   839  			"Username": usernames,
   840  		}).
   841  		OrderBy("u.Username ASC")
   842  
   843  	queryString, args, err := query.ToSql()
   844  	if err != nil {
   845  		return nil, errors.Wrap(err, "get_profiles_by_usernames")
   846  	}
   847  
   848  	var users []*model.User
   849  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   850  		return nil, errors.Wrap(err, "failed to find Users")
   851  	}
   852  
   853  	return users, nil
   854  }
   855  
   856  type UserWithLastActivityAt struct {
   857  	model.User
   858  	LastActivityAt int64
   859  }
   860  
   861  func (us SqlUserStore) GetRecentlyActiveUsersForTeam(teamId string, offset, limit int, viewRestrictions *model.ViewUsersRestrictions) ([]*model.User, error) {
   862  	query := us.usersQuery.
   863  		Column("s.LastActivityAt").
   864  		Join("TeamMembers tm ON (tm.UserId = u.Id AND tm.TeamId = ?)", teamId).
   865  		Join("Status s ON (s.UserId = u.Id)").
   866  		OrderBy("s.LastActivityAt DESC").
   867  		OrderBy("u.Username ASC").
   868  		Offset(uint64(offset)).Limit(uint64(limit))
   869  
   870  	query = applyViewRestrictionsFilter(query, viewRestrictions, true)
   871  
   872  	queryString, args, err := query.ToSql()
   873  	if err != nil {
   874  		return nil, errors.Wrap(err, "get_recently_active_users_for_team_tosql")
   875  	}
   876  
   877  	var users []*UserWithLastActivityAt
   878  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   879  		return nil, errors.Wrap(err, "failed to find Users")
   880  	}
   881  
   882  	userList := []*model.User{}
   883  
   884  	for _, userWithLastActivityAt := range users {
   885  		u := userWithLastActivityAt.User
   886  		u.Sanitize(map[string]bool{})
   887  		u.LastActivityAt = userWithLastActivityAt.LastActivityAt
   888  		userList = append(userList, &u)
   889  	}
   890  
   891  	return userList, nil
   892  }
   893  
   894  func (us SqlUserStore) GetNewUsersForTeam(teamId string, offset, limit int, viewRestrictions *model.ViewUsersRestrictions) ([]*model.User, error) {
   895  	query := us.usersQuery.
   896  		Join("TeamMembers tm ON (tm.UserId = u.Id AND tm.TeamId = ?)", teamId).
   897  		OrderBy("u.CreateAt DESC").
   898  		OrderBy("u.Username ASC").
   899  		Offset(uint64(offset)).Limit(uint64(limit))
   900  
   901  	query = applyViewRestrictionsFilter(query, viewRestrictions, true)
   902  
   903  	queryString, args, err := query.ToSql()
   904  	if err != nil {
   905  		return nil, errors.Wrap(err, "get_new_users_for_team_tosql")
   906  	}
   907  
   908  	var users []*model.User
   909  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
   910  		return nil, errors.Wrap(err, "failed to find Users")
   911  	}
   912  
   913  	for _, u := range users {
   914  		u.Sanitize(map[string]bool{})
   915  	}
   916  
   917  	return users, nil
   918  }
   919  
   920  func (us SqlUserStore) GetProfileByIds(ctx context.Context, userIds []string, options *store.UserGetByIdsOpts, allowFromCache bool) ([]*model.User, error) {
   921  	if options == nil {
   922  		options = &store.UserGetByIdsOpts{}
   923  	}
   924  
   925  	users := []*model.User{}
   926  	query := us.usersQuery.
   927  		Where(map[string]interface{}{
   928  			"u.Id": userIds,
   929  		}).
   930  		OrderBy("u.Username ASC")
   931  
   932  	if options.Since > 0 {
   933  		query = query.Where(sq.Gt(map[string]interface{}{
   934  			"u.UpdateAt": options.Since,
   935  		}))
   936  	}
   937  
   938  	query = applyViewRestrictionsFilter(query, options.ViewRestrictions, true)
   939  
   940  	queryString, args, err := query.ToSql()
   941  	if err != nil {
   942  		return nil, errors.Wrap(err, "get_profile_by_ids_tosql")
   943  	}
   944  
   945  	if _, err := us.SqlStore.DBFromContext(ctx).Select(&users, queryString, args...); err != nil {
   946  		return nil, errors.Wrap(err, "failed to find Users")
   947  	}
   948  
   949  	return users, nil
   950  }
   951  
   952  type UserWithChannel struct {
   953  	model.User
   954  	ChannelId string
   955  }
   956  
   957  func (us SqlUserStore) GetProfileByGroupChannelIdsForUser(userId string, channelIds []string) (map[string][]*model.User, error) {
   958  	if len(channelIds) > MaxGroupChannelsForProfiles {
   959  		channelIds = channelIds[0:MaxGroupChannelsForProfiles]
   960  	}
   961  
   962  	isMemberQuery := fmt.Sprintf(`
   963        EXISTS(
   964          SELECT
   965            1
   966          FROM
   967            ChannelMembers
   968          WHERE
   969            UserId = '%s'
   970          AND
   971            ChannelId = cm.ChannelId
   972          )`, userId)
   973  
   974  	query := us.getQueryBuilder().
   975  		Select("u.*, cm.ChannelId").
   976  		From("Users u").
   977  		Join("ChannelMembers cm ON u.Id = cm.UserId").
   978  		Join("Channels c ON cm.ChannelId = c.Id").
   979  		Where(sq.Eq{"c.Type": model.CHANNEL_GROUP, "cm.ChannelId": channelIds}).
   980  		Where(isMemberQuery).
   981  		Where(sq.NotEq{"u.Id": userId}).
   982  		OrderBy("u.Username ASC")
   983  
   984  	queryString, args, err := query.ToSql()
   985  	if err != nil {
   986  		return nil, errors.Wrap(err, "get_profiles_by_group_channel_ids_for_user_tosql")
   987  	}
   988  
   989  	usersWithChannel := []*UserWithChannel{}
   990  	if _, err := us.GetReplica().Select(&usersWithChannel, queryString, args...); err != nil {
   991  		return nil, errors.Wrap(err, "failed to find Users")
   992  	}
   993  
   994  	usersByChannelId := map[string][]*model.User{}
   995  	for _, user := range usersWithChannel {
   996  		if val, ok := usersByChannelId[user.ChannelId]; ok {
   997  			usersByChannelId[user.ChannelId] = append(val, &user.User)
   998  		} else {
   999  			usersByChannelId[user.ChannelId] = []*model.User{&user.User}
  1000  		}
  1001  	}
  1002  
  1003  	return usersByChannelId, nil
  1004  }
  1005  
  1006  func (us SqlUserStore) GetSystemAdminProfiles() (map[string]*model.User, error) {
  1007  	query := us.usersQuery.
  1008  		Where("Roles LIKE ?", "%system_admin%").
  1009  		OrderBy("u.Username ASC")
  1010  
  1011  	queryString, args, err := query.ToSql()
  1012  	if err != nil {
  1013  		return nil, errors.Wrap(err, "get_system_admin_profiles_tosql")
  1014  	}
  1015  
  1016  	var users []*model.User
  1017  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1018  		return nil, errors.Wrap(err, "failed to find Users")
  1019  	}
  1020  
  1021  	userMap := make(map[string]*model.User)
  1022  
  1023  	for _, u := range users {
  1024  		u.Sanitize(map[string]bool{})
  1025  		userMap[u.Id] = u
  1026  	}
  1027  
  1028  	return userMap, nil
  1029  }
  1030  
  1031  func (us SqlUserStore) GetByEmail(email string) (*model.User, error) {
  1032  	query := us.usersQuery.Where("Email = lower(?)", email)
  1033  
  1034  	queryString, args, err := query.ToSql()
  1035  	if err != nil {
  1036  		return nil, errors.Wrap(err, "get_by_email_tosql")
  1037  	}
  1038  
  1039  	user := model.User{}
  1040  	if err := us.GetReplica().SelectOne(&user, queryString, args...); err != nil {
  1041  		if err == sql.ErrNoRows {
  1042  			return nil, errors.Wrap(store.NewErrNotFound("User", fmt.Sprintf("email=%s", email)), "failed to find User")
  1043  		}
  1044  
  1045  		return nil, errors.Wrapf(err, "failed to get User with email=%s", email)
  1046  	}
  1047  
  1048  	return &user, nil
  1049  }
  1050  
  1051  func (us SqlUserStore) GetByAuth(authData *string, authService string) (*model.User, error) {
  1052  	if authData == nil || *authData == "" {
  1053  		return nil, store.NewErrInvalidInput("User", "<authData>", "empty or nil")
  1054  	}
  1055  
  1056  	query := us.usersQuery.
  1057  		Where("u.AuthData = ?", authData).
  1058  		Where("u.AuthService = ?", authService)
  1059  
  1060  	queryString, args, err := query.ToSql()
  1061  	if err != nil {
  1062  		return nil, errors.Wrap(err, "get_by_auth_tosql")
  1063  	}
  1064  
  1065  	user := model.User{}
  1066  	if err := us.GetReplica().SelectOne(&user, queryString, args...); err == sql.ErrNoRows {
  1067  		return nil, store.NewErrNotFound("User", fmt.Sprintf("authData=%s, authService=%s", *authData, authService))
  1068  	} else if err != nil {
  1069  		return nil, errors.Wrapf(err, "failed to find User with authData=%s and authService=%s", *authData, authService)
  1070  	}
  1071  	return &user, nil
  1072  }
  1073  
  1074  func (us SqlUserStore) GetAllUsingAuthService(authService string) ([]*model.User, error) {
  1075  	query := us.usersQuery.
  1076  		Where("u.AuthService = ?", authService).
  1077  		OrderBy("u.Username ASC")
  1078  
  1079  	queryString, args, err := query.ToSql()
  1080  	if err != nil {
  1081  		return nil, errors.Wrap(err, "get_all_using_auth_service_tosql")
  1082  	}
  1083  
  1084  	var users []*model.User
  1085  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1086  		return nil, errors.Wrapf(err, "failed to find Users with authService=%s", authService)
  1087  	}
  1088  
  1089  	return users, nil
  1090  }
  1091  
  1092  func (us SqlUserStore) GetAllNotInAuthService(authServices []string) ([]*model.User, error) {
  1093  	query := us.usersQuery.
  1094  		Where(sq.NotEq{"u.AuthService": authServices}).
  1095  		OrderBy("u.Username ASC")
  1096  
  1097  	queryString, args, err := query.ToSql()
  1098  	if err != nil {
  1099  		return nil, errors.Wrap(err, "get_all_not_in_auth_service_tosql")
  1100  	}
  1101  
  1102  	var users []*model.User
  1103  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1104  		return nil, errors.Wrapf(err, "failed to find Users with authServices in %v", authServices)
  1105  	}
  1106  
  1107  	return users, nil
  1108  }
  1109  
  1110  func (us SqlUserStore) GetByUsername(username string) (*model.User, error) {
  1111  	query := us.usersQuery.Where("u.Username = lower(?)", username)
  1112  
  1113  	queryString, args, err := query.ToSql()
  1114  	if err != nil {
  1115  		return nil, errors.Wrap(err, "get_by_username_tosql")
  1116  	}
  1117  
  1118  	var user *model.User
  1119  	if err := us.GetReplica().SelectOne(&user, queryString, args...); err != nil {
  1120  		if err == sql.ErrNoRows {
  1121  			return nil, errors.Wrap(store.NewErrNotFound("User", fmt.Sprintf("username=%s", username)), "failed to find User")
  1122  		}
  1123  
  1124  		return nil, errors.Wrapf(err, "failed to find User with username=%s", username)
  1125  	}
  1126  
  1127  	return user, nil
  1128  }
  1129  
  1130  func (us SqlUserStore) GetForLogin(loginId string, allowSignInWithUsername, allowSignInWithEmail bool) (*model.User, error) {
  1131  	query := us.usersQuery
  1132  	if allowSignInWithUsername && allowSignInWithEmail {
  1133  		query = query.Where("Username = lower(?) OR Email = lower(?)", loginId, loginId)
  1134  	} else if allowSignInWithUsername {
  1135  		query = query.Where("Username = lower(?)", loginId)
  1136  	} else if allowSignInWithEmail {
  1137  		query = query.Where("Email = lower(?)", loginId)
  1138  	} else {
  1139  		return nil, errors.New("sign in with username and email are disabled")
  1140  	}
  1141  
  1142  	queryString, args, err := query.ToSql()
  1143  	if err != nil {
  1144  		return nil, errors.Wrap(err, "get_for_login_tosql")
  1145  	}
  1146  
  1147  	users := []*model.User{}
  1148  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1149  		return nil, errors.Wrap(err, "failed to find Users")
  1150  	}
  1151  
  1152  	if len(users) == 0 {
  1153  		return nil, errors.New("user not found")
  1154  	}
  1155  
  1156  	if len(users) > 1 {
  1157  		return nil, errors.New("multiple users found")
  1158  	}
  1159  
  1160  	return users[0], nil
  1161  
  1162  }
  1163  
  1164  func (us SqlUserStore) VerifyEmail(userId, email string) (string, error) {
  1165  	curTime := model.GetMillis()
  1166  	if _, err := us.GetMaster().Exec("UPDATE Users SET Email = lower(:email), EmailVerified = true, UpdateAt = :Time WHERE Id = :UserId", map[string]interface{}{"email": email, "Time": curTime, "UserId": userId}); err != nil {
  1167  		return "", errors.Wrapf(err, "failed to update Users with userId=%s and email=%s", userId, email)
  1168  	}
  1169  
  1170  	return userId, nil
  1171  }
  1172  
  1173  func (us SqlUserStore) PermanentDelete(userId string) error {
  1174  	if _, err := us.GetMaster().Exec("DELETE FROM Users WHERE Id = :UserId", map[string]interface{}{"UserId": userId}); err != nil {
  1175  		return errors.Wrapf(err, "failed to delete User with userId=%s", userId)
  1176  	}
  1177  	return nil
  1178  }
  1179  
  1180  func (us SqlUserStore) Count(options model.UserCountOptions) (int64, error) {
  1181  	isPostgreSQL := us.DriverName() == model.DATABASE_DRIVER_POSTGRES
  1182  	query := us.getQueryBuilder().Select("COUNT(DISTINCT u.Id)").From("Users AS u")
  1183  
  1184  	if !options.IncludeDeleted {
  1185  		query = query.Where("u.DeleteAt = 0")
  1186  	}
  1187  
  1188  	if options.IncludeBotAccounts {
  1189  		if options.ExcludeRegularUsers {
  1190  			query = query.Join("Bots ON u.Id = Bots.UserId")
  1191  		}
  1192  	} else {
  1193  		query = query.LeftJoin("Bots ON u.Id = Bots.UserId").Where("Bots.UserId IS NULL")
  1194  		if options.ExcludeRegularUsers {
  1195  			// Currently this doesn't make sense because it will always return 0
  1196  			return int64(0), errors.New("query with IncludeBotAccounts=false and excludeRegularUsers=true always return 0")
  1197  		}
  1198  	}
  1199  
  1200  	if options.TeamId != "" {
  1201  		query = query.LeftJoin("TeamMembers AS tm ON u.Id = tm.UserId").Where("tm.TeamId = ? AND tm.DeleteAt = 0", options.TeamId)
  1202  	} else if options.ChannelId != "" {
  1203  		query = query.LeftJoin("ChannelMembers AS cm ON u.Id = cm.UserId").Where("cm.ChannelId = ?", options.ChannelId)
  1204  	}
  1205  	query = applyViewRestrictionsFilter(query, options.ViewRestrictions, false)
  1206  	query = applyMultiRoleFilters(query, options.Roles, options.TeamRoles, options.ChannelRoles, isPostgreSQL)
  1207  
  1208  	if isPostgreSQL {
  1209  		query = query.PlaceholderFormat(sq.Dollar)
  1210  	}
  1211  
  1212  	queryString, args, err := query.ToSql()
  1213  	if err != nil {
  1214  		return int64(0), errors.Wrap(err, "count_tosql")
  1215  	}
  1216  
  1217  	count, err := us.GetReplica().SelectInt(queryString, args...)
  1218  	if err != nil {
  1219  		return int64(0), errors.Wrap(err, "failed to count Users")
  1220  	}
  1221  	return count, nil
  1222  }
  1223  
  1224  func (us SqlUserStore) AnalyticsActiveCount(timePeriod int64, options model.UserCountOptions) (int64, error) {
  1225  
  1226  	time := model.GetMillis() - timePeriod
  1227  	query := us.getQueryBuilder().Select("COUNT(*)").From("Status AS s").Where("LastActivityAt > :Time", map[string]interface{}{"Time": time})
  1228  
  1229  	if !options.IncludeBotAccounts {
  1230  		query = query.LeftJoin("Bots ON s.UserId = Bots.UserId").Where("Bots.UserId IS NULL")
  1231  	}
  1232  
  1233  	if !options.IncludeDeleted {
  1234  		query = query.LeftJoin("Users ON s.UserId = Users.Id").Where("Users.DeleteAt = 0")
  1235  	}
  1236  
  1237  	queryStr, args, err := query.ToSql()
  1238  
  1239  	if err != nil {
  1240  		return 0, errors.Wrap(err, "analytics_active_count_tosql")
  1241  	}
  1242  
  1243  	v, err := us.GetReplica().SelectInt(queryStr, args...)
  1244  	if err != nil {
  1245  		return 0, errors.Wrap(err, "failed to count Users")
  1246  	}
  1247  	return v, nil
  1248  }
  1249  
  1250  func (us SqlUserStore) AnalyticsActiveCountForPeriod(startTime int64, endTime int64, options model.UserCountOptions) (int64, error) {
  1251  	query := us.getQueryBuilder().Select("COUNT(*)").From("Status AS s").Where("LastActivityAt > :StartTime AND LastActivityAt <= :EndTime", map[string]interface{}{"StartTime": startTime, "EndTime": endTime})
  1252  
  1253  	if !options.IncludeBotAccounts {
  1254  		query = query.LeftJoin("Bots ON s.UserId = Bots.UserId").Where("Bots.UserId IS NULL")
  1255  	}
  1256  
  1257  	if !options.IncludeDeleted {
  1258  		query = query.LeftJoin("Users ON s.UserId = Users.Id").Where("Users.DeleteAt = 0")
  1259  	}
  1260  
  1261  	queryStr, args, err := query.ToSql()
  1262  
  1263  	if err != nil {
  1264  		return 0, errors.Wrap(err, "Failed to build query.")
  1265  	}
  1266  
  1267  	v, err := us.GetReplica().SelectInt(queryStr, args...)
  1268  	if err != nil {
  1269  		return 0, errors.Wrap(err, "Unable to get the active users during the requested period.")
  1270  	}
  1271  	return v, nil
  1272  }
  1273  
  1274  func (us SqlUserStore) GetUnreadCount(userId string) (int64, error) {
  1275  	query := `
  1276  		SELECT SUM(CASE WHEN c.Type = 'D' THEN (c.TotalMsgCount - cm.MsgCount) ELSE cm.MentionCount END)
  1277  		FROM Channels c
  1278  		INNER JOIN ChannelMembers cm
  1279  			ON cm.ChannelId = c.Id
  1280  			AND cm.UserId = :UserId
  1281  			AND c.DeleteAt = 0
  1282  	`
  1283  	count, err := us.GetReplica().SelectInt(query, map[string]interface{}{"UserId": userId})
  1284  	if err != nil {
  1285  		return count, errors.Wrapf(err, "failed to count unread Channels for userId=%s", userId)
  1286  	}
  1287  
  1288  	return count, nil
  1289  }
  1290  
  1291  func (us SqlUserStore) GetUnreadCountForChannel(userId string, channelId string) (int64, error) {
  1292  	count, err := us.GetReplica().SelectInt("SELECT SUM(CASE WHEN c.Type = 'D' THEN (c.TotalMsgCount - cm.MsgCount) ELSE cm.MentionCount END) FROM Channels c INNER JOIN ChannelMembers cm ON c.Id = cm.ChannelId AND cm.ChannelId = :ChannelId AND cm.UserId = :UserId", map[string]interface{}{"ChannelId": channelId, "UserId": userId})
  1293  	if err != nil {
  1294  		return 0, errors.Wrapf(err, "failed to get unread count for channelId=%s and userId=%s", channelId, userId)
  1295  	}
  1296  	return count, nil
  1297  }
  1298  
  1299  func (us SqlUserStore) GetAnyUnreadPostCountForChannel(userId string, channelId string) (int64, error) {
  1300  	count, err := us.GetReplica().SelectInt("SELECT SUM(c.TotalMsgCount - cm.MsgCount) FROM Channels c INNER JOIN ChannelMembers cm ON c.Id = cm.ChannelId AND cm.ChannelId = :ChannelId AND cm.UserId = :UserId", map[string]interface{}{"ChannelId": channelId, "UserId": userId})
  1301  	if err != nil {
  1302  		return count, errors.Wrapf(err, "failed to get any unread count for channelId=%s and userId=%s", channelId, userId)
  1303  	}
  1304  	return count, nil
  1305  }
  1306  
  1307  func (us SqlUserStore) Search(teamId string, term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1308  	query := us.usersQuery.
  1309  		OrderBy("Username ASC").
  1310  		Limit(uint64(options.Limit))
  1311  
  1312  	if teamId != "" {
  1313  		query = query.Join("TeamMembers tm ON ( tm.UserId = u.Id AND tm.DeleteAt = 0 AND tm.TeamId = ? )", teamId)
  1314  	}
  1315  	return us.performSearch(query, term, options)
  1316  }
  1317  
  1318  func (us SqlUserStore) SearchWithoutTeam(term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1319  	query := us.usersQuery.
  1320  		Where(`(
  1321  				SELECT
  1322  					COUNT(0)
  1323  				FROM
  1324  					TeamMembers
  1325  				WHERE
  1326  					TeamMembers.UserId = u.Id
  1327  					AND TeamMembers.DeleteAt = 0
  1328  			) = 0`).
  1329  		OrderBy("u.Username ASC").
  1330  		Limit(uint64(options.Limit))
  1331  
  1332  	return us.performSearch(query, term, options)
  1333  }
  1334  
  1335  func (us SqlUserStore) SearchNotInTeam(notInTeamId string, term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1336  	query := us.usersQuery.
  1337  		LeftJoin("TeamMembers tm ON ( tm.UserId = u.Id AND tm.DeleteAt = 0 AND tm.TeamId = ? )", notInTeamId).
  1338  		Where("tm.UserId IS NULL").
  1339  		OrderBy("u.Username ASC").
  1340  		Limit(uint64(options.Limit))
  1341  
  1342  	if options.GroupConstrained {
  1343  		query = applyTeamGroupConstrainedFilter(query, notInTeamId)
  1344  	}
  1345  
  1346  	return us.performSearch(query, term, options)
  1347  }
  1348  
  1349  func (us SqlUserStore) SearchNotInChannel(teamId string, channelId string, term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1350  	query := us.usersQuery.
  1351  		LeftJoin("ChannelMembers cm ON ( cm.UserId = u.Id AND cm.ChannelId = ? )", channelId).
  1352  		Where("cm.UserId IS NULL").
  1353  		OrderBy("Username ASC").
  1354  		Limit(uint64(options.Limit))
  1355  
  1356  	if teamId != "" {
  1357  		query = query.Join("TeamMembers tm ON ( tm.UserId = u.Id AND tm.DeleteAt = 0 AND tm.TeamId = ? )", teamId)
  1358  	}
  1359  
  1360  	if options.GroupConstrained {
  1361  		query = applyChannelGroupConstrainedFilter(query, channelId)
  1362  	}
  1363  
  1364  	return us.performSearch(query, term, options)
  1365  }
  1366  
  1367  func (us SqlUserStore) SearchInChannel(channelId string, term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1368  	query := us.usersQuery.
  1369  		Join("ChannelMembers cm ON ( cm.UserId = u.Id AND cm.ChannelId = ? )", channelId).
  1370  		OrderBy("Username ASC").
  1371  		Limit(uint64(options.Limit))
  1372  
  1373  	return us.performSearch(query, term, options)
  1374  }
  1375  
  1376  func (us SqlUserStore) SearchInGroup(groupID string, term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1377  	query := us.usersQuery.
  1378  		Join("GroupMembers gm ON ( gm.UserId = u.Id AND gm.GroupId = ? )", groupID).
  1379  		OrderBy("Username ASC").
  1380  		Limit(uint64(options.Limit))
  1381  
  1382  	return us.performSearch(query, term, options)
  1383  }
  1384  
  1385  var spaceFulltextSearchChar = []string{
  1386  	"<",
  1387  	">",
  1388  	"+",
  1389  	"-",
  1390  	"(",
  1391  	")",
  1392  	"~",
  1393  	":",
  1394  	"*",
  1395  	"\"",
  1396  	"!",
  1397  	"@",
  1398  }
  1399  
  1400  func generateSearchQuery(query sq.SelectBuilder, terms []string, fields []string, isPostgreSQL bool) sq.SelectBuilder {
  1401  	for _, term := range terms {
  1402  		searchFields := []string{}
  1403  		termArgs := []interface{}{}
  1404  		for _, field := range fields {
  1405  			if isPostgreSQL {
  1406  				searchFields = append(searchFields, fmt.Sprintf("lower(%s) LIKE lower(?) escape '*' ", field))
  1407  			} else {
  1408  				searchFields = append(searchFields, fmt.Sprintf("%s LIKE ? escape '*' ", field))
  1409  			}
  1410  			termArgs = append(termArgs, fmt.Sprintf("%s%%", strings.TrimLeft(term, "@")))
  1411  		}
  1412  		query = query.Where(fmt.Sprintf("(%s)", strings.Join(searchFields, " OR ")), termArgs...)
  1413  	}
  1414  
  1415  	return query
  1416  }
  1417  
  1418  func (us SqlUserStore) performSearch(query sq.SelectBuilder, term string, options *model.UserSearchOptions) ([]*model.User, error) {
  1419  	term = sanitizeSearchTerm(term, "*")
  1420  
  1421  	var searchType []string
  1422  	if options.AllowEmails {
  1423  		if options.AllowFullNames {
  1424  			searchType = UserSearchTypeAll
  1425  		} else {
  1426  			searchType = UserSearchTypeAll_NO_FULL_NAME
  1427  		}
  1428  	} else {
  1429  		if options.AllowFullNames {
  1430  			searchType = UserSearchTypeNames
  1431  		} else {
  1432  			searchType = UserSearchTypeNames_NO_FULL_NAME
  1433  		}
  1434  	}
  1435  
  1436  	isPostgreSQL := us.DriverName() == model.DATABASE_DRIVER_POSTGRES
  1437  
  1438  	query = applyRoleFilter(query, options.Role, isPostgreSQL)
  1439  	query = applyMultiRoleFilters(query, options.Roles, options.TeamRoles, options.ChannelRoles, isPostgreSQL)
  1440  
  1441  	if !options.AllowInactive {
  1442  		query = query.Where("u.DeleteAt = 0")
  1443  	}
  1444  
  1445  	if strings.TrimSpace(term) != "" {
  1446  		query = generateSearchQuery(query, strings.Fields(term), searchType, isPostgreSQL)
  1447  	}
  1448  
  1449  	query = applyViewRestrictionsFilter(query, options.ViewRestrictions, true)
  1450  
  1451  	queryString, args, err := query.ToSql()
  1452  	if err != nil {
  1453  		return nil, errors.Wrap(err, "perform_search_tosql")
  1454  	}
  1455  
  1456  	var users []*model.User
  1457  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1458  		return nil, errors.Wrapf(err, "failed to find Users with term=%s and searchType=%v", term, searchType)
  1459  	}
  1460  	for _, u := range users {
  1461  		u.Sanitize(map[string]bool{})
  1462  	}
  1463  
  1464  	return users, nil
  1465  }
  1466  
  1467  func (us SqlUserStore) AnalyticsGetInactiveUsersCount() (int64, error) {
  1468  	count, err := us.GetReplica().SelectInt("SELECT COUNT(Id) FROM Users WHERE DeleteAt > 0")
  1469  	if err != nil {
  1470  		return int64(0), errors.Wrap(err, "failed to count inactive Users")
  1471  	}
  1472  	return count, nil
  1473  }
  1474  
  1475  func (us SqlUserStore) AnalyticsGetExternalUsers(hostDomain string) (bool, error) {
  1476  	count, err := us.GetReplica().SelectInt("SELECT COUNT(Id) FROM Users WHERE LOWER(Email) NOT LIKE :HostDomain", map[string]interface{}{"HostDomain": "%@" + strings.ToLower(hostDomain)})
  1477  	if err != nil {
  1478  		return false, errors.Wrap(err, "failed to count inactive Users")
  1479  	}
  1480  	return count > 0, nil
  1481  }
  1482  
  1483  func (us SqlUserStore) AnalyticsGetGuestCount() (int64, error) {
  1484  	count, err := us.GetReplica().SelectInt("SELECT count(*) FROM Users WHERE Roles LIKE :Roles and DeleteAt = 0", map[string]interface{}{"Roles": "%system_guest%"})
  1485  	if err != nil {
  1486  		return int64(0), errors.Wrap(err, "failed to count guest Users")
  1487  	}
  1488  	return count, nil
  1489  }
  1490  
  1491  func (us SqlUserStore) AnalyticsGetSystemAdminCount() (int64, error) {
  1492  	count, err := us.GetReplica().SelectInt("SELECT count(*) FROM Users WHERE Roles LIKE :Roles and DeleteAt = 0", map[string]interface{}{"Roles": "%system_admin%"})
  1493  	if err != nil {
  1494  		return int64(0), errors.Wrap(err, "failed to count system admin Users")
  1495  	}
  1496  	return count, nil
  1497  }
  1498  
  1499  func (us SqlUserStore) GetProfilesNotInTeam(teamId string, groupConstrained bool, offset int, limit int, viewRestrictions *model.ViewUsersRestrictions) ([]*model.User, error) {
  1500  	var users []*model.User
  1501  	query := us.usersQuery.
  1502  		LeftJoin("TeamMembers tm ON ( tm.UserId = u.Id AND tm.DeleteAt = 0 AND tm.TeamId = ? )", teamId).
  1503  		Where("tm.UserId IS NULL").
  1504  		OrderBy("u.Username ASC").
  1505  		Offset(uint64(offset)).Limit(uint64(limit))
  1506  
  1507  	query = applyViewRestrictionsFilter(query, viewRestrictions, true)
  1508  
  1509  	if groupConstrained {
  1510  		query = applyTeamGroupConstrainedFilter(query, teamId)
  1511  	}
  1512  
  1513  	queryString, args, err := query.ToSql()
  1514  	if err != nil {
  1515  		return nil, errors.Wrap(err, "get_profiles_not_in_team_tosql")
  1516  	}
  1517  
  1518  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1519  		return nil, errors.Wrap(err, "failed to find Users")
  1520  	}
  1521  
  1522  	for _, u := range users {
  1523  		u.Sanitize(map[string]bool{})
  1524  	}
  1525  	return users, nil
  1526  }
  1527  
  1528  func (us SqlUserStore) GetEtagForProfilesNotInTeam(teamId string) string {
  1529  	querystr := `
  1530  		SELECT
  1531  			CONCAT(MAX(UpdateAt), '.', COUNT(Id)) as etag
  1532  		FROM
  1533  			Users as u
  1534  		LEFT JOIN TeamMembers tm
  1535  			ON tm.UserId = u.Id
  1536  			AND tm.TeamId = :TeamId
  1537  			AND tm.DeleteAt = 0
  1538  		WHERE
  1539  			tm.UserId IS NULL
  1540  	`
  1541  	etag, err := us.GetReplica().SelectStr(querystr, map[string]interface{}{"TeamId": teamId})
  1542  	if err != nil {
  1543  		return fmt.Sprintf("%v.%v", model.CurrentVersion, model.GetMillis())
  1544  	}
  1545  
  1546  	return fmt.Sprintf("%v.%v", model.CurrentVersion, etag)
  1547  }
  1548  
  1549  func (us SqlUserStore) ClearAllCustomRoleAssignments() error {
  1550  	builtInRoles := model.MakeDefaultRoles()
  1551  	lastUserId := strings.Repeat("0", 26)
  1552  
  1553  	for {
  1554  		var transaction *gorp.Transaction
  1555  		var err error
  1556  
  1557  		if transaction, err = us.GetMaster().Begin(); err != nil {
  1558  			return errors.Wrap(err, "begin_transaction")
  1559  		}
  1560  		defer finalizeTransaction(transaction)
  1561  
  1562  		var users []*model.User
  1563  		if _, err := transaction.Select(&users, "SELECT * from Users WHERE Id > :Id ORDER BY Id LIMIT 1000", map[string]interface{}{"Id": lastUserId}); err != nil {
  1564  			return errors.Wrapf(err, "failed to find Users with id > %s", lastUserId)
  1565  		}
  1566  
  1567  		if len(users) == 0 {
  1568  			break
  1569  		}
  1570  
  1571  		for _, user := range users {
  1572  			lastUserId = user.Id
  1573  
  1574  			var newRoles []string
  1575  
  1576  			for _, role := range strings.Fields(user.Roles) {
  1577  				for name := range builtInRoles {
  1578  					if name == role {
  1579  						newRoles = append(newRoles, role)
  1580  						break
  1581  					}
  1582  				}
  1583  			}
  1584  
  1585  			newRolesString := strings.Join(newRoles, " ")
  1586  			if newRolesString != user.Roles {
  1587  				if _, err := transaction.Exec("UPDATE Users SET Roles = :Roles WHERE Id = :Id", map[string]interface{}{"Roles": newRolesString, "Id": user.Id}); err != nil {
  1588  					return errors.Wrap(err, "failed to update Users")
  1589  				}
  1590  			}
  1591  		}
  1592  
  1593  		if err := transaction.Commit(); err != nil {
  1594  			return errors.Wrap(err, "commit_transaction")
  1595  		}
  1596  	}
  1597  
  1598  	return nil
  1599  }
  1600  
  1601  func (us SqlUserStore) InferSystemInstallDate() (int64, error) {
  1602  	createAt, err := us.GetReplica().SelectInt("SELECT CreateAt FROM Users WHERE CreateAt IS NOT NULL ORDER BY CreateAt ASC LIMIT 1")
  1603  	if err != nil {
  1604  		return 0, errors.Wrap(err, "failed to infer system install date")
  1605  	}
  1606  
  1607  	return createAt, nil
  1608  }
  1609  
  1610  func (us SqlUserStore) GetUsersBatchForIndexing(startTime, endTime int64, limit int) ([]*model.UserForIndexing, error) {
  1611  	var users []*model.User
  1612  	usersQuery, args, _ := us.usersQuery.
  1613  		Where(sq.GtOrEq{"u.CreateAt": startTime}).
  1614  		Where(sq.Lt{"u.CreateAt": endTime}).
  1615  		OrderBy("u.CreateAt").
  1616  		Limit(uint64(limit)).
  1617  		ToSql()
  1618  	_, err := us.GetSearchReplica().Select(&users, usersQuery, args...)
  1619  	if err != nil {
  1620  		return nil, errors.Wrap(err, "failed to find Users")
  1621  	}
  1622  
  1623  	userIds := []string{}
  1624  	for _, user := range users {
  1625  		userIds = append(userIds, user.Id)
  1626  	}
  1627  
  1628  	var channelMembers []*model.ChannelMember
  1629  	channelMembersQuery, args, _ := us.getQueryBuilder().
  1630  		Select(`
  1631  				cm.ChannelId,
  1632  				cm.UserId,
  1633  				cm.Roles,
  1634  				cm.LastViewedAt,
  1635  				cm.MsgCount,
  1636  				cm.MentionCount,
  1637  				cm.NotifyProps,
  1638  				cm.LastUpdateAt,
  1639  				cm.SchemeUser,
  1640  				cm.SchemeAdmin,
  1641  				(cm.SchemeGuest IS NOT NULL AND cm.SchemeGuest) as SchemeGuest
  1642  			`).
  1643  		From("ChannelMembers cm").
  1644  		Join("Channels c ON cm.ChannelId = c.Id").
  1645  		Where(sq.Eq{"c.Type": "O", "cm.UserId": userIds}).
  1646  		ToSql()
  1647  	_, err = us.GetSearchReplica().Select(&channelMembers, channelMembersQuery, args...)
  1648  	if err != nil {
  1649  		return nil, errors.Wrap(err, "failed to find ChannelMembers")
  1650  	}
  1651  
  1652  	var teamMembers []*model.TeamMember
  1653  	teamMembersQuery, args, _ := us.getQueryBuilder().
  1654  		Select("TeamId, UserId, Roles, DeleteAt, (SchemeGuest IS NOT NULL AND SchemeGuest) as SchemeGuest, SchemeUser, SchemeAdmin").
  1655  		From("TeamMembers").
  1656  		Where(sq.Eq{"UserId": userIds, "DeleteAt": 0}).
  1657  		ToSql()
  1658  	_, err = us.GetSearchReplica().Select(&teamMembers, teamMembersQuery, args...)
  1659  	if err != nil {
  1660  		return nil, errors.Wrap(err, "failed to find TeamMembers")
  1661  	}
  1662  
  1663  	userMap := map[string]*model.UserForIndexing{}
  1664  	for _, user := range users {
  1665  		userMap[user.Id] = &model.UserForIndexing{
  1666  			Id:          user.Id,
  1667  			Username:    user.Username,
  1668  			Nickname:    user.Nickname,
  1669  			FirstName:   user.FirstName,
  1670  			LastName:    user.LastName,
  1671  			Roles:       user.Roles,
  1672  			CreateAt:    user.CreateAt,
  1673  			DeleteAt:    user.DeleteAt,
  1674  			TeamsIds:    []string{},
  1675  			ChannelsIds: []string{},
  1676  		}
  1677  	}
  1678  
  1679  	for _, c := range channelMembers {
  1680  		if userMap[c.UserId] != nil {
  1681  			userMap[c.UserId].ChannelsIds = append(userMap[c.UserId].ChannelsIds, c.ChannelId)
  1682  		}
  1683  	}
  1684  	for _, t := range teamMembers {
  1685  		if userMap[t.UserId] != nil {
  1686  			userMap[t.UserId].TeamsIds = append(userMap[t.UserId].TeamsIds, t.TeamId)
  1687  		}
  1688  	}
  1689  
  1690  	usersForIndexing := []*model.UserForIndexing{}
  1691  	for _, user := range userMap {
  1692  		usersForIndexing = append(usersForIndexing, user)
  1693  	}
  1694  	sort.Slice(usersForIndexing, func(i, j int) bool {
  1695  		return usersForIndexing[i].CreateAt < usersForIndexing[j].CreateAt
  1696  	})
  1697  
  1698  	return usersForIndexing, nil
  1699  }
  1700  
  1701  func (us SqlUserStore) GetTeamGroupUsers(teamID string) ([]*model.User, error) {
  1702  	query := applyTeamGroupConstrainedFilter(us.usersQuery, teamID)
  1703  
  1704  	queryString, args, err := query.ToSql()
  1705  	if err != nil {
  1706  		return nil, errors.Wrap(err, "get_team_group_users_tosql")
  1707  	}
  1708  
  1709  	var users []*model.User
  1710  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1711  		return nil, errors.Wrap(err, "failed to find Users")
  1712  	}
  1713  
  1714  	for _, u := range users {
  1715  		u.Sanitize(map[string]bool{})
  1716  	}
  1717  
  1718  	return users, nil
  1719  }
  1720  
  1721  func (us SqlUserStore) GetChannelGroupUsers(channelID string) ([]*model.User, error) {
  1722  	query := applyChannelGroupConstrainedFilter(us.usersQuery, channelID)
  1723  
  1724  	queryString, args, err := query.ToSql()
  1725  	if err != nil {
  1726  		return nil, errors.Wrap(err, "get_channel_group_users_tosql")
  1727  	}
  1728  
  1729  	var users []*model.User
  1730  	if _, err := us.GetReplica().Select(&users, queryString, args...); err != nil {
  1731  		return nil, errors.Wrap(err, "failed to find Users")
  1732  	}
  1733  
  1734  	for _, u := range users {
  1735  		u.Sanitize(map[string]bool{})
  1736  	}
  1737  
  1738  	return users, nil
  1739  }
  1740  
  1741  func applyViewRestrictionsFilter(query sq.SelectBuilder, restrictions *model.ViewUsersRestrictions, distinct bool) sq.SelectBuilder {
  1742  	if restrictions == nil {
  1743  		return query
  1744  	}
  1745  
  1746  	// If you have no access to teams or channels, return and empty result.
  1747  	if restrictions.Teams != nil && len(restrictions.Teams) == 0 && restrictions.Channels != nil && len(restrictions.Channels) == 0 {
  1748  		return query.Where("1 = 0")
  1749  	}
  1750  
  1751  	teams := make([]interface{}, len(restrictions.Teams))
  1752  	for i, v := range restrictions.Teams {
  1753  		teams[i] = v
  1754  	}
  1755  	channels := make([]interface{}, len(restrictions.Channels))
  1756  	for i, v := range restrictions.Channels {
  1757  		channels[i] = v
  1758  	}
  1759  	resultQuery := query
  1760  	if restrictions.Teams != nil && len(restrictions.Teams) > 0 {
  1761  		resultQuery = resultQuery.Join(fmt.Sprintf("TeamMembers rtm ON ( rtm.UserId = u.Id AND rtm.DeleteAt = 0 AND rtm.TeamId IN (%s))", sq.Placeholders(len(teams))), teams...)
  1762  	}
  1763  	if restrictions.Channels != nil && len(restrictions.Channels) > 0 {
  1764  		resultQuery = resultQuery.Join(fmt.Sprintf("ChannelMembers rcm ON ( rcm.UserId = u.Id AND rcm.ChannelId IN (%s))", sq.Placeholders(len(channels))), channels...)
  1765  	}
  1766  
  1767  	if distinct {
  1768  		return resultQuery.Distinct()
  1769  	}
  1770  
  1771  	return resultQuery
  1772  }
  1773  
  1774  func (us SqlUserStore) PromoteGuestToUser(userId string) error {
  1775  	transaction, err := us.GetMaster().Begin()
  1776  	if err != nil {
  1777  		return errors.Wrap(err, "begin_transaction")
  1778  	}
  1779  	defer finalizeTransaction(transaction)
  1780  
  1781  	user, err := us.Get(context.Background(), userId)
  1782  	if err != nil {
  1783  		return err
  1784  	}
  1785  
  1786  	roles := user.GetRoles()
  1787  
  1788  	for idx, role := range roles {
  1789  		if role == "system_guest" {
  1790  			roles[idx] = "system_user"
  1791  		}
  1792  	}
  1793  
  1794  	curTime := model.GetMillis()
  1795  	query := us.getQueryBuilder().Update("Users").
  1796  		Set("Roles", strings.Join(roles, " ")).
  1797  		Set("UpdateAt", curTime).
  1798  		Where(sq.Eq{"Id": userId})
  1799  
  1800  	queryString, args, err := query.ToSql()
  1801  	if err != nil {
  1802  		return errors.Wrap(err, "promote_guest_to_user_tosql")
  1803  	}
  1804  
  1805  	if _, err = transaction.Exec(queryString, args...); err != nil {
  1806  		return errors.Wrapf(err, "failed to update User with userId=%s", userId)
  1807  	}
  1808  
  1809  	query = us.getQueryBuilder().Update("ChannelMembers").
  1810  		Set("SchemeUser", true).
  1811  		Set("SchemeGuest", false).
  1812  		Where(sq.Eq{"UserId": userId})
  1813  
  1814  	queryString, args, err = query.ToSql()
  1815  	if err != nil {
  1816  		return errors.Wrap(err, "promote_guest_to_user_tosql")
  1817  	}
  1818  
  1819  	if _, err = transaction.Exec(queryString, args...); err != nil {
  1820  		return errors.Wrapf(err, "failed to update ChannelMembers with userId=%s", userId)
  1821  	}
  1822  
  1823  	query = us.getQueryBuilder().Update("TeamMembers").
  1824  		Set("SchemeUser", true).
  1825  		Set("SchemeGuest", false).
  1826  		Where(sq.Eq{"UserId": userId})
  1827  
  1828  	queryString, args, err = query.ToSql()
  1829  	if err != nil {
  1830  		return errors.Wrap(err, "promote_guest_to_user_tosql")
  1831  	}
  1832  
  1833  	if _, err := transaction.Exec(queryString, args...); err != nil {
  1834  		return errors.Wrapf(err, "failed to update TeamMembers with userId=%s", userId)
  1835  	}
  1836  
  1837  	if err := transaction.Commit(); err != nil {
  1838  		return errors.Wrap(err, "commit_transaction")
  1839  	}
  1840  	return nil
  1841  }
  1842  
  1843  func (us SqlUserStore) DemoteUserToGuest(userID string) (*model.User, error) {
  1844  	transaction, err := us.GetMaster().Begin()
  1845  	if err != nil {
  1846  		return nil, errors.Wrap(err, "begin_transaction")
  1847  	}
  1848  	defer finalizeTransaction(transaction)
  1849  
  1850  	user, err := us.Get(context.Background(), userID)
  1851  	if err != nil {
  1852  		return nil, err
  1853  	}
  1854  
  1855  	roles := user.GetRoles()
  1856  
  1857  	newRoles := []string{}
  1858  	for _, role := range roles {
  1859  		if role == model.SYSTEM_USER_ROLE_ID {
  1860  			newRoles = append(newRoles, model.SYSTEM_GUEST_ROLE_ID)
  1861  		} else if role != model.SYSTEM_ADMIN_ROLE_ID {
  1862  			newRoles = append(newRoles, role)
  1863  		}
  1864  	}
  1865  
  1866  	curTime := model.GetMillis()
  1867  	newRolesDBStr := strings.Join(newRoles, " ")
  1868  	query := us.getQueryBuilder().Update("Users").
  1869  		Set("Roles", newRolesDBStr).
  1870  		Set("UpdateAt", curTime).
  1871  		Where(sq.Eq{"Id": userID})
  1872  
  1873  	queryString, args, err := query.ToSql()
  1874  	if err != nil {
  1875  		return nil, errors.Wrap(err, "demote_user_to_guest_tosql")
  1876  	}
  1877  
  1878  	if _, err = transaction.Exec(queryString, args...); err != nil {
  1879  		return nil, errors.Wrapf(err, "failed to update User with userId=%s", userID)
  1880  	}
  1881  
  1882  	user.Roles = newRolesDBStr
  1883  	user.UpdateAt = curTime
  1884  
  1885  	query = us.getQueryBuilder().Update("ChannelMembers").
  1886  		Set("SchemeUser", false).
  1887  		Set("SchemeGuest", true).
  1888  		Where(sq.Eq{"UserId": userID})
  1889  
  1890  	queryString, args, err = query.ToSql()
  1891  	if err != nil {
  1892  		return nil, errors.Wrap(err, "demote_user_to_guest_tosql")
  1893  	}
  1894  
  1895  	if _, err = transaction.Exec(queryString, args...); err != nil {
  1896  		return nil, errors.Wrapf(err, "failed to update ChannelMembers with userId=%s", userID)
  1897  	}
  1898  
  1899  	query = us.getQueryBuilder().Update("TeamMembers").
  1900  		Set("SchemeUser", false).
  1901  		Set("SchemeGuest", true).
  1902  		Where(sq.Eq{"UserId": userID})
  1903  
  1904  	queryString, args, err = query.ToSql()
  1905  	if err != nil {
  1906  		return nil, errors.Wrap(err, "demote_user_to_guest_tosql")
  1907  	}
  1908  
  1909  	if _, err := transaction.Exec(queryString, args...); err != nil {
  1910  		return nil, errors.Wrapf(err, "failed to update TeamMembers with userId=%s", userID)
  1911  	}
  1912  
  1913  	if err := transaction.Commit(); err != nil {
  1914  		return nil, errors.Wrap(err, "commit_transaction")
  1915  	}
  1916  	return user, nil
  1917  }
  1918  
  1919  func (us SqlUserStore) AutocompleteUsersInChannel(teamId, channelId, term string, options *model.UserSearchOptions) (*model.UserAutocompleteInChannel, error) {
  1920  	var usersInChannel, usersNotInChannel []*model.User
  1921  	g := errgroup.Group{}
  1922  	g.Go(func() (err error) {
  1923  		usersInChannel, err = us.SearchInChannel(channelId, term, options)
  1924  		return err
  1925  	})
  1926  	g.Go(func() (err error) {
  1927  		usersNotInChannel, err = us.SearchNotInChannel(teamId, channelId, term, options)
  1928  		return err
  1929  	})
  1930  	err := g.Wait()
  1931  	if err != nil {
  1932  		return nil, err
  1933  	}
  1934  
  1935  	return &model.UserAutocompleteInChannel{
  1936  		InChannel:    usersInChannel,
  1937  		OutOfChannel: usersNotInChannel,
  1938  	}, nil
  1939  }
  1940  
  1941  // GetKnownUsers returns the list of user ids of users with any direct
  1942  // relationship with a user. That means any user sharing any channel, including
  1943  // direct and group channels.
  1944  func (us SqlUserStore) GetKnownUsers(userId string) ([]string, error) {
  1945  	var userIds []string
  1946  	usersQuery, args, _ := us.getQueryBuilder().
  1947  		Select("DISTINCT ocm.UserId").
  1948  		From("ChannelMembers AS cm").
  1949  		Join("ChannelMembers AS ocm ON ocm.ChannelId = cm.ChannelId").
  1950  		Where(sq.NotEq{"ocm.UserId": userId}).
  1951  		Where(sq.Eq{"cm.UserId": userId}).
  1952  		ToSql()
  1953  	_, err := us.GetSearchReplica().Select(&userIds, usersQuery, args...)
  1954  	if err != nil {
  1955  		return nil, errors.Wrap(err, "failed to find ChannelMembers")
  1956  	}
  1957  
  1958  	return userIds, nil
  1959  }