Files
rust-discovery/migrations/0005_transport_controls.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

48 lines
3.6 KiB
PL/PgSQL

ALTER TABLE agent_installations ADD COLUMN transport text NOT NULL DEFAULT 'wss' CHECK(transport IN ('wss','https'));
ALTER TABLE agent_installations ADD COLUMN connection_boot_id uuid;
ALTER TABLE agent_installations ADD COLUMN last_sequence bigint NOT NULL DEFAULT 0;
ALTER TABLE agent_installations ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();
ALTER TABLE agent_installations ADD COLUMN restore_blocked boolean NOT NULL DEFAULT false;
ALTER TABLE agent_installations ADD COLUMN ingress_window timestamptz NOT NULL DEFAULT now();
ALTER TABLE agent_installations ADD COLUMN ingress_count integer NOT NULL DEFAULT 0;
GRANT UPDATE(restore_blocked,ingress_window,ingress_count) ON agent_installations TO rmm_app;
ALTER TABLE agent_installations ADD COLUMN previous_certificate_sha256 text;
ALTER TABLE agent_installations ADD COLUMN previous_certificate_until timestamptz;
ALTER TABLE agent_installations ADD COLUMN certificate_pem text;
ALTER TABLE agent_installations ADD COLUMN renewal_csr_sha256 text;
GRANT UPDATE(transport,connection_boot_id,last_sequence,previous_certificate_sha256,previous_certificate_until,certificate_pem,renewal_csr_sha256,certificate_sha256,revoked_at) ON agent_installations TO rmm_app;
GRANT UPDATE(changes_stopped) ON tenants TO rmm_app;
CREATE OR REPLACE 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.revoked_at IS NULL AND (a.certificate_sha256=fingerprint OR
(a.previous_certificate_sha256=fingerprint AND a.previous_certificate_until>now()));
$$;
CREATE TABLE memberships (
tenant_id uuid NOT NULL REFERENCES tenants, principal text NOT NULL,
kind text NOT NULL CHECK(kind IN ('viewer','automation','test_human')),
permissions text[] NOT NULL, revoked_at timestamptz,
PRIMARY KEY(tenant_id,principal)
);
INSERT INTO memberships SELECT tenant_id,'viewer:'||tenant_id,'viewer',ARRAY['inventory:read'],NULL FROM tenants;
INSERT INTO memberships SELECT tenant_id,'automation:'||tenant_id,'automation',ARRAY['inventory:read','logs:read','operations:read','operations:plan','operations:submit','jobs:cancel','scripts:execute'],NULL FROM tenants;
INSERT INTO memberships VALUES('11111111-1111-4111-8111-111111111111','simulated-testprincipal-a','test_human',ARRAY['inventory:read','logs:read','operations:read','operations:plan','operations:submit','jobs:cancel','scripts:execute','plans:approve','plans:revoke','devices:revoke','tenant:stop','catalog:write'],NULL);
ALTER TABLE memberships ENABLE ROW LEVEL SECURITY;
ALTER TABLE memberships FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON memberships USING(tenant_id::text=current_setting('app.tenant_id',true)) WITH CHECK(tenant_id::text=current_setting('app.tenant_id',true));
GRANT SELECT ON memberships TO rmm_app;
-- Registry is intentionally explicit in this development profile; no worker RLS bypass.
CREATE TABLE tenant_limits (
tenant_id uuid PRIMARY KEY REFERENCES tenants,
starts_per_hour integer NOT NULL DEFAULT 100 CHECK(starts_per_hour BETWEEN 1 AND 10000),
max_parallel integer NOT NULL DEFAULT 10 CHECK(max_parallel BETWEEN 1 AND 100)
);
INSERT INTO tenant_limits(tenant_id) SELECT tenant_id FROM tenants;
ALTER TABLE tenant_limits ENABLE ROW LEVEL SECURITY;
ALTER TABLE tenant_limits FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_scope ON tenant_limits USING(tenant_id::text=current_setting('app.tenant_id',true)) WITH CHECK(tenant_id::text=current_setting('app.tenant_id',true));
GRANT SELECT ON tenant_limits TO rmm_app;
ALTER TABLE jobs ADD COLUMN pending_reason text;
GRANT UPDATE(pending_reason) ON jobs TO rmm_app;