119 lines
6.8 KiB
SQL
119 lines
6.8 KiB
SQL
-- Additive Realisierung der Asset-/Kantenschicht aus RESULT_CONTRACT 1.0.0
|
|
-- (CANONICAL_ASSET, ASSET_PROPERTY, ASSET_EDGE, EDGE_COVERAGE).
|
|
-- Siehe docs/RESULT_CONTRACT.md ("Abweichungen der ASSESSOR-Realisierung") und
|
|
-- docs/CANONICAL_MODEL_1.0.0.md.
|
|
--
|
|
-- Feldtreue Übernahme aus dem gelieferten DDL, mit zwei Anpassungen an die
|
|
-- Bestandskonventionen: text-Primärschlüssel statt uuid/gen_random_uuid()
|
|
-- (kein pgcrypto im Repo), und CHECK-Enums statt CREATE TYPE. Kein Producer,
|
|
-- kein Merge- oder Rekonziliationscode in dieser Migration — die Tabellen
|
|
-- sind leer nutzbar angelegt.
|
|
|
|
CREATE TABLE assessor_canonical_assets (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
assessment_id text NOT NULL REFERENCES assessor_assessments(id) ON DELETE RESTRICT,
|
|
asset_key text NOT NULL CHECK (btrim(asset_key) <> ''),
|
|
asset_key_kind text NOT NULL CHECK (asset_key_kind IN (
|
|
'sid', 'object_guid', 'gpo_guid', 'domain_sid', 'trust_key', 'synthetic'
|
|
)),
|
|
asset_type text NOT NULL CHECK (asset_type IN (
|
|
'user', 'group', 'computer', 'domain_controller', 'domain',
|
|
'ou', 'gpo', 'trust', 'tenant', 'service_principal', 'unknown'
|
|
)),
|
|
primary_name text,
|
|
domain text,
|
|
key_confidence numeric NOT NULL DEFAULT 1.0
|
|
CHECK (key_confidence >= 0 AND key_confidence <= 1),
|
|
resolution_method text,
|
|
source_tools text[],
|
|
first_seen_at timestamptz NOT NULL DEFAULT now(),
|
|
last_seen_at timestamptz NOT NULL DEFAULT now(),
|
|
original_fields jsonb,
|
|
extensions jsonb,
|
|
CONSTRAINT assessor_canonical_assets_key_uq UNIQUE (assessment_id, asset_key)
|
|
);
|
|
COMMENT ON TABLE assessor_canonical_assets IS
|
|
'Deduplizierter Knotenregister (RESULT_CONTRACT CANONICAL_ASSET). Identitaet '
|
|
'nur ueber autoritativen Schluessel (asset_key_kind <> synthetic); Name fuehrt '
|
|
'nie zu Merge. synthetic-Knoten verschmelzen nie automatisch mit autoritativen '
|
|
'Knoten; eine Bindung erfolgt ausschliesslich ueber eine explizite, versionierte, '
|
|
'protokollierte Rekonziliationsregel, die in dieser Migration noch nicht existiert.';
|
|
|
|
CREATE TABLE assessor_asset_properties (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
canonical_asset_id text NOT NULL REFERENCES assessor_canonical_assets(id) ON DELETE CASCADE,
|
|
property_key text NOT NULL CHECK (btrim(property_key) <> ''),
|
|
property_value text,
|
|
source_result_id text REFERENCES assessor_finding_occurrences(id) ON DELETE SET NULL,
|
|
mapping_rule_version text
|
|
);
|
|
COMMENT ON TABLE assessor_asset_properties IS
|
|
'Finding-abgeleitete Knoten-Eigenschaften (z. B. kerberoastable, '
|
|
'asrep_roastable, unconstrained_delegation). Faehigkeiten eines Knotens sind '
|
|
'Eigenschaften, keine Kanten; die Zuordnung Finding -> Eigenschaft laeuft ueber '
|
|
'mapping_rule_version.';
|
|
|
|
CREATE TABLE assessor_asset_edges (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
assessment_id text NOT NULL REFERENCES assessor_assessments(id) ON DELETE RESTRICT,
|
|
source_asset_id text NOT NULL REFERENCES assessor_canonical_assets(id) ON DELETE RESTRICT,
|
|
target_asset_id text NOT NULL REFERENCES assessor_canonical_assets(id) ON DELETE RESTRICT,
|
|
edge_type text NOT NULL CHECK (edge_type IN (
|
|
'MemberOf', 'Trusts', 'AllowedToDelegate', 'AllowedToActOnBehalfOf',
|
|
'GpLink', 'Contains', 'GPO-DACL',
|
|
'HasSession', 'AdminTo', 'CanRDP', 'CanPSRemote', 'ExecuteDCOM', 'ObjectDACL'
|
|
)),
|
|
direction text NOT NULL DEFAULT 'source_to_target',
|
|
edge_source text NOT NULL CHECK (btrim(edge_source) <> ''),
|
|
collection_method text NOT NULL CHECK (btrim(collection_method) <> ''),
|
|
collector_version text NOT NULL CHECK (btrim(collector_version) <> ''),
|
|
source_locator text,
|
|
mapping_method text,
|
|
mapping_rule_version text,
|
|
confidence numeric CHECK (confidence IS NULL OR confidence BETWEEN 0 AND 1),
|
|
first_seen_at timestamptz NOT NULL DEFAULT now(),
|
|
last_seen_at timestamptz NOT NULL DEFAULT now(),
|
|
properties jsonb,
|
|
original_fields jsonb
|
|
);
|
|
COMMENT ON TABLE assessor_asset_edges IS
|
|
'Gerichtete Kanten, erste Klasse, getrennt von Findings (RESULT_CONTRACT '
|
|
'ASSET_EDGE). edge_source, collection_method und collector_version sind '
|
|
'Pflicht und Grundlage der ehrlichen Blast-Radius-Deklaration ueber '
|
|
'assessor_edge_coverage.';
|
|
|
|
CREATE TABLE assessor_edge_coverage (
|
|
id text PRIMARY KEY CHECK (btrim(id) <> ''),
|
|
assessment_id text NOT NULL REFERENCES assessor_assessments(id) ON DELETE RESTRICT,
|
|
scan_run_id text REFERENCES assessor_scan_runs(id) ON DELETE SET NULL,
|
|
edge_type text NOT NULL CHECK (edge_type IN (
|
|
'MemberOf', 'Trusts', 'AllowedToDelegate', 'AllowedToActOnBehalfOf',
|
|
'GpLink', 'Contains', 'GPO-DACL',
|
|
'HasSession', 'AdminTo', 'CanRDP', 'CanPSRemote', 'ExecuteDCOM', 'ObjectDACL'
|
|
)),
|
|
edge_source text,
|
|
collected boolean NOT NULL,
|
|
collector_version text
|
|
);
|
|
COMMENT ON TABLE assessor_edge_coverage IS
|
|
'Deklariert je Kantentyp, ob erhoben. Grundlage fuer computed_over_edge_types '
|
|
'und uncollected_edge_types jeder Erreichbarkeits-/Blast-Radius-Berechnung; '
|
|
'ein Blast Radius ueber eine unvollstaendige Kantenmenge darf nie als '
|
|
'vollstaendig dargestellt werden.';
|
|
|
|
CREATE INDEX assessor_canonical_assets_assessment_idx ON assessor_canonical_assets(assessment_id);
|
|
CREATE INDEX assessor_canonical_assets_key_idx ON assessor_canonical_assets(asset_key);
|
|
CREATE INDEX assessor_canonical_assets_type_idx ON assessor_canonical_assets(asset_type);
|
|
CREATE INDEX assessor_canonical_assets_domain_idx ON assessor_canonical_assets(domain);
|
|
|
|
CREATE INDEX assessor_asset_properties_asset_idx ON assessor_asset_properties(canonical_asset_id);
|
|
CREATE INDEX assessor_asset_properties_key_idx ON assessor_asset_properties(property_key);
|
|
|
|
CREATE INDEX assessor_asset_edges_assessment_idx ON assessor_asset_edges(assessment_id);
|
|
CREATE INDEX assessor_asset_edges_source_idx ON assessor_asset_edges(source_asset_id);
|
|
CREATE INDEX assessor_asset_edges_target_idx ON assessor_asset_edges(target_asset_id);
|
|
CREATE INDEX assessor_asset_edges_type_idx ON assessor_asset_edges(edge_type);
|
|
|
|
CREATE INDEX assessor_edge_coverage_assessment_idx ON assessor_edge_coverage(assessment_id);
|
|
CREATE INDEX assessor_edge_coverage_scan_run_idx ON assessor_edge_coverage(scan_run_id);
|