Postgrest

smithery badge npm version License: MIT

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.

1. Configure jwt-secret in your PostgREST config
2. Generate a JWT with the appropriate role claim
3. 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

---

What is PostgREST?

PostgREST is a standalone web server that:

• Turns PostgreSQL tables into CRUD endpoints (GET, POST, PATCH, PUT, DELETE)
• Turns views into read-only endpoints
• Turns functions into RPC endpoints (/rpc/function_name)
• Uses PostgreSQL Row Level Security (RLS) for authorization
• Auto-generates an OpenAPI specification

---

License

MIT License - see LICENSE

Copyright (c) 2026 Node2Flow

Links

• npm Package
• PostgREST Documentation
• MCP Protocol
• Node2Flow