PostgreSQL Security Compliance and Automated Auditing

PostgreSQL Security: Beyond Basic Checks

Security audits are tedious, error-prone, and rarely continuous. Most teams:

• Run manual checks quarterly (if lucky)
• Use generic security scanners (not PostgreSQL-aware)
• Miss configuration drift over time
• Have no audit trail of changes

Traditional Security Approaches

CIS Benchmarks (Manual)

The gold standard for PostgreSQL security - 100+ checks covering:

• Installation and configuration
• Authentication and authorization
• Logging and auditing
• Encryption settings

Problems:

• 200+ page PDF to interpret
• Manual execution of each check
• No continuous monitoring
• No drift detection

pgAudit Extension

Provides detailed session and object auditing:

Shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write,ddl'
pgaudit.log_catalog = off

Problems:

• Generates massive log volume
• No analysis or alerting built-in
• Requires expert interpretation
• Performance overhead

External Security Scanners

Tools like Nessus, OpenVAS have PostgreSQL plugins:

• Generic checks (version, SSL)
• Not PostgreSQL-configuration aware
• No ongoing monitoring

PG Monitoring's Continuous Security Auditing

1. 50+ Automated Security Checks

Security Score: 73/100 (Needs Improvement)

Critical Issues (2):
❌ SSL not enforced (ssl = off)
❌ Superuser connections from app servers

High Priority (4):
⚠️ Trust authentication for local connections
⚠️ Log_connections disabled (compliance issue)
⚠️ track_activities = off (blind to user actions)
⚠️ Password encryption: md5 (should be scram-sha-256)

Medium Priority (8):
⚡ shared_preload_libraries missing passwordcheck
⚡ row_security disabled
⚡ log_line_prefix insufficient for audit trails
...

2. Configuration Drift Detection

Know when security settings change:

ALERT: Security-related configuration changed

Parameter: ssl changed from 'on' to 'off'
Time: 2026-04-12 14:23:17
User: postgres
Source: postgresql.conf edit

Previous baseline (approved): 2026-03-15
Compliance impact: PCI-DSS violation
Immediate action required: Re-enable SSL

3. User Privilege Analysis

Privilege Audit:

Overprivileged users (3):
- app_user: Has CREATE on public schema (unnecessary)
- analytics: Has SUPERUSER (dangerous!)
- backup_svc: Has DELETE on production tables

Unused privileges (12):
- readonly_role: CONNECT granted but never used
- legacy_app: 8 table permissions on dropped tables

Recommendation: Implement principle of least privilege
Risk reduction: 65% → estimated 95%

4. Compliance Report Generation

One-click reports for auditors:

PCI-DSS Compliance Report
Generated: 2026-04-12
Scope: production-db-01

Requirement 2.3 (Encrypted transmissions):
Status: NON-COMPLIANT
Details: SSL disabled, connections in plaintext
Evidence: ssl = off, ssl_cert_file not set

Requirement 10.2 (Audit trail coverage):
Status: COMPLIANT
Details: 98% of activities logged
Gap: CREATE TEMP TABLE statements not captured

Remediation plan generated with timeline

Feature Comparison

Case Study: The SSL That Wasn't

A fintech company believed their PostgreSQL connections were encrypted. Annual audit revealed otherwise.

What happened:

Timeline from PG Monitoring:
- 2025-11-15: ssl = on (compliant)
- 2025-12-02: Configuration reload during maintenance
- 2026-01-10: ssl inadvertently set to off
- 2026-01-10 to 2026-04-05: 86 days of unencrypted traffic
- 2026-04-05: Audit discovered issue

Impact:
- 3.2M sensitive transactions in plaintext
- PCI-DSS violation
- $50K audit remediation costs

With PG Monitoring: Alert would have fired within minutes of the configuration change.

The Bottom Line

Traditional approach: "Run CIS benchmarks quarterly, hope nothing changed in between."

PG Monitoring: "Security score 73/100 with 2 critical issues detected. SSL disabled 3 days ago by configuration change. 12 overprivileged users identified. PCI-DSS compliance report ready for auditors with remediation timeline."