pgaudit Operations — psql-secops-prod

Purpose: Day-to-day operations for pgaudit once it is enabled on psql-secops-prod — what to query, what anomalies to watch for, and how to respond.

Audience: Rory Prerequisite: pgaudit must be enabled and forwarding to LAW. See runbooks/pgaudit-setup.md for setup.

---

Where Logs Land

pgaudit events flow: psql-secops-prod → Azure Diagnostics → cirius-logging-law-central

LAW table: AzureDiagnosticsFilter: Category == "PostgreSQLLogs" and Message contains AUDIT:

AzureDiagnostics
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:"
| project TimeGenerated, Resource, Message
| order by TimeGenerated desc
| take 50

---

pgaudit Log Format

Each audit line in the Message field looks like:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE foo (id int),<none>
AUDIT: SESSION,1,1,WRITE,INSERT,public,incidents,INSERT INTO incidents ...,<none>
AUDIT: SESSION,1,1,ROLE,GRANT,,,GRANT SELECT ON incidents TO readonly_user,<none>

Fields: AUDIT: {type},{class_seq},{subclass_seq},{class},{command},{object_type},{object_name},{statement},{parameter}

Audit classes enabled (from pgaudit-setup.md): write,role,ddl

• WRITE — INSERT, UPDATE, DELETE, TRUNCATE on any table
• ROLE — GRANT, REVOKE, CREATE ROLE, ALTER ROLE, DROP ROLE
• DDL — CREATE/ALTER/DROP TABLE, INDEX, FUNCTION, VIEW, etc.

---

Routine Queries

Activity in the last 24 hours

AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:"
| extend AuditClass = extract("AUDIT: SESSION,[0-9]+,[0-9]+,([A-Z]+),", 1, Message)
| summarize Count = count() by AuditClass, bin(TimeGenerated, 1h)
| order by TimeGenerated desc

DDL changes (schema modifications)

AzureDiagnostics
| where TimeGenerated > ago(7d)
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:" and Message contains ",DDL,"
| project TimeGenerated, Message
| order by TimeGenerated desc

ROLE changes (privilege modifications)

AzureDiagnostics
| where TimeGenerated > ago(7d)
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:" and Message contains ",ROLE,"
| project TimeGenerated, Message
| order by TimeGenerated desc

High-volume WRITE activity (potential data exfiltration or bulk operation)

AzureDiagnostics
| where TimeGenerated > ago(4h)
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:" and Message contains ",WRITE,"
| summarize WriteCount = count() by bin(TimeGenerated, 10m)
| where WriteCount > 500
| order by TimeGenerated desc

Baseline write volume: check normal patterns first. A sudden spike in writes (>3× normal) is worth investigating.

---

Anomalies to Watch For

TRUNCATE on a production table

AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Category == "PostgreSQLLogs"
| where Message contains "TRUNCATE"
| project TimeGenerated, Message

TRUNCATE empties a table without row-level logging. In a production database it should never happen outside a CM window. Any TRUNCATE outside a CM window is HIGH severity — it could be data destruction.

Unexpected DDL outside CM window

AzureDiagnostics
| where TimeGenerated > ago(24h)
| where Category == "PostgreSQLLogs"
| where Message contains ",DDL,"
| project TimeGenerated, Message

Cross-reference with CM tickets. DDL changes outside of Terraform applies or migrations are suspicious.

ROLE privilege escalation

AzureDiagnostics
| where TimeGenerated > ago(7d)
| where Category == "PostgreSQLLogs"
| where Message contains ",ROLE," and (Message contains "GRANT" or Message contains "superuser")
| project TimeGenerated, Message

Any GRANT of superuser or GRANT on sensitive tables (incidents, findings, known_good_rules) outside of a planned migration is HIGH severity.

DROP TABLE or DROP DATABASE

AzureDiagnostics
| where TimeGenerated > ago(7d)
| where Category == "PostgreSQLLogs"
| where Message contains "DROP TABLE" or Message contains "DROP DATABASE"
| project TimeGenerated, Message

DROP statements should only appear during planned migrations with a CM ticket. Any unexpected DROP is CRITICAL — potential data destruction.

---

Connecting the psql User to an Action

pgaudit logs the database user, not the application layer user. In the SecOps platform, all agents connect as the same service account (e.g., secops_app). To trace a database write back to an application action:

1. Get the timestamp and statement from the pgaudit log
2. Cross-reference with SecOps application logs in Container Apps:

   ContainerAppConsoleLogs
   | where TimeGenerated between (datetime(<start>) .. datetime(<end>))
   | where Log contains "POST /api/" or Log contains "INSERT" or Log contains "UPDATE"
   | project TimeGenerated, ContainerName, Log

3. The Container App logs the API endpoint and user that triggered each DB write

---

Responding to Anomalies

TRUNCATE or DROP outside CM window

1. Open a SecOps incident with severity HIGH
2. Identify who executed the statement — check which Container App instance was connected at that time, or whether the connection came from a psql session (direct DB access)
3. Check Azure Database for PostgreSQL activity logs: Azure Portal → psql-secops-prod → Activity log
4. If direct DB access (not via the app): check who has DB credentials. Credentials are in Key Vault — check Key Vault access logs:

   az monitor activity-log list --resource-group rg-logging-logs \
     --resource-type "Microsoft.KeyVault/vaults" --offset 24h

5. If unauthorized: rotate the DB password immediately, revoke the offending session, notify Rory

Unexpected privilege escalation

1. Identify which role received the grant
2. Check if the role exists and what it can access: \dp in psql to show table-level permissions
3. Revoke if unauthorized: REVOKE <privilege> ON <table> FROM <role>;
4. Rotate credentials if the escalation suggests account compromise

Bulk INSERT/DELETE anomaly

1. Determine the volume and tables involved
2. Is this a legitimate bulk operation (agent run, migration)? Check Container Apps logs and CM tickets
3. If unexplained: check whether data was exfiltrated before the bulk DELETE — compare row counts with the last known backup

---

Evidence for Compliance Audits

pgaudit logs are the primary evidence for HIPAA §164.312(b) (Audit Controls) and SOC2 CC6.1 at the database layer. For audits, export:

AzureDiagnostics
| where TimeGenerated between (datetime(<audit_period_start>) .. datetime(<audit_period_end>))
| where Category == "PostgreSQLLogs"
| where Message contains "AUDIT:"
| project TimeGenerated, Message
| order by TimeGenerated asc

Export to CSV from LAW and provide to auditors as PostgreSQL audit log evidence. Retain 6 years per HIPAA retention requirements (LAW retention policy must be set to 6 years or logs archived to cold storage — verify in runbooks/log-retention-verification.md).

---

Related Documents

• runbooks/pgaudit-setup.md — enabling pgaudit on psql-secops-prod
• runbooks/postgresql-operations.md — general PostgreSQL operations
• runbooks/log-retention-verification.md — verifying 6-year retention
• compliance/hipaa-controls.md — §164.312(b) audit control mapping