Database query anomaly detection

Severity: Recommended (Critical for big projects)

Large database queries returning unusual amounts of data can indicate exfiltration via application abuse or compromised credentials. Monitoring should detect queries that return abnormally large result sets.

Check automatically:

1. Check for database audit logging:

# PostgreSQL pgaudit extension
grep -rE "pgaudit|shared_preload_libraries.*audit" --include="*.sql" --include="*.conf" --include="*.tf" 2>/dev/null

# MySQL audit plugin
grep -rE "audit_log|server_audit" --include="*.sql" --include="*.cnf" --include="*.tf" 2>/dev/null

# Check cloud database audit settings
# AWS RDS
aws rds describe-db-parameters --db-parameter-group-name default.postgres14 --query "Parameters[?ParameterName=='pgaudit.log']" 2>/dev/null

# GCP Cloud SQL
gcloud sql instances describe INSTANCE_NAME --format="get(settings.databaseFlags)" 2>/dev/null | grep -i audit

2. Check for slow query logging with row counts:

# PostgreSQL slow query log
grep -rE "log_min_duration_statement|log_statement" --include="*.conf" --include="*.tf" --include="*.sql" 2>/dev/null

# MySQL slow query log
grep -rE "slow_query_log|long_query_time" --include="*.cnf" --include="*.tf" 2>/dev/null

3. Check for application-level query monitoring:

# Look for query logging middleware
grep -rE "query.*log|prisma.*log|typeorm.*logging|sequelize.*logging" --include="*.ts" --include="*.js" src/ 2>/dev/null

# Look for row count tracking
grep -rE "rowCount|affectedRows|rows\.length|count\(\)" --include="*.ts" --include="*.js" src/ 2>/dev/null

# Look for query result size alerts
grep -rE "result.*size|rows.*threshold|bulk.*warning" --include="*.ts" --include="*.js" src/ 2>/dev/null

4. Check for database proxy/firewall:

# ProxySQL, PgBouncer, or similar with logging
grep -rE "proxysql|pgbouncer|mysql-proxy" --include="docker-compose*" --include="*.tf" --include="*.yml" 2>/dev/null

# Database firewall rules
grep -rE "db.*firewall|database.*rules|query.*filter" --include="*.tf" --include="*.yml" 2>/dev/null

5. Check cloud database insights/monitoring:

# AWS Performance Insights
aws rds describe-db-instances --query "DBInstances[].{DBInstanceIdentifier:DBInstanceIdentifier,PerformanceInsightsEnabled:PerformanceInsightsEnabled}" 2>/dev/null

# GCP Query Insights
gcloud sql instances describe INSTANCE_NAME --format="get(settings.insightsConfig)" 2>/dev/null

Ask user:

• "Do you log database queries with row counts?"
• "What would constitute an unusually large query result?"
• "How would you detect if someone exported your entire user table?"

Anomaly patterns to detect:

• Single query returning >10,000 rows
• Multiple SELECT * queries in short timeframe
• Queries on sensitive tables (users, payments) without normal access patterns
• After-hours database activity

Cross-reference with:

• IDS-001 (general IDS)
• IDS-002 (network-level - exfiltration may show in both)
• MON-002 (database performance monitoring from section 12)
• DB-004 (database users from section 5)

Pass criteria:

• Database queries logged with row counts or data volume
• Thresholds defined for "unusual" query sizes
• Alerts trigger on threshold breach
• Someone reviews database activity regularly

Fail criteria:

• No query logging beyond basic slow query logs
• No awareness of what "normal" query patterns look like
• No alerting on large result sets
• "We'd notice if the database was slow"

Evidence to capture:

• Query logging mechanism (database native, proxy, application)
• Row count/data volume tracking
• Thresholds configured
• Last review of database activity logs

---