AI导哈

PostgreSQL管理控制协议服务器

PostgreSQL MCP服务器提供模式内省、受控查询/写入/DDL/事务、模板管理和执行计划分析功能，适用于需要安全数据库操作权限控制的场景。

  • by RexySaragih
  • TypeScript
  • 本地部署
  • 更新于
在 GitHub 查看 RexySaragih/mcp-pgsql

使用说明

PostgreSQL MCP Server

MCP server for PostgreSQL: schema introspection, SELECT (with lock confirmation), gated writes/DDL/transactions, templates, and EXPLAIN.

Quick Start (npx)

Secrets belong in agent mcp.json env (mcpServers.*.env) — not project .env. Do not create a project .env for runtime credentials.

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@rexymayderio/postgres-mcp"],
      "env": {
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "your_database_name",
        "PGUSER": "your_username",
        "PGPASSWORD": "your-password"
      }
    }
  }
}

Optional: DATABASE_URL, PGSSL=true, PG_STATEMENT_TIMEOUT_MS, PG_MAX_ROWS.

Privilege scope: use a least-privilege Postgres role. Prefer a SELECT-only user for read-heavy agents; use a separate write role when writes are required.

Tools

Tool Notes
list_schemas Non-system schemas
list_tables Estimated rows (reltuples); exact_counts=true for COUNT(*)
describe_table Columns, PK, FKs, indexes
list_relationships FK graph
read_query SELECT only; LIMIT-capped; FOR UPDATE/FOR SHARE need confirmed
write_query INSERT/UPDATE/DELETE — strong confirm (separate from reads/DDL)
schema_query CREATE/ALTER/DROP — strong confirm (separate from DML)
transaction_query Multi-statement — per-statement risk preview + strong confirm
list_templates / run_template Safe parameterized helpers
explain_query Plan-only by default; analyze=true needs confirmed

Writes are separate tools from reads. Agents must not use read_query for DML/DDL.

Strong confirmation

MCP has no native UI modal. Mutating tools return an impact preview and do not execute until called again with the same sql and confirmed: true.

Client operators should require approval on write_query, schema_query, and transaction_query (destructiveHint / non-readOnly).

Safety

  • Comment-aware SQL classification (WITH … INSERT is not read-only)
  • Multi-statement blocked on read_query
  • statement_timeout on connections
  • Confirmation previews explain impact before mutating or locking
  • Errors redacted (no password/URL dumps)

Local development

npm install
npm test
npm run build
# Smoke (pass env inline — never create a repo .env):
# PGHOST=… PGDATABASE=… PGUSER=… PGPASSWORD=… npm run test:connections

Risks

  • Row content is untrusted (prompt injection)
  • transaction_query can still run privileged SQL after confirmation — use a scoped DB role
  • Shared bot identity across sessions

Deferred

  • Aurora IAM / RDS Data API
  • Splitting read vs write into separate MCP packages

相关 MCP