PostgreSQL MCP server: connect Claude and Cursor to Postgres safely

-- Sample data, standing in for your real database.
CREATE TABLE customers (
  id serial PRIMARY KEY,
  name text NOT NULL,
  email text NOT NULL,
  country text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
  id serial PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers(id),
  total_cents int NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO customers (name, email, country) VALUES
  ('Ana', 'ana@example.com', 'US'),
  ('Ben', 'ben@example.com', 'UK'),
  ('Cleo', 'cleo@example.com', 'DE');
INSERT INTO orders (customer_id, total_cents, status)
  SELECT 1 + (g % 3), 1000 + g * 37, CASE WHEN g % 5 = 0 THEN 'refunded' ELSE 'paid' END
  FROM generate_series(1, 300) AS g;

-- The role the MCP server logs in as. Read-only, short timeout, and no
-- access to the email column.
CREATE ROLE mcp_reader LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE shop TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON orders TO mcp_reader;
GRANT SELECT (id, name, country, created_at) ON customers TO mcp_reader;
ALTER ROLE mcp_reader SET default_transaction_read_only = on;
ALTER ROLE mcp_reader SET statement_timeout = '5s';

mkdir postgres-mcp && cd postgres-mcp
npm init -y
npm pkg set type=module scripts.build=tsc
npm install @modelcontextprotocol/sdk zod pg
npm install -D typescript @types/pg @types/node

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "outDir": "dist",
    "rootDir": "src",
    "strict": true,
    "skipLibCheck": true
  },
  "include": ["src/server.ts"]
}

import { McpServer } from "@modelcontextprotocol/sdk/server/mcp.js";
import { StdioServerTransport } from "@modelcontextprotocol/sdk/server/stdio.js";
import pg from "pg";
import { z } from "zod";

// Connect as the read-only role, never as an admin user.
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 3,
});

const MAX_ROWS = 200;
const log = (...args: unknown[]) => console.error("[postgres-mcp]", ...args);

const ok = (data: unknown) => ({
  content: [{ type: "text" as const, text: JSON.stringify(data, null, 2) }],
});
const fail = (message: string) => ({
  isError: true,
  content: [{ type: "text" as const, text: message }],
});

// Every query runs in a read-only transaction with a timeout, as a second
// layer on top of the role's own settings.
async function readOnly<T>(run: (client: pg.PoolClient) => Promise<T>): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN READ ONLY");
    await client.query("SET LOCAL statement_timeout = '5s'");
    const result = await run(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK").catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

const server = new McpServer({ name: "postgres", version: "1.0.0" });

server.registerTool(
  "list_tables",
  {
    title: "List tables",
    description: "List the tables you can query in the public schema. Call this first.",
    inputSchema: {},
    annotations: { readOnlyHint: true },
  },
  async () => {
    try {
      const { rows } = await readOnly((c) =>
        c.query(
          `SELECT table_name FROM information_schema.tables
           WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
           ORDER BY table_name`,
        ),
      );
      return ok(rows.map((r) => r.table_name));
    } catch (err) {
      return fail((err as Error).message);
    }
  },
);

server.registerTool(
  "describe_table",
  {
    title: "Describe table",
    description: "Show the columns and types of one table, so you can write a correct query.",
    inputSchema: {
      table: z.string().regex(/^[a-z_][a-z0-9_]*$/, "Use a plain table name like orders"),
    },
    annotations: { readOnlyHint: true },
  },
  async ({ table }) => {
    try {
      // Only columns this role may read are listed.
      const { rows } = await readOnly((c) =>
        c.query(
          `SELECT column_name, data_type, is_nullable
           FROM information_schema.columns
           WHERE table_schema = 'public' AND table_name = $1
             AND has_column_privilege(format('public.%I', table_name), column_name, 'SELECT')
           ORDER BY ordinal_position`,
          [table],
        ),
      );
      return rows.length ? ok(rows) : fail(`No readable table named ${table}.`);
    } catch (err) {
      return fail((err as Error).message);
    }
  },
);

server.registerTool(
  "run_select",
  {
    title: "Run a SELECT query",
    description: `Run one read-only SQL query (SELECT or WITH ... SELECT) and get up to ${MAX_ROWS} rows. Use describe_table first. Prefer aggregates over pulling raw rows.`,
    inputSchema: {
      sql: z.string().min(1).max(5000),
    },
    annotations: { readOnlyHint: true },
  },
  async ({ sql }) => {
    const text = sql.trim().replace(/;\s*$/, "");
    if (!/^(select|with)\b/i.test(text)) {
      return fail("Only SELECT queries are allowed.");
    }
    log("run_select", text.slice(0, 200));
    try {
      // Wrapping the query as a subquery caps the rows and makes Postgres reject
      // anything that is not a single SELECT ("SELECT 1; DROP ..." is a syntax error).
      const result = await readOnly((c) =>
        c.query(`SELECT * FROM (${text}) AS q LIMIT ${MAX_ROWS + 1}`),
      );
      const truncated = result.rows.length > MAX_ROWS;
      return ok({ rows: result.rows.slice(0, MAX_ROWS), rowCount: Math.min(result.rows.length, MAX_ROWS), truncated });
    } catch (err) {
      return fail((err as Error).message);
    }
  },
);

await server.connect(new StdioServerTransport());
log("ready");

ok    list_tables                 [ "customers", "orders" ]
ok    describe customers          id, name, country, created_at
ok    revenue by country (CTE)    rowCount 3, truncated false: [{"country":"DE","orders":"80","revenue":"5277"}, ...]
ok    all orders (row cap)        rowCount 200, truncated true
ERROR INSERT                      Only SELECT queries are allowed.
ERROR two statements              syntax error at or near ";"
ERROR DELETE hidden in a CTE      WITH clause containing a data-modifying statement must be at the top level
ERROR read the email column       permission denied for table customers
ERROR write via a function        permission denied for sequence orders_id_seq
ERROR slow query                  canceling statement due to statement timeout
orders still in table: 300

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-mcp/dist/server.js"],
      "env": {
        "DATABASE_URL": "postgres://mcp_reader:change-me@db.example.com:5432/shop"
      }
    }
  }
}

claude mcp add postgres -e DATABASE_URL=postgres://mcp_reader:change-me@db.example.com:5432/shop -- node /absolute/path/to/postgres-mcp/dist/server.js