# MCP Production Server Package ==================================================================== MODULE 1: REPOSITORY MANIFEST, DOCKERFILE & ENV TEMPLATE ==================================================================== - Package Manifest (`package.json`): ```json { "name": "postgres-mcp-server", "version": "1.0.0", "type": "module", "main": "dist/index.js", "scripts": { "build": "tsc", "start": "node dist/index.js" }, "dependencies": { "@modelcontextprotocol/sdk": "^1.0.4", "pg": "^8.13.1" }, "devDependencies": { "@types/node": "^22.10.1", "@types/pg": "^8.11.10", "typescript": "^5.7.2" } } 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 CMD ["node", "dist/index.js"] DATABASE_URL=postgresql://postgres.user:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres STATEMENT_TIMEOUT_MS=3000 MAX_QUERY_ROW_LIMIT=100 import { Server } from "@modelcontextprotocol/sdk/server/index.js"; import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js"; import { CallToolRequestSchema, ListToolsRequestSchema } from "@modelcontextprotocol/sdk/types.js"; import pg from "pg"; const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 5, statement_timeout: Number(process.env.STATEMENT_TIMEOUT_MS) || 3000, ssl: { rejectUnauthorized: false } }); const server = new Server( { name: "postgres-mcp-server", version: "1.0.0" }, { capabilities: { tools: {} } } ); server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: [ { name: "list_tables", description: "List all accessible tables in the database schema", inputSchema: { type: "object", properties: { schema_name: { type: "string", default: "public", description: "Target database schema" } } } }, { name: "describe_table", description: "Inspect column names, data types, and constraints for a specific table", inputSchema: { type: "object", properties: { table_name: { type: "string", description: "Name of the table to inspect" } }, required: ["table_name"] } }, { name: "execute_read_query", description: "Run a parameterized read-only SQL SELECT query with safety row limits", inputSchema: { type: "object", properties: { query: { type: "string", description: "The SQL SELECT statement to execute" }, limit: { type: "integer", default: 50, description: "Maximum rows to return (cap: 100)" } }, required: ["query"] } } ] })); server.setRequestHandler(CallToolRequestSchema, async (request) => { const { name, arguments: args } = request.params; const client = await pool.connect(); try { if (name === "list_tables") { const schema = (args?.schema_name as string) || "public"; const res = await client.query( "SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = $1 ORDER BY table_name", [schema] ); return { content: [{ type: "text", text: JSON.stringify(res.rows, null, 2) }] }; } if (name === "describe_table") { const tableName = args?.table_name as string; const res = await client.query( "SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = $1 ORDER BY ordinal_position", [tableName] ); return { content: [{ type: "text", text: JSON.stringify(res.rows, null, 2) }] }; } if (name === "execute_read_query") { const rawQuery = (args?.query as string).trim(); const limit = Math.min(Number(args?.limit) || 50, Number(process.env.MAX_QUERY_ROW_LIMIT) || 100); if (!/^SELECT\b/i.test(rawQuery)) { throw new Error("Security Violation: Only SELECT statements are permitted."); } if (/\b(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|GRANT|REVOKE)\b/i.test(rawQuery)) { throw new Error("Security Violation: Destructive operations are strictly rejected."); } const sanitizedQuery = `WITH user_query AS (${rawQuery.replace(/;+$/, "")}) SELECT * FROM user_query LIMIT ${limit}`; const res = await client.query(sanitizedQuery); return { content: [{ type: "text", text: JSON.stringify({ rowCount: res.rowCount, rows: res.rows }, null, 2) }] }; } throw new Error(`Tool not found: ${name}`); } catch (err: any) { return { isError: true, content: [{ type: "text", text: `Database Error: ${err.message}` }] }; } finally { client.release(); } }); async function run() { const transport = new StdioServerTransport(); await server.connect(transport); console.error("Postgres MCP Server operational on stdio"); } run().catch((error) => { console.error("Fatal server error:", error); process.exit(1); }); { "tools": [ { "name": "list_tables", "description": "List all accessible tables in the database schema", "inputSchema": { "type": "object", "properties": { "schema_name": { "type": "string", "default": "public" } } } }, { "name": "describe_table", "description": "Inspect column names, data types, and constraints for a specific table", "inputSchema": { "type": "object", "properties": { "table_name": { "type": "string" } }, "required": ["table_name"] } }, { "name": "execute_read_query", "description": "Run a parameterized read-only SQL SELECT query with safety row limits", "inputSchema": { "type": "object", "properties": { "query": { "type": "string" }, "limit": { "type": "integer", "default": 50 } }, "required": ["query"] } } ] } { "mcpServers": { "supabase-postgres": { "command": "node", "args": ["/Users/developer/mcp-servers/postgres-mcp/dist/index.js"], "env": { "DATABASE_URL": "postgresql://postgres.user:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres" } } } } { "mcpServers": { "supabase-postgres": { "command": "node", "args": ["/Users/developer/mcp-servers/postgres-mcp/dist/index.js"], "env": { "DATABASE_URL": "postgresql://postgres.user:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres" } } } } echo '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"list_tables","arguments":{"schema_name":"public"}}}' | node dist/index.js { "jsonrpc": "2.0", "id": 1, "result": { "content": [ { "type": "text", "text": "[\n {\n \"table_name\": \"users\",\n \"table_type\": \"BASE TABLE\"\n }\n]" } ] } } # PostgreSQL & Supabase MCP Server Production-ready Model Context Protocol (MCP) server providing read-only PostgreSQL exploration. ## Quickstart 1. Clone repository and install dependencies: ```bash npm install && npm run build cp .env.example .env