Skip to content

armgit / ARES

Public Imported from GitHub
0

ARES - authorized red-team engagement automation with dashboard, campaign scope, module orchestration, OPSEC controls, encrypted vault, and reporting.

145 KB
Raw Blame
"""Real PostgreSQL integration coverage for one-time WebSocket tickets."""
from __future__ import annotations

import asyncio
import base64
import contextlib
import hashlib
import multiprocessing
import os
import re
import sys
from collections.abc import AsyncIterator, Awaitable, Callable
from contextlib import asynccontextmanager
from dataclasses import dataclass, field
from datetime import UTC, datetime, timedelta
from types import SimpleNamespace
from typing import Any
from unittest.mock import patch
from urllib.parse import quote
from uuid import uuid4

import pytest

from ares.db import postgres as postgres_module
from ares.db.postgres import PostgresDatabase
from ares.db.websocket_tickets import (
    ApiKeyTicketSource,
    BearerTicketSource,
    WebSocketTicketCredentialKind,
    generate_websocket_ticket,
    hash_websocket_ticket,
)

_POSTGRES_ENV = (
    "ARES_TEST_POSTGRES_HOST",
    "ARES_TEST_POSTGRES_PORT",
    "ARES_TEST_POSTGRES_USER",
    "ARES_TEST_POSTGRES_DB",
)
_SAFE_DATABASE_NAME = re.compile(r"^[a-z][a-z0-9_]+$")
_SAFE_EXCEPTION_TYPE = re.compile(r"^[A-Za-z_][A-Za-z0-9_]*$")
_POSTGRES_OPERATION_TIMEOUT_SECONDS = 15.0
_MIGRATION_PROCESS_TIMEOUT_SECONDS = 45.0
_MIGRATION_ACTIONS = frozenset({"upgrade", "downgrade"})
_MIGRATION_REVISIONS = frozenset(
    {"0006", "0007", "0008", "0009", "0010", "0011", "head"}
)
_OWNED_MIGRATION_PROCESSES: set[Any] = set()


@dataclass(frozen=True, slots=True, eq=False, repr=False)
class _PostgresHarness:
    database: PostgresDatabase = field(repr=False)
    database_name: str = field(repr=False)


def _require_fixed(condition: bool, message: str) -> None:
    if not condition:
        pytest.fail(message, pytrace=False)


def _postgres_test_config() -> dict[str, str | int]:
    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 present != set(_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 re.fullmatch(r"[1-9][0-9]{0,4}", raw_port) is None:
        pytest.fail("PostgreSQL test port is invalid", pytrace=False)
    port = int(raw_port)
    if not 1 <= port <= 65535:
        pytest.fail("PostgreSQL test port is outside its valid range", pytrace=False)
    return {
        "host": values["ARES_TEST_POSTGRES_HOST"],
        "port": port,
        "user": values["ARES_TEST_POSTGRES_USER"],
        "database": values["ARES_TEST_POSTGRES_DB"],
    }


def _runtime_dsn(config: dict[str, str | int], database: str) -> str:
    host = str(config["host"])
    if ":" in host and not host.startswith("["):
        host = f"[{host}]"
    return (
        "postgresql://"
        f"{quote(str(config['user']), safe='')}@"
        f"{host}:{config['port']}/"
        f"{quote(database, safe='')}"
    )


def _database_for_target(
    database_name: str,
    *,
    pool_min: int = 1,
    pool_max: int = 2,
) -> PostgresDatabase:
    return PostgresDatabase(
        _runtime_dsn(_postgres_test_config(), database_name),
        pool_min=pool_min,
        pool_max=pool_max,
    )


def _set_complete_postgres_config(monkeypatch: pytest.MonkeyPatch) -> None:
    monkeypatch.setenv("ARES_TEST_POSTGRES_HOST", "127.0.0.1")
    monkeypatch.setenv("ARES_TEST_POSTGRES_PORT", "5432")
    monkeypatch.setenv("ARES_TEST_POSTGRES_USER", "synthetic_runner")
    monkeypatch.setenv("ARES_TEST_POSTGRES_DB", "synthetic_control")


def test_ticket_postgres_config_skips_only_when_canonical_names_are_absent(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    for name in _POSTGRES_ENV:
        monkeypatch.delenv(name, raising=False)
    monkeypatch.setenv("ARES_TEST_POSTGRES_DATABASE", "ignored_alias")
    monkeypatch.setenv("ARES_DATABASE_URL", "ignored_fallback")
    skipped = False
    try:
        _postgres_test_config()
    except pytest.skip.Exception:
        skipped = True
    _require_fixed(
        skipped,
        "noncanonical PostgreSQL configuration prevented the intentional skip",
    )


@pytest.mark.parametrize(
    ("name", "value"),
    [
        pytest.param("ARES_TEST_POSTGRES_HOST", "", id="empty-host"),
        pytest.param("ARES_TEST_POSTGRES_HOST", " ", id="blank-host"),
        pytest.param("ARES_TEST_POSTGRES_HOST", " host", id="padded-host"),
        pytest.param("ARES_TEST_POSTGRES_PORT", "not-a-port", id="invalid-port"),
        pytest.param("ARES_TEST_POSTGRES_PORT", "+5432", id="signed-port"),
        pytest.param("ARES_TEST_POSTGRES_PORT", "05432", id="leading-zero-port"),
        pytest.param("ARES_TEST_POSTGRES_PORT", "5432", id="unicode-port"),
        pytest.param("ARES_TEST_POSTGRES_PORT", "0", id="low-port"),
        pytest.param("ARES_TEST_POSTGRES_PORT", "65536", id="high-port"),
        pytest.param("ARES_TEST_POSTGRES_USER", "", id="empty-user"),
        pytest.param("ARES_TEST_POSTGRES_DB", " ", id="blank-database"),
    ],
)
def test_ticket_postgres_config_rejects_present_invalid_values(
    monkeypatch: pytest.MonkeyPatch,
    name: str,
    value: str,
) -> None:
    _set_complete_postgres_config(monkeypatch)
    monkeypatch.setenv(name, value)
    failed = False
    try:
        _postgres_test_config()
    except pytest.fail.Exception:
        failed = True
    _require_fixed(failed, "invalid PostgreSQL configuration was accepted")


def test_ticket_postgres_config_rejects_partial_presence(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    for name in _POSTGRES_ENV:
        monkeypatch.delenv(name, raising=False)
    monkeypatch.setenv("ARES_TEST_POSTGRES_HOST", "127.0.0.1")
    failed = False
    try:
        _postgres_test_config()
    except pytest.fail.Exception:
        failed = True
    _require_fixed(failed, "partial PostgreSQL configuration was accepted")


def test_ticket_postgres_config_accepts_complete_canonical_names(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    _set_complete_postgres_config(monkeypatch)
    config = _postgres_test_config()
    reduced = (
        set(config) == {"host", "port", "user", "database"}
        and config["port"] == 5432
        and all(config[name] for name in ("host", "user", "database"))
    )
    _require_fixed(reduced, "complete canonical PostgreSQL configuration was rejected")


def test_ticket_harness_representation_is_secret_safe() -> None:
    marker = "synthetic-confidential-marker"
    harness = _PostgresHarness(
        database=PostgresDatabase(marker),
        database_name=f"ares_ws_ticket_{uuid4().hex}",
    )
    marker_absent = marker not in repr(harness)
    _require_fixed(marker_absent, "PostgreSQL harness representation exposed state")


class _StartupPool:
    def __init__(self) -> None:
        self.close_count = 0

    async def close(self) -> None:
        self.close_count += 1


@pytest.mark.asyncio
async def test_postgres_connect_closes_failed_pool_and_can_recover(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    pools = (_StartupPool(), _StartupPool())
    pool_index = 0

    async def _create_pool(*_args: object, **_kwargs: object) -> _StartupPool:
        nonlocal pool_index
        selected = pools[pool_index]
        pool_index += 1
        return selected

    primary = RuntimeError()
    initialization_count = 0

    async def _initialize() -> None:
        nonlocal initialization_count
        initialization_count += 1
        if initialization_count == 1:
            raise primary

    monkeypatch.setitem(
        sys.modules,
        "asyncpg",
        SimpleNamespace(create_pool=_create_pool),
    )
    database = PostgresDatabase("synthetic")
    database._init_schema = _initialize
    observed: BaseException | None = None
    with patch.object(postgres_module.logger, "info", return_value=None):
        try:
            await database.connect()
        except Exception as exc:
            observed = exc
        first_state = (
            observed is primary
            and pools[0].close_count == 1
            and database._pool is None
        )
        recovered = await database.connect()
        await database.close()
    recovered_state = (
        recovered is database
        and database._pool is pools[1]
        and pools[1].close_count == 1
        and initialization_count == 2
    )
    _require_fixed(
        first_state and recovered_state,
        "PostgreSQL startup pool cleanup/recovery contract failed",
    )


@pytest.mark.parametrize(
    ("first", "terminal", "exit_code", "eof"),
    [
        pytest.param(("OK",), ("OK",), 0, True, id="ok-before-ready"),
        pytest.param(("READY",), ("READY",), 0, True, id="duplicate-ready"),
        pytest.param(("READY",), ("ERROR", "RuntimeError"), 0, True, id="error"),
        pytest.param(("READY",), ("OK",), 1, True, id="success-then-crash"),
        pytest.param(("READY",), ("OK",), 0, False, id="trailing-frame"),
        pytest.param(("READY", "extra"), ("OK",), 0, True, id="malformed-ready"),
        pytest.param(("READY",), ("OK", "extra"), 0, True, id="malformed-terminal"),
    ],
)
def test_ticket_migration_protocol_rejects_invalid_sequences(
    first: object,
    terminal: object,
    exit_code: object,
    eof: bool,
) -> None:
    accepted = _migration_protocol_succeeded(first, terminal, exit_code, eof)
    _require_fixed(not accepted, "invalid migration child protocol was accepted")


def test_ticket_migration_protocol_accepts_one_complete_sequence() -> None:
    accepted = _migration_protocol_succeeded(
        ("READY",),
        ("OK",),
        0,
        True,
    )
    _require_fixed(accepted, "valid migration child protocol was rejected")


def _sanitized_exception_type(exc: BaseException) -> str:
    name = type(exc).__name__
    return name if _SAFE_EXCEPTION_TYPE.fullmatch(name) else "Exception"


def test_ticket_migration_exception_type_is_reduced_to_a_safe_frame() -> None:
    unsafe_exception = type("unsafe exception", (Exception,), {})
    reduced = _sanitized_exception_type(unsafe_exception())
    _require_fixed(
        reduced == "Exception",
        "migration child exception type was not sanitized",
    )


class _SettlementProcess:
    def __init__(self, states: tuple[bool, ...]) -> None:
        self._states = list(states)
        self._last_state = states[-1]
        self.actions: list[str] = []

    def is_alive(self) -> bool:
        if self._states:
            self._last_state = self._states.pop(0)
        return self._last_state

    def terminate(self) -> None:
        self.actions.append("terminate")

    def kill(self) -> None:
        self.actions.append("kill")

    def join(self, *, timeout: int) -> None:
        self.actions.append(f"join-{timeout}")


def test_ticket_migration_settlement_escalates_to_kill() -> None:
    process = _SettlementProcess((True, True, False))
    settled = _settle_migration_process(process)
    _require_fixed(
        settled
        and tuple(process.actions)
        == ("terminate", "join-2", "kill", "join-2"),
        "migration child settlement did not use the bounded escalation order",
    )


def test_ticket_migration_settlement_refuses_unproved_death() -> None:
    process = _SettlementProcess((True, True, True))
    settled = _settle_migration_process(process)
    _require_fixed(
        not settled,
        "migration child settlement accepted a live process",
    )


def _sanitized_setup_failure(action: str, exc: Exception) -> RuntimeError:
    return RuntimeError(
        f"PostgreSQL ticket setup failed [{action}: {type(exc).__name__}]"
    )


async def _bounded_operation(
    action: str,
    operation: Awaitable[Any],
) -> Any:
    try:
        return await asyncio.wait_for(
            operation,
            timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
        )
    except Exception as exc:
        raise _sanitized_setup_failure(action, exc) from None


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__}")


def _migration_child_config() -> dict[str, str | int]:
    if any(name not in os.environ for name in _POSTGRES_ENV):
        raise RuntimeError()
    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()
    raw_port = values["ARES_TEST_POSTGRES_PORT"]
    if re.fullmatch(r"[1-9][0-9]{0,4}", raw_port) is None:
        raise RuntimeError()
    port = int(raw_port)
    if not 1 <= port <= 65535:
        raise RuntimeError()
    return {
        "host": values["ARES_TEST_POSTGRES_HOST"],
        "port": port,
        "user": values["ARES_TEST_POSTGRES_USER"],
        "database": values["ARES_TEST_POSTGRES_DB"],
    }


def _ticket_migration_child(
    database_name: str,
    action: str,
    revision: str,
    control: Any,
) -> None:
    terminal_sent = False
    ready_sent = False
    devnull = -1
    try:
        devnull = os.open(os.devnull, os.O_WRONLY)
        os.dup2(devnull, 1)
        os.dup2(devnull, 2)

        from alembic import command
        from alembic.config import Config

        if (
            _SAFE_DATABASE_NAME.fullmatch(database_name) is None
            or not database_name.startswith("ares_ws_ticket_")
            or action not in _MIGRATION_ACTIONS
            or revision not in _MIGRATION_REVISIONS
        ):
            raise RuntimeError()
        config_values = _migration_child_config()
        migration_url = _runtime_dsn(
            config_values,
            database_name,
        ).replace("postgresql://", "postgresql+asyncpg://", 1)
        alembic_config = Config("alembic.ini")
        alembic_config.cmd_opts = SimpleNamespace(
            x=[f"db_url={migration_url}"],
        )
        migration_action = getattr(command, action)
        control.send(("READY",))
        ready_sent = True
        if control.recv() != ("GO",):
            raise RuntimeError()
        migration_action(alembic_config, revision)
        control.send(("OK",))
        terminal_sent = True
    except Exception as exc:
        if not terminal_sent:
            frame = "ERROR" if ready_sent else "SETUP_ERROR"
            with contextlib.suppress(Exception):
                control.send((frame, _sanitized_exception_type(exc)))
    finally:
        if devnull >= 0:
            with contextlib.suppress(OSError):
                os.close(devnull)
        with contextlib.suppress(Exception):
            control.close()


def _process_is_alive(process: Any) -> bool | None:
    try:
        return bool(process.is_alive())
    except (AssertionError, ValueError):
        return None


def _settle_migration_process(process: Any) -> bool:
    alive = _process_is_alive(process)
    if alive is None:
        return getattr(process, "pid", None) is None
    if alive:
        process.terminate()
        process.join(timeout=2)
        alive = _process_is_alive(process)
    if alive:
        process.kill()
        process.join(timeout=2)
        alive = _process_is_alive(process)
    return alive is False


def _migration_protocol_succeeded(
    first: object,
    terminal: object,
    exit_code: object,
    eof: bool,
) -> bool:
    return (
        first == ("READY",)
        and terminal == ("OK",)
        and exit_code == 0
        and eof
    )


def _run_ticket_migration(
    database_name: str,
    action: str,
    revision: str,
) -> None:
    context = multiprocessing.get_context("spawn")
    parent = None
    child = None
    process = None
    start_attempted = False
    completed = False
    try:
        parent, child = context.Pipe(duplex=True)
        process = context.Process(
            target=_ticket_migration_child,
            args=(database_name, action, revision, child),
        )
        _OWNED_MIGRATION_PROCESSES.add(process)
        start_attempted = True
        process.start()
        child.close()
        if not parent.poll(_MIGRATION_PROCESS_TIMEOUT_SECONDS):
            raise RuntimeError("PostgreSQL ticket migration process timed out")
        first = parent.recv()
        if first != ("READY",):
            raise RuntimeError("PostgreSQL ticket migration setup failed")
        parent.send(("GO",))
        if not parent.poll(_MIGRATION_PROCESS_TIMEOUT_SECONDS):
            raise RuntimeError("PostgreSQL ticket migration process timed out")
        terminal = parent.recv()
        process.join(timeout=2)
        if process.is_alive():
            raise RuntimeError("PostgreSQL ticket migration process did not exit")
        eof = False
        if parent.poll(1):
            try:
                parent.recv()
            except EOFError:
                eof = True
        completed = _migration_protocol_succeeded(
            first,
            terminal,
            process.exitcode,
            eof,
        )
        if not completed:
            raise RuntimeError("PostgreSQL ticket migration action failed")
    finally:
        settled = process is None
        if process is not None:
            if completed:
                settled = _process_is_alive(process) is False
            elif start_attempted:
                settled = _settle_migration_process(process)
            if not settled:
                raise RuntimeError(
                    "PostgreSQL ticket migration process could not be settled"
                ) from None
        if parent is not None:
            with contextlib.suppress(Exception):
                parent.close()
        if child is not None:
            with contextlib.suppress(Exception):
                child.close()
        if process is not None and settled:
            with contextlib.suppress(Exception):
                process.close()
            _OWNED_MIGRATION_PROCESSES.discard(process)


@asynccontextmanager
async def _postgres_harness(
    *,
    initialize_runtime: bool = True,
) -> AsyncIterator[_PostgresHarness]:
    config = _postgres_test_config()
    try:
        import asyncpg
    except ImportError:
        pytest.fail(
            "asyncpg is required for configured PostgreSQL tests",
            pytrace=False,
        )

    test_database = f"ares_ws_ticket_{uuid4().hex}"
    _require_fixed(
        _SAFE_DATABASE_NAME.fullmatch(test_database) is not None,
        "generated PostgreSQL database identifier was unsafe",
    )
    _require_fixed(
        test_database != config["database"],
        "generated PostgreSQL database identifier collided",
    )

    admin = None
    database = None
    creation_attempted = False
    cleanup_failures: list[str] = []
    try:
        try:
            admin = await _bounded_operation(
                "admin-connect",
                asyncpg.connect(
                    host=config["host"],
                    port=config["port"],
                    user=config["user"],
                    database=config["database"],
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                ),
            )
        except Exception as exc:
            raise _sanitized_setup_failure("admin-connect", exc) from None

        creation_attempted = True
        try:
            await _bounded_operation(
                "database-create",
                admin.execute(f'CREATE DATABASE "{test_database}"'),
            )
        except Exception as exc:
            raise _sanitized_setup_failure("database-create", exc) from None

        dsn = _runtime_dsn(config, test_database)
        database = PostgresDatabase(dsn, pool_min=1, pool_max=8)
        if initialize_runtime:
            try:
                _run_ticket_migration(test_database, "upgrade", "head")
                await _bounded_operation(
                    "runtime-initialize",
                    database.connect(),
                )
            except Exception as exc:
                raise _sanitized_setup_failure("runtime-initialize", exc) from None
        yield _PostgresHarness(
            database=database,
            database_name=test_database,
        )
    finally:
        primary_failure = sys.exception()
        workers_unsettled = bool(_OWNED_MIGRATION_PROCESSES)
        if workers_unsettled:
            cleanup_failures.append("migration-worker: UnsettledProcess")
        if database is not None:
            await _attempt_cleanup(
                "close-runtime-pool",
                database.close,
                cleanup_failures,
            )
        if creation_attempted and admin is not None and not workers_unsettled:

            async def _drop_database() -> None:
                await admin.execute(
                    f'DROP DATABASE IF EXISTS "{test_database}" WITH (FORCE)'
                )

            await _attempt_cleanup(
                "drop-test-database",
                _drop_database,
                cleanup_failures,
            )
        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 ticket cleanup failure [{failure}]"
                    )
            else:
                raise RuntimeError(
                    "PostgreSQL ticket cleanup failed: "
                    + "; ".join(cleanup_failures)
                ) from None


async def _seed_identity(
    database: PostgresDatabase,
    *,
    scopes: str = "read",
) -> tuple[str, str, str, str, str]:
    username = f"ws_ticket_{uuid4().hex}"
    original_info = postgres_module.logger.info

    def _suppress_identity_log(event: str, **values: object) -> Any:
        if event == "user_created":
            return None
        return original_info(event, **values)

    with patch.object(
        postgres_module.logger,
        "info",
        new=_suppress_identity_log,
    ):
        user_id = await database.create_user(
            username,
            "synthetic-test-password",
            "operator",
        )
    campaign_id = f"campaign-{uuid4().hex}"
    other_campaign_id = f"campaign-{uuid4().hex}"
    async with database._pool.acquire() as connection:
        await connection.executemany(
            """
            INSERT INTO campaigns(id, name, operator)
            VALUES($1, $2, $3)
            """,
            (
                (campaign_id, "Ticket campaign", username),
                (other_campaign_id, "Other campaign", username),
            ),
        )
        now = datetime.now(UTC)
        await connection.execute(
            """
            INSERT INTO refresh_token_families(
                id,user_id,auth_epoch,state,created_at,
                absolute_expires_at,retain_until
            ) VALUES($1,$2,1,'active',$3,$4,$5)
            """,
            _test_family_id(user_id),
            user_id,
            now,
            now + timedelta(days=30),
            now + timedelta(days=60),
        )
    api_key_id = (
        await database.create_api_key(
            user_id,
            "ticket-key",
            scopes=scopes,
        )
    )[0]
    return user_id, username, campaign_id, other_campaign_id, api_key_id


def _test_family_id(user_id: str) -> str:
    digest = hashlib.sha256(b"ARES-PG-WS-TEST-FAMILY\0" + user_id.encode()).digest()
    return base64.urlsafe_b64encode(digest).rstrip(b"=").decode("ascii")


def _bearer_source(user_id: str, username: str) -> BearerTicketSource:
    return BearerTicketSource(
        user_id=user_id,
        subject=username,
        jti=f"ticket-jti-{uuid4().hex}",
        expires_at=datetime.now(UTC) + timedelta(minutes=5),
        family_id=_test_family_id(user_id),
        auth_epoch=1,
    )


_EXPECTED_TICKET_COLUMNS = (
    ("ticket_hash", "text", True, "", "", True, None),
    ("campaign_id", "text", True, "", "", True, None),
    ("user_id", "text", True, "", "", True, None),
    ("credential_kind", "text", True, "", "", True, None),
    ("bearer_subject", "text", False, "", "", True, None),
    ("bearer_jti", "text", False, "", "", True, None),
    (
        "bearer_expires_at",
        "timestamp with time zone",
        False,
        "",
        "",
        True,
        None,
    ),
    ("api_key_id", "text", False, "", "", True, None),
    ("required_scope", "text", False, "", "", True, None),
    (
        "created_at",
        "timestamp with time zone",
        True,
        "",
        "",
        True,
        None,
    ),
    (
        "expires_at",
        "timestamp with time zone",
        True,
        "",
        "",
        True,
        None,
    ),
    (
        "consumed_at",
        "timestamp with time zone",
        False,
        "",
        "",
        True,
        None,
    ),
    ("bearer_family_id", "text", False, "", "", True, None),
    ("bearer_auth_epoch", "bigint", False, "", "", True, None),
)

_EXPECTED_TICKET_CHECKS = (
    (
        "ck_ws_ticket_bearer_expires_finite",
        "CHECK (bearer_expires_at IS NULL OR isfinite(bearer_expires_at))",
    ),
    (
        "ck_ws_ticket_consumed_at",
        "CHECK (consumed_at IS NULL OR consumed_at < expires_at)",
    ),
    (
        "ck_ws_ticket_consumed_finite",
        "CHECK (consumed_at IS NULL OR isfinite(consumed_at))",
    ),
    ("ck_ws_ticket_created_at", "CHECK (created_at < expires_at)"),
    ("ck_ws_ticket_created_finite", "CHECK (isfinite(created_at))"),
    ("ck_ws_ticket_expires_at", "CHECK (expires_at > created_at)"),
    ("ck_ws_ticket_expires_finite", "CHECK (isfinite(expires_at))"),
    (
        "ck_ws_ticket_hash",
        "CHECK (ticket_hash ~ '^[0-9a-f]{64}$'::text)",
    ),
    (
        "ck_ws_ticket_kind",
        "CHECK (credential_kind = ANY "
        "(ARRAY['bearer'::text, 'api_key'::text]))",
    ),
    (
        "ck_ws_ticket_source_shape",
        "CHECK (credential_kind = 'bearer'::text "
        "AND bearer_subject IS NOT NULL "
        "AND length(btrim(bearer_subject)) > 0 "
        "AND bearer_subject = btrim(bearer_subject) "
        "AND bearer_jti IS NOT NULL "
        "AND length(btrim(bearer_jti)) > 0 "
        "AND bearer_jti = btrim(bearer_jti) "
        "AND bearer_expires_at IS NOT NULL "
        "AND bearer_family_id IS NOT NULL "
        "AND bearer_auth_epoch IS NOT NULL "
        "AND bearer_auth_epoch >= 1 "
        "AND api_key_id IS NULL "
        "AND required_scope IS NULL "
        "OR credential_kind = 'api_key'::text "
        "AND bearer_subject IS NULL "
        "AND bearer_jti IS NULL "
        "AND bearer_expires_at IS NULL "
        "AND bearer_family_id IS NULL "
        "AND bearer_auth_epoch IS NULL "
        "AND api_key_id IS NOT NULL "
        "AND length(btrim(api_key_id)) > 0 "
        "AND api_key_id = btrim(api_key_id) "
        "AND required_scope = 'read'::text)",
    ),
    (
        "ck_ws_ticket_time_order",
        "CHECK (expires_at > created_at AND "
        "(consumed_at IS NULL OR consumed_at < expires_at))",
    ),
)

_EXPECTED_TICKET_FOREIGN_KEYS = (
    (
        "fk_ws_ticket_api_key",
        ("api_key_id",),
        "api_keys",
        ("id",),
        "a",
        "c",
    ),
    (
        "fk_ws_ticket_bearer_family",
        ("bearer_family_id", "user_id"),
        "refresh_token_families",
        ("id", "user_id"),
        "a",
        "c",
    ),
    (
        "fk_ws_ticket_campaign",
        ("campaign_id",),
        "campaigns",
        ("id",),
        "a",
        "c",
    ),
    (
        "fk_ws_ticket_user",
        ("user_id",),
        "users",
        ("id",),
        "a",
        "c",
    ),
)

_EXPECTED_TICKET_INDEXES = (
    (
        "idx_ws_tickets_api_key",
        "btree",
        False,
        False,
        True,
        True,
        True,
        1,
        1,
        ("api_key_id",),
        (0,),
        ("text_ops",),
        (True,),
        None,
        None,
    ),
    (
        "idx_ws_tickets_bearer_family",
        "btree",
        False,
        False,
        True,
        True,
        True,
        1,
        1,
        ("bearer_family_id",),
        (0,),
        ("text_ops",),
        (True,),
        None,
        None,
    ),
    (
        "idx_ws_tickets_campaign",
        "btree",
        False,
        False,
        True,
        True,
        True,
        1,
        1,
        ("campaign_id",),
        (0,),
        ("text_ops",),
        (True,),
        None,
        None,
    ),
    (
        "idx_ws_tickets_expires",
        "btree",
        False,
        False,
        True,
        True,
        True,
        1,
        1,
        ("expires_at",),
        (0,),
        ("timestamptz_ops",),
        (True,),
        None,
        None,
    ),
    (
        "idx_ws_tickets_user",
        "btree",
        False,
        False,
        True,
        True,
        True,
        1,
        1,
        ("user_id",),
        (0,),
        ("text_ops",),
        (True,),
        None,
        None,
    ),
    (
        "websocket_tickets_pkey",
        "btree",
        True,
        True,
        True,
        True,
        True,
        1,
        1,
        ("ticket_hash",),
        (0,),
        ("text_ops",),
        (True,),
        None,
        None,
    ),
)

_EXPECTED_TICKET_INDEX_IDENTITIES = (
    (
        "idx_ws_tickets_api_key",
        "i",
        "p",
        False,
        ("text_ops",),
        ("pg_catalog",),
    ),
    (
        "idx_ws_tickets_bearer_family",
        "i",
        "p",
        False,
        ("text_ops",),
        ("pg_catalog",),
    ),
    (
        "idx_ws_tickets_campaign",
        "i",
        "p",
        False,
        ("text_ops",),
        ("pg_catalog",),
    ),
    (
        "idx_ws_tickets_expires",
        "i",
        "p",
        False,
        ("timestamptz_ops",),
        ("pg_catalog",),
    ),
    (
        "idx_ws_tickets_user",
        "i",
        "p",
        False,
        ("text_ops",),
        ("pg_catalog",),
    ),
    (
        "websocket_tickets_pkey",
        "i",
        "p",
        False,
        ("text_ops",),
        ("pg_catalog",),
    ),
)


async def _ticket_catalog_fingerprint(connection: Any) -> dict[str, object]:
    relation = await connection.fetchrow(
        """
        SELECT rel.oid::bigint AS table_oid,
               nsp.oid::bigint AS schema_oid,
               nsp.nspname AS schema_name,
               rel.relkind::text AS relkind,
               rel.relpersistence::text AS relpersistence,
               rel.relispartition,
               rel.relrowsecurity,
               rel.relforcerowsecurity,
               (
                   SELECT COUNT(*) FROM pg_inherits AS inherited
                   WHERE inherited.inhrelid=rel.oid
               ) AS parent_count,
               (
                   SELECT COUNT(*) FROM pg_inherits AS inherited
                   WHERE inherited.inhparent=rel.oid
               ) AS child_count,
               (
                   SELECT COUNT(*) FROM pg_policy AS policy
                   WHERE policy.polrelid=rel.oid
               ) AS policy_count,
               (
                   SELECT COUNT(*) FROM pg_trigger AS trg
                   WHERE trg.tgrelid=rel.oid
                     AND NOT trg.tgisinternal
               ) AS user_trigger_count,
               (
                   SELECT COUNT(*) FROM pg_rewrite AS rewrite
                   WHERE rewrite.ev_class=rel.oid
               ) AS user_rule_count
        FROM pg_class AS rel
        JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
        WHERE nsp.nspname=current_schema()
          AND rel.relname='websocket_tickets'
        """
    )
    if relation is None:
        return {"relation": None}
    table_oid = int(relation["table_oid"])
    schema_oid = int(relation["schema_oid"])
    column_rows = await connection.fetch(
        """
        SELECT att.attname AS name,
               pg_catalog.format_type(att.atttypid, att.atttypmod) AS type,
               att.attnotnull,
               att.attidentity::text AS attidentity,
               att.attgenerated::text AS attgenerated,
               att.attcollation=typ.typcollation AS collation_is_default,
               pg_get_expr(def.adbin, def.adrelid) AS default_value
        FROM pg_attribute AS att
        JOIN pg_type AS typ ON typ.oid=att.atttypid
        LEFT JOIN pg_attrdef AS def
          ON def.adrelid=att.attrelid AND def.adnum=att.attnum
        WHERE att.attrelid=$1::oid
          AND att.attnum > 0
          AND NOT att.attisdropped
        ORDER BY att.attnum
        """,
        table_oid,
    )
    constraint_rows = await connection.fetch(
        """
        SELECT con.conname, con.contype::text AS contype,
               con.conrelid::bigint AS table_oid,
               con.confrelid::bigint AS referenced_oid,
               con.conindid::bigint AS constraint_index_oid,
               con.convalidated,
               con.condeferrable, con.condeferred,
               pg_get_constraintdef(con.oid, true) AS definition,
               ref_nsp.oid::bigint AS referenced_schema_oid,
               ref_nsp.nspname=current_schema() AS reference_is_local,
               ref_rel.relname AS referenced_table,
               ARRAY(
                   SELECT local_att.attname
                   FROM unnest(con.conkey) WITH ORDINALITY AS key(attnum, ordinality)
                   JOIN pg_attribute AS local_att
                     ON local_att.attrelid=con.conrelid
                    AND local_att.attnum=key.attnum
                   ORDER BY key.ordinality
               ) AS local_columns,
               ARRAY(
                   SELECT remote_att.attname
                   FROM unnest(con.confkey) WITH ORDINALITY AS key(attnum, ordinality)
                   JOIN pg_attribute AS remote_att
                     ON remote_att.attrelid=con.confrelid
                    AND remote_att.attnum=key.attnum
                   ORDER BY key.ordinality
               ) AS remote_columns,
               con.confupdtype::text AS confupdtype,
               con.confdeltype::text AS confdeltype
        FROM pg_constraint AS con
        LEFT JOIN pg_class AS ref_rel ON ref_rel.oid=con.confrelid
        LEFT JOIN pg_namespace AS ref_nsp ON ref_nsp.oid=ref_rel.relnamespace
        WHERE con.conrelid=$1::oid
        ORDER BY con.conname
        """,
        table_oid,
    )
    index_rows = await connection.fetch(
        """
        SELECT ind.indrelid::bigint AS table_oid,
               ind.indexrelid::bigint AS index_oid,
               idx.relnamespace::bigint AS index_schema_oid,
               idx_nsp.nspname=current_schema() AS index_schema_is_local,
               idx.relname AS name,
               idx.relkind::text AS relkind,
               idx.relpersistence::text AS relpersistence,
               idx.relispartition,
               am.amname,
               ind.indisunique, ind.indisprimary,
               ind.indisvalid, ind.indisready, ind.indislive,
               ind.indnkeyatts, ind.indnatts,
               ARRAY(
                   SELECT CASE WHEN key.attnum=0 THEN NULL ELSE att.attname END
                   FROM unnest(ind.indkey) WITH ORDINALITY AS key(attnum, ordinality)
                   LEFT JOIN pg_attribute AS att
                     ON att.attrelid=ind.indrelid AND att.attnum=key.attnum
                   ORDER BY key.ordinality
               ) AS columns,
               ARRAY(
                   SELECT option
                   FROM unnest(ind.indoption) WITH ORDINALITY AS item(option, ordinality)
                   ORDER BY item.ordinality
               ) AS options,
               ARRAY(
                   SELECT opc.opcname
                   FROM unnest(ind.indclass) WITH ORDINALITY AS item(opclass, ordinality)
                   JOIN pg_opclass AS opc ON opc.oid=item.opclass
                   ORDER BY item.ordinality
               ) AS opclasses,
               ARRAY(
                   SELECT opc.oid::bigint
                   FROM unnest(ind.indclass) WITH ORDINALITY AS item(opclass, ordinality)
                   JOIN pg_opclass AS opc ON opc.oid=item.opclass
                   ORDER BY item.ordinality
               ) AS opclass_oids,
               ARRAY(
                   SELECT opc_nsp.nspname
                   FROM unnest(ind.indclass) WITH ORDINALITY AS item(opclass, ordinality)
                   JOIN pg_opclass AS opc ON opc.oid=item.opclass
                   JOIN pg_namespace AS opc_nsp
                     ON opc_nsp.oid=opc.opcnamespace
                   ORDER BY item.ordinality
               ) AS opclass_namespaces,
               ARRAY(
                   SELECT item.collation_oid=att.attcollation
                   FROM unnest(ind.indcollation) WITH ORDINALITY
                        AS item(collation_oid, ordinality)
                   JOIN unnest(ind.indkey) WITH ORDINALITY
                        AS key(attnum, ordinality)
                     ON key.ordinality=item.ordinality
                   LEFT JOIN pg_attribute AS att
                     ON att.attrelid=ind.indrelid AND att.attnum=key.attnum
                   ORDER BY item.ordinality
               ) AS collations_match,
               pg_get_expr(ind.indexprs, ind.indrelid) AS expressions,
               pg_get_expr(ind.indpred, ind.indrelid) AS predicate
        FROM pg_index AS ind
        JOIN pg_class AS idx ON idx.oid=ind.indexrelid
        JOIN pg_namespace AS idx_nsp ON idx_nsp.oid=idx.relnamespace
        JOIN pg_am AS am ON am.oid=idx.relam
        WHERE ind.indrelid=$1::oid
        ORDER BY idx.relname
        """,
        table_oid,
    )
    canonical_opclass_rows = await connection.fetch(
        """
        SELECT opc.opcname, opc.oid::bigint AS opclass_oid
        FROM pg_opclass AS opc
        JOIN pg_namespace AS nsp ON nsp.oid=opc.opcnamespace
        JOIN pg_am AS am ON am.oid=opc.opcmethod
        WHERE nsp.nspname='pg_catalog'
          AND am.amname='btree'
          AND opc.opcname=ANY($1::text[])
        """,
        ["text_ops", "timestamptz_ops"],
    )
    canonical_opclasses = {
        str(row["opcname"]): int(row["opclass_oid"])
        for row in canonical_opclass_rows
    }
    checks = tuple(
        (
            str(row["conname"]),
            " ".join(str(row["definition"]).split()),
        )
        for row in constraint_rows
        if str(row["contype"]) == "c"
        and bool(row["convalidated"])
        and not bool(row["condeferrable"])
        and not bool(row["condeferred"])
    )
    foreign_keys = tuple(
        (
            str(row["conname"]),
            tuple(str(value) for value in row["local_columns"]),
            str(row["referenced_table"]),
            tuple(str(value) for value in row["remote_columns"]),
            str(row["confupdtype"]),
            str(row["confdeltype"]),
        )
        for row in constraint_rows
        if str(row["contype"]) == "f"
        and bool(row["convalidated"])
        and not bool(row["condeferrable"])
        and not bool(row["condeferred"])
        and bool(row["reference_is_local"])
    )
    primary = tuple(
        (
            str(row["conname"]),
            tuple(str(value) for value in row["local_columns"]),
            bool(row["convalidated"]),
            bool(row["condeferrable"]),
            bool(row["condeferred"]),
        )
        for row in constraint_rows
        if str(row["contype"]) == "p"
    )
    return {
        "relation": (
            str(relation["relkind"]),
            str(relation["relpersistence"]),
            bool(relation["relispartition"]),
            bool(relation["relrowsecurity"]),
            bool(relation["relforcerowsecurity"]),
            int(relation["parent_count"]),
            int(relation["child_count"]),
            int(relation["policy_count"]),
            int(relation["user_trigger_count"]),
            int(relation["user_rule_count"]),
        ),
        "relation_oid": table_oid,
        "schema_oid": schema_oid,
        "columns": tuple(
            (
                str(row["name"]),
                str(row["type"]),
                bool(row["attnotnull"]),
                str(row["attidentity"]),
                str(row["attgenerated"]),
                bool(row["collation_is_default"]),
                None
                if row["default_value"] is None
                else str(row["default_value"]),
            )
            for row in column_rows
        ),
        "checks": checks,
        "foreign_keys": foreign_keys,
        "primary": primary,
        "indexes": tuple(
            (
                str(row["name"]),
                str(row["amname"]),
                bool(row["indisunique"]),
                bool(row["indisprimary"]),
                bool(row["indisvalid"]),
                bool(row["indisready"]),
                bool(row["indislive"]),
                int(row["indnkeyatts"]),
                int(row["indnatts"]),
                tuple(
                    None if value is None else str(value)
                    for value in row["columns"]
                ),
                tuple(int(value) for value in row["options"]),
                tuple(str(value) for value in row["opclasses"]),
                tuple(bool(value) for value in row["collations_match"]),
                None if row["expressions"] is None else str(row["expressions"]),
                None if row["predicate"] is None else str(row["predicate"]),
            )
            for row in index_rows
        ),
        "index_identities": tuple(
            (
                str(row["name"]),
                str(row["relkind"]),
                str(row["relpersistence"]),
                bool(row["relispartition"]),
                tuple(str(value) for value in row["opclasses"]),
                tuple(str(value) for value in row["opclass_namespaces"]),
            )
            for row in index_rows
        ),
        "index_bindings": tuple(
            (
                int(row["table_oid"]) == table_oid,
                int(row["index_schema_oid"]) == schema_oid,
                bool(row["index_schema_is_local"]),
                int(row["index_oid"]),
                tuple(int(value) for value in row["opclass_oids"]),
                tuple(
                    canonical_opclasses.get(str(name), -1)
                    for name in row["opclasses"]
                ),
            )
            for row in index_rows
        ),
        "constraint_bindings": tuple(
            (
                str(row["conname"]),
                int(row["table_oid"]),
                int(row["referenced_oid"]),
                None
                if row["referenced_schema_oid"] is None
                else int(row["referenced_schema_oid"]),
                int(row["constraint_index_oid"]),
            )
            for row in constraint_rows
        ),
        "constraint_count": len(constraint_rows),
    }


def _ticket_catalog_matches_fixed_contract(
    fingerprint: dict[str, object],
) -> bool:
    return (
        fingerprint.get("relation")
        == ("r", "p", False, False, False, 0, 0, 0, 0, 0)
        and isinstance(fingerprint.get("relation_oid"), int)
        and isinstance(fingerprint.get("schema_oid"), int)
        and fingerprint.get("columns") == _EXPECTED_TICKET_COLUMNS
        and fingerprint.get("checks") == _EXPECTED_TICKET_CHECKS
        and fingerprint.get("foreign_keys") == _EXPECTED_TICKET_FOREIGN_KEYS
        and fingerprint.get("primary")
        == (("websocket_tickets_pkey", ("ticket_hash",), True, False, False),)
        and fingerprint.get("indexes") == _EXPECTED_TICKET_INDEXES
        and fingerprint.get("index_identities")
        == _EXPECTED_TICKET_INDEX_IDENTITIES
        and all(
            table_is_exact
            and schema_oid_is_exact
            and schema_name_is_exact
            and index_oid > 0
            and opclass_oids == canonical_oids
            for (
                table_is_exact,
                schema_oid_is_exact,
                schema_name_is_exact,
                index_oid,
                opclass_oids,
                canonical_oids,
            ) in fingerprint.get("index_bindings", ())
        )
        and len(
            {
                binding[3]
                for binding in fingerprint.get("index_bindings", ())
            }
        )
        == 6
        and all(
            binding[1] == fingerprint.get("relation_oid")
            for binding in fingerprint.get("constraint_bindings", ())
        )
        and fingerprint.get("constraint_count") == 16
    )


def _ticket_catalog_matches_revision_0007_contract(
    fingerprint: dict[str, object],
) -> bool:
    old_source_shape = (
        "CHECK (credential_kind = 'bearer'::text "
        "AND bearer_subject IS NOT NULL "
        "AND length(btrim(bearer_subject)) > 0 "
        "AND bearer_subject = btrim(bearer_subject) "
        "AND bearer_jti IS NOT NULL "
        "AND length(btrim(bearer_jti)) > 0 "
        "AND bearer_jti = btrim(bearer_jti) "
        "AND bearer_expires_at IS NOT NULL "
        "AND api_key_id IS NULL AND required_scope IS NULL "
        "OR credential_kind = 'api_key'::text "
        "AND bearer_subject IS NULL AND bearer_jti IS NULL "
        "AND bearer_expires_at IS NULL AND api_key_id IS NOT NULL "
        "AND length(btrim(api_key_id)) > 0 "
        "AND api_key_id = btrim(api_key_id) "
        "AND required_scope = 'read'::text)"
    )
    old_checks = tuple(
        (name, old_source_shape if name == "ck_ws_ticket_source_shape" else definition)
        for name, definition in _EXPECTED_TICKET_CHECKS
    )
    old_foreign_keys = tuple(
        item
        for item in _EXPECTED_TICKET_FOREIGN_KEYS
        if item[0] != "fk_ws_ticket_bearer_family"
    )
    old_indexes = tuple(
        item
        for item in _EXPECTED_TICKET_INDEXES
        if item[0] != "idx_ws_tickets_bearer_family"
    )
    old_identities = tuple(
        item
        for item in _EXPECTED_TICKET_INDEX_IDENTITIES
        if item[0] != "idx_ws_tickets_bearer_family"
    )
    return (
        fingerprint.get("relation")
        == ("r", "p", False, False, False, 0, 0, 0, 0, 0)
        and fingerprint.get("columns") == _EXPECTED_TICKET_COLUMNS[:12]
        and fingerprint.get("checks") == old_checks
        and fingerprint.get("foreign_keys") == old_foreign_keys
        and fingerprint.get("primary")
        == (("websocket_tickets_pkey", ("ticket_hash",), True, False, False),)
        and fingerprint.get("indexes") == old_indexes
        and fingerprint.get("index_identities") == old_identities
        and len(fingerprint.get("index_bindings", ())) == 5
        and fingerprint.get("constraint_count") == 15
    )


@pytest.mark.asyncio
async def test_postgres_catalog_issue_consume_replay_and_revalidation() -> None:
    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, other_id, _key_id = (
            await _seed_identity(database)
        )
        source = _bearer_source(user_id, username)
        issued = await database.issue_websocket_ticket(campaign_id, source)
        _require_fixed(issued is not None, "valid bearer ticket was not issued")
        raw_ticket, ttl = issued or ("", 0)

        async with database._pool.acquire() as connection:
            await database._validate_websocket_ticket_schema(connection)
            catalog = await _ticket_catalog_fingerprint(connection)
            row = await connection.fetchrow(
                """
                SELECT ticket_hash, bearer_subject, bearer_jti, api_key_id
                FROM websocket_tickets
                """
            )
        catalog_is_exact = _ticket_catalog_matches_fixed_contract(catalog)
        raw_absent = row is not None and all(
            raw_ticket != value for value in tuple(row)
        )
        digest_matches = (
            row is not None
            and row["ticket_hash"] == hash_websocket_ticket(raw_ticket)
        )

        wrong = await database.consume_websocket_ticket(raw_ticket, other_id)
        consumed = await database.consume_websocket_ticket(
            raw_ticket,
            campaign_id,
        )
        replay = await database.consume_websocket_ticket(
            raw_ticket,
            campaign_id,
        )
        principal = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        resolved = (
            principal is not None
            and principal.user_id == user_id
            and principal.role == "operator"
        )

        _require_fixed(ttl == 30, "ticket lifetime contract changed")
        _require_fixed(catalog_is_exact, "ticket catalog contract changed")
        _require_fixed(raw_absent, "raw ticket was persisted")
        _require_fixed(digest_matches, "stored digest did not match ticket")
        _require_fixed(wrong is None, "wrong campaign consumed a ticket")
        _require_fixed(consumed is not None, "valid ticket was not consumed")
        _require_fixed(replay is None, "ticket replay was accepted")
        _require_fixed(resolved, "bearer principal was not resolved")

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='reporter' WHERE id=$1",
                user_id,
            )
        current_role = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        current_role_applied = (
            current_role is not None and current_role.role == "reporter"
        )
        _require_fixed(
            current_role_applied,
            "bearer resolver did not use the current role",
        )

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='unknown-role' WHERE id=$1",
                user_id,
            )
        invalid_role = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        _require_fixed(invalid_role is None, "unknown role remained authorized")

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='operator' WHERE id=$1",
                user_id,
            )
            await connection.execute(
                "UPDATE campaigns SET operator='different-operator' WHERE id=$1",
                campaign_id,
            )
        inaccessible = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        _require_fixed(
            inaccessible is None,
            "campaign access loss remained authorized",
        )

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='team_lead' WHERE id=$1",
                user_id,
            )
        promoted = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='reporter' WHERE id=$1",
                user_id,
            )
        demoted = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        _require_fixed(
            promoted is not None
            and promoted.role == "team_lead"
            and demoted is None,
            "bearer role/campaign revalidation matrix failed",
        )

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='operator' WHERE id=$1",
                user_id,
            )
            await connection.execute(
                "UPDATE campaigns SET operator=$1 WHERE id=$2",
                username,
                campaign_id,
            )
            await connection.execute(
                """
                INSERT INTO revoked_access_tokens(jti, user_id, expires_at)
                VALUES($1, $2, $3)
                """,
                source.jti,
                user_id,
                datetime.now(UTC) + timedelta(minutes=5),
            )
        revoked = (
            await database.resolve_websocket_ticket_principal(consumed)
            if consumed is not None
            else None
        )
        _require_fixed(revoked is None, "revoked bearer remained authorized")


@pytest.mark.asyncio
async def test_postgres_bearer_resolver_mutation_matrix_and_pool_recovery() -> None:
    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, _other_id, _key_id = (
            await _seed_identity(database)
        )
        issued = await database.issue_websocket_ticket(
            campaign_id,
            _bearer_source(user_id, username),
        )
        _require_fixed(issued is not None, "bearer mutation setup failed")
        consumed = await database.consume_websocket_ticket(
            (issued or ("", 0))[0],
            campaign_id,
        )
        _require_fixed(consumed is not None, "bearer mutation consume failed")
        if consumed is None:
            return

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET username='renamed-user' WHERE id=$1",
                user_id,
            )
        renamed = await database.resolve_websocket_ticket_principal(consumed)
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET username=$1 WHERE id=$2",
                username,
                user_id,
            )
            await connection.execute(
                "UPDATE users SET is_active=0 WHERE id=$1",
                user_id,
            )
        inactive = await database.resolve_websocket_ticket_principal(consumed)
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET is_active=1 WHERE id=$1",
                user_id,
            )
        async with database._pool.acquire() as connection:
            await connection.execute(
                """
                UPDATE refresh_token_families
                SET created_at=now() - interval '2 days',
                    absolute_expires_at=now() - interval '1 day',
                    retain_until=now() + interval '1 day'
                WHERE id=$1
                """,
                consumed.bearer_family_id,
            )
        expired = await database.resolve_websocket_ticket_principal(consumed)
        async with database._pool.acquire() as connection:
            await connection.execute(
                """
                UPDATE refresh_token_families
                SET created_at=now(),
                    absolute_expires_at=now() + interval '30 days',
                    retain_until=now() + interval '60 days'
                WHERE id=$1
                """,
                consumed.bearer_family_id,
            )
        recovered = await database.resolve_websocket_ticket_principal(consumed)
        async with database._pool.acquire() as connection:
            reusable = await connection.fetchval("SELECT 1")
            await connection.execute("DELETE FROM users WHERE id=$1", user_id)
        deleted = await database.resolve_websocket_ticket_principal(consumed)
        _require_fixed(
            renamed is None
            and inactive is None
            and expired is None
            and recovered is not None
            and deleted is None
            and reusable == 1,
            "bearer resolver mutation matrix failed",
        )


@pytest.mark.asyncio
async def test_postgres_ticket_timestamps_reject_both_infinities() -> None:
    async with _postgres_harness() as harness:
        import asyncpg

        database = harness.database
        user_id, username, campaign_id, _other_id, _key_id = (
            await _seed_identity(database)
        )
        issued = await database.issue_websocket_ticket(
            campaign_id,
            _bearer_source(user_id, username),
        )
        _require_fixed(issued is not None, "finite timestamp setup failed")
        raw_ticket = (issued or ("", 0))[0]
        digest = hash_websocket_ticket(raw_ticket)
        rejected = 0
        async with database._pool.acquire() as connection:
            await connection.execute(
                """
                UPDATE websocket_tickets
                SET bearer_expires_at=now() + interval '5 minutes',
                    expires_at=now() + interval '2 minutes',
                    created_at=now(),
                    consumed_at=now()
                WHERE ticket_hash=$1
                """,
                digest,
            )
            finite_values = await connection.fetchrow(
                """
                SELECT bearer_expires_at, created_at, expires_at, consumed_at
                FROM websocket_tickets
                WHERE ticket_hash=$1
                """,
                digest,
            )
            finite_values_are_aware = finite_values is not None and all(
                value is not None and value.utcoffset() is not None
                for value in tuple(finite_values)
            )
            for statement in (
                """
                UPDATE websocket_tickets
                SET bearer_expires_at=$2::text::timestamptz
                WHERE ticket_hash=$1
                """,
                """
                UPDATE websocket_tickets
                SET created_at=$2::text::timestamptz
                WHERE ticket_hash=$1
                """,
                """
                UPDATE websocket_tickets
                SET expires_at=$2::text::timestamptz
                WHERE ticket_hash=$1
                """,
                """
                UPDATE websocket_tickets
                SET consumed_at=$2::text::timestamptz
                WHERE ticket_hash=$1
                """,
            ):
                for literal in ("infinity", "-infinity"):
                    before = await connection.fetchrow(
                        """
                        SELECT bearer_expires_at, created_at,
                               expires_at, consumed_at
                        FROM websocket_tickets
                        WHERE ticket_hash=$1
                        """,
                        digest,
                    )
                    failed = False
                    try:
                        await connection.execute(
                            statement,
                            digest,
                            literal,
                        )
                    except asyncpg.CheckViolationError:
                        failed = True
                    after = await connection.fetchrow(
                        """
                        SELECT bearer_expires_at, created_at,
                               expires_at, consumed_at
                        FROM websocket_tickets
                        WHERE ticket_hash=$1
                        """,
                        digest,
                    )
                    unchanged = (
                        before is not None
                        and after is not None
                        and tuple(before) == tuple(after)
                    )
                    rejected += int(failed)
                    reusable = await connection.fetchval("SELECT 1")
                    _require_fixed(
                        failed and unchanged and reusable == 1,
                        "non-finite ticket timestamp was accepted",
                    )
            null_rejected = 0
            for statement in (
                """
                UPDATE websocket_tickets
                SET created_at=NULL
                WHERE ticket_hash=$1
                """,
                """
                UPDATE websocket_tickets
                SET expires_at=NULL
                WHERE ticket_hash=$1
                """,
            ):
                before = await connection.fetchrow(
                    """
                    SELECT bearer_expires_at, created_at,
                           expires_at, consumed_at
                    FROM websocket_tickets
                    WHERE ticket_hash=$1
                    """,
                    digest,
                )
                failed = False
                try:
                    await connection.execute(
                        statement,
                        digest,
                    )
                except asyncpg.NotNullViolationError:
                    failed = True
                after = await connection.fetchrow(
                    """
                    SELECT bearer_expires_at, created_at,
                           expires_at, consumed_at
                    FROM websocket_tickets
                    WHERE ticket_hash=$1
                    """,
                    digest,
                )
                unchanged = (
                    before is not None
                    and after is not None
                    and tuple(before) == tuple(after)
                )
                reusable = await connection.fetchval("SELECT 1")
                null_rejected += int(failed)
                _require_fixed(
                    failed and unchanged and reusable == 1,
                    "required ticket timestamp accepted NULL",
                )

            api_issued = await database.issue_websocket_ticket(
                campaign_id,
                ApiKeyTicketSource(user_id=user_id, api_key_id=_key_id),
            )
            _require_fixed(
                api_issued is not None,
                "API-key timestamp setup failed",
            )
            api_digest = hash_websocket_ticket((api_issued or ("", 0))[0])
            await connection.execute(
                """
                UPDATE websocket_tickets
                SET bearer_expires_at=NULL, consumed_at=NULL
                WHERE ticket_hash=$1
                """,
                api_digest,
            )
            nullable_values = await connection.fetchrow(
                """
                SELECT bearer_expires_at IS NULL AS bearer_is_null,
                       consumed_at IS NULL AS consumed_is_null
                FROM websocket_tickets
                WHERE ticket_hash=$1
                """,
                api_digest,
            )
            nullable_accepted = (
                nullable_values is not None
                and bool(nullable_values["bearer_is_null"])
                and bool(nullable_values["consumed_is_null"])
            )
            insert_hash = "f" * 64
            insert_failed = False
            try:
                await connection.execute(
                    """
                    INSERT INTO websocket_tickets(
                        ticket_hash, campaign_id, user_id,
                        credential_kind, bearer_subject, bearer_jti,
                        bearer_expires_at, created_at, expires_at
                    )
                    VALUES(
                        $1, $2, $3, 'bearer', $4, $5,
                        now() + interval '5 minutes',
                        'infinity'::timestamptz,
                        now() + interval '30 seconds'
                    )
                    """,
                    insert_hash,
                    campaign_id,
                    user_id,
                    username,
                    f"finite-insert-{uuid4().hex}",
                )
            except asyncpg.CheckViolationError:
                insert_failed = True
            inserted_rows = await connection.fetchval(
                """
                SELECT COUNT(*) FROM websocket_tickets
                WHERE ticket_hash=$1
                """,
                insert_hash,
            )
            insert_reusable = await connection.fetchval("SELECT 1")
            rejected_insert_is_clean = (
                insert_failed and inserted_rows == 0 and insert_reusable == 1
            )
        _require_fixed(rejected == 8, "finite timestamp rejection count changed")
        _require_fixed(
            null_rejected == 2,
            "required timestamp NULL rejection count changed",
        )
        _require_fixed(
            finite_values_are_aware
            and nullable_accepted
            and rejected_insert_is_clean,
            "ticket timestamp finite/nullable contract changed",
        )


@pytest.mark.asyncio
async def test_postgres_api_key_resolution_and_expiry_cleanup() -> None:
    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, _other_id, key_id = (
            await _seed_identity(database, scopes="read,write")
        )
        async with database._pool.acquire() as connection:
            before = await connection.fetchval(
                "SELECT last_used FROM api_keys WHERE id=$1",
                key_id,
            )
        issued = await database.issue_websocket_ticket(
            campaign_id,
            ApiKeyTicketSource(user_id=user_id, api_key_id=key_id),
        )
        _require_fixed(issued is not None, "valid API-key ticket was not issued")
        raw_ticket = (issued or ("", 0))[0]
        handle = await database.consume_websocket_ticket(
            raw_ticket,
            campaign_id,
        )
        principal = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        repeated_principal = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            after = await connection.fetchval(
                "SELECT last_used FROM api_keys WHERE id=$1",
                key_id,
            )
            await connection.execute(
                "UPDATE api_keys SET scopes='write' WHERE id=$1",
                key_id,
            )
        write_principal = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE api_keys SET scopes='admin' WHERE id=$1",
                key_id,
            )
        admin_principal = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        renamed_username = f"renamed-{uuid4().hex}"
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET username=$1 WHERE id=$2",
                renamed_username,
                user_id,
            )
            await connection.execute(
                "UPDATE campaigns SET operator=$1 WHERE id=$2",
                renamed_username,
                campaign_id,
            )
        renamed_principal = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE campaigns SET operator='different-owner' WHERE id=$1",
                campaign_id,
            )
        reassigned = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='team_lead' WHERE id=$1",
                user_id,
            )
        promoted = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='reporter' WHERE id=$1",
                user_id,
            )
        demoted = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET role='unknown-role' WHERE id=$1",
                user_id,
            )
        invalid_role = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET username=$1, role='operator' WHERE id=$2",
                username,
                user_id,
            )
            await connection.execute(
                "UPDATE campaigns SET operator=$1 WHERE id=$2",
                username,
                campaign_id,
            )
            await connection.execute(
                "UPDATE api_keys SET scopes='read' WHERE id=$1",
                key_id,
            )
            await connection.execute(
                """
                UPDATE websocket_tickets
                SET expires_at=now(), consumed_at=NULL
                WHERE ticket_hash=$1
                """,
                hash_websocket_ticket(raw_ticket),
            )
        deleted = await database.purge_expired_websocket_tickets()
        async with database._pool.acquire() as connection:
            after_purge = await connection.fetchval(
                "SELECT last_used FROM api_keys WHERE id=$1",
                key_id,
            )
        resolved = (
            principal is not None
            and principal.credential_kind
            is WebSocketTicketCredentialKind.API_KEY
            and "read" in principal.api_key_scopes
        )
        _require_fixed(resolved, "API-key principal was not resolved")
        scope_and_identity_matrix = (
            repeated_principal is not None
            and write_principal is not None
            and write_principal.api_key_scopes == ("write",)
            and admin_principal is not None
            and admin_principal.api_key_scopes == ("admin",)
            and renamed_principal is not None
            and renamed_principal.username == renamed_username
            and renamed_principal.user_id == user_id
            and renamed_principal.api_key_id == key_id
            and reassigned is None
            and promoted is not None
            and promoted.role == "team_lead"
            and demoted is None
            and invalid_role is None
        )
        _require_fixed(
            scope_and_identity_matrix,
            "API-key scope/identity/role matrix failed",
        )
        _require_fixed(
            before == after == after_purge,
            "API-key last_used was mutated",
        )
        _require_fixed(deleted == 1, "exact-now purge boundary changed")

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE api_keys SET scopes='telemetry' WHERE id=$1",
                key_id,
            )
        invalid_scope = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        issue_denied = await database.issue_websocket_ticket(
            campaign_id,
            ApiKeyTicketSource(user_id=user_id, api_key_id=key_id),
        )
        _require_fixed(
            invalid_scope is None,
            "API-key scope loss remained authorized",
        )
        _require_fixed(
            issue_denied is None,
            "API key without an accepted scope issued a ticket",
        )

        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE api_keys SET scopes='read' WHERE id=$1",
                key_id,
            )
            await connection.execute(
                "UPDATE api_keys SET is_active=0 WHERE id=$1",
                key_id,
            )
        inactive_key = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE api_keys SET is_active=1, expires_at=now() WHERE id=$1",
                key_id,
            )
        expired_key = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE api_keys SET expires_at=NULL WHERE id=$1",
                key_id,
            )
            await connection.execute(
                "UPDATE users SET is_active=0 WHERE id=$1",
                user_id,
            )
        inactive_user = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "UPDATE users SET is_active=1 WHERE id=$1",
                user_id,
            )
        recovered_principal = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        _require_fixed(
            inactive_key is None
            and expired_key is None
            and inactive_user is None
            and recovered_principal is not None,
            "API-key resolver mutation matrix failed",
        )

        expiring = await database.issue_websocket_ticket(
            campaign_id,
            ApiKeyTicketSource(user_id=user_id, api_key_id=key_id),
        )
        _require_fixed(expiring is not None, "expiry setup ticket was not issued")
        expiring_raw = (expiring or ("", 0))[0]
        async with database._pool.acquire() as connection:
            await connection.execute(
                """
                UPDATE websocket_tickets SET expires_at=now()
                WHERE ticket_hash=$1
                """,
                hash_websocket_ticket(expiring_raw),
            )
        exact_now = await database.consume_websocket_ticket(
            expiring_raw,
            campaign_id,
        )
        _require_fixed(exact_now is None, "exact-now ticket was consumed")

        replacement_key_id = (
            await database.create_api_key(
                user_id,
                "replacement-ticket-key",
                scopes="read",
            )
        )[0]
        stable_binding = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute(
                "DELETE FROM api_keys WHERE id=$1",
                key_id,
            )
        deleted_key = (
            await database.resolve_websocket_ticket_principal(handle)
            if handle is not None
            else None
        )
        replacement_issued = await database.issue_websocket_ticket(
            campaign_id,
            ApiKeyTicketSource(
                user_id=user_id,
                api_key_id=replacement_key_id,
            ),
        )
        replacement_handle = (
            await database.consume_websocket_ticket(
                (replacement_issued or ("", 0))[0],
                campaign_id,
            )
            if replacement_issued is not None
            else None
        )
        async with database._pool.acquire() as connection:
            await connection.execute("DELETE FROM users WHERE id=$1", user_id)
        deleted_owner = (
            await database.resolve_websocket_ticket_principal(replacement_handle)
            if replacement_handle is not None
            else None
        )
        binding_and_deletion_matrix = (
            stable_binding is not None
            and stable_binding.user_id == user_id
            and stable_binding.api_key_id == key_id
            and replacement_key_id != key_id
            and deleted_key is None
            and replacement_handle is not None
            and deleted_owner is None
        )
        _require_fixed(
            binding_and_deletion_matrix,
            "API-key ownership/replacement/deletion matrix failed",
        )


@pytest.mark.asyncio
async def test_postgres_two_pools_have_one_consumer() -> None:
    async with _postgres_harness() as harness:
        first = harness.database
        second = _database_for_target(harness.database_name)
        first_task: asyncio.Task[Any] | None = None
        second_task: asyncio.Task[Any] | None = None
        release_exit = asyncio.Event()
        original_pool = first._pool
        second_original_pool = None
        first_backend_pids: list[int] = []
        second_backend_pids: list[int] = []
        second_update_started = asyncio.Event()
        try:
            await _bounded_operation("second-runtime-initialize", second.connect())
            second_original_pool = second._pool
            user_id, username, campaign_id, _other_id, _key_id = (
                await _seed_identity(first)
            )
            issued = await first.issue_websocket_ticket(
                campaign_id,
                _bearer_source(user_id, username),
            )
            _require_fixed(issued is not None, "ticket setup failed")
            raw_ticket = (issued or ("", 0))[0]
            digest = hash_websocket_ticket(raw_ticket)
            exit_reached = asyncio.Event()
            first._pool = _BarrierPool(
                original_pool,
                exit_reached,
                release_exit,
                first_backend_pids,
            )
            second._pool = _PoolProxy(
                second_original_pool,
                [],
                second_backend_pids,
                second_update_started,
            )
            first_task = asyncio.create_task(
                first.consume_websocket_ticket(raw_ticket, campaign_id)
            )
            await asyncio.wait_for(exit_reached.wait(), timeout=10)
            second_task = asyncio.create_task(
                second.consume_websocket_ticket(raw_ticket, campaign_id)
            )
            await asyncio.wait_for(second_update_started.wait(), timeout=10)

            async def _observe_blocked_consumer() -> bool:
                async with original_pool.acquire() as observer:
                    observer_pid = int(
                        await observer.fetchval("SELECT pg_backend_pid()")
                    )
                    for _ in range(200):
                        blocked = await observer.fetchval(
                            """
                            SELECT
                                EXISTS(
                                    SELECT 1 FROM pg_locks
                                    WHERE pid=$1 AND NOT granted
                                )
                                AND $2=ANY(pg_blocking_pids($1))
                            """,
                            second_backend_pids[0],
                            first_backend_pids[0],
                        )
                        if blocked:
                            hidden = await observer.fetchval(
                                """
                                SELECT consumed_at IS NULL
                                FROM websocket_tickets
                                WHERE ticket_hash=$1
                                """,
                                digest,
                            )
                            distinct = len(
                                {
                                    first_backend_pids[0],
                                    second_backend_pids[0],
                                    observer_pid,
                                }
                            ) == 3
                            return bool(hidden) and distinct
                        await asyncio.sleep(0.01)
                return False

            lock_and_visibility_proved = await asyncio.wait_for(
                _observe_blocked_consumer(),
                timeout=10,
            )
            second_waited = not second_task.done()
            release_exit.set()
            results = await asyncio.wait_for(
                asyncio.gather(first_task, second_task),
                timeout=10,
            )
            first._pool = original_pool
            second._pool = second_original_pool
            winner_count = sum(result is not None for result in results)
            async with original_pool.acquire() as observer:
                committed_visible = await observer.fetchval(
                    """
                    SELECT consumed_at IS NOT NULL
                    FROM websocket_tickets
                    WHERE ticket_hash=$1
                    """,
                    digest,
                )
            _require_fixed(
                lock_and_visibility_proved and second_waited,
                "cross-pool row-lock visibility was not proved",
            )
            _require_fixed(
                winner_count == 1 and bool(committed_visible),
                "ticket did not have exactly one PostgreSQL consumer",
            )
            async with second._pool.acquire() as connection:
                reusable = await connection.fetchval("SELECT 1")
            _require_fixed(reusable == 1, "second pool was not reusable")
        finally:
            primary_failure = sys.exception()
            release_exit.set()
            first._pool = original_pool
            if second_original_pool is not None:
                second._pool = second_original_pool
            for task in (first_task, second_task):
                if task is not None and not task.done():
                    task.cancel()
            tasks = tuple(
                task
                for task in (first_task, second_task)
                if task is not None
            )
            if tasks:
                await asyncio.wait_for(
                    asyncio.gather(*tasks, return_exceptions=True),
                    timeout=10,
                )
            close_failures: list[str] = []
            if second._pool is not None:
                await _attempt_cleanup(
                    "second-runtime-close",
                    second.close,
                    close_failures,
                )
            if close_failures:
                if primary_failure is not None:
                    for failure in close_failures:
                        primary_failure.add_note(
                            f"PostgreSQL ticket cleanup failure [{failure}]"
                        )
                else:
                    raise RuntimeError(
                        "PostgreSQL ticket secondary cleanup failed"
                    ) from None


@pytest.mark.asyncio
async def test_postgres_collision_rolls_back_and_pool_recovers(
    monkeypatch: pytest.MonkeyPatch,
) -> None:
    async with _postgres_harness() as harness:
        import asyncpg

        database = harness.database
        user_id, username, campaign_id, _other_id, _key_id = (
            await _seed_identity(database)
        )
        fixed_ticket = generate_websocket_ticket()
        with monkeypatch.context() as patcher:
            patcher.setattr(
                postgres_module,
                "generate_websocket_ticket",
                lambda: fixed_ticket,
            )
            first = await database.issue_websocket_ticket(
                campaign_id,
                _bearer_source(user_id, username),
            )
            _require_fixed(first is not None, "collision setup failed")
            collision_type: type[BaseException] | None = None
            try:
                await database.issue_websocket_ticket(
                    campaign_id,
                    _bearer_source(user_id, username),
                )
            except Exception as exc:
                collision_type = type(exc)
            _require_fixed(
                collision_type is asyncpg.UniqueViolationError,
                "ticket collision did not fail with the expected fixed type",
            )

        consumed = await database.consume_websocket_ticket(
            fixed_ticket,
            campaign_id,
        )
        recovered = await database.issue_websocket_ticket(
            campaign_id,
            _bearer_source(user_id, username),
        )
        async with database._pool.acquire() as connection:
            reusable = await connection.fetchval("SELECT 1")
        _require_fixed(consumed is not None, "collision damaged prior ticket")
        _require_fixed(recovered is not None, "issuance did not recover")
        _require_fixed(reusable == 1, "pool was not reusable after collision")


class _ConnectionProxy:
    def __init__(
        self,
        connection: Any,
        classes: list[str],
        backend_pids: list[int] | None = None,
        update_started: asyncio.Event | None = None,
    ) -> None:
        self._connection = connection
        self._classes = classes
        self._backend_pids = backend_pids
        self._update_started = update_started

    def transaction(self, *args: object, **kwargs: object) -> Any:
        return self._connection.transaction(*args, **kwargs)

    async def fetchrow(
        self,
        query: str,
        *args: object,
        **kwargs: object,
    ) -> Any:
        normalized = " ".join(query.upper().split())
        if (
            normalized.startswith("INSERT INTO WEBSOCKET_TICKETS")
            and "'API_KEY'" in normalized
        ):
            required = (
                "CREDENTIAL_KIND, API_KEY_ID, REQUIRED_SCOPE,"
                " CREATED_AT, EXPIRES_AT" in normalized
                and "SELECT $1, C.ID, U.ID, 'API_KEY', AK.ID, 'READ',"
                " NOW(), NOW() + INTERVAL '30 SECONDS'" in normalized
                and "AK.ID=$4" in normalized
                and "AK.USER_ID=$3" in normalized
            )
            self._classes.append(
                "API KEY INSERT WITH REQUIRED SCOPE"
                if required
                else "UNSAFE API KEY INSERT"
            )
        elif normalized.startswith("UPDATE WEBSOCKET_TICKETS"):
            if self._backend_pids is not None:
                self._backend_pids.append(
                    int(await self._connection.fetchval("SELECT pg_backend_pid()"))
                )
            if self._update_started is not None:
                self._update_started.set()
            required_predicates = (
                "TICKET_HASH=$1",
                "CAMPAIGN_ID=$2",
                "CONSUMED_AT IS NULL",
                "EXPIRES_AT > NOW()",
            )
            required_return_fields = (
                "CAMPAIGN_ID",
                "USER_ID",
                "CREDENTIAL_KIND",
                "BEARER_SUBJECT",
                "BEARER_JTI",
                "BEARER_EXPIRES_AT",
                "BEARER_FAMILY_ID",
                "BEARER_AUTH_EPOCH",
                "API_KEY_ID",
                "REQUIRED_SCOPE",
            )
            returning = normalized.partition(" RETURNING ")[2]
            returned_fields = tuple(
                field.strip() for field in returning.split(",")
            )
            required = (
                all(predicate in normalized for predicate in required_predicates)
                and returned_fields == required_return_fields
            )
            self._classes.append(
                "CONDITIONAL UPDATE RETURNING"
                if required
                else "UNSAFE UPDATE"
            )
        return await self._connection.fetchrow(query, *args, **kwargs)

    def __getattr__(self, name: str) -> Any:
        return getattr(self._connection, name)


class _AcquireProxy:
    def __init__(
        self,
        acquisition: Any,
        classes: list[str],
        backend_pids: list[int] | None = None,
        update_started: asyncio.Event | None = None,
    ) -> None:
        self._acquisition = acquisition
        self._classes = classes
        self._backend_pids = backend_pids
        self._update_started = update_started
        self._connection = None

    async def __aenter__(self) -> _ConnectionProxy:
        self._connection = await self._acquisition.__aenter__()
        return _ConnectionProxy(
            self._connection,
            self._classes,
            self._backend_pids,
            self._update_started,
        )

    async def __aexit__(self, *exc_info: object) -> object:
        return await self._acquisition.__aexit__(*exc_info)


class _PoolProxy:
    def __init__(
        self,
        pool: Any,
        classes: list[str],
        backend_pids: list[int] | None = None,
        update_started: asyncio.Event | None = None,
    ) -> None:
        self._pool = pool
        self._classes = classes
        self._backend_pids = backend_pids
        self._update_started = update_started

    def acquire(self) -> _AcquireProxy:
        return _AcquireProxy(
            self._pool.acquire(),
            self._classes,
            self._backend_pids,
            self._update_started,
        )

    def __getattr__(self, name: str) -> Any:
        return getattr(self._pool, name)


class _ResolverConnectionProxy:
    def __init__(
        self,
        connection: Any,
        started: asyncio.Event,
        release: asyncio.Event | None,
        failure: Exception | None,
    ) -> None:
        self._connection = connection
        self._started = started
        self._release = release
        self._failure = failure

    async def fetchrow(
        self,
        query: str,
        *args: object,
        **kwargs: object,
    ) -> Any:
        normalized = " ".join(query.upper().split())
        if normalized.startswith("SELECT U.ID"):
            self._started.set()
            if self._failure is not None:
                raise self._failure
            if self._release is not None:
                await asyncio.wait_for(self._release.wait(), timeout=10)
        return await self._connection.fetchrow(query, *args, **kwargs)

    def __getattr__(self, name: str) -> Any:
        return getattr(self._connection, name)


class _ResolverAcquireProxy:
    def __init__(
        self,
        acquisition: Any,
        started: asyncio.Event,
        release: asyncio.Event | None,
        failure: Exception | None,
    ) -> None:
        self._acquisition = acquisition
        self._started = started
        self._release = release
        self._failure = failure

    async def __aenter__(self) -> _ResolverConnectionProxy:
        connection = await self._acquisition.__aenter__()
        return _ResolverConnectionProxy(
            connection,
            self._started,
            self._release,
            self._failure,
        )

    async def __aexit__(self, *exc_info: object) -> object:
        return await self._acquisition.__aexit__(*exc_info)


class _ResolverPoolProxy:
    def __init__(
        self,
        pool: Any,
        started: asyncio.Event,
        release: asyncio.Event | None = None,
        failure: Exception | None = None,
    ) -> None:
        self._pool = pool
        self._started = started
        self._release = release
        self._failure = failure

    def acquire(self) -> _ResolverAcquireProxy:
        return _ResolverAcquireProxy(
            self._pool.acquire(),
            self._started,
            self._release,
            self._failure,
        )

    def __getattr__(self, name: str) -> Any:
        return getattr(self._pool, name)


class _PureAsyncContext:
    def __init__(self, value: Any = None) -> None:
        self._value = value

    async def __aenter__(self) -> Any:
        return self._value

    async def __aexit__(self, *exc_info: object) -> None:
        return None


class _PureTicketConnection:
    def transaction(self, *args: object, **kwargs: object) -> _PureAsyncContext:
        return _PureAsyncContext()

    async def execute(
        self,
        _query: str,
        *_args: object,
        **_kwargs: object,
    ) -> str:
        return "DELETE 0"

    async def fetchrow(
        self,
        _query: str,
        *_args: object,
        **_kwargs: object,
    ) -> dict[str, object]:
        return {
            "campaign_id": "synthetic-campaign",
            "user_id": "synthetic-user",
            "credential_kind": "bearer",
            "bearer_subject": "synthetic-subject",
            "bearer_jti": "synthetic-jti",
            "bearer_expires_at": datetime.now(UTC)
            + timedelta(minutes=1),
            "bearer_family_id": _test_family_id("synthetic-user"),
            "bearer_auth_epoch": 1,
            "api_key_id": None,
            "required_scope": None,
        }


class _PureTicketPool:
    def __init__(self) -> None:
        self._connection = _PureTicketConnection()

    def acquire(self) -> _PureAsyncContext:
        return _PureAsyncContext(self._connection)


class _TransactionExitBarrier:
    def __init__(
        self,
        transaction: Any,
        exit_reached: asyncio.Event,
        release_exit: asyncio.Event,
    ) -> None:
        self._transaction = transaction
        self._exit_reached = exit_reached
        self._release_exit = release_exit

    async def __aenter__(self) -> Any:
        return await self._transaction.__aenter__()

    async def __aexit__(self, *exc_info: object) -> object:
        self._exit_reached.set()
        await asyncio.wait_for(self._release_exit.wait(), timeout=10)
        return await self._transaction.__aexit__(*exc_info)


class _BarrierConnection:
    def __init__(
        self,
        connection: Any,
        exit_reached: asyncio.Event,
        release_exit: asyncio.Event,
        backend_pids: list[int],
    ) -> None:
        self._connection = connection
        self._exit_reached = exit_reached
        self._release_exit = release_exit
        self._backend_pids = backend_pids

    def transaction(self, *args: object, **kwargs: object) -> Any:
        return _TransactionExitBarrier(
            self._connection.transaction(*args, **kwargs),
            self._exit_reached,
            self._release_exit,
        )

    async def fetchrow(
        self,
        query: str,
        *args: object,
        **kwargs: object,
    ) -> Any:
        normalized = " ".join(query.upper().split())
        if normalized.startswith("UPDATE WEBSOCKET_TICKETS"):
            self._backend_pids.append(
                int(await self._connection.fetchval("SELECT pg_backend_pid()"))
            )
        return await self._connection.fetchrow(query, *args, **kwargs)

    def __getattr__(self, name: str) -> Any:
        return getattr(self._connection, name)


class _BarrierAcquire:
    def __init__(
        self,
        acquisition: Any,
        exit_reached: asyncio.Event,
        release_exit: asyncio.Event,
        backend_pids: list[int],
    ) -> None:
        self._acquisition = acquisition
        self._exit_reached = exit_reached
        self._release_exit = release_exit
        self._backend_pids = backend_pids

    async def __aenter__(self) -> _BarrierConnection:
        connection = await self._acquisition.__aenter__()
        return _BarrierConnection(
            connection,
            self._exit_reached,
            self._release_exit,
            self._backend_pids,
        )

    async def __aexit__(self, *exc_info: object) -> object:
        return await self._acquisition.__aexit__(*exc_info)


class _BarrierPool:
    def __init__(
        self,
        pool: Any,
        exit_reached: asyncio.Event,
        release_exit: asyncio.Event,
        backend_pids: list[int],
    ) -> None:
        self._pool = pool
        self._exit_reached = exit_reached
        self._release_exit = release_exit
        self._backend_pids = backend_pids

    def acquire(self) -> _BarrierAcquire:
        return _BarrierAcquire(
            self._pool.acquire(),
            self._exit_reached,
            self._release_exit,
            self._backend_pids,
        )

    def __getattr__(self, name: str) -> Any:
        return getattr(self._pool, name)


@pytest.mark.asyncio
async def test_consume_sql_tripwire_exercises_production_without_postgres() -> None:
    statement_classes: list[str] = []
    database = PostgresDatabase("synthetic")
    database._pool = _PoolProxy(_PureTicketPool(), statement_classes)
    consumed = await database.consume_websocket_ticket(
        generate_websocket_ticket(),
        "synthetic-campaign",
    )
    _require_fixed(consumed is not None, "pure consume tripwire did not execute")
    _require_fixed(
        tuple(statement_classes) == ("CONDITIONAL UPDATE RETURNING",),
        "production consume SQL lost its exact CAS/return contract",
    )


@pytest.mark.asyncio
async def test_api_key_issue_sql_persists_required_scope_without_postgres() -> None:
    statement_classes: list[str] = []
    database = PostgresDatabase("synthetic")
    database._pool = _PoolProxy(_PureTicketPool(), statement_classes)
    issued = await database.issue_websocket_ticket(
        "synthetic-campaign",
        ApiKeyTicketSource(
            user_id="synthetic-user",
            api_key_id="synthetic-key",
        ),
    )
    _require_fixed(issued is not None, "pure API-key issue tripwire did not execute")
    _require_fixed(
        tuple(statement_classes) == ("API KEY INSERT WITH REQUIRED SCOPE",),
        "production API-key ticket SQL lost its required scope",
    )


@pytest.mark.asyncio
async def test_postgres_consume_uses_one_conditional_update_returning() -> None:
    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, _other_id, _key_id = (
            await _seed_identity(database)
        )
        issued = await database.issue_websocket_ticket(
            campaign_id,
            _bearer_source(user_id, username),
        )
        _require_fixed(issued is not None, "ticket setup failed")
        raw_ticket = (issued or ("", 0))[0]
        original_pool = database._pool
        statement_classes: list[str] = []
        database._pool = _PoolProxy(original_pool, statement_classes)
        try:
            consumed = await database.consume_websocket_ticket(
                raw_ticket,
                campaign_id,
            )
        finally:
            database._pool = original_pool

        _require_fixed(consumed is not None, "instrumented consume failed")
        _require_fixed(
            tuple(statement_classes) == ("CONDITIONAL UPDATE RETURNING",),
            "PostgreSQL consume lost its conditional UPDATE RETURNING",
        )


@pytest.mark.asyncio
async def test_postgres_resolver_errors_and_cancellation_release_pool() -> None:
    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, _other_id, key_id = (
            await _seed_identity(database)
        )
        bearer_issued = await database.issue_websocket_ticket(
            campaign_id,
            _bearer_source(user_id, username),
        )
        api_issued = await database.issue_websocket_ticket(
            campaign_id,
            ApiKeyTicketSource(user_id=user_id, api_key_id=key_id),
        )
        _require_fixed(
            bearer_issued is not None and api_issued is not None,
            "resolver failure setup failed",
        )
        bearer = await database.consume_websocket_ticket(
            (bearer_issued or ("", 0))[0],
            campaign_id,
        )
        api_key = await database.consume_websocket_ticket(
            (api_issued or ("", 0))[0],
            campaign_id,
        )
        _require_fixed(
            bearer is not None and api_key is not None,
            "resolver failure handles were not created",
        )
        if bearer is None or api_key is None:
            return

        original_pool = database._pool
        for handle in (bearer, api_key):
            started = asyncio.Event()
            database._pool = _ResolverPoolProxy(
                original_pool,
                started,
                failure=RuntimeError(),
            )
            failure_type: type[BaseException] | None = None
            try:
                await database.resolve_websocket_ticket_principal(handle)
            except Exception as exc:
                failure_type = type(exc)
            finally:
                database._pool = original_pool
            _require_fixed(
                started.is_set() and failure_type is RuntimeError,
                "resolver database error did not propagate safely",
            )

            cancel_started = asyncio.Event()
            release = asyncio.Event()
            database._pool = _ResolverPoolProxy(
                original_pool,
                cancel_started,
                release=release,
            )
            task = asyncio.create_task(
                database.resolve_websocket_ticket_principal(handle)
            )
            cancelled = False
            try:
                await asyncio.wait_for(cancel_started.wait(), timeout=10)
                task.cancel()
                try:
                    await asyncio.wait_for(task, timeout=10)
                except asyncio.CancelledError:
                    cancelled = True
            finally:
                release.set()
                database._pool = original_pool
                if not task.done():
                    task.cancel()
                await asyncio.wait_for(
                    asyncio.gather(task, return_exceptions=True),
                    timeout=10,
                )
            recovered = await database.resolve_websocket_ticket_principal(handle)
            async with original_pool.acquire() as connection:
                reusable = await connection.fetchval("SELECT 1")
            _require_fixed(
                cancelled and recovered is not None and reusable == 1,
                "resolver cancellation did not release and recover the pool",
            )


@pytest.mark.asyncio
async def test_postgres_issue_returns_only_after_transaction_exit() -> None:
    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, _other_id, _key_id = (
            await _seed_identity(database)
        )
        original_pool = database._pool
        exit_reached = asyncio.Event()
        release_exit = asyncio.Event()
        task: asyncio.Task[tuple[str, int] | None] | None = None
        database._pool = _BarrierPool(
            original_pool,
            exit_reached,
            release_exit,
            [],
        )
        try:
            task = asyncio.create_task(
                database.issue_websocket_ticket(
                    campaign_id,
                    _bearer_source(user_id, username),
                )
            )
            await asyncio.wait_for(exit_reached.wait(), timeout=10)
            returned_early = task.done()
            release_exit.set()
            issued = await asyncio.wait_for(task, timeout=10)
        finally:
            release_exit.set()
            database._pool = original_pool
            if task is not None and not task.done():
                task.cancel()
                await asyncio.gather(task, return_exceptions=True)

        _require_fixed(
            not returned_early,
            "ticket was returned before transaction exit",
        )
        _require_fixed(issued is not None, "barrier issuance did not complete")


@pytest.mark.asyncio
async def test_postgres_revision_0007_ownership_and_0008_compatibility(
) -> None:
    migration_catalog: dict[str, object] | None = None
    async with _postgres_harness(initialize_runtime=False) as harness:
        import asyncpg

        config = _postgres_test_config()

        async def _connect() -> Any:
            return await _bounded_operation(
                "migration-catalog-connect",
                asyncpg.connect(
                    host=config["host"],
                    port=config["port"],
                    user=config["user"],
                    database=harness.database_name,
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                ),
            )

        connection = await _connect()
        try:
            initial_counts = await _bounded_operation(
                "migration-empty-catalog",
                connection.fetchrow(
                    """
                    SELECT
                        (
                            SELECT COUNT(*)
                            FROM information_schema.tables
                            WHERE table_schema=current_schema()
                        ) AS tables,
                        (
                            SELECT to_regclass('alembic_version') IS NULL
                        ) AS version_absent
                    """
                ),
            )
        finally:
            await _attempt_cleanup(
                "migration-empty-close",
                connection.close,
                [],
            )
        initially_empty = (
            initial_counts is not None
            and int(initial_counts["tables"]) == 0
            and bool(initial_counts["version_absent"])
        )
        _require_fixed(
            initially_empty,
            "PostgreSQL migration target was not genuinely empty",
        )

        _run_ticket_migration(harness.database_name, "upgrade", "0006")
        connection = await _connect()
        try:
            parent_revision = await connection.fetchval(
                "SELECT version_num FROM alembic_version"
            )
            seed_user = f"migration-user-{uuid4().hex}"
            seed_campaign = f"migration-campaign-{uuid4().hex}"
            seed_key = f"migration-key-{uuid4().hex}"
            await connection.execute(
                """
                INSERT INTO users(id, username, hashed_password, role)
                VALUES($1, $2, $3, 'operator')
                """,
                seed_user,
                f"migration-name-{uuid4().hex}",
                "synthetic-hash",
            )
            await connection.execute(
                """
                INSERT INTO campaigns(id, name, operator)
                VALUES($1, $2, $3)
                """,
                seed_campaign,
                "Migration campaign",
                "migration-operator",
            )
            await connection.execute(
                """
                INSERT INTO api_keys(
                    id, user_id, name, key_hash, key_prefix, scopes
                )
                VALUES($1, $2, $3, $4, $5, 'read')
                """,
                seed_key,
                seed_user,
                "migration-key",
                "synthetic-key-hash",
                "synthetic-key",
            )
        finally:
            await _attempt_cleanup(
                "migration-parent-close",
                connection.close,
                [],
            )
        _require_fixed(
            parent_revision == "0006",
            "PostgreSQL migration did not reach revision 0006",
        )

        _run_ticket_migration(harness.database_name, "upgrade", "0007")
        connection = await _connect()
        try:
            migration_catalog = await _ticket_catalog_fingerprint(connection)
            upgraded_state = await connection.fetchrow(
                """
                SELECT
                    (SELECT version_num FROM alembic_version) AS revision,
                    (SELECT COUNT(*) FROM users WHERE id=$1) AS users,
                    (SELECT COUNT(*) FROM campaigns WHERE id=$2) AS campaigns,
                    (SELECT COUNT(*) FROM api_keys WHERE id=$3) AS api_keys
                """,
                seed_user,
                seed_campaign,
                seed_key,
            )
        finally:
            await _attempt_cleanup(
                "migration-upgrade-close",
                connection.close,
                [],
            )
        upgraded = (
            upgraded_state is not None
            and upgraded_state["revision"] == "0007"
            and all(
                int(upgraded_state[name]) == 1
                for name in ("users", "campaigns", "api_keys")
            )
            and migration_catalog is not None
            and _ticket_catalog_matches_revision_0007_contract(
                migration_catalog
            )
        )
        _require_fixed(
            upgraded,
            "PostgreSQL ticket migration contract changed",
        )

        _run_ticket_migration(harness.database_name, "downgrade", "0006")
        connection = await _connect()
        try:
            downgraded_state = await connection.fetchrow(
                """
                SELECT
                    to_regclass('websocket_tickets') IS NULL AS ticket_absent,
                    (SELECT version_num FROM alembic_version) AS revision,
                    (SELECT COUNT(*) FROM users WHERE id=$1) AS users,
                    (SELECT COUNT(*) FROM campaigns WHERE id=$2) AS campaigns,
                    (SELECT COUNT(*) FROM api_keys WHERE id=$3) AS api_keys
                """,
                seed_user,
                seed_campaign,
                seed_key,
            )
        finally:
            await _attempt_cleanup(
                "migration-downgrade-close",
                connection.close,
                [],
            )
        downgraded = (
            downgraded_state is not None
            and bool(downgraded_state["ticket_absent"])
            and downgraded_state["revision"] == "0006"
            and all(
                int(downgraded_state[name]) == 1
                for name in ("users", "campaigns", "api_keys")
            )
        )
        _require_fixed(downgraded, "PostgreSQL ticket downgrade contract changed")

        _run_ticket_migration(harness.database_name, "upgrade", "0007")
        connection = await _connect()
        try:
            reupgraded_catalog = await _ticket_catalog_fingerprint(connection)
            final_state = await connection.fetchrow(
                """
                SELECT
                    (SELECT version_num FROM alembic_version) AS revision,
                    (SELECT COUNT(*) FROM users WHERE id=$1) AS users,
                    (SELECT COUNT(*) FROM campaigns WHERE id=$2) AS campaigns,
                    (SELECT COUNT(*) FROM api_keys WHERE id=$3) AS api_keys
                """,
                seed_user,
                seed_campaign,
                seed_key,
            )
        finally:
            await _attempt_cleanup(
                "migration-reupgrade-close",
                connection.close,
                [],
            )
        final_state_is_exact = (
            final_state is not None
            and final_state["revision"] == "0007"
            and all(
                int(final_state[name]) == 1
                for name in ("users", "campaigns", "api_keys")
            )
            and _ticket_catalog_matches_revision_0007_contract(
                reupgraded_catalog
            )
        )
        _require_fixed(
            final_state_is_exact,
            "PostgreSQL ticket re-upgrade contract changed",
        )

        connection = await _connect()
        try:
            await connection.execute(
                """
                INSERT INTO websocket_tickets(
                    ticket_hash, campaign_id, user_id, credential_kind,
                    api_key_id, required_scope, created_at, expires_at
                )
                VALUES(
                    $1, $2, $3, 'api_key', $4, 'read',
                    now(), now() + interval '30 seconds'
                )
                """,
                "e" * 64,
                seed_campaign,
                seed_user,
                seed_key,
            )
            ticket_catalog_before_0008 = (
                await _ticket_catalog_fingerprint(connection)
            )
            ticket_rows_before_0008 = await connection.fetch(
                """
                SELECT ticket_hash, campaign_id, user_id, credential_kind,
                       bearer_subject, bearer_jti, bearer_expires_at,
                       api_key_id, required_scope, created_at, expires_at,
                       consumed_at
                FROM websocket_tickets
                ORDER BY ticket_hash
                """
            )
        finally:
            await _attempt_cleanup(
                "migration-pre-0008-close",
                connection.close,
                [],
            )

        _run_ticket_migration(harness.database_name, "upgrade", "0008")
        connection = await _connect()
        try:
            migration_catalog = await _ticket_catalog_fingerprint(connection)
            ticket_rows_after_0008 = await connection.fetch(
                """
                SELECT ticket_hash, campaign_id, user_id, credential_kind,
                       bearer_subject, bearer_jti, bearer_expires_at,
                       api_key_id, required_scope, created_at, expires_at,
                       consumed_at
                FROM websocket_tickets
                ORDER BY ticket_hash
                """
            )
            head_state = await connection.fetchrow(
                """
                SELECT
                    (SELECT version_num FROM alembic_version) AS revision,
                    (SELECT COUNT(*) FROM users WHERE id=$1) AS users,
                    (SELECT COUNT(*) FROM campaigns WHERE id=$2) AS campaigns,
                    (SELECT COUNT(*) FROM api_keys WHERE id=$3) AS api_keys
                """,
                seed_user,
                seed_campaign,
                seed_key,
            )
        finally:
            await _attempt_cleanup(
                "migration-0008-close",
                connection.close,
                [],
            )
        revision_0008_is_compatible = (
            head_state is not None
            and head_state["revision"] == "0008"
            and all(
                int(head_state[name]) == 1
                for name in ("users", "campaigns", "api_keys")
            )
            and _ticket_catalog_matches_revision_0007_contract(
                migration_catalog
            )
            and migration_catalog == ticket_catalog_before_0008
            and tuple(ticket_rows_after_0008)
            == tuple(ticket_rows_before_0008)
        )
        _require_fixed(
            revision_0008_is_compatible,
            "revision 0008 changed the revision 0007 ticket contract",
        )

        _run_ticket_migration(harness.database_name, "upgrade", "0009")
        connection = await _connect()
        try:
            await PostgresDatabase._validate_websocket_ticket_schema(connection)
            migration_catalog = await _ticket_catalog_fingerprint(connection)
            ticket_rows_after_0009 = await connection.fetch(
                """
                SELECT ticket_hash, campaign_id, user_id, credential_kind,
                       bearer_subject, bearer_jti, bearer_expires_at,
                       api_key_id, required_scope, created_at, expires_at,
                       consumed_at
                FROM websocket_tickets
                ORDER BY ticket_hash
                """
            )
            current_state = await connection.fetchrow(
                """
                SELECT
                    (SELECT version_num FROM alembic_version) AS revision,
                    (SELECT COUNT(*) FROM users WHERE id=$1) AS users,
                    (SELECT COUNT(*) FROM campaigns WHERE id=$2) AS campaigns,
                    (SELECT COUNT(*) FROM api_keys WHERE id=$3) AS api_keys
                """,
                seed_user,
                seed_campaign,
                seed_key,
            )
        finally:
            await _attempt_cleanup(
                "migration-0009-close",
                connection.close,
                [],
            )
        revision_0009_is_current = (
            current_state is not None
            and current_state["revision"] == "0009"
            and all(
                int(current_state[name]) == 1
                for name in ("users", "campaigns", "api_keys")
            )
            and _ticket_catalog_matches_fixed_contract(migration_catalog)
            and tuple(ticket_rows_after_0009)
            == tuple(ticket_rows_before_0008)
        )
        _require_fixed(
            revision_0009_is_current,
            "revision 0009 ticket contract changed",
        )

    async with _postgres_harness() as runtime_harness:
        async with runtime_harness.database._pool.acquire() as connection:
            runtime_catalog = await _ticket_catalog_fingerprint(connection)
    parity = (
        migration_catalog is not None
        and _ticket_catalog_matches_fixed_contract(runtime_catalog)
        and _ticket_catalog_matches_fixed_contract(migration_catalog)
    )
    _require_fixed(
        parity,
        "runtime and migration ticket catalogs diverged",
    )


@pytest.mark.asyncio
async def test_postgres_partial_ticket_schema_fails_clearly() -> None:
    from ares.db.postgres import _PostgresStartupDiagnosticError

    async with _postgres_harness() as harness:
        database = harness.database
        async with database._pool.acquire() as connection:
            await connection.execute("DROP TABLE websocket_tickets")
            await connection.execute(
                """
                CREATE TABLE websocket_tickets(
                    ticket_hash TEXT PRIMARY KEY
                )
                """
            )
            incompatible = await _ticket_catalog_fingerprint(connection)
        failure_type: type[BaseException] | None = None
        failure_message_is_fixed = False
        try:
            await database._init_schema()
        except Exception as exc:
            failure_type = type(exc)
            failure_message_is_fixed = (
                str(exc) == "Incompatible WebSocket ticket schema"
            )
        async with database._pool.acquire() as connection:
            after_failure = await _ticket_catalog_fingerprint(connection)
            reusable = await connection.fetchval("SELECT 1")
        _require_fixed(
            failure_type is _PostgresStartupDiagnosticError,
            "partial PostgreSQL ticket schema failure type changed",
        )
        _require_fixed(
            failure_message_is_fixed,
            "partial PostgreSQL ticket schema failure message changed",
        )
        _require_fixed(
            incompatible == after_failure,
            "partial PostgreSQL ticket schema mutated after rejection",
        )
        _require_fixed(
            reusable == 1,
            "partial PostgreSQL ticket schema connection was not reusable",
        )


@pytest.mark.parametrize(
    "mutation",
    [
        pytest.param(
            """
            CREATE INDEX idx_ws_ticket_unexpected_expression
            ON websocket_tickets ((lower(ticket_hash)))
            """,
            id="expression-index",
        ),
        pytest.param(
            """
            DROP INDEX idx_ws_tickets_expires;
            CREATE INDEX idx_ws_tickets_expires
            ON websocket_tickets(expires_at DESC)
            """,
            id="index-order",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            DROP CONSTRAINT ck_ws_ticket_created_finite;
            ALTER TABLE websocket_tickets
            ADD CONSTRAINT ck_ws_ticket_created_finite
            CHECK (isfinite(created_at)) NOT VALID
            """,
            id="not-valid-check",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            DROP CONSTRAINT fk_ws_ticket_campaign;
            ALTER TABLE websocket_tickets
            ADD CONSTRAINT fk_ws_ticket_campaign
            FOREIGN KEY(campaign_id) REFERENCES campaigns(id)
            ON UPDATE CASCADE ON DELETE CASCADE
            """,
            id="foreign-key-action",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            ALTER COLUMN required_scope SET DEFAULT 'read'
            """,
            id="column-default",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            ALTER COLUMN created_at SET DEFAULT now()
            """,
            id="timestamp-column-default",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            DROP CONSTRAINT ck_ws_ticket_created_at;
            ALTER TABLE websocket_tickets
            ADD CONSTRAINT ck_ws_ticket_created_at
            CHECK (created_at <= expires_at)
            """,
            id="same-name-check-body",
        ),
        pytest.param(
            """
            DROP INDEX idx_ws_tickets_campaign;
            CREATE INDEX idx_ws_tickets_campaign
            ON websocket_tickets(user_id)
            """,
            id="same-name-index-column",
        ),
        pytest.param(
            """
            DROP INDEX idx_ws_tickets_campaign;
            CREATE UNIQUE INDEX idx_ws_tickets_campaign
            ON websocket_tickets(campaign_id)
            """,
            id="same-name-index-uniqueness",
        ),
        pytest.param(
            """
            CREATE INDEX idx_ws_ticket_duplicate_campaign
            ON websocket_tickets(campaign_id)
            """,
            id="duplicate-equivalent-index",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            DROP CONSTRAINT websocket_tickets_pkey;
            ALTER TABLE websocket_tickets
            ADD CONSTRAINT websocket_tickets_pkey PRIMARY KEY(campaign_id)
            """,
            id="wrong-primary-key",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            DROP CONSTRAINT fk_ws_ticket_campaign;
            ALTER TABLE websocket_tickets
            ADD CONSTRAINT fk_ws_ticket_campaign
            FOREIGN KEY(campaign_id) REFERENCES users(id)
            ON DELETE CASCADE
            """,
            id="wrong-foreign-key-target",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            DROP CONSTRAINT ck_ws_ticket_created_finite
            """,
            id="missing-constraint",
        ),
        pytest.param(
            """
            DROP INDEX idx_ws_tickets_campaign
            """,
            id="missing-index",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            ADD CONSTRAINT ck_ws_ticket_unexpected CHECK (true)
            """,
            id="extra-constraint",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            ALTER COLUMN bearer_expires_at
            TYPE timestamp without time zone
            USING bearer_expires_at AT TIME ZONE 'UTC'
            """,
            id="timestamp-type",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            ALTER COLUMN created_at DROP NOT NULL
            """,
            id="column-nullability",
        ),
        pytest.param(
            """
            ALTER TABLE websocket_tickets
            ALTER COLUMN bearer_subject TYPE text COLLATE "C"
            """,
            id="column-collation",
        ),
    ],
)
@pytest.mark.asyncio
async def test_postgres_validator_rejects_near_compatible_catalogs(
    mutation: str,
) -> None:
    from ares.db.postgres import _PostgresStartupDiagnosticError

    async with _postgres_harness() as harness:
        database = harness.database
        async with database._pool.acquire() as connection:
            await connection.execute(mutation)
            incompatible = await _ticket_catalog_fingerprint(connection)
        failure_type: type[BaseException] | None = None
        fixed_message = False
        try:
            await database._init_schema()
        except Exception as exc:
            failure_type = type(exc)
            fixed_message = (
                str(exc) == "Incompatible WebSocket ticket schema"
            )
        async with database._pool.acquire() as connection:
            after_failure = await _ticket_catalog_fingerprint(connection)
            reusable = await connection.fetchval("SELECT 1")
        _require_fixed(
            failure_type is _PostgresStartupDiagnosticError
            and fixed_message
            and incompatible == after_failure
            and reusable == 1,
            "near-compatible ticket startup prevalidation failed",
        )


_POSTGRES_RELATION_METADATA_MUTATIONS = (
    "unlogged",
    "wrong-kind",
    "partitioned",
    "inheritance-child",
    "inheritance-parent",
    "rls",
    "force-rls",
    "policy",
    "consumption-trigger",
    "rewrite-rule",
    "decoy-schema",
    "wrong-opclass",
    "wrong-relation-binding",
)


async def _apply_ticket_metadata_mutation(
    connection: Any,
    mutation: str,
) -> None:
    if mutation == "unlogged":
        await connection.execute(
            "ALTER TABLE websocket_tickets SET UNLOGGED"
        )
    elif mutation == "wrong-kind":
        await connection.execute(
            "ALTER TABLE websocket_tickets RENAME TO websocket_tickets_saved"
        )
        await connection.execute(
            """
            CREATE VIEW websocket_tickets AS
            SELECT * FROM websocket_tickets_saved
            """
        )
    elif mutation == "partitioned":
        await connection.execute(
            "ALTER TABLE websocket_tickets RENAME TO websocket_tickets_saved"
        )
        await connection.execute(
            """
            CREATE TABLE websocket_tickets(ticket_hash TEXT NOT NULL)
            PARTITION BY HASH(ticket_hash)
            """
        )
        await connection.execute(
            """
            CREATE TABLE websocket_tickets_partition
            PARTITION OF websocket_tickets
            FOR VALUES WITH (MODULUS 1, REMAINDER 0)
            """
        )
    elif mutation == "inheritance-child":
        await connection.execute(
            "CREATE TABLE ws_ticket_inheritance_parent()"
        )
        await connection.execute(
            """
            ALTER TABLE websocket_tickets
            INHERIT ws_ticket_inheritance_parent
            """
        )
    elif mutation == "inheritance-parent":
        await connection.execute(
            """
            CREATE TABLE ws_ticket_inheritance_child()
            INHERITS (websocket_tickets)
            """
        )
    elif mutation == "rls":
        await connection.execute(
            "ALTER TABLE websocket_tickets ENABLE ROW LEVEL SECURITY"
        )
    elif mutation == "force-rls":
        await connection.execute(
            "ALTER TABLE websocket_tickets ENABLE ROW LEVEL SECURITY"
        )
        await connection.execute(
            "ALTER TABLE websocket_tickets FORCE ROW LEVEL SECURITY"
        )
    elif mutation == "policy":
        await connection.execute(
            """
            CREATE POLICY ws_ticket_policy
            ON websocket_tickets
            USING (true)
            WITH CHECK (true)
            """
        )
    elif mutation == "consumption-trigger":
        await connection.execute(
            """
            CREATE FUNCTION ws_ticket_preserve_unconsumed()
            RETURNS trigger
            LANGUAGE plpgsql
            AS $function$
            BEGIN
                NEW.consumed_at := NULL;
                RETURN NEW;
            END
            $function$
            """
        )
        await connection.execute(
            """
            CREATE TRIGGER ws_ticket_preserve_unconsumed
            BEFORE UPDATE OF consumed_at ON websocket_tickets
            FOR EACH ROW
            WHEN (NEW.consumed_at IS NOT NULL)
            EXECUTE FUNCTION ws_ticket_preserve_unconsumed()
            """
        )
    elif mutation == "rewrite-rule":
        await connection.execute(
            """
            CREATE RULE ws_ticket_update_rule AS
            ON UPDATE TO websocket_tickets
            DO ALSO NOTHING
            """
        )
    elif mutation == "decoy-schema":
        await connection.execute(
            "DROP INDEX idx_ws_tickets_campaign"
        )
        await connection.execute("CREATE SCHEMA ws_ticket_decoy")
        await connection.execute(
            """
            CREATE TABLE ws_ticket_decoy.websocket_tickets(
                campaign_id TEXT
            )
            """
        )
        await connection.execute(
            """
            CREATE INDEX idx_ws_tickets_campaign
            ON ws_ticket_decoy.websocket_tickets(campaign_id)
            """
        )
    elif mutation == "wrong-opclass":
        await connection.execute(
            "DROP INDEX idx_ws_tickets_campaign"
        )
        await connection.execute(
            """
            CREATE INDEX idx_ws_tickets_campaign
            ON websocket_tickets(campaign_id text_pattern_ops)
            """
        )
    elif mutation == "wrong-relation-binding":
        await connection.execute(
            "DROP INDEX idx_ws_tickets_campaign"
        )
        await connection.execute(
            "CREATE TABLE ws_ticket_index_decoy(campaign_id TEXT)"
        )
        await connection.execute(
            """
            CREATE INDEX idx_ws_tickets_campaign
            ON ws_ticket_index_decoy(campaign_id)
            """
        )
    else:
        raise RuntimeError("Unknown PostgreSQL ticket metadata mutation")


async def _remove_ticket_metadata_mutation(
    connection: Any,
    mutation: str,
) -> None:
    if mutation == "unlogged":
        await connection.execute(
            "ALTER TABLE websocket_tickets SET LOGGED"
        )
    elif mutation == "wrong-kind":
        await connection.execute("DROP VIEW websocket_tickets")
        await connection.execute(
            "ALTER TABLE websocket_tickets_saved RENAME TO websocket_tickets"
        )
    elif mutation == "partitioned":
        await connection.execute(
            "DROP TABLE websocket_tickets CASCADE"
        )
        await connection.execute(
            "ALTER TABLE websocket_tickets_saved RENAME TO websocket_tickets"
        )
    elif mutation == "inheritance-child":
        await connection.execute(
            """
            ALTER TABLE websocket_tickets
            NO INHERIT ws_ticket_inheritance_parent
            """
        )
        await connection.execute(
            "DROP TABLE ws_ticket_inheritance_parent"
        )
    elif mutation == "inheritance-parent":
        await connection.execute(
            "DROP TABLE ws_ticket_inheritance_child"
        )
    elif mutation == "rls":
        await connection.execute(
            "ALTER TABLE websocket_tickets DISABLE ROW LEVEL SECURITY"
        )
    elif mutation == "force-rls":
        await connection.execute(
            "ALTER TABLE websocket_tickets NO FORCE ROW LEVEL SECURITY"
        )
        await connection.execute(
            "ALTER TABLE websocket_tickets DISABLE ROW LEVEL SECURITY"
        )
    elif mutation == "policy":
        await connection.execute(
            "DROP POLICY ws_ticket_policy ON websocket_tickets"
        )
    elif mutation == "consumption-trigger":
        await connection.execute(
            """
            DROP TRIGGER ws_ticket_preserve_unconsumed
            ON websocket_tickets
            """
        )
        await connection.execute(
            "DROP FUNCTION ws_ticket_preserve_unconsumed()"
        )
    elif mutation == "rewrite-rule":
        await connection.execute(
            "DROP RULE ws_ticket_update_rule ON websocket_tickets"
        )
    elif mutation == "decoy-schema":
        await connection.execute(
            "DROP SCHEMA ws_ticket_decoy CASCADE"
        )
        await connection.execute(
            """
            CREATE INDEX idx_ws_tickets_campaign
            ON websocket_tickets(campaign_id)
            """
        )
    elif mutation == "wrong-opclass":
        await connection.execute(
            "DROP INDEX idx_ws_tickets_campaign"
        )
        await connection.execute(
            """
            CREATE INDEX idx_ws_tickets_campaign
            ON websocket_tickets(campaign_id)
            """
        )
    elif mutation == "wrong-relation-binding":
        await connection.execute(
            "DROP TABLE ws_ticket_index_decoy"
        )
        await connection.execute(
            """
            CREATE INDEX idx_ws_tickets_campaign
            ON websocket_tickets(campaign_id)
            """
        )
    else:
        raise RuntimeError("Unknown PostgreSQL ticket metadata mutation")


async def _ticket_metadata_mutation_exists(
    connection: Any,
    mutation: str,
) -> bool:
    fingerprint = await _ticket_catalog_fingerprint(connection)
    relation = fingerprint.get("relation")
    if not isinstance(relation, tuple):
        return False
    if mutation == "unlogged":
        return relation[1] == "u"
    if mutation == "wrong-kind":
        return relation[0] == "v"
    if mutation == "partitioned":
        return relation[0] == "p" and relation[6] == 1
    if mutation == "inheritance-child":
        return relation[5] == 1
    if mutation == "inheritance-parent":
        return relation[6] == 1
    if mutation == "rls":
        return relation[3] is True and relation[4] is False
    if mutation == "force-rls":
        return relation[3] is True and relation[4] is True
    if mutation == "policy":
        return relation[7] == 1
    if mutation == "consumption-trigger":
        return relation[8] == 1
    if mutation == "rewrite-rule":
        return relation[9] == 1
    if mutation == "wrong-opclass":
        return any(
            identity[0] == "idx_ws_tickets_campaign"
            and identity[4] == ("text_pattern_ops",)
            and identity[5] == ("pg_catalog",)
            for identity in fingerprint.get("index_identities", ())
        )
    if mutation == "decoy-schema":
        return bool(
            await connection.fetchval(
                """
                SELECT
                    NOT EXISTS(
                        SELECT 1
                        FROM pg_index AS ind
                        JOIN pg_class AS rel ON rel.oid=ind.indrelid
                        JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
                        JOIN pg_class AS idx ON idx.oid=ind.indexrelid
                        WHERE nsp.nspname=current_schema()
                          AND rel.relname='websocket_tickets'
                          AND idx.relname='idx_ws_tickets_campaign'
                    )
                    AND EXISTS(
                        SELECT 1
                        FROM pg_index AS ind
                        JOIN pg_class AS rel ON rel.oid=ind.indrelid
                        JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
                        JOIN pg_class AS idx ON idx.oid=ind.indexrelid
                        WHERE nsp.nspname='ws_ticket_decoy'
                          AND rel.relname='websocket_tickets'
                          AND idx.relname='idx_ws_tickets_campaign'
                    )
                """
            )
        )
    if mutation == "wrong-relation-binding":
        return bool(
            await connection.fetchval(
                """
                SELECT
                    NOT EXISTS(
                        SELECT 1
                        FROM pg_index AS ind
                        JOIN pg_class AS rel ON rel.oid=ind.indrelid
                        JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
                        JOIN pg_class AS idx ON idx.oid=ind.indexrelid
                        WHERE nsp.nspname=current_schema()
                          AND rel.relname='websocket_tickets'
                          AND idx.relname='idx_ws_tickets_campaign'
                    )
                    AND EXISTS(
                        SELECT 1
                        FROM pg_index AS ind
                        JOIN pg_class AS rel ON rel.oid=ind.indrelid
                        JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
                        JOIN pg_class AS idx ON idx.oid=ind.indexrelid
                        WHERE nsp.nspname=current_schema()
                          AND rel.relname='ws_ticket_index_decoy'
                          AND idx.relname='idx_ws_tickets_campaign'
                    )
                """
            )
        )
    return False


async def _ticket_related_object_fingerprint(
    connection: Any,
) -> tuple[tuple[object, ...], ...]:
    rows = await connection.fetch(
        """
        SELECT nsp.nspname, rel.relname, rel.relkind,
               rel.relpersistence, rel.relispartition,
               rel.relrowsecurity, rel.relforcerowsecurity
        FROM pg_class AS rel
        JOIN pg_namespace AS nsp ON nsp.oid=rel.relnamespace
        WHERE (
                nsp.nspname=current_schema()
                AND (
                    rel.relname LIKE 'websocket_tickets%'
                    OR rel.relname LIKE 'idx_ws_tickets%'
                    OR rel.relname LIKE 'ws_ticket_%'
                )
              )
           OR nsp.nspname='ws_ticket_decoy'
        ORDER BY nsp.nspname, rel.relname
        """
    )
    return tuple(tuple(row) for row in rows)


async def _ticket_rows_for_metadata_mutation(
    connection: Any,
    mutation: str,
) -> tuple[tuple[object, ...], ...]:
    if mutation in {"wrong-kind", "partitioned"}:
        query = """
            SELECT ticket_hash, campaign_id, user_id, credential_kind,
                   bearer_subject, bearer_jti, bearer_expires_at,
                   api_key_id, required_scope, created_at, expires_at,
                   consumed_at
            FROM websocket_tickets_saved
            ORDER BY ticket_hash
        """
    else:
        query = """
            SELECT ticket_hash, campaign_id, user_id, credential_kind,
                   bearer_subject, bearer_jti, bearer_expires_at,
                   api_key_id, required_scope, created_at, expires_at,
                   consumed_at
            FROM websocket_tickets
            ORDER BY ticket_hash
        """
    rows = await connection.fetch(
        query
    )
    return tuple(tuple(row) for row in rows)


@pytest.mark.parametrize(
    "mutation",
    [
        pytest.param(value, id=value)
        for value in _POSTGRES_RELATION_METADATA_MUTATIONS
    ],
)
@pytest.mark.asyncio
async def test_postgres_rejects_security_altering_relation_metadata(
    mutation: str,
) -> None:
    from ares.db.postgres import (
        _PostgresManagedSchemaError,
        _PostgresStartupDiagnosticError,
    )

    async with _postgres_harness() as harness:
        database = harness.database
        user_id, username, campaign_id, _other_id, _key_id = (
            await _seed_identity(database)
        )
        issued = await database.issue_websocket_ticket(
            campaign_id,
            _bearer_source(user_id, username),
        )
        _require_fixed(
            issued is not None,
            "PostgreSQL metadata mutation ticket setup failed",
        )
        raw_ticket = (issued or ("", 0))[0]
        observer_pool = database._pool
        candidate = _database_for_target(harness.database_name)
        mutation_applied = False
        candidate_connected = False
        try:
            async with observer_pool.acquire() as connection:
                await asyncio.wait_for(
                    _apply_ticket_metadata_mutation(connection, mutation),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                mutation_applied = True
                mutation_exists = await asyncio.wait_for(
                    _ticket_metadata_mutation_exists(connection, mutation),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )

            trigger_would_enable_replay = True
            if mutation == "consumption-trigger":
                first = await asyncio.wait_for(
                    database.consume_websocket_ticket(
                        raw_ticket,
                        campaign_id,
                    ),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                second = await asyncio.wait_for(
                    database.consume_websocket_ticket(
                        raw_ticket,
                        campaign_id,
                    ),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                async with observer_pool.acquire() as connection:
                    remains_unconsumed = bool(
                        await connection.fetchval(
                            """
                            SELECT consumed_at IS NULL
                            FROM websocket_tickets
                            WHERE ticket_hash=$1
                            """,
                            hash_websocket_ticket(raw_ticket),
                        )
                    )
                trigger_would_enable_replay = (
                    first is not None
                    and second is not None
                    and remains_unconsumed
                )

            async with observer_pool.acquire() as connection:
                before_catalog = await asyncio.wait_for(
                    _ticket_catalog_fingerprint(connection),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                before_related = await asyncio.wait_for(
                    _ticket_related_object_fingerprint(connection),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                before_rows = await asyncio.wait_for(
                    _ticket_rows_for_metadata_mutation(connection, mutation),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )

            failure_type: type[BaseException] | None = None
            failure_is_sanitized = False
            try:
                await asyncio.wait_for(
                    candidate.connect(),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                candidate_connected = True
            except Exception as exc:
                failure_type = type(exc)
                failure_is_sanitized = (
                    failure_type is _PostgresStartupDiagnosticError
                    and str(exc) == "Incompatible WebSocket ticket schema"
                ) or (
                    failure_type is _PostgresManagedSchemaError
                    and str(exc)
                    == "Incompatible managed PostgreSQL schema"
                )
            pool_was_cleared = candidate._pool is None

            async with observer_pool.acquire() as connection:
                after_catalog = await asyncio.wait_for(
                    _ticket_catalog_fingerprint(connection),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                after_related = await asyncio.wait_for(
                    _ticket_related_object_fingerprint(connection),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                after_rows = await asyncio.wait_for(
                    _ticket_rows_for_metadata_mutation(connection, mutation),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                observer_reusable = (
                    await asyncio.wait_for(
                        connection.fetchval("SELECT 1"),
                        timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                    )
                    == 1
                )
            startup_preserved_state = (
                before_catalog == after_catalog
                and before_related == after_related
                and before_rows == after_rows
            )
            _require_fixed(
                mutation_exists
                and trigger_would_enable_replay
                and failure_is_sanitized
                and pool_was_cleared
                and startup_preserved_state
                and observer_reusable,
                "PostgreSQL relation metadata prevalidation failed",
            )
        finally:
            if candidate_connected or candidate._pool is not None:
                await _attempt_cleanup(
                    "metadata-candidate-close",
                    candidate.close,
                    [],
                )
            if mutation_applied:
                async with observer_pool.acquire() as connection:
                    await asyncio.wait_for(
                        _remove_ticket_metadata_mutation(connection, mutation),
                        timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                    )

        recovery = _database_for_target(harness.database_name)
        try:
            await asyncio.wait_for(
                recovery.connect(),
                timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
            )
            async with recovery._pool.acquire() as connection:
                recovered_catalog = await asyncio.wait_for(
                    _ticket_catalog_fingerprint(connection),
                    timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                )
                recovered_connection = (
                    await asyncio.wait_for(
                        connection.fetchval("SELECT 1"),
                        timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
                    )
                    == 1
                )
            consumed = await asyncio.wait_for(
                recovery.consume_websocket_ticket(
                    raw_ticket,
                    campaign_id,
                ),
                timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
            )
            replay = await asyncio.wait_for(
                recovery.consume_websocket_ticket(
                    raw_ticket,
                    campaign_id,
                ),
                timeout=_POSTGRES_OPERATION_TIMEOUT_SECONDS,
            )
            recovery_is_exact = (
                _ticket_catalog_matches_fixed_contract(recovered_catalog)
                and recovered_connection
                and consumed is not None
                and replay is None
            )
            _require_fixed(
                recovery_is_exact,
                "PostgreSQL metadata correction recovery failed",
            )
        finally:
            if recovery._pool is not None:
                await _attempt_cleanup(
                    "metadata-recovery-close",
                    recovery.close,
                    [],
                )