Claude Code MCP Postgres: separate investigation from migration authority

Claude Code can inspect an application, trace a failing query, and propose a PostgreSQL migration in one session.

That does not mean the same session should be allowed to execute the migration.

Investigation and mutation have different blast radii. A useful Claude Code MCP Postgres setup keeps them behind different tools, identities, environments, and approvals.

Start with the developer workflow

Define what Claude Code actually needs to accomplish. Typical read workflows include inspecting schema, explaining a query plan, sampling redacted rows, finding lock contention, comparing migration metadata, and checking whether application code matches the deployed schema.

Write workflows are different: creating an index, changing a constraint, running a backfill, modifying a role, terminating sessions, or deploying a migration. Do not bundle both categories into one broad database tool.

Use separate database identities

Create a dedicated investigation role with access only to approved schemas, catalog views, statistics, and redacted application views. Avoid reusing the application owner, migration role, or a developer's personal superuser connection.

If mutation is required, use a separate short-lived execution identity with the minimum privileges for one approved operation. The model should never receive or choose the credential directly.

The baseline connection guide is connect PostgreSQL to Claude.

Separate tool discovery

The investigation server should expose read tools such as:

• describe_schema for approved objects;
• explain_query with no execution by default;
• get_lock_summary with bounded detail;
• sample_view with row and field limits;
• get_migration_status from a known history table.

Mutation tools should not appear merely because the database role could technically perform them. Discovery is part of the authority surface.

Make environment explicit

Bind every server, tool call, and result to an environment identifier. Development, staging, and production should use different endpoints, credentials, policies, and visible labels.

Reject an omitted environment instead of selecting production from a default configuration or previous conversation. Test copied configs, stale terminals, renamed clusters, and failover endpoints.

Return a migration plan, not executable prose

A proposed change should be a structured artifact containing target environment, database and schema, current schema version, expected application version, preconditions, ordered statements, lock expectations, timeouts, affected-object estimates, compatibility window, validation queries, rollback or roll-forward plan, and plan digest.

Free-form SQL in a chat message is useful for review, but it is not an execution authorization.

Use schema migrations with AI agents for a deeper rollout sequence.

Validate against current state

Before approval, compare the plan with the current schema, migration history, extension versions, PostgreSQL version, table size, indexes, constraints, active locks, replication topology, and application compatibility.

Before execution, repeat the checks. Bind approval to a plan digest and observed-state version. If the schema or relevant runtime state changed, invalidate the approval and produce a new plan.

Use preview and impact evidence

Estimate affected rows and objects without running the mutation. For data changes, provide a deterministic selection query, counts by tenant or category, sample identifiers, and a checksum or snapshot reference.

For DDL, include expected lock mode, scan or rewrite behavior, disk and WAL implications, replication effects, and whether the operation can run concurrently or in phases.

PostgreSQL behavior depends on version, table shape, and current workload. Avoid generic “zero downtime” claims.

Approve the exact operation

The approval should identify the plan digest, target, scope, expiry, approver, allowed execution window, and maximum impact. It should not authorize “whatever Claude decides next.”

Require a fresh approval when the target changes, the plan changes, the preconditions drift, the window expires, or impact exceeds the approved bound.

Execute through a controlled runner

A migration runner—not the conversational agent—should exchange the approval for a short-lived execution identity, revalidate state, apply timeouts, record each step, stop on violated invariants, and return a durable receipt.

The receipt should include plan digest, target, identity, statements or migration IDs, timestamps, affected rows or objects, validation results, final schema version, and trace linkage. Never log credentials or sensitive row contents.

Design recovery before execution

Many PostgreSQL changes are safer to roll forward than to roll back. Mark the point of no return, define resumable batches for backfills, preserve old application compatibility during phased changes, and rehearse failure between steps.

Useful controls include lock_timeout, statement_timeout, bounded batches, checkpoints, reconciliation queries, validated constraints, and explicit cancellation behavior.

Concrete example: adding a non-null column

Claude Code finds that application code expects orders.fulfilment_region. The investigation identity confirms the column is missing and reads table statistics.

Claude proposes an expand-and-contract plan: add a nullable column, deploy dual-write application code, backfill in resumable batches, reconcile nulls and source values, add and validate a check constraint, switch reads, then enforce the final constraint in an approved window.

The plan is reviewed and bound to the current migration version. A runner executes it with a separate identity. Claude Code never receives production DDL authority.

Boundary test checklist

1. Ask the investigation tool to run DDL and verify refusal.
2. Omit the environment and verify fail-closed behavior.
3. Reuse an approval after the schema changes.
4. Change one statement after approval.
5. Exceed the affected-row or lock budget.
6. Interrupt a backfill and resume from its checkpoint.
7. Revoke the execution identity during a persistent session.
8. Reconstruct the change from plan, approval, runner, and database evidence.

Where Conexor fits

Conexor provides MCP infrastructure for connecting Claude and other AI clients to PostgreSQL and other data sources through governed tools. The safest developer setup begins with scoped read-only investigation and keeps production mutations behind a separate controlled workflow.

Review the PostgreSQL and Claude setup

Then add approval gates for AI database writes.