PostgreSQL 数据探索与 API 集成 MCP 服务器

workbench-mcp

A local Python MCP server for interactive PostgreSQL data exploration, API integration, and automation on Fedora/Linux systems.

Overview

Version 1 includes:

• Python virtual environment setup for Fedora/Linux systems
• PostgreSQL 18 connectivity configured via .env file
• MCP tools for:
  • Discovering tables, columns, and schema structure
  • Running read-only query previews
  • Executing guarded SQL batches with temporary table support
  • Calling PostgreSQL stored functions and procedures
  • Accessing your backend API through HTTP tools using resource paths (host and base come from API_BASE_URL)
  • Executing bash scripts available in PATH
• Enforced safety: persistent schema and data modifications are blocked
• Session-scoped temporary table workflows supported within SQL batches

Fedora / Linux Setup

Start by installing required system packages:

sudo dnf install -y python3 python3-pip nodejs npm

Python 3.12 or later is required. Use pyenv or similar if managing multiple versions.

Virtual Environment Setup

From the project root, create and activate a Python virtual environment:

python3 -m venv .venv
source .venv/bin/activate
python -m pip install --upgrade pip
pip install -e .

Environment Variables

Copy the example configuration and populate PostgreSQL connection details:

cp .env.example .env

Required:

• DB_HOST — PostgreSQL server hostname
• DB_NAME — Database name
• DB_USER — Database username
• DB_PASSWORD — Database password

Optional (tuning):

• DB_PORT — Connection port (default: 5432)
• DB_SSLMODE — SSL mode (default: prefer)
• DB_APPLICATION_NAME — Application identifier
• DB_QUERY_TIMEOUT_SECONDS — Query timeout (default: 30)
• DB_MAX_ROWS — Maximum rows per result set (default: 100)
• DB_MAX_RESULT_SETS — Maximum result sets per batch (default: 5)
• DB_OBJECT_PREVIEW_CHARS — Max definition preview length (default: 4000)

Example local development:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=app_dev
DB_USER=app_user
DB_PASSWORD=your-secure-password
DB_SSLMODE=prefer

Optional: HTTP Request Tuning

The HTTP tools (http_get, http_head, http_post, http_put, http_patch, http_delete) receive only a resource path (e.g. /account/session-info) and append it to the server base URL configured with API_BASE_URL (e.g. https://localhost:5004/api/v1). They do not accept full URLs — the scheme, host, and /api/v1 prefix always come from the environment.

Supported environment settings:

Example call shape (with API_BASE_URL=https://localhost:5004/api/v1):

path: /breakouts/filter/1871161/dd-table?ParameterSetId=231022
method: GET

This issues GET https://localhost:5004/api/v1/breakouts/filter/1871161/dd-table?ParameterSetId=231022.

For authenticated calls, set API_BEARER_TOKEN in .env (or process env). HTTP tools automatically use it unless the caller passes its own jwt_token.

Authorization Handling

HTTP tools support two authorization sources:

1. jwt_token passed in the tool call
2. API_BEARER_TOKEN from .env or process environment

Precedence

• If jwt_token is provided, that token is forwarded as Authorization: Bearer <jwt_token>.
• If jwt_token is omitted or blank, the server falls back to API_BEARER_TOKEN.
• If neither value is present, the request is sent without an Authorization header.

Important rule for agents

Do not place the bearer token inside headers.Authorization. The MCP server strips Authorization from headers and only accepts auth through the dedicated jwt_token field.

This prevents accidental header collisions and makes token precedence explicit.

Example: use the default server token

With API_BASE_URL=https://localhost:5004/api/v1, pass only the resource path:

{
  "path": "/sales/my-sales"
}

Example: forward the caller's own token

{
  "path": "/sales/my-sales",
  "jwt_token": "eyJhbGciOi..."
}

Example: forward caller token with extra headers

{
  "path": "/sales/my-sales",
  "jwt_token": "eyJhbGciOi...",
  "headers": {
    "Accept": "application/json"
  }
}

The same jwt_token field is available on http_get, http_head, http_post, http_put, http_patch, and http_delete, and all of them accept a path (never a full URL).

Session Auth

Instead of passing a per-call jwt_token, agents can acquire a session-scoped JWT once and have every HTTP tool call use it automatically for the rest of the session.

How it works

1. An agent calls auth_start_session with the target user's email.
2. The MCP server exchanges the shared secret + email for a scoped JWT from the backend broker (POST /api/v1/mcp/exchange).
3. The token is cached in process memory.
4. Every subsequent HTTP tool call that omits jwt_token uses the session token automatically.
5. The agent can inspect the session with auth_status, switch users with auth_switch_user, or clear it with auth_clear_session.

Token precedence (highest → lowest)

Required environment variables

Session auth tools

See docs/SESSION_AUTH.md for the full agent-facing reference.

Run Locally

After activating the virtual environment and installing dependencies, start the MCP server with either command:

workbench-mcp

python -m workbench_mcp.server

MCP Inspector

For local MCP development and debugging, the MCP Inspector provides a fast manual test loop:

npx @modelcontextprotocol/inspector .venv/bin/python -m workbench_mcp.server

To launch the MCP server under debugpy for breakpoint debugging in the Inspector:

npx @modelcontextprotocol/inspector .venv/bin/python -m debugpy --listen 127.0.0.1:5678 -m workbench_mcp.server

After launch, open the Inspector UI, connect over STDIO, and test tools such as health, describe_object, and exec_proc_preview.

Breakpoints (debugpy): Use port 5678 for the debugger, not 6274 (6274 is only the Inspector web UI). Step-by-step workflow and “what was wrong before” are in docs/DEBUG_MCP.md.

VS Code Setup

To register the local MCP server in VS Code, add an entry to the workspace MCP configuration file:

• Workspace file: .vscode/mcp.json

Example configuration:

{
  "servers": {
    "workbench-mcp": {
      "type": "stdio",
      "command": "/absolute/path/to/workbench-mcp/.venv/bin/python",
      "args": ["-m", "workbench_mcp.server"]
    }
  }
}

Replace the command path with the local repository path to your virtual environment Python.

Secrets and Environment Values

You can supply environment values in either place:

1. workbench-mcp/.env
2. env in .vscode/mcp.json — VS Code injects these into the MCP server process.

Precedence: process environment (including .vscode/mcp.json → env) overrides values from .env for the same key.

Example with HTTP tuning in VS Code:

{
  "servers": {
    "workbench-mcp": {
      "type": "stdio",
      "command": "/absolute/path/to/workbench-mcp/.venv/bin/python",
      "args": ["-m", "workbench_mcp.server"],
      "env": {
        "API_TIMEOUT_SECONDS": "30",
        "API_MAX_RESPONSE_BYTES": "2097152",
        "API_VERIFY_SSL": "false"
      }
    }
  }
}

Do not commit real tokens. Prefer a local-only workspace configuration or omit env and use .env (which should stay out of git).

If other MCP servers are already configured, add workbench-mcp inside the existing servers object instead of replacing the entire file.

After saving .vscode/mcp.json, reload VS Code or refresh MCP servers so the new server is discovered. After the server loads, run the health tool before testing database procedures.

Initial Tools

• health
• describe_object
• list_tables_and_columns
• preview_query
• execute_readonly_sql
• exec_proc_preview
• exec_function_preview
• insert_row
• insert_rows
• http_get
• http_head
• http_post
• http_put
• http_patch
• http_delete
• auth_start_session
• auth_switch_user
• auth_status
• auth_clear_session
• execute_path_bash_script (script name resolved via PATH)

Safety Model

• Persistent DDL and DML are blocked in ad-hoc PostgreSQL batches
• Only temp-table writes are allowed, and only for temp tables created in the current batch
• preview_query allows only SELECT statements and CTE-based reads
• exec_proc_preview can execute PostgreSQL procedures and functions; overloaded routines should be passed with a signature such as public.my_func(integer, text)
• execute_path_bash_script only accepts script names (not paths), resolves them via PATH, and executes through bash

Suggested First Checks

After .env is configured, a typical validation flow is:

1. Describe the function, procedure, table, or view to inspect.
2. Preview the supporting configuration or reference data needed to understand that object.
3. Run exec_proc_preview, preview_query, or execute_readonly_sql with known inputs.
4. Compare the returned shape with the feature, investigation, or debugging scenario being evaluated.

Function Execution Example

For positional PostgreSQL function calls, use exec_function_preview. Pass PostgreSQL arrays as normal JSON lists.

Example SQL target:

select * from sales."Fn_GetSalesChamps"(2, 2025, array[1,2,5,6,7,8,9,10,11,12,15,16,18,19], 5);

Equivalent MCP tool input:

{
  "function_name": "sales.\"Fn_GetSalesChamps\"",
  "parameters": [2, 2025, [1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 15, 16, 18, 19], 5]
}

Insert Examples

Single row insert:

{
  "table_name": "sales.orders",
  "row": {
    "customer_id": 10,
    "status": "new"
  },
  "returning_columns": ["order_id"]
}

Batch insert:

{
  "table_name": "sales.orders",
  "rows": [
    {"customer_id": 10, "status": "new"},
    {"customer_id": 11, "status": "pending"}
  ]
}