mcp-postgres-secure

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.