Audit Trail

postgres-mcp includes a built-in JSONL audit trail with per-call token estimates, OAuth identity, timing, and outcome. Write/admin tools are always logged; read-scoped tools can be opted in via --audit-reads. Size-based log rotation prevents unbounded growth.

Enabling

CLI flags:

node dist/cli.js --audit-log /var/log/audit.jsonl --postgres "postgres://..."

# Omit tool arguments from entries
node dist/cli.js --audit-log /var/log/audit.jsonl --audit-redact --postgres "postgres://..."

Environment variables:

Variable	Default	Description
`AUDIT_LOG_PATH`	—	File path or `stderr` to enable audit logging
`AUDIT_REDACT`	`false`	Omit tool arguments from entries (privacy/compliance)
`AUDIT_READS`	`false`	Log read-scoped tool calls (compact entries)
`AUDIT_LOG_MAX_SIZE`	`10485760`	Max log file size in bytes before rotation (10MB)

Docker:

docker run --rm -p 3000:3000 \
  -e POSTGRES_URL=postgres://user:pass@host:5432/db \
  -e AUDIT_LOG_PATH=/var/log/audit.jsonl \
  -v /host/logs:/var/log \
  writenotenow/postgres-mcp:latest \
  --transport http --port 3000

Container Mode (stderr)

For Docker, Kubernetes, and any orchestrated environment, route audit entries directly to the container log stream:

--audit-log stderr
# or
AUDIT_LOG_PATH=stderr

The orchestrator (Docker log driver, Kubernetes Fluentd/Fluentbit, CloudWatch Logs, etc.) captures stderr natively—no volume mounts or log rotation needed. Entries are JSON-per-line, compatible with structured log parsers.

JSONL Format

Each line is a self-contained JSON object:

{
  "timestamp": "2026-03-23T05:21:50.760Z",
  "requestId": "75e9f60f-aad0-4519-81db-e0a79a57dd0e",
  "tool": "pg_transaction_begin",
  "category": "write",
  "scope": "write",
  "user": "jane@example.com",
  "scopes": ["read", "write"],
  "durationMs": 3,
  "success": true,
  "args": {},
  "tokenEstimate": 42
}

Field Reference

Field	Type	Description
`timestamp`	ISO 8601 string	UTC timestamp of the invocation
`requestId`	UUID string	Correlation ID for the MCP request
`tool`	string	MCP tool name (e.g., `pg_transaction_begin`)
`category`	`read`/`write`/`admin`	Derived from the tool's OAuth scope group
`scope`	string	Required OAuth scope for this tool
`user`	string or null	OAuth `sub` claim (null for reads and no-OAuth)
`scopes`	string[]	OAuth scopes granted to the caller
`durationMs`	number	Execution time in milliseconds
`success`	boolean	Whether the tool completed without error
`error`	string?	Error message (present only on failure)
`args`	object?	Tool input arguments (omitted for reads and `--audit-redact`)
`tokenEstimate`	number?	Estimated token count of the tool response (~4 bytes/token)

Which Tools Are Logged?

Write and admin tools are always logged. Read-scoped tools are logged only when --audit-reads (or AUDIT_READS=true) is enabled.

Scope	Groups / Tools	Logged?
`read`	core (read-only tools), schema, jsonb, text, stats, monitoring, performance, vector, postgis, introspection, kcache, citext, ltree, pgcrypto, security, docstore	⚡ Opt-in (`--audit-reads`)
`write`	transactions, migration, core write tools (`pg_write_query`, `pg_create_table`, `pg_create_index`, `pg_upsert`, `pg_batch_insert`), docstore write tools	✅ Always
`admin`	admin, backup, cron, partman, partitioning, roles, codemode, core destructive tools (`pg_drop_table`, `pg_drop_index`, `pg_truncate`), docstore admin tools	✅ Always

Read entries are compact — args, user, and scopes are omitted to keep per-entry size around ~100 bytes.

Per-tool scope overrides: 8 core group tools have individual scope overrides (5 → write, 3 → admin) so they are properly audited and OAuth-protected despite being in the read-scope core group. See TOOL_SCOPE_OVERRIDES in src/auth/scopes.ts.

Agent Access

The postgres://audit resource exposes recent audit entries with a session summary to AI agents:

{
  "summary": {
    "totalTokenEstimate": 14200,
    "callCount": 47,
    "topToolsByTokens": [
      { "tool": "pg_read_query", "calls": 12, "tokens": 8400 },
      { "tool": "pg_execute_code", "calls": 3, "tokens": 3100 }
    ],
    "note": "Last 47 tool calls consumed ~14,200 tokens"
  },
  "entries": [{ "tool": "pg_read_query", "tokenEstimate": 420, "..." }],
  "total": 47
}

The summary block gives agents session-level token consumption visibility without parsing individual entries. When audit logging is disabled, the resource returns an empty array with a status message.

Log Rotation

The audit log rotates automatically when it exceeds AUDIT_LOG_MAX_SIZE (default: 10MB). On rotation, the current file cascades down a 5-tiered rotation chain (e.g., .4 becomes .5, and the active log rotates to .1). A maximum of 5 rotated archive files are securely kept natively.

For container deployments using --audit-log stderr, rotation is unnecessary—the orchestrator handles log management.

Log Shipping

JSONL format is directly compatible with log aggregation platforms:

Platform	Method
Datadog	File tailing agent or stderr → Docker log driver
Grafana Loki	Promtail file/journal source
Elastic/ELK	Filebeat JSONL input
Splunk	Universal Forwarder or HEC (JSON)
CloudWatch Logs	`awslogs` Docker log driver (stderr mode)
Fluentd/Fluentbit	`tail` plugin with JSON parser

For container deployments, --audit-log stderr is the recommended approach—the orchestrator handles shipping, rotation, and retention.

Backup Snapshots

Pre-mutation DDL snapshots automatically capture the state of database objects before destructive or write operations. This enables point-in-time recovery and schema drift detection.

Enabling

# Enable audit log + backup snapshots
node dist/cli.js --audit-log /var/log/audit.jsonl --audit-backup --postgres "postgres://..."

# Also capture sample data (up to 100 rows)
node dist/cli.js --audit-log /var/log/audit.jsonl --audit-backup --audit-backup-data --postgres "postgres://..."

Variable	Default	Description
`AUDIT_BACKUP`	`false`	Enable pre-mutation snapshots
`AUDIT_BACKUP_DATA`	`false`	Include sample data rows in snapshots
`AUDIT_BACKUP_MAX_AGE`	`30`	Maximum snapshot age in days
`AUDIT_BACKUP_MAX_COUNT`	`100`	Maximum number of snapshots to retain
`AUDIT_BACKUP_MAX_DATA_SIZE`	`52428800`	Max table size for data capture (50MB)

Snapshotted Tools

Snapshots are triggered by these tools (all others are audited but don't produce snapshots):

Tool	Snapshot Type
`pg_drop_table`	Table DDL (+data)
`pg_drop_index`	Object DDL
`pg_truncate`	Table DDL (+data)
`pg_vacuum`	Table DDL
`pg_reindex`	Table DDL
`pg_cluster`	Table DDL
`pg_copy_import`	Table DDL (+data)
`pg_drop_schema`	Schema object list
`pg_drop_view`	View DDL
`pg_drop_sequence`	Sequence DDL
`pg_detach_partition`	Partition DDL
`pg_migration_apply`	Migration marker
`pg_migration_rollback`	Migration marker

Backup Management Tools

Three MCP tools provide agent access to backup snapshots:

Tool	Scope	Description
`pg_audit_list_backups`	`read`	List snapshots with optional tool/target filter
`pg_audit_restore_backup`	`admin`	Restore snapshot DDL+data (supports dry run, non-destructive `restoreAs`)
`pg_audit_diff_backup`	`read`	Compare snapshot DDL against live schema; includes `volumeDrift`

v2.3.0+ features: pg_audit_restore_backup supports restoreAs: "new_table_name" for non-destructive side-by-side restore — the original table is untouched and the snapshot is restored under a new name. pg_audit_diff_backup now returns volumeDrift: { rowCountSnapshot, rowCountCurrent, sizeBytesSnapshot, sizeBytesCurrent, summary } for row count and size drift detection alongside schema diff.

Retention

Snapshots are cleaned up automatically during each snapshot capture cycle based on maxAgeDays and maxCount. The oldest snapshots exceeding either limit are deleted first.

Implementation Details

The audit system is implemented in src/audit/ with four components:

types.ts — AuditEntry (with tokenEstimate), AuditConfig (with auditReads, maxSizeBytes), BackupConfig, SnapshotMetadata, SnapshotContent interfaces
logger.ts — AuditLogger class with async-buffered writes (100ms flush interval, 50-entry high water mark) and size-based log rotation. Zero performance impact on tool execution
interceptor.ts — createAuditInterceptor() wraps DatabaseAdapter.registerTool() to intercept all tool handlers. Computes tokenEstimate from JSON.stringify(result) byte length (~4 bytes per token). Gates read-scope logging behind auditReads config. Optionally captures pre-mutation snapshots via BackupManager
backup-manager.ts — BackupManager class for pre-mutation DDL/data snapshot capture, retention cleanup, and stats. Non-throwing design ensures snapshot failures never block tool execution

The interceptor reads OAuth identity from AsyncLocalStorage and determines the tool's required scope via the scope map at src/auth/scope-map.ts.

Audit Trail

Audit Trail

Enabling

Container Mode (stderr)

JSONL Format

Field Reference

Which Tools Are Logged?

Agent Access

Log Rotation

Log Shipping

Backup Snapshots

Enabling

Snapshotted Tools

Backup Management Tools

Retention

Implementation Details

Related

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

PostgreSQL MCP Server

📣 Updates

🚀 Getting Started

🔌 Extensions

🤖 AI Integration

🔐 Enterprise

🔗 External Links

Clone this wiki locally