Files
rust-discovery/migrations/0012_certificate_evidence.sql
BuTzZ 6c3203d725
Rust development slice / linux (push) Failing after 1m10s
feat: snapshot Linux-first RMM MVP and pause handover
2026-11-12 22:06:25 +01:00

29 lines
1.6 KiB
PL/PgSQL

-- Issued/registered certificate identity is not evidence that it was used.
-- Do not infer/backfill authentication from last_contact_at or the current pin.
ALTER TABLE agent_installations
ADD COLUMN last_authenticated_certificate_sha256 text
CHECK(last_authenticated_certificate_sha256 ~ '^[0-9a-f]{64}$'),
ADD COLUMN last_authenticated_at timestamptz,
ADD CONSTRAINT authentication_evidence_pair CHECK
((last_authenticated_certificate_sha256 IS NULL) = (last_authenticated_at IS NULL));
-- Every caller passes only the leaf DER SHA-256 obtained from verified rustls
-- peer identity, never an HTTP header/agent payload. Keep binding and evidence
-- atomic so revocation/expired overlap cannot leave a false authentication row.
-- This narrowly scoped SECURITY DEFINER function already owns identity lookup;
-- do not grant the application general UPDATE rights on the evidence columns.
CREATE OR REPLACE FUNCTION device_binding(fingerprint text)
RETURNS TABLE(tenant_id uuid, agent_id uuid)
LANGUAGE sql VOLATILE SECURITY DEFINER SET search_path = pg_catalog, public AS $$
UPDATE public.agent_installations AS a
SET last_authenticated_certificate_sha256=fingerprint,
last_authenticated_at=clock_timestamp()
WHERE a.revoked_at IS NULL
AND fingerprint ~ '^[0-9a-f]{64}$'
AND (a.certificate_sha256=fingerprint OR
(a.previous_certificate_sha256=fingerprint AND a.previous_certificate_until>clock_timestamp()))
RETURNING a.tenant_id,a.agent_id;
$$;
REVOKE ALL ON FUNCTION device_binding(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION device_binding(text) TO rmm_app;