mcp-postgres-secure

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

- Application-levelSQL classification (blocks multi-statement queries and disallowed statement types)
- PostgreSQL sessiondefault_transaction_read_only = oninreadonlymode
- Connection lockviaPG_LOCK_CONNECTIONso 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.

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

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

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

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

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

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" } } } }

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" } } } }

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-readonlyandpostgres-dml) and enable only the one you need per project.

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] } });

Mutating SQL. Indmlmode:INSERT,UPDATE,DELETE,MERGEonly. Infullmode: 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

Optional runtime connection whenPG_LOCK_CONNECTION=falseand env vars are not set. Disabled by default when using env-based config.

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;

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.

- Parameterized queries for user-supplied values
- Single-statement enforcement (no;-chained batches)
- Statement-type validation per access mode
- Read-only PostgreSQL transactions inreadonlymode
- Runtimeconnect_dbdisabled 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.

- Invalid or disallowed SQL for the current access mode
- Multiple statements in one request
- Connection failures
- Missing parameters
- Disabled tools (executeinreadonly,connect_dbwhen locked)

Forked fromantonorlov/mcp-postgres-server.

Expert-level PostgreSQL database analysis MCP server for AI assistants.