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
-- 109_mcp_access.sql — who may call which MCP tool, and the queue that holds
-- the ones a key alone is not enough for.
--
-- Depth plan 9.1 (parity 6.6). `services/mcp` was stdio-only: an MCP host
-- started it as a child process with one operator's API key in the host's
-- config file. Nothing in this repository deployed it, and nothing recorded
-- what it read. Moving it onto HTTP is what makes these two tables
-- necessary, because the transport change is also a threat-model change:
-- several tenants now share one process, and the credential arrives in the
-- request rather than in the environment.
--
-- aisoc_mcp_tool_grants — the tenant-level switch
-- -----------------------------------------------
-- A key's scopes say what a *credential* may do. This says what a *tenant*
-- has turned on, which is a different decision and belongs to a different
-- person: a tenant administrator who has decided their estate's event lake
-- is not something an assistant reaches, whatever key somebody mints.
--
-- Absence of a row is not "denied". It means "the tool's own default
-- applies", and the default is declared per tool in
-- `app/data/mcp_tool_catalog.json`: read-only tools default on, anything
-- that writes defaults off. Storing only the deliberate decisions keeps the
-- table small and, more usefully, keeps "nobody has thought about this tool"
-- distinguishable from "somebody turned it off" — which a NOT NULL boolean
-- seeded for every tool would have destroyed.
--
-- aisoc_mcp_tool_approvals — the queue for everything that is not a read
-- ---------------------------------------------------------------------
-- A tool whose registry entry says `readOnlyHint: false` cannot run on the
-- strength of a bearer token. The authorize path creates a row here instead
-- and answers the caller with its id, so the refusal is actionable rather
-- than a dead end: a human approves it in the console and the same call
-- succeeds on retry.
--
-- `argument_fingerprint` is a SHA-256 over the canonical argument *names and
-- values* the caller supplied. An approval has to be for a specific call,
-- not for a verb: approving "run an investigation on case A" must not
-- authorise "run an investigation on case B". It is a hash rather than the
-- arguments themselves because those arguments routinely carry hostnames
-- and queries, and this table is read by the console.
--
-- `expires_at` is NOT NULL for the reason every standing grant in this tree
-- is time-boxed: an approval that never expires is a permission, and nobody
-- decided to grant a permission.
--
-- Reversible: DROP TABLE IF EXISTS, either order (no FK between them).
CREATE TABLE IF NOT EXISTS aisoc_mcp_tool_grants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
-- Matches `name` in app/data/mcp_tool_catalog.json. Not an FK: the
-- catalogue is a generated file held in parity with the TypeScript
-- registry by scripts/check_mcp_over_http.py, and a row for a tool
-- that has since been removed should survive as history rather than
-- block the removal.
tool_name TEXT NOT NULL,
enabled BOOLEAN NOT NULL,
-- Who flipped it. Free text because the actor may be a user email or an
-- API key prefix, and an auditor reading this wants whichever it was.
decided_by TEXT NOT NULL DEFAULT '',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT aisoc_mcp_tool_grants_unique UNIQUE (tenant_id, tool_name)
);
CREATE INDEX IF NOT EXISTS aisoc_mcp_tool_grants_tenant
ON aisoc_mcp_tool_grants (tenant_id);
CREATE TABLE IF NOT EXISTS aisoc_mcp_tool_approvals (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
tool_name TEXT NOT NULL,
-- SHA-256 hex over the canonicalised arguments. See the header for why
-- this is a digest and not the arguments.
argument_fingerprint CHAR(64) NOT NULL,
-- The key prefix (never the key) or user email that asked.
requested_by TEXT NOT NULL DEFAULT '',
state TEXT NOT NULL DEFAULT 'pending'
CHECK (state IN ('pending', 'approved', 'denied', 'consumed', 'expired')),
decided_by TEXT,
decided_at TIMESTAMPTZ,
-- Why it was denied, or any note the approver left. Shown to the caller.
decision_note TEXT NOT NULL DEFAULT '',
expires_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- The authorize path's lookup: "is there a live approval for exactly this
-- call?". Partial on `approved` because pending and consumed rows accumulate
-- and the hot query only ever wants the one state.
CREATE INDEX IF NOT EXISTS aisoc_mcp_tool_approvals_live_idx
ON aisoc_mcp_tool_approvals (tenant_id, tool_name, argument_fingerprint)
WHERE state = 'approved';
-- The console's queue.
CREATE INDEX IF NOT EXISTS aisoc_mcp_tool_approvals_pending_idx
ON aisoc_mcp_tool_approvals (tenant_id, created_at DESC)
WHERE state = 'pending';
COMMENT ON TABLE aisoc_mcp_tool_grants IS
'Per-tenant MCP tool switch. An absent row means the tool default in '
'app/data/mcp_tool_catalog.json applies, which keeps "nobody decided" '
'distinguishable from "somebody turned it off".';
COMMENT ON TABLE aisoc_mcp_tool_approvals IS
'One row per non-read-only MCP tool call that needed a human. The '
'fingerprint binds an approval to a specific call, so approving an '
'investigation on one case does not authorise one on another.';
-- Written out per table rather than looped with format(): a loop is shorter
-- and makes the policy invisible to grep and to
-- scripts/check_tenant_query_predicates.py, which reads migrations to learn
-- which tables carry one.
DO $$
BEGIN
EXECUTE 'ALTER TABLE aisoc_mcp_tool_grants ENABLE ROW LEVEL SECURITY';
EXECUTE 'ALTER TABLE aisoc_mcp_tool_grants FORCE ROW LEVEL SECURITY';
EXECUTE 'DROP POLICY IF EXISTS tenant_isolation ON aisoc_mcp_tool_grants';
EXECUTE
'CREATE POLICY tenant_isolation ON aisoc_mcp_tool_grants '
'USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL) '
'WITH CHECK (tenant_id = current_tenant_id())';
EXECUTE 'ALTER TABLE aisoc_mcp_tool_approvals ENABLE ROW LEVEL SECURITY';
EXECUTE 'ALTER TABLE aisoc_mcp_tool_approvals FORCE ROW LEVEL SECURITY';
EXECUTE 'DROP POLICY IF EXISTS tenant_isolation ON aisoc_mcp_tool_approvals';
EXECUTE
'CREATE POLICY tenant_isolation ON aisoc_mcp_tool_approvals '
'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 aisoc_mcp_tool_grants TO aisoc_app';
EXECUTE 'GRANT SELECT, INSERT, UPDATE, DELETE ON aisoc_mcp_tool_approvals TO aisoc_app';
END IF;
END
$$;