wehub-resource-sync
573 lines
20 KiB
TypeScript
573 lines
20 KiB
TypeScript
import { beforeAll, beforeEach, describe, expect, it, vi } from 'vitest';
|
|
import express from 'express';
|
|
import request from 'supertest';
|
|
import { ERROR_CODES } from '@insforge/shared-schemas';
|
|
|
|
const { queryMock, connectMock, clientQueryMock, releaseMock } = vi.hoisted(() => ({
|
|
queryMock: vi.fn(),
|
|
connectMock: vi.fn(),
|
|
clientQueryMock: vi.fn(),
|
|
releaseMock: vi.fn(),
|
|
}));
|
|
|
|
vi.mock('../../src/infra/database/database.manager', () => ({
|
|
DatabaseManager: {
|
|
getInstance: vi.fn(() => ({
|
|
getPool: vi.fn(() => ({
|
|
query: queryMock,
|
|
connect: connectMock,
|
|
})),
|
|
})),
|
|
},
|
|
}));
|
|
|
|
vi.mock('../../src/api/middlewares/auth.js', () => ({
|
|
verifyAdmin: vi.fn((req, _res, next) => {
|
|
req.user = { id: 'admin-id', role: 'project_admin' };
|
|
next();
|
|
}),
|
|
}));
|
|
|
|
import { DatabaseAdvisorService } from '../../src/services/database/database-advisor.service';
|
|
import { advisorRouter } from '../../src/api/routes/advisor/index.routes';
|
|
|
|
let app: express.Express;
|
|
|
|
describe('Database Advisor Unit Tests', () => {
|
|
beforeEach(() => {
|
|
vi.clearAllMocks();
|
|
connectMock.mockResolvedValue({ query: clientQueryMock, release: releaseMock });
|
|
clientQueryMock.mockResolvedValue({ rows: [] });
|
|
queryMock.mockResolvedValue({ rows: [] });
|
|
});
|
|
|
|
describe('DatabaseAdvisorService', () => {
|
|
it('should trigger a scan and start it in background', async () => {
|
|
const service = DatabaseAdvisorService.getInstance();
|
|
clientQueryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('pg_try_advisory_lock')) {
|
|
return Promise.resolve({ rows: [{ acquired: true }] });
|
|
}
|
|
if (sql.includes('INSERT INTO system.advisor_scans')) {
|
|
return Promise.resolve({ rows: [{ id: 'scan-uuid' }] });
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
const scanId = await service.triggerScan('manual');
|
|
expect(scanId).toBe('scan-uuid');
|
|
expect(service.isScanInProgress()).toBe(true);
|
|
|
|
// Wait for background scan to finish
|
|
await vi.waitFor(() => {
|
|
expect(service.isScanInProgress()).toBe(false);
|
|
});
|
|
|
|
expect(connectMock).toHaveBeenCalled();
|
|
});
|
|
|
|
it('should reject concurrent scans with 409 Conflict', async () => {
|
|
const service = DatabaseAdvisorService.getInstance();
|
|
clientQueryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('pg_try_advisory_lock')) {
|
|
return Promise.resolve({ rows: [{ acquired: true }] });
|
|
}
|
|
if (sql.includes('INSERT INTO system.advisor_scans')) {
|
|
return Promise.resolve({ rows: [{ id: 'scan-uuid-1' }] });
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
const firstScan = await service.triggerScan('manual');
|
|
expect(firstScan).toBe('scan-uuid-1');
|
|
|
|
// For the second call, we mock pg_try_advisory_lock returning false (locked)
|
|
clientQueryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('pg_try_advisory_lock')) {
|
|
return Promise.resolve({ rows: [{ acquired: false }] });
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
await expect(service.triggerScan('manual')).rejects.toMatchObject({
|
|
statusCode: 409,
|
|
code: ERROR_CODES.DATABASE_CONSTRAINT_VIOLATION,
|
|
});
|
|
|
|
// Wait for first background scan to finish
|
|
await vi.waitFor(() => {
|
|
expect(service.isScanInProgress()).toBe(false);
|
|
});
|
|
});
|
|
|
|
it('should reset isScanning lock if DB insert fails in triggerScan', async () => {
|
|
const service = DatabaseAdvisorService.getInstance();
|
|
clientQueryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('pg_try_advisory_lock')) {
|
|
return Promise.resolve({ rows: [{ acquired: true }] });
|
|
}
|
|
if (sql.includes('INSERT INTO system.advisor_scans')) {
|
|
return Promise.reject(new Error('DB connection failure'));
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
await expect(service.triggerScan('manual')).rejects.toThrow('DB connection failure');
|
|
expect(service.isScanInProgress()).toBe(false);
|
|
});
|
|
|
|
it('should query latest scan and return summary', async () => {
|
|
const service = DatabaseAdvisorService.getInstance();
|
|
queryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('advisor_scans')) {
|
|
return Promise.resolve({
|
|
rows: [
|
|
{
|
|
id: 'scan-uuid',
|
|
status: 'completed',
|
|
scan_type: 'manual',
|
|
scanned_at: new Date('2026-06-18T10:00:00Z'),
|
|
error_message: null,
|
|
},
|
|
],
|
|
});
|
|
}
|
|
if (sql.includes('advisor_findings')) {
|
|
return Promise.resolve({
|
|
rows: [
|
|
{ severity: 'critical', count: 2 },
|
|
{ severity: 'warning', count: 3 },
|
|
],
|
|
});
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
const summary = await service.getLatestScan();
|
|
expect(summary).not.toBeNull();
|
|
expect(summary).toEqual({
|
|
scanId: 'scan-uuid',
|
|
status: 'completed',
|
|
scanType: 'manual',
|
|
scannedAt: '2026-06-18T10:00:00.000Z',
|
|
errorMessage: null,
|
|
summary: {
|
|
total: 5,
|
|
critical: 2,
|
|
warning: 3,
|
|
info: 0,
|
|
},
|
|
});
|
|
});
|
|
|
|
it('should query paginated findings/issues', async () => {
|
|
const service = DatabaseAdvisorService.getInstance();
|
|
queryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('advisor_scans')) {
|
|
return Promise.resolve({ rows: [{ id: 'scan-uuid' }] });
|
|
}
|
|
if (sql.includes('count(*)::int')) {
|
|
return Promise.resolve({ rows: [{ total: 12 }] });
|
|
}
|
|
if (sql.includes('advisor_findings')) {
|
|
return Promise.resolve({
|
|
rows: [
|
|
{
|
|
id: '1',
|
|
ruleId: 'rls-disabled',
|
|
severity: 'critical',
|
|
category: 'security',
|
|
title: 'RLS disabled',
|
|
description: 'Enable RLS',
|
|
affectedObject: 'users',
|
|
recommendation: 'ALTER TABLE',
|
|
},
|
|
],
|
|
});
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
const result = await service.getLatestScanIssues({
|
|
severity: 'critical',
|
|
limit: 10,
|
|
offset: 0,
|
|
});
|
|
|
|
expect(result.total).toBe(12);
|
|
expect(result.issues).toHaveLength(1);
|
|
expect(result.issues[0].ruleId).toBe('rls-disabled');
|
|
});
|
|
});
|
|
|
|
describe('Advisor Routes', () => {
|
|
beforeAll(() => {
|
|
app = express();
|
|
app.use(express.json());
|
|
app.use('/api/advisor', advisorRouter);
|
|
app.use(
|
|
(
|
|
err: { statusCode?: number; message?: string },
|
|
_req: express.Request,
|
|
res: express.Response,
|
|
_next: express.NextFunction
|
|
) => {
|
|
void _next;
|
|
res.status(err.statusCode || 500).json({ error: err.message });
|
|
}
|
|
);
|
|
});
|
|
|
|
it('POST /api/advisor/scan should start scan', async () => {
|
|
clientQueryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('pg_try_advisory_lock')) {
|
|
return Promise.resolve({ rows: [{ acquired: true }] });
|
|
}
|
|
if (sql.includes('INSERT INTO system.advisor_scans')) {
|
|
return Promise.resolve({ rows: [{ id: 'scan-uuid' }] });
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
const res = await request(app).post('/api/advisor/scan').expect(201);
|
|
|
|
expect(res.body).toEqual({
|
|
scanId: 'scan-uuid',
|
|
message: 'Scan started',
|
|
});
|
|
});
|
|
|
|
it('GET /api/advisor/latest should return latest scan', async () => {
|
|
queryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('advisor_scans')) {
|
|
return Promise.resolve({
|
|
rows: [
|
|
{
|
|
id: 'scan-uuid',
|
|
status: 'completed',
|
|
scan_type: 'manual',
|
|
scanned_at: new Date('2026-06-18T10:00:00Z'),
|
|
},
|
|
],
|
|
});
|
|
}
|
|
if (sql.includes('advisor_findings')) {
|
|
return Promise.resolve({
|
|
rows: [{ severity: 'critical', count: 2 }],
|
|
});
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
const res = await request(app).get('/api/advisor/latest').expect(200);
|
|
|
|
expect(res.body.scanId).toBe('scan-uuid');
|
|
expect(res.body.summary.critical).toBe(2);
|
|
});
|
|
|
|
it('GET /api/advisor/issues should return issues list', async () => {
|
|
queryMock.mockImplementation((sql: string) => {
|
|
if (sql.includes('advisor_scans')) {
|
|
return Promise.resolve({ rows: [{ id: 'scan-uuid' }] });
|
|
}
|
|
if (sql.includes('count(*)::int')) {
|
|
return Promise.resolve({ rows: [{ total: 1 }] });
|
|
}
|
|
if (sql.includes('advisor_findings')) {
|
|
return Promise.resolve({
|
|
rows: [
|
|
{
|
|
id: '1',
|
|
ruleId: 'rls-disabled',
|
|
severity: 'critical',
|
|
category: 'security',
|
|
title: 'RLS disabled',
|
|
description: 'Enable RLS',
|
|
},
|
|
],
|
|
});
|
|
}
|
|
return Promise.resolve({ rows: [] });
|
|
});
|
|
|
|
const res = await request(app)
|
|
.get('/api/advisor/issues')
|
|
.query({ severity: 'critical' })
|
|
.expect(200);
|
|
|
|
expect(res.body.total).toBe(1);
|
|
expect(res.body.issues[0].ruleId).toBe('rls-disabled');
|
|
});
|
|
|
|
it('GET /api/advisor/issues should return 400 for invalid limit or offset', async () => {
|
|
const res = await request(app).get('/api/advisor/issues').query({ limit: 'abc' }).expect(400);
|
|
|
|
expect(res.body.error).toContain('Invalid limit parameter');
|
|
|
|
const res2 = await request(app)
|
|
.get('/api/advisor/issues')
|
|
.query({ offset: '-5' })
|
|
.expect(400);
|
|
|
|
expect(res2.body.error).toContain('Invalid offset parameter');
|
|
});
|
|
|
|
it('GET /api/advisor/issues should return 400 for non-integer limit/offset', async () => {
|
|
const res = await request(app).get('/api/advisor/issues').query({ limit: '1.5' }).expect(400);
|
|
expect(res.body.error).toContain('Invalid limit parameter');
|
|
|
|
const res2 = await request(app)
|
|
.get('/api/advisor/issues')
|
|
.query({ offset: '2abc' })
|
|
.expect(400);
|
|
expect(res2.body.error).toContain('Invalid offset parameter');
|
|
});
|
|
|
|
it('GET /api/advisor/issues should return 400 for invalid severity or category', async () => {
|
|
const res = await request(app)
|
|
.get('/api/advisor/issues')
|
|
.query({ severity: 'urgent' })
|
|
.expect(400);
|
|
expect(res.body.error).toContain('Invalid severity parameter');
|
|
|
|
const res2 = await request(app)
|
|
.get('/api/advisor/issues')
|
|
.query({ category: 'networking' })
|
|
.expect(400);
|
|
expect(res2.body.error).toContain('Invalid category parameter');
|
|
});
|
|
|
|
it('GET /api/advisor/suppressions should return suppressions list', async () => {
|
|
vi.spyOn(DatabaseAdvisorService.getInstance(), 'listSuppressions').mockResolvedValue([
|
|
{
|
|
id: '11111111-1111-1111-1111-111111111111',
|
|
ruleId: 'dangerous-function',
|
|
affectedObject: 'public.f()',
|
|
scope: 'instance',
|
|
reason: 'false_positive',
|
|
note: null,
|
|
createdAt: new Date().toISOString(),
|
|
},
|
|
]);
|
|
|
|
const res = await request(app).get('/api/advisor/suppressions').expect(200);
|
|
|
|
expect(res.body.suppressions).toHaveLength(1);
|
|
expect(res.body.suppressions[0].ruleId).toBe('dangerous-function');
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 400 on bad scope/reason', async () => {
|
|
const bad1 = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'x', scope: 'nope', reason: 'false_positive', affectedObject: 'y' })
|
|
.expect(400);
|
|
expect(bad1.body.error).toContain('Invalid scope');
|
|
|
|
const bad2 = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'x', scope: 'instance', reason: 'nope', affectedObject: 'y' })
|
|
.expect(400);
|
|
expect(bad2.body.error).toContain('Invalid reason');
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 400 when reason is "other" without a note', async () => {
|
|
const missing = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'x', scope: 'rule', reason: 'other' })
|
|
.expect(400);
|
|
expect(missing.body.error).toContain('required when reason is "other"');
|
|
|
|
// Whitespace-only note must also be rejected (trim-based check).
|
|
const whitespace = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'x', scope: 'rule', reason: 'other', note: ' ' })
|
|
.expect(400);
|
|
expect(whitespace.body.error).toContain('required when reason is "other"');
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 201 when reason is "other" with a note', async () => {
|
|
const createSpy = vi
|
|
.spyOn(DatabaseAdvisorService.getInstance(), 'createSuppression')
|
|
.mockResolvedValue({
|
|
id: '66666666-6666-6666-6666-666666666666',
|
|
ruleId: 'rls-select-only',
|
|
affectedObject: null,
|
|
scope: 'rule',
|
|
reason: 'other',
|
|
note: 'team decision',
|
|
createdBy: 'admin-id',
|
|
createdAt: new Date().toISOString(),
|
|
});
|
|
|
|
const res = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'rls-select-only', scope: 'rule', reason: 'other', note: 'team decision' })
|
|
.expect(201);
|
|
expect(res.body.reason).toBe('other');
|
|
// The note must actually be forwarded to the service, not dropped.
|
|
expect(createSpy).toHaveBeenCalledWith(
|
|
expect.objectContaining({ reason: 'other', note: 'team decision' })
|
|
);
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 400 when instance scope lacks affectedObject', async () => {
|
|
const res = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'x', scope: 'instance', reason: 'wont_fix' })
|
|
.expect(400);
|
|
expect(res.body.error).toContain('Invalid affectedObject');
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 201 on valid rule-scope body', async () => {
|
|
vi.spyOn(DatabaseAdvisorService.getInstance(), 'createSuppression').mockResolvedValue({
|
|
id: '22222222-2222-2222-2222-222222222222',
|
|
ruleId: 'rls-select-only',
|
|
affectedObject: null,
|
|
scope: 'rule',
|
|
reason: 'accepted_risk',
|
|
note: null,
|
|
createdAt: new Date().toISOString(),
|
|
});
|
|
|
|
const res = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'rls-select-only', scope: 'rule', reason: 'accepted_risk' })
|
|
.expect(201);
|
|
|
|
expect(res.body.id).toBe('22222222-2222-2222-2222-222222222222');
|
|
expect(res.body.scope).toBe('rule');
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 201 on instance scope and pass through the fingerprint', async () => {
|
|
const createSpy = vi
|
|
.spyOn(DatabaseAdvisorService.getInstance(), 'createSuppression')
|
|
.mockResolvedValue({
|
|
id: '44444444-4444-4444-4444-444444444444',
|
|
ruleId: 'dangerous-function',
|
|
affectedObject: 'public.f()',
|
|
scope: 'instance',
|
|
reason: 'false_positive',
|
|
note: null,
|
|
createdBy: 'admin-id',
|
|
createdAt: new Date().toISOString(),
|
|
});
|
|
|
|
const res = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
// Leading/trailing whitespace on ruleId must be trimmed before persisting.
|
|
.send({
|
|
ruleId: ' dangerous-function ',
|
|
scope: 'instance',
|
|
reason: 'false_positive',
|
|
affectedObject: 'public.f()',
|
|
note: null,
|
|
})
|
|
.expect(201);
|
|
|
|
expect(res.body.scope).toBe('instance');
|
|
expect(createSpy).toHaveBeenCalledWith({
|
|
ruleId: 'dangerous-function',
|
|
affectedObject: 'public.f()',
|
|
scope: 'instance',
|
|
reason: 'false_positive',
|
|
note: null,
|
|
createdBy: 'admin-id',
|
|
});
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should 400 when instance affectedObject is whitespace-only', async () => {
|
|
const res = await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
.send({ ruleId: 'x', scope: 'instance', reason: 'wont_fix', affectedObject: ' ' })
|
|
.expect(400);
|
|
expect(res.body.error).toContain('Invalid affectedObject');
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should store affectedObject verbatim (no trimming)', async () => {
|
|
const createSpy = vi
|
|
.spyOn(DatabaseAdvisorService.getInstance(), 'createSuppression')
|
|
.mockResolvedValue({
|
|
id: '77777777-7777-7777-7777-777777777777',
|
|
ruleId: 'rls-disabled',
|
|
affectedObject: ' public.spaced ',
|
|
scope: 'instance',
|
|
reason: 'false_positive',
|
|
note: null,
|
|
createdBy: 'admin-id',
|
|
createdAt: new Date().toISOString(),
|
|
});
|
|
|
|
await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
// Leading/trailing spaces are significant: advisor findings build
|
|
// affected_object from catalog identifiers without trimming, so a
|
|
// trimmed store would never match on re-scan.
|
|
.send({
|
|
ruleId: 'rls-disabled',
|
|
scope: 'instance',
|
|
reason: 'false_positive',
|
|
affectedObject: ' public.spaced ',
|
|
})
|
|
.expect(201);
|
|
expect(createSpy).toHaveBeenCalledWith(
|
|
expect.objectContaining({ affectedObject: ' public.spaced ' })
|
|
);
|
|
});
|
|
|
|
it('POST /api/advisor/suppressions should null out affectedObject for rule scope', async () => {
|
|
const createSpy = vi
|
|
.spyOn(DatabaseAdvisorService.getInstance(), 'createSuppression')
|
|
.mockResolvedValue({
|
|
id: '55555555-5555-5555-5555-555555555555',
|
|
ruleId: 'rls-select-only',
|
|
affectedObject: null,
|
|
scope: 'rule',
|
|
reason: 'accepted_risk',
|
|
note: null,
|
|
createdBy: 'admin-id',
|
|
createdAt: new Date().toISOString(),
|
|
});
|
|
|
|
await request(app)
|
|
.post('/api/advisor/suppressions')
|
|
// A stray affectedObject on a rule-scope request must be dropped.
|
|
.send({
|
|
ruleId: 'rls-select-only',
|
|
scope: 'rule',
|
|
reason: 'accepted_risk',
|
|
affectedObject: 'ignored.for.rule.scope',
|
|
})
|
|
.expect(201);
|
|
|
|
expect(createSpy).toHaveBeenCalledWith(
|
|
expect.objectContaining({ scope: 'rule', affectedObject: null })
|
|
);
|
|
});
|
|
|
|
it('DELETE /api/advisor/suppressions/:id should 200 with { deleted: true } on success', async () => {
|
|
vi.spyOn(DatabaseAdvisorService.getInstance(), 'deleteSuppression').mockResolvedValue(true);
|
|
|
|
const res = await request(app)
|
|
.delete('/api/advisor/suppressions/33333333-3333-3333-3333-333333333333')
|
|
.expect(200);
|
|
expect(res.body.deleted).toBe(true);
|
|
});
|
|
|
|
it('DELETE /api/advisor/suppressions/:id should 400 on a malformed (non-UUID) id', async () => {
|
|
const deleteSpy = vi.spyOn(DatabaseAdvisorService.getInstance(), 'deleteSuppression');
|
|
|
|
const res = await request(app).delete('/api/advisor/suppressions/not-a-uuid').expect(400);
|
|
expect(res.body.error).toContain('Invalid id');
|
|
expect(deleteSpy).not.toHaveBeenCalled();
|
|
});
|
|
|
|
it('DELETE /api/advisor/suppressions/:id should 404 when missing', async () => {
|
|
vi.spyOn(DatabaseAdvisorService.getInstance(), 'deleteSuppression').mockResolvedValue(false);
|
|
|
|
const res = await request(app)
|
|
.delete('/api/advisor/suppressions/33333333-3333-3333-3333-333333333333')
|
|
.expect(404);
|
|
expect(res.body.error).toBe('Suppression not found');
|
|
});
|
|
});
|
|
});
|