Database Security - SysManage

UUID-Based Primary Key Architecture

SysManage implements UUID-based primary keys across all database tables instead of traditional sequential integer IDs. This architectural decision provides significant security advantages and prevents common attack vectors.

Security Benefits

🛡️ ID Enumeration Attack Prevention

Sequential IDs (1, 2, 3...) allow attackers to easily enumerate resources. UUIDs eliminate this vulnerability by using non-predictable identifiers.

❌ Vulnerable (Sequential):

GET /api/hosts/1, /api/hosts/2, /api/hosts/3...

✅ Secure (UUID):

GET /api/hosts/a1b2c3d4-e5f6-7890-abcd-ef1234567890

🔄 Replay Attack Protection

Non-sequential identifiers prevent attackers from predicting valid resource IDs for replay attacks or automated scanning.

🔒 Information Leakage Prevention

Sequential IDs can reveal business metrics (number of users, hosts, etc.). UUIDs prevent this information disclosure.

🕵️ Enhanced Privacy Protection

UUIDs provide non-correlatable identifiers that enhance user privacy and prevent cross-system tracking.

📋 GDPR Compliance Support

UUIDs support data anonymization requirements by providing identifiers that cannot be easily correlated across systems.

🌐 Distributed System Security

UUIDs are globally unique, preventing ID conflicts in distributed systems and reducing attack surface in multi-tenant environments.

Secure Installation Script

SysManage includes a mandatory secure installation script that must be run on new installations to establish proper security foundations.

Script Location

scripts/sysmanage_secure_installation

Security Features Implemented

👤 Initial Admin User Creation

Prompts for secure administrator password
Enforces password complexity requirements
Prevents use of default or example credentials

🔐 JWT Security Token Generation

Generates cryptographically secure unique JWT secret
Uses high-entropy random data for token generation
Prevents credential reuse across installations

🧂 Password Salt Generation

Creates unique password salt for each installation
Prevents rainbow table attacks
Enhances password hashing security

⚙️ Configuration File Hardening

Sets restrictive file permissions (600)
Ensures proper file ownership
Validates configuration security settings

⚠️ Security Requirements

Mandatory execution: The script MUST be run before first use
Run once only: Execute only once per installation
Configuration backup: Backup configuration file after script execution
No default credentials: System will not function with default security tokens

Implementation Details

Database Schema Design

All primary keys in SysManage use UUID version 4 (random) for maximum security:

Example Table Definitions:

-- Users table with UUID primary key
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Hosts table with UUID primary key and foreign key
CREATE TABLE hosts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hostname VARCHAR(255) NOT NULL,
    owner_id UUID REFERENCES users(id),
    approved_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

API Security Considerations

UUID Validation

All UUID inputs are validated for proper format
Pydantic models enforce UUID typing
Invalid UUIDs are immediately rejected

Authorization Checks

Resource ownership validated before UUID resolution
Role-based access control applied to UUID-referenced resources
No information disclosure even for invalid UUIDs

Database Security Best Practices

🏭 Production Deployment

Enable database encryption at rest
Use SSL/TLS for database connections
Store database credentials securely
Restrict database network access
Implement database activity monitoring

💾 Backup and Recovery

Use encrypted database backups
Store backups in secure locations
Implement backup access controls
Regularly test backup restoration
Follow data retention policies

🔧 Maintenance and Updates

Apply security patches promptly
Test database migrations in staging
Review schema changes for security implications
Maintain comprehensive audit logs
Monitor database performance and security metrics

Migration from Sequential IDs

SysManage has been fully migrated from sequential integer IDs to UUIDs using a comprehensive migration strategy.

Migration Process

Step 1: Schema Migration

Alembic migrations converted all primary keys and foreign keys from integers to UUIDs

Step 2: Data Migration

Existing data was migrated with new UUID identifiers while preserving relationships

Step 3: API Updates

All API endpoints updated to handle UUID parameters and responses

Step 4: Frontend Updates

Frontend components updated to work with UUID identifiers

Post-Migration Security Benefits

Dramatically reduced attack surface
Eliminated ID enumeration vulnerabilities
Prevented information disclosure through predictable IDs
Enhanced regulatory compliance posture
Future-proofed against sequential ID-based attacks