🛡️ PSonar - PostgreSQL Security Auditor

🇹🇷 Türkçe README

PSonar is an enterprise-grade security auditing tool for PostgreSQL databases. It performs comprehensive read-only security assessments aligned with CIS PostgreSQL Benchmark and MITRE ATT&CK framework.

✨ Features

🔒 Core Security

• Read-Only Operations: All operations are strictly read-only - no data modification
• CIS Benchmark Compliance: 20+ security controls from CIS PostgreSQL Benchmark
• MITRE ATT&CK Mapping: Findings mapped to relevant attack techniques
• Risk Scoring: Comprehensive risk analysis with prioritized recommendations

🔍 Advanced Auditing

• Vulnerability Scanner: CVE detection, version EOL checks, risky extensions
• Configuration Audit: Best practices comparison, 20+ setting validations
• Sensitive Data Discovery: PII, financial data, credentials detection (GDPR, PCI-DSS, HIPAA, KVKK)
• User & Privilege Audit: Superuser detection, privilege escalation paths, password policies
• Session Monitoring: Active sessions, blocking queries, suspicious activities
• Schema Security: SECURITY DEFINER functions, triggers, views (SECURITY BARRIER), FDW credential exposure
• Database Health: Connection stats, cache ratios, bloat detection, vacuum status

📊 Enterprise Features

• Enterprise Security Controls: pgaudit, RLS, encryption, replication, backup checks
• Compliance Scoring: CIS, PCI-DSS, SOC2, HIPAA, GDPR readiness scores
• Beautiful Reports: Professional HTML reports with dark mode support
• Multi-Database Support: Scan multiple databases from config file
• SSL Support: Full SSL/TLS support for secure connections

🚀 Quick Start

Installation

# Clone the repository
git clone https://github.com/cumakurt/psonar.git
cd psonar

# Install dependencies
pip install -r requirements.txt

# Or install as package
pip install -e .

PyPI Installation (Coming Soon)

pip install psonar

Docker

# Build
docker build -t psonar .

# Run (reports stay inside container unless you mount a volume)
docker run -it psonar scan -h your-db-host -d mydb -U postgres

Saving Reports on the Host (Volume Mount)

By default, reports are written inside the container at /app/reports. To get them on your host machine, mount a volume with -v.

Recommended: Save reports to host /tmp (avoids permission issues):

docker run -it -v /tmp:/app/reports -e PGPASSWORD=yourpassword psonar scan -h your-db-host -d mydb -U postgres
# Report will appear on host at: /tmp/psonar_<host>_<port>_<db>_<timestamp>.html

Save reports to current directory (if you get "Permission denied", add --user root):

docker run -it -v "$(pwd):/app/reports" --user root -e PGPASSWORD=yourpassword psonar scan -h your-db-host -d mydb -U postgres

Save reports to a reports folder on the host:

mkdir -p reports
docker run -it -v "$(pwd)/reports:/app/reports" --user root -e PGPASSWORD=yourpassword psonar scan -h your-db-host -d mydb -U postgres

Note: The container runs as non-root user psonar. If the mounted directory is not writable by that user, use --user root or mount a writable path such as /tmp.

Basic Usage

# Scan a single database
psonar scan -h localhost -d mydb -U postgres

# Scan with password prompt
psonar scan -h localhost -d mydb -U postgres -W

# Scan multiple databases from config
psonar scan -c config/databases.yaml

# Run specific checks only
psonar scan -h localhost -d mydb -U postgres --checks CIS-3.1,CIS-4.1,CIS-6.1

# Skip health or CIS checks
psonar scan -h localhost -d mydb -U postgres --skip-health
psonar scan -h localhost -d mydb -U postgres --skip-cis

# Output format: html (default) or json
psonar scan -h localhost -d mydb -U postgres -o ./reports -f html

# Generate sample config file
psonar init-config -o my-config.yaml

# List available checks
psonar list-checks

Python API

from psonar import PSonarScanner

# Using context manager
with PSonarScanner(
    host="localhost",
    database="mydb",
    username="postgres",
    password="secret"
) as scanner:
    scanner.run_health_checks()
    scanner.run_cis_checks()
    scanner.calculate_risk_scores()

# Or run full scan
from psonar.main import scan_database

report = scan_database(
    host="localhost",
    database="mydb",
    username="postgres",
    password="secret",
    output_path="./report.html"
)

print(f"Compliance: {report.compliance_score['compliance_percentage']}%")

📋 Security Checks

Audit Modules

Module	Description	Checks
🔓 Vulnerability Scanner	CVE detection, version support	8+ known CVEs, EOL checks
⚙️ Configuration Audit	Best practices comparison	20+ settings validation
👥 User & Privilege Audit	Access control analysis	Superusers, privileges, passwords
🔍 Sensitive Data Discovery	PII/secrets detection	Email, CC, SSN, API keys
📊 Session Monitoring	Real-time session analysis	Blocking, long queries
🏗️ Schema Security	Object security audit	SECURITY DEFINER functions, triggers, views, FDW credentials
🗄️ Database Health	Performance & health	Connections, cache, bloat
🏢 Enterprise Security	Compliance controls	pgaudit, RLS, encryption
📜 CIS Benchmark	Industry standard	21 CIS controls
🎯 MITRE ATT&CK	Threat mapping	Attack technique correlation

CIS PostgreSQL Benchmark Controls

ID	Category	Control	Severity
CIS-3.1	Authentication	Password encryption	HIGH
CIS-3.2	Authentication	Password expiration policy	MEDIUM
CIS-4.1	Privilege Management	Superuser role count	HIGH
CIS-4.2	Privilege Management	Public schema privileges	HIGH
CIS-4.3	Privilege Management	Default privileges	MEDIUM
CIS-5.1	Privilege Management	Dangerous extensions	MEDIUM
CIS-5.2	Privilege Management	Untrusted procedural languages	HIGH
CIS-6.1	Connection Security	SSL enabled	CRITICAL
CIS-6.2	Connection Security	SSL required for remote	HIGH
CIS-6.3	Authentication	Trust authentication	CRITICAL
CIS-6.4	Authentication	Password auth method	HIGH
CIS-7.1	Logging	Log connections	MEDIUM
CIS-7.2	Logging	Log disconnections	MEDIUM
CIS-7.3	Logging	Log statement	MEDIUM
CIS-7.4	Logging	Log statement duration	LOW
CIS-7.5	Logging	Log hostname	LOW
CIS-8.1	Network	Listen addresses	MEDIUM
CIS-8.2	Network	Port configuration	LOW
CIS-9.1	Data Protection	Data checksums	MEDIUM
CIS-10.1	Maintenance	Autovacuum	HIGH
CIS-10.2	Maintenance	Backup configuration	MEDIUM

Sensitive Data Types Detected

Category	Types
PII	Email, Phone, SSN, National ID, Passport, Name, Address, DOB
Financial	Credit Card, Bank Account, IBAN
Credentials	Password, API Key, Secret Key, Token, Private Key
Health	Medical records, Patient ID
Other	IP Address, Geolocation, Biometric

Vulnerability Checks

• PostgreSQL version EOL status
• Known CVEs (CVE-2024-7348, CVE-2024-0985, CVE-2023-5869, etc.)
• Security update availability
• Risky extensions (plpythonu, file_fdw, dblink, adminpack)

🎯 MITRE ATT&CK Mapping

Findings are mapped to relevant MITRE ATT&CK techniques:

Tactic	Techniques
Credential Access	T1110 (Brute Force), T1552 (Unsecured Credentials), T1040 (Network Sniffing)
Privilege Escalation	T1068 (Exploitation), T1078 (Valid Accounts)
Persistence	T1098 (Account Manipulation), T1505 (Server Software Component)
Defense Evasion	T1562 (Impair Defenses), T1070 (Indicator Removal)

📊 Risk Scoring

PSonar calculates risk scores based on:

• Base Severity: CIS benchmark severity (Low to Critical)
• Exposure Factor: Category-based multiplier
• MITRE Factor: Attack technique relevance

Risk Levels:

• 🟢 Low (0-3.9): Minor issues
• 🟡 Medium (4-6.9): Should be addressed
• 🟠 High (7-8.9): Prioritize remediation
• 🔴 Critical (9-10): Immediate action required

📄 Configuration

YAML Configuration

# psonar.yaml
databases:
  - name: "production"
    host: "prod-db.example.com"
    port: 5432
    database: "myapp"
    username: "auditor"
    password: "${DB_PASSWORD}"  # Environment variable
    ssl_mode: "require"

settings:
  connect_timeout: 10
  query_timeout: 30

output:
  directory: "./reports"
  format: "html"

Environment Variables

export DB_PASSWORD="your-secure-password"
export STAGING_DB_PASSWORD="staging-password"

🔒 Security Considerations

Read-Only Guarantee

PSonar enforces strict read-only operations:

1. Transaction Mode: All connections use transaction_read_only = on
2. Query Validation: All queries are validated before execution
3. Forbidden Operations: INSERT, UPDATE, DELETE, DROP, etc. are blocked
4. Catalog Only: Queries only access pg_catalog and information_schema

Recommended Audit User

Create a dedicated read-only user for auditing:

-- Create audit role
CREATE ROLE security_auditor WITH LOGIN PASSWORD 'secure_password';

-- Grant read access to system catalogs (already available)
GRANT CONNECT ON DATABASE mydb TO security_auditor;

-- Grant pg_read_all_settings for configuration access
GRANT pg_read_all_settings TO security_auditor;

-- Grant pg_read_all_stats for statistics access  
GRANT pg_read_all_stats TO security_auditor;

-- For pg_hba.conf access (PostgreSQL 10+)
GRANT pg_read_all_data TO security_auditor;

SSL Configuration

Always use SSL for production scans:

psonar scan -h prod-db.example.com --ssl-mode require

📈 Report Output

Reports can be generated in HTML (default) or JSON format (-f html or -f json). HTML reports are self-contained, support dark mode, and include developer information in the footer.

Report sections include:

1. Executive Summary: Compliance score, key findings, risk overview
2. Vulnerability Scan: CVEs, version status, risky extensions
3. Configuration Audit: Settings compliance, best practices
4. User & Privilege Audit: Access control analysis, risk users
5. Sensitive Data Discovery: PII/credential locations (GDPR, PCI-DSS, HIPAA, KVKK)
6. Session Monitoring: Active sessions, blocking analysis
7. Schema Security: SECURITY DEFINER functions, triggers, views, FDW credential exposure
8. Database Health: Performance metrics and warnings
9. Enterprise Security: Compliance controls status
10. CIS Compliance Table: All 21 checks with status and evidence
11. MITRE ATT&CK Mapping: Techniques and tactics coverage
12. Risk Analysis: Scored findings with prioritization
13. Recommendations: Actionable remediation steps

Sample Output

┌──────────────────────────────────────────────────────────────────┐
│                     Scan Results Summary                         │
├────────────────────┬─────────────────────────────────────────────┤
│ Compliance Score   │ 75.0%                                       │
│ Checks Passed      │ 15                                          │
│ Checks Failed      │ 5                                           │
│ Warnings           │ 3                                           │
│ Overall Risk       │ Medium                                      │
└────────────────────┴─────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────┐
│                   🔓 Vulnerability Scan                          │
├────────────────────┬─────────────────────────────────────────────┤
│ PostgreSQL Version │ 14.5                                        │
│ Support Status     │ ✓ Supported                                 │
│ CVEs Found         │ 2                                           │
│ Risky Extensions   │ 1                                           │
└────────────────────┴─────────────────────────────────────────────┘

┌──────────────────────────────────────────────────────────────────┐
│                   👥 User & Privilege Audit                      │
├────────────────────┬─────────────────────────────────────────────┤
│ Total Users        │ 12                                          │
│ Superusers         │ 2                                           │
│ Critical Risk      │ 1                                           │
└────────────────────┴─────────────────────────────────────────────┘

🛠️ Development

Project Structure

psonar/
├── psonar/
│   ├── __init__.py
│   ├── cli.py                  # CLI interface
│   ├── config.py               # Configuration handling
│   ├── main.py                 # Main entry point
│   ├── db/
│   │   ├── connection.py       # Database connection
│   │   └── safe_query.py       # Safe query execution
│   ├── checks/
│   │   ├── cis.py              # CIS benchmark checks
│   │   ├── health.py           # Health checks
│   │   ├── users.py            # User & privilege audit
│   │   ├── databases.py        # Database health audit
│   │   ├── enterprise.py       # Enterprise security
│   │   ├── sensitive_data.py   # PII/secrets discovery
│   │   ├── sessions.py         # Session monitoring
│   │   ├── configuration.py    # Config audit
│   │   ├── vulnerabilities.py  # CVE scanner
│   │   └── schema_security.py  # Schema security
│   ├── analysis/
│   │   ├── risk_scoring.py     # Risk calculation
│   │   └── mitre_mapping.py    # MITRE ATT&CK mapping
│   └── reporting/
│       ├── html_report.py      # HTML report generation
│       └── templates/          # Jinja2 templates
├── config/
│   └── example.yaml            # Example configuration
├── tests/
├── requirements.txt
├── pyproject.toml
├── Dockerfile
├── docker-compose.yaml
└── README.md

Running Tests

# Install dev dependencies
pip install -e ".[dev]"

# Run tests
pytest tests/

# Run with coverage
pytest --cov=psonar tests/

# Lint
black psonar/
mypy psonar/

🤝 Contributing

Contributions are welcome! Please:

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Make your changes
4. Run tests (pytest tests/)
5. Commit your changes (git commit -m 'Add amazing feature')
6. Push to the branch (git push origin feature/amazing-feature)
7. Open a Pull Request

📝 License

GNU General Public License v3.0 (GPL-3.0) - see LICENSE file for details.

⚠️ Disclaimer

This tool is for security assessment purposes only. Always:

• Get proper authorization before scanning
• Use dedicated audit accounts
• Review findings before remediation
• Test changes in non-production first

👤 Author

Cuma KURT

• 📧 Email: cumakurt@gmail.com
• 💼 LinkedIn: cuma-kurt-34414917
• 🐙 GitHub: cumakurt

---

Made with ❤️ for PostgreSQL security
_{© 2024-2026 Cuma KURT. All rights reserved.}