Files
rust-discovery/migrations/0015_agent_update_catalog.sql
2026-11-14 21:48:21 +01:00

50 lines
2.5 KiB
SQL

-- Separate large update objects. Legacy APT/MSI object limits are unchanged.
CREATE TABLE agent_update_artifacts (
tenant_id uuid NOT NULL REFERENCES tenants,
artifact_id uuid NOT NULL,
sha256 text NOT NULL CHECK(sha256 ~ '^[0-9a-f]{64}$'),
size_bytes bigint NOT NULL CHECK(size_bytes BETWEEN 1 AND 268435456),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY(tenant_id,artifact_id),
UNIQUE(tenant_id,artifact_id,sha256,size_bytes)
);
CREATE TABLE agent_update_releases (
tenant_id uuid NOT NULL REFERENCES tenants,
release_id uuid NOT NULL,
platform text NOT NULL CHECK(platform IN ('x86_64-unknown-linux-gnu','x86_64-pc-windows-msvc')),
release_sequence bigint NOT NULL CHECK(release_sequence>0),
manifest_sha256 text NOT NULL CHECK(manifest_sha256 ~ '^[0-9a-f]{64}$'),
envelope_sha256 text NOT NULL CHECK(envelope_sha256 ~ '^[0-9a-f]{64}$'),
envelope bytea NOT NULL CHECK(octet_length(envelope) BETWEEN 1 AND 32768),
agent_artifact_id uuid NOT NULL,
agent_sha256 text NOT NULL,
agent_bytes bigint NOT NULL,
helper_artifact_id uuid NOT NULL,
helper_sha256 text NOT NULL,
helper_bytes bigint NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY(tenant_id,release_id),
UNIQUE(tenant_id,platform,release_sequence),
UNIQUE(tenant_id,manifest_sha256),
CHECK(agent_artifact_id<>helper_artifact_id),
FOREIGN KEY(tenant_id,agent_artifact_id,agent_sha256,agent_bytes)
REFERENCES agent_update_artifacts(tenant_id,artifact_id,sha256,size_bytes),
FOREIGN KEY(tenant_id,helper_artifact_id,helper_sha256,helper_bytes)
REFERENCES agent_update_artifacts(tenant_id,artifact_id,sha256,size_bytes)
);
ALTER TABLE agent_update_artifacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_update_artifacts FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON agent_update_artifacts
USING (tenant_id::text=current_setting('app.tenant_id',true))
WITH CHECK (tenant_id::text=current_setting('app.tenant_id',true));
ALTER TABLE agent_update_releases ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_update_releases FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON agent_update_releases
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 agent_update_artifacts,agent_update_releases TO rmm_app;
CREATE TRIGGER immutable_content BEFORE UPDATE OR DELETE ON agent_update_artifacts
FOR EACH ROW EXECUTE FUNCTION immutable_plan();
CREATE TRIGGER immutable_content BEFORE UPDATE OR DELETE ON agent_update_releases
FOR EACH ROW EXECUTE FUNCTION immutable_plan();