mcp-postgres-secure Server

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.

Documentation