jpetree331
f97d4845af
Complete RedFlag codebase with two major security audit implementations.
== A-1: Ed25519 Key Rotation Support ==
Server:
- SignCommand sets SignedAt timestamp and KeyID on every signature
- signing_keys database table (migration 020) for multi-key rotation
- InitializePrimaryKey registers active key at startup
- /api/v1/public-keys endpoint for rotation-aware agents
- SigningKeyQueries for key lifecycle management
Agent:
- Key-ID-aware verification via CheckKeyRotation
- FetchAndCacheAllActiveKeys for rotation pre-caching
- Cache metadata with TTL and staleness fallback
- SecurityLogger events for key rotation and command signing
== A-2: Replay Attack Fixes (F-1 through F-7) ==
F-5 CRITICAL - RetryCommand now signs via signAndCreateCommand
F-1 HIGH - v3 format: "{agent_id}:{cmd_id}:{type}:{hash}:{ts}"
F-7 HIGH - Migration 026: expires_at column with partial index
F-6 HIGH - GetPendingCommands/GetStuckCommands filter by expires_at
F-2 HIGH - Agent-side executedIDs dedup map with cleanup
F-4 HIGH - commandMaxAge reduced from 24h to 4h
F-3 CRITICAL - Old-format commands rejected after 48h via CreatedAt
Verification fixes: migration idempotency (ETHOS #4), log format
compliance (ETHOS #1), stale comments updated.
All 24 tests passing. Docker --no-cache build verified.
See docs/ for full audit reports and deviation log (DEV-001 to DEV-019).
Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
80 lines
3.9 KiB
SQL
80 lines
3.9 KiB
SQL
-- Event sourcing table for all update events
|
|
CREATE TABLE IF NOT EXISTS update_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
|
|
package_type VARCHAR(50) NOT NULL,
|
|
package_name TEXT NOT NULL,
|
|
version_from TEXT,
|
|
version_to TEXT NOT NULL,
|
|
severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'important', 'moderate', 'low')),
|
|
repository_source TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
event_type VARCHAR(20) NOT NULL CHECK (event_type IN ('discovered', 'updated', 'failed', 'ignored')),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Current state table for optimized queries
|
|
CREATE TABLE IF NOT EXISTS current_package_state (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
|
|
package_type VARCHAR(50) NOT NULL,
|
|
package_name TEXT NOT NULL,
|
|
current_version TEXT NOT NULL,
|
|
available_version TEXT,
|
|
severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'important', 'moderate', 'low')),
|
|
repository_source TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
last_discovered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
last_updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'updated', 'failed', 'ignored', 'installing')),
|
|
UNIQUE(agent_id, package_type, package_name)
|
|
);
|
|
|
|
-- Version history table for audit trails
|
|
CREATE TABLE IF NOT EXISTS update_version_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
|
|
package_type VARCHAR(50) NOT NULL,
|
|
package_name TEXT NOT NULL,
|
|
version_from TEXT NOT NULL,
|
|
version_to TEXT NOT NULL,
|
|
severity VARCHAR(20) NOT NULL CHECK (severity IN ('critical', 'important', 'moderate', 'low')),
|
|
repository_source TEXT,
|
|
metadata JSONB DEFAULT '{}',
|
|
update_initiated_at TIMESTAMP WITH TIME ZONE,
|
|
update_completed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
update_status VARCHAR(20) NOT NULL CHECK (update_status IN ('success', 'failed', 'rollback')),
|
|
failure_reason TEXT
|
|
);
|
|
|
|
-- Batch processing tracking
|
|
CREATE TABLE IF NOT EXISTS update_batches (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
|
|
batch_size INTEGER NOT NULL,
|
|
processed_count INTEGER DEFAULT 0,
|
|
failed_count INTEGER DEFAULT 0,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'processing' CHECK (status IN ('processing', 'completed', 'failed', 'cancelled')),
|
|
error_details JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
completed_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_agent_events ON update_events(agent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_package_events ON update_events(package_name, package_type);
|
|
CREATE INDEX IF NOT EXISTS idx_severity_events ON update_events(severity);
|
|
CREATE INDEX IF NOT EXISTS idx_created_events ON update_events(created_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_agent_state ON current_package_state(agent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_package_state ON current_package_state(package_name, package_type);
|
|
CREATE INDEX IF NOT EXISTS idx_severity_state ON current_package_state(severity);
|
|
CREATE INDEX IF NOT EXISTS idx_status_state ON current_package_state(status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_agent_history ON update_version_history(agent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_package_history ON update_version_history(package_name, package_type);
|
|
CREATE INDEX IF NOT EXISTS idx_completed_history ON update_version_history(update_completed_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_agent_batches ON update_batches(agent_id);
|
|
CREATE INDEX IF NOT EXISTS idx_batch_status ON update_batches(status);
|
|
CREATE INDEX IF NOT EXISTS idx_created_batches ON update_batches(created_at); |