27 lines
1.9 KiB
SQL
27 lines
1.9 KiB
SQL
ALTER TABLE agent_installations ADD COLUMN sync_required boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE agent_installations ADD COLUMN sync_ready boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE agent_installations ADD COLUMN helper_sync_ready boolean NOT NULL DEFAULT false;
|
|
ALTER TABLE agent_installations ADD COLUMN sequence_regressed boolean NOT NULL DEFAULT false;
|
|
GRANT UPDATE(sync_required,sync_ready,helper_sync_ready,sequence_regressed) ON agent_installations TO rmm_app;
|
|
CREATE TABLE agent_sync_state (
|
|
tenant_id uuid NOT NULL, agent_id uuid NOT NULL, journal_id uuid NOT NULL,
|
|
checkpoint bigint NOT NULL, decision_id uuid NOT NULL,
|
|
helper_journal_id uuid, helper_checkpoint bigint NOT NULL DEFAULT 0,
|
|
PRIMARY KEY(tenant_id,agent_id), FOREIGN KEY(tenant_id,agent_id) REFERENCES agent_installations(tenant_id,agent_id)
|
|
);
|
|
CREATE TABLE sync_checkpoints (
|
|
tenant_id uuid NOT NULL, agent_id uuid NOT NULL, request_id uuid NOT NULL,
|
|
decision_id uuid NOT NULL, request_hash text NOT NULL, journal_id uuid NOT NULL,
|
|
checkpoint bigint NOT NULL, connection_generation bigint NOT NULL,
|
|
payload bytea NOT NULL, signature bytea NOT NULL, created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY(tenant_id,agent_id,request_id), UNIQUE(tenant_id,agent_id,decision_id),
|
|
FOREIGN KEY(tenant_id,agent_id) REFERENCES agent_installations(tenant_id,agent_id)
|
|
);
|
|
DO $$ DECLARE t text; BEGIN FOREACH t IN ARRAY ARRAY['agent_sync_state','sync_checkpoints'] 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);
|
|
END LOOP;END $$;
|
|
GRANT UPDATE(checkpoint,decision_id,helper_checkpoint,helper_journal_id) ON agent_sync_state TO rmm_app;
|