postgres-local-mcp

Small MCP stdio server for PostgreSQL inspection and carefully gated local operations.

It is designed for two common cases:

• local development databases where an agent may need schema inspection and occasional reset/apply operations;
• remote or shared databases where the MCP should be configured as read-only.

Safety Model

The default profile is read-only. In read-only mode, use tools such as db_select, schema listing, table description and dependency inspection. Tools that execute arbitrary SQL or mutate schemas are blocked.

Privileged tools are:

• db_execute_sql
• db_apply_sql_file
• db_drop_schema
• db_reset_schema

For a mutable operation to run, all of these must be true:

• safety.read_only is false;
• safety.allow_destructive is true;
• the host and database match the profile allowlists;
• for non-local hosts, safety.allow_remote_destructive is also true;
• the tool call includes the hardcoded confirmation string:

I_UNDERSTAND_THIS_CAN_DESTROY_DATA

This confirmation string is intentionally not a password and is not configurable. It is a friction step for tools and agents, not a security boundary.

db_execute_sql and db_apply_sql_file require this confirmation even if the SQL looks read-only. Use db_select for normal read-only queries.

For real protection, use a PostgreSQL user with the minimum privileges needed. Remote/shared profiles should normally use a database role that cannot INSERT, UPDATE, DELETE, DROP, TRUNCATE, ALTER, or CREATE.

Limitations

SQL classification is conservative text-based filtering, not a full PostgreSQL parser. db_select blocks obvious mutable SQL, DML CTEs and EXPLAIN ANALYZE, but database-side functions can still have side effects if the connected role is allowed to run them. Treat database roles and network access as the real safety boundary.

If an agent can edit this package, edit the MCP config and restart the server, software-only locks are not absolute. Keep production credentials and write-capable shared credentials outside agent-writeable workspaces.

Package

@erivelto_muller/postgres-local-mcp

This package is intended for npm distribution so MCP clients can run it with npx.

Quick Start

Generic process-spawned MCP configuration:

{
  "mcpServers": {
    "postgres-local": {
      "command": "npx",
      "args": ["-y", "@erivelto_muller/postgres-local-mcp"],
      "env": {
        "POSTGRES_LOCAL_MCP_CONFIG": "/path/to/postgres-local.yaml",
        "POSTGRES_LOCAL_PASSWORD": "set-outside-version-control"
      }
    }
  }
}

The server can also be started directly:

POSTGRES_LOCAL_MCP_CONFIG=/path/to/postgres-local.yaml \
npx -y @erivelto_muller/postgres-local-mcp

No repository clone is required for normal use after the package is published.

Development

npm install
npm run build -w packages/postgres-local-mcp
npm test -w packages/postgres-local-mcp

Run from a local build:

POSTGRES_LOCAL_MCP_CONFIG=/path/to/postgres-local.yaml \
node packages/postgres-local-mcp/dist/index.js

Configuration

Set POSTGRES_LOCAL_MCP_CONFIG to the main YAML config:

export POSTGRES_LOCAL_MCP_CONFIG=/path/to/postgres-local.yaml

The main config points to a profile directory:

name: postgres-local
environment: local
profiles:
  directory: /path/to/postgres-local-profiles
  default: app_local

Local Writable Profile

name: app_local
description: Local development database
environment: local
connection:
  host: localhost
  port: 5432
  database: app_local
  user: postgres
  password_env: POSTGRES_LOCAL_PASSWORD
safety:
  read_only: false
  allow_destructive: true
  allow_remote_destructive: false
  allowed_hosts:
    - localhost
    - 127.0.0.1
    - host.docker.internal
  allowed_databases:
    - app_local
  allowed_schemas:
    - public
    - app_schema
  blocked_database_patterns:
    - prod
    - production
  allow_drop_public: false
query:
  default_limit: 200
  max_limit: 5000
  statement_timeout_ms: 30000

Remote Read-Only Profile

name: reporting_readonly
description: Shared database accessed with a read-only PostgreSQL role
environment: remote
connection:
  host: db.example.com
  port: 5432
  database: reporting
  user: readonly_user
  password_env: POSTGRES_REPORTING_PASSWORD
safety:
  read_only: true
  allow_destructive: false
  allow_remote_destructive: false
  allowed_hosts:
    - db.example.com
  allowed_databases:
    - reporting
  allowed_schemas:
    - public
  blocked_database_patterns:
    - prod
    - production
  allow_drop_public: false
query:
  default_limit: 100
  max_limit: 1000
  statement_timeout_ms: 15000

connection.password is supported for private local configs, but public examples use password_env. Tool responses only expose password_source, never the password value.

Tools

• db_list_profiles
• db_profile_info
• db_healthcheck
• db_current_connection
• db_list_schemas
• db_list_tables
• db_describe_table
• db_list_views
• db_view_definition
• db_list_foreign_keys
• db_table_dependencies
• db_flyway_history
• db_select
• db_execute_sql
• db_apply_sql_file
• db_drop_schema
• db_reset_schema
• db_snapshot_schema

Examples

db_list_profiles()
db_healthcheck(profile="app_local")
db_snapshot_schema(profile="app_local", schema="app_schema", include_row_counts=false)
db_select(profile="reporting_readonly", sql="select * from public.some_view", limit=50)

Mutable example for a local development profile:

db_reset_schema(
  profile="app_local",
  schema="app_schema",
  cascade=true,
  confirm="I_UNDERSTAND_THIS_CAN_DESTROY_DATA"
)

License

MIT. See LICENSE.