pgaudit Setup — psql-secops-prod

Database audit logging for HIPAA §164.312(b) and SOC2 CC6.1 compliance.

Quick Reference

Item	Value
Server	`psql-secops-prod`
Resource group	`rg-logging-logs`
Subscription	Logging
Extension	`pgaudit` (Azure-managed, not self-installed)
Audit scope	`write,role,ddl`
Log destination	`cirius-logging-law-central` (ID: `5d76d1f2`)
LAW table	`AzureDiagnostics` where `Category == "PostgreSQLLogs"`
Compliance driver	HIPAA §164.312(b), SOC2 CC6.1

1. Current State — Check Before Touching Anything

Check via Azure CLI

bash

# Is pgaudit in shared_preload_libraries?
az postgres flexible-server parameter show \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name shared_preload_libraries \
  --query "value" \
  --output tsv

# Is pgaudit in the azure.extensions allowlist?
az postgres flexible-server parameter show \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name azure.extensions \
  --query "value" \
  --output tsv

# What is pgaudit.log currently set to?
az postgres flexible-server parameter show \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log \
  --query "value" \
  --output tsv

Check via psql

Connect first (see the postgresql-operations runbook for auth):

bash

export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)
psql "host=psql-secops-prod.postgres.database.azure.com port=5432 dbname=secops user=rgarshol@ciriusgroup.com sslmode=require"

Then in psql:

sql

-- Is the extension installed?
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';

-- What are the current pgaudit settings?
SELECT name, setting, unit, context, source
FROM pg_settings
WHERE name LIKE 'pgaudit%'
ORDER BY name;

-- Is pgaudit in shared_preload_libraries? (must be there before CREATE EXTENSION)
SHOW shared_preload_libraries;

Expected output when fully configured:

        name          |    setting     | unit | context | source
----------------------+----------------+------+---------+--------
 pgaudit.log          | write,role,ddl |      | superuser | configuration file
 pgaudit.log_catalog  | off            |      | superuser | configuration file
 pgaudit.log_client   | off            |      | superuser | configuration file
 pgaudit.log_level    | log            |      | superuser | configuration file
 pgaudit.log_parameter| on             |      | superuser | configuration file
 pgaudit.log_relation | on             |      | superuser | configuration file
 pgaudit.role         | audit_role     |      | superuser | configuration file

Check Diagnostic Settings (Log Export to LAW)

bash

az monitor diagnostic-settings list \
  --resource "/subscriptions/$(az account show --query id --output tsv)/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secops-prod" \
  --output table

Look for a setting with PostgreSQLLogs enabled and destination workspace cirius-logging-law-central.

2. Enabling pgaudit

pgaudit on Azure Database for PostgreSQL Flexible Server requires three server parameter changes before you can CREATE EXTENSION. Two of these require a server restart.

Estimated downtime: under 30 seconds. Azure Flexible Server restart is fast — the service restarts the PostgreSQL process, not the underlying VM. Plan during off-hours or a maintenance window.

Step 1 — Add pgaudit to the Extension Allowlist

Azure Flexible Server requires extensions to be explicitly allowlisted before they can be created. This parameter change does not require a restart.

Portal path: psql-secops-prod → Server parameters → search azure.extensions → add PGAUDIT to the comma-separated list → Save.

Azure CLI:

bash

# Check existing value first — do not overwrite other extensions
az postgres flexible-server parameter show \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name azure.extensions \
  --query "value" \
  --output tsv

# Set — include any existing extensions plus pgaudit
# Example if pg_stat_statements is already there:
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name azure.extensions \
  --value "PG_STAT_STATEMENTS,PGAUDIT"

If azure.extensions is currently empty:

bash

az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name azure.extensions \
  --value "PGAUDIT"

Step 2 — Add pgaudit to shared_preload_libraries

This parameter requires a restart. pgaudit must be loaded at startup to hook into the PostgreSQL executor.

Portal path: psql-secops-prod → Server parameters → search shared_preload_libraries → add pgaudit → Save.

Azure CLI:

bash

# Check existing value first
az postgres flexible-server parameter show \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name shared_preload_libraries \
  --query "value" \
  --output tsv

# Set — include existing entries plus pgaudit
# Example if pg_stat_statements is already loaded:
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name shared_preload_libraries \
  --value "pg_stat_statements,pgaudit"

# If currently empty:
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name shared_preload_libraries \
  --value "pgaudit"

Step 3 — Restart the Server

Required after changing shared_preload_libraries. Do not skip.

bash

az postgres flexible-server restart \
  --name psql-secops-prod \
  --resource-group rg-logging-logs

Monitor restart completion:

bash

watch -n 5 'az postgres flexible-server show \
  --name psql-secops-prod \
  --resource-group rg-logging-logs \
  --query "state" \
  --output tsv'

State transitions: Ready → Restarting → Ready. Takes 20–60 seconds.

After restart, verify the app is still healthy:

bash

curl -s https://secops.bedrockcybersecurity.org/health

Step 4 — Create the Extension

Connect to the secops database and install the extension:

bash

export PGPASSWORD=$(az account get-access-token --resource-type oss-rdbms --query accessToken --output tsv)
psql "host=psql-secops-prod.postgres.database.azure.com port=5432 dbname=secops user=rgarshol@ciriusgroup.com sslmode=require"

sql

CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Verify
SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';

3. Configuration — Audit Scope

Recommended Settings for HIPAA Compliance

bash

# pgaudit.log — what to audit
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log \
  --value "write,role,ddl"

# Log each relation (table) separately — required for per-table granularity
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log_relation \
  --value "on"

# Capture bind parameters in log entries
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log_parameter \
  --value "on"

# Do not audit system catalog reads — suppresses noise from internal PG housekeeping
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log_catalog \
  --value "off"

# Log level — 'log' routes audit entries through the normal PostgreSQL log pipeline
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log_level \
  --value "log"

Why This Scope

Category	Included	Rationale
`write`	Yes	Captures INSERT, UPDATE, DELETE, TRUNCATE — all data modification events
`role`	Yes	Captures GRANT, REVOKE, CREATE ROLE — all privilege changes (SOC2 CC6.1)
`ddl`	Yes	Captures CREATE, ALTER, DROP — all schema changes
`read`	No	Generates enormous volume; HIPAA does not require audit of every SELECT
`function`	No	High noise from internal PG function calls; add if compliance scope expands
`misc`	No	Low signal; covers FETCH, CHECKPOINT, VACUUM — not required
`all`	Never	Prohibitive volume; breaks LAW ingestion budgets

pgaudit.log_parameter Caveat

With pgaudit.log_parameter = on, actual bound parameter values appear in audit logs. For tables like incidents and findings, this means patient-adjacent data may appear in log lines. Mitigating factors:

LAW access is restricted to the logging subscription — same access controls as the production database
The alternative (no parameter logging) is worse for HIPAA: you see the statement but not the data affected, which weakens forensic value
HIPAA audit logs that contain PHI are themselves PHI — apply the same access controls to LAW as you do to the database

Session vs. Object Auditing

pgaudit supports two audit modes:

Session auditing (what we configure above) logs events for all sessions, filtered by pgaudit.log. This is the right choice for HIPAA baseline coverage.

Object auditing logs events for specific database objects — useful for table-level precision on top of session auditing:

sql

-- Create a dedicated audit role (no login, no real privileges — just for pgaudit tracking)
CREATE ROLE audit_role;

-- Grant SELECT on the tables you want pgaudit to track at the object level
-- Note: this does NOT give audit_role permission to actually read data
GRANT SELECT, INSERT, UPDATE, DELETE ON incidents TO audit_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON findings TO audit_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON known_good_rules TO audit_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON changes TO audit_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON evidence TO audit_role;

Then set the parameter:

bash

az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.role \
  --value "audit_role"

Object auditing via pgaudit.role augments session auditing — it does not replace it. Use it to capture read events on specific sensitive tables without enabling read globally.

Per-Database or Per-Role Scope Adjustment

If you need tighter scope for specific databases or roles (e.g., audit only the app user's writes, not Rory's maintenance queries):

sql

-- Apply audit settings to a specific database only (overrides global pgaudit.log for that database)
ALTER DATABASE secops SET pgaudit.log = 'write,role,ddl';

-- Apply a different scope to a specific role
-- Example: disable write auditing for a read-only reporting role to cut noise
ALTER ROLE reporting_reader SET pgaudit.log = 'ddl,role';

-- Apply tighter scope to the app user
ALTER ROLE "ca-secops-prod" SET pgaudit.log = 'write,role,ddl';

Database-level and role-level settings override the server-level pgaudit.log parameter for that scope. Use RESET to revert:

sql

ALTER DATABASE secops RESET pgaudit.log;

4. Diagnostic Settings — Log Export to LAW

pgaudit events land in the PostgreSQL log stream. Azure routes this stream to Log Analytics via Diagnostic Settings.

Check Existing Diagnostic Settings

bash

PSQL_RESOURCE_ID="/subscriptions/$(az account show --query id --output tsv)/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secops-prod"

az monitor diagnostic-settings list \
  --resource "$PSQL_RESOURCE_ID" \
  --output json

Confirm PostgreSQLLogs is enabled and the workspace ID matches cirius-logging-law-central (workspace ID: 5d76d1f2).

Create Diagnostic Settings (if missing)

Portal path: psql-secops-prod → Diagnostic settings → Add diagnostic setting → check PostgreSQLLogs → check AllMetrics → Destination: cirius-logging-law-central → Save.

Azure CLI:

bash

PSQL_RESOURCE_ID="/subscriptions/$(az account show --query id --output tsv)/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secops-prod"

LAW_ID="/subscriptions/$(az account show --query id --output tsv)/resourceGroups/rg-logging-logs/providers/Microsoft.OperationalInsights/workspaces/cirius-logging-law-central"

az monitor diagnostic-settings create \
  --name "psql-secops-prod-to-law" \
  --resource "$PSQL_RESOURCE_ID" \
  --workspace "$LAW_ID" \
  --logs '[{"category":"PostgreSQLLogs","enabled":true,"retentionPolicy":{"enabled":false,"days":0}}]' \
  --metrics '[{"category":"AllMetrics","enabled":true,"retentionPolicy":{"enabled":false,"days":0}}]'

Retention is managed in LAW, not here — set to 0 days (disabled) on the diagnostic setting itself.

5. Terraform — Full IaC for pgaudit

Bring all three parameter changes and the diagnostic settings under Terraform. Add these blocks to the existing PostgreSQL resource file in azure-infra.

Server Parameter Resources

hcl

# azure.extensions — allowlist pgaudit before CREATE EXTENSION
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_extensions" {
  name      = "azure.extensions"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "PGAUDIT"

  # If pg_stat_statements is separately managed, combine here:
  # value = "PG_STAT_STATEMENTS,PGAUDIT"
}

# shared_preload_libraries — required for pgaudit executor hooks
# IMPORTANT: changing this parameter triggers a server restart
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_preload" {
  name      = "shared_preload_libraries"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "pgaudit"

  # If pg_stat_statements is already loaded, combine:
  # value = "pg_stat_statements,pgaudit"
}

# pgaudit.log — audit scope: data writes, role/privilege changes, schema changes
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_log" {
  name      = "pgaudit.log"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "write,role,ddl"
}

# pgaudit.log_relation — log each relation separately for per-table granularity
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_log_relation" {
  name      = "pgaudit.log_relation"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "on"
}

# pgaudit.log_parameter — capture bound parameter values for forensic completeness
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_log_parameter" {
  name      = "pgaudit.log_parameter"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "on"
}

# pgaudit.log_catalog — suppress system catalog noise
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_log_catalog" {
  name      = "pgaudit.log_catalog"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "off"
}

# pgaudit.log_level — route audit events through PostgreSQL log pipeline
resource "azurerm_postgresql_flexible_server_configuration" "pgaudit_log_level" {
  name      = "pgaudit.log_level"
  server_id = azurerm_postgresql_flexible_server.secops_prod.id
  value     = "log"
}

Diagnostic Settings Resource

hcl

resource "azurerm_monitor_diagnostic_setting" "psql_secops_prod" {
  name                       = "psql-secops-prod-to-law"
  target_resource_id         = azurerm_postgresql_flexible_server.secops_prod.id
  log_analytics_workspace_id = data.azurerm_log_analytics_workspace.central.id

  enabled_log {
    category = "PostgreSQLLogs"
  }

  metric {
    category = "AllMetrics"
    enabled  = true
  }
}

If the LAW workspace is a data source:

hcl

data "azurerm_log_analytics_workspace" "central" {
  name                = "cirius-logging-law-central"
  resource_group_name = "rg-logging-logs"
}

Terraform Apply Notes

shared_preload_libraries change triggers a server restart. Azure applies this automatically during terraform apply. Expect 30–60 seconds of PostgreSQL unavailability.

Import existing diagnostic settings before applying if they already exist:

bash

terraform import azurerm_monitor_diagnostic_setting.psql_secops_prod \
  "/subscriptions/<logging-sub-id>/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secops-prod|psql-secops-prod-to-law"

6. Verification

Verify Extension is Installed

sql

SELECT extname, extversion FROM pg_extension WHERE extname = 'pgaudit';
-- Expected: one row, extname = 'pgaudit'

Verify Parameter Configuration

sql

SELECT name, setting
FROM pg_settings
WHERE name LIKE 'pgaudit%'
ORDER BY name;

Expected output:

        name          |    setting
----------------------+----------------
 pgaudit.log          | write,role,ddl
 pgaudit.log_catalog  | off
 pgaudit.log_client   | off
 pgaudit.log_level    | log
 pgaudit.log_parameter| on
 pgaudit.log_relation | on

End-to-End Test: Write → Audit → LAW

Run a harmless write operation to generate an audit event:

sql

-- Step 1: generate an auditable write
BEGIN;
INSERT INTO known_good_rules (name, description, enabled, created_at, updated_at)
VALUES ('pgaudit-test-rule', 'pgaudit verification — delete after test', false, NOW(), NOW());
ROLLBACK;
-- The ROLLBACK undoes the insert, but pgaudit logs the attempt regardless

Alternatively, a DDL event (easier to spot in logs):

sql

-- Step 2: generate a DDL event
CREATE TABLE pgaudit_verify_delete_me (id serial PRIMARY KEY, ts timestamptz DEFAULT NOW());
DROP TABLE pgaudit_verify_delete_me;

Step 3: check PostgreSQL logs in LAW. Allow 2–5 minutes for log ingestion lag, then query:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where ResourceType == "FLEXIBLESERVERS"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| order by TimeGenerated desc
| take 20

You should see entries like:

AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,CREATE TABLE pgaudit_verify_delete_me ...
AUDIT: SESSION,2,1,DDL,DROP TABLE,,,DROP TABLE pgaudit_verify_delete_me

If nothing appears after 10 minutes:

Confirm diagnostic settings are active: az monitor diagnostic-settings list --resource <id>
Confirm pgaudit.log is set: SHOW pgaudit.log; in psql
Confirm the extension is installed: SELECT extname FROM pg_extension WHERE extname = 'pgaudit';
Check general PostgreSQL log flow in LAW (non-audit events) to isolate whether the issue is pgaudit or the diagnostic pipeline:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| order by TimeGenerated desc
| take 10

7. KQL Queries — Audit Investigation

All queries target AzureDiagnostics in cirius-logging-law-central.

All Audit Events (Last 24 Hours)

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where ResourceType == "FLEXIBLESERVERS"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| extend
    audit_class   = extract(@"AUDIT: (\w+)", 1, Message),
    audit_command = extract(@"AUDIT: \w+,\d+,\d+,(\w+),(\w+)", 2, Message),
    audit_object  = extract(@"AUDIT: \w+,\d+,\d+,\w+,\w+,([^,]+)", 1, Message),
    audit_user    = extract(@"AUDIT: \w+,\d+,\d+,\w+,\w+,[^,]*,([^,]+)", 1, Message)
| project TimeGenerated, audit_class, audit_command, audit_object, audit_user, Message
| order by TimeGenerated desc

DDL Changes in Last 7 Days

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:" and Message contains ",DDL,"
| extend
    audit_command = extract(@"AUDIT: \w+,\d+,\d+,DDL,([^,]+)", 1, Message),
    statement_preview = extract(@",,(.{0,120})", 1, Message)
| where TimeGenerated >= ago(7d)
| project TimeGenerated, audit_command, statement_preview, Message
| order by TimeGenerated desc

ROLE Permission Changes

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:" and Message contains ",ROLE,"
| extend
    audit_command = extract(@"AUDIT: \w+,\d+,\d+,ROLE,([^,]+)", 1, Message),
    statement = extract(@",,(.+)$", 1, Message)
| where TimeGenerated >= ago(30d)
| project TimeGenerated, audit_command, statement, Message
| order by TimeGenerated desc

All Writes by a Specific User

Replace rgarshol@ciriusgroup.com with the target user:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:" and Message contains ",WRITE,"
| where Message contains "rgarshol@ciriusgroup.com"
| extend
    audit_command = extract(@"AUDIT: \w+,\d+,\d+,WRITE,([^,]+)", 1, Message),
    audit_object  = extract(@"AUDIT: \w+,\d+,\d+,WRITE,[^,]+,([^,]+)", 1, Message)
| project TimeGenerated, audit_command, audit_object, Message
| order by TimeGenerated desc

To target the application service account:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:" and Message contains ",WRITE,"
| where Message contains "ca-secops-prod"
| project TimeGenerated, Message
| order by TimeGenerated desc

Bulk Data Operations

PostgreSQL audit log does not include affected row counts directly. Use log_min_duration_statement alongside pgaudit to catch long-running write operations that imply bulk volume:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "duration:" and (Message contains "INSERT" or Message contains "UPDATE" or Message contains "DELETE" or Message contains "TRUNCATE")
| extend duration_ms = toint(extract(@"duration: (\d+)", 1, Message))
| where duration_ms > 10000
| project TimeGenerated, duration_ms, Message
| order by duration_ms desc

For exact row counts, enable log_min_duration_statement at the database level:

sql

ALTER DATABASE secops SET log_min_duration_statement = 5000;  -- log queries taking > 5s

Activity Outside Business Hours

Business hours defined as 06:00–22:00 Pacific (14:00–06:00 UTC next day). Adjust UTC offsets for DST.

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| extend hour_utc = datetime_part("hour", TimeGenerated)
// Outside 14:00–06:00 UTC = outside 06:00–22:00 Pacific
| where hour_utc >= 6 and hour_utc < 14
| extend
    audit_class   = extract(@"AUDIT: (\w+)", 1, Message),
    audit_command = extract(@"AUDIT: \w+,\d+,\d+,(\w+),(\w+)", 2, Message)
| project TimeGenerated, audit_class, audit_command, Message
| order by TimeGenerated desc

Audit Summary Dashboard Query

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| where TimeGenerated >= ago(7d)
| extend audit_class = extract(@"AUDIT: (\w+)", 1, Message)
| extend audit_category = case(
    Message contains ",DDL,",   "DDL",
    Message contains ",ROLE,",  "ROLE",
    Message contains ",WRITE,", "WRITE",
    "OTHER"
  )
| summarize count() by audit_category, bin(TimeGenerated, 1d)
| render columnchart

8. HIPAA Evidence Package

For an auditor demonstrating that database audit controls are in place (HIPAA §164.312(b)):

Evidence Items to Collect

E1 — Extension installation proof:

sql

SELECT extname, extversion, extrelocatable FROM pg_extension WHERE extname = 'pgaudit';

Screenshot or export. Documents that the extension is installed in the secops database.

E2 — Audit parameter configuration:

sql

SELECT name, setting, source FROM pg_settings WHERE name LIKE 'pgaudit%' ORDER BY name;

Documents what is being audited and that it is applied via configuration file (not a one-time session change that could disappear).

E3 — Diagnostic settings export:

bash

az monitor diagnostic-settings show \
  --name "psql-secops-prod-to-law" \
  --resource "/subscriptions/<logging-sub-id>/resourceGroups/rg-logging-logs/providers/Microsoft.DBforPostgreSQL/flexibleServers/psql-secops-prod" \
  --output json

Documents that audit logs are exported to a SIEM (LAW) rather than staying only on the database server.

E4 — LAW retention policy:

bash

az monitor log-analytics workspace show \
  --workspace-name cirius-logging-law-central \
  --resource-group rg-logging-logs \
  --query "retentionInDays" \
  --output tsv

Documents log retention period. HIPAA requires 6-year retention for audit logs — confirm that LAW has either a long retention period configured, or that an archive export to blob storage covers the gap. Cross-reference with the log-retention-verification runbook.

E5 — Sample audit events from LAW:

Run this KQL and export to CSV for the evidence package:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| where TimeGenerated >= ago(30d)
| project TimeGenerated, Category, Level, Message
| order by TimeGenerated desc
| take 100

Export via: LAW → Logs → run query → Export → CSV.

E6 — Access control on LAW (who can see the audit logs):

bash

az role assignment list \
  --scope "/subscriptions/<logging-sub-id>/resourceGroups/rg-logging-logs/providers/Microsoft.OperationalInsights/workspaces/cirius-logging-law-central" \
  --output table

Documents that audit log access is restricted (principle of least privilege on the audit trail itself).

Compliance Mapping

Requirement	Control	Evidence
HIPAA §164.312(b) — Audit controls	pgaudit captures all writes, DDL, role changes	E1, E2, E5
HIPAA §164.312(b) — Activity recorded	Logs exported to LAW with timestamp, user, operation	E3, E5
HIPAA §164.312(b) — Logs retained	LAW retention + archive to blob storage	E4
SOC2 CC6.1 — Privileged access logged	ROLE category captures all GRANT/REVOKE	E2, E5
SOC2 CC6.1 — Access restricted	LAW RBAC controls who can query audit logs	E6

9. Operational Considerations

Performance Impact

pgaudit with write,role,ddl scope has a measured overhead of 3–7% on write-heavy workloads. For the secops database (incident/finding ingestion from 17 agents in 4-hour cycles), this is within acceptable bounds.

What drives overhead:

Each audited statement incurs a string formatting operation in the PostgreSQL executor
log_relation = on multiplies this by the number of relations touched per statement
log_parameter = on adds parameter serialization

What does NOT drive significant overhead:

The logging pipeline itself (async)
Log export to LAW (happens at the Azure platform layer, not the PostgreSQL process)

If performance degrades measurably after enabling pgaudit, narrow the scope:

bash

# Remove 'write' if write throughput is critical — loses data modification audit
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log \
  --value "role,ddl"

Then use object auditing via pgaudit.role to selectively audit writes to the highest-sensitivity tables only (incidents, findings).

Log Volume Estimates

Based on the current workload profile (17 agents, 4-hour cycles, ~500 incidents/day):

Scope	Estimated Events/Day	Estimated Log Volume/Day
`write` alone	~5,000–20,000	5–20 MB
`ddl` alone	~10–50	Negligible
`role` alone	~1–10	Negligible
`write,role,ddl` (recommended)	~5,000–20,000	5–20 MB
`all` (not recommended)	50,000+	50+ MB

These estimates assume agent bulk-inserts into findings are the dominant write pattern. Verify actual volume after enabling:

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| where TimeGenerated >= ago(1d)
| summarize count(), estimate_data_size(Message) by bin(TimeGenerated, 1h)
| render timechart

LAW Retention Settings

LAW default retention is 30 days. HIPAA requires 6-year retention for audit records. Options:

LAW extended retention — increase the workspace retention to 2190 days (6 years). This is expensive at scale.
Archive tier — LAW supports moving older data to archive tier (cheaper, queryable with time delay). Set at the table level:

bash

az monitor log-analytics workspace table update \
  --workspace-name cirius-logging-law-central \
  --resource-group rg-logging-logs \
  --name AzureDiagnostics \
  --retention-time 90 \
  --total-retention-time 2190

Export to blob storage — the existing WORM-compliant blob export pipeline (cross-reference log-retention-verification runbook) should cover AzureDiagnostics including pgaudit events.

Confirm with Rory which retention strategy applies before enabling pgaudit at scale — the volume difference between 90-day and 2190-day LAW retention affects cost materially.

Monitoring Audit Log Health

Add this KQL alert to confirm pgaudit events are flowing daily. If the count drops to zero, either pgaudit was disabled or the diagnostic pipeline broke.

kql

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.DBFORPOSTGRESQL"
| where Resource =~ "psql-secops-prod"
| where Message contains "AUDIT:"
| where TimeGenerated >= ago(24h)
| summarize audit_events = count()

Set an alert rule on this query: if audit_events < 1 over a 4-hour window, fire a HIGH alert. A healthy system with active agent ingestion should produce hundreds of audit events per day.

10. Rollback Procedure

If pgaudit causes unexpected performance degradation or log volume problems:

Option A — Narrow Scope (no restart)

bash

# Remove 'write' to cut 95% of volume — DDL and ROLE still covered
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name pgaudit.log \
  --value "role,ddl"

No restart required. Takes effect on next connection.

Option B — Disable Entirely (restart required)

bash

# Remove pgaudit from shared_preload_libraries
az postgres flexible-server parameter set \
  --server-name psql-secops-prod \
  --resource-group rg-logging-logs \
  --name shared_preload_libraries \
  --value ""

# Restart
az postgres flexible-server restart \
  --name psql-secops-prod \
  --resource-group rg-logging-logs

Then in psql (after restart):

sql

DROP EXTENSION IF EXISTS pgaudit;

Note: disabling pgaudit constitutes a gap in the HIPAA audit control. Document the outage period, the reason, and the date re-enabled. Include this in the HIPAA evidence package as a documented exception.

postgresql-operations.md — Connection procedures, Alembic, backups, performance
log-retention-verification.md — LAW and blob storage retention verification
kql-query-reference.md — General KQL patterns across all log sources
compliance/hipaa-controls.md — HIPAA control mapping and evidence procedures

pgaudit Setup — psql-secops-prod ​

Quick Reference ​

1. Current State — Check Before Touching Anything ​

Check via Azure CLI ​

Check via psql ​

Check Diagnostic Settings (Log Export to LAW) ​

2. Enabling pgaudit ​

Step 1 — Add pgaudit to the Extension Allowlist ​

Step 2 — Add pgaudit to shared_preload_libraries ​

Step 3 — Restart the Server ​

Step 4 — Create the Extension ​

3. Configuration — Audit Scope ​

Recommended Settings for HIPAA Compliance ​

Why This Scope ​

pgaudit.log_parameter Caveat ​

Session vs. Object Auditing ​

Per-Database or Per-Role Scope Adjustment ​

4. Diagnostic Settings — Log Export to LAW ​

Check Existing Diagnostic Settings ​

Create Diagnostic Settings (if missing) ​

5. Terraform — Full IaC for pgaudit ​

Server Parameter Resources ​

Diagnostic Settings Resource ​

Terraform Apply Notes ​

6. Verification ​

Verify Extension is Installed ​

Verify Parameter Configuration ​

End-to-End Test: Write → Audit → LAW ​

7. KQL Queries — Audit Investigation ​

All Audit Events (Last 24 Hours) ​

DDL Changes in Last 7 Days ​

ROLE Permission Changes ​

All Writes by a Specific User ​

Bulk Data Operations ​

Activity Outside Business Hours ​

Audit Summary Dashboard Query ​

8. HIPAA Evidence Package ​

Evidence Items to Collect ​

Compliance Mapping ​

9. Operational Considerations ​

Performance Impact ​

Log Volume Estimates ​

LAW Retention Settings ​

Monitoring Audit Log Health ​

10. Rollback Procedure ​

Option A — Narrow Scope (no restart) ​

Option B — Disable Entirely (restart required) ​

Related Runbooks ​

pgaudit Setup — psql-secops-prod

Quick Reference

1. Current State — Check Before Touching Anything

Check via Azure CLI

Check via psql

Check Diagnostic Settings (Log Export to LAW)

2. Enabling pgaudit

Step 1 — Add pgaudit to the Extension Allowlist

Step 2 — Add pgaudit to shared_preload_libraries

Step 3 — Restart the Server

Step 4 — Create the Extension

3. Configuration — Audit Scope

Recommended Settings for HIPAA Compliance

Why This Scope

pgaudit.log_parameter Caveat

Session vs. Object Auditing

Per-Database or Per-Role Scope Adjustment

4. Diagnostic Settings — Log Export to LAW

Check Existing Diagnostic Settings

Create Diagnostic Settings (if missing)

5. Terraform — Full IaC for pgaudit

Server Parameter Resources

Diagnostic Settings Resource

Terraform Apply Notes

6. Verification

Verify Extension is Installed

Verify Parameter Configuration

End-to-End Test: Write → Audit → LAW

7. KQL Queries — Audit Investigation

All Audit Events (Last 24 Hours)

DDL Changes in Last 7 Days

ROLE Permission Changes

All Writes by a Specific User

Bulk Data Operations

Activity Outside Business Hours

Audit Summary Dashboard Query

8. HIPAA Evidence Package

Evidence Items to Collect

Compliance Mapping

9. Operational Considerations

Performance Impact

Log Volume Estimates

LAW Retention Settings

Monitoring Audit Log Health

10. Rollback Procedure

Option A — Narrow Scope (no restart)

Option B — Disable Entirely (restart required)

Related Runbooks