427 lines
15 KiB
TypeScript
427 lines
15 KiB
TypeScript
import { mkdtempSync, rmSync } from 'node:fs'
|
|
import { tmpdir } from 'node:os'
|
|
import { join } from 'node:path'
|
|
import Database from 'better-sqlite3'
|
|
import { afterEach, describe, expect, it } from 'vitest'
|
|
import {
|
|
SCHEMA_VERSION,
|
|
appendAudit,
|
|
appendAuditBatch,
|
|
closeDb,
|
|
consumeOidcAuthCode,
|
|
countWebauthnCredentials,
|
|
createDb,
|
|
createOidcAuthCode,
|
|
createRefreshSession,
|
|
createUser,
|
|
createWebauthnCredential,
|
|
getActiveOidcSigningKey,
|
|
getUserApps,
|
|
getUserById,
|
|
getUserPermissions,
|
|
insertOidcSigningKey,
|
|
listActiveSessions,
|
|
listAudit,
|
|
migrateSchema,
|
|
revokeSessionById,
|
|
runHousekeeping,
|
|
searchDirectoryUsers,
|
|
setUserAccess,
|
|
withTransaction,
|
|
type AppDb,
|
|
type Sqlite,
|
|
} from '../src/index.js'
|
|
|
|
const open: Sqlite[] = []
|
|
const dirs: string[] = []
|
|
|
|
function memDb(): { db: AppDb; sqlite: Sqlite } {
|
|
const r = createDb('sqlite::memory:')
|
|
migrateSchema(r.sqlite)
|
|
open.push(r.sqlite)
|
|
return r
|
|
}
|
|
|
|
afterEach(() => {
|
|
for (const s of open.splice(0)) closeDb(s)
|
|
for (const d of dirs.splice(0)) rmSync(d, { recursive: true, force: true })
|
|
})
|
|
|
|
function mkUser(db: AppDb, email: string, extra: { name?: string } = {}) {
|
|
return createUser(db, {
|
|
email,
|
|
name: extra.name ?? email.split('@')[0]!,
|
|
passwordHash: 'x',
|
|
isAdmin: false,
|
|
})
|
|
}
|
|
|
|
describe('createDb / closeDb', () => {
|
|
it('выставляет pragma на файловой БД', () => {
|
|
const dir = mkdtempSync(join(tmpdir(), 'ap-db-'))
|
|
dirs.push(dir)
|
|
const { sqlite } = createDb(join(dir, 'app.db'))
|
|
open.push(sqlite)
|
|
expect(sqlite.pragma('journal_mode', { simple: true })).toBe('wal')
|
|
expect(sqlite.pragma('busy_timeout', { simple: true })).toBe(5000)
|
|
expect(sqlite.pragma('synchronous', { simple: true })).toBe(1)
|
|
expect(sqlite.pragma('temp_store', { simple: true })).toBe(2)
|
|
expect(sqlite.pragma('cache_size', { simple: true })).toBe(-16000)
|
|
expect(sqlite.pragma('foreign_keys', { simple: true })).toBe(1)
|
|
})
|
|
|
|
it('closeDb идемпотентен', () => {
|
|
const { sqlite } = memDb()
|
|
closeDb(sqlite)
|
|
expect(sqlite.open).toBe(false)
|
|
expect(() => closeDb(sqlite)).not.toThrow()
|
|
})
|
|
})
|
|
|
|
describe('migrateSchema', () => {
|
|
it('ставит user_version и повторный запуск — no-op', () => {
|
|
const { sqlite } = memDb()
|
|
expect(sqlite.pragma('user_version', { simple: true })).toBe(SCHEMA_VERSION)
|
|
expect(() => migrateSchema(sqlite)).not.toThrow()
|
|
})
|
|
|
|
it('создаёт новые индексы', () => {
|
|
const { sqlite } = memDb()
|
|
const names = (
|
|
sqlite
|
|
.prepare(`SELECT name FROM sqlite_master WHERE type='index'`)
|
|
.all() as Array<{ name: string }>
|
|
).map((r) => r.name)
|
|
for (const n of [
|
|
'uq_user_apps',
|
|
'uq_user_permissions',
|
|
'idx_refresh_sessions_expires',
|
|
'idx_oidc_auth_codes_expires',
|
|
'idx_audit_log_severity',
|
|
'idx_audit_log_actor_email',
|
|
]) {
|
|
expect(names).toContain(n)
|
|
}
|
|
})
|
|
|
|
it('мигрирует «старую» схему: дедупликация и недостающие колонки', () => {
|
|
const sqlite = new Database(':memory:')
|
|
open.push(sqlite)
|
|
sqlite.pragma('foreign_keys = ON')
|
|
sqlite.exec(`
|
|
CREATE TABLE users (
|
|
id TEXT PRIMARY KEY NOT NULL, email TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL, password_hash TEXT NOT NULL,
|
|
is_admin INTEGER NOT NULL DEFAULT 0, disabled INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL, updated_at TEXT NOT NULL
|
|
);
|
|
CREATE TABLE user_apps (user_id TEXT NOT NULL, app_id TEXT NOT NULL);
|
|
CREATE TABLE user_permissions (user_id TEXT NOT NULL, permission TEXT NOT NULL);
|
|
INSERT INTO users VALUES ('u1','a@b.c','A','h',0,0,'t','t');
|
|
INSERT INTO user_apps VALUES ('u1','dns'),('u1','dns'),('u1','vps');
|
|
INSERT INTO user_permissions VALUES ('u1','p'),('u1','p');
|
|
`)
|
|
migrateSchema(sqlite)
|
|
expect(
|
|
sqlite.prepare('SELECT COUNT(*) c FROM user_apps').get(),
|
|
).toEqual({ c: 2 })
|
|
expect(
|
|
sqlite.prepare('SELECT COUNT(*) c FROM user_permissions').get(),
|
|
).toEqual({ c: 1 })
|
|
const cols = (
|
|
sqlite.prepare('PRAGMA table_info(users)').all() as Array<{ name: string }>
|
|
).map((c) => c.name)
|
|
expect(cols).toContain('last_login_at')
|
|
expect(sqlite.pragma('user_version', { simple: true })).toBe(SCHEMA_VERSION)
|
|
expect(() =>
|
|
sqlite.prepare(`INSERT INTO user_apps VALUES ('u1','dns')`).run(),
|
|
).toThrow(/UNIQUE/)
|
|
})
|
|
|
|
it('откатывается целиком при ошибке', () => {
|
|
const sqlite = new Database(':memory:')
|
|
open.push(sqlite)
|
|
// users — это view-подобная помеха: таблица с несовместимой схемой user_apps без user_id
|
|
sqlite.exec(`CREATE TABLE user_apps (foo TEXT)`)
|
|
expect(() => migrateSchema(sqlite)).toThrow()
|
|
expect(sqlite.pragma('user_version', { simple: true })).toBe(0)
|
|
})
|
|
})
|
|
|
|
describe('withTransaction / setUserAccess', () => {
|
|
it('откатывает все изменения при исключении', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
setUserAccess(db, u.id, ['dns'], ['p1'])
|
|
expect(() =>
|
|
withTransaction(db, () => {
|
|
setUserAccess(db, u.id, ['vps', 'bgp'], ['p2'])
|
|
throw new Error('boom')
|
|
}),
|
|
).toThrow('boom')
|
|
expect(getUserApps(db, u.id)).toEqual(['dns'])
|
|
expect(getUserPermissions(db, u.id)).toEqual(['p1'])
|
|
})
|
|
|
|
it('схлопывает дубликаты во входе', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
setUserAccess(db, u.id, ['dns', 'dns'], ['p', 'p'])
|
|
expect(getUserApps(db, u.id)).toEqual(['dns'])
|
|
expect(getUserPermissions(db, u.id)).toEqual(['p'])
|
|
})
|
|
|
|
it('вложенная транзакция откатывается как savepoint', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
withTransaction(db, () => {
|
|
setUserAccess(db, u.id, ['dns'], [])
|
|
try {
|
|
withTransaction(db, () => {
|
|
setUserAccess(db, u.id, ['vps'], [])
|
|
throw new Error('inner')
|
|
})
|
|
} catch {
|
|
/* ожидаемо */
|
|
}
|
|
})
|
|
expect(getUserApps(db, u.id)).toEqual(['dns'])
|
|
})
|
|
})
|
|
|
|
describe('сессии и каталог', () => {
|
|
it('listActiveSessions: только активные, фильтр по пользователю, сортировка', () => {
|
|
const { db } = memDb()
|
|
const a = mkUser(db, 'a@x.io')
|
|
const b = mkUser(db, 'b@x.io')
|
|
const future = new Date(Date.now() + 3600_000)
|
|
const past = new Date(Date.now() - 3600_000)
|
|
const s1 = createRefreshSession(db, a.id, 't1', future)
|
|
createRefreshSession(db, a.id, 't2', past)
|
|
const s3 = createRefreshSession(db, b.id, 't3', future)
|
|
const s4 = createRefreshSession(db, b.id, 't4', future)
|
|
revokeSessionById(db, s4)
|
|
|
|
const all = listActiveSessions(db).map((s) => s.id)
|
|
expect(new Set(all)).toEqual(new Set([s1, s3]))
|
|
expect(listActiveSessions(db, { userId: a.id }).map((s) => s.id)).toEqual([
|
|
s1,
|
|
])
|
|
})
|
|
|
|
it('searchDirectoryUsers: без passwordHash, без disabled, LIMIT, экранирование', () => {
|
|
const { db } = memDb()
|
|
for (let i = 0; i < 60; i++) mkUser(db, `user${i}@x.io`)
|
|
const weird = mkUser(db, 'pct@x.io', { name: '100%' })
|
|
const dis = mkUser(db, 'off@x.io')
|
|
db.$client.prepare('UPDATE users SET disabled=1 WHERE id=?').run(dis.id)
|
|
|
|
const page = searchDirectoryUsers(db, '', 50)
|
|
expect(page).toHaveLength(50)
|
|
expect(Object.keys(page[0]!).sort()).toEqual(['email', 'id', 'name'])
|
|
expect(searchDirectoryUsers(db, 'off@x.io')).toEqual([])
|
|
expect(searchDirectoryUsers(db, 'USER7@').map((u) => u.email)).toEqual([
|
|
'user7@x.io',
|
|
])
|
|
expect(searchDirectoryUsers(db, '%').map((u) => u.id)).toEqual([weird.id])
|
|
expect(searchDirectoryUsers(db, weird.id)).toHaveLength(1)
|
|
})
|
|
|
|
it('countWebauthnCredentials считает COUNT(*)', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
expect(countWebauthnCredentials(db, u.id)).toBe(0)
|
|
for (const id of ['c1', 'c2']) {
|
|
createWebauthnCredential(db, {
|
|
userId: u.id,
|
|
credentialId: id,
|
|
publicKey: 'k',
|
|
counter: 0,
|
|
name: id,
|
|
})
|
|
}
|
|
expect(countWebauthnCredentials(db, u.id)).toBe(2)
|
|
})
|
|
})
|
|
|
|
describe('OIDC', () => {
|
|
function code(db: AppDb, userId: string, raw: string, expiresAt: Date) {
|
|
createOidcAuthCode(db, {
|
|
rawCode: raw,
|
|
clientId: 'c',
|
|
userId,
|
|
redirectUri: 'https://x/cb',
|
|
scope: 'openid',
|
|
expiresAt,
|
|
})
|
|
}
|
|
|
|
it('consumeOidcAuthCode: одноразовый, просроченный отклоняется', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
code(db, u.id, 'ok', new Date(Date.now() + 60_000))
|
|
code(db, u.id, 'old', new Date(Date.now() - 60_000))
|
|
expect(consumeOidcAuthCode(db, 'ok')?.userId).toBe(u.id)
|
|
expect(consumeOidcAuthCode(db, 'ok')).toBeUndefined()
|
|
expect(consumeOidcAuthCode(db, 'old')).toBeUndefined()
|
|
expect(consumeOidcAuthCode(db, 'missing')).toBeUndefined()
|
|
})
|
|
|
|
it('insertOidcSigningKey оставляет один активный ключ — новый', () => {
|
|
const { db } = memDb()
|
|
insertOidcSigningKey(db, { kid: 'k1', privatePem: 'p', publicJwkJson: '{}' })
|
|
insertOidcSigningKey(db, { kid: 'k2', privatePem: 'p', publicJwkJson: '{}' })
|
|
expect(getActiveOidcSigningKey(db)?.kid).toBe('k2')
|
|
const active = db.$client
|
|
.prepare('SELECT COUNT(*) c FROM oidc_signing_keys WHERE active=1')
|
|
.get()
|
|
expect(active).toEqual({ c: 1 })
|
|
})
|
|
})
|
|
|
|
describe('audit', () => {
|
|
it('appendAudit: дубликат event_id → false', () => {
|
|
const { db } = memDb()
|
|
expect(appendAudit(db, { eventId: 'e1', action: 'a', summary: 's' })).toBe(
|
|
true,
|
|
)
|
|
expect(appendAudit(db, { eventId: 'e1', action: 'a', summary: 's' })).toBe(
|
|
false,
|
|
)
|
|
expect(appendAudit(db, { action: 'a', summary: 's' })).toBe(true)
|
|
expect(appendAudit(db, { action: 'a', summary: 's' })).toBe(true)
|
|
})
|
|
|
|
it('appendAuditBatch: принимает, пропускает дубли, не теряет остальные', () => {
|
|
const { db } = memDb()
|
|
const events = Array.from({ length: 500 }, (_, i) => ({
|
|
eventId: `e${i % 400}`,
|
|
action: 'x.y',
|
|
summary: 's',
|
|
}))
|
|
const res = appendAuditBatch(db, events)
|
|
expect(res).toEqual({ accepted: 400, duplicates: 100, failed: 0 })
|
|
expect(listAudit(db, { limit: 1000 })).toHaveLength(400)
|
|
})
|
|
|
|
it('listAudit kind=logins/changes делит по префиксу auth.', () => {
|
|
const { db } = memDb()
|
|
appendAudit(db, { action: 'auth.login', summary: 's' })
|
|
appendAudit(db, { action: 'auth.login_failed', summary: 's' })
|
|
appendAudit(db, { action: 'user.create', summary: 's' })
|
|
appendAudit(db, { action: 'audit.purge', summary: 's' })
|
|
appendAudit(db, { action: 'authz.other', summary: 's' })
|
|
const logins = listAudit(db, { kind: 'logins' }).map((e) => e.action)
|
|
const changes = listAudit(db, { kind: 'changes' }).map((e) => e.action)
|
|
expect(logins.sort()).toEqual(['auth.login', 'auth.login_failed'])
|
|
expect(changes.sort()).toEqual(['audit.purge', 'authz.other', 'user.create'])
|
|
})
|
|
})
|
|
|
|
describe('runHousekeeping', () => {
|
|
it('удаляет старые истёкшие/отозванные, оставляя активные и свежие', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
const day = 86_400_000
|
|
const active = createRefreshSession(db, u.id, 'a', new Date(Date.now() + day))
|
|
createRefreshSession(db, u.id, 'oldexp', new Date(Date.now() - 10 * day))
|
|
createRefreshSession(db, u.id, 'freshexp', new Date(Date.now() - day))
|
|
const oldRevoked = createRefreshSession(
|
|
db,
|
|
u.id,
|
|
'oldrev',
|
|
new Date(Date.now() + day),
|
|
)
|
|
db.$client
|
|
.prepare('UPDATE refresh_sessions SET revoked_at=? WHERE id=?')
|
|
.run(new Date(Date.now() - 10 * day).toISOString(), oldRevoked)
|
|
|
|
createOidcAuthCode(db, {
|
|
rawCode: 'stale',
|
|
clientId: 'c',
|
|
userId: u.id,
|
|
redirectUri: 'r',
|
|
scope: 's',
|
|
expiresAt: new Date(Date.now() - 10 * day),
|
|
})
|
|
createOidcAuthCode(db, {
|
|
rawCode: 'live',
|
|
clientId: 'c',
|
|
userId: u.id,
|
|
redirectUri: 'r',
|
|
scope: 's',
|
|
expiresAt: new Date(Date.now() + 60_000),
|
|
})
|
|
db.$client
|
|
.prepare(
|
|
`INSERT INTO webauthn_challenges (id,purpose,challenge,expires_at,created_at)
|
|
VALUES ('c1','register','x',?,?)`,
|
|
)
|
|
.run(new Date(Date.now() - 1000).toISOString(), new Date().toISOString())
|
|
|
|
const res = runHousekeeping(db, { retentionDays: 7 })
|
|
expect(res).toEqual({ sessions: 2, authCodes: 1, challenges: 1 })
|
|
const left = db.$client
|
|
.prepare('SELECT id FROM refresh_sessions')
|
|
.all() as Array<{ id: string }>
|
|
expect(left).toHaveLength(2)
|
|
expect(left.map((r) => r.id)).toContain(active)
|
|
|
|
expect(runHousekeeping(db, { retentionDays: 7 })).toEqual({
|
|
sessions: 0,
|
|
authCodes: 0,
|
|
challenges: 0,
|
|
})
|
|
})
|
|
|
|
it('удаляет пачками > 1000 строк', () => {
|
|
const { db } = memDb()
|
|
const u = mkUser(db, 'a@x.io')
|
|
const ins = db.$client.prepare(
|
|
`INSERT INTO refresh_sessions (id,user_id,token_hash,expires_at,created_at)
|
|
VALUES (?,?,?,?,?)`,
|
|
)
|
|
db.$client.transaction(() => {
|
|
for (let i = 0; i < 2500; i++) {
|
|
ins.run(`s${i}`, u.id, `h${i}`, '2000-01-01T00:00:00.000Z', 't')
|
|
}
|
|
})()
|
|
expect(runHousekeeping(db, { retentionDays: 1 }).sessions).toBe(2500)
|
|
})
|
|
})
|
|
|
|
describe('подготовленные запросы', () => {
|
|
it('горячие запросы не вызывают prepare повторно и дают прежние результаты', () => {
|
|
const { db, sqlite } = memDb()
|
|
const u = mkUser(db, 'p@x.io')
|
|
setUserAccess(db, u.id, ['dns', 'vps'], ['dns:dns:read'])
|
|
// прогрев: первая подготовка
|
|
getUserById(db, u.id)
|
|
getUserApps(db, u.id)
|
|
getUserPermissions(db, u.id)
|
|
|
|
const calls: string[] = []
|
|
const orig = sqlite.prepare.bind(sqlite)
|
|
sqlite.prepare = ((sql: string) => {
|
|
calls.push(sql)
|
|
return orig(sql)
|
|
}) as typeof sqlite.prepare
|
|
|
|
for (let i = 0; i < 20; i++) {
|
|
expect(getUserById(db, u.id)?.email).toBe('p@x.io')
|
|
expect(getUserApps(db, u.id).sort()).toEqual(['dns', 'vps'])
|
|
expect(getUserPermissions(db, u.id)).toEqual(['dns:dns:read'])
|
|
}
|
|
expect(getUserById(db, 'missing')).toBeUndefined()
|
|
expect(calls).toHaveLength(0)
|
|
})
|
|
|
|
it('независимые соединения не делят подготовленные запросы', () => {
|
|
const a = memDb()
|
|
const b = memDb()
|
|
const ua = mkUser(a.db, 'a@x.io')
|
|
expect(getUserById(a.db, ua.id)).toBeDefined()
|
|
expect(getUserById(b.db, ua.id)).toBeUndefined()
|
|
})
|
|
})
|