smolkik-code
9b15f8b09c
- PostgreSQL schema: 14 tables, JSONB attributes, audit triggers, soft delete - FastAPI backend: CRUD, search/filter, relationship graph, bulk import, JWT RBAC - React frontend: CI table, detail card, force-graph, dashboard - Seed data: homelab scenario (Proxmox, Mikrotik, VMs, services) - Docker Compose + Kubernetes manifests - 20 backend tests (pytest + httpx)
3.1 KiB
3.1 KiB
Security & Operations Guide
PostgreSQL Security
User Privileges
-- Create limited user for the application
CREATE USER cmdb_app WITH PASSWORD 'strong_password';
GRANT CONNECT ON DATABASE cmdb TO cmdb_app;
GRANT USAGE ON SCHEMA public TO cmdb_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO cmdb_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO cmdb_app;
-- Read-only user for reporting
CREATE USER cmdb_reader WITH PASSWORD 'reader_password';
GRANT CONNECT ON DATABASE cmdb TO cmdb_reader;
GRANT USAGE ON SCHEMA public TO cmdb_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cmdb_reader;
SSL/TLS
# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_min_protocol_version = 'TLSv1.2'
# pg_hba.conf — force SSL for remote connections
hostssl cmdb cmdb_app 10.0.0.0/24 scram-sha-256
Backup Strategy
pg_dump (logical backup)
# Daily backup script
#!/bin/bash
BACKUP_DIR="/var/backups/cmdb"
DATE=$(date +%Y%m%d_%H%M%S)
pg_dump -h localhost -U cmdb cmdb | gzip > "$BACKUP_DIR/cmdb_$DATE.sql.gz"
# Keep 30 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +30 -delete
pg_basebackup (physical backup)
# For PITR (Point-in-Time Recovery)
pg_basebackup -h localhost -U replicator -D /var/lib/cmdb-backup \
-Fp -Xs -P -R
pgBackRest (enterprise)
# pgbackrest.conf
[cmdb]
pg1-path=/var/lib/postgresql/data
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
Recovery
# From pg_dump
dropdb cmdb && createdb cmdb
zcat /var/backups/cmdb/cmdb_20240101_030000.sql.gz | psql -U cmdb cmdb
# From WAL replay
restore_command = 'cp /var/lib/pgbackrest/archive/cmdb/%f %p'
recovery_target_time = '2024-01-01 12:00:00'
API Security
Rate Limiting
Configured in backend/.env: RATE_LIMIT_PER_MINUTE=120
CORS
# Restrict in production
CORS_ORIGINS=["https://cmdb.yourdomain.com"]
JWT Best Practices
- Rotate secrets quarterly
- Short expiration (60 min default)
- Store in HTTP-only cookies for web clients
- Validate
exp,iss,audclaims
Monitoring
Prometheus Metrics (add to FastAPI)
from prometheus_client import Counter, Histogram
REQUEST_COUNT = Counter('cmdb_requests_total', 'Total requests', ['method', 'endpoint'])
REQUEST_LATENCY = Histogram('cmdb_request_latency_seconds', 'Request latency', ['endpoint'])
Health Checks
# Backend
curl http://localhost:8000/api/health
# → {"status": "healthy", "version": "1.0.0"}
# PostgreSQL
pg_isready -h localhost -p 5432 -U cmdb
Log Aggregation
# docker-compose logging
logging:
driver: json-file
options:
max-size: "10m"
max-file: "3"
Hardening Checklist
- Run containers as non-root
- Use read-only filesystem mounts where possible
- Enable seccomp/AppArmor profiles
- Scan images with Trivy/Snyk
- Rotate database passwords regularly
- Audit changelog table weekly
- Monitor for failed login attempts
- Set up alerting for 5xx errors