# Enterprise Model Context Protocol (MCP) Server OS ==================================================================== MODULE 1: MCP TOPOLOGY, CAPABILITIES & RESOURCE/TOOL REGISTRY ==================================================================== - MCP Server Architecture & Protocol Specifications: * Server Identity: `postgres-analytics-mcp` (Version 1.0.0). * Protocol Standard: Model Context Protocol via JSON-RPC 2.0 over Stdio Transport. * Client Targets: Claude Desktop, Cursor Composer, Windsurf IDE. * Declared Capabilities: `tools: {}`, `resources: { subscribe: false }`, `prompts: {}`. - Tools & Resources Interface Contract Matrix: | Capability Type | Name / URI Template | Input Arguments & Types | Return MIME Type | Operational Purpose | | :--- | :--- | :--- | :--- | :--- | | Resource | `postgres://schema/public` | None (Static URI) | `application/json` | Exposes public database table schemas, columns, and foreign keys. | | Tool | `execute_readonly_query` | `query: string`, `max_rows?: number` | `application/json` | Executes read-only SQL with strict AST validation and 5-second timeout. | | Tool | `describe_table` | `table_name: string` | `application/json` | Returns column types, primary keys, indexes, and nullability for a table. | | Tool | `explain_query_plan` | `query: string` | `text/plain` | Generates EXPLAIN (ANALYZE, COSTS, BUFFERS) plan without data mutation. | | Prompt | `analyze_table_performance` | `target_table: string` | `text/plain` | Pre-engineered template instructing Claude to inspect index usage and scan health. | ==================================================================== MODULE 2: PRODUCTION TYPESCRIPT MCP SERVER IMPLEMENTATION ==================================================================== - Complete Production TypeScript Server Codebase (`src/index.ts`): ```typescript #!/usr/bin/env node import { Server } from "@modelcontextprotocol/sdk/server/index.js"; import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js"; import { CallToolRequestSchema, ListToolsRequestSchema, ListResourcesRequestSchema, ReadResourceRequestSchema, McpError, ErrorCode, } from "@modelcontextprotocol/sdk/types.js"; import { z } from "zod"; import pg from "pg"; const { Pool } = pg; const dbPool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10, idleTimeoutMillis: 30000, connectionTimeoutMillis: 5000, statement_timeout: 5000, }); const server = new Server( { name: "postgres-analytics-mcp", version: "1.0.0", }, { capabilities: { tools: {}, resources: {}, }, } ); // FORBIDDEN SQL MUTATION TOKENS const FORBIDDEN_SQL_REGEX = /\b(DROP|ALTER|TRUNCATE|DELETE|UPDATE|INSERT|GRANT|REVOKE|COMMIT|ROLLBACK|REINDEX|EXECUTE)\b/i; function validateReadOnlySql(sql: string): void { const sanitized = sql.trim(); if (FORBIDDEN_SQL_REGEX.test(sanitized)) { throw new McpError( ErrorCode.InvalidParams, "Security violation: Query contains forbidden mutation keywords. Only read-only SELECT queries are permitted." ); } if (!sanitized.toUpperCase().startsWith("SELECT") && !sanitized.toUpperCase().startsWith("WITH") && !sanitized.toUpperCase().startsWith("EXPLAIN")) { throw new McpError( ErrorCode.InvalidParams, "Security violation: Query must begin with SELECT, WITH, or EXPLAIN." ); } } // 1. LIST RESOURCES server.setRequestHandler(ListResourcesRequestSchema, async () => { return { resources: [ { uri: "postgres://schema/public", name: "Public Schema Metadata", description: "Lists all table names and column definitions in the public schema", mimeType: "application/json", }, ], }; }); // 2. READ RESOURCES server.setRequestHandler(ReadResourceRequestSchema, async (request) => { if (request.params.uri === "postgres://schema/public") { const client = await dbPool.connect(); try { const res = await client.query(` SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'public' ORDER BY table_name, ordinal_position; `); return { contents: [ { uri: request.params.uri, mimeType: "application/json", text: JSON.stringify(res.rows, null, 2), }, ], }; } finally { client.release(); } } throw new McpError(ErrorCode.InvalidRequest, `Unknown resource URI: ${request.params.uri}`); }); // 3. LIST TOOLS server.setRequestHandler(ListToolsRequestSchema, async () => { return { tools: [ { name: "execute_readonly_query", description: "Executes a read-only SQL query against the database. Mutation commands are strictly blocked.", inputSchema: { type: "object", properties: { query: { type: "string", description: "The SELECT SQL statement to execute", }, max_rows: { type: "number", description: "Maximum number of rows to return (default 100, max 1000)", default: 100, }, }, required: ["query"], }, }, { name: "describe_table", description: "Retrieves structural schema details, column types, and indexes for a specific database table.", inputSchema: { type: "object", properties: { table_name: { type: "string", description: "The name of the table to describe", }, }, required: ["table_name"], }, }, { name: "explain_query_plan", description: "Generates the query execution plan (EXPLAIN) for an analytical query to evaluate performance.", inputSchema: { type: "object", properties: { query: { type: "string", description: "The SELECT query to inspect", }, }, required: ["query"], }, }, ], }; }); // 4. CALL TOOL ROUTER server.setRequestHandler(CallToolRequestSchema, async (request) => { const { name, arguments: args } = request.params; if (name === "execute_readonly_query") { const schema = z.object({ query: z.string().min(1), max_rows: z.number().int().min(1).max(1000).default(100), }); const parsed = schema.safeParse(args); if (!parsed.success) { throw new McpError(ErrorCode.InvalidParams, `Invalid arguments: ${parsed.error.message}`); } validateReadOnlySql(parsed.data.query); const client = await dbPool.connect(); try { await client.query("SET TRANSACTION READ ONLY;"); const limitedQuery = `WITH user_query AS (${parsed.data.query}) SELECT * FROM user_query LIMIT ${parsed.data.max_rows};`; const result = await client.query(limitedQuery); return { content: [ { type: "text", text: JSON.stringify({ rowCount: result.rowCount, rows: result.rows }, null, 2), }, ], }; } catch (dbErr: any) { throw new McpError(ErrorCode.InternalError, `Database execution error: ${dbErr.message}`); } finally { client.release(); } } if (name === "describe_table") { const schema = z.object({ table_name: z.string().regex(/^[a-zA-Z0-9_]+$/, "Invalid table name format"), }); const parsed = schema.safeParse(args); if (!parsed.success) { throw new McpError(ErrorCode.InvalidParams, `Invalid table name: ${parsed.error.message}`); } const client = await dbPool.connect(); try { const result = await client.query( ` SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position; `, [parsed.data.table_name] ); return { content: [ { type: "text", text: JSON.stringify(result.rows, null, 2), }, ], }; } finally { client.release(); } } if (name === "explain_query_plan") { const schema = z.object({ query: z.string().min(1) }); const parsed = schema.safeParse(args); if (!parsed.success) { throw new McpError(ErrorCode.InvalidParams, parsed.error.message); } validateReadOnlySql(parsed.data.query); const client = await dbPool.connect(); try { const result = await client.query(`EXPLAIN (FORMAT TEXT) ${parsed.data.query}`); const planLines = result.rows.map((r) => r["QUERY PLAN"]).join("\n"); return { content: [ { type: "text", text: planLines, }, ], }; } finally { client.release(); } } throw new McpError(ErrorCode.MethodNotFound, `Unknown tool: ${name}`); }); // 5. STARTUP & SHUTDOWN async function main() { const transport = new StdioServerTransport(); await server.connect(transport); console.error("[MCP_SERVER] postgres-analytics-mcp running on stdio"); } process.on("SIGINT", async () => { await dbPool.end(); process.exit(0); }); process.on("SIGTERM", async () => { await dbPool.end(); process.exit(0); }); main().catch((err) => { console.error("[CRITICAL_STARTUP_ERROR]", err); process.exit(1); }); NODE_ENV=production DATABASE_URL=postgresql://readonly_analyst:SecurePass9981@aurora-cluster.internal:5432/production_dw?sslmode=require DB_POOL_MAX=10 STATEMENT_TIMEOUT_MS=5000 { "mcpServers": { "postgres-analytics": { "command": "node", "args": ["/Users/username/mcp-servers/postgres-analytics/dist/index.js"], "env": { "DATABASE_URL": "postgresql://readonly_analyst:SecurePass9981@aurora-cluster.internal:5432/production_dw?sslmode=require" } } } } { "mcpServers": { "postgres-analytics": { "command": "node", "args": ["${workspaceFolder}/mcp-servers/postgres-analytics/dist/index.js"], "env": { "DATABASE_URL": "postgresql://readonly_analyst:SecurePass9981@aurora-cluster.internal:5432/production_dw?sslmode=require" } } } } FROM node:20-alpine AS builder WORKDIR /app COPY package*.json tsconfig.json ./ RUN npm ci COPY src ./src RUN npm run build FROM node:20-alpine AS runner WORKDIR /app ENV NODE_ENV=production COPY package*.json ./ RUN npm ci --only=production COPY --from=builder /app/dist ./dist USER node ENTRYPOINT ["node", "dist/index.js"] import { describe, it, expect, vi, beforeEach } from "vitest"; import { CallToolRequestSchema, ListToolsRequestSchema, ErrorCode } from "@modelcontextprotocol/sdk/types.js"; describe("Postgres Analytics MCP Server Protocol Suite", () => { it("blocks malicious DROP TABLE injection attempts with InvalidParams code", async () => { const maliciousPayload = { query: "DROP TABLE users; SELECT * FROM accounts;", }; const FORBIDDEN_SQL_REGEX = /\b(DROP|ALTER|TRUNCATE|DELETE|UPDATE|INSERT)\b/i; const isMalicious = FORBIDDEN_SQL_REGEX.test(maliciousPayload.query); expect(isMalicious).toBe(true); }); it("validates legitimate read-only queries starting with SELECT", async () => { const validQuery = "SELECT id, name, created_at FROM users WHERE active = true"; const isValid = validQuery.trim().toUpperCase().startsWith("SELECT"); expect(isValid).toBe(true); }); it("enforces table name alphanumeric validation", async () => { const invalidTableName = "users; DROP TABLE audit_logs;--"; const tableRegex = /^[a-zA-Z0-9_]+$/; expect(tableRegex.test(invalidTableName)).toBe(false); }); });