Sync-in · Sovereign platform for file storage, sharing, synchronization, and collaboration.
TypeScript cloud-alternativecollaborationcollaborative-editingdropboxfile-serverfile-sharingfile-storagefile-syncfull-text-searchhomelabnextcloudon-premiseopen-sourceprivacy-firstprivate-cloudrealtimesecureself-hostedstorage-serverwebdav
import { toErrorMessage } from '../../../common/errors.js'
import { Inject, Injectable, Logger } from '@nestjs/common'
import { and, countDistinct, desc, eq, gte, inArray, isNotNull, isNull, like, lte, ne, notInArray, or, SQL, sql } from 'drizzle-orm'
import { alias } from 'drizzle-orm/mysql-core'
import { MySql2PreparedQuery, MySqlQueryResult } from 'drizzle-orm/mysql2'
import { CACHE_AUTH_WEBDAV_PREFIX } from '../../../authentication/constants/cache.js'
import { anonymizePassword, comparePassword, uniquePermissions } from '../../../common/functions.js'
import { CacheDecorator } from '../../../infrastructure/cache/cache.decorator.js'
import { Cache } from '../../../infrastructure/cache/cache.service.js'
import { configuration } from '../../../configuration/config.environment.js'
import { DB_TOKEN_PROVIDER } from '../../../infrastructure/database/constants.js'
import { type DBSchema } from '../../../infrastructure/database/interfaces/database.interface.js'
import {
concatDistinctObjectsInArray,
convertToSelect,
dateTimeUTC,
dbCheckAffectedRows,
dbGetInsertedId,
dbParseJson
} from '../../../infrastructure/database/utils.js'
import { GROUP_TYPE, GROUP_VISIBILITY } from '../constants/group.js'
import { MEMBER_TYPE } from '../constants/member.js'
import { USER_GROUP_ROLE, USER_MAX_PASSWORD_ATTEMPTS, USER_ONLINE_STATUS, USER_PERMS_SEP, USER_ROLE } from '../constants/user.js'
import type { UserCreateOrUpdateGroupDto } from '../dto/create-or-update-group.dto.js'
import type { CreateUserDto } from '../dto/create-or-update-user.dto.js'
import type { ValidatedSearchMembersDto } from '../dto/search-members.dto.js'
import { GroupMember, GroupWithMembers } from '../interfaces/group-member.js'
import { GuestUser } from '../interfaces/guest-user.interface.js'
import { Member } from '../interfaces/member.interface.js'
import type { UserAccessUpdateResult } from '../interfaces/user-access-update-result.interface.js'
import type { UserPreferences } from '../interfaces/user-preferences.interface.js'
import { UserSecrets } from '../interfaces/user-secrets.interface.js'
import { UserOnline } from '../interfaces/websocket.interface.js'
import { UserModel } from '../models/user.model.js'
import type { Group } from '../schemas/group.interface.js'
import { groups } from '../schemas/groups.schema.js'
import type { UserGroup } from '../schemas/user-group.interface.js'
import type { User } from '../schemas/user.interface.js'
import { usersGroups } from '../schemas/users-groups.schema.js'
import { usersGuests } from '../schemas/users-guests.schema.js'
import { userFullNameSQL, users } from '../schemas/users.schema.js'
import { normalizeUserPreferences } from '../utils/user.js'
@Injectable()
export class UsersQueries {
private readonly logger = new Logger(UsersQueries.name)
private fromExternalIdOrEmailPermissionsQuery: MySql2PreparedQuery<any> | null = null
private fromLoginOrEmailPermissionsQuery: MySql2PreparedQuery<any> | null = null
private fromIdPermissionsQuery: MySql2PreparedQuery<any> | null = null
constructor(
@Inject(DB_TOKEN_PROVIDER) private readonly db: DBSchema,
private readonly cache: Cache
) {}
checkUserExists(login?: string | null, email?: string | null): Promise<{ login?: string; email?: string } | undefined> {
if (!login && !email) {
throw new Error('login or email must be specified')
}
const columns: { login?: boolean; email?: boolean } = {}
const where: SQL[] = []
if (login) {
columns.login = true
where.push(eq(users.login, login))
}
if (email) {
columns.email = true
where.push(eq(users.email, email))
}
const operator = login && email ? or : and
return this.db.query.users.findFirst({
columns: columns,
where: operator(...where)
})
}
setOnlineStatus(userId: number, onlineStatus: USER_ONLINE_STATUS): Promise<boolean> {
return this.updateUserOrGuest(userId, { onlineStatus: onlineStatus })
}
getOnlineUsers(userIds: number[]): Promise<UserOnline[]> {
return this.db
.select({
id: users.id,
login: users.login,
email: users.email,
fullName: userFullNameSQL(users),
onlineStatus: users.onlineStatus
} satisfies Record<keyof UserOnline, any>)
.from(users)
.where(inArray(users.id, userIds))
}
async checkGroupNameExists(groupName: string): Promise<boolean> {
const [group] = await this.db.select({ name: groups.name }).from(groups).where(eq(groups.name, groupName)).limit(1)
return !!group?.name
}
async compareUserPassword(userId: number, password: string): Promise<boolean> {
const [hash] = await this.selectUsers(['password'], [eq(users.id, userId)])
return comparePassword(password, hash?.password)
}
async from(userId?: number | null, loginOrEmail?: string): Promise<User | null> {
// retrieve user with application permissions
let pQuery = userId ? this.fromIdPermissionsQuery : this.fromLoginOrEmailPermissionsQuery
if (!pQuery) {
const where = userId
? eq(users.id, sql.placeholder('userId'))
: and(
lte(users.role, USER_ROLE.GUEST),
or(eq(users.login, sql.placeholder('loginOrEmail')), eq(users.email, sql.placeholder('loginOrEmail')))
)
pQuery = this.db
.select({
user: users,
groupsPermissions: sql`GROUP_CONCAT(DISTINCT (${groups.permissions}) SEPARATOR ${USER_PERMS_SEP})`
})
.from(users)
.leftJoin(usersGroups, eq(usersGroups.userId, users.id))
.leftJoin(groups, and(eq(groups.id, usersGroups.groupId), ne(groups.permissions, '')))
.where(where)
.groupBy(users.id)
.limit(1)
.prepare()
if (userId) {
this.fromIdPermissionsQuery = pQuery
} else {
this.fromLoginOrEmailPermissionsQuery = pQuery
}
}
const r = await pQuery.execute(userId ? { userId } : { loginOrEmail: loginOrEmail?.trim() })
if (!r.length) return null
const [user, groupsPermissions] = [r[0].user, r[0].groupsPermissions]
// merge user and groups permissions
user.permissions = uniquePermissions(`${user.permissions},${groupsPermissions}`, USER_PERMS_SEP)
return user
}
async fromExternalIdOrEmail(externalId: string, email: string): Promise<User | null> {
if (!this.fromExternalIdOrEmailPermissionsQuery) {
const externalIdPlaceholder = sql.placeholder('externalId')
const emailPlaceholder = sql.placeholder('email')
this.fromExternalIdOrEmailPermissionsQuery = this.db
.select({
user: users,
groupsPermissions: sql`GROUP_CONCAT(DISTINCT (${groups.permissions}) SEPARATOR ${USER_PERMS_SEP})`
})
.from(users)
.leftJoin(usersGroups, eq(usersGroups.userId, users.id))
.leftJoin(groups, and(eq(groups.id, usersGroups.groupId), ne(groups.permissions, '')))
.where(and(lte(users.role, USER_ROLE.GUEST), or(eq(users.externalId, externalIdPlaceholder), eq(users.email, emailPlaceholder))))
.groupBy(users.id)
.orderBy(desc(eq(users.externalId, externalIdPlaceholder)))
.limit(1)
.prepare()
}
const r = await this.fromExternalIdOrEmailPermissionsQuery.execute({ externalId, email })
if (!r.length) return null
const [user, groupsPermissions] = [r[0].user, r[0].groupsPermissions]
user.permissions = uniquePermissions(`${user.permissions},${groupsPermissions}`, USER_PERMS_SEP)
return user
}
async getUserSecrets(userId: number): Promise<UserSecrets> {
const [r] = await this.db.select({ secrets: users.secrets }).from(users).where(eq(users.id, userId)).limit(1)
return r!.secrets || {}
}
async mutateUserSecrets<T>(userId: number, mutate: (secrets: UserSecrets) => { result: T; secrets?: UserSecrets }): Promise<T> {
// Every secrets writer must use the latest document while holding the same row lock.
return this.db.transaction(async (tx) => {
const [user] = await tx.select({ secrets: users.secrets }).from(users).where(eq(users.id, userId)).limit(1).for('update')
if (!user) {
throw new Error(`User (${userId}) not found`)
}
const mutation = mutate(user.secrets || {})
if (mutation.secrets !== undefined) {
dbCheckAffectedRows(await tx.update(users).set({ secrets: mutation.secrets }).where(eq(users.id, userId)), 1)
}
return mutation.result
})
}
async updateUserPreferences(userId: number, patch: Partial<UserPreferences>): Promise<UserPreferences> {
return this.db.transaction(async (tx) => {
const [user] = await tx.select({ preferences: users.preferences }).from(users).where(eq(users.id, userId)).limit(1).for('update')
if (!user) {
throw new Error(`User (${userId}) not found`)
}
const currentPreferences = normalizeUserPreferences(user.preferences)
const preferences = { ...currentPreferences, ...patch }
if (
preferences.theme === currentPreferences.theme &&
preferences.editor === currentPreferences.editor &&
preferences.showHiddenFiles === currentPreferences.showHiddenFiles &&
preferences.useSystemNotifications === currentPreferences.useSystemNotifications &&
preferences.sidebarQuickAccessVisibility === currentPreferences.sidebarQuickAccessVisibility &&
preferences.sidebarQuickAccessPosition === currentPreferences.sidebarQuickAccessPosition
) {
return currentPreferences
}
dbCheckAffectedRows(await tx.update(users).set({ preferences }).where(eq(users.id, userId)), 1)
return preferences
})
}
selectUsers<K extends keyof User>(fields: readonly K[], where: SQL[]): Promise<Pick<User, K>[]> {
const select = convertToSelect(users, fields)
return this.db
.select(select)
.from(users)
.where(and(...where)) as Promise<Pick<User, K>[]>
}
async usersHaveRole(userIds: number[], role: USER_ROLE): Promise<boolean> {
const uniqueUserIds = [...new Set(userIds)]
if (!uniqueUserIds.length) return true
const matchingUsers = await this.db
.select({ id: users.id })
.from(users)
.where(and(inArray(users.id, uniqueUserIds), lte(users.role, role)))
return matchingUsers.length === uniqueUserIds.length
}
async selectUserProperties<K extends keyof User>(userId: number, fields: readonly K[]): Promise<Pick<User, K> | undefined> {
const select = convertToSelect(users, fields)
const [user] = (await this.db.select(select).from(users).where(eq(users.id, userId)).limit(1)) as Pick<User, K>[]
return user
}
async createUserOrGuest(createUserDto: CreateUserDto, userRole: USER_ROLE): Promise<User['id']> {
const userId: number = dbGetInsertedId(await this.db.insert(users).values({ ...createUserDto, role: userRole } as User))
if (createUserDto.groups?.length) {
await this.db.insert(usersGroups).values(createUserDto.groups.map((gid: number) => ({ userId: userId, groupId: gid })))
}
if (userRole === USER_ROLE.GUEST && createUserDto.managers?.length) {
await this.db.insert(usersGuests).values(createUserDto.managers.map((uid: number) => ({ guestId: userId, userId: uid })))
}
return userId
}
async updateUserOrGuest(userId: number, set: Partial<Record<Exclude<keyof User, 'secrets'>, any>>, userRole?: USER_ROLE): Promise<boolean> {
try {
dbCheckAffectedRows(
await this.db
.update(users)
.set({ ...set, ...(userRole && { role: userRole }) } as User)
.where(eq(users.id, userId)),
1
)
this.logger.verbose({ tag: this.updateUserOrGuest.name, msg: `user (${userId}) was updated : ${JSON.stringify(anonymizePassword(set))}` })
if (['login', 'email', 'password', 'isActive', 'role', 'permissions'].some((field) => field in set)) {
await this.clearWebDAVAuthCache(userId)
}
return true
} catch (e) {
this.logger.error({
tag: this.updateUserOrGuest.name,
msg: `user (${userId}) was not updated : ${JSON.stringify(anonymizePassword(set))} : ${toErrorMessage(e)}`
})
return false
}
}
async bindExternalId(userId: number, externalId: string): Promise<boolean> {
try {
const isBound = await this.db.transaction(async (tx) => {
const [user]: Pick<User, 'externalId'>[] = await tx
.select({ externalId: users.externalId })
.from(users)
.where(eq(users.id, userId))
.limit(1)
.for('update')
if (!user) {
return false
}
if (user.externalId !== null) {
return user.externalId === externalId
}
dbCheckAffectedRows(
await tx
.update(users)
.set({ externalId })
.where(and(eq(users.id, userId), isNull(users.externalId))),
1
)
return true
})
if (isBound) {
this.logger.verbose({ tag: this.bindExternalId.name, msg: `external identity was bound to user (${userId})` })
} else {
this.logger.warn({ tag: this.bindExternalId.name, msg: `external identity binding was rejected for user (${userId})` })
}
return isBound
} catch (e) {
this.logger.error({ tag: this.bindExternalId.name, msg: `external identity was not bound to user (${userId}) : ${toErrorMessage(e)}` })
return false
}
}
async updateAccesses(userId: number, ip: string | null, attemptsAction: 'preserve' | 'reset' | 'increment'): Promise<UserAccessUpdateResult> {
const passwordAttemptsUpdate =
attemptsAction === 'reset'
? sql`, ${sql.identifier(users.passwordAttempts.name)} = ${sql.param(0, users.passwordAttempts)}`
: attemptsAction === 'increment'
? sql`, ${sql.identifier(users.passwordAttempts.name)} = LEAST(${users.passwordAttempts} + 1, ${USER_MAX_PASSWORD_ATTEMPTS})`
: sql``
const updateAccessesQuery = () => sql`
UPDATE ${users}
SET ${sql.identifier(users.lastAccess.name)} = ${users.currentAccess},
${sql.identifier(users.currentAccess.name)} = ${sql.param(new Date(), users.currentAccess)},
${sql.identifier(users.lastIp.name)} = ${users.currentIp},
${sql.identifier(users.currentIp.name)} = ${sql.param(ip, users.currentIp)} ${passwordAttemptsUpdate}
WHERE ${users.id} = ${userId}
`
try {
// Drizzle orders UPDATE assignments by schema order.
// Keep each previous value assignment first because MariaDB evaluates them left-to-right.
let temporaryLockStarted = false
if (attemptsAction === 'increment') {
// Serialize failures so only one request can report the transition to a temporary lock.
temporaryLockStarted = await this.db.transaction(async (tx) => {
const [user]: Pick<User, 'passwordAttempts'>[] = await tx
.select({ passwordAttempts: users.passwordAttempts })
.from(users)
.where(eq(users.id, userId))
.limit(1)
.for('update')
if (!user) {
throw new Error(`User (${userId}) not found`)
}
const currentPasswordAttempts = user.passwordAttempts ?? 0
if (currentPasswordAttempts >= USER_MAX_PASSWORD_ATTEMPTS) {
// Do not move currentAccess: it is also the start of the temporary lock period.
return false
}
dbCheckAffectedRows(await tx.execute(updateAccessesQuery()), 1)
return currentPasswordAttempts + 1 >= USER_MAX_PASSWORD_ATTEMPTS
})
} else {
dbCheckAffectedRows(await this.db.execute(updateAccessesQuery()), 1)
}
this.logger.verbose({ tag: this.updateAccesses.name, msg: `accesses for user (${userId}) were updated` })
return { success: true, temporaryLockStarted }
} catch (e) {
this.logger.error({ tag: this.updateAccesses.name, msg: `accesses for user (${userId}) were not updated : ${toErrorMessage(e)}` })
return { success: false, temporaryLockStarted: false }
}
}
async resetExpiredPasswordAttempts(userId: number, expiredBefore: Date): Promise<boolean> {
try {
// The conditional update lets concurrent requests release an expired lock without resetting newer failures.
const result = await this.db
.update(users)
.set({ passwordAttempts: 0 })
.where(
and(
eq(users.id, userId),
gte(users.passwordAttempts, USER_MAX_PASSWORD_ATTEMPTS),
or(isNull(users.currentAccess), lte(users.currentAccess, expiredBefore))
)
)
if (!dbCheckAffectedRows(result, 1, false)) {
this.logger.warn({ tag: this.resetExpiredPasswordAttempts.name, msg: `expired password lock for user (${userId}) was not released` })
return false
}
this.logger.verbose({ tag: this.resetExpiredPasswordAttempts.name, msg: `expired password lock for user (${userId}) was released` })
return true
} catch (e) {
this.logger.error({
tag: this.resetExpiredPasswordAttempts.name,
msg: `expired password lock for user (${userId}) was not released : ${toErrorMessage(e)}`
})
return false
}
}
async deleteGuestLink(userId: number): Promise<void> {
dbCheckAffectedRows(await this.db.delete(users).where(and(eq(users.id, userId), eq(users.role, USER_ROLE.LINK))), 1)
}
async searchUsersOrGroups(searchMembersDto: ValidatedSearchMembersDto, userId?: number): Promise<Member[]> {
// `userId` is required for user routes to avoid searching across all users and groups
const limit = searchMembersDto.onlyUsers || searchMembersDto.onlyGroups ? 6 : 3
const members: Member[] = []
if (!searchMembersDto.onlyGroups) {
for (const u of await this.searchUsers(searchMembersDto, userId, limit)) {
members.push({
id: u.id,
login: u.login,
name: u.fullName,
description: u.email,
type: u.role === USER_ROLE.GUEST ? MEMBER_TYPE.GUEST : MEMBER_TYPE.USER,
permissions: searchMembersDto.withPermissions ? u.permissions : undefined
})
}
}
if (!searchMembersDto.onlyUsers) {
for (const g of await this.searchGroups(searchMembersDto, userId, limit)) {
members.push({
id: g.id,
name: g.name,
description: g.description,
type: g.type === GROUP_TYPE.USER ? MEMBER_TYPE.GROUP : MEMBER_TYPE.PGROUP,
permissions: searchMembersDto.withPermissions ? g.permissions : undefined
})
}
}
return members
}
async groupFromName(userId: number, name: string): Promise<(Pick<Group, 'id' | 'name' | 'type'> & { role: UserGroup['role'] }) | undefined> {
const [group] = await this.db
.select({
id: groups.id,
name: groups.name,
type: groups.type,
role: usersGroups.role
} satisfies Partial<Record<keyof Group, any>> & { role: any })
.from(usersGroups)
.innerJoin(groups, eq(groups.id, usersGroups.groupId))
.where(and(eq(usersGroups.userId, userId), eq(groups.name, name)))
.limit(1)
return group
}
async browseRootGroups(userId: number): Promise<Member[]> {
const members = alias(usersGroups, 'members')
return this.db
.select({
id: groups.id,
name: groups.name,
description: groups.description,
createdAt: groups.createdAt,
modifiedAt: groups.modifiedAt,
type: sql<MEMBER_TYPE>`IF(${groups.type} = ${GROUP_TYPE.USER}, ${MEMBER_TYPE.GROUP}, ${MEMBER_TYPE.PGROUP})`,
groupRole: sql<USER_GROUP_ROLE>`${usersGroups.role}`,
counts: { users: countDistinct(members.userId) }
} satisfies { [K in keyof Member]: any })
.from(usersGroups)
.innerJoin(groups, and(eq(groups.id, usersGroups.groupId), eq(usersGroups.userId, userId)))
.leftJoin(members, eq(members.groupId, groups.id))
.groupBy(groups.id)
}
browseGroupMembers(groupId: number): Promise<Member[]> {
return this.db
.select({
id: users.id,
login: users.login,
name: userFullNameSQL(users).as('name'),
description: users.email,
createdAt: usersGroups.createdAt,
type: sql<MEMBER_TYPE>`IF(${users.role} = ${USER_ROLE.GUEST}, ${MEMBER_TYPE.GUEST}, ${MEMBER_TYPE.USER})`,
groupRole: sql<USER_GROUP_ROLE>`${usersGroups.role}`
} satisfies { [K in keyof Member]: any })
.from(groups)
.innerJoin(usersGroups, and(eq(usersGroups.groupId, groups.id), eq(usersGroups.groupId, groupId)))
.leftJoin(users, eq(users.id, usersGroups.userId))
.groupBy(users.id) as Promise<Member[]>
}
async canDeletePersonalGroup(userId: number, groupId: number): Promise<boolean> {
const [group] = await this.db
.select({ id: usersGroups.groupId })
.from(usersGroups)
.innerJoin(groups, and(eq(groups.id, usersGroups.groupId)))
.where(
and(
eq(groups.type, GROUP_TYPE.PERSONAL),
eq(usersGroups.userId, userId),
eq(usersGroups.groupId, groupId),
eq(usersGroups.role, USER_GROUP_ROLE.MANAGER)
)
)
.limit(1)
return !!group?.id
}
async getGroup(userId: number, groupId: number, asAdmin = false): Promise<GroupMember | undefined> {
const [group] = await this.db
.select({
id: groups.id,
name: groups.name,
description: groups.description,
createdAt: groups.createdAt,
modifiedAt: groups.modifiedAt,
type: sql<MEMBER_TYPE>`IF(${groups.type} = ${GROUP_TYPE.USER}, ${MEMBER_TYPE.GROUP}, ${MEMBER_TYPE.PGROUP})`
})
.from(usersGroups)
.innerJoin(groups, and(eq(groups.id, usersGroups.groupId)))
.where(
and(
eq(usersGroups.groupId, groupId),
sql`IF(${+asAdmin} = 0, ${usersGroups.userId} = ${userId} AND ${usersGroups.role} = ${USER_GROUP_ROLE.MANAGER}, 1)`
)
)
.limit(1)
return group
}
async getGroupWithMembers(userId: number, groupId: number, asAdmin = false): Promise<GroupWithMembers | undefined> {
const usersGroupsAlias: any = alias(usersGroups, 'usersFromGroups')
const [group] = await this.db
.select({
id: groups.id,
name: groups.name,
description: groups.description,
createdAt: groups.createdAt,
modifiedAt: groups.modifiedAt,
type: sql<MEMBER_TYPE>`IF(${groups.type} = ${GROUP_TYPE.USER}, ${MEMBER_TYPE.GROUP}, ${MEMBER_TYPE.PGROUP})`,
members: concatDistinctObjectsInArray(users.id, {
id: users.id,
login: users.login,
name: userFullNameSQL(users),
description: users.email,
type: sql<MEMBER_TYPE>`IF(${users.role} = ${USER_ROLE.GUEST}, ${MEMBER_TYPE.GUEST}, ${MEMBER_TYPE.USER})`,
groupRole: usersGroupsAlias.role,
createdAt: dateTimeUTC(usersGroupsAlias.createdAt)
} satisfies { [K in keyof Member]: any })
} satisfies { [K in keyof GroupWithMembers]: any })
.from(usersGroups)
.innerJoin(groups, eq(groups.id, usersGroups.groupId))
.leftJoin(usersGroupsAlias, and(eq(usersGroupsAlias.groupId, groups.id)))
.leftJoin(users, eq(users.id, usersGroupsAlias.userId))
.where(
and(
eq(usersGroups.groupId, groupId),
sql`IF(${+asAdmin} = 0, ${usersGroups.userId} = ${userId} AND ${usersGroups.role} = ${USER_GROUP_ROLE.MANAGER}, 1)`
)
)
.groupBy(groups.id)
.limit(1)
return group
}
async deletePersonalGroup(groupId: number): Promise<boolean> {
return dbCheckAffectedRows(
await this.db
.delete(groups)
.where(and(eq(groups.id, groupId), eq(groups.type, GROUP_TYPE.PERSONAL)))
.limit(1),
1,
false
)
}
async createPersonalGroup(managerId: number, userCreateOrUpdateGroupDto: UserCreateOrUpdateGroupDto): Promise<Group['id']> {
const groupId: number = dbGetInsertedId(
await this.db.insert(groups).values({
...userCreateOrUpdateGroupDto,
type: GROUP_TYPE.PERSONAL,
visibility: GROUP_VISIBILITY.PRIVATE
} as Group)
)
await this.db.insert(usersGroups).values({ userId: managerId, groupId: groupId, role: USER_GROUP_ROLE.MANAGER } as UserGroup)
return groupId
}
async updateGroup(groupId: number, set: Partial<Record<keyof Group, any>>) {
if (Object.keys(set).length) {
try {
await this.db.update(groups).set(set).where(eq(groups.id, groupId))
this.logger.log({ tag: this.updateGroup.name, msg: `group (${groupId}) was updated : ${JSON.stringify(set)}` })
} catch (e) {
this.logger.error({ tag: this.updateGroup.name, msg: `group (${groupId}) was not updated : ${JSON.stringify(set)} : ${toErrorMessage(e)}` })
throw new Error('Group was not updated')
}
}
}
async updateGroupMembers(
groupId: number,
members: {
add?: Pick<Member, 'id' | 'groupRole'>[]
remove?: UserGroup['userId'][]
}
): Promise<void> {
if (members?.add?.length) {
try {
await this.db.insert(usersGroups).values(members.add.map((m) => ({ userId: m.id, groupId: groupId, role: m.groupRole })))
// clear cache
void this.clearWhiteListCaches(members.add.map((m) => m.id))
this.logger.log({
tag: this.updateGroupMembers.name,
msg: `users ${JSON.stringify(members.add.map((m) => m.id))} was added to group (${groupId})`
})
} catch (e) {
this.logger.error({
tag: this.updateGroupMembers.name,
msg: `users ${JSON.stringify(members.add.map((m) => m.id))} was not added to group (${groupId}) : ${toErrorMessage(e)}`
})
throw new Error('Group members was not added')
}
}
if (members?.remove?.length) {
try {
await this.db
.delete(usersGroups)
.where(and(eq(usersGroups.groupId, groupId), inArray(usersGroups.userId, members.remove)))
.limit(members.remove.length)
// clear cache
void this.clearWhiteListCaches(members.remove)
this.logger.log({ tag: this.updateGroupMembers.name, msg: `users ${JSON.stringify(members.remove)} was removed from group (${groupId})` })
} catch (e) {
this.logger.error({
tag: this.updateGroupMembers.name,
msg: `users ${JSON.stringify(members.remove)} was not removed from group (${groupId}) : ${toErrorMessage(e)}`
})
throw new Error('Group members was not removed')
}
}
}
async listGuests(guestId: null, managerId?: number | null, asAdmin?: boolean): Promise<GuestUser[]>
async listGuests(guestId: number, managerId?: number | null, asAdmin?: boolean): Promise<GuestUser | undefined>
async listGuests(guestId: number | null, managerId?: number | null, asAdmin = false): Promise<GuestUser | GuestUser[] | undefined> {
const where: SQL[] = [...(guestId ? [eq(usersGuests.guestId, guestId)] : []), ...(asAdmin ? [] : [eq(usersGuests.userId, managerId as number)])]
const managersGuestAlias: any = alias(usersGuests, 'managersGuestAlias')
const managersAlias: any = alias(users, 'managersAlias')
const q = this.db
.select({
id: users.id,
login: users.login,
email: users.email,
firstName: users.firstName,
lastName: users.lastName,
fullName: userFullNameSQL(users),
role: users.role,
isActive: users.isActive,
passwordAttempts: users.passwordAttempts,
language: users.language,
notification: users.notification,
currentAccess: users.currentAccess,
lastAccess: users.lastAccess,
currentIp: users.currentIp,
lastIp: users.lastIp,
createdAt: users.createdAt,
managers: concatDistinctObjectsInArray(managersAlias.id, {
id: managersAlias.id,
login: managersAlias.login,
name: userFullNameSQL(managersAlias),
type: sql`${MEMBER_TYPE.USER}`,
description: managersAlias.email,
createdAt: dateTimeUTC(managersGuestAlias.createdAt)
} satisfies { [K in keyof Member]: any }),
...(guestId && {
groups: concatDistinctObjectsInArray(groups.id, {
id: groups.id,
name: groups.name,
description: groups.description,
type: sql`${MEMBER_TYPE.PGROUP}`,
permissions: groups.permissions,
createdAt: dateTimeUTC(usersGroups.createdAt)
} satisfies { [K in keyof Member]: any })
})
} satisfies { [K in keyof GuestUser]: any })
.from(usersGuests)
q.innerJoin(users, and(eq(users.id, usersGuests.guestId), eq(users.role, USER_ROLE.GUEST)))
q.leftJoin(managersGuestAlias, eq(managersGuestAlias.guestId, users.id))
q.leftJoin(managersAlias, eq(managersAlias.id, managersGuestAlias.userId))
if (guestId) {
q.leftJoin(usersGroups, eq(usersGroups.userId, users.id))
q.leftJoin(groups, and(eq(groups.id, usersGroups.groupId), eq(groups.type, GROUP_TYPE.PERSONAL)))
}
q.where(and(...where))
q.groupBy(users.id)
if (guestId) q.limit(1)
const guests = await q
if (!guestId) return guests as GuestUser[]
return guests[0]
}
async isGuestManager(managerId: number, guestId: number): Promise<{ id: number; login: string } | undefined> {
const [guest] = await this.db
.select({ id: usersGuests.guestId, login: users.login })
.from(usersGuests)
.innerJoin(users, eq(users.id, usersGuests.guestId))
.where(and(eq(usersGuests.userId, managerId), eq(usersGuests.guestId, guestId), eq(users.role, USER_ROLE.GUEST)))
.limit(1)
return guest
}
@CacheDecorator(1800)
async usersWhitelist(userId: number, lowerOrEqualUserRole: USER_ROLE = USER_ROLE.GUEST): Promise<number[]> {
/* Get the list of user ids allowed to the current user
- the current user, when their role is lower than or equal to lowerOrEqualUserRole
- users with no groups only when applications.users.showUngroupedUsers = true
(guest accounts are excluded from this global branch, and guest requesters never get this branch)
(also excludes users with a role higher than lowerOrEqualUserRole)
- all users who are members of groups visible to the current user
(VISIBLE groups for everyone, PRIVATE groups only if the current user is a member, never members of ISOLATED groups)
- all guests managed by the current user
- all managers who manage the current guest
*/
if (await this.isGuestLink(userId)) {
// Guest-link accounts must never receive a user whitelist
return []
}
const showUngroupedUsers = +configuration.applications.users.showUngroupedUsers
const userIds = sql`
WITH visible_groups AS (
SELECT ${groups.id} AS id
FROM ${groups}
LEFT JOIN ${usersGroups}
ON ${usersGroups.groupId} = ${groups.id}
AND ${usersGroups.userId} = ${userId}
WHERE
${groups.visibility} != ${GROUP_VISIBILITY.ISOLATED}
AND (
${groups.visibility} = ${GROUP_VISIBILITY.VISIBLE}
OR (
${groups.visibility} = ${GROUP_VISIBILITY.PRIVATE}
AND ${usersGroups.userId} IS NOT NULL
)
)
)
SELECT JSON_ARRAYAGG(id) AS ids
FROM (
-- 0) Current user
SELECT ${users.id} AS id
FROM ${users}
WHERE ${users.id} = ${userId}
AND ${users.role} <= ${lowerOrEqualUserRole}
UNION
-- 1) Users from groups visible to the current user
SELECT ${users.id} AS id
FROM ${users}
INNER JOIN ${usersGroups}
ON ${usersGroups.userId} = ${users.id}
INNER JOIN visible_groups
ON visible_groups.id = ${usersGroups.groupId}
WHERE ${users.role} <= ${lowerOrEqualUserRole}
UNION
-- 2) Users with no groups
SELECT ${users.id} AS id
FROM ${users}
WHERE
${showUngroupedUsers} = 1
AND
${users.role} <= ${lowerOrEqualUserRole}
-- Guests without (personal) groups are not globally visible.
-- They are allowed only through part 1 (shared visible group) or part 3 (manager relation).
AND ${users.role} != ${USER_ROLE.GUEST}
-- Guests must not see all users without groups:
-- this branch is enabled only for non-guest requesters.
AND EXISTS (
SELECT 1
FROM ${users}
WHERE ${users.id} = ${userId}
AND ${users.role} != ${USER_ROLE.GUEST}
)
AND NOT EXISTS (
SELECT 1
FROM ${usersGroups}
WHERE ${usersGroups.userId} = ${users.id}
)
UNION
-- 3) Guests managed by the user and managers of the current guest
SELECT CASE
WHEN ${usersGuests.userId} = ${userId} THEN ${usersGuests.guestId}
WHEN ${usersGuests.guestId} = ${userId} THEN ${usersGuests.userId}
END AS id
FROM ${usersGuests}
WHERE ${usersGuests.userId} = ${userId}
OR ${usersGuests.guestId} = ${userId}
) AS usersUnion
`
const [r] = (await this.db.execute(userIds)) as unknown as [{ ids: unknown }[], unknown]
return dbParseJson(r[0]!.ids) || []
}
@CacheDecorator(1800)
async groupsWhitelist(userId: number, groupType?: GROUP_TYPE, userGroupRole?: USER_GROUP_ROLE): Promise<number[]> {
/* Get the list of group IDs the current user is allowed to see.
- A group marked VISIBLE is always included.
- A group marked ISOLATED is always excluded.
- A PRIVATE group is included only if the user is a direct member of it.
- Visibility does not inherit from parent or child groups.
*/
if (await this.isGuestLink(userId)) {
// Guest-link accounts must never receive a group whitelist
return []
}
const optionalFilters: SQL[] = [
...(groupType != null ? [eq(groups.type, groupType)] : []),
...(userGroupRole != null ? [eq(usersGroups.role, userGroupRole)] : [])
]
const q = this.db
.select({ id: sql`JSON_ARRAYAGG(${groups.id}) as ids` })
.from(groups)
.leftJoin(usersGroups, and(eq(usersGroups.groupId, groups.id), eq(usersGroups.userId, userId)))
.where(
and(
...optionalFilters,
ne(groups.visibility, GROUP_VISIBILITY.ISOLATED),
or(eq(groups.visibility, GROUP_VISIBILITY.VISIBLE), and(eq(groups.visibility, GROUP_VISIBILITY.PRIVATE), isNotNull(usersGroups.userId)))
)
)
const [r] = (await this.db.execute(q)) as unknown as [{ ids: unknown }[], unknown]
return dbParseJson(r[0]!.ids) || []
}
async clearWhiteListCaches(userIds: number[] | '*'): Promise<void> {
try {
// '*' -> Means all entries
const whitelists = [this.usersWhitelist.name, this.groupsWhitelist.name]
const keysToDelete: string[] = []
if (userIds === '*') {
const patterns = whitelists.map((whitelist) => this.cache.genSlugKey(this.constructor.name, whitelist, userIds))
for (const pattern of patterns) {
keysToDelete.push(...(await this.cache.keys(pattern)))
}
} else {
// exact keys for 1-arg cache calls
keysToDelete.push(...whitelists.flatMap((whitelist) => userIds.map((id) => this.cache.genSlugKey(this.constructor.name, whitelist, id))))
// dynamic keys for optional-args cache calls
const patterns = whitelists.flatMap((whitelist) => userIds.map((id) => this.cache.genSlugKey(this.constructor.name, whitelist, id, '*')))
for (const pattern of patterns) {
keysToDelete.push(...(await this.cache.keys(pattern)))
}
}
this.logger.verbose({ tag: this.clearWhiteListCaches.name, msg: JSON.stringify(keysToDelete) })
await this.cache.mdel(keysToDelete)
} catch (e) {
this.logger.error({ tag: this.clearWhiteListCaches.name, msg: toErrorMessage(e) })
}
}
async clearWebDAVAuthCache(userIds: number | number[]): Promise<void> {
try {
const targetUserIds = new Set(Array.isArray(userIds) ? userIds : [userIds])
if (!targetUserIds.size) return
// Cache keys use a credential HMAC and cannot be rebuilt from the stored password hashes.
// Inspect cached values and remove every positive WebDAV authentication entry for the targeted users.
const keys = await this.cache.keys(`${CACHE_AUTH_WEBDAV_PREFIX}-*`)
const keysToDelete: string[] = []
for (const key of keys) {
const cachedUser: null | undefined | Partial<UserModel> = await this.cache.get(key)
if (cachedUser?.id !== undefined && targetUserIds.has(cachedUser.id)) {
keysToDelete.push(key)
}
}
if (keysToDelete.length) {
await this.cache.mdel(keysToDelete)
}
} catch (e) {
this.logger.error({ tag: this.clearWebDAVAuthCache.name, msg: toErrorMessage(e) })
}
}
async allUserIdsFromGroupsAndSubGroups(groupIds: number[]): Promise<number[]> {
if (!groupIds.length) return []
const subGroup: any = alias(groups, 'subGroup')
const withChildren: any = sql`
WITH RECURSIVE child (id, parentId) AS
(SELECT ${groups.id}, ${groups.parentId}
FROM ${groups}
WHERE ${inArray(groups.id, groupIds)}
UNION
SELECT ${subGroup.id},
${subGroup.parentId}
FROM ${groups} AS subGroup
INNER JOIN child AS cs ON ${subGroup.parentId} = cs.id)
SELECT DISTINCT ${usersGroups.userId} as userId
FROM child
INNER JOIN ${usersGroups} ON child.id = ${usersGroups.groupId}
`
const [r]: { userId: number }[][] = (await this.db.execute(withChildren)) as MySqlQueryResult
return r.map(({ userId }) => userId)
}
private async isGuestLink(userId: number): Promise<boolean> {
const [user] = await this.db.select({ role: users.role }).from(users).where(eq(users.id, userId)).limit(1)
return !user || user.role === USER_ROLE.LINK
}
private async searchGroups(
searchMembersDto: ValidatedSearchMembersDto,
userId?: number,
limit = 3
): Promise<Pick<Group, 'id' | 'name' | 'description' | 'type' | 'permissions'>[]> {
/* Search for groups */
const where: SQL[] = [or(like(groups.name, `%${searchMembersDto.search}%`), like(groups.description, `%${searchMembersDto.search}%`))!]
if (userId) {
const userRole = searchMembersDto.isGroupManager ? USER_GROUP_ROLE.MANAGER : undefined
let idsWhitelist: number[] = await this.groupsWhitelist(userId, undefined, userRole)
if (searchMembersDto.ignoreGroupIds?.length) {
idsWhitelist = idsWhitelist.filter((id) => searchMembersDto.ignoreGroupIds!.indexOf(id) === -1)
}
where.unshift(inArray(groups.id, idsWhitelist))
} else if (searchMembersDto.ignoreGroupIds?.length) {
where.unshift(notInArray(groups.id, searchMembersDto.ignoreGroupIds))
}
if (searchMembersDto.excludePersonalGroups) {
where.unshift(eq(groups.type, GROUP_TYPE.USER))
} else if (searchMembersDto.onlyPersonalGroups) {
where.unshift(eq(groups.type, GROUP_TYPE.PERSONAL))
}
return this.db
.select({ id: groups.id, name: groups.name, description: groups.description, type: groups.type, permissions: groups.permissions })
.from(groups)
.where(and(...where))
.limit(limit)
}
private async searchUsers(
searchMembersDto: ValidatedSearchMembersDto,
userId?: number,
limit = 3
): Promise<Pick<UserModel, 'id' | 'login' | 'email' | 'fullName' | 'role' | 'permissions'>[]> {
/* Search for users */
const where: SQL[] = [
ne(users.role, USER_ROLE.LINK),
or(like(sql`CONCAT_WS('-', ${users.login}, ${users.email}, ${users.firstName}, ${users.lastName})`, `%${searchMembersDto.search}%`))!
]
if (userId) {
let idsWhitelist: number[] = await this.usersWhitelist(userId)
if (searchMembersDto.ignoreUserIds?.length) {
idsWhitelist = idsWhitelist.filter((id) => searchMembersDto.ignoreUserIds!.indexOf(id) === -1)
}
where.unshift(inArray(users.id, idsWhitelist))
} else {
if (searchMembersDto.ignoreUserIds?.length) {
where.unshift(notInArray(users.id, searchMembersDto.ignoreUserIds))
}
}
if (typeof searchMembersDto.usersRole !== 'undefined') {
if (searchMembersDto.usersRole === USER_ROLE.USER) {
// allow admin users
where.unshift(lte(users.role, searchMembersDto.usersRole as USER_ROLE))
} else {
where.unshift(eq(users.role, searchMembersDto.usersRole as USER_ROLE))
}
}
return this.db
.select({
id: users.id,
login: users.login,
email: users.email,
fullName: userFullNameSQL(users),
role: users.role,
permissions: users.permissions
})
.from(users)
.where(and(...where))
.limit(limit)
}
}