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, ¬ifyProps, &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, ¬ifyProps, &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 }