{
  "markdown": "> **English** | 🌐 [Português](README_PT.md)\n\n<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\">\n  <tr>\n    <td width=\"110\">\n      <img src=\"https://raw.githubusercontent.com/edelciomolina/postgres-mcp/main/icon.png\" width=\"96\" alt=\"Postgres MCP Icon\"/>\n    </td>\n    <td>\n      <h1>Postgres MCP</h1>\n      <p>🔌 Native MCP server for PostgreSQL - reads credentials from <code>.env</code> at runtime with flexible key mapping, configurable tool selection, and <strong>read-only mode by default</strong>.</p>\n      <a href=\"https://www.npmjs.com/package/@edelciomolina/postgres-mcp\"><img src=\"https://img.shields.io/npm/v/@edelciomolina/postgres-mcp\" alt=\"npm version\"/></a>\n      <a href=\"https://www.npmjs.com/package/@edelciomolina/postgres-mcp\"><img src=\"https://img.shields.io/npm/l/%40edelciomolina%2Fpostgres-mcp\" alt=\"license\"/></a>\n      <a href=\"https://github.com/edelciomolina/postgres-mcp/actions/workflows/ci.yml\"><img src=\"https://github.com/edelciomolina/postgres-mcp/actions/workflows/ci.yml/badge.svg\" alt=\"CI\"/></a>\n    </td>\n  </tr>\n</table>\n\n---\n\n## ✨ What it does\n\nMost 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.\n\nBuilt with [`@modelcontextprotocol/sdk`](https://www.npmjs.com/package/@modelcontextprotocol/sdk) and [`pg`](https://www.npmjs.com/package/pg), it provides:\n\n- 🧠 **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\n- 🛡️ **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\n- 🔐 **Runtime credential resolution** - credentials are read from `.env` at startup; nothing sensitive lives in `mcp.json`\n- 🎯 **Explicit tool selection** - every tool is opt-in via `tool=<name>` args, so the LLM only sees what you choose to expose\n\n---\n\n## 📋 Requirements\n\n- ⚙️ Node.js >= 18\n- 📄 A `.env` file with database credentials (anywhere in the project tree - see [.env Discovery](#-env-file-discovery))\n\n---\n\n## 🚀 Installation\n\nThere are two ways to use this package. Choose the one that best fits your workflow.\n\n### Option 1 - No installation (via `npx`, recommended for quick start)\n\nNo installation needed. `npx` downloads and runs the package on demand. Add `-y` as the first argument to skip the confirmation prompt.\n\n```json\n{\n  \"servers\": {\n    \"Postgres Tools\": {\n      \"type\": \"stdio\",\n      \"command\": \"npx\",\n      \"args\": [\n        \"-y\",\n        \"@edelciomolina/postgres-mcp\"\n      ],\n      \"env\": {\n        \"MCP_KEY_HOST\":    \"DB_HOST\",\n        \"MCP_KEY_PORT\":    \"DB_PORT\",\n        \"MCP_KEY_NAME\":    \"DB_NAME\",\n        \"MCP_KEY_SSLMODE\": \"DB_SSLMODE\",\n        \"MCP_KEY_USER\":    \"DB_USER\",\n        \"MCP_KEY_PASS\":    \"DB_PASS\"\n      }\n    }\n  }\n}\n```\n\nThis 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).\n\n> 💡 **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).\n\n---\n\n### Option 2 - Install via VS Code (MCP extension marketplace)\n\nVS Code supports discovering and installing MCP servers directly in the editor, without using the terminal.\n\n1. Open the **Command Palette** (<kbd>Cmd</kbd>+<kbd>Shift</kbd>+<kbd>P</kbd> on Mac / <kbd>Ctrl</kbd>+<kbd>Shift</kbd>+<kbd>P</kbd> on Windows/Linux)\n2. Run **`MCP: Add Server`**\n3. Choose **\"Browse MCP Servers\"** (or **\"From registry\"**, depending on your VS Code version)\n4. Search for **`postgres-mcp`** or **`edelciomolina`**\n5. Select **Postgres MCP** and follow the instructions - VS Code will add the entry to your `mcp.json` automatically\n\n> 💡 You can also open the MCP Servers panel via **Copilot chat icon → Manage MCP Servers** to browse, enable, or disable servers at any time.\n\nAfter 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.\n\n---\n\n## 🚀 Usage in VS Code (`mcp.json`)\n\n**Read-only (default - no `tool=` arguments needed):**\n\n```json\n{\n  \"servers\": {\n    \"Postgres Tools\": {\n      \"type\": \"stdio\",\n      \"command\": \"npx\",\n      \"args\": [\"@edelciomolina/postgres-mcp\"],\n      \"env\": {\n        \"MCP_KEY_HOST\":    \"DB_HOST\",\n        \"MCP_KEY_PORT\":    \"DB_PORT\",\n        \"MCP_KEY_NAME\":    \"DB_NAME\",\n        \"MCP_KEY_SSLMODE\": \"DB_SSLMODE\",\n        \"MCP_KEY_USER\":    \"DB_USER\",\n        \"MCP_KEY_PASS\":    \"DB_PASS\"\n      }\n    }\n  }\n}\n```\n\n**With write tools (explicit opt-in required):**\n\n```json\n{\n  \"servers\": {\n    \"Postgres Tools\": {\n      \"type\": \"stdio\",\n      \"command\": \"npx\",\n      \"args\": [\n        \"@edelciomolina/postgres-mcp\",\n        \"tool=pg_manage_schema\",\n        \"tool=pg_manage_indexes\"\n      ],\n      \"env\": {\n        \"POSTGRES_MCP_ALLOW_WRITE\": \"true\",\n        \"MCP_KEY_HOST\":    \"DB_HOST\",\n        \"MCP_KEY_PORT\":    \"DB_PORT\",\n        \"MCP_KEY_NAME\":    \"DB_NAME\",\n        \"MCP_KEY_SSLMODE\": \"DB_SSLMODE\",\n        \"MCP_KEY_USER\":    \"DB_USER\",\n        \"MCP_KEY_PASS\":    \"DB_PASS\"\n      }\n    }\n  }\n}\n```\n\n> ⚠️ Write-capable tools require `POSTGRES_MCP_ALLOW_WRITE=true` in `env`. Without it, the server exits at startup.\n\nThe corresponding `.env` file at the root of your project:\n\n```env\nDB_HOST=db.your-project.supabase.co\nDB_PORT=5432\nDB_NAME=postgres\nDB_SSLMODE=require\nDB_USER=readonly_user\nDB_PASS=your_password\n```\n\n---\n\n## ⚙️ How `mcp.json` configuration works\n\n### 🗝️ `env` - credential key mapping\n\nThe `env` block does **not** contain the actual credentials. It maps each `MCP_KEY_*` to the variable name in your `.env` file.\n\n| Key in `env`      | Points to `.env` variable   | Example value            |\n|-------------------|-----------------------------|--------------------------|\n| `MCP_KEY_URL`     | `DATABASE_URL`              | `postgresql://user:pass@host:5432/db?sslmode=require` |\n| `MCP_KEY_HOST`    | `DB_HOST`                   | `db.example.supabase.co` |\n| `MCP_KEY_PORT`    | `DB_PORT`                   | `5432`                   |\n| `MCP_KEY_NAME`    | `DB_NAME`                   | `postgres`               |\n| `MCP_KEY_SSLMODE` | `DB_SSLMODE`                | `require`                |\n| `MCP_KEY_USER`    | `DB_USER`                   | `readonly_user`          |\n| `MCP_KEY_PASS`    | `DB_PASS`                   | `secret`                 |\n\n> **Priority:** when `MCP_KEY_URL` (or `DATABASE_URL`) is present, the server uses the URL directly and **ignores** the individual credential keys.\n\nThis indirection lets you use **any variable name** in your `.env` - useful when sharing a `.env` across multiple services with different naming conventions.\n\n### 🔧 `args` - tool selection via `tool=` prefix\n\nEach enabled MCP tool is declared as a separate argument in the format `tool=<name>`:\n\n```json\n\"args\": [\n  \"-y\",\n  \"@edelciomolina/postgres-mcp\",\n  \"tool=pg_manage_schema\",\n  \"tool=pg_manage_indexes\"\n]\n```\n\nThis makes the tool list **explicit and auditable** directly in `mcp.json` - no hidden configuration files. 🔍\n\n---\n\n## 🔗 Connection via URL (`DATABASE_URL`)\n\nIn addition to individual credentials, you can provide a **full connection string** - the standard format on platforms like Supabase, Neon, and Railway.\n\n**`.env`:**\n```env\nDATABASE_URL=postgresql://user:password@host:5432/database?sslmode=require\n```\n\n**`mcp.json`:**\n```json\n{\n  \"servers\": {\n    \"Postgres Tools\": {\n      \"type\": \"stdio\",\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@edelciomolina/postgres-mcp\"],\n      \"env\": {\n        \"MCP_KEY_URL\": \"DATABASE_URL\"\n      }\n    }\n  }\n}\n```\n\nThe 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.\n\nIf the platform uses a different name (e.g. `DB_URL`), just adjust the mapping:\n```json\n\"MCP_KEY_URL\": \"DB_URL\"\n```\n\n---\n\n## 🛡️ Why read-only is the default\n\nIf 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.\n\n**✅ Included in defaults (read-only):**\n\n```\npg_execute_query       pg_manage_query        pg_inspect_schema\npg_get_setup_instructions                     pg_analyze_database\npg_monitor_database                           pg_debug_database\npg_inspect_database_graph                     pg_describe_table_semantics\npg_find_related_tables                        pg_classify_query_risk\n```\n\n> 💡 `pg_execute_query` rejects `INSERT`, `UPDATE`, `DELETE`, DDL, `ANALYZE`, `VACUUM`, `EXPLAIN ANALYZE` and other write/maintenance commands before the database is queried.\n\n> 💡 `pg_inspect_schema` provides read-only schema introspection (`get_info`, `get_enums`). For DDL operations, use `pg_manage_schema` with explicit opt-in.\n\n**⚠️ Excluded from defaults - require `tool=` argument AND `POSTGRES_MCP_ALLOW_WRITE=true`:**\n\n| Tool | Operations |\n|------|-----------|\n| `pg_manage_schema` | CREATE TABLE, ALTER TABLE, CREATE TYPE |\n| `pg_manage_indexes` | CREATE INDEX, DROP INDEX, REINDEX |\n| `pg_manage_constraints` | ADD CONSTRAINT, DROP CONSTRAINT |\n| `pg_manage_functions` | CREATE FUNCTION, DROP FUNCTION |\n| `pg_manage_triggers` | CREATE TRIGGER, DROP TRIGGER, enable/disable |\n| `pg_manage_rls` | ENABLE/DISABLE RLS, CREATE/ALTER/DROP POLICY |\n| `pg_manage_users` | CREATE/DROP/ALTER USER, GRANT, REVOKE |\n| `pg_execute_mutation` | INSERT / UPDATE / DELETE / UPSERT |\n| `pg_execute_sql` | Arbitrary SQL with transaction support |\n\n---\n\n## 📍 `.env` file discovery\n\nThe server resolves the `.env` file in this order:\n\n1. **`env-file=<path>` argument** - explicit path relative to `cwd`; takes priority over everything\n2. **Upward search** - starting from `cwd`, searches each parent directory until a `.env` is found or the filesystem root is reached\n\nIf no `.env` is found, the server exits with a clear error message.\n\n### Monorepos and subfolders\n\nWhen 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:\n\n```json\n{\n  \"servers\": {\n    \"Postgres Tools\": {\n      \"type\": \"stdio\",\n      \"command\": \"npx\",\n      \"args\": [\n        \"-y\",\n        \"@edelciomolina/postgres-mcp\",\n        \"env-file=functions/.env\"\n      ],\n      \"env\": {\n        \"MCP_KEY_HOST\":    \"DB_HOST\",\n        \"MCP_KEY_PORT\":    \"DB_PORT\",\n        \"MCP_KEY_NAME\":    \"DB_NAME\",\n        \"MCP_KEY_SSLMODE\": \"DB_SSLMODE\",\n        \"MCP_KEY_USER\":    \"DB_USER\",\n        \"MCP_KEY_PASS\":    \"DB_PASS\"\n      }\n    }\n  }\n}\n```\n\n> 💡 The upward search behavior handles the common case automatically. Use `env-file=` when you need explicit control (CI, monorepos, Docker bind-mounts).\n\n---\n\n## 🧰 Available tools\n\n### Read-only (enabled by default)\n\n| Tool | Description |\n|------|-------------|\n| `pg_execute_query` | SELECT / COUNT / EXISTS with write and multi-statement guards |\n| `pg_manage_query` | EXPLAIN plans, slow query analysis, `pg_stat_statements` |\n| `pg_inspect_schema` | Schema info and ENUM types (read-only introspection) |\n| `pg_get_setup_instructions` | Setup instructions per platform |\n| `pg_analyze_database` | Performance, configuration, and storage analysis |\n| `pg_monitor_database` | Real-time monitoring of connections, queries, locks, and replication |\n| `pg_debug_database` | Diagnose connections, locks, performance, and replication |\n| `pg_inspect_database_graph` | Build a full knowledge graph of the database: schemas, tables, columns, FKs, indexes, inferred relations, and business domains |\n| `pg_describe_table_semantics` | Describe a table with semantic context: risk level, column roles, sensitive columns, and related tables |\n| `pg_find_related_tables` | Find tables related to a given table via explicit FKs and inferred naming patterns, with path explanation |\n| `pg_classify_query_risk` | Classify query risk (`safe` / `warning` / `review` / `blocked`) without executing it |\n\n### Write-capable (opt-in via `tool=` argument + `POSTGRES_MCP_ALLOW_WRITE=true`)\n\n| Tool | Description |\n|------|-------------|\n| `pg_manage_schema` | Schema info, create/alter tables, manage ENUMs |\n| `pg_manage_indexes` | List, create, drop, reindex, analyze index usage |\n| `pg_manage_constraints` | List, create, and drop constraints and foreign keys |\n| `pg_manage_functions` | List, create, and drop functions and procedures |\n| `pg_manage_triggers` | List, create, drop, enable/disable triggers |\n| `pg_manage_rls` | Row-Level Security policies |\n| `pg_manage_users` | User permissions, create/drop/alter users, grant/revoke |\n| `pg_execute_mutation` | INSERT / UPDATE / DELETE / UPSERT with parameterized queries |\n| `pg_execute_sql` | Arbitrary SQL execution with optional transaction support |\n\n---\n\n## 🧠 Semantic Layer\n\nThe 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**.\n\nAll 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.\n\n### Optional configuration (`mcp-config.json`)\n\nPlace 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.\n\n```json\n{\n  \"security\": {\n    \"defaultLimit\": 100,\n    \"maxLimit\": 1000,\n    \"blockedSchemas\": [\"pg_catalog\", \"information_schema\"],\n    \"blockedTables\": [],\n    \"requireLimit\": true\n  },\n  \"semanticLayer\": {\n    \"enabled\": true,\n    \"inferRelationsWithoutForeignKeys\": true,\n    \"inferBusinessEntities\": true,\n    \"sensitiveKeywords\": [\"password\", \"secret\", \"token\", \"api_key\", \"ssn\", \"hash\"]\n  }\n}\n```\n\n---\n\n## 🏗️ Architecture\n\nFor a detailed view of the communication flow between the MCP client, the proxy, and PostgreSQL - including the full sequence diagram - see [ARCHITECT.md](ARCHITECT.md).\n\n---\n\n## 📄 License\n\nMIT © [Edelcio Molina](https://github.com/edelciomolina)\n\n",
  "bytes": 14775,
  "sha": "763e3d85be16111608c025faeef7daa6827cfc813f9cc0fc10fa04ad0544f739",
  "repo_slug": "edelciomolina/postgres-mcp",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_edelciomolina_postgres_mcp_c0be76b8/readme"
}