Ильяс Султанов
67b71ffd0e
Основная отсечка прав — на уровне пользователя внешней БД (GRANT SELECT); allowlist в подключении теперь опциональное дополнение (было обязательным).
326 lines
14 KiB
TypeScript
326 lines
14 KiB
TypeScript
// Внешние подключения к БД: реестр (admin) + read-only SQL-шлюз (finance.manage).
|
||
// Назначение: отчёты и виджеты (в т.ч. построенные ИИ-агентами через custom pages)
|
||
// читают внешние витрины данных без новых серверных эндпоинтов и без redeploy.
|
||
//
|
||
// Безопасность:
|
||
// - пароль шифруется AES-256-GCM, по API не отдаётся (только маска);
|
||
// - шлюз: только SELECT, без мульти-выражений, принудительный LIMIT (обёртка подзапросом),
|
||
// statement_timeout 10 с; опциональный allowlist таблиц (пустой = все таблицы);
|
||
// - каждый запрос пишется в external_db_query_log (кто/когда/SQL/строки/мс);
|
||
// - основной рубеж — пользователь внешней БД с реальным GRANT SELECT ( defence in depth:
|
||
// шлюз не заменяет права БД).
|
||
|
||
import type { Express, Response } from "express";
|
||
import { Router } from "express";
|
||
import { Pool, type PoolConfig } from "pg";
|
||
import { createCipheriv, createDecipheriv, randomBytes, createHash } from "crypto";
|
||
import { db } from "../db";
|
||
import { sql } from "drizzle-orm";
|
||
import { authenticateToken, requirePermission, type AuthenticatedRequest } from "../middleware/auth.middleware";
|
||
|
||
const MAX_ROWS = 5000;
|
||
const STATEMENT_TIMEOUT_MS = 10000;
|
||
|
||
function encryptionKey(): Buffer {
|
||
const secret = process.env.EXTERNAL_DB_SECRET || process.env.API_KEY_HMAC_SECRET || "";
|
||
if (!secret) {
|
||
console.warn("[external-db] EXTERNAL_DB_SECRET не задан — используется небезопасный dev-ключ");
|
||
}
|
||
return createHash("sha256").update(secret || "iistwin-external-db-dev-key").digest();
|
||
}
|
||
|
||
function encryptPassword(plain: string): string {
|
||
const iv = randomBytes(12);
|
||
const cipher = createCipheriv("aes-256-gcm", encryptionKey(), iv);
|
||
const enc = Buffer.concat([cipher.update(plain, "utf8"), cipher.final()]);
|
||
const tag = cipher.getAuthTag();
|
||
return `${iv.toString("base64")}.${tag.toString("base64")}.${enc.toString("base64")}`;
|
||
}
|
||
|
||
function decryptPassword(stored: string): string {
|
||
const [ivB64, tagB64, dataB64] = stored.split(".");
|
||
if (!ivB64 || !tagB64 || !dataB64) throw new Error("Некорректный формат зашифрованного пароля");
|
||
const decipher = createDecipheriv("aes-256-gcm", encryptionKey(), Buffer.from(ivB64, "base64"));
|
||
decipher.setAuthTag(Buffer.from(tagB64, "base64"));
|
||
return Buffer.concat([decipher.update(Buffer.from(dataB64, "base64")), decipher.final()]).toString("utf8");
|
||
}
|
||
|
||
interface ConnRow {
|
||
id: number;
|
||
organization_id: number;
|
||
name: string;
|
||
host: string;
|
||
port: number;
|
||
database: string;
|
||
username: string;
|
||
password_encrypted: string;
|
||
ssl: boolean;
|
||
allowed_tables: string[];
|
||
is_active: boolean;
|
||
created_at: string;
|
||
updated_at: string;
|
||
}
|
||
|
||
const pools = new Map<number, Pool>();
|
||
|
||
function getPool(conn: ConnRow): Pool {
|
||
let pool = pools.get(conn.id);
|
||
if (!pool) {
|
||
const cfg: PoolConfig = {
|
||
host: conn.host,
|
||
port: conn.port,
|
||
database: conn.database,
|
||
user: conn.username,
|
||
password: decryptPassword(conn.password_encrypted),
|
||
ssl: conn.ssl ? { rejectUnauthorized: false } : false,
|
||
max: 3,
|
||
connectionTimeoutMillis: 8000,
|
||
statement_timeout: STATEMENT_TIMEOUT_MS,
|
||
} as PoolConfig;
|
||
pool = new Pool(cfg);
|
||
pool.on("error", (err) => console.error(`[external-db] pool ${conn.id} error:`, err.message));
|
||
pools.set(conn.id, pool);
|
||
}
|
||
return pool;
|
||
}
|
||
|
||
function dropPool(connId: number): void {
|
||
const pool = pools.get(connId);
|
||
if (pool) {
|
||
pools.delete(connId);
|
||
pool.end().catch(() => {});
|
||
}
|
||
}
|
||
|
||
// --- Валидация SQL для read-only шлюза ---
|
||
|
||
// Вырезаем строковые литералы, чтобы ключевые слова внутри них не ломали проверки
|
||
function stripStringLiterals(sqlText: string): string {
|
||
return sqlText.replace(/'([^']|'')*'/g, "''");
|
||
}
|
||
|
||
function validateReadOnly(rawSql: unknown): string {
|
||
if (typeof rawSql !== "string" || !rawSql.trim()) {
|
||
throw new Error("SQL запрос не задан");
|
||
}
|
||
const cleaned = rawSql.trim().replace(/;+\s*$/, "");
|
||
if (!/^select\b/i.test(cleaned)) {
|
||
throw new Error("Только SELECT-запросы");
|
||
}
|
||
if (/;\s*\S/.test(cleaned)) {
|
||
throw new Error("Не допускается несколько выражений в одном запросе");
|
||
}
|
||
const noStrings = stripStringLiterals(cleaned);
|
||
const forbidden = /\b(insert|update|delete|drop|alter|truncate|grant|revoke|create|copy|call|execute|do|merge|vacuum|cluster|reindex|refresh)\b/i;
|
||
if (forbidden.test(noStrings)) {
|
||
throw new Error("Запрещённое ключевое слово в запросе");
|
||
}
|
||
return cleaned;
|
||
}
|
||
|
||
function extractTableNames(sqlText: string): string[] {
|
||
const names = new Set<string>();
|
||
const re = /\b(?:from|join)\s+([a-zA-Z_][\w$]*(?:\.[a-zA-Z_][\w$]*)?)/gi;
|
||
let m: RegExpExecArray | null;
|
||
while ((m = re.exec(sqlText)) !== null) {
|
||
const parts = m[1].split(".");
|
||
names.add(parts[parts.length - 1].toLowerCase());
|
||
}
|
||
return [...names];
|
||
}
|
||
|
||
function toPublicConn(row: ConnRow) {
|
||
return {
|
||
id: row.id,
|
||
name: row.name,
|
||
host: row.host,
|
||
port: row.port,
|
||
database: row.database,
|
||
username: row.username,
|
||
hasPassword: true,
|
||
ssl: row.ssl,
|
||
allowedTables: row.allowed_tables ?? [],
|
||
isActive: row.is_active,
|
||
createdAt: row.created_at,
|
||
updatedAt: row.updated_at,
|
||
};
|
||
}
|
||
|
||
export function registerExternalDbRoutes(app: Express): void {
|
||
const r = Router();
|
||
app.use("/api/external-db", r);
|
||
|
||
// Управление подключениями — только admin приложения
|
||
r.use("/connections", authenticateToken, requireAppAdmin);
|
||
|
||
r.get("/connections", async (req: AuthenticatedRequest, res: Response) => {
|
||
try {
|
||
const orgId = req.user!.organizationId;
|
||
const rows = await db.execute(sql`
|
||
SELECT * FROM external_db_connections
|
||
WHERE organization_id = ${orgId}
|
||
ORDER BY id
|
||
`);
|
||
res.json((rows.rows as unknown as ConnRow[]).map(toPublicConn));
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ error: "Ошибка базы данных" });
|
||
}
|
||
});
|
||
|
||
r.post("/connections", async (req: AuthenticatedRequest, res: Response) => {
|
||
try {
|
||
const orgId = req.user!.organizationId;
|
||
const { name, host, port, database, username, password, ssl, allowedTables } = req.body ?? {};
|
||
if (!name || !host || !database || !username || !password) {
|
||
return res.status(400).json({ error: "Обязательные поля: name, host, database, username, password" });
|
||
}
|
||
const tables = sanitizeTables(allowedTables);
|
||
const inserted = await db.execute(sql`
|
||
INSERT INTO external_db_connections
|
||
(organization_id, name, host, port, database, username, password_encrypted, ssl, allowed_tables)
|
||
VALUES
|
||
(${orgId}, ${String(name)}, ${String(host)}, ${Number(port) || 5432}, ${String(database)},
|
||
${String(username)}, ${encryptPassword(String(password))}, ${!!ssl}, ${JSON.stringify(tables)}::jsonb)
|
||
RETURNING *
|
||
`);
|
||
res.status(201).json(toPublicConn(inserted.rows[0] as unknown as ConnRow));
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ error: "Ошибка базы данных" });
|
||
}
|
||
});
|
||
|
||
r.patch("/connections/:id", async (req: AuthenticatedRequest, res: Response) => {
|
||
try {
|
||
const orgId = req.user!.organizationId;
|
||
const id = parseInt(req.params.id, 10);
|
||
const b = req.body ?? {};
|
||
const existing = await findConn(orgId, id);
|
||
if (!existing) return res.status(404).json({ error: "Подключение не найдено" });
|
||
|
||
const sets: ReturnType<typeof sql>[] = [];
|
||
if (b.name !== undefined) sets.push(sql`name = ${String(b.name)}`);
|
||
if (b.host !== undefined) sets.push(sql`host = ${String(b.host)}`);
|
||
if (b.port !== undefined) sets.push(sql`port = ${Number(b.port) || 5432}`);
|
||
if (b.database !== undefined) sets.push(sql`database = ${String(b.database)}`);
|
||
if (b.username !== undefined) sets.push(sql`username = ${String(b.username)}`);
|
||
if (b.password) sets.push(sql`password_encrypted = ${encryptPassword(String(b.password))}`);
|
||
if (b.ssl !== undefined) sets.push(sql`ssl = ${!!b.ssl}`);
|
||
if (b.allowedTables !== undefined) sets.push(sql`allowed_tables = ${JSON.stringify(sanitizeTables(b.allowedTables))}::jsonb`);
|
||
if (b.isActive !== undefined) sets.push(sql`is_active = ${!!b.isActive}`);
|
||
if (sets.length === 0) return res.status(400).json({ error: "Нет полей для обновления" });
|
||
|
||
const updated = await db.execute(sql`
|
||
UPDATE external_db_connections
|
||
SET ${sql.join(sets, sql`, `)}, updated_at = NOW()
|
||
WHERE id = ${id} AND organization_id = ${orgId}
|
||
RETURNING *
|
||
`);
|
||
dropPool(id);
|
||
res.json(toPublicConn(updated.rows[0] as unknown as ConnRow));
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ error: "Ошибка базы данных" });
|
||
}
|
||
});
|
||
|
||
r.delete("/connections/:id", async (req: AuthenticatedRequest, res: Response) => {
|
||
try {
|
||
const orgId = req.user!.organizationId;
|
||
const id = parseInt(req.params.id, 10);
|
||
await db.execute(sql`
|
||
DELETE FROM external_db_connections WHERE id = ${id} AND organization_id = ${orgId}
|
||
`);
|
||
dropPool(id);
|
||
res.json({ success: true });
|
||
} catch (err) {
|
||
console.error(err);
|
||
res.status(500).json({ error: "Ошибка базы данных" });
|
||
}
|
||
});
|
||
|
||
r.post("/connections/:id/test", async (req: AuthenticatedRequest, res: Response) => {
|
||
try {
|
||
const orgId = req.user!.organizationId;
|
||
const id = parseInt(req.params.id, 10);
|
||
const conn = await findConn(orgId, id);
|
||
if (!conn) return res.status(404).json({ error: "Подключение не найдено" });
|
||
const started = Date.now();
|
||
await getPool(conn).query("SELECT 1");
|
||
res.json({ ok: true, durationMs: Date.now() - started });
|
||
} catch (err: any) {
|
||
res.json({ ok: false, error: err?.message || "Ошибка подключения" });
|
||
}
|
||
});
|
||
|
||
// --- Read-only шлюз (finance.manage) ---
|
||
r.post("/:id/query", authenticateToken, requirePermission("finance.manage"), async (req: AuthenticatedRequest, res: Response) => {
|
||
const started = Date.now();
|
||
const orgId = req.user!.organizationId;
|
||
const id = parseInt(req.params.id, 10);
|
||
const sqlText = req.body?.sql;
|
||
let rowCount = 0;
|
||
let errorText: string | null = null;
|
||
let conn: ConnRow | null = null;
|
||
try {
|
||
conn = await findConn(orgId, id);
|
||
if (!conn) return res.status(404).json({ error: "Подключение не найдено" });
|
||
if (!conn.is_active) return res.status(403).json({ error: "Подключение отключено" });
|
||
|
||
const cleaned = validateReadOnly(sqlText);
|
||
const allowed = (conn.allowed_tables ?? []).map((t) => String(t).toLowerCase());
|
||
// Пустой список = доступ ко ВСЕМ таблицам (ограничение — опционально;
|
||
// основной рубеж — права самого пользователя внешней БД, GRANT SELECT).
|
||
if (allowed.length > 0) {
|
||
const usedTables = extractTableNames(stripStringLiterals(cleaned));
|
||
const denied = usedTables.filter((t) => !allowed.includes(t));
|
||
if (denied.length > 0) {
|
||
return res.status(403).json({ error: `Таблицы не в allowlist: ${denied.join(", ")}` });
|
||
}
|
||
}
|
||
|
||
const wrapped = `SELECT * FROM (${cleaned}) AS __gateway_q LIMIT ${MAX_ROWS}`;
|
||
const result = await getPool(conn).query(wrapped);
|
||
rowCount = result.rowCount ?? result.rows.length;
|
||
res.json({ columns: result.fields.map((f) => f.name), rows: result.rows, rowCount, truncated: rowCount >= MAX_ROWS });
|
||
} catch (err: any) {
|
||
errorText = err?.message || String(err);
|
||
if (!res.headersSent) {
|
||
res.status(400).json({ error: errorText });
|
||
}
|
||
} finally {
|
||
if (conn) {
|
||
const botId = (req as any).bot?.id ?? null;
|
||
db.execute(sql`
|
||
INSERT INTO external_db_query_log
|
||
(organization_id, connection_id, user_id, bot_id, sql_text, row_count, duration_ms, error)
|
||
VALUES
|
||
(${orgId}, ${id}, ${req.user!.id}, ${botId},
|
||
${String(sqlText ?? "").slice(0, 4000)}, ${rowCount}, ${Date.now() - started}, ${errorText})
|
||
`).catch((e) => console.error("[external-db] audit log error:", e.message));
|
||
}
|
||
}
|
||
});
|
||
|
||
async function findConn(orgId: number, id: number): Promise<ConnRow | null> {
|
||
const rows = await db.execute(sql`
|
||
SELECT * FROM external_db_connections WHERE id = ${id} AND organization_id = ${orgId}
|
||
`);
|
||
return (rows.rows[0] as unknown as ConnRow) ?? null;
|
||
}
|
||
}
|
||
|
||
function sanitizeTables(input: unknown): string[] {
|
||
if (!Array.isArray(input)) return [];
|
||
return [...new Set(input.map((t) => String(t).trim().toLowerCase()).filter(Boolean))];
|
||
}
|
||
|
||
function requireAppAdmin(req: AuthenticatedRequest, res: Response, next: () => void): void {
|
||
if (req.user?.appRole !== "admin") {
|
||
res.status(403).json({ error: "Требуется роль администратора" });
|
||
return;
|
||
}
|
||
next();
|
||
}
|