# postgres-mcp [Health: Active] **Category:** πŸ—„οΈ Databases **Repository:** https://github.com/edelciomolina/postgres-mcp **GitHub Stars:** 5 **Views:** 0 **Installs:** 0 **Upvotes:** 0 **Directory Page:** https://allmcps.com/mcp/postgres-mcp ## Description PostgreSQL MCP wrapper with .env credential mapping, tool selection, and safe read-only defaults. ## Claude Desktop Quick Installation Install path detected from listing signals. Uses `npx` (confidence: high): ```json "mcpServers": { "postgres-mcp": { "command": "npx", "args": ["-y","@edelciomolina/postgres-mcp"] } } ``` ## Documentation & README > **English** | 🌐 [PortuguΓͺs](https://github.com/edelciomolina/postgres-mcp/blob/HEAD/README_PT.md)
Postgres MCP Icon

Postgres MCP

πŸ”Œ Native MCP server for PostgreSQL - reads credentials from .env at runtime with flexible key mapping, configurable tool selection, and read-only mode by default.

npm version license CI
--- ## ✨ What it does Most LLMs interact with databases by guessing - assuming table names, inventing column names, and writing queries that may fail or expose sensitive data. Postgres MCP solves this by giving the LLM a **structured, safe interface** to actually understand the database before touching it. Built with [`@modelcontextprotocol/sdk`](https://www.npmjs.com/package/@modelcontextprotocol/sdk) and [`pg`](https://www.npmjs.com/package/pg), it provides: - 🧠 **Semantic knowledge graph** - the LLM gets a complete map of schemas, tables, columns, foreign keys, inferred relations, risk levels, and business domains - built from the real schema, not invented - πŸ›‘οΈ **Read-only by default** - no writes, no DDL, no arbitrary SQL unless you explicitly opt in; `pg_classify_query_risk` lets the LLM check a query's safety before running it - πŸ” **Runtime credential resolution** - credentials are read from `.env` at startup; nothing sensitive lives in `mcp.json` - 🎯 **Explicit tool selection** - every tool is opt-in via `tool=` args, so the LLM only sees what you choose to expose --- ## πŸ“‹ Requirements - βš™οΈ Node.js >= 18 - πŸ“„ A `.env` file with database credentials (anywhere in the project tree - see [.env Discovery](#-env-file-discovery)) --- ## πŸš€ Installation There are two ways to use this package. Choose the one that best fits your workflow. ### Option 1 - No installation (via `npx`, recommended for quick start) No installation needed. `npx` downloads and runs the package on demand. Add `-y` as the first argument to skip the confirmation prompt. ```json { "servers": { "Postgres Tools": { "type": "stdio", "command": "npx", "args": [ "-y", "@edelciomolina/postgres-mcp" ], "env": { "MCP_KEY_HOST": "DB_HOST", "MCP_KEY_PORT": "DB_PORT", "MCP_KEY_NAME": "DB_NAME", "MCP_KEY_SSLMODE": "DB_SSLMODE", "MCP_KEY_USER": "DB_USER", "MCP_KEY_PASS": "DB_PASS" } } } } ``` This starts the server with the **default read-only tool set** - no `tool=` arguments needed. To enable write-capable tools, see [Write-capable tools](#write-capable-opt-in-via-tool-argument). > πŸ’‘ **Using Supabase, Neon, Railway or another platform that only provides a connection string?** Use `MCP_KEY_URL` pointing to `DATABASE_URL` (or whatever variable name the platform uses). The server will prioritize the URL and ignore the individual variables. See [Connection via URL](#-connection-via-url-database_url). --- ### Option 2 - Install via VS Code (MCP extension marketplace) VS Code supports discovering and installing MCP servers directly in the editor, without using the terminal. 1. Open the **Command Palette** (Cmd+Shift+P on Mac / Ctrl+Shift+P on Windows/Linux) 2. Run **`MCP: Add Server`** 3. Choose **"Browse MCP Servers"** (or **"From registry"**, depending on your VS Code version) 4. Search for **`postgres-mcp`** or **`edelciomolina`** 5. Select **Postgres MCP** and follow the instructions - VS Code will add the entry to your `mcp.json` automatically > πŸ’‘ You can also open the MCP Servers panel via **Copilot chat icon β†’ Manage MCP Servers** to browse, enable, or disable servers at any time. After installing, edit the generated entry in `.vscode/mcp.json` to add your `tool=` arguments and `env` key mappings as shown in the [Usage](#-usage-in-vs-code-mcpjson) section below. --- ## πŸš€ Usage in VS Code (`mcp.json`) **Read-only (default - no `tool=` arguments needed):** ```json { "servers": { "Postgres Tools": { "type": "stdio", "command": "npx", "args": ["@edelciomolina/postgres-mcp"], "env": { "MCP_KEY_HOST": "DB_HOST", "MCP_KEY_PORT": "DB_PORT", "MCP_KEY_NAME": "DB_NAME", "MCP_KEY_SSLMODE": "DB_SSLMODE", "MCP_KEY_USER": "DB_USER", "MCP_KEY_PASS": "DB_PASS" } } } } ``` **With write tools (explicit opt-in required):** ```json { "servers": { "Postgres Tools": { "type": "stdio", "command": "npx", "args": [ "@edelciomolina/postgres-mcp", "tool=pg_manage_schema", "tool=pg_manage_indexes" ], "env": { "POSTGRES_MCP_ALLOW_WRITE": "true", "MCP_KEY_HOST": "DB_HOST", "MCP_KEY_PORT": "DB_PORT", "MCP_KEY_NAME": "DB_NAME", "MCP_KEY_SSLMODE": "DB_SSLMODE", "MCP_KEY_USER": "DB_USER", "MCP_KEY_PASS": "DB_PASS" } } } } ``` > ⚠️ Write-capable tools require `POSTGRES_MCP_ALLOW_WRITE=true` in `env`. Without it, the server exits at startup. The corresponding `.env` file at the root of your project: ```env DB_HOST=db.your-project.supabase.co DB_PORT=5432 DB_NAME=postgres DB_SSLMODE=require DB_USER=readonly_user DB_PASS=your_password ``` --- ## βš™οΈ How `mcp.json` configuration works ### πŸ—οΈ `env` - credential key mapping The `env` block does **not** contain the actual credentials. It maps each `MCP_KEY_*` to the variable name in your `.env` file. | Key in `env` | Points to `.env` variable | Example value | |-------------------|-----------------------------|--------------------------| | `MCP_KEY_URL` | `DATABASE_URL` | `postgresql://user:pass@host:5432/db?sslmode=require` | | `MCP_KEY_HOST` | `DB_HOST` | `db.example.supabase.co` | | `MCP_KEY_PORT` | `DB_PORT` | `5432` | | `MCP_KEY_NAME` | `DB_NAME` | `postgres` | | `MCP_KEY_SSLMODE` | `DB_SSLMODE` | `require` | | `MCP_KEY_USER` | `DB_USER` | `readonly_user` | | `MCP_KEY_PASS` | `DB_PASS` | `secret` | > **Priority:** when `MCP_KEY_URL` (or `DATABASE_URL`) is present, the server uses the URL directly and **ignores** the individual credential keys. This indirection lets you use **any variable name** in your `.env` - useful when sharing a `.env` across multiple services with different naming conventions. ### πŸ”§ `args` - tool selection via `tool=` prefix Each enabled MCP tool is declared as a separate argument in the format `tool=`: ```json "args": [ "-y", "@edelciomolina/postgres-mcp", "tool=pg_manage_schema", "tool=pg_manage_indexes" ] ``` This makes the tool list **explicit and auditable** directly in `mcp.json` - no hidden configuration files. πŸ” --- ## πŸ”— Connection via URL (`DATABASE_URL`) In addition to individual credentials, you can provide a **full connection string** - the standard format on platforms like Supabase, Neon, and Railway. **`.env`:** ```env DATABASE_URL=postgresql://user:password@host:5432/database?sslmode=require ``` **`mcp.json`:** ```json { "servers": { "Postgres Tools": { "type": "stdio", "command": "npx", "args": ["-y", "@edelciomolina/postgres-mcp"], "env": { "MCP_KEY_URL": "DATABASE_URL" } } } } ``` The variable mapped by `MCP_KEY_URL` has **priority** over the other keys (`MCP_KEY_HOST`, `MCP_KEY_PORT`, etc.). If the URL is present, the other variables are ignored. If the platform uses a different name (e.g. `DB_URL`), just adjust the mapping: ```json "MCP_KEY_URL": "DB_URL" ``` --- ## πŸ›‘οΈ Why read-only is the default If you omit all `tool=` arguments, the server starts with a **curated read-only set** - all tools that can retrieve, analyze, or explain data, but nothing that can modify it. **βœ… Included in defaults (read-only):** ``` pg_execute_query pg_manage_query pg_inspect_schema pg_get_setup_instructions pg_analyze_database pg_monitor_database pg_debug_database pg_inspect_database_graph pg_describe_table_semantics pg_find_related_tables pg_classify_query_risk ``` > πŸ’‘ `pg_execute_query` rejects `INSERT`, `UPDATE`, `DELETE`, DDL, `ANALYZE`, `VACUUM`, `EXPLAIN ANALYZE` and other write/maintenance commands before the database is queried. > πŸ’‘ `pg_inspect_schema` provides read-only schema introspection (`get_info`, `get_enums`). For DDL operations, use `pg_manage_schema` with explicit opt-in. **⚠️ Excluded from defaults - require `tool=` argument AND `POSTGRES_MCP_ALLOW_WRITE=true`:** | Tool | Operations | |------|-----------| | `pg_manage_schema` | CREATE TABLE, ALTER TABLE, CREATE TYPE | | `pg_manage_indexes` | CREATE INDEX, DROP INDEX, REINDEX | | `pg_manage_constraints` | ADD CONSTRAINT, DROP CONSTRAINT | | `pg_manage_functions` | CREATE FUNCTION, DROP FUNCTION | | `pg_manage_triggers` | CREATE TRIGGER, DROP TRIGGER, enable/disable | | `pg_manage_rls` | ENABLE/DISABLE RLS, CREATE/ALTER/DROP POLICY | | `pg_manage_users` | CREATE/DROP/ALTER USER, GRANT, REVOKE | | `pg_execute_mutation` | INSERT / UPDATE / DELETE / UPSERT | | `pg_execute_sql` | Arbitrary SQL with transaction support | --- ## πŸ“ `.env` file discovery The server resolves the `.env` file in this order: 1. **`env-file=` argument** - explicit path relative to `cwd`; takes priority over everything 2. **Upward search** - starting from `cwd`, searches each parent directory until a `.env` is found or the filesystem root is reached If no `.env` is found, the server exits with a clear error message. ### Monorepos and subfolders When VS Code starts the MCP process, `cwd` is typically the workspace root. If your `.env` is in a subfolder (e.g. `functions/.env`), use `env-file=` to point to it explicitly: ```json { "servers": { "Postgres Tools": { "type": "stdio", "command": "npx", "args": [ "-y", "@edelciomolina/postgres-mcp", "env-file=functions/.env" ], "env": { "MCP_KEY_HOST": "DB_HOST", "MCP_KEY_PORT": "DB_PORT", "MCP_KEY_NAME": "DB_NAME", "MCP_KEY_SSLMODE": "DB_SSLMODE", "MCP_KEY_USER": "DB_USER", "MCP_KEY_PASS": "DB_PASS" } } } } ``` > πŸ’‘ The upward search behavior handles the common case automatically. Use `env-file=` when you need explicit control (CI, monorepos, Docker bind-mounts). --- ## 🧰 Available tools ### Read-only (enabled by default) | Tool | Description | |------|-------------| | `pg_execute_query` | SELECT / COUNT / EXISTS with write and multi-statement guards | | `pg_manage_query` | EXPLAIN plans, slow query analysis, `pg_stat_statements` | | `pg_inspect_schema` | Schema info and ENUM types (read-only introspection) | | `pg_get_setup_instructions` | Setup instructions per platform | | `pg_analyze_database` | Performance, configuration, and storage analysis | | `pg_monitor_database` | Real-time monitoring of connections, queries, locks, and replication | | `pg_debug_database` | Diagnose connections, locks, performance, and replication | | `pg_inspect_database_graph` | Build a full knowledge graph of the database: schemas, tables, columns, FKs, indexes, inferred relations, and business domains | | `pg_describe_table_semantics` | Describe a table with semantic context: risk level, column roles, sensitive columns, and related tables | | `pg_find_related_tables` | Find tables related to a given table via explicit FKs and inferred naming patterns, with path explanation | | `pg_classify_query_risk` | Classify query risk (`safe` / `warning` / `review` / `blocked`) without executing it | ### Write-capable (opt-in via `tool=` argument + `POSTGRES_MCP_ALLOW_WRITE=true`) | Tool | Description | |------|-------------| | `pg_manage_schema` | Schema info, create/alter tables, manage ENUMs | | `pg_manage_indexes` | List, create, drop, reindex, analyze index usage | | `pg_manage_constraints` | List, create, and drop constraints and foreign keys | | `pg_manage_functions` | List, create, and drop functions and procedures | | `pg_manage_triggers` | List, create, drop, enable/disable triggers | | `pg_manage_rls` | Row-Level Security policies | | `pg_manage_users` | User permissions, create/drop/alter users, grant/revoke | | `pg_execute_mutation` | INSERT / UPDATE / DELETE / UPSERT with parameterized queries | | `pg_execute_sql` | Arbitrary SQL execution with optional transaction support | --- ## 🧠 Semantic Layer The four `pg_*_graph` / `pg_*_semantics` / `pg_*_risk` tools build an in-memory knowledge graph of your database at runtime. This gives the LLM a structured map - schemas, tables, columns, foreign keys, inferred relations, risk levels, and business domains - **without executing any query against your data**. All inferred fields (column semantic roles, table probable types, inferred relations) are clearly tagged so the LLM knows to treat them as hints, not schema facts. ### Optional configuration (`mcp-config.json`) Place a `mcp-config.json` file beside your `.env` to tune the semantic layer and security limits. All fields are optional - omitting the file applies safe defaults. ```json { "security": { "defaultLimit": 100, "maxLimit": 1000, "blockedSchemas": ["pg_catalog", "information_schema"], "blockedTables": [], "requireLimit": true }, "semanticLayer": { "enabled": true, "inferRelationsWithoutForeignKeys": true, "inferBusinessEntities": true, "sensitiveKeywords": ["password", "secret", "token", "api_key", "ssn", "hash"] } } ``` --- ## πŸ—οΈ Architecture For a detailed view of the communication flow between the MCP client, the proxy, and PostgreSQL - including the full sequence diagram - see [ARCHITECT.md](https://github.com/edelciomolina/postgres-mcp/blob/HEAD/ARCHITECT.md). --- ## πŸ“„ License MIT Β© [Edelcio Molina](https://github.com/edelciomolina)