Files
rust-discovery/migrations/0019_windows_echo_completion.sql
2026-11-16 10:25:25 +01:00

104 lines
7.5 KiB
PL/PgSQL

-- No existing scope is retired by this migration. A separate interactive
-- dev/test WebUI decision and a matching native receipt application are needed.
CREATE TABLE windows_echo_completions (
tenant_id uuid PRIMARY KEY REFERENCES windows_echo_probe_scopes(tenant_id),
receipt_id uuid NOT NULL UNIQUE, witness_sha256 text NOT NULL CHECK(witness_sha256 ~ '^[0-9a-f]{64}$'),
payload bytea NOT NULL, signature bytea NOT NULL CHECK(octet_length(signature)=64),
confirmed_by text NOT NULL CHECK(confirmed_by='simulated-testprincipal-a'),
session_hash text NOT NULL CHECK(session_hash ~ '^[0-9a-f]{64}$'),
issued_at timestamptz NOT NULL, valid_until timestamptz NOT NULL
CHECK(valid_until>issued_at AND valid_until<=issued_at+interval '10 minutes')
);
ALTER TABLE windows_echo_completions ENABLE ROW LEVEL SECURITY;
ALTER TABLE windows_echo_completions FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON windows_echo_completions USING(tenant_id::text=current_setting('app.tenant_id',true))
WITH CHECK(tenant_id::text=current_setting('app.tenant_id',true));
GRANT SELECT,INSERT ON windows_echo_completions TO rmm_app;
CREATE TRIGGER immutable_content BEFORE UPDATE OR DELETE ON windows_echo_completions
FOR EACH ROW EXECUTE FUNCTION immutable_plan();
CREATE FUNCTION check_windows_echo_completion() RETURNS trigger LANGUAGE plpgsql
SET search_path=pg_catalog,public AS $$
DECLARE s windows_echo_probe_scopes%ROWTYPE; j jobs%ROWTYPE; p execution_permits%ROWTYPE; r jsonb; w jsonb;
BEGIN
SELECT * INTO STRICT s FROM windows_echo_probe_scopes WHERE tenant_id=NEW.tenant_id;
SELECT * INTO STRICT j FROM jobs WHERE tenant_id=s.tenant_id AND job_id=s.job_id AND attempt_id=s.attempt_id FOR SHARE;
SELECT * INTO STRICT p FROM execution_permits WHERE tenant_id=j.tenant_id AND job_id=j.job_id AND attempt_id=j.attempt_id;
r=convert_from(NEW.payload,'UTF8')::jsonb; w=r->'witness';
IF r->>'domain' IS DISTINCT FROM 'rmm.dev.windows-echo-completion.v1' OR r->>'schema_version' IS DISTINCT FROM '1'
OR r->>'receipt_id' IS DISTINCT FROM NEW.receipt_id::text
OR r->>'witness_sha256' IS DISTINCT FROM NEW.witness_sha256
OR r->>'confirmed_by' IS DISTINCT FROM NEW.confirmed_by
OR r->>'approval_origin' IS DISTINCT FROM 'dev_test_browser_simulation' OR r->'testsimulation' IS DISTINCT FROM 'true'::jsonb
OR r->'ready' IS DISTINCT FROM 'false'::jsonb OR r->'helper_ready' IS DISTINCT FROM 'false'::jsonb OR r->'unlock' IS DISTINCT FROM 'false'::jsonb
OR (r->>'issued_at')::timestamptz IS DISTINCT FROM NEW.issued_at OR (r->>'valid_until')::timestamptz IS DISTINCT FROM NEW.valid_until
OR w->'scope' IS DISTINCT FROM convert_from(s.receipt,'UTF8')::jsonb->'scope'
OR w->>'probe_receipt_id' IS DISTINCT FROM s.receipt_id::text
OR w->>'probe_receipt_sha256' IS DISTINCT FROM encode(sha256(s.receipt),'hex')
OR w->>'probe_signature_sha256' IS DISTINCT FROM encode(sha256(s.signature),'hex')
OR w->>'grant_id' IS DISTINCT FROM p.grant_id::text
OR w->>'grant_payload_sha256' IS DISTINCT FROM encode(sha256(p.payload),'hex')
OR w->>'grant_signature_sha256' IS DISTINCT FROM encode(sha256(p.signature),'hex')
OR j.state!='succeeded' OR j.result->>'exit_code' IS DISTINCT FROM '0'
OR j.result->'stdout' IS DISTINCT FROM '[90,101,105,116,112,114,111,102,105,108,45,84,101,115,116,13,10]'::jsonb
OR j.result->'stderr' IS DISTINCT FROM '[]'::jsonb OR j.result->'output_truncated' IS DISTINCT FROM 'false'::jsonb
OR j.result->'reboot_required' IS DISTINCT FROM 'false'::jsonb OR j.result->>'error_code' IS DISTINCT FROM ''
OR j.result#>>'{evidence,execution_sid}' IS DISTINCT FROM 'S-1-5-19'
OR j.result->>'grant_id' IS DISTINCT FROM p.grant_id::text
OR EXISTS(SELECT FROM jobs WHERE tenant_id=s.tenant_id AND agent_id=s.agent_id AND job_id!=s.job_id)
THEN RAISE EXCEPTION 'WINDOWS_ECHO_COMPLETION_MISMATCH'; END IF;
RETURN NEW;
END $$;
CREATE TRIGGER validate_windows_echo_completion BEFORE INSERT ON windows_echo_completions
FOR EACH ROW EXECUTE FUNCTION check_windows_echo_completion();
CREATE OR REPLACE FUNCTION restrict_windows_echo_probe_job() RETURNS trigger LANGUAGE plpgsql
SET search_path=pg_catalog,public AS $$
DECLARE s windows_echo_probe_scopes%ROWTYPE;
BEGIN
IF NEW.tenant_id='11111111-1111-4111-8111-111111111111' AND
(NEW.agent_id='63ea15b2-0643-403d-b5b4-cba1288dbea3' OR NEW.asset_id='a176520e-8a91-4e93-a78b-8e7620f430e0') THEN
SELECT * INTO s FROM windows_echo_probe_scopes WHERE tenant_id=NEW.tenant_id;
IF FOUND AND NOT EXISTS(SELECT FROM windows_echo_completions WHERE tenant_id=NEW.tenant_id)
AND (s.operation_id,s.job_id,s.attempt_id,s.agent_id,s.asset_id,s.job_hash,s.job_bytes)
IS DISTINCT FROM (NEW.operation_id,NEW.job_id,NEW.attempt_id,NEW.agent_id,NEW.asset_id,NEW.payload_hash,NEW.payload)
THEN RAISE EXCEPTION 'WINDOWS_ECHO_SCOPE_MISMATCH'; END IF;
END IF;
RETURN NEW;
END $$;
CREATE OR REPLACE FUNCTION restrict_windows_echo_delivery() RETURNS trigger LANGUAGE plpgsql
SET search_path=pg_catalog,public AS $$
DECLARE s windows_echo_probe_scopes%ROWTYPE; j jobs%ROWTYPE; approval authorizations%ROWTYPE; g jsonb; payload jsonb;
BEGIN
IF NEW.tenant_id!='11111111-1111-4111-8111-111111111111'
OR EXISTS(SELECT FROM windows_echo_completions WHERE tenant_id=NEW.tenant_id) THEN RETURN NEW; END IF;
SELECT * INTO s FROM windows_echo_probe_scopes WHERE tenant_id=NEW.tenant_id;
IF NOT FOUND THEN RETURN NEW; END IF;
SELECT * INTO STRICT j FROM jobs WHERE tenant_id=NEW.tenant_id AND job_id=NEW.job_id AND attempt_id=NEW.attempt_id;
IF j.agent_id!=s.agent_id AND j.asset_id!=s.asset_id THEN RETURN NEW; END IF;
SELECT * INTO STRICT approval FROM authorizations WHERE tenant_id=NEW.tenant_id AND operation_id=s.operation_id;
IF (s.operation_id,s.job_id,s.attempt_id,s.agent_id,s.asset_id,s.job_hash,s.job_bytes)
IS DISTINCT FROM (j.operation_id,j.job_id,j.attempt_id,j.agent_id,j.asset_id,j.payload_hash,j.payload)
OR (approval.plan_hash,approval.plan_id,approval.principal,approval.approval_origin,approval.testsimulation)
IS DISTINCT FROM (s.plan_hash,s.plan_id,'simulated-testprincipal-a'::text,'dev_test_browser_simulation'::text,true)
OR approval.revoked_at IS NOT NULL
THEN RAISE EXCEPTION 'WINDOWS_ECHO_SCOPE_MISMATCH'; END IF;
IF TG_TABLE_NAME='execution_permits' THEN
g=convert_from(NEW.payload,'UTF8')::jsonb;
payload=convert_from(j.payload,'UTF8')::jsonb;
IF (g->>'grant_id',g->>'job_id',g->>'attempt_id',g->>'agent_id',g->>'asset_id',g->>'tenant_id',g->>'plan_hash',g->>'job_payload_hash',g->>'authorization_id')
IS DISTINCT FROM (NEW.grant_id::text,s.job_id::text,s.attempt_id::text,s.agent_id::text,s.asset_id::text,s.tenant_id::text,s.plan_hash,s.job_hash,approval.authorization_id::text)
OR g->>'domain' IS DISTINCT FROM 'rmm.execution-permit' OR g->>'schema_version' IS DISTINCT FROM '1'
OR g->>'action' IS DISTINCT FROM 'run_script' OR g->>'execution_identity' IS DISTINCT FROM 'agent_service'
OR g->>'enrollment_generation' IS DISTINCT FROM '1' OR g->>'assignment_version' IS DISTINCT FROM '1'
OR g->>'max_runtime_seconds' IS DISTINCT FROM '60' OR g->>'not_before' IS NULL OR g->>'start_before' IS NULL
OR (g->>'not_before')::timestamptz < (payload->>'not_before')::timestamptz
OR (g->>'start_before')::timestamptz > (payload->>'start_before')::timestamptz
OR (g->>'start_before')::timestamptz <= (g->>'not_before')::timestamptz
OR (g->>'start_before')::timestamptz > (g->>'not_before')::timestamptz + interval '60 seconds'
THEN RAISE EXCEPTION 'WINDOWS_ECHO_SCOPE_MISMATCH'; END IF;
END IF;
RETURN NEW;
END $$;