Files
mtusi2026lab7/app/db.py
alenchha 861e7722b5 ЛР 7 — БВТ2301 — Курлова — на 4
Перенесён минимум lab6 (API без изменений), добавлена защита на границах доверия:
- внешний HTTPS; обязательный mTLS LB -> API с allowlist identity и отзывом по serial;
- TLS на всех связях: PostgreSQL verify-full, MinIO HTTPS, RabbitMQ AMQPS mTLS (EXTERNAL),
  Valkey TLS + ACL, Collector и хранилища телеметрии через mTLS с отдельными CA;
- OpenBao в server mode: cert auth, токены 15 мин, политики только на свой путь;
- роли БД, S3-политики и права брокера по принципу минимальных прав;
- Argon2id с замером, rehash, общий лимит входа, бюджет защиты от login DoS;
- hardening всех контейнеров, scan образов с разбором, модель угроз, матрица доверия;
- отрицательные тесты: TLS-аудит (135 проверок), ACL, ротации, sealed OpenBao.
2026-11-16 10:49:13 +03:00

146 lines
5.9 KiB
Python

import asyncio
import logging
import re
import time
from contextlib import asynccontextmanager
import psycopg
from opentelemetry.trace import SpanKind, Status, StatusCode
from psycopg.rows import dict_row
from psycopg_pool import AsyncConnectionPool, PoolTimeout
from app import config, telemetry
from app.errors import unavailable
BROKEN = (psycopg.OperationalError, psycopg.InterfaceError, PoolTimeout)
OPERATION_RE = re.compile(r"^\s*([A-Za-z]+)")
TABLE_RE = re.compile(r"\b(?:FROM|INTO|UPDATE|JOIN)\s+([a-z_]+)", re.IGNORECASE)
POOL_WAIT_SPAN_SECONDS = 0.005
log = logging.getLogger("messenger.db")
def statement(query) -> tuple:
"""Span name and attributes for a parametrised SQL statement (values are never part of the text)."""
text = query if isinstance(query, str) else str(query)
op = OPERATION_RE.match(text)
table = TABLE_RE.search(text)
operation = op.group(1).upper() if op else "SQL"
name = operation + (" " + table.group(1) if table else "")
return name, {"db.system": "postgresql", "db.operation.name": operation, "db.namespace": "messenger",
"db.collection.name": table.group(1) if table else "", "db.query.text": text[:400]}
async def discard(conn) -> None:
try:
await conn.close()
except Exception:
pass
class Guarded:
def __init__(self, conn, timeout: float):
self.conn = conn
self.timeout = timeout
async def execute(self, *args, **kwargs):
# Own CLIENT span around the awaited statement: the time really spent in PostgreSQL (lock waits included).
# (opentelemetry-instrumentation-psycopg 0.51b0 ends async spans before the await completes -> 0 ms spans.)
name, attrs = statement(args[0] if args else "")
with telemetry.span(name, kind=SpanKind.CLIENT, attributes=attrs) as span:
try:
return await asyncio.wait_for(self.conn.execute(*args, **kwargs), self.timeout)
except asyncio.TimeoutError:
await discard(self.conn)
span.set_status(Status(StatusCode.ERROR, "client timeout %.1fs" % self.timeout))
raise unavailable("Хранилище не ответило за отведённое время") from None
def transaction(self):
return self.conn.transaction()
class Database:
def __init__(self) -> None:
options = "-c statement_timeout=%d -c lock_timeout=%d -c idle_in_transaction_session_timeout=%d" % (
config.DB_STATEMENT_TIMEOUT_MS,
config.DB_STATEMENT_TIMEOUT_MS,
config.DB_STATEMENT_TIMEOUT_MS * 2,
)
self.pool = AsyncConnectionPool(
conninfo=config.DATABASE_URL,
min_size=config.DB_POOL_MIN,
max_size=config.DB_POOL_MAX,
timeout=config.DB_POOL_TIMEOUT,
max_lifetime=600.0,
open=False,
kwargs={
"autocommit": True,
"row_factory": dict_row,
"connect_timeout": config.DB_CONNECT_TIMEOUT,
"options": options,
# lab7: TLS with full server verification (CA + hostname "postgres"); no fallback to plaintext.
"sslmode": config.DATABASE_SSLMODE,
"sslrootcert": config.TLS_CA_FILE,
},
)
def rotate(self, conninfo: str) -> None:
"""New credentials from OpenBao: new connections use them; open ones keep working until max_lifetime."""
if conninfo and conninfo != self.pool.conninfo:
self.pool.conninfo = conninfo
log.info("db_credentials_rotated: new connections use the new password")
async def open(self) -> None:
await self.pool.open(wait=False)
async def close(self) -> None:
await self.pool.close()
@asynccontextmanager
async def connection(self):
started, acquired = time.time_ns(), False
try:
async with self.pool.connection(timeout=config.DB_POOL_TIMEOUT) as conn:
acquired = True
waited = (time.time_ns() - started) / 1e9
if waited >= POOL_WAIT_SPAN_SECONDS: # only noticeable waits become spans
telemetry.tracer.start_span("db.pool.acquire", start_time=started,
attributes={"db.system": "postgresql",
"db.client.connection.wait_s": round(waited, 4)}).end()
yield Guarded(conn, config.DB_OP_TIMEOUT)
except BROKEN as exc:
if not acquired:
span = telemetry.tracer.start_span("db.pool.acquire", start_time=started,
attributes={"db.system": "postgresql"})
span.record_exception(exc)
span.set_status(Status(StatusCode.ERROR, type(exc).__name__))
span.end()
raise unavailable("Хранилище недоступно") from None
@asynccontextmanager
async def transaction(self):
async with self.connection() as conn:
async with conn.transaction():
yield conn
async def in_transaction(self, action):
async def run():
async with self.transaction() as conn:
return await action(conn)
try:
return await asyncio.wait_for(run(), config.DB_OP_TIMEOUT)
except asyncio.TimeoutError:
raise unavailable("Хранилище не ответило за отведённое время") from None
async def ready(self) -> bool:
try:
async with self.pool.connection(timeout=config.DB_READY_TIMEOUT) as conn:
try:
await asyncio.wait_for(conn.execute("SELECT 1"), config.DB_READY_TIMEOUT)
except asyncio.TimeoutError:
await discard(conn)
return False
return True
except Exception:
return False