# Directory Structure
```
├── .gitignore
├── bun.lock
├── Dockerfile
├── index.ts
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── tsconfig.base.json
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
1 | node_modules
2 | dist
3 |
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
1 | # PostgreSQL Multi-Schema MCP Server
2 |
3 | A Model Context Protocol server that provides read-only access to PostgreSQL databases with enhanced multi-schema support. This server enables LLMs to inspect database schemas across multiple namespaces and execute read-only queries while maintaining schema isolation.
4 |
5 | ## Key Features
6 |
7 | - **Multi-Schema Support**: Explicitly specify which schemas to expose through command-line configuration
8 | - **Schema Isolation**: Strict access control to only authorized schemas listed during server startup
9 | - **Cross-Schema Discovery**: Unified view of tables across multiple schemas while maintaining schema boundaries
10 | - **Metadata Security**: Filters system catalogs to only expose user-defined tables in specified schemas
11 |
12 | ## Components
13 |
14 | ### Tools
15 |
16 | - **query**
17 | - Execute read-only SQL queries against the connected database
18 | - Input: `sql` (string): The SQL query to execute
19 | - All queries are executed within a READ ONLY transaction
20 | - Schema context maintained through search_path restriction
21 |
22 | ### Resources
23 |
24 | The server provides schema information for each table across authorized schemas:
25 |
26 | - **Table Schemas** (`postgres://<host>/<db_schema>/<table>/schema`)
27 | - JSON schema information for each table
28 | - Includes column names, data types, and type modifiers
29 | - Automatically discovered from database metadata
30 | - Multi-schema support with explicit schema allow-list
31 |
32 | ## Usage
33 |
34 | The server requires a database URL and accepts a comma-separated list of schemas to expose:
35 |
36 | ```
37 | npx -y mcp-server-postgres-multi-schema <database-url> [schemas]
38 | ```
39 |
40 | - **database-url**: PostgreSQL connection string (e.g., `postgresql://localhost/mydb`)
41 | - **schemas**: Comma-separated list of schemas to expose (defaults to 'public' if not specified)
42 |
43 | ### Examples
44 |
45 | ```bash
46 | # Connect with default public schema
47 | npx -y mcp-server-postgres-multi-schema postgresql://localhost/mydb
48 |
49 | # Connect with multiple schemas
50 | npx -y mcp-server-postgres-multi-schema postgresql://localhost/mydb public,analytics,staging
51 | ```
52 |
53 | ## Usage with Claude Desktop
54 |
55 | Configure the "mcpServers" section in your `claude_desktop_config.json`:
56 |
57 | ### NPX
58 |
59 | ```json
60 | {
61 | "mcpServers": {
62 | "postgres": {
63 | "command": "npx",
64 | "args": [
65 | "-y",
66 | "mcp-server-postgres-multi-schema",
67 | "postgresql://localhost/mydb",
68 | "public,audit"
69 | ]
70 | }
71 | }
72 | }
73 | ```
74 |
75 | ## License
76 |
77 | This multi-schema MCP server is licensed under the MIT License. You may use, modify, and distribute the software according to the terms in the LICENSE file.
78 |
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "extends": "./tsconfig.base.json",
3 | "compilerOptions": {
4 | "outDir": "./dist",
5 | "rootDir": "."
6 | },
7 | "include": [
8 | "./**/*.ts"
9 | ]
10 | }
```
--------------------------------------------------------------------------------
/tsconfig.base.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "compilerOptions": {
3 | "target": "ES2022",
4 | "module": "Node16",
5 | "moduleResolution": "Node16",
6 | "strict": true,
7 | "esModuleInterop": true,
8 | "skipLibCheck": true,
9 | "forceConsistentCasingInFileNames": true,
10 | "resolveJsonModule": true
11 | },
12 | "include": [
13 | "src/**/*"
14 | ],
15 | "exclude": [
16 | "node_modules"
17 | ]
18 | }
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
1 | FROM node:22.12-alpine AS builder
2 |
3 | COPY src/postgres /app
4 | COPY tsconfig.json /tsconfig.json
5 |
6 | WORKDIR /app
7 |
8 | RUN --mount=type=cache,target=/root/.npm npm install
9 |
10 | RUN --mount=type=cache,target=/root/.npm-production npm ci --ignore-scripts --omit-dev
11 |
12 | FROM node:22-alpine AS release
13 |
14 | COPY --from=builder /app/dist /app/dist
15 | COPY --from=builder /app/package.json /app/package.json
16 | COPY --from=builder /app/package-lock.json /app/package-lock.json
17 |
18 | ENV NODE_ENV=production
19 |
20 | WORKDIR /app
21 |
22 | RUN npm ci --ignore-scripts --omit-dev
23 |
24 | ENTRYPOINT ["node", "dist/index.js"]
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
1 | {
2 | "name": "mcp-server-postgres-multi-schema",
3 | "version": "0.1.3",
4 | "description": "MCP server for interacting with PostgreSQL databases, with support for multiple schemas",
5 | "license": "MIT",
6 | "author": "Harjot Singh Rana",
7 | "homepage": "https://github.com/HarjjotSinghh/mcp-server-postgres-multi-schema",
8 | "bugs": "https://github.com/HarjjotSinghh/mcp-server-postgres-multi-schema/issues",
9 | "repository": "https://github.com/HarjjotSinghh/mcp-server-postgres-multi-schema",
10 | "publishConfig": {
11 | "registry": "https://npm.pkg.github.com"
12 | },
13 | "type": "module",
14 | "bin": {
15 | "mcp-server-postgres-multi-schema": "dist/index.js"
16 | },
17 | "files": [
18 | "dist"
19 | ],
20 | "scripts": {
21 | "build": "tsc && shx chmod +x dist/*.js",
22 | "prepare": "npm run build",
23 | "watch": "tsc --watch"
24 | },
25 | "dependencies": {
26 | "@modelcontextprotocol/sdk": "1.0.1",
27 | "pg": "^8.13.0"
28 | },
29 | "devDependencies": {
30 | "@types/pg": "^8.11.10",
31 | "shx": "^0.3.4",
32 | "typescript": "^5.6.2"
33 | }
34 | }
```
--------------------------------------------------------------------------------
/index.ts:
--------------------------------------------------------------------------------
```typescript
1 | #!/usr/bin/env node
2 |
3 | import { Server } from "@modelcontextprotocol/sdk/server/index.js";
4 | import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
5 | import {
6 | CallToolRequestSchema,
7 | ListResourcesRequestSchema,
8 | ListToolsRequestSchema,
9 | ReadResourceRequestSchema,
10 | } from "@modelcontextprotocol/sdk/types.js";
11 | import pg from "pg";
12 |
13 | const server = new Server(
14 | {
15 | name: "mcp-server-postgres-multi-schema",
16 | version: "0.1.0",
17 | },
18 | {
19 | capabilities: {
20 | resources: {},
21 | tools: {},
22 | },
23 | },
24 | );
25 |
26 | const args = process.argv.slice(2);
27 | if (args.length === 0) {
28 | console.error("Please provide a database URL as a command-line argument");
29 | console.error("Usage: npx -y mcp-server-postgres-multi-schema <database-url> [schemas]");
30 | console.error("Example: npx -y mcp-server-postgres-multi-schema postgresql://localhost/mydb \"public,custom_schema\"");
31 | process.exit(1);
32 | }
33 |
34 | const databaseUrl = args[0];
35 | // Parse schemas from the second argument or default to 'public'
36 | const schemas = args[1]
37 | ? args[1].split(',').map(schema => schema.trim())
38 | : ['public'];
39 |
40 | console.log(`Connecting to database: ${databaseUrl}`);
41 | console.log(`Using schemas: ${schemas.join(', ')}`);
42 |
43 | const resourceBaseUrl = new URL(databaseUrl);
44 | resourceBaseUrl.protocol = "postgres:";
45 | resourceBaseUrl.password = "";
46 |
47 | const pool = new pg.Pool({
48 | connectionString: databaseUrl,
49 | });
50 |
51 | const SCHEMA_PATH = "schema";
52 |
53 | server.setRequestHandler(ListResourcesRequestSchema, async () => {
54 | const client = await pool.connect();
55 | try {
56 | // Build a parameterized query for multiple schemas
57 | const placeholders = schemas.map((_, i) => `$${i + 1}`).join(', ');
58 | const query = `
59 | SELECT table_schema, table_name
60 | FROM information_schema.tables
61 | WHERE table_schema IN (${placeholders})
62 | ORDER BY table_schema, table_name
63 | `;
64 |
65 | const result = await client.query(query, schemas);
66 |
67 | return {
68 | resources: result.rows.map((row) => ({
69 | uri: new URL(`${row.table_schema}/${row.table_name}/${SCHEMA_PATH}`, resourceBaseUrl).href,
70 | mimeType: "application/json",
71 | name: `"${row.table_name}" table in "${row.table_schema}" schema`,
72 | })),
73 | };
74 | } finally {
75 | client.release();
76 | }
77 | });
78 |
79 | server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
80 | const resourceUrl = new URL(request.params.uri);
81 |
82 | const pathComponents = resourceUrl.pathname.split("/");
83 | const schemaPath = pathComponents.pop();
84 | const tableName = pathComponents.pop();
85 | const dbSchema = pathComponents.pop();
86 |
87 | if (schemaPath !== SCHEMA_PATH) {
88 | throw new Error("Invalid resource URI");
89 | }
90 |
91 | if (!dbSchema) {
92 | throw new Error("Schema is required");
93 | }
94 |
95 | if (!schemas.includes(dbSchema)) {
96 | throw new Error(`Schema "${dbSchema}" is not in the allowed schemas list`);
97 | }
98 |
99 | const client = await pool.connect();
100 | try {
101 | const result = await client.query(
102 | "SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2",
103 | [dbSchema, tableName],
104 | );
105 |
106 | return {
107 | contents: [
108 | {
109 | uri: request.params.uri,
110 | mimeType: "application/json",
111 | text: JSON.stringify(result.rows, null, 2),
112 | },
113 | ],
114 | };
115 | } finally {
116 | client.release();
117 | }
118 | });
119 |
120 | server.setRequestHandler(ListToolsRequestSchema, async () => {
121 | return {
122 | tools: [
123 | {
124 | name: "query",
125 | description: "Run a read-only SQL query",
126 | inputSchema: {
127 | type: "object",
128 | properties: {
129 | sql: { type: "string" },
130 | },
131 | },
132 | },
133 | ],
134 | };
135 | });
136 |
137 | server.setRequestHandler(CallToolRequestSchema, async (request) => {
138 | if (request.params.name === "query") {
139 | // Ensure sql is provided
140 | if (!request.params.arguments || typeof request.params.arguments.sql !== 'string') {
141 | throw new Error("SQL query is required and must be a string");
142 | }
143 |
144 | const sql = request.params.arguments.sql;
145 |
146 | const client = await pool.connect();
147 | try {
148 | await client.query("BEGIN TRANSACTION READ ONLY");
149 | const result = await client.query(sql);
150 | return {
151 | content: [{ type: "text", text: JSON.stringify(result.rows, null, 2) }],
152 | isError: false,
153 | };
154 | } catch (error) {
155 | throw error;
156 | } finally {
157 | client
158 | .query("ROLLBACK")
159 | .catch((error) =>
160 | console.warn("Could not roll back transaction:", error),
161 | );
162 |
163 | client.release();
164 | }
165 | }
166 | throw new Error(`Unknown tool: ${request.params.name}`);
167 | });
168 |
169 | async function runServer() {
170 | const transport = new StdioServerTransport();
171 | await server.connect(transport);
172 | }
173 |
174 | runServer().catch(console.error);
175 |
```