What is Postgresql Mcp Server

postgresql-mcp-server is a Spring Boot application that implements an MCP (Model Context Protocol) server, providing read-only access to a PostgreSQL database through STDIO transport.

Use cases

Use cases include providing data access for front-end applications, integrating with data visualization tools, and enabling secure API endpoints for data retrieval in microservices architectures.

How to use

To use postgresql-mcp-server, you need to execute a read-only SQL query with a WHERE clause, specifying optional parameters like pageNumber and pageSize. The server validates the query and returns results in JSON format. You can build the application using Maven and create Docker images for deployment.

Key features

Key features include executing read-only SQL queries with pagination, automatic discovery of table schemas, and exposing metadata as JSON via MCP resource endpoints. The server enforces query validation to ensure security and proper usage.

Where to use

postgresql-mcp-server can be used in various fields such as data analytics, reporting systems, and applications requiring secure read-only access to PostgreSQL databases.

Spring boot Starter for Postgresql MCP Servers With Spring AI

A Spring Boot application implementing an MCP (Model Context Protocol) server that provides read-only access to a
PostgreSQL database via STDIO transport.

Components

Tools

• query
  • Executes a read-only SQL query with paging
  • Input:
    • sql (string): SQL query. Must include a WHERE clause, must not use wildcard (SELECT *).
    • pageNumber (integer, optional, default = 0): Zero-based page index
    • pageSize (integer, optional, default = 10, max = 50): Number of rows per page
  • Behavior:
    • Validates that the query contains a WHERE clause
    • Prohibits SELECT * or alias wildcards
    • Executes query in a READ ONLY transaction with LIMIT/OFFSET
    • Returns JSON array of records

Resources

• Table Schemas (postgres://<host>/<table>/schema)
  • Automatically discovered from information_schema.columns
  • Each resource URI lists the table’s column metadata:
    • column_name (string)
    • data_type (string)
  • Exposed as JSON via MCP resource endpoints
  • Dynamic discovery on each request; no preloading at startup

To create jar file

mvn clean package

Podman Image

There are two type Docker image for Spring MCP server for this projecy

SSE Docker Image

• To create SSE docker image use Dockerfile

 podman build -f dockers/sse/Dockerfile   -t postgresql-mcp-server:latest  .

STDIO Docker Image

• To create STDIO docker image use Dockerfile

  podman build   -f dockers/stdio/Dockerfile   -t postgresql-mcp-server:latest  .

 podman build -t postgresql-mcp-server  .

Create Postgresql MCP container For SSE

-- change host to database host, change 5432 to database port

podman run -i --rm -e DATABASE_URL=jdbc:postgresql://host:5432/db -e DATABASE_USERNAME=user -e DATABASE_PASSWORD=pass -e APP_CURRENT_SCHEMA=public mcp-postgres-spring

Host / Client settings

Claude Desktop

this configuration should be added to claude_desktop_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run",
        "-i",
        "--rm",
        "-e",
        "DATABASE_URL=${input:pg_url}",
        "-e",
        "DATABASE_USERNAME=${input:pg_user}",
        "-e",
        "DATABASE_PASSWORD=${input:pg_password}",
        "-e",
        "APP_CURRENT_SCHEMA=${input:pg_schema:public}",
        "mcp-postgres-spring"
      ]
    }
  }
}

VS CODE: CODING ASSISTANTS Settings

For manual installation, add the following JSON block to your Preferences (JSON) file in VS Code. You can do this by
pressing Ctrl + Shift + P and typing Preferences: Open User Settings (JSON).

Optionally, you can add it to a file called .vscode/mcp.json in your workspace. This allows you to share the
configuration with others.

Note that the mcp key is not required in the .vscode/mcp.json file.

{
  "mcp": {
    "inputs": [
      {
        "type": "promptString",
        "id": "pg_url",
        "description": "PostgreSQL URL (e.g. jdbc:postgresql://host:5432/db)"
      },
      {
        "type": "promptString",
        "id": "pg_user",
        "description": "Database username"
      },
      {
        "type": "promptString",
        "id": "pg_password",
        "description": "Database password"
      },
      {
        "type": "promptString",
        "id": "pg_schema",
        "description": "Database schema (default: public)",
        "default": "public"
      }
    ],
    "servers": {
      "postgres": {
        "command": "docker",
        "args": [
          "run",
          "-i",
          "--rm",
          "-e",
          "DATABASE_URL=${input:pg_url}",
          "-e",
          "DATABASE_USERNAME=${input:pg_user}",
          "-e",
          "DATABASE_PASSWORD=${input:pg_password}",
          "mcp-postgres-spring"
        ]
      }
    }
  }
}

TECH Stack

• Spring Boot: 3.4.4
• Spring AI: 1.0.0-M7
• Spring MCP Server (Stdio/Webflux)
• Lombok: 1.18.38
• SpotBugs: 4.8.6
• Jackson Databind
• CheckStyle