CSA (312-39) SOC Simulation Lab

Welcome to this CSA simulation. You will step into a live scenario involving a cloud database logging gap, identify the missing telemetry configuration, and understand how database logs feed into centralized SIEM monitoring for L1/L2 triage.

Scenario Context

You are a Tier 3 SOC Analyst at a cloud service provider. Your L2 team escalated a ticket involving suspected brute-force activity originating from 198.51.100.45 against a customer's external web portal.

While your WAF (Web Application Firewall) logs show 500 Internal Server Errors corresponding to the attacker's traffic, you attempt to correlate these events with the backend PostgreSQL database to confirm if any authentication bypass or data exfiltration occurred. However, your SIEM shows zero database logs for the past 48 hours. No failed auths, no slow queries, no errors.

Security Environment

You pull the current SIEM query results and a snippet of the local PostgreSQL configuration from the affected instance:

splunk> index=cloud_db sourcetype=postgresql host=db-prod-01 | stats count by action

Result: No results found. (Time range: Last 48 hours)

root@db-prod-01:~# cat /etc/postgresql/14/main/postgresql.conf | grep -i log

# ERROR REPORTING AND LOGGING

#logging_collector = off # Enable capturing of stderr and csvlog

log_directory = 'log' # directory where log files are written

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_connections = on

log_disconnections = on

Note: The '#' indicates the parameter is commented out and using the default state (off).

Question

You are working as a SOC analyst for a cloud-based service provider that relies on PostgreSQL databases to store critical customer data. During a recent security review, you discover that logs are not being generated for failed authentication attempts, slow queries, or database errors. This lack of visibility is making it difficult for you to detect potential threats and investigate suspicious database activity. To ensure that PostgreSQL captures and stores logs for centralized monitoring and forensic analysis, which configuration parameter should you enable?

SOC Hint: PostgreSQL configuration parameters strictly follow a specific naming convention. They never use camelCase or hyphens, and the parameter explicitly defines the act of "collecting" the "logging" output. Look closely at the commented-out lines in the environment snippet!

Senior Analyst Debrief

1. What is happening here?

We have a classic SOC visibility gap. The WAF is screaming about external 500 errors, but without the DB logs, we can't tell if the attacker successfully extracted data (via SQLi) or if they just crashed a poorly written query. Even though parameters like log_connections are set to on, PostgreSQL is not actually writing these events to files because the master process that intercepts `stderr` and routes it to log files is disabled. If there are no log files, the SIEM forwarder (like Filebeat or Splunk Universal Forwarder) has nothing to ingest.

2. Why is Option D correct?

D. logging_collector is the exact, correct parameter required in PostgreSQL to launch a background process that captures log messages sent to `stderr` and redirects them into log files. Without enabling this (logging_collector = on), PostgreSQL simply sends output to the console/systemd, which is often discarded or easily overwritten, making centralized forensic analysis impossible.

3. Why are the other options wrong?

A (loggingcollector) & B (logging-collector): Incorrect syntax. PostgreSQL configuration parameters do not use camelCase, joined words, or hyphens. They strictly use underscores.
C (log_collector): A common misnomer. While log_ is the prefix for many parameters (like log_connections), the specific process that collects these logs is named logging_collector.

4. Real-world SOC Application

In a real cloud environment (AWS RDS, Azure PostgreSQL, or self-hosted), enabling the logging_collector is just step one. Once the logs are actively writing to the log_directory, L3 engineering must ensure:

A log shipper (e.g., Fluentd) is tracking the .log or .csv files.
The logs are parsed properly in the SIEM (using STIX/TAXII feeds to enrich external IPs against Threat Intel).
Alerts are built for Event IDs related to FATAL errors, excessive failed logins (Event Code 28000), and DDL changes (schema modifications).

MINI LESSON: The Minimum Viable Database Telemetry

When onboarding a new database to a SIEM, a SOC analyst must enforce "Minimum Viable Telemetry." Turning on every log will cause alert fatigue and explode your SIEM ingestion costs. At a minimum, ensure these are configured via the postgresql.conf:

logging_collector = on (The prerequisite)
log_connections = on & log_disconnections = on (Tracks who is accessing the DB, critical for lateral movement detection).
log_line_prefix = '%m [%p] %q%u@%d ' (Ensures every log line contains a Timestamp, Process ID, User, and Database name for SIEM parsing).
log_min_error_statement = error (Captures the exact SQL statement that caused an error—crucial for analyzing SQL injection payloads).