MCP 服务器

postgrest-mcp

MCP server for PostgREST that turns PostgreSQL databases into RESTful APIs, providing 10 tools for schema discovery, CRUD operations, filtering, resource embedding, and RPC.

README

@node2flow/postgrest-mcp

MCP server for PostgREST — a standalone web server that turns PostgreSQL databases into RESTful APIs. 10 tools for schema discovery, CRUD operations, filtering, resource embedding, and RPC via the Model Context Protocol.

Quick Start

Claude Desktop / Cursor

Add to your MCP config:

{
  "mcpServers": {
    "postgrest": {
      "command": "npx",
      "args": ["-y", "@node2flow/postgrest-mcp"],
      "env": {
        "POSTGREST_URL": "http://localhost:3000",
        "POSTGREST_TOKEN": "your-jwt-token"
      }
    }
  }
}

Note: JWT token is optional. If PostgREST has an anonymous role configured (db-anon-role), schema and read operations work without authentication.

HTTP Mode

POSTGREST_URL=http://localhost:3000 POSTGREST_TOKEN=your-jwt npx @node2flow/postgrest-mcp --http

MCP endpoint: http://localhost:3000/mcp

Cloudflare Worker

Available at: https://postgrest-mcp-community.node2flow.net/mcp

POST https://postgrest-mcp-community.node2flow.net/mcp?POSTGREST_URL=http://your-server:3000&POSTGREST_TOKEN=your-jwt

Tools (10)

Schema (2) — No Auth Required (with anonymous role)

Tool	Description
`pg_get_schema`	Get OpenAPI schema — lists all tables, views, functions
`pg_describe_table`	Get column details, types, constraints for a table/view

Read (3) — Read-Only

Tool	Description
`pg_list_records`	Query records with filters, select, order, pagination
`pg_count_records`	Count records (exact, planned, or estimated)
`pg_call_function`	Call PostgreSQL functions/procedures via RPC

Write (5) — Requires Auth

Tool	Description	Risk
`pg_insert_records`	Insert one or more records	Safe
`pg_update_records`	Update records matching a filter	Safe (filter required)
`pg_upsert_records`	Insert or update on conflict	Safe
`pg_delete_records`	Delete records matching a filter	Destructive
`pg_replace_record`	Full replace (PUT) a single record	Safe

Filter Syntax

PostgREST uses a powerful filter syntax on query parameters:

# Comparison
age=gt.18                    # greater than
status=eq.active             # equals
price=lte.100               # less than or equal

# Pattern matching
name=ilike.*john*            # case-insensitive LIKE
email=like.*@gmail.com       # case-sensitive LIKE

# Lists and NULL
id=in.(1,2,3)               # IN list
deleted_at=is.null           # NULL check

# Logic
or=(age.lt.18,age.gt.65)    # OR conditions
not.status=eq.inactive       # NOT

# Full-text search
content=fts.postgresql       # full-text search

# Array/JSONB
tags=cs.{tech,api}           # array contains

Resource Embedding (JOINs)

Query related tables using the select parameter:

*,orders(*)                         — embed all columns from related table
id,name,orders(id,total,status)     — specific columns from embed
id,author:user_id(name,email)       — renamed embed

Filter on embedded resources:

select: id,name,orders(*)
filter: orders.status=eq.completed

Configuration

Parameter	Required	Description
`POSTGREST_URL`	Yes	PostgREST server URL (e.g. `http://localhost:3000`)
`POSTGREST_TOKEN`	No	JWT token for authenticated requests

JWT Authentication

PostgREST uses JSON Web Tokens (JWT) for stateless authentication.

Configure jwt-secret in your PostgREST config
Generate a JWT with the appropriate role claim
Pass the token via POSTGREST_TOKEN environment variable

JWT Claims:

role — Database role name (required for RLS)
exp — Token expiration (Unix timestamp)
Custom claims accessible in SQL via current_setting()

Tip: Use db-anon-role in PostgREST config for public read-only access without tokens.

Safety

Update and Delete require a filter — prevents accidental full-table operations
Use pg_count_records to verify filter matches before delete
Use return="representation" to see what was changed
Use pg_describe_table to check required columns before insert