Audit Logging

-- SCOPE: defensive logging on the bundled lab app. Authorised testing only.
-- Do not run probes against any database you do not own or have permission to test.

-- ===== The goal =====
-- 1. Detect query-shape abuse early
-- 2. Avoid logging credentials, tokens, or PII you cannot legally retain
-- 3. Make incident response fast: who, what, when, from where

-- ===== Postgres: pgaudit baseline =====
-- postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'
pgaudit.log_parameter = off       -- never log parameter values: PII risk
pgaudit.log_relation = on
log_min_duration_statement = 500  -- log slow queries
log_statement = 'ddl'             -- DDL always; DML via pgaudit
log_min_error_statement = error

-- Result: every DDL + write + privilege change is logged with user, db, role.
-- Slow queries (>500ms) get a row even if they succeeded.

-- ===== App layer: structured query log =====
-- pseudo
function runQuery(sql, params, ctx) {
  const start = Date.now();
  try {
    const r = pg.query(sql, params);
    const ms = Date.now() - start;
    audit.write({
      ts: new Date().toISOString(),
      route: ctx.route,                 // /api/users/:id
      user_id: ctx.userId,              // app-level user
      ip: hash(ctx.ip),                 // hash + truncate; do not store raw
      sql_template: sql.slice(0, 200),  // template only, no values
      param_count: params.length,
      rows: r.rowCount,
      duration_ms: ms,
    });
    return r;
  } catch (e) {
    audit.write({
      ts: new Date().toISOString(),
      route: ctx.route,
      user_id: ctx.userId,
      ip: hash(ctx.ip),
      sql_template: sql.slice(0, 200),
      param_count: params.length,
      error_code: e.code,             // e.g. 42601 syntax error
      error_class: e.code?.slice(0,2) // 42 syntax, 23 constraint, etc
    });
    throw e;
  }
}

-- ===== Detection signals =====
-- 1. Spike of error_class = '42' (syntax) from one user/IP  -> probe
-- 2. param_count = 0 on a query template that normally has parameters
-- 3. Sudden growth in sql_template variants per route
-- 4. Slow query growth on hot endpoints
-- 5. DDL outside maintenance windows (deploy ID required)

-- ===== Example alert (Postgres + Grafana / Loki) =====
-- LogQL
-- sum by (route, user_id) (
--   count_over_time({app="shop"} |= "sql_template" | json | error_class="42" [5m])
-- ) > 20

-- ===== Storage policy =====
-- - Templates: keep 90 days (low risk)
-- - Errors: keep 30 days
-- - Parameters: NEVER, unless masked + legally cleared
-- - IPs: hashed, salted per-tenant; rotate salt yearly

-- ===== Patterns to internalise =====
-- - Parameterise every query; ORM or pg.query(sql, params) only
-- - Log templates, not values
-- - Tag every row with user_id, route, deploy_id, ip_hash
-- - Alert on shape, not on words ('union' in a body is meaningless)
-- - Replay error_class to reconstruct intent without seeing PII

-- ===== Pitfalls =====
-- - String-concatenating SQL even once: a single ${userInput} is enough
-- - Logging full SQL with parameter values: GDPR + PCI exposure
-- - Storing raw IPs as a permanent identifier
-- - No deploy_id: cannot tell expected DDL from intrusion DDL
-- - Treating ORM-generated queries as opaque: still parameterise

// Log query exceptions with correlation IDs.
// Spike in 500s with SQL syntax errors = a scanner is probing you.
// Trip a rate-limit and alert on-call.