PostgreSQL MCP Server with a Dedicated Read-Only Role

Why a Dedicated Role

The postgres MCP server enforces "no writes" at the protocol level — it only exposes a query tool that wraps a read-only transaction. But protocol-level filters can have bugs. The robust pattern is to give the connection string a database user that the server physically cannot misuse.

One-time database setup

-- Create the role
CREATE ROLE mcp_readonly WITH LOGIN PASSWORD 'use-a-strong-password';

-- Allow connecting to the database
GRANT CONNECT ON DATABASE myapp TO mcp_readonly;

-- Allow listing schemas
GRANT USAGE ON SCHEMA public TO mcp_readonly;

-- Grant SELECT on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;

-- Auto-grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO mcp_readonly;

-- Allow reading sequences (for SERIAL columns in introspection)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO mcp_readonly;

Config

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://mcp_readonly:STRONG-PASSWORD@localhost:5432/myapp"
      ]
    }
  }
}

What this protects against

• A bug in the MCP server that accidentally allows DELETE.
• A model "hallucinating" a tool name that bypasses the read-only check.
• A future server upgrade that adds write tools you didn't enable.

Hardening the connection itself

Append ?sslmode=require to the connection string for any non-localhost database. For production-adjacent reads, use a separate read replica rather than your primary; the model's queries can be expensive and you don't want them competing with user traffic.

Multi-database setups

For projects with multiple databases, run multiple postgres MCP entries with different names:

"postgres-prod-replica": { ... },
"postgres-staging": { ... }