Skip to content

armgit / AiSOC

Public Imported from GitHub
0

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.

10.5 KB
Raw Blame
-- An enforced retention window for the audit log, without breaking the chain.
--
-- Depth 8.1 (parity 4.4's remainder).
--
-- `retention_policies.audit_days` has been storable since the table existed
-- and purged by nothing. `app/workers/retention_purge.py` says so in its own
-- module docstring, and says why:
--
--   > The audit log is an append-only hash chain (migration 043) whose entire
--   > value is that no row was deleted, reordered, or rewritten; a retention
--   > purge would break the chain and make every subsequent verification
--   > fail. Enforcing an audit retention window needs chain-aware truncation
--   > with a re-anchored checkpoint, which is a different piece of work.
--
-- This is that piece of work. Saying it in one place beat a purge that
-- silently errored every tick, but it left an operator who configured
-- `audit_days` with a setting the product ignored — and a regulator's
-- erasure request with no mechanism behind it.
--
-- ── Why a checkpoint rather than a plain DELETE ─────────────────────────────
--
-- `verify_chain_breaks` replays a tenant's rows oldest-first starting from
-- `prev_hash = NULL`. Remove the oldest rows and the new first row names a
-- predecessor that is gone, so the replay reports `prev_hash mismatch` at
-- index 0 — a chain that is perfectly intact reads as tampered with, which
-- is the worst possible outcome for a control whose only job is to tell
-- those two apart.
--
-- A checkpoint re-anchors it. It records the cutoff that was applied, how
-- many rows went, the hash of the last one removed, and the hash the oldest
-- survivor names as its predecessor. Verification then starts from that
-- anchor and the surviving chain replays clean — and the removal itself is
-- now a fact an auditor can read, which a plain DELETE would not be.

CREATE TABLE IF NOT EXISTS aisoc_audit_checkpoints (
    id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id                UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,

    -- The cutoff applied, and the policy that produced it. Both, because
    -- "everything before 1 March went" and "a 90-day window was in force"
    -- answer different questions and an auditor asks the second one.
    truncated_before         TIMESTAMPTZ NOT NULL,
    window_days              INTEGER NOT NULL,

    rows_removed             BIGINT NOT NULL,

    -- The last link of what was removed, and the first link of what stayed.
    -- `first_retained_prev_hash` is what makes the remainder verifiable: it
    -- is the anchor the replay starts from, and it must equal
    -- `last_removed_hash` on an untampered chain. Storing both means the
    -- checkpoint can be checked against itself.
    last_removed_hash        TEXT,
    first_retained_prev_hash TEXT,
    first_retained_hash      TEXT,

    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS aisoc_audit_checkpoints_tenant_idx
    ON aisoc_audit_checkpoints (tenant_id, created_at DESC);

-- ── The checkpoint is immutable too ─────────────────────────────────────────
--
-- A record of what was deleted, which can itself be edited or deleted, is
-- not a record. This is the same trigger shape migration `004` uses on
-- `audit_log` and `101` repaired: DELETE is refused first and
-- unconditionally, because on DELETE there is no NEW and `RETURN NEW`
-- returns NULL, which in a BEFORE trigger silently *cancels* the statement
-- and reports "DELETE 0" as success.
--
-- The one exception is the cascade from `tenants`. The foreign key above
-- declares `ON DELETE CASCADE`, and an unconditional refusal here would
-- make that declaration a lie: the tenant delete would raise instead of
-- cascading, so the row could never be removed by any means at all.
--
-- A cascade is told apart from a direct DELETE by whether the parent is
-- still there. PostgreSQL implements `ON DELETE CASCADE` as an AFTER
-- trigger on the parent, so by the time this BEFORE trigger runs on the
-- child the tenant row is already gone; a caller deleting checkpoints
-- directly still has their tenant. Measured both ways rather than assumed.
--
-- That is a narrow hole and deliberately an expensive one: erasing the
-- record of a truncation means destroying the entire tenant, which takes
-- every alert, case and credential with it. Selective erasure -- the thing
-- this table exists to prevent -- is still refused.
CREATE OR REPLACE FUNCTION public.audit_checkpoint_immutable()
RETURNS trigger
LANGUAGE plpgsql
AS $fn$
BEGIN
    IF TG_OP = 'DELETE' AND NOT EXISTS (SELECT 1 FROM tenants WHERE id = OLD.tenant_id) THEN
        RETURN OLD;
    END IF;
    RAISE EXCEPTION 'aisoc_audit_checkpoints rows are immutable (attempted %)', TG_OP;
END;
$fn$;

DROP TRIGGER IF EXISTS audit_checkpoint_immutable ON aisoc_audit_checkpoints;
CREATE TRIGGER audit_checkpoint_immutable
    BEFORE UPDATE OR DELETE ON aisoc_audit_checkpoints
    FOR EACH ROW EXECUTE FUNCTION public.audit_checkpoint_immutable();

ALTER TABLE aisoc_audit_checkpoints ENABLE ROW LEVEL SECURITY;
ALTER TABLE aisoc_audit_checkpoints FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON aisoc_audit_checkpoints;
CREATE POLICY tenant_isolation ON aisoc_audit_checkpoints
    USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL)
    WITH CHECK (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);

-- ── The one sanctioned way to remove an audit row ───────────────────────────
--
-- `SECURITY DEFINER`, so it runs as the table owner, and the application
-- role has **no DELETE privilege on `audit_log` at all** after this
-- migration. That is the gate that cannot be talked around: the
-- transaction-local setting below is a second one, but a custom GUC can be
-- set by anybody, so on its own it would be a comment rather than a
-- control.
--
-- `SET search_path` is pinned on the function. Without it a caller who
-- controls `search_path` can put their own `audit_log` in front of the real
-- one and have a definer-rights function operate on it.
CREATE OR REPLACE FUNCTION public.audit_retention_truncate(
    p_tenant_id  UUID,
    p_cutoff     TIMESTAMPTZ,
    p_window_days INTEGER
)
RETURNS BIGINT
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $fn$
DECLARE
    v_rows               BIGINT;
    v_last_removed_hash  TEXT;
    v_first_prev_hash    TEXT;
    v_first_hash         TEXT;
BEGIN
    IF p_tenant_id IS NULL OR p_cutoff IS NULL THEN
        RAISE EXCEPTION 'audit_retention_truncate needs a tenant and a cutoff';
    END IF;

    SELECT count(*) INTO v_rows
      FROM audit_log
     WHERE tenant_id = p_tenant_id AND created_at < p_cutoff;

    IF v_rows = 0 THEN
        -- No checkpoint for a no-op. A table of "removed 0 rows" entries
        -- every tick buries the ones that mean something.
        RETURN 0;
    END IF;

    SELECT entry_hash INTO v_last_removed_hash
      FROM audit_log
     WHERE tenant_id = p_tenant_id AND created_at < p_cutoff
     ORDER BY created_at DESC, chain_index DESC
     LIMIT 1;

    SELECT prev_hash, entry_hash INTO v_first_prev_hash, v_first_hash
      FROM audit_log
     WHERE tenant_id = p_tenant_id AND created_at >= p_cutoff
     ORDER BY created_at ASC, chain_index ASC
     LIMIT 1;

    -- Recorded before the delete and in the same transaction, so there is
    -- no ordering in which rows are gone and the record of them is not.
    INSERT INTO aisoc_audit_checkpoints (
        tenant_id, truncated_before, window_days, rows_removed,
        last_removed_hash, first_retained_prev_hash, first_retained_hash
    )
    VALUES (
        p_tenant_id, p_cutoff, p_window_days, v_rows,
        v_last_removed_hash, v_first_prev_hash, v_first_hash
    );

    -- The trigger on `audit_log` refuses every DELETE unless this is set,
    -- and it is `SET LOCAL`, so it lapses at the end of this transaction
    -- rather than leaving the door open for the rest of the session.
    PERFORM set_config('app.audit_retention_purge', 'on', TRUE);
    DELETE FROM audit_log WHERE tenant_id = p_tenant_id AND created_at < p_cutoff;
    PERFORM set_config('app.audit_retention_purge', 'off', TRUE);

    RETURN v_rows;
END;
$fn$;

-- ── Teach the immutability trigger about the one exception ──────────────────
--
-- Byte-identical to `101_audit_immutable_nullify.sql` apart from the DELETE
-- arm. Restated in full rather than patched, because a trigger function
-- assembled from two migrations is one nobody can read in one place.
CREATE OR REPLACE FUNCTION public.audit_log_immutable()
RETURNS trigger
LANGUAGE plpgsql
AS $fn$
DECLARE
    key text;
    old_row jsonb := to_jsonb(OLD);
    new_row jsonb := to_jsonb(NEW);
BEGIN
    IF TG_OP = 'DELETE' THEN
        -- The retention window, and nothing else. `audit_retention_truncate`
        -- is the only thing that sets this, it is `SET LOCAL` so it cannot
        -- outlive the statement that needed it, and the application role has
        -- no DELETE privilege here anyway — so a caller who sets the setting
        -- by hand is refused by Postgres before this trigger is reached.
        IF coalesce(current_setting('app.audit_retention_purge', TRUE), 'off') = 'on' THEN
            RETURN OLD;
        END IF;
        RAISE EXCEPTION 'audit_log rows are immutable (attempted %)', TG_OP;
    END IF;

    FOR key IN SELECT jsonb_object_keys(new_row) LOOP
        IF old_row -> key IS DISTINCT FROM new_row -> key THEN
            -- The ONE legal mutation, pinned to its column: only actor_id
            -- carries ON DELETE SET NULL.
            IF key = 'actor_id'
               AND jsonb_typeof(old_row -> key) <> 'null'
               AND jsonb_typeof(new_row -> key) = 'null'
            THEN
                CONTINUE;
            END IF;
            RAISE EXCEPTION 'audit_log rows are immutable (attempted UPDATE of %)', key;
        END IF;
    END LOOP;
    RETURN NEW;
END;
$fn$;

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'aisoc_app') THEN
        GRANT SELECT, INSERT ON aisoc_audit_checkpoints TO aisoc_app;
        GRANT EXECUTE ON FUNCTION public.audit_retention_truncate(UUID, TIMESTAMPTZ, INTEGER) TO aisoc_app;

        -- The privilege half of the control. The trigger has always
        -- refused a direct DELETE; this makes the refusal happen one layer
        -- lower, where no setting a caller can reach has any say.
        REVOKE DELETE ON audit_log FROM aisoc_app;
    END IF;
END
$$;