MCP PostgreSQL Secure

A Model Context Protocol server for PostgreSQL with permission-based access modes. Choose how much database power the AI gets at install time.

Access modes

| Mode | PG_ACCESS_MODE | Tools | SQL allowed | |------|------------------|-------|-------------| | Read-only | readonly (default) | query, schema introspection | SELECT, WITH, EXPLAIN, SHOW, etc. | | Read + DML | dml | above + execute | DML: INSERT, UPDATE, DELETE, MERGE | | Full access | full | above + execute (DDL) | DML + DDL: CREATE, ALTER, DROP, TRUNCATE, etc. |

Defense in depth:

• Application-level SQL classification (blocks multi-statement queries and disallowed statement types)
• PostgreSQL session default_transaction_read_only = on in readonly mode
• Connection lock via PG_LOCK_CONNECTION so credentials cannot be swapped at runtime when using env config

Pair each mode with a PostgreSQL role that has matching grants. The server enforces intent; the database user is the final authority.

Installation

From npm

npm install mcp-postgres-secure

Or run directly:

npx mcp-postgres-secure --access-mode readonly

From source (fork)

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

Point Cursor at node /absolute/path/to/mcp-postgres-secure/build/index.js.

Configuration

All modes use the same connection environment variables. Set the access level with --access-mode (CLI) or PG_ACCESS_MODE (env). The CLI flag wins if both are set.

| Variable / flag | Required | Default | Description | |-----------------|----------|---------|-------------| | --access-mode | no | readonly | readonly, dml, or full (overrides env) | | PG_ACCESS_MODE | no | readonly | Same as --access-mode | | PG_HOST | yes | — | Database host | | PG_PORT | no | 5432 | Database port | | PG_USER | yes | — | Database user | | PG_PASSWORD | yes | — | Database password | | PG_DATABASE | yes | — | Database name | | PG_LOCK_CONNECTION | no | true when env config is set | Disables connect_db at runtime |

# CLI examples
npx mcp-postgres-secure --access-mode readonly
npx mcp-postgres-secure --access-mode=dml
node build/index.js --help

1. Read-only (recommended default)

Use for exploring schemas and running analytics without write risk.

{
  "mcpServers": {
    "postgres-readonly": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-secure", "--access-mode", "readonly"],
      "env": {
        "PG_HOST": "localhost",
        "PG_PORT": "5432",
        "PG_USER": "mcp_readonly",
        "PG_PASSWORD": "your_password",
        "PG_DATABASE": "your_database",
        "PG_LOCK_CONNECTION": "true"
      }
    }
  }
}

2. Read + DML

Use when the AI may insert, update, or delete rows but must not change schema.

{
  "mcpServers": {
    "postgres-dml": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-secure", "--access-mode", "dml"],
      "env": {
        "PG_HOST": "localhost",
        "PG_PORT": "5432",
        "PG_USER": "mcp_dml",
        "PG_PASSWORD": "your_password",
        "PG_DATABASE": "your_database",
        "PG_LOCK_CONNECTION": "true"
      }
    }
  }
}

3. Full access (DDL)

Use only when schema changes are required. Prefer a dedicated low-privilege admin role, not a superuser.

{
  "mcpServers": {
    "postgres-full": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-postgres-secure", "--access-mode", "full"],
      "env": {
        "PG_HOST": "localhost",
        "PG_PORT": "5432",
        "PG_USER": "mcp_admin",
        "PG_PASSWORD": "your_password",
        "PG_DATABASE": "your_database",
        "PG_LOCK_CONNECTION": "true"
      }
    }
  }
}

You can register multiple MCP entries (e.g. postgres-readonly and postgres-dml) and enable only the one you need per project.

Available tools

query

Read-only SQL. Supports PostgreSQL ($1, $2) and MySQL-style (?) placeholders.

use_mcp_tool({
  server_name: "postgres-readonly",
  tool_name: "query",
  arguments: {
    sql: "SELECT * FROM users WHERE id = $1",
    params: [1]
  }
});

execute (dml and full modes only)

Mutating SQL. In dml mode: INSERT, UPDATE, DELETE, MERGE only. In full mode: DML and DDL.

use_mcp_tool({
  server_name: "postgres-dml",
  tool_name: "execute",
  arguments: {
    sql: "UPDATE users SET active = $1 WHERE id = $2",
    params: [true, 1]
  }
});

list_schemas, list_tables, describe_table

Schema introspection (all modes).

connect_db

Optional runtime connection when PG_LOCK_CONNECTION=false and env vars are not set. Disabled by default when using env-based config.

PostgreSQL role examples

Read-only user:

CREATE ROLE mcp_readonly LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE your_database TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;

DML user (add write grants, no DDL):

CREATE ROLE mcp_dml LOGIN PASSWORD '...';
-- same as above, plus:
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mcp_dml;

Admin user (migrations / DDL): grant only on schemas the AI should manage.

Security

• Parameterized queries for user-supplied values
• Single-statement enforcement (no ;-chained batches)
• Statement-type validation per access mode
• Read-only PostgreSQL transactions in readonly mode
• Runtime connect_db disabled when connection is env-locked
• Credentials via environment variables (not chat arguments)

Limitations: validation is keyword-based, not a full SQL parser. Use least-privilege DB roles and non-production databases when possible.

Error handling

The server returns clear errors for:

• Invalid or disallowed SQL for the current access mode
• Multiple statements in one request
• Connection failures
• Missing parameters
• Disabled tools (execute in readonly, connect_db when locked)

License

MIT

Upstream

Forked from antonorlov/mcp-postgres-server.