# Directory Structure
```
├── .gitignore
├── bun.lock
├── Dockerfile
├── index.ts
├── LICENSE
├── package-lock.json
├── package.json
├── README.md
├── tsconfig.base.json
└── tsconfig.json
```
# Files
--------------------------------------------------------------------------------
/.gitignore:
--------------------------------------------------------------------------------
```
node_modules
dist
```
--------------------------------------------------------------------------------
/README.md:
--------------------------------------------------------------------------------
```markdown
# PostgreSQL Multi-Schema MCP Server
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.
## Key Features
- **Multi-Schema Support**: Explicitly specify which schemas to expose through command-line configuration
- **Schema Isolation**: Strict access control to only authorized schemas listed during server startup
- **Cross-Schema Discovery**: Unified view of tables across multiple schemas while maintaining schema boundaries
- **Metadata Security**: Filters system catalogs to only expose user-defined tables in specified schemas
## Components
### Tools
- **query**
- Execute read-only SQL queries against the connected database
- Input: `sql` (string): The SQL query to execute
- All queries are executed within a READ ONLY transaction
- Schema context maintained through search_path restriction
### Resources
The server provides schema information for each table across authorized schemas:
- **Table Schemas** (`postgres://<host>/<db_schema>/<table>/schema`)
- JSON schema information for each table
- Includes column names, data types, and type modifiers
- Automatically discovered from database metadata
- Multi-schema support with explicit schema allow-list
## Usage
The server requires a database URL and accepts a comma-separated list of schemas to expose:
```
npx -y mcp-server-postgres-multi-schema <database-url> [schemas]
```
- **database-url**: PostgreSQL connection string (e.g., `postgresql://localhost/mydb`)
- **schemas**: Comma-separated list of schemas to expose (defaults to 'public' if not specified)
### Examples
```bash
# Connect with default public schema
npx -y mcp-server-postgres-multi-schema postgresql://localhost/mydb
# Connect with multiple schemas
npx -y mcp-server-postgres-multi-schema postgresql://localhost/mydb public,analytics,staging
```
## Usage with Claude Desktop
Configure the "mcpServers" section in your `claude_desktop_config.json`:
### NPX
```json
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": [
"-y",
"mcp-server-postgres-multi-schema",
"postgresql://localhost/mydb",
"public,audit"
]
}
}
}
```
## License
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.
```
--------------------------------------------------------------------------------
/tsconfig.json:
--------------------------------------------------------------------------------
```json
{
"extends": "./tsconfig.base.json",
"compilerOptions": {
"outDir": "./dist",
"rootDir": "."
},
"include": [
"./**/*.ts"
]
}
```
--------------------------------------------------------------------------------
/tsconfig.base.json:
--------------------------------------------------------------------------------
```json
{
"compilerOptions": {
"target": "ES2022",
"module": "Node16",
"moduleResolution": "Node16",
"strict": true,
"esModuleInterop": true,
"skipLibCheck": true,
"forceConsistentCasingInFileNames": true,
"resolveJsonModule": true
},
"include": [
"src/**/*"
],
"exclude": [
"node_modules"
]
}
```
--------------------------------------------------------------------------------
/Dockerfile:
--------------------------------------------------------------------------------
```dockerfile
FROM node:22.12-alpine AS builder
COPY src/postgres /app
COPY tsconfig.json /tsconfig.json
WORKDIR /app
RUN --mount=type=cache,target=/root/.npm npm install
RUN --mount=type=cache,target=/root/.npm-production npm ci --ignore-scripts --omit-dev
FROM node:22-alpine AS release
COPY --from=builder /app/dist /app/dist
COPY --from=builder /app/package.json /app/package.json
COPY --from=builder /app/package-lock.json /app/package-lock.json
ENV NODE_ENV=production
WORKDIR /app
RUN npm ci --ignore-scripts --omit-dev
ENTRYPOINT ["node", "dist/index.js"]
```
--------------------------------------------------------------------------------
/package.json:
--------------------------------------------------------------------------------
```json
{
"name": "mcp-server-postgres-multi-schema",
"version": "0.1.3",
"description": "MCP server for interacting with PostgreSQL databases, with support for multiple schemas",
"license": "MIT",
"author": "Harjot Singh Rana",
"homepage": "https://github.com/HarjjotSinghh/mcp-server-postgres-multi-schema",
"bugs": "https://github.com/HarjjotSinghh/mcp-server-postgres-multi-schema/issues",
"repository": "https://github.com/HarjjotSinghh/mcp-server-postgres-multi-schema",
"publishConfig": {
"registry": "https://npm.pkg.github.com"
},
"type": "module",
"bin": {
"mcp-server-postgres-multi-schema": "dist/index.js"
},
"files": [
"dist"
],
"scripts": {
"build": "tsc && shx chmod +x dist/*.js",
"prepare": "npm run build",
"watch": "tsc --watch"
},
"dependencies": {
"@modelcontextprotocol/sdk": "1.0.1",
"pg": "^8.13.0"
},
"devDependencies": {
"@types/pg": "^8.11.10",
"shx": "^0.3.4",
"typescript": "^5.6.2"
}
}
```
--------------------------------------------------------------------------------
/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,
ListResourcesRequestSchema,
ListToolsRequestSchema,
ReadResourceRequestSchema,
} from "@modelcontextprotocol/sdk/types.js";
import pg from "pg";
const server = new Server(
{
name: "mcp-server-postgres-multi-schema",
version: "0.1.0",
},
{
capabilities: {
resources: {},
tools: {},
},
},
);
const args = process.argv.slice(2);
if (args.length === 0) {
console.error("Please provide a database URL as a command-line argument");
console.error("Usage: npx -y mcp-server-postgres-multi-schema <database-url> [schemas]");
console.error("Example: npx -y mcp-server-postgres-multi-schema postgresql://localhost/mydb \"public,custom_schema\"");
process.exit(1);
}
const databaseUrl = args[0];
// Parse schemas from the second argument or default to 'public'
const schemas = args[1]
? args[1].split(',').map(schema => schema.trim())
: ['public'];
console.log(`Connecting to database: ${databaseUrl}`);
console.log(`Using schemas: ${schemas.join(', ')}`);
const resourceBaseUrl = new URL(databaseUrl);
resourceBaseUrl.protocol = "postgres:";
resourceBaseUrl.password = "";
const pool = new pg.Pool({
connectionString: databaseUrl,
});
const SCHEMA_PATH = "schema";
server.setRequestHandler(ListResourcesRequestSchema, async () => {
const client = await pool.connect();
try {
// Build a parameterized query for multiple schemas
const placeholders = schemas.map((_, i) => `$${i + 1}`).join(', ');
const query = `
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema IN (${placeholders})
ORDER BY table_schema, table_name
`;
const result = await client.query(query, schemas);
return {
resources: result.rows.map((row) => ({
uri: new URL(`${row.table_schema}/${row.table_name}/${SCHEMA_PATH}`, resourceBaseUrl).href,
mimeType: "application/json",
name: `"${row.table_name}" table in "${row.table_schema}" schema`,
})),
};
} finally {
client.release();
}
});
server.setRequestHandler(ReadResourceRequestSchema, async (request) => {
const resourceUrl = new URL(request.params.uri);
const pathComponents = resourceUrl.pathname.split("/");
const schemaPath = pathComponents.pop();
const tableName = pathComponents.pop();
const dbSchema = pathComponents.pop();
if (schemaPath !== SCHEMA_PATH) {
throw new Error("Invalid resource URI");
}
if (!dbSchema) {
throw new Error("Schema is required");
}
if (!schemas.includes(dbSchema)) {
throw new Error(`Schema "${dbSchema}" is not in the allowed schemas list`);
}
const client = await pool.connect();
try {
const result = await client.query(
"SELECT column_name, data_type FROM information_schema.columns WHERE table_schema = $1 AND table_name = $2",
[dbSchema, tableName],
);
return {
contents: [
{
uri: request.params.uri,
mimeType: "application/json",
text: JSON.stringify(result.rows, null, 2),
},
],
};
} finally {
client.release();
}
});
server.setRequestHandler(ListToolsRequestSchema, async () => {
return {
tools: [
{
name: "query",
description: "Run a read-only SQL query",
inputSchema: {
type: "object",
properties: {
sql: { type: "string" },
},
},
},
],
};
});
server.setRequestHandler(CallToolRequestSchema, async (request) => {
if (request.params.name === "query") {
// Ensure sql is provided
if (!request.params.arguments || typeof request.params.arguments.sql !== 'string') {
throw new Error("SQL query is required and must be a string");
}
const sql = request.params.arguments.sql;
const client = await pool.connect();
try {
await client.query("BEGIN TRANSACTION READ ONLY");
const result = await client.query(sql);
return {
content: [{ type: "text", text: JSON.stringify(result.rows, null, 2) }],
isError: false,
};
} catch (error) {
throw error;
} finally {
client
.query("ROLLBACK")
.catch((error) =>
console.warn("Could not roll back transaction:", error),
);
client.release();
}
}
throw new Error(`Unknown tool: ${request.params.name}`);
});
async function runServer() {
const transport = new StdioServerTransport();
await server.connect(transport);
}
runServer().catch(console.error);
```