A PostgreSQL diagnostic role can be unable to update one business row yet still reveal other users’ SQL, hold locks, consume I/O, and turn an incident investigation into a data-exposure event.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

Previous PostgreSQL investigations used statistics, plans, logs, host evidence, and Aurora metrics. These can expose SQL text, bind values, identities, object names, and topology.

The LLM does not need that privilege. Collectors need scoped access; the model needs a sanitized, time-bounded evidence pack. Requests for more evidence return through a policy broker.

The Problem

PostgreSQL’s monitoring roles are intentionally powerful. pg_monitor includes pg_read_all_settings, pg_read_all_stats, and pg_stat_scan_tables. The last can invoke monitoring functions that take ACCESS SHARE locks for a long time. pg_read_all_stats can reveal other users’ query text and query identifiers in pg_stat_statements.

Read-only transactions can spill, scan, wait, or displace cache pages. A replica protects the writer from some collector load, but a bad diagnostic can increase lag or exhaust local resources.

Audit coverage is fragmented. PostgreSQL logs, EC2 evidence, Aurora Database Activity Streams, and CloudTrail describe different activity. None records what was redacted, what the model saw, or why a recommendation was accepted.

How do we prove causality while keeping sensitive data, unbounded diagnostic cost, and production authority outside the LLM’s control?

Build a Five-Boundary Diagnostic Control Plane

flowchart TD
    A[operator request and incident scope] --> B[policy broker — catalog budget and expiry]
    B --> C[PostgreSQL allowlisted collectors]
    B --> D[EC2 or Aurora telemetry collectors]
    C --> E[restricted raw evidence vault]
    D --> E
    E --> F[deterministic minimization and redaction]
    F --> G[versioned sanitized evidence pack]
    G --> H[LLM reasoning — no production credentials]
    H --> I[observations hypotheses gaps and proposed actions]
    I --> J[human approval and separate change workflow]
    K[independent audit ledger] --> L[DBA and security review]
    B --> K
    C --> K
    D --> K
    F --> K
    H --> K
    J --> K

The five boundaries are identity, query capability, resource budget, disclosure, and change authority. Each must fail closed.

IdentityAllowedExcluded
Baseline database collectorFixed statistics, waits, locks, WAL, vacuum, and digest summariesApplication tables, arbitrary SQL, backend signaling, resets
Elevated evidence roleExpiring cross-session text or approved plan evidencepg_signal_backend, pg_read_all_data, CREATEROLE, DDL
Host or AWS collectorNamed OS metrics, CloudWatch data, RDS events, topologyShell execution, cluster modification, failover, deletion
Evidence transformerRead restricted artifacts; write sanitized packsProduction access
LLM runtimeRead sanitized packs; produce analysisCredentials, collectors, approvals, deletion
Change executorExecute one approved runbookModel-selected scope or self-approval

Do not grant pg_monitor automatically. Prefer fixed views containing only required columns. Treat pg_read_all_stats as elevated because it expands cross-user visibility. Never combine it with pg_signal_backend, which can cancel or terminate sessions.

Symptoms

Security or operational smellBoundary failureImmediate risk
Collector connects as the application, owner, or rds_superuserIdentity separation failedExcessive, unattributable authority
pg_monitor is called harmless read accessEvidence sensitivity was ignoredQuery text, settings, and long-locking functions become reachable
Model emits executable SQLReasoning and collection are combinedNovel or expensive statements reach production
Query text, errors, or plans enter prompts unchangedRedaction was bypassedLiterals, comments, identifiers, and personal data escape
statement_timeout is zeroResource boundary is absentRead-only collection can run indefinitely
CloudTrail is treated as the SQL audit trailAudit planes are conflatedDatabase activity is missing
IAM token expiry is treated as session expiryAuthentication and authorization are confusedEstablished access outlives token generation

First Five Checks

1. Prove the effective database identity

Record login role, memberships, grants, role settings, and targets. Reject superuser, BYPASSRLS, CREATEDB, CREATEROLE, replication, pg_read_all_data, file access, program execution, and backend signaling.

pg_monitor is a bundle, not a synonym for read-only. Prefer a NOINHERIT login with a separately controlled evidence role. Aurora IAM authentication replaces the password with a 15-minute token; PostgreSQL privileges still govern the session. AWS states that CloudWatch and CloudTrail do not log token-generation calls, so record broker authentication separately.

2. Replace arbitrary SQL with a collector catalog

The model may request blocked_sessions_v3 with a window and target; it may not compose a query. Each collector fixes SQL, versions, columns, rows, bytes, topology role, timeout, concurrency, and owner.

Run each database request in a read-only transaction with a short statement_timeout, a shorter lock_timeout, an idle-session limit, a bounded connection pool, and a read-only search path. PostgreSQL read-only mode disallows DML against non-temporary tables, DDL, grants, and unsafe EXPLAIN ANALYZE; it does not make an unbounded SELECT operationally safe.

Avoid SECURITY DEFINER where ordinary grants suffice. If unavoidable, use a trusted search_path, put pg_temp last, schema-qualify objects, revoke default PUBLIC execution, and grant selectively.

3. Minimize query and log content before inference

Start with query identifiers, timings, rows, blocks, waits, and pseudonyms. pg_stat_statements groups structurally equivalent statements, but privileged viewers can see representative text across users. Normalization is not a disclosure guarantee.

Parse known formats and remove unused fields. Strip comments and bind values; use stable keyed pseudonyms where correlation matters. Treat SQL, errors, identifiers, and plans as untrusted data that cannot select tools or override policy. Quarantine unparseable evidence.

4. Bound logging and audit volume

Do not enable log_statement = 'all' merely for an incident. PostgreSQL warns that logs can reveal sensitive data, including plaintext passwords. Prefer duration thresholds or sampling, then configure parameter-length controls. Correlate entries through log_line_prefix, session identity, and incident window.

pgAudit adds session or object auditing through PostgreSQL logging, but audit-all can create disproportionate volume. Keep parameters off unless required. It uses shared_preload_libraries; full DDL object information requires CREATE EXTENSION pgaudit.

5. Reconcile the audit chain

For every pack, store incident ID, target pseudonym, collector and redaction versions, source window and resets, hashes, approver, model and prompt version, tool requests, policy decisions, and output. Preserve allowed and denied requests.

On EC2, reconcile database logs with host identity and process evidence. On Aurora, exported PostgreSQL logs can flow to CloudWatch Logs; CloudTrail records Aurora API actions, not SQL. Database Activity Streams sends database activity to Kinesis and separates stream administration from DBAs, but the stream includes full SQL and, for Aurora PostgreSQL, bind variables and stored-procedure parameters. It belongs in a restricted audit path, not directly in an LLM prompt.

Decision Tree

flowchart TD
    A[diagnostic question] --> B{approved aggregate collector sufficient}
    B -->|yes| C[run baseline role with budgets]
    B -->|no| D{cross-session text required and approved}
    D -->|no| E[request safer evidence or stop]
    D -->|yes| F[activate expiring evidence role]
    C --> G[validate size scope and source time]
    F --> G
    G --> H[redact and pseudonymize]
    H --> I{classification confidence acceptable}
    I -->|no| J[quarantine for security review]
    I -->|yes| K[seal evidence pack and manifest]
    K --> L[LLM correlates evidence]
    L --> M{new request matches catalog and budget}
    M -->|no| N[deny and record]
    M -->|yes| C

What the LLM Can and Cannot Do

It mayIt may not
Separate observations, hypotheses, contradictions, and gapsTreat evidence text as instructions
Rank causes against sanitized evidence IDsConnect to PostgreSQL, EC2, CloudWatch, or RDS
Request an approved collector and bounded parametersWrite or execute arbitrary SQL or shell commands
Explain a plan captured through an approved pathRun EXPLAIN ANALYZE on production SQL automatically
Draft remediation, validation, risk, and rollbackCancel sessions, reset statistics, alter roles, or change parameters
Recommend human escalationReboot, resize, fail over, promote, or delete infrastructure
Identify incomplete audit coverageApprove itself, hide denied requests, or erase evidence

In Practice

PostgreSQL’s predefined-role documentation says pg_monitor includes three monitoring roles and warns that their privileges may change as capabilities are added. Role reviews must therefore recur after major upgrades rather than treating the initial grant as permanent proof.

The pg_stat_statements security model exposes other users’ SQL text and queryid only to superusers and roles with pg_read_all_stats. This is precisely why cross-workload correlation and evidence confidentiality must be designed together.

PostgreSQL’s logging controls separate statement logging, duration thresholds, sampling, and parameter limits, supporting incident-specific collection. pgAudit supports session classes and finer-grained object auditing with superuser-controlled settings.

For Aurora, CloudWatch export preserves logs by retention after export is disabled. CloudTrail captures Aurora API requests. Database Activity Streams captures database activity through Kinesis, including sensitive SQL and parameters. The sources are complementary.

Remediation Options

Existing riskSafer remediationProof
Shared owner credentialDedicated NOINHERIT collector identityEffective grants and connection logs agree
Permanent pg_monitorFixed views plus expiring elevated accessPost-expiry access is denied and recorded
Free-form model SQLVersioned collector catalog and brokerUnknown collector IDs and parameters fail closed
Raw SQL in promptsAggregate-first extraction and deterministic redactionSeeded literals and comments never reach the pack
Unbounded diagnostic readsTimeouts, row and byte caps, concurrency limitsLoad test terminates at every configured budget
One audit sourceReconciled database, host or AWS, pipeline, and approval recordsOne incident ID traces the complete decision
Model-issued remediationSeparate human-approved change identityLLM runtime has no reachable mutation path

Rollback Plan

If diagnostics create load, stop collection, cancel only identified sessions through a separate DBA identity, and revoke elevated membership. Confirm elevated queries fail. Revert incident logging normally; on Aurora, disabling export does not delete existing CloudWatch records.

If evidence exposure occurs, quarantine raw and derived artifacts, remove them from retrieval, disable model access, revoke storage and KMS permissions, and rotate exposed credentials. Apply retention and deletion policy to every copy without deleting the security-event record or its hashes. Verify caches, indexes, backups, and downstream consumers separately.

Where It Breaks

Failure modeWhy the superficial control failsRequired response
Role is read-onlyReads can leak data, lock, spill, and saturate resourcesEnforce identity, catalog, budgets, and disclosure limits
pg_stat_statements is normalizedRepresentative text and object names remain sensitiveMinimize and redact before inference
Collector runs on a replicaReplica lag and local resources remain exposedBudget by instance and protect recovery objectives
pgAudit is enabledCoverage, classes, storage, and retention may still be wrongTest expected and denied events end to end
Database Activity Streams is isolated from DBAsFull SQL and parameters still reach the audit consumerRestrict consumers and sanitize any analytic derivative
IAM token lasts 15 minutesEstablished sessions and database grants are separateBound pool, session, role, and broker lifetime
Prompt injection detection is enabledDetection does not authorize toolsKeep policy and credentials outside the model
Everything is redactedCausal relationships disappearUse approved pseudonyms and preserve restricted originals

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

  • No Raw Customer Data: The LLM must not receive bind values, EXPLAIN ANALYZE output containing literal filter constants, pg_stat_activity.query text with embedded parameters, or table contents. Send normalized pg_stat_statements queryid and digest text, plan shapes, and timings.
  • No Autonomous Production Execution: The LLM cannot run VACUUM FULL, REINDEX, ALTER SYSTEM, pg_terminate_backend, index DDL, or parameter changes. It proposes; a human authorizes, applies, validates, and holds the rollback condition.
  • No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
  • No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
  • No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

  • Problem: PostgreSQL performance evidence crosses privileged statistics, SQL text, engine logs, host or cloud telemetry, and model output; read-only access does not contain disclosure or resource risk.
  • Solution: Separate identities, expose only versioned collectors, enforce runtime budgets, sanitize before inference, reconcile audit planes, and reserve every production change for a distinct human-approved path.
  • Proof: Seed a literal, SQL comment, sensitive identifier, forbidden collector, oversized result, expired role, and prohibited cloud action. Sensitive content must be removed or pseudonymized; unsafe requests must fail; every decision must remain attributable.
  • Action: Inventory current monitoring grants and evidence fields, then run denial, load, leakage, audit, revocation, and recovery tests before adding another PostgreSQL source to the LLM workflow.

The next article begins the Oracle track by using DB time, average active sessions, waits, host evidence, and workload changes to classify an incident before SQL tuning begins.

Sources