ARES - authorized red-team engagement automation with dashboard, campaign scope, module orchestration, OPSEC controls, encrypted vault, and reporting.
Python active-directorycloud-securitydefensive-validationfastapigood-first-issuemitre-attackopsecreactred-teamreportingsecurity
"""Real PostgreSQL execution tests for the historical Alembic chain."""
from __future__ import annotations
import asyncio
import builtins
import importlib
import importlib.machinery
import multiprocessing
import os
import re
import sys
import threading
import time
import types
from collections.abc import AsyncIterator, Awaitable, Callable, Iterator
from contextlib import asynccontextmanager, contextmanager
from dataclasses import dataclass, field, replace
from datetime import UTC, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any
from uuid import uuid4
import pytest
_REPO_ROOT = Path(__file__).resolve().parents[2]
_POSTGRES_ENV = (
"ARES_TEST_POSTGRES_HOST",
"ARES_TEST_POSTGRES_PORT",
"ARES_TEST_POSTGRES_USER",
"ARES_TEST_POSTGRES_DB",
)
_SAFE_DATABASE_NAME = re.compile(r"^ares_migration_[0-9a-f]{32}$")
_SAFE_EXCEPTION_TYPE = re.compile(r"^[A-Za-z_][A-Za-z0-9_]{0,63}$")
_WORKER_READY_SECONDS = 15.0
_WORKER_TIMEOUT_SECONDS = 25.0
_WORKER_SETTLE_SECONDS = 5.0
_POSTGRES_OPERATION_TIMEOUT_SECONDS = 15.0
_WORKER_POLL_SECONDS = 0.01
_OWNED_WORKERS: set[_OwnedWorker] = set()
_EXPECTED_TABLES = {
"campaigns",
"module_runs",
"findings",
"hosts",
"credentials",
"loot",
"audit_log",
"users",
"api_keys",
"refresh_tokens",
"revoked_access_tokens",
"rate_limit_events",
}
_EXPECTED_FOREIGN_KEYS = {
("module_runs", "campaign_id", "campaigns", "CASCADE"),
("findings", "campaign_id", "campaigns", "CASCADE"),
("hosts", "campaign_id", "campaigns", "CASCADE"),
("credentials", "campaign_id", "campaigns", "CASCADE"),
("credentials", "host_id", "hosts", "SET NULL"),
("loot", "campaign_id", "campaigns", "CASCADE"),
("loot", "host_id", "hosts", "SET NULL"),
("audit_log", "campaign_id", "campaigns", "SET NULL"),
("api_keys", "user_id", "users", "CASCADE"),
("refresh_tokens", "user_id", "users", "CASCADE"),
}
_EXPECTED_INDEXES = {
"idx_module_runs_campaign": ("campaign_id",),
"idx_module_runs_completed": ("completed_at",),
"idx_findings_campaign": ("campaign_id",),
"idx_findings_severity": ("severity",),
"idx_findings_fp": ("false_positive",),
"idx_findings_mitre": ("mitre_technique",),
"idx_findings_cvss": ("cvss_score",),
"idx_hosts_campaign": ("campaign_id",),
"idx_hosts_ip": ("ip_address",),
"idx_hosts_domain": ("domain",),
"idx_creds_campaign": ("campaign_id",),
"idx_creds_username": ("username",),
"idx_creds_type": ("cred_type",),
"idx_loot_campaign": ("campaign_id",),
"idx_loot_type": ("loot_type",),
"idx_audit_campaign": ("campaign_id",),
"idx_audit_actor": ("actor",),
"idx_audit_action": ("action",),
"idx_users_username": ("username",),
"idx_users_role": ("role",),
"idx_apikeys_user": ("user_id",),
"idx_apikeys_prefix": ("key_prefix",),
"idx_refresh_user": ("user_id",),
"idx_refresh_exp": ("expires_at",),
"idx_rat_expires": ("expires_at",),
"idx_rle_ip": ("ip_address",),
"idx_rle_timestamp": ("timestamp",),
"idx_rle_blocked": ("blocked",),
}
_POSTGRES_INDEX_TABLES = {
"idx_module_runs_campaign": "module_runs",
"idx_module_runs_completed": "module_runs",
"idx_findings_campaign": "findings",
"idx_findings_severity": "findings",
"idx_findings_fp": "findings",
"idx_findings_mitre": "findings",
"idx_findings_cvss": "findings",
"idx_hosts_campaign": "hosts",
"idx_hosts_ip": "hosts",
"idx_hosts_domain": "hosts",
"idx_creds_campaign": "credentials",
"idx_creds_username": "credentials",
"idx_creds_type": "credentials",
"idx_loot_campaign": "loot",
"idx_loot_type": "loot",
"idx_audit_campaign": "audit_log",
"idx_audit_actor": "audit_log",
"idx_audit_action": "audit_log",
"idx_users_username": "users",
"idx_users_role": "users",
"idx_apikeys_user": "api_keys",
"idx_apikeys_prefix": "api_keys",
"idx_refresh_user": "refresh_tokens",
"idx_refresh_exp": "refresh_tokens",
"idx_rat_expires": "revoked_access_tokens",
"idx_rle_ip": "rate_limit_events",
"idx_rle_timestamp": "rate_limit_events",
"idx_rle_blocked": "rate_limit_events",
}
_EXPECTED_COLUMN_ORDER = {
"campaigns": (
"id",
"name",
"client",
"operator",
"noise_profile",
"status",
"scope_json",
"targets_json",
"notes",
"created_at",
"updated_at",
),
"module_runs": (
"id",
"campaign_id",
"module_id",
"outcome",
"success",
"duration_ms",
"completed_at",
),
"findings": (
"id",
"campaign_id",
"module_id",
"title",
"description",
"severity",
"confidence",
"mitre_technique",
"mitre_tactic",
"cvss_score",
"cvss_vector",
"evidence_json",
"remediation",
"host",
"validated",
"false_positive",
"discovered_at",
"trace_id",
),
"hosts": (
"id",
"campaign_id",
"ip_address",
"hostname",
"fqdn",
"os",
"os_version",
"domain",
"is_dc",
"open_ports_json",
"tags_json",
"first_seen",
"last_seen",
),
"credentials": (
"id",
"campaign_id",
"host_id",
"username",
"secret_enc",
"cred_type",
"domain",
"source_module",
"notes",
"cracked",
"cracked_value_enc",
"captured_at",
),
"loot": (
"id",
"campaign_id",
"host_id",
"loot_type",
"name",
"description",
"content_enc",
"size_bytes",
"path_on_target",
"source_module",
"tags_json",
"captured_at",
),
"audit_log": (
"id",
"campaign_id",
"actor",
"action",
"detail",
"module_id",
"timestamp",
),
"users": (
"id",
"username",
"hashed_password",
"role",
"is_active",
"created_by",
"created_at",
"last_login",
),
"api_keys": (
"id",
"user_id",
"name",
"key_hash",
"key_prefix",
"scopes",
"is_active",
"last_used",
"expires_at",
"created_at",
),
"refresh_tokens": (
"id",
"user_id",
"is_revoked",
"expires_at",
"created_at",
"used_at",
),
"revoked_access_tokens": (
"jti",
"user_id",
"revoked_at",
"expires_at",
),
"rate_limit_events": (
"id",
"ip_address",
"bucket",
"username",
"blocked",
"timestamp",
),
}
_POSTGRES_INTEGER_COLUMNS = {
("module_runs", "success"),
("hosts", "is_dc"),
("credentials", "cracked"),
("loot", "size_bytes"),
("audit_log", "id"),
("users", "is_active"),
("api_keys", "is_active"),
("refresh_tokens", "is_revoked"),
("rate_limit_events", "id"),
("rate_limit_events", "blocked"),
("findings", "validated"),
("findings", "false_positive"),
}
_POSTGRES_FLOAT_COLUMNS = {
("module_runs", "duration_ms"),
("findings", "confidence"),
("findings", "cvss_score"),
}
_POSTGRES_TIMESTAMP_COLUMNS = {
("campaigns", "created_at"),
("campaigns", "updated_at"),
("module_runs", "completed_at"),
("findings", "discovered_at"),
("hosts", "first_seen"),
("hosts", "last_seen"),
("credentials", "captured_at"),
("loot", "captured_at"),
("audit_log", "timestamp"),
("users", "created_at"),
("users", "last_login"),
("api_keys", "last_used"),
("api_keys", "expires_at"),
("api_keys", "created_at"),
("refresh_tokens", "expires_at"),
("refresh_tokens", "created_at"),
("refresh_tokens", "used_at"),
("revoked_access_tokens", "revoked_at"),
("revoked_access_tokens", "expires_at"),
("rate_limit_events", "timestamp"),
}
_POSTGRES_FINITE_TIMESTAMP_CONSTRAINTS = {
("campaigns", "created_at"): (
"ck_campaigns_created_at_finite",
False,
),
("campaigns", "updated_at"): (
"ck_campaigns_updated_at_finite",
False,
),
("module_runs", "completed_at"): (
"ck_module_runs_completed_at_finite",
False,
),
("findings", "discovered_at"): (
"ck_findings_discovered_at_finite",
False,
),
("hosts", "first_seen"): ("ck_hosts_first_seen_finite", False),
("hosts", "last_seen"): ("ck_hosts_last_seen_finite", False),
("credentials", "captured_at"): (
"ck_credentials_captured_at_finite",
False,
),
("loot", "captured_at"): ("ck_loot_captured_at_finite", False),
("audit_log", "timestamp"): ("ck_audit_log_timestamp_finite", False),
("users", "created_at"): ("ck_users_created_at_finite", False),
("users", "last_login"): ("ck_users_last_login_finite", True),
("api_keys", "last_used"): ("ck_api_keys_last_used_finite", True),
("api_keys", "expires_at"): ("ck_api_keys_expires_at_finite", True),
("api_keys", "created_at"): ("ck_api_keys_created_at_finite", False),
("refresh_tokens", "expires_at"): (
"ck_refresh_tokens_expires_at_finite",
False,
),
("refresh_tokens", "created_at"): (
"ck_refresh_tokens_created_at_finite",
False,
),
("refresh_tokens", "used_at"): (
"ck_refresh_tokens_used_at_finite",
True,
),
("revoked_access_tokens", "revoked_at"): (
"ck_revoked_access_tokens_revoked_at_finite",
False,
),
("revoked_access_tokens", "expires_at"): (
"ck_revoked_access_tokens_expires_at_finite",
False,
),
("rate_limit_events", "timestamp"): (
"ck_rate_limit_events_timestamp_finite",
False,
),
}
_POSTGRES_NULLABLE_COLUMNS = {
("campaigns", "notes"),
("findings", "mitre_technique"),
("findings", "mitre_tactic"),
("findings", "remediation"),
("findings", "host"),
("hosts", "hostname"),
("hosts", "fqdn"),
("hosts", "os"),
("hosts", "os_version"),
("hosts", "domain"),
("credentials", "host_id"),
("credentials", "secret_enc"),
("credentials", "domain"),
("credentials", "source_module"),
("credentials", "notes"),
("credentials", "cracked_value_enc"),
("loot", "host_id"),
("loot", "description"),
("loot", "content_enc"),
("loot", "size_bytes"),
("loot", "path_on_target"),
("loot", "source_module"),
("audit_log", "campaign_id"),
("audit_log", "detail"),
("audit_log", "module_id"),
("users", "last_login"),
("api_keys", "last_used"),
("api_keys", "expires_at"),
("refresh_tokens", "used_at"),
("rate_limit_events", "username"),
}
_POSTGRES_DEFAULTS = {
("campaigns", "client"): "literal:Internal",
("campaigns", "operator"): "literal:unknown",
("campaigns", "noise_profile"): "literal:stealth",
("campaigns", "status"): "literal:created",
("campaigns", "scope_json"): "literal:[]",
("campaigns", "targets_json"): "literal:[]",
("campaigns", "notes"): "literal:",
("campaigns", "created_at"): "now",
("campaigns", "updated_at"): "now",
("module_runs", "success"): "literal:0",
("module_runs", "duration_ms"): "literal:0.0",
("module_runs", "completed_at"): "now",
("findings", "confidence"): "literal:1.0",
("findings", "cvss_score"): "literal:0.0",
("findings", "cvss_vector"): "literal:",
("findings", "evidence_json"): "literal:{}",
("findings", "remediation"): "literal:",
("findings", "validated"): "literal:0",
("findings", "false_positive"): "literal:0",
("findings", "discovered_at"): "now",
("findings", "trace_id"): "literal:",
("hosts", "is_dc"): "literal:0",
("hosts", "open_ports_json"): "literal:[]",
("hosts", "tags_json"): "literal:[]",
("hosts", "first_seen"): "now",
("hosts", "last_seen"): "now",
("credentials", "notes"): "literal:",
("credentials", "cracked"): "literal:0",
("credentials", "captured_at"): "now",
("loot", "description"): "literal:",
("loot", "size_bytes"): "literal:0",
("loot", "tags_json"): "literal:[]",
("loot", "captured_at"): "now",
("audit_log", "id"): "sequence",
("audit_log", "actor"): "literal:system",
("audit_log", "detail"): "literal:",
("audit_log", "timestamp"): "now",
("users", "role"): "literal:reporter",
("users", "is_active"): "literal:1",
("users", "created_by"): "literal:system",
("users", "created_at"): "now",
("api_keys", "scopes"): "literal:read",
("api_keys", "is_active"): "literal:1",
("api_keys", "created_at"): "now",
("refresh_tokens", "is_revoked"): "literal:0",
("refresh_tokens", "created_at"): "now",
("revoked_access_tokens", "revoked_at"): "now",
("rate_limit_events", "id"): "sequence",
("rate_limit_events", "blocked"): "literal:0",
("rate_limit_events", "timestamp"): "now",
}
@dataclass(frozen=True, eq=False)
class _PostgresConfig:
host: str = field(repr=False)
port: int = field(repr=False)
user: str = field(repr=False)
maintenance_database: str = field(repr=False)
@dataclass(frozen=True, eq=False)
class _MigrationHarness:
config: _PostgresConfig = field(repr=False)
database_name: str = field(repr=False)
@dataclass(eq=False)
class _OwnedWorker:
process: Any = field(repr=False)
channel: Any = field(repr=False)
child_channel: Any = field(default=None, repr=False)
start_attempted: bool = False
may_have_started: bool = False
death_proven: bool = False
settled_exit_code: int | None = None
eof: bool = False
parent_closed: bool = False
child_closed: bool = False
process_closed: bool = False
closed: bool = False
@dataclass(frozen=True)
class _PostgresCatalogContract:
tables: tuple[str, ...]
columns: tuple[tuple[str, str, str, bool, str | None], ...]
primary_keys: tuple[tuple[str, tuple[str, ...]], ...]
unique_constraints: tuple[tuple[str, str, tuple[str, ...]], ...]
checks: tuple[tuple[str, str, str], ...]
foreign_keys: tuple[
tuple[
str,
str,
tuple[str, ...],
str,
tuple[str, ...],
str,
str,
],
...,
]
indexes: tuple[tuple[str, str, tuple[str, ...], bool, str | None], ...]
sequences: tuple[str, ...]
@dataclass(frozen=True, repr=False)
class _PostgresIsolationFingerprint:
schemas: tuple[str, ...] = field(repr=False)
relations: tuple[tuple[object, ...], ...] = field(repr=False)
columns: tuple[tuple[object, ...], ...] = field(repr=False)
constraints: tuple[tuple[object, ...], ...] = field(repr=False)
indexes: tuple[tuple[object, ...], ...] = field(repr=False)
sequences: tuple[tuple[object, ...], ...] = field(repr=False)
versions: tuple[tuple[str, str], ...] = field(repr=False)
@dataclass(frozen=True)
class _PostgresLegacyRecipe:
constraints_to_drop: tuple[tuple[str, str], ...]
tables_to_drop: tuple[str, ...]
nullable_columns: tuple[tuple[str, str], ...]
indexes_to_create: tuple[tuple[str, str, tuple[str, ...]], ...]
class _BoundedTransaction:
def __init__(self, transaction: Any) -> None:
self._transaction = transaction
async def start(self) -> object:
return await asyncio.wait_for(
self._transaction.start(),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
async def commit(self) -> object:
return await asyncio.wait_for(
self._transaction.commit(),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
async def rollback(self) -> object:
return await asyncio.wait_for(
self._transaction.rollback(),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
class _BoundedConnection:
def __init__(self, connection: Any) -> None:
self._connection = connection
async def execute(self, *args: object) -> object:
return await asyncio.wait_for(
self._connection.execute(*args),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
async def fetch(self, *args: object) -> object:
return await asyncio.wait_for(
self._connection.fetch(*args),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
async def fetchrow(self, *args: object) -> object:
return await asyncio.wait_for(
self._connection.fetchrow(*args),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
async def fetchval(self, *args: object) -> object:
return await asyncio.wait_for(
self._connection.fetchval(*args),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
async def close(self) -> None:
await asyncio.wait_for(
self._connection.close(),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
def transaction(self) -> _BoundedTransaction:
return _BoundedTransaction(self._connection.transaction())
def _require_fixed(condition: bool, message: str) -> None:
if not condition:
pytest.fail(message, pytrace=False)
def _requires_candidate_origin(name: str) -> bool:
return (
name in {"ares", "migrations", "tests.unit", "tests.integration"}
or name.startswith(("ares.", "migrations."))
or name.startswith(("tests.unit.", "tests.integration."))
)
def _path_is_within_candidate(path: object, candidate_root: Path) -> bool:
try:
resolved_path = os.path.normcase(str(Path(path).resolve(strict=False)))
resolved_root = os.path.normcase(str(candidate_root.resolve(strict=True)))
return os.path.commonpath((resolved_path, resolved_root)) == (resolved_root)
except (OSError, RuntimeError, TypeError, ValueError):
return False
def _module_origin_is_candidate_local(
module: object,
candidate_root: Path,
) -> bool:
spec = getattr(module, "__spec__", None)
concrete_origins: list[object] = []
module_file = getattr(module, "__file__", None)
if module_file is not None:
concrete_origins.append(module_file)
spec_origin = getattr(spec, "origin", None)
if spec_origin not in {None, "built-in", "frozen"}:
concrete_origins.append(spec_origin)
concrete_is_local = not concrete_origins or all(
_path_is_within_candidate(origin, candidate_root) for origin in concrete_origins
)
search_locations = getattr(spec, "submodule_search_locations", None)
if search_locations is None:
search_locations = getattr(module, "__path__", None)
if search_locations is None:
locations_are_local = True
has_locations = False
else:
locations = tuple(search_locations)
has_locations = bool(locations)
locations_are_local = has_locations and all(
_path_is_within_candidate(location, candidate_root) for location in locations
)
return concrete_is_local and locations_are_local and (bool(concrete_origins) or has_locations)
def _spec_origin_is_candidate_local(
spec: object,
candidate_root: Path,
) -> bool:
concrete_origins: list[object] = []
spec_origin = getattr(spec, "origin", None)
if spec_origin not in {None, "built-in", "frozen"}:
concrete_origins.append(spec_origin)
concrete_is_local = not concrete_origins or all(
_path_is_within_candidate(origin, candidate_root) for origin in concrete_origins
)
search_locations = getattr(spec, "submodule_search_locations", None)
if search_locations is None:
locations_are_local = True
has_locations = False
else:
locations = tuple(search_locations)
has_locations = bool(locations)
locations_are_local = has_locations and all(
_path_is_within_candidate(location, candidate_root) for location in locations
)
return concrete_is_local and locations_are_local and (bool(concrete_origins) or has_locations)
def _relative_path_is_first_party(relative_path: Path) -> bool:
folded_parts = tuple(part.casefold() for part in relative_path.parts)
if not folded_parts:
return False
if folded_parts[0] in {"ares", "migrations"}:
return True
return len(folded_parts) >= 2 and folded_parts[:2] in {
("tests", "unit"),
("tests", "integration"),
}
def _frame_file_is_first_party(
module_file: object,
candidate_root: Path,
) -> bool:
if not isinstance(module_file, (str, os.PathLike)):
return False
try:
resolved_file = Path(module_file).resolve()
except (OSError, RuntimeError, TypeError, ValueError):
return False
for root in (candidate_root, _REPO_ROOT):
try:
relative_path = resolved_file.relative_to(root.resolve())
except ValueError:
continue
if _relative_path_is_first_party(relative_path):
return True
folded_parts = tuple(part.casefold() for part in resolved_file.parts)
if len(folded_parts) >= 2 and folded_parts[-2:] == (
"migrations",
"env.py",
):
return True
return (
len(folded_parts) >= 3
and folded_parts[-3:-1] == ("migrations", "versions")
and re.fullmatch(r"[0-9]{4}_[a-z0-9_]+\.py", folded_parts[-1]) is not None
)
def _frame_is_first_party(
frame: types.FrameType,
candidate_root: Path,
) -> bool:
globals_map = frame.f_globals
name = globals_map.get("__name__")
package = globals_map.get("__package__")
if isinstance(name, str) and _requires_candidate_origin(name):
return True
if isinstance(package, str) and _requires_candidate_origin(package):
return True
return _frame_file_is_first_party(
globals_map.get("__file__"),
candidate_root,
)
class _CandidateOriginFinder:
def __init__(
self,
ledger: _CandidateOriginLedger,
delegates: tuple[object, ...],
) -> None:
self._ledger = ledger
self._delegates = delegates
def find_spec(
self,
fullname: str,
path: object = None,
target: object = None,
) -> object:
for finder in self._delegates:
find_spec = getattr(finder, "find_spec", None)
if find_spec is None:
continue
spec = find_spec(fullname, path, target)
if spec is not None:
self._ledger.observe_spec(fullname, spec)
return spec
return None
@dataclass(slots=True, repr=False)
class _CandidateOriginLedger:
candidate_root: Path
isolated: bool = False
violated: bool = False
_finder: _CandidateOriginFinder | None = field(
default=None,
init=False,
repr=False,
)
_previous_profile: object = field(default=None, init=False, repr=False)
_profile_callback: object = field(default=None, init=False, repr=False)
_previous_meta_path: tuple[object, ...] | None = field(
default=None,
init=False,
repr=False,
)
_observed_frame_origins: set[
tuple[
types.CodeType,
int,
str | None,
str | None,
str | None,
]
] = field(
default_factory=set,
init=False,
repr=False,
)
def observe_spec(self, name: str, spec: object) -> None:
if _requires_candidate_origin(name) and not (
_spec_origin_is_candidate_local(spec, self.candidate_root)
):
self.violated = True
def observe_modules(self, modules: object) -> None:
if not _loaded_first_party_origins_are_candidate_local(
modules,
self.candidate_root,
):
self.violated = True
def _profile(
self,
frame: types.FrameType,
event: str,
arg: object,
) -> None:
if event == "call":
globals_map = frame.f_globals
raw_file = globals_map.get("__file__")
try:
normalized_file = (
os.fspath(raw_file) if isinstance(raw_file, (str, os.PathLike)) else None
)
except TypeError:
normalized_file = None
raw_name = globals_map.get("__name__")
raw_package = globals_map.get("__package__")
observation = (
frame.f_code,
id(globals_map),
raw_name if isinstance(raw_name, str) else None,
raw_package if isinstance(raw_package, str) else None,
normalized_file,
)
if observation not in self._observed_frame_origins:
self._observed_frame_origins.add(observation)
if _frame_is_first_party(frame, self.candidate_root):
module_file = frame.f_globals.get("__file__")
if module_file is None or not _path_is_within_candidate(
module_file,
self.candidate_root,
):
self.violated = True
previous = self._previous_profile
if not self.isolated and callable(previous):
previous(frame, event, arg)
def install(self) -> None:
self._previous_meta_path = tuple(sys.meta_path)
delegates = tuple(
finder
for finder in self._previous_meta_path
if not isinstance(finder, _CandidateOriginFinder)
)
self._finder = _CandidateOriginFinder(self, delegates)
sys.meta_path.insert(0, self._finder)
self._previous_profile = sys.getprofile()
self._profile_callback = self._profile
sys.setprofile(self._profile_callback)
def installed_exactly(self) -> bool:
return (
self._finder is not None
and bool(sys.meta_path)
and sys.meta_path[0] is self._finder
and sys.getprofile() is self._profile_callback
)
def close(self) -> None:
previous_meta_path = self._previous_meta_path
if previous_meta_path is not None:
sys.meta_path[:] = previous_meta_path
sys.setprofile(self._previous_profile)
self._finder = None
self._profile_callback = None
self._previous_profile = None
self._previous_meta_path = None
self._observed_frame_origins.clear()
_ACTIVE_ORIGIN_LEDGERS: list[_CandidateOriginLedger] = []
def _loaded_first_party_origins_are_candidate_local(
modules: object,
candidate_root: Path,
) -> bool:
try:
loaded = tuple(modules.items())
except AttributeError:
return False
for name, module in loaded:
if not isinstance(name, str) or not _requires_candidate_origin(name):
continue
if module is None or not _module_origin_is_candidate_local(
module,
candidate_root,
):
return False
return True
def _assert_candidate_origin_boundary(
ledger: _CandidateOriginLedger | None = None,
) -> None:
ledgers = (ledger,) if ledger is not None else tuple(_ACTIVE_ORIGIN_LEDGERS)
for active_ledger in ledgers:
active_ledger.observe_modules(sys.modules)
if not (
_path_is_within_candidate(__file__, _REPO_ROOT)
and all(
not active_ledger.violated and active_ledger.installed_exactly()
for active_ledger in ledgers
)
and _loaded_first_party_origins_are_candidate_local(
sys.modules,
_REPO_ROOT,
)
):
raise RuntimeError("first-party migration import escaped the source candidate")
@contextmanager
def _candidate_origin_boundary(
*,
isolated: bool = False,
) -> Iterator[_CandidateOriginLedger]:
ledger = _CandidateOriginLedger(_REPO_ROOT, isolated=isolated)
ledger.install()
_ACTIVE_ORIGIN_LEDGERS.append(ledger)
try:
_assert_candidate_origin_boundary(ledger)
yield ledger
finally:
try:
_assert_candidate_origin_boundary(ledger)
finally:
for index in range(len(_ACTIVE_ORIGIN_LEDGERS) - 1, -1, -1):
if _ACTIVE_ORIGIN_LEDGERS[index] is ledger:
del _ACTIVE_ORIGIN_LEDGERS[index]
break
ledger.close()
@pytest.fixture(scope="module", autouse=True)
def _final_candidate_origin_boundary() -> Iterator[None]:
with _candidate_origin_boundary():
yield
def _postgres_config() -> _PostgresConfig:
present = [name for name in _POSTGRES_ENV if name in os.environ]
if not present:
pytest.skip("real PostgreSQL test environment is not configured")
if len(present) != len(_POSTGRES_ENV):
pytest.fail("Incomplete PostgreSQL test environment", pytrace=False)
values = {name: os.environ[name] for name in _POSTGRES_ENV}
if any(not value or value != value.strip() for value in values.values()):
pytest.fail("Invalid PostgreSQL test environment", pytrace=False)
raw_port = values["ARES_TEST_POSTGRES_PORT"]
if not raw_port.isascii() or not raw_port.isdecimal():
pytest.fail("PostgreSQL test port is invalid", pytrace=False)
try:
port = int(raw_port)
except ValueError:
pytest.fail("PostgreSQL test port is invalid", pytrace=False)
if str(port) != raw_port or not 1 <= port <= 65535:
pytest.fail(
"PostgreSQL test port is outside its valid range",
pytrace=False,
)
return _PostgresConfig(
host=values["ARES_TEST_POSTGRES_HOST"],
port=port,
user=values["ARES_TEST_POSTGRES_USER"],
maintenance_database=values["ARES_TEST_POSTGRES_DB"],
)
def _child_postgres_config() -> _PostgresConfig:
present = [name for name in _POSTGRES_ENV if name in os.environ]
if len(present) != len(_POSTGRES_ENV):
raise RuntimeError("worker-postgres-config-error")
values = {name: os.environ[name] for name in _POSTGRES_ENV}
if any(not value or value != value.strip() for value in values.values()):
raise RuntimeError("worker-postgres-config-error")
raw_port = values["ARES_TEST_POSTGRES_PORT"]
if not raw_port.isascii() or not raw_port.isdecimal():
raise RuntimeError("worker-postgres-config-error")
try:
port = int(raw_port)
except ValueError:
raise RuntimeError("worker-postgres-config-error") from None
if str(port) != raw_port or not 1 <= port <= 65535:
raise RuntimeError("worker-postgres-config-error")
return _PostgresConfig(
host=values["ARES_TEST_POSTGRES_HOST"],
port=port,
user=values["ARES_TEST_POSTGRES_USER"],
maintenance_database=values["ARES_TEST_POSTGRES_DB"],
)
def _migration_url(
config: _PostgresConfig,
database_name: str,
*,
ordinary_driver: bool = False,
) -> str:
from sqlalchemy.engine import URL
driver = "postgresql" if ordinary_driver else "postgresql+asyncpg"
return URL.create(
driver,
username=config.user,
host=config.host,
port=config.port,
database=database_name,
).render_as_string(hide_password=False)
def _alembic_config(url: str) -> Any:
import argparse
from alembic.config import Config
config = Config(str(_REPO_ROOT / "alembic.ini"))
config.set_main_option(
"script_location",
str(_REPO_ROOT / "migrations"),
)
config.set_main_option(
"sqlalchemy.url",
os.environ.get("ARES_DATABASE_URL", "sqlite:///unused.db"),
)
config.cmd_opts = argparse.Namespace(x=[f"db_url={url}"])
return config
def _redirect_child_output() -> None:
sink = os.open(os.devnull, os.O_WRONLY)
try:
os.dup2(sink, 1)
os.dup2(sink, 2)
finally:
os.close(sink)
def _safe_exception_type(exc: Exception) -> str:
exception_type = type(exc).__name__
if _SAFE_EXCEPTION_TYPE.fullmatch(exception_type) is None:
return "Exception"
return exception_type
def _worker_send(channel: Any, frame: tuple[str, ...]) -> None:
channel.send(frame)
def _prepare_child_migration(
payload: dict[object, object],
command: Any,
) -> tuple[Any, Any, str, str | None]:
allowed_keys = {
"mode",
"operation",
"revision",
"database_name",
"fault",
}
if set(payload) - allowed_keys:
raise RuntimeError("worker-payload-error")
postgres_config = _child_postgres_config()
database_name = payload.get("database_name")
if not isinstance(database_name, str) or _SAFE_DATABASE_NAME.fullmatch(database_name) is None:
raise RuntimeError("worker-payload-error")
if database_name == postgres_config.maintenance_database:
raise RuntimeError("worker-payload-error")
migration_url = _migration_url(
postgres_config,
database_name,
ordinary_driver=True,
)
config = _alembic_config(migration_url)
operation = payload.get("operation")
revision = payload.get("revision")
fault = payload.get("fault")
if (
operation not in {"upgrade", "downgrade"}
or not isinstance(revision, str)
or not revision
or fault
not in {
None,
"0005-before-alter",
"0005-after-alter",
"0006-after-rename",
"0011-after-authority-ddl",
}
):
raise RuntimeError("worker-payload-error")
action = command.upgrade if operation == "upgrade" else command.downgrade
return config, action, revision, fault
def _prepared_child_target_is_authoritative(
config: object,
database_name: str,
) -> bool:
from sqlalchemy.engine import make_url
command_options = getattr(config, "cmd_opts", None)
arguments = getattr(command_options, "x", None)
if not isinstance(arguments, list) or len(arguments) != 1:
return False
key, separator, raw_url = arguments[0].partition("=")
if key != "db_url" or separator != "=" or not raw_url:
return False
try:
parsed = make_url(raw_url)
except Exception:
return False
expected = _child_postgres_config()
return (
parsed.drivername == "postgresql"
and parsed.host == expected.host
and parsed.port == expected.port
and parsed.username == expected.user
and parsed.password is None
and parsed.database == database_name
and not parsed.query
)
def _worker_import_then_remove_external_probe() -> None:
from tempfile import TemporaryDirectory
parent = importlib.import_module("ares")
package_path = getattr(parent, "__path__", None)
if package_path is None:
raise RuntimeError("worker-origin-probe-error")
original_locations = tuple(package_path)
name = "ares.worker_removed_origin_probe"
child_name = "worker_removed_origin_probe"
with TemporaryDirectory(prefix="ares-worker-origin-") as directory:
external_package = Path(directory) / "ares"
external_package.mkdir()
(external_package / f"{child_name}.py").write_text(
"def marker():\n return True\n",
encoding="utf-8",
)
package_path.append(str(external_package))
importlib.invalidate_caches()
try:
imported = importlib.import_module(name)
marker = getattr(imported, "marker", None)
if not callable(marker) or marker() is not True:
raise RuntimeError("worker-origin-probe-error")
finally:
sys.modules.pop(name, None)
if hasattr(parent, child_name):
delattr(parent, child_name)
package_path[:] = original_locations
importlib.invalidate_caches()
def _alembic_worker_entry(channel: Any) -> None:
"""Execute one operation after establishing a secret-free IPC boundary."""
_redirect_child_output()
exit_code = 0
try:
emit_success = False
with _candidate_origin_boundary():
forbidden_preloads = (
"alembic",
"sqlalchemy",
"asyncpg",
)
if any(
name == prefix or name.startswith(f"{prefix}.")
for name in sys.modules
for prefix in forbidden_preloads
):
raise RuntimeError("worker-import-boundary-error")
from alembic import command
initial = channel.recv()
if (
not isinstance(initial, tuple)
or len(initial) != 2
or initial[0] != "PAYLOAD"
or not isinstance(initial[1], dict)
):
raise RuntimeError("worker-payload-error")
payload = initial[1]
mode = payload.get("mode")
if mode == "protocol-ok-before-ready":
_worker_send(channel, ("OK",))
return
if mode == "setup-error":
raise RuntimeError("worker-setup-error")
if mode in {
"migrate",
"ipc-prepare-success",
"ipc-prepare-error",
}:
config, action, revision, fault = _prepare_child_migration(
payload,
command,
)
database_name = payload.get("database_name")
if not isinstance(database_name, str) or not (
_prepared_child_target_is_authoritative(
config,
database_name,
)
):
raise RuntimeError("worker-target-error")
if mode == "migrate" and fault is not None:
if fault == "0011-after-authority-ddl":
from sqlalchemy.engine import Connection
from ares.db.execution_lifecycle import (
POSTGRES_ADMISSION_AUTHORITY_V11_DDL,
)
original_execute = Connection.execute
final_authority_ddl = " ".join(
POSTGRES_ADMISSION_AUTHORITY_V11_DDL[-1].split()
)
def _fail_after_authority_ddl(
connection: Connection,
statement: object,
*args: object,
**kwargs: object,
) -> object:
result = original_execute(
connection,
statement,
*args,
**kwargs,
)
if " ".join(str(statement).split()) == final_authority_ddl:
raise RuntimeError("worker-test-error")
return result
Connection.execute = _fail_after_authority_ddl
else:
from alembic.operations import Operations
original_alter = Operations.alter_column
def _failing_alter(
operations: Operations,
table_name: str,
column_name: str,
*args: object,
**kwargs: object,
) -> object:
is_trace = table_name == "findings" and column_name == "trace_id"
is_credential = table_name == "credentials" and column_name in {
"cracked_value",
"cracked_value_enc",
}
if fault == "0005-before-alter" and is_trace:
raise RuntimeError("worker-test-error")
result = original_alter(
operations,
table_name,
column_name,
*args,
**kwargs,
)
if (fault == "0005-after-alter" and is_trace) or (
fault == "0006-after-rename" and is_credential
):
raise RuntimeError("worker-test-error")
return result
Operations.alter_column = _failing_alter
elif mode in {
"success",
"success-dirty-origin",
"success-dirty-origin-removed",
"migration-error",
"block",
"never-ready",
"protocol-duplicate-ready",
"protocol-duplicate-ok",
"protocol-malformed",
"protocol-eof-before-terminal",
"protocol-success-crash",
"protocol-zero-without-success",
}:
revision = ""
config = None
action = None
else:
raise RuntimeError("worker-mode-error")
if mode == "never-ready":
threading.Event().wait()
_worker_send(channel, ("READY",))
if mode == "protocol-duplicate-ready":
_worker_send(channel, ("READY",))
go = channel.recv()
if go != ("GO",):
raise RuntimeError("worker-go-error")
if mode == "block":
threading.Event().wait()
if mode in {"migration-error", "ipc-prepare-error"}:
raise RuntimeError("worker-test-error")
if mode == "protocol-malformed":
_worker_send(channel, ("UNKNOWN",))
return
if mode in {
"protocol-eof-before-terminal",
"protocol-zero-without-success",
}:
return
if mode == "protocol-duplicate-ok":
_worker_send(channel, ("OK",))
_worker_send(channel, ("OK",))
return
if mode == "protocol-success-crash":
_worker_send(channel, ("OK",))
raise SystemExit(71)
if mode == "success-dirty-origin":
dirty_module = types.ModuleType("ares.worker_dirty_probe")
dirty_origin = _REPO_ROOT.parent / "worker_dirty_probe.py"
dirty_module.__file__ = str(dirty_origin)
dirty_module.__spec__ = importlib.machinery.ModuleSpec(
"ares.worker_dirty_probe",
loader=None,
origin=str(dirty_origin),
)
sys.modules["ares.worker_dirty_probe"] = dirty_module
if mode == "success-dirty-origin-removed":
_worker_import_then_remove_external_probe()
if mode == "migrate":
action(config, revision)
emit_success = True
if emit_success:
_worker_send(channel, ("OK",))
except Exception as exc:
exit_code = 70
try:
_worker_send(channel, ("ERROR", _safe_exception_type(exc)))
except (BrokenPipeError, EOFError, OSError):
pass
finally:
channel.close()
if exit_code:
raise SystemExit(exit_code)
async def _wait_for_process_exit(process: Any, timeout: float) -> bool:
deadline = time.monotonic() + timeout
while process.is_alive() and time.monotonic() < deadline:
process.join(timeout=0)
await asyncio.sleep(_WORKER_POLL_SECONDS)
process.join(timeout=0)
return not process.is_alive() and process.exitcode is not None
def _add_fixed_cleanup_note(
primary: Exception,
action: str,
cleanup_error: Exception,
) -> None:
primary.add_note(f"Alembic worker cleanup failed [{action}: {type(cleanup_error).__name__}]")
def _close_unowned_endpoint(
endpoint: Any,
action: str,
primary: Exception,
) -> None:
if endpoint is None:
return
try:
endpoint.close()
except Exception as cleanup_error:
_add_fixed_cleanup_note(primary, action, cleanup_error)
def _process_may_have_started(process: Any) -> bool:
try:
if process.pid is not None:
return True
except Exception:
return True
try:
return bool(process.is_alive())
except Exception:
return True
def _close_settled_worker_resources(worker: _OwnedWorker) -> None:
failures: list[tuple[str, Exception]] = []
if not worker.parent_closed:
try:
worker.channel.close()
worker.parent_closed = True
except Exception as exc:
failures.append(("close-parent-endpoint", exc))
if worker.child_channel is not None and not worker.child_closed:
try:
worker.child_channel.close()
worker.child_closed = True
except Exception as exc:
failures.append(("close-child-endpoint", exc))
if not worker.process_closed:
try:
worker.process.close()
worker.process_closed = True
except Exception as exc:
failures.append(("close-process", exc))
if failures:
error = RuntimeError("Alembic worker resource closure failed")
for action, failure in failures:
_add_fixed_cleanup_note(error, action, failure)
raise error from None
worker.closed = True
_OWNED_WORKERS.discard(worker)
async def _settle_worker(
worker: _OwnedWorker,
*,
terminate: bool,
) -> int:
if worker.closed:
raise RuntimeError("Alembic worker ownership was already closed")
if worker.death_proven:
exit_code = -1 if worker.settled_exit_code is None else worker.settled_exit_code
_close_settled_worker_resources(worker)
return exit_code
process = worker.process
try:
process_id = process.pid
except Exception:
process_id = None
worker.may_have_started = True
if process_id is None:
if worker.may_have_started:
raise RuntimeError("Alembic worker death was not proven")
worker.death_proven = True
worker.settled_exit_code = -1
_close_settled_worker_resources(worker)
return -1
if terminate and process.is_alive():
process.terminate()
settled = await _wait_for_process_exit(
process,
_WORKER_SETTLE_SECONDS,
)
if not settled and terminate:
process.kill()
settled = await _wait_for_process_exit(
process,
_WORKER_SETTLE_SECONDS,
)
if not settled:
raise RuntimeError("Alembic worker death was not proven")
exit_code = int(process.exitcode)
worker.death_proven = True
worker.settled_exit_code = exit_code
process.join(timeout=0)
_close_settled_worker_resources(worker)
return exit_code
async def _receive_worker_frame(
worker: _OwnedWorker,
*,
deadline: float,
) -> tuple[str, ...] | None:
while time.monotonic() < deadline:
try:
has_frame = worker.channel.poll()
except (BrokenPipeError, EOFError, OSError):
worker.eof = True
return None
if has_frame:
try:
frame = worker.channel.recv()
except (BrokenPipeError, EOFError, OSError):
worker.eof = True
return None
if not isinstance(frame, tuple) or not all(isinstance(item, str) for item in frame):
raise RuntimeError("Alembic worker failed [ProtocolError]")
return frame
if not worker.process.is_alive():
await asyncio.sleep(0)
try:
has_frame = worker.channel.poll()
except (BrokenPipeError, EOFError, OSError):
worker.eof = True
return None
if has_frame:
continue
try:
worker.channel.recv()
except (BrokenPipeError, EOFError, OSError):
worker.eof = True
return None
raise RuntimeError("Alembic worker failed [ProtocolError]")
await asyncio.sleep(_WORKER_POLL_SECONDS)
raise TimeoutError
async def _run_worker(
payload: dict[str, str],
*,
timeout: float,
started: asyncio.Event | None = None,
) -> None:
parent_channel = None
child_channel = None
process = None
pipe_resources: tuple[Any, ...] = ()
try:
context = multiprocessing.get_context("spawn")
pipe_result = context.Pipe(duplex=True)
if isinstance(pipe_result, (tuple, list)):
pipe_resources = tuple(pipe_result)
if len(pipe_resources) != 2:
raise RuntimeError("worker-pipe-construction-error")
parent_channel, child_channel = pipe_resources
except Exception as exc:
primary = RuntimeError(f"Alembic worker startup failed [{type(exc).__name__}]")
for index, endpoint in enumerate(pipe_resources):
_close_unowned_endpoint(
endpoint,
f"close-pipe-endpoint-{index}",
primary,
)
raise primary from None
try:
process = context.Process(
target=_alembic_worker_entry,
args=(child_channel,),
name="ares-alembic-worker",
)
except Exception as exc:
primary = RuntimeError(f"Alembic worker startup failed [{type(exc).__name__}]")
_close_unowned_endpoint(
parent_channel,
"close-parent-endpoint",
primary,
)
_close_unowned_endpoint(
child_channel,
"close-child-endpoint",
primary,
)
raise primary from None
worker = _OwnedWorker(
process=process,
channel=parent_channel,
child_channel=child_channel,
)
_OWNED_WORKERS.add(worker)
try:
worker.start_attempted = True
process.start()
worker.may_have_started = True
child_channel.close()
worker.child_closed = True
parent_channel.send(("PAYLOAD", payload))
except Exception as exc:
worker.may_have_started = worker.may_have_started or _process_may_have_started(process)
primary = RuntimeError(f"Alembic worker startup failed [{type(exc).__name__}]")
try:
await asyncio.shield(_settle_worker(worker, terminate=True))
except Exception as cleanup_error:
_add_fixed_cleanup_note(
primary,
"settle-startup-worker",
cleanup_error,
)
raise primary from None
protocol_failure = False
terminal: tuple[str, ...] | None = None
try:
ready_deadline = time.monotonic() + _WORKER_READY_SECONDS
ready = await _receive_worker_frame(
worker,
deadline=ready_deadline,
)
if ready != ("READY",):
setup_error = (
ready is not None
and len(ready) == 2
and ready[0] == "ERROR"
and _SAFE_EXCEPTION_TYPE.fullmatch(ready[1]) is not None
)
if setup_error:
after_setup_error = await _receive_worker_frame(
worker,
deadline=time.monotonic() + _WORKER_SETTLE_SECONDS,
)
exit_code = await _settle_worker(
worker,
terminate=False,
)
if after_setup_error is None and exit_code == 70:
raise RuntimeError(f"Alembic worker failed [{ready[1]}]") from None
raise RuntimeError("Alembic worker failed [ProtocolError]")
if started is not None:
started.set()
parent_channel.send(("GO",))
terminal_deadline = time.monotonic() + timeout
terminal = await _receive_worker_frame(
worker,
deadline=terminal_deadline,
)
if terminal is None or (
terminal != ("OK",)
and not (
len(terminal) == 2
and terminal[0] == "ERROR"
and _SAFE_EXCEPTION_TYPE.fullmatch(terminal[1]) is not None
)
):
raise RuntimeError("Alembic worker failed [ProtocolError]")
after_terminal = await _receive_worker_frame(
worker,
deadline=time.monotonic() + _WORKER_SETTLE_SECONDS,
)
if after_terminal is not None:
raise RuntimeError("Alembic worker failed [ProtocolError]")
exit_code = await _settle_worker(worker, terminate=False)
if terminal == ("OK",) and exit_code == 0:
return
if terminal[0] == "ERROR" and exit_code == 70:
raise RuntimeError(f"Alembic worker failed [{terminal[1]}]") from None
raise RuntimeError("Alembic worker failed [ProtocolError]")
except TimeoutError:
protocol_failure = True
raise RuntimeError("Alembic worker timed out") from None
except asyncio.CancelledError:
protocol_failure = True
raise
except RuntimeError:
protocol_failure = True
raise
finally:
if worker in _OWNED_WORKERS:
try:
await asyncio.shield(_settle_worker(worker, terminate=protocol_failure))
except RuntimeError:
raise RuntimeError("Alembic worker settlement failed") from None
async def _alembic(
harness: _MigrationHarness,
operation: str,
revision: str,
*,
fault: str | None = None,
timeout: float = _WORKER_TIMEOUT_SECONDS,
) -> None:
if operation not in {"upgrade", "downgrade"}:
raise AssertionError("unknown migration operation")
payload = {
"mode": "migrate",
"database_name": harness.database_name,
"operation": operation,
"revision": revision,
}
if fault is not None:
payload["fault"] = fault
await _run_worker(
payload,
timeout=timeout,
)
def _sanitized_setup_failure(action: str, exc: Exception) -> RuntimeError:
return RuntimeError(f"PostgreSQL migration setup failed [{action}: {type(exc).__name__}]")
async def _attempt_cleanup(
action: str,
cleanup: Callable[[], Awaitable[object]],
failures: list[str],
) -> None:
try:
await asyncio.wait_for(
cleanup(),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
except Exception as exc:
failures.append(f"{action}: {type(exc).__name__}")
async def _settle_owned_workers_for_cleanup(
failures: list[str],
) -> bool:
for worker in tuple(_OWNED_WORKERS):
try:
await _settle_worker(worker, terminate=True)
except Exception as exc:
failures.append(f"settle-alembic-worker: {type(exc).__name__}")
return not _OWNED_WORKERS
@asynccontextmanager
async def _postgres_harness() -> AsyncIterator[_MigrationHarness]:
config = _postgres_config()
try:
import asyncpg
except ImportError:
pytest.fail(
"asyncpg is required for configured PostgreSQL tests",
pytrace=False,
)
database_name = f"ares_migration_{uuid4().hex}"
_require_fixed(
_SAFE_DATABASE_NAME.fullmatch(database_name) is not None,
"generated PostgreSQL database identifier was unsafe",
)
_require_fixed(
database_name != config.maintenance_database,
"generated PostgreSQL database identifier collided",
)
admin = None
creation_attempted = False
cleanup_failures: list[str] = []
try:
try:
admin = await asyncio.wait_for(
asyncpg.connect(
host=config.host,
port=config.port,
user=config.user,
database=config.maintenance_database,
),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
except Exception as exc:
raise _sanitized_setup_failure("admin-connect", exc) from None
creation_attempted = True
try:
await asyncio.wait_for(
admin.execute(f'CREATE DATABASE "{database_name}"'),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
except Exception as exc:
raise _sanitized_setup_failure("database-create", exc) from None
yield _MigrationHarness(
config=config,
database_name=database_name,
)
finally:
primary_failure = sys.exception()
workers_settled = await _settle_owned_workers_for_cleanup(cleanup_failures)
if creation_attempted and admin is not None and workers_settled:
async def _drop_database() -> None:
await asyncio.wait_for(
admin.execute(
"""
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname=$1 AND pid <> pg_backend_pid()
""",
database_name,
),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
await asyncio.wait_for(
admin.execute(f'DROP DATABASE IF EXISTS "{database_name}" WITH (FORCE)'),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
await _attempt_cleanup(
"drop-test-database",
_drop_database,
cleanup_failures,
)
elif creation_attempted and not workers_settled:
cleanup_failures.append("drop-test-database: WorkerOwnershipError")
if admin is not None:
await _attempt_cleanup(
"close-admin-connection",
admin.close,
cleanup_failures,
)
if cleanup_failures:
if primary_failure is not None:
for failure in cleanup_failures:
primary_failure.add_note(f"PostgreSQL migration cleanup failure [{failure}]")
else:
raise RuntimeError(
"PostgreSQL migration cleanup failed: " + "; ".join(cleanup_failures)
) from None
async def _connect(harness: _MigrationHarness) -> Any:
try:
import asyncpg
except ImportError:
pytest.fail(
"asyncpg is required for configured PostgreSQL tests",
pytrace=False,
)
try:
connection = await asyncio.wait_for(
asyncpg.connect(
host=harness.config.host,
port=harness.config.port,
user=harness.config.user,
database=harness.database_name,
),
timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
)
return _BoundedConnection(connection)
except Exception as exc:
raise _sanitized_setup_failure("catalog-connect", exc) from None
async def _postgres_integrity_failure(
connection: Any,
statement: str,
*parameters: object,
expected_type: str,
) -> bool:
rejected = False
try:
await connection.execute(statement, *parameters)
except Exception as exc:
rejected = type(exc).__name__ == expected_type
reusable = await connection.fetchval("SELECT 1") == 1
return rejected and reusable
async def _seed_postgres_contract(connection: Any) -> None:
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"campaign-contract",
"Synthetic campaign",
)
await connection.execute(
"""
INSERT INTO module_runs(id, campaign_id, module_id, outcome)
VALUES($1, $2, $3, $4)
""",
"run-contract",
"campaign-contract",
"module",
"complete",
)
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title, description, severity
) VALUES($1, $2, $3, $4, $5, $6)
""",
"finding-contract",
"campaign-contract",
"module",
"Synthetic finding",
"Synthetic description",
"low",
)
await connection.execute(
"""
INSERT INTO hosts(id, campaign_id, ip_address)
VALUES($1, $2, $3)
""",
"host-contract",
"campaign-contract",
"192.0.2.10",
)
await connection.execute(
"""
INSERT INTO credentials(
id, campaign_id, host_id, username, cred_type
) VALUES($1, $2, $3, $4, $5)
""",
"credential-contract",
"campaign-contract",
"host-contract",
"synthetic-user",
"password",
)
await connection.execute(
"""
INSERT INTO loot(id, campaign_id, host_id, loot_type, name)
VALUES($1, $2, $3, $4, $5)
""",
"loot-contract",
"campaign-contract",
"host-contract",
"metadata",
"Synthetic loot",
)
await connection.execute(
"INSERT INTO audit_log(campaign_id, action) VALUES($1, $2)",
"campaign-contract",
"synthetic-action",
)
await connection.execute(
"""
INSERT INTO users(id, username, hashed_password)
VALUES($1, $2, $3)
""",
"user-contract",
"synthetic-account",
"synthetic-hash",
)
await connection.execute(
"""
INSERT INTO api_keys(id, user_id, name, key_hash, key_prefix)
VALUES($1, $2, $3, $4, $5)
""",
"api-key-contract",
"user-contract",
"Synthetic key",
"synthetic-hash",
"synthetic",
)
await connection.execute(
"""
INSERT INTO refresh_tokens(id, user_id, expires_at)
VALUES($1, $2, $3)
""",
"refresh-contract",
"user-contract",
datetime(2099, 1, 1, tzinfo=UTC),
)
await connection.execute(
"""
INSERT INTO revoked_access_tokens(jti, user_id, expires_at)
VALUES($1, $2, $3)
""",
"revoked-contract",
"user-contract",
datetime(2099, 1, 1, tzinfo=UTC),
)
await connection.execute(
"""
INSERT INTO rate_limit_events(ip_address, bucket)
VALUES($1, $2)
""",
"192.0.2.20",
"synthetic-bucket",
)
async def _postgres_isolation_fingerprint(
connection: Any,
) -> _PostgresIsolationFingerprint:
try:
schemas = tuple(
str(row["schema_name"])
for row in await connection.fetch(
"""
SELECT nspname AS schema_name
FROM pg_namespace
WHERE nspname !~ '^pg_'
AND nspname <> 'information_schema'
ORDER BY nspname
"""
)
)
relations = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
namespace.nspname AS schema_name,
relation.relname AS relation_name,
relation.relkind::text AS relation_kind,
relation.relpersistence::text AS persistence
FROM pg_class AS relation
JOIN pg_namespace AS namespace
ON namespace.oid=relation.relnamespace
WHERE namespace.nspname !~ '^pg_'
AND namespace.nspname <> 'information_schema'
ORDER BY
namespace.nspname,
relation.relname,
relation.relkind
"""
)
)
columns = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
namespace.nspname AS schema_name,
relation.relname AS relation_name,
attribute.attnum AS ordinal_position,
attribute.attname AS column_name,
format_type(
attribute.atttypid,
attribute.atttypmod
) AS formatted_type,
attribute.attnotnull AS not_null,
pg_get_expr(
default_value.adbin,
default_value.adrelid
) AS default_expression,
attribute.attidentity::text AS identity_kind,
attribute.attgenerated::text AS generated_kind
FROM pg_attribute AS attribute
JOIN pg_class AS relation
ON relation.oid=attribute.attrelid
JOIN pg_namespace AS namespace
ON namespace.oid=relation.relnamespace
LEFT JOIN pg_attrdef AS default_value
ON default_value.adrelid=attribute.attrelid
AND default_value.adnum=attribute.attnum
WHERE namespace.nspname !~ '^pg_'
AND namespace.nspname <> 'information_schema'
AND attribute.attnum > 0
AND NOT attribute.attisdropped
ORDER BY
namespace.nspname,
relation.relname,
attribute.attnum
"""
)
)
constraints = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
namespace.nspname AS schema_name,
relation.relname AS relation_name,
constraint_value.conname AS constraint_name,
constraint_value.contype::text AS constraint_type,
pg_get_constraintdef(
constraint_value.oid,
true
) AS definition
FROM pg_constraint AS constraint_value
JOIN pg_class AS relation
ON relation.oid=constraint_value.conrelid
JOIN pg_namespace AS namespace
ON namespace.oid=relation.relnamespace
WHERE namespace.nspname !~ '^pg_'
AND namespace.nspname <> 'information_schema'
ORDER BY
namespace.nspname,
relation.relname,
constraint_value.conname
"""
)
)
indexes = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
namespace.nspname AS schema_name,
table_relation.relname AS relation_name,
index_relation.relname AS index_name,
index_value.indisunique AS is_unique,
index_value.indisprimary AS is_primary,
pg_get_indexdef(index_relation.oid) AS definition
FROM pg_index AS index_value
JOIN pg_class AS table_relation
ON table_relation.oid=index_value.indrelid
JOIN pg_class AS index_relation
ON index_relation.oid=index_value.indexrelid
JOIN pg_namespace AS namespace
ON namespace.oid=table_relation.relnamespace
WHERE namespace.nspname !~ '^pg_'
AND namespace.nspname <> 'information_schema'
ORDER BY
namespace.nspname,
table_relation.relname,
index_relation.relname
"""
)
)
sequences = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
namespace.nspname AS schema_name,
relation.relname AS sequence_name,
format_type(
sequence_value.seqtypid,
NULL
) AS data_type,
sequence_value.seqstart AS start_value,
sequence_value.seqmin AS minimum_value,
sequence_value.seqmax AS maximum_value,
sequence_value.seqincrement AS increment_value,
sequence_value.seqcycle AS cycles,
sequence_value.seqcache AS cache_size
FROM pg_sequence AS sequence_value
JOIN pg_class AS relation
ON relation.oid=sequence_value.seqrelid
JOIN pg_namespace AS namespace
ON namespace.oid=relation.relnamespace
WHERE namespace.nspname !~ '^pg_'
AND namespace.nspname <> 'information_schema'
ORDER BY namespace.nspname, relation.relname
"""
)
)
version_rows = tuple(
(
str(row["schema_name"]),
str(row["version_state"]),
)
for row in await connection.fetch(
"""
SELECT
namespace.nspname AS schema_name,
table_to_xml(
relation.oid::regclass,
true,
false,
''
)::text AS version_state
FROM pg_class AS relation
JOIN pg_namespace AS namespace
ON namespace.oid=relation.relnamespace
WHERE namespace.nspname !~ '^pg_'
AND namespace.nspname <> 'information_schema'
AND relation.relkind IN ('r', 'p')
AND relation.relname='alembic_version'
ORDER BY namespace.nspname
"""
)
)
return _PostgresIsolationFingerprint(
schemas=schemas,
relations=relations,
columns=columns,
constraints=constraints,
indexes=indexes,
sequences=sequences,
versions=version_rows,
)
except Exception as exc:
raise _sanitized_setup_failure(
"isolation-fingerprint",
exc,
) from None
def _isolation_fingerprint_has_no_ares_state(
fingerprint: _PostgresIsolationFingerprint,
) -> bool:
forbidden_relations = _EXPECTED_TABLES | {"alembic_version"}
return not fingerprint.versions and all(
str(relation[1]) not in forbidden_relations for relation in fingerprint.relations
)
async def _database_fingerprint(
config: _PostgresConfig,
database_name: str,
) -> _PostgresIsolationFingerprint:
harness = _MigrationHarness(
config=config,
database_name=database_name,
)
connection = await _connect(harness)
try:
return await _postgres_isolation_fingerprint(connection)
finally:
primary = sys.exception()
try:
await connection.close()
except Exception as cleanup_error:
if primary is not None:
primary.add_note(
"PostgreSQL migration fingerprint cleanup failed "
f"[{type(cleanup_error).__name__}]"
)
else:
raise _sanitized_setup_failure(
"fingerprint-close",
cleanup_error,
) from None
async def _database_revision(
config: _PostgresConfig,
database_name: str,
) -> str | None:
harness = _MigrationHarness(
config=config,
database_name=database_name,
)
connection = await _connect(harness)
try:
return await _version(connection)
finally:
primary = sys.exception()
try:
await connection.close()
except Exception as cleanup_error:
if primary is not None:
primary.add_note(
f"PostgreSQL migration revision cleanup failed [{type(cleanup_error).__name__}]"
)
else:
raise _sanitized_setup_failure(
"revision-close",
cleanup_error,
) from None
async def _version(connection: Any) -> str | None:
exists = await connection.fetchval("SELECT to_regclass('alembic_version') IS NOT NULL")
if not exists:
return None
value = await connection.fetchval("SELECT version_num FROM alembic_version")
return None if value is None else str(value)
def _normalize_postgres_expression(value: str) -> str:
normalized = value.lower().replace('"', "")
normalized = re.sub(r"\s+", "", normalized)
while normalized.startswith("(") and normalized.endswith(")"):
normalized = normalized[1:-1]
if normalized.startswith("check(") and normalized.endswith(")"):
normalized = normalized[6:-1]
return normalized.replace("(", "").replace(")", "")
def _postgres_default_kind(
value: object,
*,
data_type: str,
) -> str | None:
if value is None:
return None
normalized = str(value).strip()
lowered = normalized.lower()
if lowered.startswith("nextval("):
return "sequence"
if "now()" in lowered:
return "now"
while normalized.startswith("(") and normalized.endswith(")"):
normalized = normalized[1:-1].strip()
cast_position = normalized.rfind("::")
if cast_position >= 0:
normalized = normalized[:cast_position]
normalized = normalized.strip()
if len(normalized) >= 2 and normalized[0] == normalized[-1] == "'":
normalized = normalized[1:-1].replace("''", "'")
if data_type == "double precision":
try:
numeric = Decimal(normalized)
except InvalidOperation:
pass
else:
if numeric.is_finite():
normalized = format(numeric, "f")
if "." not in normalized:
normalized += ".0"
return f"literal:{normalized}"
async def _postgres_catalog_contract(
connection: Any,
) -> _PostgresCatalogContract:
tables = tuple(
str(row["table_name"])
for row in await connection.fetch(
"""
SELECT table_name
FROM information_schema.tables
WHERE table_schema=current_schema()
AND table_type='BASE TABLE'
AND table_name <> 'alembic_version'
ORDER BY table_name
"""
)
)
columns = tuple(
(
str(row["table_name"]),
str(row["column_name"]),
str(row["data_type"]),
str(row["is_nullable"]) == "YES",
_postgres_default_kind(
row["column_default"],
data_type=str(row["data_type"]),
),
)
for row in await connection.fetch(
"""
SELECT
table_name,
ordinal_position,
column_name,
data_type,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name <> 'alembic_version'
ORDER BY table_name, ordinal_position
"""
)
)
primary_keys = tuple(
(
str(row["table_name"]),
tuple(str(value) for value in row["columns"]),
)
for row in await connection.fetch(
"""
SELECT
relation.relname AS table_name,
ARRAY(
SELECT attribute.attname
FROM unnest(constraint_record.conkey)
WITH ORDINALITY AS key(attnum, position)
JOIN pg_attribute AS attribute
ON attribute.attrelid=constraint_record.conrelid
AND attribute.attnum=key.attnum
ORDER BY key.position
) AS columns
FROM pg_constraint AS constraint_record
JOIN pg_class AS relation
ON relation.oid=constraint_record.conrelid
WHERE relation.relnamespace=current_schema()::regnamespace
AND constraint_record.contype='p'
AND relation.relname <> 'alembic_version'
ORDER BY relation.relname
"""
)
)
unique_constraints = tuple(
(
str(row["table_name"]),
str(row["constraint_name"]),
tuple(str(value) for value in row["columns"]),
)
for row in await connection.fetch(
"""
SELECT
relation.relname AS table_name,
constraint_record.conname AS constraint_name,
ARRAY(
SELECT attribute.attname
FROM unnest(constraint_record.conkey)
WITH ORDINALITY AS key(attnum, position)
JOIN pg_attribute AS attribute
ON attribute.attrelid=constraint_record.conrelid
AND attribute.attnum=key.attnum
ORDER BY key.position
) AS columns
FROM pg_constraint AS constraint_record
JOIN pg_class AS relation
ON relation.oid=constraint_record.conrelid
WHERE relation.relnamespace=current_schema()::regnamespace
AND constraint_record.contype='u'
ORDER BY relation.relname, constraint_record.conname
"""
)
)
checks = tuple(
(
str(row["table_name"]),
str(row["constraint_name"]),
_normalize_postgres_expression(str(row["definition"])),
)
for row in await connection.fetch(
"""
SELECT
relation.relname AS table_name,
constraint_record.conname AS constraint_name,
pg_get_constraintdef(constraint_record.oid) AS definition
FROM pg_constraint AS constraint_record
JOIN pg_class AS relation
ON relation.oid=constraint_record.conrelid
WHERE relation.relnamespace=current_schema()::regnamespace
AND constraint_record.contype='c'
ORDER BY relation.relname, constraint_record.conname
"""
)
)
foreign_keys = tuple(
(
str(row["table_name"]),
str(row["constraint_name"]),
tuple(str(value) for value in row["local_columns"]),
str(row["remote_table"]),
tuple(str(value) for value in row["remote_columns"]),
str(row["update_action"]),
str(row["delete_action"]),
)
for row in await connection.fetch(
"""
SELECT
source.relname AS table_name,
constraint_record.conname AS constraint_name,
ARRAY(
SELECT attribute.attname
FROM unnest(constraint_record.conkey)
WITH ORDINALITY AS key(attnum, position)
JOIN pg_attribute AS attribute
ON attribute.attrelid=constraint_record.conrelid
AND attribute.attnum=key.attnum
ORDER BY key.position
) AS local_columns,
target.relname AS remote_table,
ARRAY(
SELECT attribute.attname
FROM unnest(constraint_record.confkey)
WITH ORDINALITY AS key(attnum, position)
JOIN pg_attribute AS attribute
ON attribute.attrelid=constraint_record.confrelid
AND attribute.attnum=key.attnum
ORDER BY key.position
) AS remote_columns,
CASE constraint_record.confupdtype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS update_action,
CASE constraint_record.confdeltype
WHEN 'a' THEN 'NO ACTION'
WHEN 'r' THEN 'RESTRICT'
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
END AS delete_action
FROM pg_constraint AS constraint_record
JOIN pg_class AS source
ON source.oid=constraint_record.conrelid
JOIN pg_class AS target
ON target.oid=constraint_record.confrelid
WHERE source.relnamespace=current_schema()::regnamespace
AND constraint_record.contype='f'
ORDER BY source.relname, constraint_record.conname
"""
)
)
indexes = tuple(
(
str(row["table_name"]),
str(row["index_name"]),
tuple(str(value) for value in row["columns"]),
bool(row["is_unique"]),
(
None
if row["predicate"] is None
else _normalize_postgres_expression(str(row["predicate"]))
),
)
for row in await connection.fetch(
"""
SELECT
table_relation.relname AS table_name,
index_relation.relname AS index_name,
ARRAY(
SELECT attribute.attname
FROM unnest(index_catalog.indkey)
WITH ORDINALITY AS key(attnum, position)
JOIN pg_attribute AS attribute
ON attribute.attrelid=table_relation.oid
AND attribute.attnum=key.attnum
WHERE key.position <= index_catalog.indnkeyatts
ORDER BY key.position
) AS columns,
index_catalog.indisunique AS is_unique,
pg_get_expr(
index_catalog.indpred,
index_catalog.indrelid
) AS predicate
FROM pg_index AS index_catalog
JOIN pg_class AS table_relation
ON table_relation.oid=index_catalog.indrelid
JOIN pg_class AS index_relation
ON index_relation.oid=index_catalog.indexrelid
WHERE table_relation.relnamespace=current_schema()::regnamespace
AND NOT EXISTS (
SELECT 1 FROM pg_constraint AS constraint_record
WHERE constraint_record.conindid=index_catalog.indexrelid
)
ORDER BY table_relation.relname, index_relation.relname
"""
)
)
sequences = tuple(
str(row["sequence_name"])
for row in await connection.fetch(
"""
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema=current_schema()
ORDER BY sequence_name
"""
)
)
return _PostgresCatalogContract(
tables=tables,
columns=columns,
primary_keys=primary_keys,
unique_constraints=unique_constraints,
checks=checks,
foreign_keys=foreign_keys,
indexes=indexes,
sequences=sequences,
)
def _fixed_postgres_contract(revision: str) -> _PostgresCatalogContract:
if revision == "base":
return _PostgresCatalogContract((), (), (), (), (), (), (), ())
tables = {
"campaigns",
"module_runs",
"findings",
"hosts",
"credentials",
"loot",
"audit_log",
"users",
"api_keys",
"refresh_tokens",
}
if revision >= "0003":
tables.add("rate_limit_events")
if revision >= "0004":
tables.add("revoked_access_tokens")
ordered_tables = tuple(sorted(tables))
order = {
table: tuple(columns)
for table, columns in _EXPECTED_COLUMN_ORDER.items()
if table in tables
}
if revision == "0001":
order["findings"] = tuple(column for column in order["findings"] if column != "trace_id")
if revision < "0006":
order["credentials"] = tuple(
"cracked_value" if column == "cracked_value_enc" else column
for column in order["credentials"]
)
columns: list[tuple[str, str, str, bool, str | None]] = []
for table in ordered_tables:
for column in order[table]:
canonical_column = "cracked_value_enc" if column == "cracked_value" else column
key = (table, canonical_column)
if key in _POSTGRES_INTEGER_COLUMNS:
data_type = "integer"
elif key in _POSTGRES_FLOAT_COLUMNS:
data_type = "double precision"
elif key in _POSTGRES_TIMESTAMP_COLUMNS:
data_type = "timestamp with time zone"
else:
data_type = "text"
nullable = key in _POSTGRES_NULLABLE_COLUMNS
if table == "findings" and column == "trace_id":
nullable = revision < "0005"
columns.append(
(
table,
column,
data_type,
nullable,
_POSTGRES_DEFAULTS.get(key),
)
)
primary_keys = tuple(
(
table,
("jti" if table == "revoked_access_tokens" else "id",),
)
for table in ordered_tables
)
unique_constraints = tuple(
sorted(
(
("hosts", "uq_hosts_campaign_ip", ("campaign_id", "ip_address")),
("users", "uq_users_username", ("username",)),
)
)
)
foreign_key_names = {
("module_runs", "campaign_id"): "fk_module_runs_campaign",
("findings", "campaign_id"): "fk_findings_campaign",
("hosts", "campaign_id"): "fk_hosts_campaign",
("credentials", "campaign_id"): "fk_credentials_campaign",
("credentials", "host_id"): "fk_credentials_host",
("loot", "campaign_id"): "fk_loot_campaign",
("loot", "host_id"): "fk_loot_host",
("audit_log", "campaign_id"): "fk_audit_campaign",
("api_keys", "user_id"): "fk_api_keys_user",
("refresh_tokens", "user_id"): "fk_refresh_tokens_user",
}
foreign_keys = tuple(
sorted(
(
table,
foreign_key_names[(table, local)],
(local,),
remote,
("id",),
"NO ACTION",
action,
)
for table, local, remote, action in _EXPECTED_FOREIGN_KEYS
if table in tables
)
)
index_names = set(_EXPECTED_INDEXES)
if revision == "0001":
index_names.discard("idx_findings_cvss")
if revision < "0003":
index_names.difference_update({"idx_rle_ip", "idx_rle_timestamp", "idx_rle_blocked"})
if revision < "0004":
index_names.discard("idx_rat_expires")
indexes = tuple(
sorted(
(
_POSTGRES_INDEX_TABLES[name],
name,
columns,
False,
None,
)
for name, columns in _EXPECTED_INDEXES.items()
if name in index_names
)
)
checks = [
(
table,
name,
(f"{column}isnullorisfinite{column}" if nullable else f"isfinite{column}"),
)
for (table, column), (name, nullable) in (_POSTGRES_FINITE_TIMESTAMP_CONSTRAINTS.items())
if table in tables
]
if revision >= "0003":
checks.append(
(
"rate_limit_events",
"ck_rate_limit_events_blocked_bool",
"blocked=anyarray[0,1]",
)
)
sequences = ["audit_log_id_seq"]
if revision >= "0003":
sequences.append("rate_limit_events_id_seq")
return _PostgresCatalogContract(
tables=ordered_tables,
columns=tuple(columns),
primary_keys=primary_keys,
unique_constraints=unique_constraints,
checks=tuple(sorted(checks)),
foreign_keys=foreign_keys,
indexes=indexes,
sequences=tuple(sorted(sequences)),
)
def _fixed_legacy_postgres_contract(
revision: str,
) -> _PostgresCatalogContract:
repaired = _fixed_postgres_contract(revision)
tables = tuple(table for table in repaired.tables if table != "module_runs")
columns = tuple(
(
table,
column,
data_type,
(
True
if table == "findings" and column in {"cvss_score", "cvss_vector", "trace_id"}
else nullable
),
default,
)
for table, column, data_type, nullable, default in repaired.columns
if table != "module_runs"
)
indexes = tuple(index for index in repaired.indexes if index[0] != "module_runs") + (
(
"findings",
"idx_findings_validated",
("validated",),
False,
None,
),
)
return replace(
repaired,
tables=tables,
columns=columns,
primary_keys=tuple(item for item in repaired.primary_keys if item[0] != "module_runs"),
checks=(),
foreign_keys=(),
indexes=tuple(sorted(indexes)),
)
_POSTGRES_RUNTIME_LEGACY_CONSTRAINT_RECIPE = (
("0001", "module_runs", "fk_module_runs_campaign"),
("0001", "findings", "fk_findings_campaign"),
("0001", "hosts", "fk_hosts_campaign"),
("0001", "credentials", "fk_credentials_campaign"),
("0001", "credentials", "fk_credentials_host"),
("0001", "loot", "fk_loot_campaign"),
("0001", "loot", "fk_loot_host"),
("0001", "audit_log", "fk_audit_campaign"),
("0001", "api_keys", "fk_api_keys_user"),
("0001", "refresh_tokens", "fk_refresh_tokens_user"),
("0001", "campaigns", "ck_campaigns_created_at_finite"),
("0001", "campaigns", "ck_campaigns_updated_at_finite"),
("0001", "module_runs", "ck_module_runs_completed_at_finite"),
("0001", "findings", "ck_findings_discovered_at_finite"),
("0001", "hosts", "ck_hosts_first_seen_finite"),
("0001", "hosts", "ck_hosts_last_seen_finite"),
("0001", "credentials", "ck_credentials_captured_at_finite"),
("0001", "loot", "ck_loot_captured_at_finite"),
("0001", "audit_log", "ck_audit_log_timestamp_finite"),
("0001", "users", "ck_users_created_at_finite"),
("0001", "users", "ck_users_last_login_finite"),
("0001", "api_keys", "ck_api_keys_last_used_finite"),
("0001", "api_keys", "ck_api_keys_expires_at_finite"),
("0001", "api_keys", "ck_api_keys_created_at_finite"),
("0001", "refresh_tokens", "ck_refresh_tokens_expires_at_finite"),
("0001", "refresh_tokens", "ck_refresh_tokens_created_at_finite"),
("0001", "refresh_tokens", "ck_refresh_tokens_used_at_finite"),
(
"0003",
"rate_limit_events",
"ck_rate_limit_events_blocked_bool",
),
(
"0003",
"rate_limit_events",
"ck_rate_limit_events_timestamp_finite",
),
(
"0004",
"revoked_access_tokens",
"ck_revoked_access_tokens_revoked_at_finite",
),
(
"0004",
"revoked_access_tokens",
"ck_revoked_access_tokens_expires_at_finite",
),
)
def _postgres_runtime_legacy_recipe(
revision: str,
) -> _PostgresLegacyRecipe:
if revision not in {"0001", "0002", "0003", "0004", "0005"}:
raise AssertionError("unsupported PostgreSQL legacy recipe revision")
nullable_columns = [
("findings", "cvss_score"),
("findings", "cvss_vector"),
]
if revision >= "0002":
nullable_columns.append(("findings", "trace_id"))
return _PostgresLegacyRecipe(
constraints_to_drop=tuple(
(table, constraint)
for introduced, table, constraint in (_POSTGRES_RUNTIME_LEGACY_CONSTRAINT_RECIPE)
if introduced <= revision
),
tables_to_drop=("module_runs",),
nullable_columns=tuple(nullable_columns),
indexes_to_create=(
(
"idx_findings_validated",
"findings",
("validated",),
),
),
)
@pytest.fixture(autouse=True)
def _forbid_runtime_bootstrap(monkeypatch: pytest.MonkeyPatch) -> None:
from ares.db.postgres import PostgresDatabase
async def _prohibited_connect(*_args: object, **_kwargs: object) -> None:
pytest.fail(
"runtime PostgreSQL bootstrap was invoked",
pytrace=False,
)
monkeypatch.setattr(PostgresDatabase, "connect", _prohibited_connect)
def _clear_postgres_environment(monkeypatch: pytest.MonkeyPatch) -> None:
for name in _POSTGRES_ENV:
monkeypatch.delenv(name, raising=False)
def test_postgres_legacy_recipe_does_not_call_expected_oracle(
monkeypatch: pytest.MonkeyPatch,
) -> None:
expected_recipe = _postgres_runtime_legacy_recipe("0005")
def _poisoned_oracle(*_args: object, **_kwargs: object) -> object:
raise AssertionError("expected oracle was traversed by fixture recipe")
module = sys.modules[__name__]
monkeypatch.setattr(
module,
"_fixed_postgres_contract",
_poisoned_oracle,
)
monkeypatch.setattr(
module,
"_fixed_legacy_postgres_contract",
_poisoned_oracle,
)
constructed = _postgres_runtime_legacy_recipe("0005")
_require_fixed(
constructed == expected_recipe,
"PostgreSQL legacy recipe depended on an expected oracle",
)
def test_postgres_legacy_recipe_and_oracle_have_no_mutable_alias() -> None:
def _deeply_immutable(value: object) -> bool:
if isinstance(value, tuple):
return all(_deeply_immutable(item) for item in value)
return isinstance(value, (str, int, bool, type(None)))
recipe = _postgres_runtime_legacy_recipe("0005")
oracle = _fixed_legacy_postgres_contract("0005")
recipe_constraints = list(recipe.constraints_to_drop)
oracle_tables = list(oracle.tables)
recipe_constraints.append(("synthetic_table", "synthetic_constraint"))
oracle_tables.append("synthetic_table")
independent = (
tuple(recipe_constraints) != recipe.constraints_to_drop
and tuple(oracle_tables) != oracle.tables
and _postgres_runtime_legacy_recipe("0005") == recipe
and _fixed_legacy_postgres_contract("0005") == oracle
and all(
_deeply_immutable(value)
for value in (
recipe.constraints_to_drop,
recipe.tables_to_drop,
recipe.nullable_columns,
recipe.indexes_to_create,
oracle.tables,
oracle.columns,
oracle.checks,
oracle.foreign_keys,
)
)
)
_require_fixed(
independent,
"PostgreSQL legacy recipe and oracle share mutable state",
)
def test_postgres_configuration_all_absent_skips(
monkeypatch: pytest.MonkeyPatch,
) -> None:
_clear_postgres_environment(monkeypatch)
skipped = False
try:
_postgres_config()
except pytest.skip.Exception:
skipped = True
_require_fixed(
skipped,
"absent PostgreSQL configuration did not intentionally skip",
)
def test_noncanonical_database_names_do_not_configure_harness(
monkeypatch: pytest.MonkeyPatch,
) -> None:
_clear_postgres_environment(monkeypatch)
monkeypatch.setenv("ARES_TEST_POSTGRES_DATABASE", "ignored")
monkeypatch.setenv("ARES_DATABASE_URL", "ignored")
skipped = False
try:
_postgres_config()
except pytest.skip.Exception:
skipped = True
_require_fixed(
skipped,
"noncanonical database configuration activated the harness",
)
@pytest.mark.parametrize(
"values",
[
{"ARES_TEST_POSTGRES_HOST": "configured"},
{
"ARES_TEST_POSTGRES_HOST": "",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": " ",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "05432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "+5432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "65536",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": "",
"ARES_TEST_POSTGRES_PORT": "",
"ARES_TEST_POSTGRES_USER": "",
"ARES_TEST_POSTGRES_DB": "",
},
{
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "not-a-port",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
{
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "\u0665\u0664\u0663\u0662",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
},
],
ids=(
"partial",
"empty",
"whitespace",
"empty-port",
"noncanonical-port",
"signed-port",
"out-of-range-port",
"all-empty",
"malformed-port",
"non-ascii-port",
),
)
def test_postgres_configuration_invalid_states_fail(
monkeypatch: pytest.MonkeyPatch,
values: dict[str, str],
) -> None:
_clear_postgres_environment(monkeypatch)
for name, value in values.items():
monkeypatch.setenv(name, value)
failed = False
try:
_postgres_config()
except pytest.fail.Exception:
failed = True
_require_fixed(
failed,
"invalid PostgreSQL configuration did not fail",
)
def test_postgres_configuration_complete_shape_is_accepted(
monkeypatch: pytest.MonkeyPatch,
) -> None:
_clear_postgres_environment(monkeypatch)
values = {
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
}
for name, value in values.items():
monkeypatch.setenv(name, value)
config = _postgres_config()
accepted = (
config.port == 5432
and bool(config.host)
and bool(config.user)
and bool(config.maintenance_database)
)
_require_fixed(
accepted,
"complete PostgreSQL configuration was rejected",
)
@pytest.mark.asyncio
async def test_complete_configuration_without_driver_fails_safely(
monkeypatch: pytest.MonkeyPatch,
) -> None:
_clear_postgres_environment(monkeypatch)
values = {
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
}
for name, value in values.items():
monkeypatch.setenv(name, value)
real_import = builtins.__import__
def _without_asyncpg(
name: str,
*args: object,
**kwargs: object,
) -> object:
if name == "asyncpg":
raise ImportError
return real_import(name, *args, **kwargs)
monkeypatch.setattr(builtins, "__import__", _without_asyncpg)
failed = False
try:
async with _postgres_harness():
pytest.fail(
"driver gate unexpectedly entered database setup",
pytrace=False,
)
except pytest.fail.Exception:
failed = True
_require_fixed(
failed,
"configured PostgreSQL harness accepted a missing driver",
)
@pytest.mark.asyncio
async def test_owned_worker_completion_and_error_are_settled() -> None:
await _run_worker({"mode": "success"}, timeout=2.0)
failed = False
try:
await _run_worker({"mode": "migration-error"}, timeout=2.0)
except RuntimeError:
failed = True
_require_fixed(failed, "worker exception did not propagate")
_require_fixed(
len(_OWNED_WORKERS) == 0,
"completed worker remained owned",
)
def test_worker_ready_operation_and_settlement_budgets_are_independent() -> None:
worst_case = _WORKER_READY_SECONDS + _WORKER_TIMEOUT_SECONDS + (2 * _WORKER_SETTLE_SECONDS)
budgets_are_independent = (
_WORKER_READY_SECONDS == 15.0
and _WORKER_TIMEOUT_SECONDS == 25.0
and _WORKER_SETTLE_SECONDS == 5.0
and worst_case == 50.0
and worst_case < 60.0
)
_require_fixed(
budgets_are_independent,
"worker readiness, operation, and settlement budgets must remain bounded",
)
@pytest.mark.asyncio
async def test_worker_that_never_reaches_ready_is_settled(
monkeypatch: pytest.MonkeyPatch,
) -> None:
monkeypatch.setattr(
sys.modules[__name__],
"_WORKER_READY_SECONDS",
0.1,
)
failed = False
try:
await _run_worker({"mode": "never-ready"}, timeout=0.1)
except RuntimeError:
failed = True
_require_fixed(
failed and not _OWNED_WORKERS,
"pre-READY timeout did not settle the owned worker",
)
@pytest.mark.asyncio
async def test_owned_worker_timeout_is_terminated() -> None:
started = asyncio.Event()
failed = False
try:
await _run_worker(
{"mode": "block"},
timeout=0.1,
started=started,
)
except RuntimeError:
failed = True
_require_fixed(started.is_set(), "blocking worker never became ready")
_require_fixed(failed, "blocking worker did not time out")
_require_fixed(
len(_OWNED_WORKERS) == 0,
"timed-out worker remained owned",
)
@pytest.mark.asyncio
async def test_owned_worker_parent_cancellation_is_settled() -> None:
started = asyncio.Event()
task = asyncio.create_task(
_run_worker(
{"mode": "block"},
timeout=30.0,
started=started,
)
)
await asyncio.wait_for(
started.wait(),
timeout=_WORKER_READY_SECONDS,
)
task.cancel()
cancelled = False
try:
await task
except asyncio.CancelledError:
cancelled = True
_require_fixed(cancelled, "worker parent cancellation was suppressed")
_require_fixed(
len(_OWNED_WORKERS) == 0,
"cancelled worker remained owned",
)
@pytest.mark.asyncio
@pytest.mark.parametrize(
"mode",
[
"protocol-ok-before-ready",
"protocol-duplicate-ready",
"protocol-duplicate-ok",
"protocol-malformed",
"protocol-eof-before-terminal",
"protocol-success-crash",
"protocol-zero-without-success",
],
)
async def test_worker_protocol_rejects_invalid_sequences(mode: str) -> None:
failed = False
try:
await _run_worker({"mode": mode}, timeout=2.0)
except RuntimeError:
failed = True
_require_fixed(failed, "invalid worker protocol sequence was accepted")
_require_fixed(
len(_OWNED_WORKERS) == 0,
"invalid worker protocol retained a settled worker",
)
@pytest.mark.asyncio
async def test_worker_setup_failure_occurs_before_ready() -> None:
started = asyncio.Event()
caught: Exception | None = None
try:
await _run_worker(
{"mode": "setup-error"},
timeout=2.0,
started=started,
)
except RuntimeError as error:
caught = error
fixed_failure = (
type(caught) is RuntimeError
and str(caught) == "Alembic worker failed [RuntimeError]"
and bool(getattr(caught, "__suppress_context__", False))
)
_require_fixed(
fixed_failure,
"worker setup failure was not sanitized",
)
_require_fixed(
not started.is_set(),
"worker setup failure was incorrectly reported as ready",
)
@pytest.mark.asyncio
@pytest.mark.parametrize(
"mode",
["success-dirty-origin", "success-dirty-origin-removed"],
ids=("present-at-final-guard", "removed-before-final-guard"),
)
async def test_worker_final_origin_guard_rejects_late_dirty_import(
mode: str,
) -> None:
failed = False
try:
await _run_worker(
{"mode": mode},
timeout=2.0,
)
except RuntimeError:
failed = True
_require_fixed(
failed and not _OWNED_WORKERS,
"worker final origin guard accepted a late dirty import",
)
class _IntegrationOriginProbeLoader:
def create_module(self, _spec: object) -> None:
return None
def exec_module(self, _module: object) -> None:
return None
class _IntegrationOriginProbeFinder:
def __init__(self, name: str, spec: object) -> None:
self._name = name
self._spec = spec
def find_spec(
self,
fullname: str,
_path: object = None,
_target: object = None,
) -> object:
return self._spec if fullname == self._name else None
@contextmanager
def _integration_origin_probe(
name: str,
spec: object,
) -> Iterator[Callable[[], None]]:
finder = _IntegrationOriginProbeFinder(name, spec)
parent_name, _, child_name = name.rpartition(".")
parent = importlib.import_module(parent_name) if parent_name else None
missing = object()
previous_module = sys.modules.pop(name, missing)
previous_attribute = parent.__dict__.get(child_name, missing) if parent is not None else missing
sys.meta_path.insert(0, finder)
def _import_then_remove() -> None:
importlib.import_module(name)
sys.modules.pop(name, None)
if parent is not None and child_name in parent.__dict__:
delattr(parent, child_name)
try:
yield _import_then_remove
finally:
if finder in sys.meta_path:
sys.meta_path.remove(finder)
sys.modules.pop(name, None)
if previous_module is not missing:
sys.modules[name] = previous_module
if parent is not None:
if previous_attribute is missing:
if child_name in parent.__dict__:
delattr(parent, child_name)
else:
setattr(parent, child_name, previous_attribute)
@pytest.mark.parametrize(
("name", "mixed"),
[
("ares.integration_removed_probe", False),
("migrations.versions.0007_add_websocket_tickets", False),
("migrations.versions.integration_mixed_probe", True),
],
ids=("dirty-ares", "dirty-0007", "mixed-namespace"),
)
def test_integration_origin_ledger_retains_removed_import(
tmp_path: Path,
name: str,
mixed: bool,
) -> None:
if mixed:
spec = importlib.machinery.ModuleSpec(
name,
loader=None,
is_package=True,
)
spec.submodule_search_locations = [
str(_REPO_ROOT / "migrations" / "versions"),
str(tmp_path),
]
else:
spec = importlib.machinery.ModuleSpec(
name,
_IntegrationOriginProbeLoader(),
origin=str(tmp_path / "probe.py"),
)
rejected = False
try:
with _integration_origin_probe(name, spec) as import_then_remove:
with _candidate_origin_boundary(isolated=True):
import_then_remove()
except RuntimeError:
rejected = True
_require_fixed(
rejected,
"integration origin ledger forgot a removed import",
)
def test_integration_final_origin_guard_rejects_late_dirty_import(
monkeypatch: pytest.MonkeyPatch,
tmp_path: Path,
) -> None:
dirty_module = types.ModuleType("migrations.versions.late_dirty_probe")
dirty_origin = tmp_path / "late_dirty_probe.py"
dirty_module.__file__ = str(dirty_origin)
dirty_module.__spec__ = importlib.machinery.ModuleSpec(
"migrations.versions.late_dirty_probe",
loader=None,
origin=str(dirty_origin),
)
monkeypatch.setitem(
sys.modules,
"migrations.versions.late_dirty_probe",
dirty_module,
)
rejected = not _loaded_first_party_origins_are_candidate_local(
sys.modules,
_REPO_ROOT,
)
_require_fixed(
rejected,
"integration final origin guard accepted a late dirty import",
)
def _integration_reused_origin_probe_body() -> None:
return None
def test_integration_origin_ledger_rechecks_reused_code(
tmp_path: Path,
) -> None:
probe_code = _integration_reused_origin_probe_body.__code__
local_probe = types.FunctionType(
probe_code,
{
"__name__": "migrations.versions.integration_reused_probe",
"__package__": "migrations.versions",
"__file__": str(
_REPO_ROOT / "migrations" / "versions" / "0006_integration_reused_probe.py"
),
},
)
dirty_probe = types.FunctionType(
probe_code,
{
"__name__": "migrations.versions.integration_reused_probe",
"__package__": "migrations.versions",
"__file__": str(
tmp_path / "migrations" / "versions" / "0007_integration_reused_probe.py"
),
},
)
rejected = False
try:
with _candidate_origin_boundary(isolated=True):
local_probe()
dirty_probe()
except RuntimeError:
rejected = True
_require_fixed(
rejected,
"integration origin ledger cached a stale frame origin",
)
def test_integration_origin_ledger_ignores_unrelated_migrations_directory(
tmp_path: Path,
) -> None:
third_party_probe = types.FunctionType(
_integration_reused_origin_probe_body.__code__,
{
"__name__": "third_party.integration_origin_probe",
"__package__": "third_party",
"__file__": str(tmp_path / "migrations" / "integration_origin_probe.py"),
},
)
with _candidate_origin_boundary(isolated=True):
third_party_probe()
@dataclass(slots=True, repr=False)
class _IpcConfidentialityAudit:
forbidden_values: tuple[str, ...] = field(repr=False)
process_args_safe: bool = False
process_closed: bool = False
parent_endpoint_closed: bool = False
sensitive_seen: bool = False
unsafe_object_seen: bool = False
sent_signatures: list[tuple[str, int]] = field(
default_factory=list,
repr=False,
)
received_signatures: list[tuple[str, int]] = field(
default_factory=list,
repr=False,
)
def _observe_value(self, value: object) -> None:
if isinstance(value, str):
if any(marker in value for marker in self.forbidden_values):
self.sensitive_seen = True
folded = value.casefold()
if folded in {
"postgresql",
"postgresql+asyncpg",
"asyncpg",
} or any(
marker in folded
for marker in (
"postgresql://",
"postgresql+asyncpg://",
"ares_test_postgres_",
)
):
self.sensitive_seen = True
return
if isinstance(value, bytes):
lowered = value.lower()
if (
any(marker.encode("utf-8") in value for marker in self.forbidden_values)
or b"postgresql" in lowered
):
self.sensitive_seen = True
return
if isinstance(value, int) and any(
marker.isdecimal() and int(marker) == value for marker in self.forbidden_values
):
self.sensitive_seen = True
return
if isinstance(value, dict):
for key, item in value.items():
self._observe_value(key)
self._observe_value(item)
return
if isinstance(value, (tuple, list)):
for item in value:
self._observe_value(item)
return
module_name = type(value).__module__
if module_name.startswith(("alembic", "sqlalchemy")):
self.unsafe_object_seen = True
def observe_process(self, kwargs: dict[str, object]) -> None:
arguments = kwargs.get("args")
safe_shape = (
kwargs.get("target") is _alembic_worker_entry
and isinstance(arguments, tuple)
and len(arguments) == 1
)
if isinstance(arguments, tuple):
for argument in arguments:
self._observe_value(argument)
self.process_args_safe = safe_shape
def observe_frame(self, direction: str, frame: object) -> None:
self._observe_value(frame)
if not isinstance(frame, tuple) or not frame:
self.unsafe_object_seen = True
return
kind = frame[0] if isinstance(frame[0], str) else "INVALID"
signature = (kind, len(frame))
if direction == "sent":
self.sent_signatures.append(signature)
else:
self.received_signatures.append(signature)
@dataclass(slots=True, repr=False)
class _AuditedParentChannel:
raw: object = field(repr=False)
audit: _IpcConfidentialityAudit = field(repr=False)
def send(self, frame: object) -> None:
self.audit.observe_frame("sent", frame)
self.raw.send(frame)
def recv(self) -> object:
frame = self.raw.recv()
self.audit.observe_frame("received", frame)
return frame
def poll(self) -> bool:
return bool(self.raw.poll())
def close(self) -> None:
self.raw.close()
self.audit.parent_endpoint_closed = True
@dataclass(slots=True, repr=False)
class _AuditedProcess:
raw: object = field(repr=False)
audit: _IpcConfidentialityAudit = field(repr=False)
def __getattr__(self, name: str) -> object:
return getattr(self.raw, name)
def close(self) -> None:
self.raw.close()
self.audit.process_closed = True
@dataclass(slots=True, repr=False)
class _AuditedSpawnContext:
raw: object = field(repr=False)
audit: _IpcConfidentialityAudit = field(repr=False)
child_endpoint: object = field(default=None, init=False, repr=False)
def Pipe(self, *, duplex: bool) -> tuple[object, object]: # noqa: N802
parent, child = self.raw.Pipe(duplex=duplex)
self.child_endpoint = child
return _AuditedParentChannel(parent, self.audit), child
def Process(self, **kwargs: object) -> _AuditedProcess: # noqa: N802
self.audit.observe_process(kwargs)
return _AuditedProcess(self.raw.Process(**kwargs), self.audit)
@pytest.mark.asyncio
@pytest.mark.parametrize(
("mode", "should_succeed"),
[
("ipc-prepare-success", True),
("ipc-prepare-error", False),
],
ids=("prepared-success", "prepared-sanitized-failure"),
)
async def test_migration_ipc_uses_only_fixed_control_data(
monkeypatch: pytest.MonkeyPatch,
mode: str,
should_succeed: bool,
) -> None:
canonical_values = {
"ARES_TEST_POSTGRES_HOST": "ipc-host-canary",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "ipc-user-canary",
"ARES_TEST_POSTGRES_DB": "ipc-maintenance-canary",
}
for name, value in canonical_values.items():
monkeypatch.setenv(name, value)
real_context = multiprocessing.get_context("spawn")
audit = _IpcConfidentialityAudit(tuple(canonical_values.values()))
audited_context = _AuditedSpawnContext(real_context, audit)
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: audited_context,
)
caught: Exception | None = None
try:
await _run_worker(
{
"mode": mode,
"database_name": f"ares_migration_{'a' * 32}",
"operation": "upgrade",
"revision": "0001",
},
timeout=5.0,
)
except RuntimeError as error:
caught = error
child_endpoint_closed = bool(
audited_context.child_endpoint is not None
and getattr(audited_context.child_endpoint, "closed", False)
)
expected_received = (
[("READY", 1), ("OK", 1)] if should_succeed else [("READY", 1), ("ERROR", 2)]
)
result_safe = (
(caught is None if should_succeed else type(caught) is RuntimeError)
and (should_succeed or str(caught) == "Alembic worker failed [RuntimeError]")
and audit.process_args_safe
and audit.sent_signatures == [("PAYLOAD", 2), ("GO", 1)]
and audit.received_signatures == expected_received
and not audit.sensitive_seen
and not audit.unsafe_object_seen
and audit.parent_endpoint_closed
and child_endpoint_closed
and audit.process_closed
and not _OWNED_WORKERS
)
_require_fixed(
result_safe,
"migration IPC confidentiality protocol diverged",
)
def test_child_constructs_explicit_target_from_inherited_configuration(
monkeypatch: pytest.MonkeyPatch,
) -> None:
child_environment = {
"ARES_TEST_POSTGRES_HOST": "child-host-canary",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "child-user-canary",
"ARES_TEST_POSTGRES_DB": "child-maintenance-canary",
}
for name, value in child_environment.items():
monkeypatch.setenv(name, value)
target = f"ares_migration_{'b' * 32}"
target_url_was_child_local = False
config_sentinel = object()
upgrade_sentinel = object()
def _capture_config(url: str) -> object:
nonlocal target_url_was_child_local
target_url_was_child_local = (
target in url
and child_environment["ARES_TEST_POSTGRES_HOST"] in url
and child_environment["ARES_TEST_POSTGRES_USER"] in url
)
return config_sentinel
monkeypatch.setattr(
sys.modules[__name__],
"_alembic_config",
_capture_config,
)
config, action, revision, fault = _prepare_child_migration(
{
"mode": "migrate",
"database_name": target,
"operation": "upgrade",
"revision": "0001",
},
types.SimpleNamespace(
upgrade=upgrade_sentinel,
downgrade=object(),
),
)
prepared = (
target_url_was_child_local
and config is config_sentinel
and action is upgrade_sentinel
and revision == "0001"
and fault is None
)
_require_fixed(
prepared,
"child-side explicit migration target construction failed",
)
def test_malformed_child_target_is_rejected_before_url_construction(
monkeypatch: pytest.MonkeyPatch,
) -> None:
child_environment = {
"ARES_TEST_POSTGRES_HOST": "configured",
"ARES_TEST_POSTGRES_PORT": "5432",
"ARES_TEST_POSTGRES_USER": "configured",
"ARES_TEST_POSTGRES_DB": "configured",
}
for name, value in child_environment.items():
monkeypatch.setenv(name, value)
url_constructed = False
def _record_url(*_args: object, **_kwargs: object) -> str:
nonlocal url_constructed
url_constructed = True
return "unexpected"
monkeypatch.setattr(
sys.modules[__name__],
"_migration_url",
_record_url,
)
failed = False
try:
_prepare_child_migration(
{
"mode": "migrate",
"database_name": "invalid",
"operation": "upgrade",
"revision": "0001",
},
types.SimpleNamespace(
upgrade=object(),
downgrade=object(),
),
)
except RuntimeError:
failed = True
_require_fixed(
failed and not url_constructed,
"malformed child target reached URL construction",
)
@pytest.mark.asyncio
async def test_child_configuration_failure_returns_fixed_protocol_result(
monkeypatch: pytest.MonkeyPatch,
) -> None:
_clear_postgres_environment(monkeypatch)
caught: Exception | None = None
try:
await _run_worker(
{
"mode": "migrate",
"database_name": f"ares_migration_{'c' * 32}",
"operation": "upgrade",
"revision": "0001",
},
timeout=2.0,
)
except RuntimeError as error:
caught = error
fixed_result = (
type(caught) is RuntimeError
and str(caught) == "Alembic worker failed [RuntimeError]"
and bool(getattr(caught, "__suppress_context__", False))
and not _OWNED_WORKERS
)
_require_fixed(
fixed_result,
"child configuration failure exposed non-protocol data",
)
class _WorkerConstructionError(RuntimeError):
pass
class _WorkerStartError(RuntimeError):
pass
class _WorkerEndpointCloseError(RuntimeError):
pass
class _ConstructionEndpoint:
def __init__(self, *, fail_close_once: bool = False) -> None:
self.closed = False
self.close_attempts = 0
self.fail_close_once = fail_close_once
def close(self) -> None:
self.close_attempts += 1
if self.fail_close_once and self.close_attempts == 1:
raise _WorkerEndpointCloseError
self.closed = True
class _NeverStartedProcess:
def __init__(self) -> None:
self.pid = None
self.exitcode = None
self.closed = False
self.start_called = False
def start(self) -> None:
self.start_called = True
raise _WorkerStartError
def is_alive(self) -> bool:
return False
def close(self) -> None:
self.closed = True
class _PipeFailureContext:
def Pipe(self, *, duplex: bool) -> object: # noqa: N802
del duplex
raise _WorkerConstructionError
class _PartialPipeFailureContext:
def __init__(self) -> None:
self.endpoint = _ConstructionEndpoint()
def Pipe(self, *, duplex: bool) -> tuple[object]: # noqa: N802
del duplex
return (self.endpoint,)
class _ProcessFailureContext:
def __init__(self) -> None:
self.parent = _ConstructionEndpoint()
self.child = _ConstructionEndpoint()
self.process_kwargs: dict[str, object] | None = None
def Pipe( # noqa: N802
self,
*,
duplex: bool,
) -> tuple[object, object]:
del duplex
return self.parent, self.child
def Process(self, **kwargs: object) -> object: # noqa: N802
self.process_kwargs = dict(kwargs)
raise _WorkerConstructionError
class _StartFailureContext:
def __init__(self, *, fail_parent_close_once: bool = False) -> None:
self.parent = _ConstructionEndpoint(fail_close_once=fail_parent_close_once)
self.child = _ConstructionEndpoint()
self.process = _NeverStartedProcess()
def Pipe( # noqa: N802
self,
*,
duplex: bool,
) -> tuple[object, object]:
del duplex
return self.parent, self.child
def Process( # noqa: N802
self,
**_kwargs: object,
) -> _NeverStartedProcess:
return self.process
@pytest.mark.asyncio
async def test_worker_pipe_construction_failure_leaves_no_owned_resource(
monkeypatch: pytest.MonkeyPatch,
) -> None:
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: _PipeFailureContext(),
)
caught: Exception | None = None
try:
await _run_worker({"mode": "success"}, timeout=1.0)
except RuntimeError as error:
caught = error
failed_closed = (
type(caught) is RuntimeError
and str(caught) == "Alembic worker startup failed [_WorkerConstructionError]"
and bool(getattr(caught, "__suppress_context__", False))
and not _OWNED_WORKERS
)
_require_fixed(
failed_closed,
"Pipe construction failure leaked worker ownership",
)
@pytest.mark.asyncio
async def test_worker_partial_pipe_result_closes_obtained_endpoint(
monkeypatch: pytest.MonkeyPatch,
) -> None:
context = _PartialPipeFailureContext()
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: context,
)
caught: Exception | None = None
try:
await _run_worker({"mode": "success"}, timeout=1.0)
except RuntimeError as error:
caught = error
failed_closed = type(caught) is RuntimeError and context.endpoint.closed and not _OWNED_WORKERS
_require_fixed(
failed_closed,
"partial Pipe construction leaked its obtained endpoint",
)
@pytest.mark.asyncio
async def test_worker_process_construction_failure_closes_both_endpoints(
monkeypatch: pytest.MonkeyPatch,
) -> None:
context = _ProcessFailureContext()
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: context,
)
caught: Exception | None = None
try:
await _run_worker({"mode": "success"}, timeout=1.0)
except RuntimeError as error:
caught = error
process_arguments_are_control_only = (
context.process_kwargs is not None
and context.process_kwargs.get("target") is _alembic_worker_entry
and context.process_kwargs.get("args") == (context.child,)
and context.process_kwargs.get("name") == "ares-alembic-worker"
and set(context.process_kwargs) == {"target", "args", "name"}
)
failed_closed = (
type(caught) is RuntimeError
and context.parent.closed
and context.child.closed
and process_arguments_are_control_only
and not _OWNED_WORKERS
)
_require_fixed(
failed_closed,
"Process construction failure did not close Pipe endpoints",
)
@pytest.mark.asyncio
async def test_worker_start_failure_before_child_creation_is_settled(
monkeypatch: pytest.MonkeyPatch,
) -> None:
context = _StartFailureContext()
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: context,
)
caught: Exception | None = None
try:
await _run_worker({"mode": "success"}, timeout=1.0)
except RuntimeError as error:
caught = error
settled = (
type(caught) is RuntimeError
and context.process.start_called
and context.process.closed
and context.parent.closed
and context.child.closed
and not _OWNED_WORKERS
)
_require_fixed(
settled,
"pre-child start failure did not settle all owned resources",
)
@pytest.mark.asyncio
async def test_worker_start_failure_preserves_fixed_primary_when_close_fails(
monkeypatch: pytest.MonkeyPatch,
) -> None:
context = _StartFailureContext(fail_parent_close_once=True)
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: context,
)
caught: Exception | None = None
try:
await _run_worker({"mode": "success"}, timeout=1.0)
except RuntimeError as error:
caught = error
retained = tuple(_OWNED_WORKERS)
primary_preserved = (
type(caught) is RuntimeError
and str(caught) == "Alembic worker startup failed [_WorkerStartError]"
and tuple(getattr(caught, "__notes__", ()))
== ("Alembic worker cleanup failed [settle-startup-worker: RuntimeError]",)
and len(retained) == 1
)
if retained:
await _settle_worker(retained[0], terminate=True)
_require_fixed(
primary_preserved and not _OWNED_WORKERS,
"startup cleanup failure replaced its fixed primary failure",
)
class _AmbiguousStartProcess:
def __init__(self, process: Any) -> None:
self._process = process
self._closed = False
self._final_exitcode: int | None = None
def start(self) -> None:
self._process.start()
raise _WorkerStartError
@property
def exitcode(self) -> int | None:
if self._closed:
return self._final_exitcode
return self._process.exitcode
def is_alive(self) -> bool:
return False if self._closed else bool(self._process.is_alive())
def close(self) -> None:
self._final_exitcode = self._process.exitcode
self._process.close()
self._closed = True
def __getattr__(self, name: str) -> object:
return getattr(self._process, name)
class _AmbiguousStartContext:
def __init__(self, context: Any) -> None:
self._context = context
self.endpoints: tuple[Any, Any] | None = None
self.process: _AmbiguousStartProcess | None = None
def Pipe(self, *, duplex: bool) -> tuple[Any, Any]: # noqa: N802
self.endpoints = self._context.Pipe(duplex=duplex)
return self.endpoints
def Process( # noqa: N802
self,
**kwargs: object,
) -> _AmbiguousStartProcess:
self.process = _AmbiguousStartProcess(self._context.Process(**kwargs))
return self.process
@pytest.mark.asyncio
async def test_worker_ambiguous_start_failure_settles_real_child(
monkeypatch: pytest.MonkeyPatch,
) -> None:
real_context = multiprocessing.get_context("spawn")
context = _AmbiguousStartContext(real_context)
monkeypatch.setattr(
multiprocessing,
"get_context",
lambda _method: context,
)
caught: Exception | None = None
try:
await _run_worker({"mode": "success"}, timeout=1.0)
except RuntimeError as error:
caught = error
endpoints_closed = context.endpoints is not None and all(
endpoint.closed for endpoint in context.endpoints
)
child_settled = (
context.process is not None
and context.process.exitcode is not None
and not context.process.is_alive()
)
_require_fixed(
type(caught) is RuntimeError and endpoints_closed and child_settled and not _OWNED_WORKERS,
"ambiguous Process.start failure left a live child or endpoint",
)
class _FakeWorkerChannel:
def __init__(self) -> None:
self.closed = False
def close(self) -> None:
self.closed = True
class _FakeWorkerProcess:
def __init__(self, *, kill_succeeds: bool) -> None:
self.pid = 1
self.exitcode: int | None = None
self._alive = True
self._kill_succeeds = kill_succeeds
self.terminate_called = False
self.kill_called = False
self.closed = False
def is_alive(self) -> bool:
return self._alive
def join(self, timeout: float = 0) -> None:
del timeout
def terminate(self) -> None:
self.terminate_called = True
def kill(self) -> None:
self.kill_called = True
if self._kill_succeeds:
self._alive = False
self.exitcode = -9
def close(self) -> None:
self.closed = True
@pytest.mark.asyncio
async def test_worker_settlement_escalates_from_terminate_to_kill(
monkeypatch: pytest.MonkeyPatch,
) -> None:
monkeypatch.setattr(
sys.modules[__name__],
"_WORKER_SETTLE_SECONDS",
0.02,
)
process = _FakeWorkerProcess(kill_succeeds=True)
worker = _OwnedWorker(process=process, channel=_FakeWorkerChannel())
_OWNED_WORKERS.add(worker)
exit_code = await _settle_worker(worker, terminate=True)
_require_fixed(
process.terminate_called
and process.kill_called
and process.closed
and exit_code == -9
and worker not in _OWNED_WORKERS,
"worker settlement did not escalate and release ownership",
)
@pytest.mark.asyncio
async def test_unproven_worker_death_retains_ownership_and_blocks_cleanup(
monkeypatch: pytest.MonkeyPatch,
) -> None:
monkeypatch.setattr(
sys.modules[__name__],
"_WORKER_SETTLE_SECONDS",
0.02,
)
process = _FakeWorkerProcess(kill_succeeds=False)
worker = _OwnedWorker(process=process, channel=_FakeWorkerChannel())
_OWNED_WORKERS.add(worker)
failed = False
try:
await _settle_worker(worker, terminate=True)
except RuntimeError:
failed = True
cleanup_failures: list[str] = []
cleanup_allowed = await _settle_owned_workers_for_cleanup(cleanup_failures)
retained = worker in _OWNED_WORKERS
process._alive = False
process.exitcode = -9
await _settle_worker(worker, terminate=True)
_require_fixed(
failed
and not cleanup_allowed
and retained
and bool(cleanup_failures)
and worker not in _OWNED_WORKERS,
"unproven worker death did not retain ownership and block cleanup",
)
@pytest.mark.asyncio
async def test_empty_postgres_base_to_0006_uses_only_alembic() -> None:
async with _postgres_harness() as harness:
connection = await _connect(harness)
try:
initial_tables = await connection.fetchval(
"""
SELECT count(*)
FROM information_schema.tables
WHERE table_schema=current_schema()
AND table_type='BASE TABLE'
"""
)
no_alembic_version = (
await connection.fetchval("SELECT to_regclass('alembic_version') IS NULL") is True
)
finally:
await connection.close()
_require_fixed(
initial_tables == 0 and no_alembic_version,
"disposable PostgreSQL database was not genuinely empty",
)
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
tables = {
str(row["table_name"])
for row in await connection.fetch(
"""
SELECT table_name
FROM information_schema.tables
WHERE table_schema=current_schema()
AND table_type='BASE TABLE'
"""
)
}
reached_revision = await _version(connection) == "0006"
migration_only_tables = tables == _EXPECTED_TABLES | {"alembic_version"}
finally:
await connection.close()
_require_fixed(
reached_revision,
"PostgreSQL did not reach revision 0006",
)
_require_fixed(
migration_only_tables,
"PostgreSQL migration-created table contract diverged",
)
@pytest.mark.asyncio
async def test_explicit_postgres_target_excludes_environment_decoy(
monkeypatch: pytest.MonkeyPatch,
) -> None:
async with _postgres_harness() as selected:
async with _postgres_harness() as decoy:
distinct_safe_targets = (
selected.database_name != decoy.database_name
and selected.database_name != selected.config.maintenance_database
and decoy.database_name != decoy.config.maintenance_database
and _SAFE_DATABASE_NAME.fullmatch(selected.database_name) is not None
and _SAFE_DATABASE_NAME.fullmatch(decoy.database_name) is not None
)
_require_fixed(
distinct_safe_targets,
"PostgreSQL target isolation identifiers were unsafe",
)
maintenance_before = await _database_fingerprint(
selected.config,
selected.config.maintenance_database,
)
selected_before = await _database_fingerprint(
selected.config,
selected.database_name,
)
decoy_before = await _database_fingerprint(
decoy.config,
decoy.database_name,
)
empty_baselines = _isolation_fingerprint_has_no_ares_state(
selected_before
) and _isolation_fingerprint_has_no_ares_state(decoy_before)
_require_fixed(
empty_baselines,
"PostgreSQL isolation baselines were not empty",
)
decoy_url = _migration_url(
decoy.config,
decoy.database_name,
ordinary_driver=True,
)
monkeypatch.setenv("ARES_DATABASE_URL", decoy_url)
await _alembic(selected, "upgrade", "0004")
selected_after_upgrade = await _database_fingerprint(
selected.config,
selected.database_name,
)
revision_after_upgrade = await _database_revision(
selected.config,
selected.database_name,
)
decoy_after_upgrade = await _database_fingerprint(
decoy.config,
decoy.database_name,
)
maintenance_after_upgrade = await _database_fingerprint(
selected.config,
selected.config.maintenance_database,
)
await _alembic(selected, "upgrade", "0004")
selected_after_repeat = await _database_fingerprint(
selected.config,
selected.database_name,
)
revision_after_repeat = await _database_revision(
selected.config,
selected.database_name,
)
decoy_after_repeat = await _database_fingerprint(
decoy.config,
decoy.database_name,
)
maintenance_after_repeat = await _database_fingerprint(
selected.config,
selected.config.maintenance_database,
)
await _alembic(selected, "downgrade", "0003")
selected_after_downgrade = await _database_fingerprint(
selected.config,
selected.database_name,
)
revision_after_downgrade = await _database_revision(
selected.config,
selected.database_name,
)
decoy_after_downgrade = await _database_fingerprint(
decoy.config,
decoy.database_name,
)
maintenance_after_downgrade = await _database_fingerprint(
selected.config,
selected.config.maintenance_database,
)
await _alembic(selected, "upgrade", "0004")
selected_after_reupgrade = await _database_fingerprint(
selected.config,
selected.database_name,
)
revision_after_reupgrade = await _database_revision(
selected.config,
selected.database_name,
)
decoy_after_reupgrade = await _database_fingerprint(
decoy.config,
decoy.database_name,
)
maintenance_after_reupgrade = await _database_fingerprint(
selected.config,
selected.config.maintenance_database,
)
target_transitions_are_exact = (
selected_after_upgrade != selected_before
and revision_after_upgrade == "0004"
and selected_after_repeat == selected_after_upgrade
and revision_after_repeat == "0004"
and selected_after_downgrade != selected_after_upgrade
and revision_after_downgrade == "0003"
and selected_after_reupgrade == selected_after_upgrade
and revision_after_reupgrade == "0004"
)
decoy_remained_exact = all(
fingerprint == decoy_before
and _isolation_fingerprint_has_no_ares_state(fingerprint)
for fingerprint in (
decoy_after_upgrade,
decoy_after_repeat,
decoy_after_downgrade,
decoy_after_reupgrade,
)
)
maintenance_remained_exact = all(
fingerprint == maintenance_before
for fingerprint in (
maintenance_after_upgrade,
maintenance_after_repeat,
maintenance_after_downgrade,
maintenance_after_reupgrade,
)
)
_require_fixed(
target_transitions_are_exact
and decoy_remained_exact
and maintenance_remained_exact,
"explicit PostgreSQL migration target isolation failed",
)
@pytest.mark.asyncio
async def test_postgres_revision_round_trip_preserves_rows() -> None:
async with _postgres_harness() as harness:
for revision in ("0001", "0002", "0003", "0004", "0005"):
await _alembic(harness, "upgrade", revision)
connection = await _connect(harness)
try:
reached_revision = await _version(connection) == revision
if revision == "0001":
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"campaign-a",
"Migration campaign",
)
await connection.execute(
"""
INSERT INTO credentials(
id, campaign_id, username, cred_type, cracked_value
) VALUES($1, $2, $3, $4, $5)
""",
"credential-a",
"campaign-a",
"synthetic-user",
"password",
"synthetic-encrypted-marker",
)
finally:
await connection.close()
_require_fixed(
reached_revision,
"PostgreSQL revision sequence stopped unexpectedly",
)
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
upgrade_preserved = (
await connection.fetchval(
"""
SELECT cracked_value_enc IS NOT NULL
FROM credentials WHERE id=$1
""",
"credential-a",
)
is True
)
finally:
await connection.close()
_require_fixed(
upgrade_preserved,
"PostgreSQL revision 0006 did not preserve the credential value",
)
await _alembic(harness, "downgrade", "0005")
connection = await _connect(harness)
try:
downgrade_preserved = (
await connection.fetchval(
"""
SELECT cracked_value IS NOT NULL
FROM credentials WHERE id=$1
""",
"credential-a",
)
is True
)
finally:
await connection.close()
_require_fixed(
downgrade_preserved,
"PostgreSQL revision 0006 downgrade lost a credential value",
)
for revision in ("0004", "0003", "0002", "0001"):
await _alembic(harness, "downgrade", revision)
connection = await _connect(harness)
try:
reached_revision = await _version(connection) == revision
finally:
await connection.close()
_require_fixed(
reached_revision,
"PostgreSQL downgrade sequence stopped unexpectedly",
)
connection = await _connect(harness)
try:
parent_columns = {
str(row["column_name"])
for row in await connection.fetch(
"""
SELECT column_name
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='findings'
"""
)
}
finally:
await connection.close()
parent_is_repaired = {"cvss_score", "cvss_vector"}.issubset(
parent_columns
) and "trace_id" not in parent_columns
_require_fixed(
parent_is_repaired,
"PostgreSQL revision 0002 downgrade contradicted its parent",
)
await _alembic(harness, "downgrade", "base")
connection = await _connect(harness)
try:
remaining_application_tables = await connection.fetchval(
"""
SELECT count(*)
FROM information_schema.tables
WHERE table_schema=current_schema()
AND table_name = ANY($1::text[])
""",
list(_EXPECTED_TABLES),
)
finally:
await connection.close()
_require_fixed(
remaining_application_tables == 0,
"PostgreSQL downgrade to base retained an application table",
)
await _alembic(harness, "upgrade", "0006")
@pytest.mark.asyncio
async def test_postgres_downgrades_restore_exact_repaired_parents() -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0006")
parent_by_revision = {
"0006": "0005",
"0005": "0004",
"0004": "0003",
"0003": "0002",
"0002": "0001",
"0001": "base",
}
for _revision, parent in parent_by_revision.items():
await _alembic(harness, "downgrade", parent)
connection = await _connect(harness)
try:
actual = await _postgres_catalog_contract(connection)
finally:
await connection.close()
expected = _fixed_postgres_contract(parent)
_require_fixed(
actual == expected,
"PostgreSQL downgrade did not restore its repaired parent",
)
@pytest.mark.asyncio
async def test_postgres_0006_catalog_types_defaults_fks_and_indexes() -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
all_column_rows = await connection.fetch(
"""
SELECT
table_name,
ordinal_position,
column_name,
data_type,
is_nullable
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name = ANY($1::text[])
ORDER BY table_name, ordinal_position
""",
list(_EXPECTED_COLUMN_ORDER),
)
actual_columns: dict[str, list[tuple[str, str, str]]] = {}
for row in all_column_rows:
actual_columns.setdefault(
str(row["table_name"]),
[],
).append(
(
str(row["column_name"]),
str(row["data_type"]),
str(row["is_nullable"]),
)
)
columns_are_exact = True
for table, expected_order in _EXPECTED_COLUMN_ORDER.items():
rows = actual_columns.get(table, [])
if tuple(row[0] for row in rows) != expected_order:
columns_are_exact = False
break
for name, data_type, nullable in rows:
key = (table, name)
if key in _POSTGRES_INTEGER_COLUMNS:
expected_type = "integer"
elif key in _POSTGRES_FLOAT_COLUMNS:
expected_type = "double precision"
elif key in _POSTGRES_TIMESTAMP_COLUMNS:
expected_type = "timestamp with time zone"
else:
expected_type = "text"
expected_nullable = "YES" if key in _POSTGRES_NULLABLE_COLUMNS else "NO"
if data_type != expected_type or nullable != expected_nullable:
columns_are_exact = False
break
if not columns_are_exact:
break
sequence_defaults = {
(str(row["table_name"]), str(row["column_name"]))
for row in await connection.fetch(
"""
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema=current_schema()
AND column_default LIKE 'nextval(%'
"""
)
}
sequence_contract_matches = sequence_defaults == {
("audit_log", "id"),
("rate_limit_events", "id"),
}
timestamp_rows = await connection.fetch(
"""
SELECT table_name, column_name, data_type, column_default
FROM information_schema.columns
WHERE table_schema=current_schema()
AND (
(table_name='campaigns' AND column_name='created_at')
OR
(table_name='module_runs' AND column_name='completed_at')
OR
(table_name='findings' AND column_name='discovered_at')
OR
(table_name='rate_limit_events' AND column_name='timestamp')
OR
(
table_name='revoked_access_tokens'
AND column_name='revoked_at'
)
)
"""
)
timestamps_are_aware = len(timestamp_rows) == 5 and all(
row["data_type"] == "timestamp with time zone"
and "now()" in str(row["column_default"]).lower()
for row in timestamp_rows
)
hardened_columns = await connection.fetchval(
"""
SELECT count(*)
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='findings'
AND column_name = ANY(
ARRAY['cvss_score', 'cvss_vector', 'trace_id']
)
AND is_nullable='NO'
"""
)
nullability_matches = hardened_columns == 3
foreign_key_rows = await connection.fetch(
"""
SELECT
source.relname AS source_table,
source_column.attname AS source_column,
target.relname AS target_table,
CASE constraint_record.confdeltype
WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
ELSE 'OTHER'
END AS delete_action
FROM pg_constraint AS constraint_record
JOIN pg_class AS source
ON source.oid=constraint_record.conrelid
JOIN pg_class AS target
ON target.oid=constraint_record.confrelid
JOIN pg_attribute AS source_column
ON source_column.attrelid=source.oid
AND source_column.attnum=constraint_record.conkey[1]
WHERE constraint_record.contype='f'
AND source.relnamespace=current_schema()::regnamespace
"""
)
foreign_keys = {
(
str(row["source_table"]),
str(row["source_column"]),
str(row["target_table"]),
str(row["delete_action"]),
)
for row in foreign_key_rows
}
index_rows = await connection.fetch(
"""
SELECT
index_class.relname AS index_name,
array_agg(attribute.attname ORDER BY key.ordinality) AS columns
FROM pg_index AS index_catalog
JOIN pg_class AS table_class
ON table_class.oid=index_catalog.indrelid
JOIN pg_class AS index_class
ON index_class.oid=index_catalog.indexrelid
JOIN unnest(index_catalog.indkey)
WITH ORDINALITY AS key(attnum, ordinality)
ON key.attnum > 0
JOIN pg_attribute AS attribute
ON attribute.attrelid=table_class.oid
AND attribute.attnum=key.attnum
WHERE table_class.relnamespace=current_schema()::regnamespace
AND NOT index_catalog.indisprimary
AND NOT index_catalog.indisunique
GROUP BY index_class.relname
""",
)
indexes = {str(row["index_name"]): tuple(row["columns"]) for row in index_rows}
primary_key_tables = {
str(row["table_name"])
for row in await connection.fetch(
"""
SELECT table_class.relname AS table_name
FROM pg_constraint AS constraint_record
JOIN pg_class AS table_class
ON table_class.oid=constraint_record.conrelid
WHERE constraint_record.contype='p'
AND table_class.relnamespace=
current_schema()::regnamespace
AND table_class.relname <> 'alembic_version'
"""
)
}
primary_keys_match = primary_key_tables == _EXPECTED_TABLES
unique_contracts = {
str(row["constraint_name"])
for row in await connection.fetch(
"""
SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_schema=current_schema()
AND constraint_type='UNIQUE'
"""
)
}
uniqueness_matches = unique_contracts == {
"uq_hosts_campaign_ip",
"uq_users_username",
}
fk_rejected = False
try:
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title, description, severity
) VALUES($1, $2, $3, $4, $5, $6)
""",
"finding-a",
"missing-campaign",
"module-a",
"title",
"description",
"high",
)
except Exception as exc:
fk_rejected = type(exc).__name__ == "ForeignKeyViolationError"
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
_require_fixed(
columns_are_exact,
"PostgreSQL ordered column/type/nullability contract diverged",
)
_require_fixed(
sequence_contract_matches,
"PostgreSQL sequence-backed identity contract diverged",
)
_require_fixed(
timestamps_are_aware,
"PostgreSQL timestamp/default contract diverged",
)
_require_fixed(
nullability_matches,
"PostgreSQL finding nullability contract diverged",
)
_require_fixed(
primary_keys_match,
"PostgreSQL primary-key contract diverged",
)
_require_fixed(
uniqueness_matches,
"PostgreSQL uniqueness contract diverged",
)
_require_fixed(
foreign_keys == _EXPECTED_FOREIGN_KEYS,
"PostgreSQL foreign-key contract diverged",
)
_require_fixed(
indexes == _EXPECTED_INDEXES,
"PostgreSQL logical index contract diverged",
)
_require_fixed(
fk_rejected,
"PostgreSQL did not enforce a required foreign key",
)
_require_fixed(
reusable,
"PostgreSQL connection was not reusable after a constraint error",
)
@pytest.mark.asyncio
async def test_postgres_all_timestamp_columns_reject_infinity() -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
await _seed_postgres_contract(connection)
constraint_rows = await connection.fetch(
"""
SELECT
relation.relname AS table_name,
constraint_record.conname AS constraint_name,
pg_get_constraintdef(constraint_record.oid) AS definition
FROM pg_constraint AS constraint_record
JOIN pg_class AS relation
ON relation.oid=constraint_record.conrelid
WHERE relation.relnamespace=current_schema()::regnamespace
AND constraint_record.contype='c'
"""
)
observed_constraints = {
(
str(row["table_name"]),
str(row["constraint_name"]),
): " ".join(str(row["definition"]).lower().replace('"', "").split())
for row in constraint_rows
}
semantic_constraints_match = True
for (
table,
column,
), (name, nullable) in _POSTGRES_FINITE_TIMESTAMP_CONSTRAINTS.items():
definition = observed_constraints.get((table, name), "")
compact = definition.replace(" ", "").replace("(", "").replace(")", "")
has_finite = f"isfinite{column}" in compact
has_nullable = f"{column}isnullor" in compact
if not has_finite or has_nullable != nullable:
semantic_constraints_match = False
break
key_by_table = {
"campaigns": ("id", "campaign-contract"),
"module_runs": ("id", "run-contract"),
"findings": ("id", "finding-contract"),
"hosts": ("id", "host-contract"),
"credentials": ("id", "credential-contract"),
"loot": ("id", "loot-contract"),
"audit_log": ("id", 1),
"users": ("id", "user-contract"),
"api_keys": ("id", "api-key-contract"),
"refresh_tokens": ("id", "refresh-contract"),
"revoked_access_tokens": ("jti", "revoked-contract"),
"rate_limit_events": ("id", 1),
}
finite_value = datetime(2030, 1, 1, tzinfo=UTC)
enforced_for_every_column = True
for (table, column), (_name, nullable) in sorted(
_POSTGRES_FINITE_TIMESTAMP_CONSTRAINTS.items()
):
key_name, key_value = key_by_table[table]
update_finite = (
f'UPDATE "{table}" SET "{column}"=$1 ' # noqa: S608
f'WHERE "{key_name}"=$2'
)
await connection.execute(
update_finite,
finite_value,
key_value,
)
finite_applied = await connection.fetchval(
(
f'SELECT isfinite("{column}") FROM "{table}" ' # noqa: S608
f'WHERE "{key_name}"=$1'
),
key_value,
)
positive_rejected = await _postgres_integrity_failure(
connection,
(
f'UPDATE "{table}" ' # noqa: S608
f"SET \"{column}\"='infinity'::timestamptz "
f'WHERE "{key_name}"=$1'
),
key_value,
expected_type="CheckViolationError",
)
positive_unchanged = await connection.fetchval(
(
f'SELECT isfinite("{column}") FROM "{table}" ' # noqa: S608
f'WHERE "{key_name}"=$1'
),
key_value,
)
negative_rejected = await _postgres_integrity_failure(
connection,
(
f'UPDATE "{table}" ' # noqa: S608
f"SET \"{column}\"='-infinity'::timestamptz "
f'WHERE "{key_name}"=$1'
),
key_value,
expected_type="CheckViolationError",
)
negative_unchanged = await connection.fetchval(
(
f'SELECT isfinite("{column}") FROM "{table}" ' # noqa: S608
f'WHERE "{key_name}"=$1'
),
key_value,
)
if nullable:
await connection.execute(
(
f'UPDATE "{table}" SET "{column}"=NULL ' # noqa: S608
f'WHERE "{key_name}"=$1'
),
key_value,
)
null_contract = await connection.fetchval(
(
f'SELECT "{column}" IS NULL FROM "{table}" ' # noqa: S608
f'WHERE "{key_name}"=$1'
),
key_value,
)
await connection.execute(
update_finite,
finite_value,
key_value,
)
else:
null_contract = await _postgres_integrity_failure(
connection,
(
f'UPDATE "{table}" SET "{column}"=NULL ' # noqa: S608
f'WHERE "{key_name}"=$1'
),
key_value,
expected_type="NotNullViolationError",
)
if not all(
(
finite_applied is True,
positive_rejected,
positive_unchanged is True,
negative_rejected,
negative_unchanged is True,
null_contract is True,
)
):
enforced_for_every_column = False
break
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
_require_fixed(
semantic_constraints_match,
"PostgreSQL finite timestamp catalog contract diverged",
)
_require_fixed(
enforced_for_every_column and reusable,
"PostgreSQL finite timestamp enforcement diverged",
)
@pytest.mark.asyncio
async def test_postgres_0006_enforces_complete_relational_contract() -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
await _seed_postgres_contract(connection)
key_by_table = {
"campaigns": ("id", "campaign-contract"),
"module_runs": ("id", "run-contract"),
"findings": ("id", "finding-contract"),
"hosts": ("id", "host-contract"),
"credentials": ("id", "credential-contract"),
"loot": ("id", "loot-contract"),
"audit_log": ("id", 1),
"users": ("id", "user-contract"),
"api_keys": ("id", "api-key-contract"),
"refresh_tokens": ("id", "refresh-contract"),
"revoked_access_tokens": ("jti", "revoked-contract"),
"rate_limit_events": ("id", 1),
}
required_columns_rejected = True
for table, order in _EXPECTED_COLUMN_ORDER.items():
key_name, key_value = key_by_table[table]
for name in order:
if (table, name) in _POSTGRES_NULLABLE_COLUMNS:
continue
statement = (
f'UPDATE "{table}" SET "{name}"=NULL ' # noqa: S608
f'WHERE "{key_name}"=$1'
)
if not await _postgres_integrity_failure(
connection,
statement,
key_value,
expected_type="NotNullViolationError",
):
required_columns_rejected = False
break
foreign_keys_rejected = True
for table, local, _remote, _action in sorted(_EXPECTED_FOREIGN_KEYS):
key_name, key_value = key_by_table[table]
statement = (
f'UPDATE "{table}" SET "{local}"=$1 ' # noqa: S608
f'WHERE "{key_name}"=$2'
)
if not await _postgres_integrity_failure(
connection,
statement,
"missing-parent",
key_value,
expected_type="ForeignKeyViolationError",
):
foreign_keys_rejected = False
break
duplicate_host_rejected = await _postgres_integrity_failure(
connection,
"""
INSERT INTO hosts(id, campaign_id, ip_address)
VALUES($1, $2, $3)
""",
"host-duplicate",
"campaign-contract",
"192.0.2.10",
expected_type="UniqueViolationError",
)
duplicate_user_rejected = await _postgres_integrity_failure(
connection,
"""
INSERT INTO users(id, username, hashed_password)
VALUES($1, $2, $3)
""",
"user-duplicate",
"synthetic-account",
"synthetic-hash",
expected_type="UniqueViolationError",
)
transaction = connection.transaction()
await transaction.start()
try:
await connection.execute(
"DELETE FROM hosts WHERE id=$1",
"host-contract",
)
set_null_rows = await connection.fetchrow(
"""
SELECT
(SELECT host_id IS NULL FROM credentials WHERE id=$1)
AS credential_cleared,
(SELECT host_id IS NULL FROM loot WHERE id=$2)
AS loot_cleared
""",
"credential-contract",
"loot-contract",
)
set_null_enforced = (
set_null_rows is not None
and set_null_rows["credential_cleared"] is True
and set_null_rows["loot_cleared"] is True
)
finally:
await transaction.rollback()
transaction = connection.transaction()
await transaction.start()
try:
await connection.execute(
"DELETE FROM campaigns WHERE id=$1",
"campaign-contract",
)
campaign_actions = await connection.fetchrow(
"""
SELECT
(SELECT count(*) FROM module_runs) AS runs,
(SELECT count(*) FROM findings) AS findings,
(SELECT count(*) FROM hosts) AS hosts,
(SELECT count(*) FROM credentials) AS credentials,
(SELECT count(*) FROM loot) AS loot,
(
SELECT campaign_id IS NULL
FROM audit_log
WHERE id=1
) AS audit_cleared
"""
)
campaign_actions_enforced = campaign_actions is not None and tuple(
campaign_actions.values()
) == (0, 0, 0, 0, 0, True)
finally:
await transaction.rollback()
transaction = connection.transaction()
await transaction.start()
try:
await connection.execute(
"DELETE FROM users WHERE id=$1",
"user-contract",
)
user_actions = await connection.fetchrow(
"""
SELECT
(SELECT count(*) FROM api_keys) AS api_keys,
(SELECT count(*) FROM refresh_tokens)
AS refresh_tokens
"""
)
user_cascade_enforced = user_actions is not None and tuple(
user_actions.values()
) == (0, 0)
finally:
await transaction.rollback()
timestamp_values = await connection.fetchrow(
"""
SELECT
(SELECT created_at FROM campaigns LIMIT 1) AS campaign,
(SELECT completed_at FROM module_runs LIMIT 1) AS run,
(SELECT discovered_at FROM findings LIMIT 1) AS finding,
(SELECT first_seen FROM hosts LIMIT 1) AS host,
(SELECT captured_at FROM credentials LIMIT 1)
AS credential,
(SELECT captured_at FROM loot LIMIT 1) AS loot,
(SELECT timestamp FROM audit_log LIMIT 1) AS audit,
(SELECT created_at FROM users LIMIT 1) AS account,
(SELECT created_at FROM api_keys LIMIT 1) AS api_key,
(SELECT created_at FROM refresh_tokens LIMIT 1)
AS refresh,
(SELECT revoked_at FROM revoked_access_tokens LIMIT 1)
AS revoked,
(SELECT timestamp FROM rate_limit_events LIMIT 1)
AS rate_limit
"""
)
timestamp_defaults_execute = timestamp_values is not None and all(
isinstance(value, datetime) and value.utcoffset() is not None
for value in timestamp_values.values()
)
integer_defaults_execute = await connection.fetchrow(
"""
SELECT
(SELECT success=0 FROM module_runs LIMIT 1) AS run,
(
SELECT validated=0 AND false_positive=0
FROM findings LIMIT 1
) AS finding,
(SELECT is_dc=0 FROM hosts LIMIT 1) AS host,
(SELECT cracked=0 FROM credentials LIMIT 1)
AS credential,
(SELECT is_active=1 FROM users LIMIT 1) AS account,
(SELECT is_active=1 FROM api_keys LIMIT 1) AS api_key,
(SELECT is_revoked=0 FROM refresh_tokens LIMIT 1)
AS refresh,
(SELECT blocked=0 FROM rate_limit_events LIMIT 1)
AS rate_limit
"""
)
integer_defaults_match = integer_defaults_execute is not None and all(
integer_defaults_execute.values()
)
audit_second = await connection.fetchval(
"""
INSERT INTO audit_log(action) VALUES($1)
RETURNING id
""",
"second-action",
)
await connection.execute(
"DELETE FROM audit_log WHERE id=$1",
audit_second,
)
audit_third = await connection.fetchval(
"""
INSERT INTO audit_log(action) VALUES($1)
RETURNING id
""",
"third-action",
)
rate_second = await connection.fetchval(
"""
INSERT INTO rate_limit_events(ip_address, bucket)
VALUES($1, $2)
RETURNING id
""",
"192.0.2.21",
"synthetic-bucket",
)
await connection.execute(
"DELETE FROM rate_limit_events WHERE id=$1",
rate_second,
)
rate_third = await connection.fetchval(
"""
INSERT INTO rate_limit_events(ip_address, bucket)
VALUES($1, $2)
RETURNING id
""",
"192.0.2.22",
"synthetic-bucket",
)
monotonic_sequences = int(audit_third) > int(audit_second) and int(rate_third) > int(
rate_second
)
finally:
await connection.close()
_require_fixed(
required_columns_rejected,
"PostgreSQL accepted NULL in a required column",
)
_require_fixed(
foreign_keys_rejected,
"PostgreSQL accepted an invalid foreign-key parent",
)
_require_fixed(
duplicate_host_rejected and duplicate_user_rejected,
"PostgreSQL uniqueness enforcement diverged",
)
_require_fixed(
set_null_enforced,
"PostgreSQL SET NULL behavior diverged",
)
_require_fixed(
campaign_actions_enforced and user_cascade_enforced,
"PostgreSQL cascade behavior diverged",
)
_require_fixed(
timestamp_defaults_execute,
"PostgreSQL aware timestamp defaults diverged",
)
_require_fixed(
integer_defaults_match,
"PostgreSQL integer defaults diverged",
)
_require_fixed(
monotonic_sequences,
"PostgreSQL sequence behavior diverged",
)
@pytest.mark.asyncio
async def test_postgres_0003_blocked_integer_contract_is_enforced() -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0003")
connection = await _connect(harness)
try:
await connection.execute(
"""
INSERT INTO rate_limit_events(ip_address, bucket)
VALUES($1, $2)
""",
"synthetic-a",
"bucket",
)
await connection.execute(
"""
INSERT INTO rate_limit_events(ip_address, bucket, blocked)
VALUES($1, $2, $3)
""",
"synthetic-b",
"bucket",
0,
)
await connection.execute(
"""
INSERT INTO rate_limit_events(ip_address, bucket, blocked)
VALUES($1, $2, $3)
""",
"synthetic-c",
"bucket",
1,
)
valid_contract = await connection.fetchrow(
"""
SELECT count(*) AS row_count,
min(blocked) AS minimum,
max(blocked) AS maximum,
bool_and(timestamp IS NOT NULL) AS timestamps_present
FROM rate_limit_events
"""
)
rejected = 0
for index, value in enumerate((None, -1, 2, 17)):
try:
await connection.execute(
"""
INSERT INTO rate_limit_events(
ip_address, bucket, blocked
) VALUES($1, $2, $3)
""",
f"synthetic-invalid-{index}",
"bucket",
value,
)
except Exception as exc:
if type(exc).__name__ in {
"CheckViolationError",
"NotNullViolationError",
}:
rejected += 1
invalid_rows = await connection.fetchval(
"""
SELECT count(*) FROM rate_limit_events
WHERE ip_address LIKE 'synthetic-invalid-%'
"""
)
check_definition = await connection.fetchval(
"""
SELECT pg_get_constraintdef(constraint_record.oid)
FROM pg_constraint AS constraint_record
JOIN pg_class AS relation
ON relation.oid=constraint_record.conrelid
WHERE relation.relname='rate_limit_events'
AND constraint_record.conname='ck_rate_limit_events_blocked_bool'
"""
)
check_is_semantic = (
isinstance(check_definition, str)
and "blocked" in check_definition
and "= ANY" in check_definition
and "0" in check_definition
and "1" in check_definition
)
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
valid_values = (
valid_contract is not None
and valid_contract["row_count"] == 3
and valid_contract["minimum"] == 0
and valid_contract["maximum"] == 1
and valid_contract["timestamps_present"] is True
)
_require_fixed(
valid_values and rejected == 4 and invalid_rows == 0 and check_is_semantic and reusable,
"PostgreSQL blocked integer contract was not enforced",
)
await _alembic(harness, "downgrade", "0002")
connection = await _connect(harness)
try:
removed = (
await connection.fetchval("SELECT to_regclass('rate_limit_events') IS NULL") is True
)
finally:
await connection.close()
await _alembic(harness, "upgrade", "0003")
connection = await _connect(harness)
try:
reupgrade_rejected = False
try:
await connection.execute(
"""
INSERT INTO rate_limit_events(
ip_address, bucket, blocked
) VALUES($1, $2, $3)
""",
"synthetic-reupgrade",
"bucket",
2,
)
except Exception as exc:
reupgrade_rejected = type(exc).__name__ == "CheckViolationError"
finally:
await connection.close()
_require_fixed(
removed and reupgrade_rejected,
"PostgreSQL 0003 downgrade/re-upgrade lost enforcement",
)
@pytest.mark.asyncio
async def test_postgres_0005_trace_normalization_round_trip() -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0004")
connection = await _connect(harness)
try:
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"campaign-trace",
"Migration campaign",
)
for index, trace_value in enumerate((None, "", "trace-marker")):
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title,
description, severity, trace_id
) VALUES($1, $2, $3, $4, $5, $6, $7)
""",
f"finding-{index}",
"campaign-trace",
"module",
f"title-{index}",
"description",
"low",
trace_value,
)
finally:
await connection.close()
await _alembic(harness, "upgrade", "0005")
connection = await _connect(harness)
try:
upgraded = await connection.fetchrow(
"""
SELECT count(*) AS row_count,
count(*) FILTER (WHERE trace_id IS NULL) AS null_count,
count(*) FILTER (WHERE trace_id='') AS empty_count,
count(*) FILTER (
WHERE trace_id='trace-marker'
) AS marker_count,
count(DISTINCT title) AS title_count
FROM findings
"""
)
finally:
await connection.close()
upgraded_correctly = upgraded is not None and tuple(upgraded.values()) == (3, 0, 2, 1, 3)
_require_fixed(
upgraded_correctly,
"PostgreSQL trace normalization changed protected state",
)
await _alembic(harness, "downgrade", "0004")
connection = await _connect(harness)
try:
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title,
description, severity, trace_id
) VALUES($1, $2, $3, $4, $5, $6, NULL)
""",
"finding-after-downgrade",
"campaign-trace",
"module",
"title-after-downgrade",
"description",
"low",
)
finally:
await connection.close()
await _alembic(harness, "upgrade", "0005")
connection = await _connect(harness)
try:
reupgraded = await connection.fetchrow(
"""
SELECT count(*) AS row_count,
count(*) FILTER (WHERE trace_id IS NULL) AS null_count,
count(*) FILTER (WHERE trace_id='') AS empty_count,
count(*) FILTER (
WHERE trace_id='trace-marker'
) AS marker_count
FROM findings
"""
)
finally:
await connection.close()
reupgraded_correctly = reupgraded is not None and tuple(reupgraded.values()) == (4, 0, 3, 1)
_require_fixed(
reupgraded_correctly,
"PostgreSQL trace re-upgrade diverged",
)
async def _install_postgres_version_failure(connection: Any) -> None:
await connection.execute(
"""
CREATE OR REPLACE FUNCTION migration_version_failure()
RETURNS trigger
LANGUAGE plpgsql
AS $body$
BEGIN
RAISE EXCEPTION 'migration version update denied';
END;
$body$
"""
)
await connection.execute(
"""
CREATE TRIGGER migration_version_failure
BEFORE UPDATE ON alembic_version
FOR EACH ROW EXECUTE FUNCTION migration_version_failure()
"""
)
async def _remove_postgres_version_failure(connection: Any) -> None:
await connection.execute("DROP TRIGGER IF EXISTS migration_version_failure ON alembic_version")
await connection.execute("DROP FUNCTION IF EXISTS migration_version_failure()")
@pytest.mark.asyncio
@pytest.mark.parametrize("direction", ["upgrade", "downgrade"])
@pytest.mark.parametrize(
"boundary",
["before-hardening", "after-hardening", "version-update"],
)
async def test_postgres_0005_failure_boundaries_roll_back_atomically(
direction: str,
boundary: str,
) -> None:
async with _postgres_harness() as harness:
starting_revision = "0004" if direction == "upgrade" else "0005"
result_revision = "0005" if direction == "upgrade" else "0004"
await _alembic(harness, "upgrade", starting_revision)
connection = await _connect(harness)
try:
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"campaign-failure",
"Synthetic campaign",
)
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title,
description, severity, trace_id
) VALUES($1, $2, $3, $4, $5, $6, $7)
""",
"finding-failure",
"campaign-failure",
"module",
"Synthetic title",
"Synthetic description",
"low",
None if direction == "upgrade" else "",
)
before_contract = await _postgres_catalog_contract(connection)
before_rows = await connection.fetchrow(
"""
SELECT
count(*) AS rows,
count(*) FILTER (WHERE trace_id IS NULL) AS nulls,
count(*) FILTER (WHERE trace_id='') AS empty_values
FROM findings
"""
)
if boundary == "version-update":
await _install_postgres_version_failure(connection)
finally:
await connection.close()
fault = {
"before-hardening": "0005-before-alter",
"after-hardening": "0005-after-alter",
"version-update": None,
}[boundary]
failed = False
try:
if direction == "upgrade":
await _alembic(
harness,
"upgrade",
"0005",
fault=fault,
)
else:
await _alembic(
harness,
"downgrade",
"0004",
fault=fault,
)
except RuntimeError:
failed = True
connection = await _connect(harness)
try:
if boundary == "version-update":
await _remove_postgres_version_failure(connection)
after_contract = await _postgres_catalog_contract(connection)
after_rows = await connection.fetchrow(
"""
SELECT
count(*) AS rows,
count(*) FILTER (WHERE trace_id IS NULL) AS nulls,
count(*) FILTER (WHERE trace_id='') AS empty_values
FROM findings
"""
)
current_revision = await _version(connection)
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
rolled_back = (
failed
and before_contract == after_contract
and before_rows is not None
and after_rows is not None
and tuple(before_rows.values()) == tuple(after_rows.values())
and current_revision == starting_revision
and reusable
)
_require_fixed(
rolled_back,
"PostgreSQL revision 0005 failure was not atomic",
)
if direction == "upgrade":
await _alembic(harness, "upgrade", "0005")
else:
await _alembic(harness, "downgrade", "0004")
connection = await _connect(harness)
try:
recovered = await _version(
connection
) == result_revision and await _postgres_catalog_contract(
connection
) == _fixed_postgres_contract(result_revision)
finally:
await connection.close()
_require_fixed(
recovered,
"PostgreSQL revision 0005 did not recover after rollback",
)
@pytest.mark.asyncio
@pytest.mark.parametrize("direction", ["upgrade", "downgrade"])
@pytest.mark.parametrize("boundary", ["rename", "version-update"])
async def test_postgres_0006_failure_after_rename_rolls_back_atomically(
direction: str,
boundary: str,
) -> None:
async with _postgres_harness() as harness:
starting_revision = "0005" if direction == "upgrade" else "0006"
result_revision = "0006" if direction == "upgrade" else "0005"
source_column = "cracked_value" if direction == "upgrade" else "cracked_value_enc"
result_column = "cracked_value_enc" if direction == "upgrade" else "cracked_value"
await _alembic(harness, "upgrade", starting_revision)
connection = await _connect(harness)
try:
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"campaign-rename",
"Synthetic campaign",
)
await connection.execute(
f"""
INSERT INTO credentials(
id, campaign_id, username, cred_type, "{source_column}"
) VALUES($1, $2, $3, $4, $5)
""",
"credential-rename",
"campaign-rename",
"synthetic-user",
"password",
"synthetic-marker",
)
before_contract = await _postgres_catalog_contract(connection)
if boundary == "version-update":
await _install_postgres_version_failure(connection)
finally:
await connection.close()
failed = False
try:
if direction == "upgrade":
await _alembic(
harness,
"upgrade",
"0006",
fault=("0006-after-rename" if boundary == "rename" else None),
)
else:
await _alembic(
harness,
"downgrade",
"0005",
fault=("0006-after-rename" if boundary == "rename" else None),
)
except RuntimeError:
failed = True
connection = await _connect(harness)
try:
if boundary == "version-update":
await _remove_postgres_version_failure(connection)
after_contract = await _postgres_catalog_contract(connection)
source_value_query = (
"SELECT count(*) FROM credentials WHERE cracked_value IS NOT NULL"
if source_column == "cracked_value"
else "SELECT count(*) FROM credentials WHERE cracked_value_enc IS NOT NULL"
)
unchanged_value = await connection.fetchval(source_value_query)
current_revision = await _version(connection)
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
_require_fixed(
failed
and before_contract == after_contract
and unchanged_value == 1
and current_revision == starting_revision
and reusable,
"PostgreSQL revision 0006 failure was not atomic",
)
if direction == "upgrade":
await _alembic(harness, "upgrade", "0006")
else:
await _alembic(harness, "downgrade", "0005")
connection = await _connect(harness)
try:
result_value_query = (
"SELECT count(*) FROM credentials WHERE cracked_value IS NOT NULL"
if result_column == "cracked_value"
else "SELECT count(*) FROM credentials WHERE cracked_value_enc IS NOT NULL"
)
recovered_value = await connection.fetchval(result_value_query)
recovered = (
await _version(connection) == result_revision
and await _postgres_catalog_contract(connection)
== _fixed_postgres_contract(result_revision)
and recovered_value == 1
)
finally:
await connection.close()
_require_fixed(
recovered,
"PostgreSQL revision 0006 did not recover after rollback",
)
async def _transform_postgres_runtime_legacy_catalog(
connection: Any,
revision: str,
) -> None:
recipe = _postgres_runtime_legacy_recipe(revision)
for table, name in recipe.constraints_to_drop:
await connection.execute(f'ALTER TABLE "{table}" DROP CONSTRAINT "{name}"')
for table in recipe.tables_to_drop:
await connection.execute(f'DROP TABLE "{table}"')
for table, column in recipe.nullable_columns:
await connection.execute(f'ALTER TABLE "{table}" ALTER COLUMN "{column}" DROP NOT NULL')
for name, table, columns in recipe.indexes_to_create:
rendered_columns = ", ".join(f'"{column}"' for column in columns)
await connection.execute(f'CREATE INDEX "{name}" ON "{table}" ({rendered_columns})')
async def _seed_postgres_legacy_catalog(
connection: Any,
revision: str,
) -> None:
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"legacy-campaign",
"Synthetic legacy campaign",
)
if revision >= "0002":
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title,
description, severity, cvss_score, cvss_vector, trace_id
) VALUES($1, $2, $3, $4, $5, $6, NULL, NULL, NULL)
""",
"legacy-finding",
"legacy-campaign",
"module",
"Synthetic finding",
"Synthetic description",
"low",
)
else:
await connection.execute(
"""
INSERT INTO findings(
id, campaign_id, module_id, title,
description, severity, cvss_score, cvss_vector
) VALUES($1, $2, $3, $4, $5, $6, NULL, NULL)
""",
"legacy-finding",
"legacy-campaign",
"module",
"Synthetic finding",
"Synthetic description",
"low",
)
await connection.execute(
"""
INSERT INTO hosts(id, campaign_id, ip_address)
VALUES($1, $2, $3)
""",
"legacy-host",
"legacy-campaign",
"192.0.2.70",
)
await connection.execute(
"""
INSERT INTO credentials(
id, campaign_id, host_id, username, cred_type, cracked_value
) VALUES($1, $2, $3, $4, $5, $6)
""",
"legacy-credential",
"legacy-campaign",
"legacy-host",
"synthetic-user",
"password",
"synthetic-marker",
)
await connection.execute(
"""
INSERT INTO loot(id, campaign_id, host_id, loot_type, name)
VALUES($1, $2, $3, $4, $5)
""",
"legacy-loot",
"legacy-campaign",
"legacy-host",
"metadata",
"Synthetic loot",
)
await connection.execute(
"INSERT INTO audit_log(campaign_id, action) VALUES($1, $2)",
"legacy-campaign",
"synthetic-action",
)
await connection.execute(
"""
INSERT INTO users(id, username, hashed_password)
VALUES($1, $2, $3)
""",
"legacy-user",
"synthetic-account",
"synthetic-hash",
)
await connection.execute(
"""
INSERT INTO api_keys(id, user_id, name, key_hash, key_prefix)
VALUES($1, $2, $3, $4, $5)
""",
"legacy-api-key",
"legacy-user",
"Synthetic key",
"synthetic-hash",
"synthetic",
)
await connection.execute(
"""
INSERT INTO refresh_tokens(id, user_id, expires_at)
VALUES($1, $2, $3)
""",
"legacy-refresh",
"legacy-user",
datetime(2099, 1, 1, tzinfo=UTC),
)
if revision >= "0003":
await connection.execute(
"""
INSERT INTO rate_limit_events(ip_address, bucket)
VALUES($1, $2)
""",
"192.0.2.71",
"synthetic-bucket",
)
if revision >= "0004":
await connection.execute(
"""
INSERT INTO revoked_access_tokens(jti, user_id, expires_at)
VALUES($1, $2, $3)
""",
"legacy-revoked",
"legacy-user",
datetime(2099, 1, 1, tzinfo=UTC),
)
@pytest.mark.asyncio
@pytest.mark.parametrize(
"legacy_revision",
["0001", "0002", "0003", "0004", "0005"],
)
async def test_complete_postgres_runtime_legacy_catalogs_are_preserved(
legacy_revision: str,
) -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", legacy_revision)
connection = await _connect(harness)
try:
await _transform_postgres_runtime_legacy_catalog(
connection,
legacy_revision,
)
await _seed_postgres_legacy_catalog(
connection,
legacy_revision,
)
initial_contract = await _postgres_catalog_contract(connection)
finally:
await connection.close()
_require_fixed(
initial_contract == _fixed_legacy_postgres_contract(legacy_revision),
"constructed PostgreSQL legacy catalog diverged",
)
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
row_counts = await connection.fetchrow(
"""
SELECT
(SELECT count(*) FROM campaigns) AS campaigns,
(SELECT count(*) FROM findings) AS findings,
(SELECT count(*) FROM hosts) AS hosts,
(SELECT count(*) FROM credentials) AS credentials,
(SELECT count(*) FROM loot) AS loot,
(SELECT count(*) FROM audit_log) AS audit,
(SELECT count(*) FROM users) AS users,
(SELECT count(*) FROM api_keys) AS api_keys,
(SELECT count(*) FROM refresh_tokens) AS refresh_tokens
"""
)
protected_value_present = await connection.fetchval(
"""
SELECT count(*) FROM credentials
WHERE cracked_value_enc IS NOT NULL
"""
)
structural_drift_deferred = (
await connection.fetchval("SELECT to_regclass('module_runs') IS NULL") is True
and not (await _postgres_catalog_contract(connection)).foreign_keys
)
current_revision = await _version(connection)
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
preserved = (
row_counts is not None
and tuple(row_counts.values()) == (1, 1, 1, 1, 1, 1, 1, 1, 1)
and protected_value_present == 1
and structural_drift_deferred
and current_revision == "0006"
and reusable
)
_require_fixed(
preserved,
"PostgreSQL legacy catalog upgrade lost protected state",
)
@pytest.mark.asyncio
@pytest.mark.parametrize(
("revision", "column"),
[("0002", "cvss_score"), ("0005", "trace_id")],
)
async def test_postgres_legacy_missing_column_variants(
revision: str,
column: str,
) -> None:
async with _postgres_harness() as harness:
parent = "0001" if revision == "0002" else "0004"
await _alembic(harness, "upgrade", parent)
connection = await _connect(harness)
try:
if revision == "0002":
await connection.execute("ALTER TABLE findings DROP COLUMN cvss_score")
await connection.execute("ALTER TABLE findings DROP COLUMN cvss_vector")
else:
await connection.execute("ALTER TABLE findings DROP COLUMN trace_id")
finally:
await connection.close()
await _alembic(harness, "upgrade", revision)
connection = await _connect(harness)
try:
repaired = (
await connection.fetchval(
"""
SELECT count(*)
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='findings'
AND column_name=$1
AND is_nullable='NO'
""",
column,
)
== 1
)
finally:
await connection.close()
_require_fixed(
repaired,
"PostgreSQL legacy finding catalog was not reconciled",
)
@pytest.mark.asyncio
@pytest.mark.parametrize(
"variant",
["target-only", "both", "neither"],
)
async def test_postgres_0006_catalog_variants(variant: str) -> None:
async with _postgres_harness() as harness:
await _alembic(harness, "upgrade", "0005")
connection = await _connect(harness)
try:
if variant == "target-only":
await connection.execute(
"ALTER TABLE credentials RENAME COLUMN cracked_value TO cracked_value_enc"
)
elif variant == "both":
await connection.execute(
"ALTER TABLE credentials ADD COLUMN cracked_value_enc TEXT"
)
else:
await connection.execute("ALTER TABLE credentials DROP COLUMN cracked_value")
finally:
await connection.close()
if variant == "target-only":
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
accepted = await _version(connection) == "0006"
finally:
await connection.close()
_require_fixed(
accepted,
"PostgreSQL target-only catalog was not accepted",
)
return
with pytest.raises(
RuntimeError,
match="^Alembic worker failed \\[RuntimeError\\]$",
):
await _alembic(harness, "upgrade", "0006")
connection = await _connect(harness)
try:
remained_at_parent = await _version(connection) == "0005"
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
_require_fixed(
remained_at_parent,
"failed PostgreSQL revision advanced the Alembic version",
)
_require_fixed(
reusable,
"PostgreSQL connection was not reusable after migration failure",
)
async def _postgres_credential_catalog_fingerprint(
connection: Any,
) -> tuple[object, ...]:
columns = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
column_name,
ordinal_position,
data_type,
udt_name,
is_nullable,
column_default,
is_identity
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='credentials'
ORDER BY ordinal_position
"""
)
)
constraints = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT
constraint_record.conname,
constraint_record.contype::text AS contype,
pg_get_constraintdef(constraint_record.oid)
FROM pg_constraint AS constraint_record
JOIN pg_class AS relation
ON relation.oid=constraint_record.conrelid
WHERE relation.relnamespace=current_schema()::regnamespace
AND relation.relname='credentials'
ORDER BY constraint_record.conname
"""
)
)
indexes = tuple(
tuple(row.values())
for row in await connection.fetch(
"""
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname=current_schema()
AND tablename='credentials'
ORDER BY indexname
"""
)
)
return columns, constraints, indexes
async def _prepare_postgres_0006_variant(
connection: Any,
*,
source: str,
target: str,
variant: str,
) -> None:
if variant == "source-only":
return
if variant == "target-only":
await connection.execute(f'ALTER TABLE credentials RENAME COLUMN "{source}" TO "{target}"')
return
if variant == "both":
await connection.execute(f'ALTER TABLE credentials ADD COLUMN "{target}" TEXT')
return
if variant == "neither":
await connection.execute(f'ALTER TABLE credentials DROP COLUMN "{source}"')
return
if variant == "target-default-null-equivalent":
await connection.execute(f'ALTER TABLE credentials DROP COLUMN "{source}"')
await connection.execute(f'ALTER TABLE credentials ADD COLUMN "{target}" TEXT DEFAULT NULL')
return
if variant.startswith("wrong-target-"):
await connection.execute(f'ALTER TABLE credentials DROP COLUMN "{source}"')
inspected = target
if variant.endswith("-type"):
definition = "VARCHAR(64)"
elif variant.endswith("-nullability"):
definition = "TEXT NOT NULL"
elif variant.endswith("-default"):
definition = "TEXT DEFAULT ''::text"
else:
raise AssertionError("unknown PostgreSQL target variant")
await connection.execute(f'ALTER TABLE credentials ADD COLUMN "{inspected}" {definition}')
return
if variant == "source-default-null-equivalent":
await connection.execute(
f'ALTER TABLE credentials ALTER COLUMN "{source}" SET DEFAULT NULL'
)
return
if variant.startswith("wrong-source-"):
if variant.endswith("-type"):
await connection.execute(
f'ALTER TABLE credentials ALTER COLUMN "{source}" TYPE VARCHAR(64)'
)
elif variant.endswith("-nullability"):
await connection.execute(
f'ALTER TABLE credentials ALTER COLUMN "{source}" SET NOT NULL'
)
elif variant.endswith("-default"):
await connection.execute(
f"ALTER TABLE credentials ALTER COLUMN \"{source}\" SET DEFAULT ''::text"
)
else:
raise AssertionError("unknown PostgreSQL source variant")
return
raise AssertionError("unknown PostgreSQL revision 0006 variant")
@pytest.mark.asyncio
@pytest.mark.parametrize("direction", ["upgrade", "downgrade"])
@pytest.mark.parametrize(
"variant",
[
"source-only",
"target-only",
"both",
"neither",
"wrong-source-type",
"wrong-source-nullability",
"wrong-source-default",
"source-default-null-equivalent",
"wrong-target-type",
"wrong-target-nullability",
"wrong-target-default",
"target-default-null-equivalent",
],
)
async def test_postgres_0006_exact_column_state_machine(
direction: str,
variant: str,
) -> None:
async with _postgres_harness() as harness:
if direction == "upgrade":
await _alembic(harness, "upgrade", "0005")
source = "cracked_value"
target = "cracked_value_enc"
parent_revision = "0005"
result_revision = "0006"
else:
await _alembic(harness, "upgrade", "0006")
source = "cracked_value_enc"
target = "cracked_value"
parent_revision = "0006"
result_revision = "0005"
connection = await _connect(harness)
try:
await _prepare_postgres_0006_variant(
connection,
source=source,
target=target,
variant=variant,
)
present_columns = {
str(row["column_name"])
for row in await connection.fetch(
"""
SELECT column_name
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='credentials'
"""
)
}
value_column = (
source
if source in present_columns
else target
if target in present_columns
else None
)
await connection.execute(
"INSERT INTO campaigns(id, name) VALUES($1, $2)",
"campaign-0006",
"Migration campaign",
)
if value_column == "cracked_value":
await connection.execute(
"""
INSERT INTO credentials(
id, campaign_id, username, cred_type, cracked_value
) VALUES($1, $2, $3, $4, $5)
""",
"credential-0006",
"campaign-0006",
"synthetic-user",
"password",
"synthetic-marker",
)
elif value_column == "cracked_value_enc":
await connection.execute(
"""
INSERT INTO credentials(
id, campaign_id, username, cred_type, cracked_value_enc
) VALUES($1, $2, $3, $4, $5)
""",
"credential-0006",
"campaign-0006",
"synthetic-user",
"password",
"synthetic-marker",
)
before_catalog = await _postgres_credential_catalog_fingerprint(connection)
before_rows = await connection.fetchval("SELECT count(*) FROM credentials")
finally:
await connection.close()
failed = False
try:
if direction == "upgrade":
await _alembic(harness, "upgrade", "0006")
else:
await _alembic(harness, "downgrade", "0005")
except RuntimeError:
failed = True
connection = await _connect(harness)
try:
after_catalog = await _postgres_credential_catalog_fingerprint(connection)
after_rows = await connection.fetchval("SELECT count(*) FROM credentials")
target_definition = await connection.fetchrow(
"""
SELECT data_type, udt_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='credentials'
AND column_name=$1
""",
target,
)
current_credential_columns = {
str(row["column_name"])
for row in await connection.fetch(
"""
SELECT column_name
FROM information_schema.columns
WHERE table_schema=current_schema()
AND table_name='credentials'
"""
)
}
target_is_exact = (
target_definition is not None
and target_definition["data_type"] == "text"
and target_definition["udt_name"] == "text"
and target_definition["is_nullable"] == "YES"
and target_definition["column_default"] is None
)
if "cracked_value_enc" in current_credential_columns:
preserved = (
await connection.fetchval(
"""
SELECT count(*) FROM credentials
WHERE cracked_value_enc IS NOT NULL
"""
)
== 1
)
elif "cracked_value" in current_credential_columns:
preserved = (
await connection.fetchval(
"""
SELECT count(*) FROM credentials
WHERE cracked_value IS NOT NULL
"""
)
== 1
)
else:
preserved = True
current_revision = await _version(connection)
reusable = await connection.fetchval("SELECT 1") == 1
finally:
await connection.close()
allowed = variant in {
"source-only",
"target-only",
"source-default-null-equivalent",
"target-default-null-equivalent",
}
if allowed:
_require_fixed(
not failed
and current_revision == result_revision
and target_is_exact
and preserved
and reusable,
"PostgreSQL revision 0006 rejected an exact catalog",
)
else:
_require_fixed(
failed,
"PostgreSQL revision 0006 accepted an incompatible catalog",
)
_require_fixed(
current_revision == parent_revision,
"PostgreSQL revision 0006 changed an incompatible revision",
)
_require_fixed(
before_catalog == after_catalog,
"PostgreSQL revision 0006 changed an incompatible schema",
)
_require_fixed(
before_rows == after_rows,
"PostgreSQL revision 0006 changed incompatible rows",
)
_require_fixed(
reusable,
"PostgreSQL revision 0006 left connection unusable",
)
def test_revision_0010_postgres_ddl_has_ten_tables_and_dialect_cycle_order() -> None:
from ares.db.execution_lifecycle import (
LIFECYCLE_TABLES,
POSTGRES_LIFECYCLE_DDL,
)
created = tuple(
statement.split("(", maxsplit=1)[0].removeprefix("CREATE TABLE ").strip()
for statement in POSTGRES_LIFECYCLE_DDL
if statement.startswith("CREATE TABLE ")
)
closing_index = next(
index
for index, statement in enumerate(POSTGRES_LIFECYCLE_DDL)
if statement.startswith("ALTER TABLE logical_executions ADD CONSTRAINT")
)
_require_fixed(created == LIFECYCLE_TABLES, "lifecycle DDL order changed")
_require_fixed(closing_index == 2, "cyclic foreign-key order changed")
_require_fixed(
"DEFERRABLE INITIALLY DEFERRED" in POSTGRES_LIFECYCLE_DDL[closing_index],
"cyclic foreign-key deferral changed",
)