Files
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

59 lines
3.5 KiB
PL/PgSQL

CREATE TABLE tenants (tenant_id uuid PRIMARY KEY, name text NOT NULL);
INSERT INTO tenants VALUES
('11111111-1111-4111-8111-111111111111','Development A'),
('22222222-2222-4222-8222-222222222222','Development B');
CREATE TABLE enrollment_tokens (
token_hash text PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES tenants,
expires_at timestamptz NOT NULL, consumed_at timestamptz
);
CREATE TABLE assets (
tenant_id uuid NOT NULL REFERENCES tenants, asset_id uuid NOT NULL,
PRIMARY KEY(tenant_id,asset_id)
);
CREATE TABLE agent_installations (
tenant_id uuid NOT NULL, agent_id uuid NOT NULL, asset_id uuid NOT NULL,
certificate_sha256 text NOT NULL UNIQUE,
enrollment_generation bigint NOT NULL DEFAULT 1 CHECK(enrollment_generation=1),
connection_generation bigint NOT NULL DEFAULT 0,
revoked_at timestamptz, last_contact_at timestamptz,
PRIMARY KEY(tenant_id,agent_id),
FOREIGN KEY(tenant_id,asset_id) REFERENCES assets(tenant_id,asset_id)
);
CREATE TABLE observations (
tenant_id uuid NOT NULL, agent_id uuid NOT NULL, message_id uuid NOT NULL,
boot_id uuid NOT NULL, sequence bigint NOT NULL CHECK(sequence>0),
observed_at timestamptz NOT NULL, received_at timestamptz NOT NULL DEFAULT now(),
source_id text NOT NULL CHECK(source_id='linux.base'),
quality text NOT NULL CHECK(quality='valid'),
completeness text NOT NULL CHECK(completeness='partial'),
snapshot_id uuid NOT NULL, data jsonb NOT NULL, payload_sha256 text NOT NULL,
PRIMARY KEY(tenant_id,message_id),
UNIQUE(tenant_id,agent_id,sequence),
FOREIGN KEY(tenant_id,agent_id) REFERENCES agent_installations(tenant_id,agent_id)
);
-- Narrow identity bootstrap functions, owned only by the migration identity.
CREATE FUNCTION consume_enrollment(hash text) RETURNS uuid
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
UPDATE public.enrollment_tokens SET consumed_at=now()
WHERE token_hash=hash AND consumed_at IS NULL AND expires_at>now() RETURNING tenant_id;
$$;
CREATE FUNCTION device_binding(fingerprint text)
RETURNS TABLE(tenant_id uuid, agent_id uuid)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, public AS $$
SELECT a.tenant_id,a.agent_id FROM public.agent_installations a
WHERE a.certificate_sha256=fingerprint AND a.revoked_at IS NULL;
$$;
REVOKE ALL ON FUNCTION consume_enrollment(text), device_binding(text) FROM PUBLIC;
ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON tenants USING (tenant_id::text=current_setting('app.tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.tenant_id',true));
ALTER TABLE assets ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON assets USING (tenant_id::text=current_setting('app.tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.tenant_id',true));
ALTER TABLE agent_installations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON agent_installations USING (tenant_id::text=current_setting('app.tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.tenant_id',true));
ALTER TABLE observations ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON observations USING (tenant_id::text=current_setting('app.tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.tenant_id',true));
GRANT SELECT ON tenants,assets,agent_installations,observations TO rmm_app;
GRANT INSERT ON assets,agent_installations,observations TO rmm_app;
GRANT UPDATE(connection_generation,last_contact_at) ON agent_installations TO rmm_app;
GRANT EXECUTE ON FUNCTION consume_enrollment(text),device_binding(text) TO rmm_app;