Multi-Tenant PostgreSQL MCP Server

A comprehensive, read-only Model Context Protocol (MCP) server for PostgreSQL databases with advanced multi-tenant support, schema introspection, and query capabilities.

Features

🔒 Read-only by design - All queries run in read-only transactions for safety
🏢 Multi-tenant support - Access tables and functions across different schemas
🔍 Advanced schema introspection - Detailed table structures, constraints, indexes, and DDL
🔧 Function definitions - Complete function metadata and source code access
📊 Flexible querying - Execute SQL queries with optional schema context
🌐 Network-ready - Connect to local or remote PostgreSQL instances

Installation

Via npm (recommended)

npm install -g @ahmetkca/mcp-server-postgres

Via npx (no installation required)

Run directly (no install)

npx @ahmetkca/mcp-server-postgres "postgres://user:password@host:port/database"

From source

git clone https://github.com/ahmetkca/mcp-server-postgres.git
cd mcp-server-postgres
npm install
npm run build

Usage

Command Line

With global installation:

mcp-server-postgres "postgres://user:password@host:port/database"

With npx (no installation required)

Direct execution (npx)

npx @ahmetkca/mcp-server-postgres "postgres://user:password@host:port/database"

With Claude Desktop

Add to your Claude Desktop MCP configuration (~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

Option 1: Using npx (recommended - no installation required):

{
  "mcpServers": {
    "postgres-multitenant": {
      "command": "npx",
      "args": ["-y", "@ahmetkca/mcp-server-postgres", "postgres://user:password@localhost:5432/mydb"]
    }
  }
}

Option 2: Using global installation

Global install

{
  "mcpServers": {
    "postgres-multitenant": {
      "command": "mcp-server-postgres",
      "args": ["postgres://user:password@localhost:5432/mydb"]
    }
  }
}

With other MCP clients

The server uses stdio transport, so it can be used with any MCP client that supports subprocess communication:

# Direct execution
npx @ahmetkca/mcp-server-postgres "postgres://connection-string"

# Or with global install
mcp-server-postgres "postgres://connection-string"

Connection String Examples

Local PostgreSQL

mcp-server-postgres "postgres://user:password@localhost:5432/mydb"

Remote PostgreSQL

mcp-server-postgres "postgres://user:password@db.example.com:5432/mydb"

AWS RDS

mcp-server-postgres "postgres://user:password@mydb.abc123.us-east-1.rds.amazonaws.com:5432/mydb"

Google Cloud SQL

mcp-server-postgres "postgres://user:password@1.2.3.4:5432/mydb"

With SSL (recommended for production)

mcp-server-postgres "postgres://user:password@host:5432/mydb?sslmode=require"

Available Resources

Schema Overview

URI Pattern: pg-schema://{schemaName}/overview
Description: High-level overview of a schema including counts (tables, views, functions) and quick links (URIs) to table structures and function definitions for drill-down
Example: pg-schema://public/overview
Completions: Supports typeahead for schemaName

Table Structures

URI Pattern: pg-table://{schemaName}/{tableName}/structure
Description: Comprehensive table metadata including columns, constraints, indexes, and DDL
Example: pg-table://public/users/structure

Function Definitions

URI Pattern: pg-func://{schemaName}/{functionName}/(identityArgs)/definition
Description: Complete function metadata, parameters, return types, and source code
Example: pg-func://public/calculate_total/()/definition
Overloads: identityArgs is the canonical PostgreSQL identity argument list (e.g. (integer,integer)). Example overload: pg-func://public/add/(integer,integer)/definition

Available Tools

1. Multi-Tenant Database Query Tool

Execute read-only SQL queries to retrieve, analyze, and explore PostgreSQL data across multiple tenant schemas. Supports complex SQL including JOINs, CTEs, window functions, and aggregations. All queries run in read-only transactions for safety.

Parameters:

sql (string, required): The SQL query to execute. Can be any valid PostgreSQL SELECT statement including complex queries with JOINs, CTEs, window functions, aggregations, etc. Will be executed in a read-only transaction for safety.
schema (string, optional): Optional schema name (tenant) to set as search_path before executing the query. When specified, unqualified table names will resolve to tables in this schema. Use this for tenant-specific queries.
explain (boolean, optional): Set to true to return the query execution plan instead of query results. Useful for performance analysis and optimization. Returns PostgreSQL EXPLAIN output in JSON format.

Examples:

-- Simple query with default schema
SELECT * FROM users LIMIT 10;

-- Complex query with JOINs and aggregations
SELECT 
  u.name, 
  COUNT(o.id) as order_count,
  SUM(o.total) as total_spent,
  AVG(o.total) as avg_order_value
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

-- Query with CTE and window functions
WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', created_at) as month,
    SUM(total) as monthly_total,
    LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)) as prev_month
  FROM orders 
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT 
  month,
  monthly_total,
  ROUND(((monthly_total - prev_month) / prev_month * 100)::numeric, 2) as growth_rate
FROM monthly_sales
WHERE prev_month IS NOT NULL;

-- Tenant-specific query (set schema parameter to "tenant_a")
SELECT * FROM orders WHERE status = 'pending';

-- Performance analysis (set explain parameter to true)
SELECT u.*, o.total FROM users u JOIN orders o ON u.id = o.user_id;

2. List Database Schemas

Discover all available database schemas (tenants) with statistics including table and function counts. Use this to explore the multi-tenant structure, identify available tenants, or get an overview of database organization.

Parameters:

include_system (boolean, optional): Set to true to include PostgreSQL system schemas (information_schema, pg_catalog, pg_toast) in the results. Default false shows only user/tenant schemas. Use true for administrative or debugging purposes.

Use Cases:

Explore multi-tenant database structure
Identify available tenant schemas
Get overview of database organization
Administrative schema analysis

3. Describe Schema

Get comprehensive information about a specific database schema (tenant) including detailed statistics, all tables, views, functions, and custom types. Use this to understand a tenant's database structure, analyze schema composition, or prepare for schema-specific operations.

Parameters:

schema_name (string, required): Name of the database schema (tenant) to analyze. Must be an exact schema name from the database. Use list-schemas tool first to discover available schema names.

Returns:

Schema statistics (table count, view count, function count, type count)
Detailed table information with column counts
Function definitions and metadata
Organized metadata perfect for schema analysis and documentation

Multi-Tenant Architecture

This server is designed for multi-tenant PostgreSQL setups where:

Different tenants have separate schemas (e.g., tenant_a, tenant_b, public)
Each schema contains tenant-specific tables and functions
You need to query across different tenant contexts

Example Multi-Tenant Usage

-- List all schemas
-- Use "list-schemas" tool

-- Describe a specific tenant schema
-- Use "describe-schema" tool with schema_name: "tenant_a"

-- Query tenant-specific data
-- Use "query" tool with schema: "tenant_a"
SELECT * FROM orders WHERE status = 'pending';

-- Get table structure for a tenant
-- Access resource: pg-table://tenant_a/orders/structure

Security Features

Read-only transactions: All queries are wrapped in BEGIN TRANSACTION READ ONLY
No data modification: INSERT, UPDATE, DELETE, and DDL operations are blocked
Schema isolation: Optional schema context prevents cross-tenant data access
Connection pooling: Efficient resource management with automatic cleanup

Development

Prerequisites

Node.js 18+
PostgreSQL database (local or remote)
TypeScript knowledge (for contributions)

Setup

git clone https://github.com/ahmetkca/mcp-server-postgres.git
cd mcp-server-postgres
npm install

Development Commands

npm run dev          # Watch mode for development
npm run build        # Build for production
npm run start        # Start the built server

Testing

Start the server with a test database

npm run build
npm start "postgres://user:password@localhost:5432/testdb"

In another terminal, test with MCP Inspector

npx @modelcontextprotocol/inspector

Troubleshooting

Connection Issues

Verify your PostgreSQL connection string
Check network connectivity to the database
Ensure the database user has SELECT permissions
For remote connections, verify firewall settings

Permission Issues

The database user needs SELECT permissions on:
- All tables you want to query
- information_schema views
- pg_catalog system tables (for function definitions)

Schema Access

Ensure the database user has USAGE permission on schemas
For multi-tenant setups, grant access to relevant tenant schemas

Contributing

Fork the repository
Create a feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add amazing feature')
Push to the branch (git push origin feature/amazing-feature)
Open a Pull Request

Changelog

v1.1.0 - 2025-08-07

Adopt custom resource URI schemes:
- Tables: pg-table://{schemaName}/{tableName}/structure
- Functions: pg-func://{schemaName}/{functionName}/(identityArgs)/definition
Per-overload function resources using canonical identityArgs to disambiguate overloads
Deterministic, deduplicated resource listings using DISTINCT ON and stable ordering
Exact overload resolution via regprocedure/OID for function metadata
README updated to reflect new URI schemes and best practices
TypeScript build fixes: NodeNext ESM config, top-level await, and pg Pool named import

v1.0.0

Initial release
Multi-tenant PostgreSQL support
Advanced schema introspection
Function definition access
Read-only query execution
Comprehensive documentation

Examples: listing and reading

Below are minimal examples of how resources appear in clients and how to read them. Actual UX varies by MCP client, but the URIs and fields are the same.

1) Listing resources (conceptual)

Clients request a list from the server and render entries like:

name: public
uri:  pg-schema://public/overview
—
name: public.users
uri:  pg-table://public/users/structure
—
name: tenant_a.app_event
uri:  pg-table://tenant_a/app_event/structure
—
name: public.add(integer,integer)
uri:  pg-func://public/add/(integer,integer)/definition
—
name: public.add(text,text)
uri:  pg-func://public/add/(text,text)/definition

2) Reading a schema overview

Select: pg-schema://public/overview
The client sends a read request and receives JSON with statistics and quick links (URIs) to tables and functions.

JSON-RPC (abridged) example over stdio:

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "resources/read",
  "params": { "uri": "pg-schema://public/overview" }
}

Response (shape):

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "contents": [
      {
        "uri": "pg-schema://public/overview",
        "mimeType": "application/json",
        "text": "{ ... statistics, tables.first_100 [{name, uri}], functions.first_100 [{name, uri}] ... }"
      }
    ]
  }
}

2) Reading a table structure

Select: pg-table://tenant_a/app_event/structure
Client sends a read request and receives JSON describing columns, constraints, indexes, relationships, and DDL.

JSON-RPC (abridged) example over stdio:

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "resources/read",
  "params": { "uri": "pg-table://tenant_a/app_event/structure" }
}

Response (shape):

{
  "jsonrpc": "2.0",
  "id": 1,
  "result": {
    "contents": [
      {
        "uri": "pg-table://tenant_a/app_event/structure",
        "mimeType": "application/json",
        "text": "{ ... columns, constraints, indexes, ddl ... }"
      }
    ]
  }
}

3) Reading a specific function overload

Select: pg-func://public/add/(integer,integer)/definition
The server resolves the exact overload by regprocedure and returns the definition.

JSON-RPC (abridged) example:

{
  "jsonrpc": "2.0",
  "id": 2,
  "method": "resources/read",
  "params": { "uri": "pg-func://public/add/(integer,integer)/definition" }
}

Response (shape):

{
  "jsonrpc": "2.0",
  "id": 2,
  "result": {
    "contents": [
      {
        "uri": "pg-func://public/add/(integer,integer)/definition",
        "mimeType": "application/json",
        "text": "{ ... parameters, return_type, language, volatility, security, source, ddl ... }"
      }
    ]
  }
}

Tips

Choose the exact overload by picking the URI that includes the identity args in parentheses.
All identifiers in URIs are URL-encoded; clients decode them before invoking the handler.
Lists are deduplicated and ordered for a clean browsing experience.

License

This project is licensed under the MIT License - see the LICENSE file for details.

This server cannot be installed

security - not tested

license - permissive license

quality - not tested

How are these scores calculated?

hybrid server

The server is able to function both locally and remotely, depending on the configuration or use case.

Enables read-only access to PostgreSQL databases with multi-tenant support, allowing users to query data, explore schemas, inspect table structures, and view function definitions across different tenant schemas safely.

Related MCP Servers

PostgreSQL MCP Server
vinsidious
-
security
F
license
-
quality
Provides read-only access to PostgreSQL databases, enabling users to inspect database schemas and execute read-only queries through a Model Context Protocol server.
Last updated -
3
JavaScript
PostgreSQL Multi-Schema MCP Server
HarjjotSinghh
-
security
A
license
-
quality
A Model Context Protocol server that provides read-only access to PostgreSQL databases with enhanced multi-schema support, allowing LLMs to inspect database schemas across multiple namespaces and execute read-only queries while maintaining schema isolation.
Last updated -
21
3
JavaScript
MIT License
PostgreSQL MCP Server
DanielRSnell
-
security
F
license
-
quality
Provides read-only access to PostgreSQL databases, enabling LLMs to inspect database schemas and execute read-only SQL queries within a secure transaction context.
Last updated -
21,412
1
JavaScript
PostgreSQL MCP Server
hsinyuyen
-
security
F
license
-
quality
Provides read-only access to PostgreSQL databases, enabling LLMs to inspect database schemas and execute read-only SQL queries.
Last updated -
21,412
JavaScript

View all related MCP servers