{
  "$schema": "https://promptfoo.tech/schemas/workflow-template.json",
  "name": "Claude + MCP: Postgres Read-Only Analyst",
  "slug": "claude-mcp-postgres-analyst",
  "platform": "Claude Desktop",
  "category": "mcp",
  "difficulty": "Intermediate",
  "tags": [
    "claude",
    "mcp",
    "postgres",
    "analytics"
  ],
  "summary": "Give Claude Desktop safe, read-only access to a Postgres warehouse via MCP — natural-language analytics without another BI seat.",
  "overview": "BI tools are great for dashboards, bad for one-off questions. This build wires Claude Desktop to a read-only Postgres role via the official Postgres MCP server. Ask questions in English, get real SQL back, and see results as tables. The read-only role is the guardrail; the LLM is the interface.",
  "variables": {
    "ANTHROPIC_API_KEY": "sk-ant-..."
  },
  "steps": [
    {
      "order": 1,
      "name": "Create the read-only role",
      "description": "CREATE ROLE claude_ro LOGIN PASSWORD '...'; GRANT USAGE ON SCHEMA analytics TO claude_ro; GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO claude_ro; ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT SELECT ON TABLES TO claude_ro;"
    },
    {
      "order": 2,
      "name": "Restrict connection source",
      "description": "pg_hba.conf: allow claude_ro only from your machine's IP. Or run behind a Tailscale ACL."
    },
    {
      "order": 3,
      "name": "Install the MCP server",
      "description": "npm install -g @modelcontextprotocol/server-postgres. Test connection standalone first."
    },
    {
      "order": 4,
      "name": "Register in Claude",
      "description": "mcpServers.postgres: { command: ..., env: { DATABASE_URL: postgres://claude_ro:...@host/db } }."
    },
    {
      "order": 5,
      "name": "Prompt with schema context",
      "description": "'The analytics schema has orders, users, events. Use snake_case columns. Prefer window functions over subqueries when possible.'"
    },
    {
      "order": 6,
      "name": "Sanity check + iterate",
      "description": "Ask Claude to run EXPLAIN before large queries. Add row limits by default in your system message."
    }
  ],
  "howItWorks": [
    "Create a read-only role in Postgres with SELECT on the analytics schema only.",
    "Install and configure the Postgres MCP server pointed at that role.",
    "Register in claude_desktop_config.json.",
    "Claude discovers list_tables, describe_table, query tools.",
    "Every query runs under the read-only user — no chance of accidental writes."
  ],
  "benefits": [
    "PMs and founders get self-serve analytics without a BI license per seat.",
    "SQL is generated and shown, so answers are auditable.",
    "Read-only role means no risk of destructive queries."
  ],
  "useCases": [
    "Startups with 3-5 analytics questions a day that don't justify a dashboard.",
    "Data teams offloading ad-hoc requests.",
    "Product managers exploring hypotheses before opening a Metabase card."
  ],
  "example": "You: 'What was the DAU trend for the last 30 days, week over week?'\nClaude: writes a query using date_trunc, runs it, returns a table + brief interpretation.",
  "faqs": [
    {
      "q": "What if it writes a bad query?",
      "a": "The role is read-only. Worst case is a slow query. Add statement_timeout at the role level to bound it."
    },
    {
      "q": "Row limits?",
      "a": "Include 'always LIMIT 1000 unless asked otherwise' in your system prompt. Also set default_transaction_read_only=on."
    },
    {
      "q": "Can I share with the team?",
      "a": "Yes — each person needs their own Claude Desktop config; share the read-only credential securely."
    }
  ],
  "source": "https://promptfoo.tech/workflows/claude-mcp-postgres-analyst",
  "updated": "2026-06-19",
  "license": "CC-BY-4.0"
}