# Security & Operations Guide

## PostgreSQL Security

### User Privileges
```sql
-- 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
```ini
# 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'
```

```bash
# 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)
```bash
# 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)
```bash
# For PITR (Point-in-Time Recovery)
pg_basebackup -h localhost -U replicator -D /var/lib/cmdb-backup \
  -Fp -Xs -P -R
```

### pgBackRest (enterprise)
```ini
# pgbackrest.conf
[cmdb]
pg1-path=/var/lib/postgresql/data
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
```

### Recovery
```bash
# 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
```python
# 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`, `aud` claims

## Monitoring

### Prometheus Metrics (add to FastAPI)
```python
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
```bash
# Backend
curl http://localhost:8000/api/health
# → {"status": "healthy", "version": "1.0.0"}

# PostgreSQL
pg_isready -h localhost -p 5432 -U cmdb
```

### Log Aggregation
```yaml
# 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