Stand Up an Incident Copilot over Read-Only MCP

Tier: Core · Workflow area: Reliability & Incident Ops · ~40 minutes

The Model Context Protocol turns an assistant from a text generator into an investigator: it can run diagnostic SQL against your instance and reason over actual results instead of your paraphrase of them. The whole design question is blast radius. Built right — dedicated read-only role, statement timeouts, restricted schema surface, audit logging — the copilot can only ever read, briefly, and on the record. This playbook builds that configuration first and connects the assistant second, in that order, every time.

When to use

• On-call triage acceleration3 a.m. questions like "what changed in the last 6 hours and what's blocking?" answered with evidence in one conversation instead of six psql sessions.
• Estates with more services than expertsthe copilot democratizes diagnostic SQL the two senior DBAs currently carry in their heads.
• Post-incident explorationinterrogating pg_stat_statements and catalog state conversationally, with every probe logged.

When NOT to use

• Anything write-shapedno kills, no config changes, no "fix it" actions. The moment the copilot can change state, it is an unreviewed change agent inside an incident. Reads only.
• Against the struggling primary itselfduring capacity incidents, point the copilot at a replica or monitoring store; diagnostic load on a dying primary is added harm.
• Where data classification bars external processing of query resultsthe copilot sees actual rows from views you expose. If regulated columns cannot transit the model, expose only aggregate views and system catalogs, or run a tenant-approved model.

The recipe

1. 1.

   Build the role before touching any MCP config — permissions are the real guardrail.

   Copy

   CREATE ROLE incident_copilot LOGIN PASSWORD '...'
     CONNECTION LIMIT 3;
   
   -- Hard ceilings, enforced by the engine, not by the tool
   ALTER ROLE incident_copilot SET statement_timeout = '10s';
   ALTER ROLE incident_copilot SET idle_in_transaction_session_timeout = '15s';
   ALTER ROLE incident_copilot SET default_transaction_read_only = on;
   
   GRANT pg_monitor TO incident_copilot;     -- pg_stat_*, activity views
   GRANT CONNECT ON DATABASE appdb TO incident_copilot;
   GRANT USAGE ON SCHEMA public TO incident_copilot;
   -- Deliberately: NO table SELECT grants on application data yet.
   

   Starting with monitoring views only is the right default: most triage questions ("what's slow, what's blocking, what's bloated") need pg_monitor, not customer rows.

2. 2.

   Test the cage from psql before any AI is involved.

   Copy

   -- As incident_copilot: all of these must FAIL
   CREATE TABLE t(x int);            -- read-only violation
   SELECT * FROM customers LIMIT 1;  -- no grant
   SELECT pg_sleep(30);              -- statement_timeout at 10s
   
   -- And these must succeed
   SELECT * FROM pg_stat_activity WHERE state <> 'idle';
   SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
   

   A cage you did not test is a cage you assume. Keep the failed-command transcript; it goes in the deployment note.

3. 3.

   Stand up the MCP server on that role. With Postgres MCP Pro (or an equivalent Postgres MCP server), run restricted mode and the copilot credentials:

   Copy

   postgres-mcp --access-mode=restricted \
     "postgresql://incident_copilot:...@db-replica:5432/appdb"
   

   Then register it with your assistant — for Claude Code:

   Copy

   claude mcp add incident-db -- postgres-mcp --access-mode=restricted \
     "postgresql://incident_copilot:...@db-replica:5432/appdb"
   

   Restricted/read-only mode at the tool layer plus the read-only role at the engine layer is deliberate redundancy — either alone is one bug away from a write path.

4. 4.

   Turn on audit logging for the copilot's session. Every query the model runs must be attributable later:

   Copy

   ALTER ROLE incident_copilot SET log_statement = 'all';
   ALTER ROLE incident_copilot SET log_min_duration_statement = 0;
   

   The log line prefix should carry the role name so grep incident_copilot reconstructs any session. During a real incident postmortem, this transcript of what the copilot probed is evidence, exactly like a human responder's shell history.

5. 5.

   Wire in the context that makes answers good: recent changes. "What changed?" needs a change source. Give the copilot a view over your deploy/migration log, or paste the change feed into the conversation at incident start. A system prompt for the incident channel completes it:

   Copy

   You are an incident triage copilot with read-only SQL access via MCP.
   Rules: cite the query and result behind every claim; say "not
   observable with my access" rather than guessing; propose — never
   execute — any remediation; remediation decisions belong to the human
   responder. Standard first moves: current activity and blocking, top
   statements by total time since stats reset, recent autovacuum
   activity, connection counts by state and client.
   

6. 6.

   Rehearse with an injected fault before trusting it in a real page. On a lab or staging instance, create a blocking chain (open transaction holding a lock, second session waiting) and a runaway query, then ask the copilot cold: "What is blocking and for how long? What would you check next?" Grade the transcript: did every claim carry a query and result, did it stay read-only, did it admit blind spots? Fix the system prompt and grants based on misses, and file the transcript as the acceptance record.

Make it yours

• SQL Serversame architecture with a login granted VIEW SERVER PERFORMANCE STATE only, mapped through an MSSQL MCP server; triage vocabulary becomes sys.dm_exec_requests, blocking chains and Query Store regressions.
• MySQLa user with PROCESS and performance_schema SELECT only; the copilot's first moves are sys.innodb_lock_waits and statement summary tables.
• Fleet monitoring storespointing MCP at a central pganalyze/Datadog-style store instead of instances gives cross-fleet questions with zero production connection cost — often the better first deployment.

Quality check

• The engine enforces read-only, not just the MCP layerrole-level default_transaction_read_only, timeouts and absent grants were each demonstrated to fail from psql, with the transcript kept.
• The copilot's data surface was granted deliberatelystart at pg_monitor; every application table added later is a recorded decision with a reason, not a convenience grant mid-incident.
• Every session is reconstructable from logsif you cannot produce the exact SQL the copilot ran last Tuesday, audit logging is not done.
• The rehearsal transcript exists and was gradedcited claims, admitted blind spots, zero write attempts. No copilot joins the on-call rotation without its acceptance test.
• Humans execute all remediationthe copilot proposes; the responder decides and types. Any drift toward "just let it run the fix" reopens this whole design.