Files
rust-discovery/migrations/0017_windows_checkpoint_recovery.sql
2026-11-15 23:17:19 +01:00

55 lines
3.1 KiB
PL/PgSQL

-- Separate from job approval. Only the reviewed no-effect case is represented.
CREATE TABLE windows_checkpoint_reviews (
tenant_id uuid NOT NULL,
request_id uuid NOT NULL,
asset_id uuid NOT NULL,
agent_id uuid NOT NULL,
witness jsonb NOT NULL,
witness_sha256 text NOT NULL CHECK (witness_sha256 ~ '^[0-9a-f]{64}$'),
requested_by text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (tenant_id, request_id),
UNIQUE (tenant_id, agent_id),
FOREIGN KEY (tenant_id, asset_id) REFERENCES assets(tenant_id, asset_id),
FOREIGN KEY (tenant_id, agent_id) REFERENCES agent_installations(tenant_id, agent_id)
);
CREATE TABLE windows_checkpoint_review_receipts (
tenant_id uuid NOT NULL,
request_id uuid NOT NULL,
receipt_id uuid NOT NULL,
confirmed_by text NOT NULL CHECK (confirmed_by ~ '^oidc:[0-9a-f]{64}$'),
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'),
payload bytea NOT NULL,
signature bytea NOT NULL CHECK (octet_length(signature)=64),
PRIMARY KEY (tenant_id, receipt_id),
FOREIGN KEY (tenant_id, request_id) REFERENCES windows_checkpoint_reviews(tenant_id, request_id)
);
CREATE INDEX windows_checkpoint_receipts_latest ON windows_checkpoint_review_receipts(tenant_id, request_id, issued_at DESC);
DO $$ DECLARE t text; BEGIN
FOREACH t IN ARRAY ARRAY['windows_checkpoint_reviews', 'windows_checkpoint_review_receipts'] LOOP
EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY',t);
EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY',t);
EXECUTE format('CREATE POLICY tenant_scope ON %I USING (tenant_id::text=current_setting(''app.tenant_id'',true)) WITH CHECK (tenant_id::text=current_setting(''app.tenant_id'',true))',t);
EXECUTE format('GRANT SELECT, INSERT ON %I TO rmm_app',t);
EXECUTE format('CREATE TRIGGER immutable_content BEFORE UPDATE OR DELETE ON %I FOR EACH ROW EXECUTE FUNCTION immutable_plan()',t);
END LOOP;
END $$;
-- Application roles have no membership UPDATE privilege. Lock both independently
-- provisioned bindings while the API validates and records a human confirmation.
CREATE FUNCTION lock_windows_checkpoint_reviewer(name_value text, issuer_value text, subject_value text)
RETURNS boolean LANGUAGE sql SECURITY DEFINER SET search_path=pg_catalog,public AS $$
SELECT EXISTS (
SELECT 1 FROM public.memberships m JOIN public.oidc_principals p USING (principal)
WHERE m.tenant_id='11111111-1111-4111-8111-111111111111'::uuid
AND m.tenant_id::text=current_setting('app.tenant_id',true) AND m.tenant_id=ANY(p.tenant_ids)
AND m.principal=name_value AND m.kind='human' AND m.revoked_at IS NULL
AND 'recovery:confirm'=ANY(m.permissions) AND NOT p.disabled
AND p.issuer=issuer_value AND p.subject=subject_value FOR SHARE OF m,p
);
$$;
REVOKE ALL ON FUNCTION lock_windows_checkpoint_reviewer(text,text,text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION lock_windows_checkpoint_reviewer(text,text,text) TO rmm_app;