Open-source AI Security Operations Center: alert fusion, LLM-agent triage, MITRE ATT&CK investigation, and a replayable decision ledger for every agent step. Self-hostable, runs with no API keys, MIT licensed. Ships an MCP server for Claude, Cursor and Continue.
Python agentic-aiai-securityalert-triageblue-teamcybersecuritydetection-engineeringincident-responselanggraphllm-agentsmcpmitre-attackmodel-context-protocolpurple-teamsecurity-operationsself-hostedsiemsoarsocthreat-detectionthreat-intelligence
-- 124: derived privilege facts per identity, and the runtime-usage overlay
-- (depth plan wave 2, Phase 1.1).
--
-- Why derived facts and not "just resolve on read"
-- ------------------------------------------------
-- Migration 103 stores a provider's whole posture as one versioned jsonb
-- document, and the resolver computes a principal's effective permissions
-- from it. That is the right shape for an analyst asking about one identity
-- and the wrong one for three callers that need an answer on the hot path:
--
-- * fusion stamps a privilege tier on every alert at fuse time, and cannot
-- parse an estate-sized policy document per event;
-- * the queue sorts by priority, which reads the tier;
-- * the access review (1.2) scans every principal at once.
--
-- So the expensive resolution runs once per posture version and lands here as
-- a small row per principal. The rows are versioned with the same
-- valid_from / valid_until interval as the snapshot they came from, so "what
-- could this identity do when it acted" stays answerable -- which is the
-- whole reason 103 is versioned, and a derived table that collapsed to one
-- mutable row per principal would quietly undo it.
--
-- Why `unknown` is a stored value and not a NULL tier
-- ---------------------------------------------------
-- A principal with no posture covering an event time is a real and common
-- state: the tenant connected the provider last week and the alert is from
-- last month. Defaulting it to 'standard' is the confident-zero defect --
-- a reader cannot tell a measured standard account from one the platform
-- knows nothing about, and the second is the one worth escalating. So
-- 'unknown' is a first-class tier that every surface renders as unknown.
--
-- The four tags are `boolean NULL` for the same reason: NULL means "no
-- snapshot could answer this", false means "the snapshot says no".
CREATE TABLE IF NOT EXISTS identity_privilege_facts (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL,
provider text NOT NULL,
-- The provider-scoped id the resolver was asked about (AWS ARN or user
-- id, Entra object id, GCP member, Okta user id, Workspace user key,
-- GitHub login).
principal_id text NOT NULL,
-- A second, human-readable handle when the provider has one. Kept
-- alongside rather than instead of `principal_id`, because an alert
-- carries whichever the connector emitted and a join on the wrong one
-- silently matches nothing.
principal_label text,
principal_kind text,
tier text NOT NULL DEFAULT 'unknown',
-- The four computed tags. NULL is "no snapshot could answer", which is
-- not the same as false and must not render as one.
can_escalate boolean,
data_access boolean,
third_party boolean,
cross_account boolean,
-- Which actions and which policy chain drove each tag. An analyst who
-- cannot see why an identity was tagged `can_escalate` has a label, not
-- a finding.
evidence jsonb NOT NULL DEFAULT '{}'::jsonb,
action_count integer NOT NULL DEFAULT 0,
resource_count integer NOT NULL DEFAULT 0,
-- The snapshot this was derived from. ON DELETE CASCADE because a fact
-- whose snapshot is gone cannot be re-derived or audited, and keeping it
-- would be a number with no provenance.
snapshot_id uuid REFERENCES posture_snapshots(id) ON DELETE CASCADE,
resolver_version text NOT NULL DEFAULT '',
-- True when the snapshot it came from was partial. Carried down rather
-- than looked up, so a surface reading only this table can still say
-- "this answer is incomplete".
partial_source boolean NOT NULL DEFAULT false,
valid_from timestamptz NOT NULL DEFAULT now(),
valid_until timestamptz,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT identity_privilege_facts_provider_check CHECK (
provider IN ('aws', 'azure', 'gcp', 'gws', 'github', 'okta')
),
CONSTRAINT identity_privilege_facts_tier_check CHECK (
tier IN ('unknown', 'standard', 'elevated', 'admin', 'domain_admin')
),
CONSTRAINT identity_privilege_facts_interval_check CHECK (
valid_until IS NULL OR valid_until >= valid_from
)
);
-- At most one live fact per principal per provider per tenant. Same
-- invariant, and same reason, as posture_snapshots_live_idx: two live rows
-- make "the current tier" whichever the planner returned first.
CREATE UNIQUE INDEX IF NOT EXISTS identity_privilege_facts_live_idx
ON identity_privilege_facts (tenant_id, provider, principal_id)
WHERE valid_until IS NULL;
-- The point-in-time lookup fusion makes per alert.
CREATE INDEX IF NOT EXISTS identity_privilege_facts_at_idx
ON identity_privilege_facts (tenant_id, provider, principal_id, valid_from DESC);
-- The access review's scan: every principal carrying a tag, newest first.
CREATE INDEX IF NOT EXISTS identity_privilege_facts_tags_idx
ON identity_privilege_facts (tenant_id, provider, tier)
WHERE valid_until IS NULL;
-- Fusion joins on whichever handle the connector emitted, so the label needs
-- its own index or the stamp falls back to a sequential scan per alert.
CREATE INDEX IF NOT EXISTS identity_privilege_facts_label_idx
ON identity_privilege_facts (tenant_id, lower(principal_label))
WHERE valid_until IS NULL AND principal_label IS NOT NULL;
COMMENT ON TABLE identity_privilege_facts IS
'Per-identity privilege tier and computed tags, derived once per posture version. Versioned on the '
'same interval as the snapshot it came from so an alert reads the tier that was true when the '
'identity acted, not the one true now.';
COMMENT ON COLUMN identity_privilege_facts.tier IS
'unknown | standard | elevated | admin | domain_admin. unknown is a measured state (no snapshot '
'covers this time), never a default, and every surface renders it as unknown.';
COMMENT ON COLUMN identity_privilege_facts.can_escalate IS
'NULL means no snapshot could answer. false means the snapshot says no. The two are different '
'facts and a surface that renders them the same is fabricating one of them.';
-- The runtime-usage overlay: of what a principal was granted, what did it
-- actually exercise?
--
-- Sourced from the event lake, which lives in the `full` profile. A CORE
-- install has no lake, and the honest consequence is that usage is *not
-- measured* there -- which is a different answer from "used nothing". The
-- difference matters more here than almost anywhere else in the product: a
-- right-sizing recommendation built on "used nothing" tells an operator to
-- revoke every permission in the estate.
--
-- `window_start` / `window_end` are on the row rather than implied by a
-- config value, because a finding that says "unused in the window" is
-- meaningless without the window, and the window changes.
CREATE TABLE IF NOT EXISTS identity_permission_usage (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL,
provider text NOT NULL,
principal_id text NOT NULL,
-- Provider-namespaced, matching what the resolver returns, so granted
-- and used can be compared without a translation table in between.
action text NOT NULL,
use_count bigint NOT NULL DEFAULT 0,
first_used_at timestamptz,
last_used_at timestamptz,
window_start timestamptz NOT NULL,
window_end timestamptz NOT NULL,
-- Where the observation came from, so an operator can tell a lake-backed
-- measurement from a connector-backed one.
source text NOT NULL DEFAULT 'lake',
computed_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT identity_permission_usage_provider_check CHECK (
provider IN ('aws', 'azure', 'gcp', 'gws', 'github', 'okta')
),
CONSTRAINT identity_permission_usage_window_check CHECK (window_end >= window_start)
);
CREATE UNIQUE INDEX IF NOT EXISTS identity_permission_usage_unique_idx
ON identity_permission_usage (tenant_id, provider, principal_id, action);
CREATE INDEX IF NOT EXISTS identity_permission_usage_principal_idx
ON identity_permission_usage (tenant_id, provider, principal_id, last_used_at DESC);
COMMENT ON TABLE identity_permission_usage IS
'Granted actions a principal actually exercised in a trailing window, from the event lake. An '
'absent row means not observed; on a CORE install with no lake that means not measured, which is '
'not the same as never used and must never be rendered as it.';
DO $$
BEGIN
EXECUTE 'ALTER TABLE identity_privilege_facts ENABLE ROW LEVEL SECURITY';
EXECUTE 'ALTER TABLE identity_privilege_facts FORCE ROW LEVEL SECURITY';
EXECUTE 'DROP POLICY IF EXISTS tenant_isolation ON identity_privilege_facts';
EXECUTE
'CREATE POLICY tenant_isolation ON identity_privilege_facts '
'USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL) '
'WITH CHECK (tenant_id = current_tenant_id())';
EXECUTE 'ALTER TABLE identity_permission_usage ENABLE ROW LEVEL SECURITY';
EXECUTE 'ALTER TABLE identity_permission_usage FORCE ROW LEVEL SECURITY';
EXECUTE 'DROP POLICY IF EXISTS tenant_isolation ON identity_permission_usage';
EXECUTE
'CREATE POLICY tenant_isolation ON identity_permission_usage '
'USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL) '
'WITH CHECK (tenant_id = current_tenant_id())';
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'aisoc_app') THEN
EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON identity_privilege_facts TO aisoc_app';
EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON identity_permission_usage TO aisoc_app';
END IF;
END
$$;