Buckets:

download
raw
21.5 kB
"""
SQLAlchemy Database Models - Optimized with Indexes
PostgreSQL Database Schema für Zen-AI-Pentest.
Performance optimizations:
- Strategic indexes for common queries
- Connection pooling configuration
- Query optimization hints
"""
import enum
import logging
import os
from datetime import datetime, timezone
from sqlalchemy import JSON, Boolean, Column, DateTime, Float, ForeignKey, Index, Integer, String, Text, create_engine
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
logger = logging.getLogger(__name__)
Base = declarative_base()
# ============================================================================
# ENUMS
# ============================================================================
class ScanStatus(str, enum.Enum):
PENDING = "pending"
RUNNING = "running"
COMPLETED = "completed"
FAILED = "failed"
CANCELLED = "cancelled"
class Severity(str, enum.Enum):
CRITICAL = "critical"
HIGH = "high"
MEDIUM = "medium"
LOW = "low"
INFO = "info"
class ReportFormat(str, enum.Enum):
PDF = "pdf"
HTML = "html"
JSON = "json"
XML = "xml"
class ReportStatus(str, enum.Enum):
PENDING = "pending"
GENERATING = "generating"
COMPLETED = "completed"
FAILED = "failed"
# ============================================================================
# MODELS
# ============================================================================
class User(Base):
"""Benutzer-Account with optimized indexes"""
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
username = Column(String(100), unique=True, index=True, nullable=False)
email = Column(String(255), unique=True, index=True, nullable=False)
hashed_password = Column(String(255), nullable=False)
role = Column(String(50), default="operator", index=True)
is_active = Column(Integer, default=1, index=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))
# OAuth fields
oauth_provider = Column(String(50), nullable=True, index=True)
oauth_provider_id = Column(String(255), nullable=True, index=True)
profile_picture = Column(String(500), nullable=True)
last_login = Column(DateTime, nullable=True)
# Relationships
scans = relationship("Scan", back_populates="user", lazy="dynamic")
reports = relationship("Report", back_populates="user", lazy="dynamic")
targets = relationship("Target", back_populates="user", lazy="dynamic")
findings = relationship("Finding", back_populates="user", lazy="dynamic")
scheduled_scans = relationship("ScheduledScan", back_populates="user", lazy="dynamic")
# Composite index for common lookup pattern
__table_args__ = (
Index("ix_users_role_active", "role", "is_active"),
Index("ix_users_oauth", "oauth_provider", "oauth_provider_id"),
)
def __repr__(self):
return f"<User(username={self.username})>"
class Scan(Base):
"""Pentest Scan with performance indexes"""
__tablename__ = "scans"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(255), nullable=False, index=True)
target = Column(String(500), nullable=False, index=True)
scan_type = Column(String(100), nullable=False, index=True)
status = Column(String(50), default=ScanStatus.PENDING, index=True)
config = Column(JSON, default={})
result_summary = Column(Text)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
target_id = Column(Integer, ForeignKey("targets.id"), index=True, nullable=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
started_at = Column(DateTime, index=True)
completed_at = Column(DateTime, index=True)
# Relationships
user = relationship("User", back_populates="scans")
target_obj = relationship("Target", back_populates="scans")
findings = relationship(
"Finding",
back_populates="scan",
cascade="all, delete-orphan",
lazy="dynamic",
)
reports = relationship("Report", back_populates="scan", lazy="dynamic")
# Composite indexes for common query patterns
__table_args__ = (
# For filtering by status and date range
Index("ix_scans_status_created", "status", "created_at"),
# For user scans sorted by date
Index("ix_scans_user_created", "user_id", "created_at"),
# For target-based lookups
Index("ix_scans_target_type", "target", "scan_type"),
)
def __init__(self, **kwargs):
"""Auto-generate a name if one is not provided."""
if not kwargs.get("name"):
scan_type = kwargs.get("scan_type", "scan")
target = kwargs.get("target", "unknown")
kwargs["name"] = f"{scan_type}_{target}"
super().__init__(**kwargs)
def __repr__(self):
return f"<Scan(name={self.name})>"
class Target(Base):
"""Pentest target definition."""
__tablename__ = "targets"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(255), nullable=False, index=True)
host = Column(String(500), nullable=False, index=True)
description = Column(Text)
target_type = Column(String(50), default="domain", index=True)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
is_active = Column(Boolean, default=True, index=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
# Relationships
user = relationship("User", back_populates="targets")
scans = relationship("Scan", back_populates="target_obj", lazy="dynamic")
def __repr__(self):
return f"<Target(name={self.name})>"
class Finding(Base):
"""Sicherheits-Befund with optimized indexes"""
__tablename__ = "findings"
id = Column(Integer, primary_key=True, index=True)
scan_id = Column(Integer, ForeignKey("scans.id"), nullable=False, index=True)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
title = Column(String(500), nullable=False)
description = Column(Text)
severity = Column(String(50), default=Severity.MEDIUM, index=True)
cvss_score = Column(Float, index=True)
cve_id = Column(String(100), index=True)
evidence = Column(JSON)
remediation = Column(Text)
references = Column(JSON)
tool = Column(String(100), index=True)
target = Column(String(500), index=True)
port = Column(Integer, index=True)
service = Column(String(100))
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
verified = Column(Integer, default=0, index=True)
# Relationships
scan = relationship("Scan", back_populates="findings")
user = relationship("User", back_populates="findings")
# Composite indexes for efficient querying
__table_args__ = (
# For severity filtering within a scan
Index("ix_findings_scan_severity", "scan_id", "severity"),
# For verified findings by severity
Index("ix_findings_severity_verified", "severity", "verified"),
# For tool-based analysis
Index("ix_findings_tool_created", "tool", "created_at"),
# For target-based searches
Index("ix_findings_target_severity", "target", "severity"),
)
def __repr__(self):
return f"<Finding(title={self.title})>"
class Report(Base):
"""Generierter Bericht"""
__tablename__ = "reports"
id = Column(Integer, primary_key=True, index=True)
scan_id = Column(Integer, ForeignKey("scans.id"), index=True)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
name = Column(String(255), nullable=False, index=True)
description = Column(Text)
format = Column(String(50), default=ReportFormat.PDF)
template = Column(String(100))
status = Column(String(50), default=ReportStatus.PENDING, index=True)
file_path = Column(String(1000))
file_size = Column(Integer)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
generated_at = Column(DateTime)
# Relationships
scan = relationship("Scan", back_populates="reports")
user = relationship("User", back_populates="reports")
def __init__(self, **kwargs):
if not kwargs.get("name"):
scan_id = kwargs.get("scan_id", "0")
fmt = kwargs.get("format", "report")
kwargs["name"] = f"{fmt}-{scan_id}"
super().__init__(**kwargs)
__table_args__ = (Index("ix_reports_status_created", "status", "created_at"),)
class VulnerabilityDB(Base):
"""Lokale CVE/Exploit-Datenbank with full-text search support"""
__tablename__ = "vulnerabilities"
id = Column(Integer, primary_key=True, index=True)
cve_id = Column(String(100), unique=True, index=True, nullable=False)
title = Column(String(500))
description = Column(Text)
severity = Column(String(50), index=True)
cvss_score = Column(Float, index=True)
epss_score = Column(Float, index=True)
affected_products = Column(JSON)
references = Column(JSON)
exploits = Column(JSON)
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
__table_args__ = (
Index("ix_vulns_severity_cvss", "severity", "cvss_score"),
Index("ix_vulns_cve_updated", "cve_id", "updated_at"),
)
class Asset(Base):
"""Asset-Management with geographic and network indexes"""
__tablename__ = "assets"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(255), nullable=False, index=True)
asset_type = Column(String(100), index=True)
ip_address = Column(String(50), index=True)
hostname = Column(String(255), index=True)
os = Column(String(100))
services = Column(JSON)
owner = Column(String(255), index=True)
criticality = Column(String(50), default="medium", index=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
last_scanned = Column(DateTime, index=True)
__table_args__ = (
Index("ix_assets_type_criticality", "asset_type", "criticality"),
Index("ix_assets_owner_scanned", "owner", "last_scanned"),
)
class AuditLog(Base):
"""Audit-Logging für Compliance with time-series indexing"""
__tablename__ = "audit_logs"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
action = Column(String(100), nullable=False, index=True)
resource_type = Column(String(100), index=True)
resource_id = Column(Integer, index=True)
details = Column(JSON)
ip_address = Column(String(50))
user_agent = Column(String(500))
timestamp = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
__table_args__ = (
# For time-based audit queries
Index("ix_audit_user_timestamp", "user_id", "timestamp"),
Index("ix_audit_action_timestamp", "action", "timestamp"),
Index("ix_audit_resource", "resource_type", "resource_id"),
)
class Notification(Base):
"""Benachrichtigungen with read status indexing"""
__tablename__ = "notifications"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
type = Column(String(50), index=True)
title = Column(String(255))
message = Column(Text)
read = Column(Integer, default=0, index=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
__table_args__ = (
# For unread notifications per user
Index("ix_notifications_user_read", "user_id", "read", "created_at"),
)
class ToolConfig(Base):
"""Tool-Konfigurationen pro User/Team"""
__tablename__ = "tool_configs"
id = Column(Integer, primary_key=True, index=True)
user_id = Column(Integer, ForeignKey("users.id"), index=True)
tool_name = Column(String(100), nullable=False, index=True)
config = Column(JSON, default={})
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), index=True)
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))
__table_args__ = (Index("ix_toolconfig_user_tool", "user_id", "tool_name"),)
class ScheduledScan(Base):
"""Scheduled scan model"""
__tablename__ = "scheduled_scans"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(255), nullable=False)
target = Column(String(500), nullable=False)
scan_type = Column(String(100), nullable=False)
cron = Column(String(100))
config = Column(JSON, default={})
status = Column(String(50), default="active")
user_id = Column(Integer, ForeignKey("users.id"), index=True)
created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
updated_at = Column(DateTime, default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))
last_run = Column(DateTime)
next_run = Column(DateTime)
user = relationship("User", back_populates="scheduled_scans")
# ============================================================================
# DATABASE CONNECTION - OPTIMIZED
# ============================================================================
# PostgreSQL URL - in production aus Umgebungsvariablen
DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/zen_pentest")
# =============================================================================
# Connection Pool Configuration - Optimized
# =============================================================================
POOL_SIZE = int(os.getenv("DB_POOL_SIZE", "10"))
MAX_OVERFLOW = int(os.getenv("DB_MAX_OVERFLOW", "20"))
POOL_TIMEOUT = int(os.getenv("DB_POOL_TIMEOUT", "30"))
POOL_RECYCLE = int(os.getenv("DB_POOL_RECYCLE", "3600"))
POOL_PRE_PING = os.getenv("DB_POOL_PRE_PING", "true").lower() == "true"
# Connection Pool arguments - Optimized for production
engine_args = {
"pool_size": POOL_SIZE,
"max_overflow": MAX_OVERFLOW,
"pool_timeout": POOL_TIMEOUT,
"pool_recycle": POOL_RECYCLE,
"pool_pre_ping": POOL_PRE_PING,
"pool_use_lifo": True, # LIFO for better cache locality
"echo": os.getenv("DB_ECHO", "false").lower() == "true",
}
# Connection arguments for PostgreSQL
connect_args = {
"connect_timeout": 10,
"application_name": "zen-ai-pentest",
}
try:
if DATABASE_URL.startswith("sqlite"):
engine = create_engine(
DATABASE_URL,
connect_args={"check_same_thread": False},
**{k: v for k, v in engine_args.items() if k not in ["pool_use_lifo", "pool_pre_ping"]},
)
else:
# Try PostgreSQL with optimized settings
logger.info("Connecting to PostgreSQL with pool_size=%d, max_overflow=%d", POOL_SIZE, MAX_OVERFLOW)
engine = create_engine(
DATABASE_URL,
**engine_args,
connect_args=connect_args,
)
except ImportError:
# Fallback auf SQLite für lokale Entwicklung/Tests
logger.warning("PostgreSQL/psycopg2 not available. Using SQLite fallback (zen_pentest.db)")
DATABASE_URL = "sqlite:///./zen_pentest.db"
engine = create_engine(
DATABASE_URL,
connect_args={"check_same_thread": False},
**{k: v for k, v in engine_args.items() if k not in ["pool_use_lifo", "pool_pre_ping"]},
)
except Exception as e:
logger.warning("PostgreSQL connection failed (%s). Using SQLite fallback (zen_pentest.db)", e)
DATABASE_URL = "sqlite:///./zen_pentest.db"
engine = create_engine(
DATABASE_URL,
connect_args={"check_same_thread": False},
**{k: v for k, v in engine_args.items() if k not in ["pool_use_lifo", "pool_pre_ping"]},
)
# Optimized session factory
SessionLocal = sessionmaker(
autocommit=False,
autoflush=False,
bind=engine,
expire_on_commit=False, # Better performance for read-heavy workloads
)
def init_db():
"""Initialisiert die Datenbank mit all indexes"""
Base.metadata.create_all(bind=engine)
def get_db():
"""Dependency für FastAPI with automatic cleanup"""
db = SessionLocal()
try:
yield db
finally:
db.close()
# ============================================================================
# OPTIMIZED CRUD OPERATIONS
# ============================================================================
def create_scan(db, name: str, target: str, scan_type: str, config: dict, user_id: int):
"""Erstellt neuen Scan"""
db_scan = Scan(
name=name,
target=target,
scan_type=scan_type,
config=config,
user_id=user_id,
)
db.add(db_scan)
db.commit()
db.refresh(db_scan)
return db_scan
def get_scan(db, scan_id: int):
"""Holt Scan by ID - uses index on id"""
return db.query(Scan).filter(Scan.id == scan_id).first()
def get_scans(db, skip: int = 0, limit: int = 100, status: str = None):
"""Listet Scans auf - uses composite index"""
query = db.query(Scan)
if status:
query = query.filter(Scan.status == status)
# Use the composite index (status, created_at) or (user_id, created_at)
return query.order_by(Scan.created_at.desc()).offset(skip).limit(limit).all()
def get_scans_by_user(db, user_id: int, skip: int = 0, limit: int = 100):
"""Get scans for a specific user - uses composite index"""
return db.query(Scan).filter(Scan.user_id == user_id).order_by(Scan.created_at.desc()).offset(skip).limit(limit).all()
def update_scan_status(db, scan_id: int, status: str, result: dict = None):
"""Aktualisiert Scan-Status"""
scan = db.query(Scan).filter(Scan.id == scan_id).first()
if scan:
scan.status = status
if result:
scan.result_summary = str(result)
if status == "running":
scan.started_at = datetime.now(timezone.utc)
if status in ["completed", "failed"]:
scan.completed_at = datetime.now(timezone.utc)
db.commit()
db.refresh(scan)
return scan
def create_finding(
db,
scan_id: int,
title: str,
description: str,
severity: str = "medium",
cvss_score: float = None,
evidence: str = None,
tool: str = None,
target: str = None,
):
"""Erstellt neuen Befund"""
db_finding = Finding(
scan_id=scan_id,
title=title,
description=description,
severity=severity,
cvss_score=cvss_score,
evidence=evidence,
tool=tool,
target=target,
)
db.add(db_finding)
db.commit()
db.refresh(db_finding)
return db_finding
def bulk_create_findings(db, findings_data: list):
"""Efficiently create multiple findings using bulk insert"""
db.bulk_insert_mappings(Finding, findings_data)
db.commit()
def get_findings(db, scan_id: int, severity: str = None):
"""Holt Befunde für Scan - uses composite index"""
query = db.query(Finding).filter(Finding.scan_id == scan_id)
if severity:
query = query.filter(Finding.severity == severity)
return query.order_by(Finding.created_at.desc()).all()
def get_findings_by_severity(db, severity: str, limit: int = 100):
"""Get findings filtered by severity - uses index"""
return db.query(Finding).filter(Finding.severity == severity).order_by(Finding.created_at.desc()).limit(limit).all()
def create_report(db, scan_id: int, format: str, template: str, user_id: int):
"""Erstellt Report-Eintrag"""
db_report = Report(
scan_id=scan_id,
format=format,
template=template,
user_id=user_id,
status="pending",
)
db.add(db_report)
db.commit()
db.refresh(db_report)
return db_report
def get_reports(db, skip: int = 0, limit: int = 100):
"""Listet Reports auf"""
return db.query(Report).order_by(Report.created_at.desc()).offset(skip).limit(limit).all()
def create_audit_log(
db,
user_id: int,
action: str,
resource_type: str,
resource_id: int,
details: dict,
ip_address: str = None,
):
"""Erstellt Audit-Log-Eintrag"""
log = AuditLog(
user_id=user_id,
action=action,
resource_type=resource_type,
resource_id=resource_id,
details=details,
ip_address=ip_address,
)
db.add(log)
db.commit()
# Default Admin User erstellen
def create_default_admin():
"""Erstellt default Admin-User"""
db = SessionLocal()
try:
from passlib.context import CryptContext
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
admin = db.query(User).filter(User.username == "admin").first()
if not admin:
hashed = pwd_context.hash("admin")
admin = User(
username="admin",
email="admin@zen-pentest.local",
hashed_password=hashed,
role="admin",
)
db.add(admin)
db.commit()
logger.info("Default admin user created (admin/admin)")
finally:
db.close()
if __name__ == "__main__":
init_db()
create_default_admin()
logger.info("Database initialized with optimized indexes")

Xet Storage Details

Size:
21.5 kB
·
Xet hash:
f8e675bae052302233086d3dc4a7f5f9d308f339818975921101060b0505da01

Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.