Security Scan

Implement production-grade database security scanning for PostgreSQL and MySQL that detects 50+ security vulnerabilities including weak passwords, excessive permissions, SQL injection vectors, missing encryption, exposed ports, and insecure configurations. Provides OWASP Database Security compliance reports, automated remediation scripts, and continuous security monitoring with SOC2/HIPAA/PCI DSS audit trails.

When to Use This Command

Use /security-scan when you need to:

• Perform security audits for compliance (SOC2, HIPAA, PCI DSS)
• Detect vulnerabilities before security incidents occur
• Validate database hardening after deployment
• Generate security reports for stakeholders and auditors
• Implement continuous security scanning in CI/CD pipeline
• Identify privilege escalation risks and over-permissioned users

DON'T use this when:

• You lack permission to query security-sensitive system tables
• Database is in active development (expect frequent config changes)
• You need application-level security testing (use SAST/DAST tools)
• Cloud-managed database with built-in security scanning (use AWS RDS/CloudSQL tools)
• You lack remediation authority (read-only security assessment)

Design Decisions

This command implements comprehensive multi-layer security scanning because:

• Checks 50+ OWASP Database Security vulnerabilities
• Validates encryption at rest and in transit (SSL/TLS)
• Detects privilege escalation and over-permissioned roles
• Identifies SQL injection vectors in stored procedures
• Scans for weak authentication and default credentials
• Generates automated remediation scripts for findings

Alternative considered: Manual security checklist

• Simple for small databases (<10 users, <50 tables)
• Time-consuming and error-prone for large databases
• No continuous monitoring or automation
• Recommended for one-time assessments only

Alternative considered: Commercial security tools (Tenable, Qualys)

• More comprehensive (network scanning, OS hardening)
• Expensive licensing ($10k-50k/year)
• Better for enterprise-wide security programs
• Recommended when budget allows and compliance requires

Prerequisites

Before running this command:

1. Database superuser or security admin permissions
2. Access to system catalogs (pg_catalog, information_schema)
3. Understanding of compliance requirements (PCI DSS, HIPAA, SOC2)
4. Authority to implement remediation recommendations
5. Backup before applying security hardening changes

Implementation Process

Step 1: Connect with Security Admin Privileges

Ensure connection has permissions to query user roles, grants, and configurations.

Step 2: Run Vulnerability Scans

Check authentication, authorization, encryption, auditing, and network security.

Step 3: Analyze Findings

Categorize vulnerabilities by severity (critical, high, medium, low).

Step 4: Generate Remediation Scripts

Create SQL scripts to fix identified issues with rollback procedures.

Step 5: Validate and Re-scan

Apply fixes in staging, validate, then re-scan to confirm remediation.

Output Format

The command generates:

• security_report.md - Human-readable security audit report with severity ratings
• vulnerabilities.json - Machine-readable findings for CI/CD integration
• remediation.sql - SQL script to fix identified vulnerabilities
• compliance_matrix.xlsx - Mapping to SOC2/HIPAA/PCI DSS controls
• security_baseline.yml - Configuration baseline for future scans

Code Examples

Example 1: PostgreSQL Comprehensive Security Scanner

#!/usr/bin/env python3
"""
Production-ready PostgreSQL security scanner implementing OWASP
Database Security Project checks with automated remediation.
"""

import psycopg2
from psycopg2.extras import RealDictCursor
from typing import List, Dict, Optional
from dataclasses import dataclass
from enum import Enum
import json
import logging
from datetime import datetime

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)
…

Error Handling

Configuration Options

Scan Scope

• Full scan: All 50+ security checks (recommended for compliance)
• Quick scan: Critical and high severity only (fast assessment)
• Compliance scan: Specific to SOC2/HIPAA/PCI DSS requirements
• Custom scan: User-defined check selection

Severity Thresholds

• Critical: Immediate exploitation risk (exposed passwords, SQL injection)
• High: Significant security impact (excessive permissions, no encryption)
• Medium: Security weakness (insecure config, missing hardening)