NpgsqlRest

Appearance

Comments
AI-assisted, verified against source

Changelog v3.17.0 ​

Version 3.17.0 (2026-06-13) ​

Full Changelog

The headline of this release is MCP (Model Context Protocol) support — NpgsqlRest can project explicitly opted-in PostgreSQL routines as MCP tools that an AI agent can discover and call. Supporting that, the release adds neutral plugin extension points, makes one breaking change to the OpenAPI C# API, and ships two configuration/runtime fixes.

Walkthrough

For a hands-on tour — an "Acme Store" built from .sql files, a dual REST + MCP web page, and a real Claude agent driving the store — see the blog post Turn PostgreSQL into MCP Tools an AI Agent Can Call.

New Features ​

MCP (Model Context Protocol) server — new NpgsqlRest.Mcp plugin ​

NpgsqlRest can now expose opted-in PostgreSQL routines as MCP tools, so an AI agent can discover them (tools/list) and execute them (tools/call) over the Model Context Protocol (spec 2025-11-25). The entire MCP layer lives in the new plugin — core stays protocol-agnostic, built only on the neutral extension points below.

Opt-in, never automatic. A routine becomes a tool only when its PostgreSQL comment carries the mcp annotation:

  • mcp — expose as a tool; description derived from the comment prose.

  • mcp <text> — expose, with <text> as an inline (explicit) description.

  • mcp_description <text> (alias mcp_desc) — explicit, authoritative description.

  • mcp_name <name> — override the tool name (default: the routine name).

  • A bare mcp with no HTTP tag is MCP-only — the tool exists with no public HTTP route. The HTTP tag controls the REST route and mcp controls the tool, independently: HTTP GET + mcp = both interfaces; mcp alone = tool only (an endpoint that exists solely because a plugin requested it defaults to internal-only, so opting into MCP never silently widens the HTTP surface); internal remains the explicit way to hide a declared HTTP route. Works identically for SQL file endpoints (a .sql file with mcp and no HTTP tag becomes an MCP-only tool; files with neither annotation are skipped as non-endpoint scripts, as before). All other annotations (authorize, parameter handling, …) apply unchanged.

    Description precedence — the highest-priority source that is present wins, regardless of the order the lines appear in the comment; an explicit description suppresses the comment-prose fallback, so unrelated comment lines never leak in: mcp_description › inline mcp <text> › comment prose › routine name.

The endpoint. A single Streamable-HTTP JSON-RPC endpoint (default /mcp, POST only). It implements the lifecycle (initialize → protocol version + tools capability + serverInfo; notifications/initialized → 202; ping), tools/list (with a JSON-Schema inputSchema per tool, derived from the routine's parameters), and tools/call. Transport rules per spec: the Origin header is validated (DNS-rebinding protection — a present, untrusted origin → 403); a present MCP-Protocol-Version other than 2025-11-25 → 400; GET → 405 (no SSE).

Calling a tool. tools/call runs the routine through the same pipeline as the HTTP endpoint, forwarding the authenticated principal so authorize checks apply. Arguments map to the routine as a query string (GET/DELETE), a JSON body (POST/PUT), or path-segment substitution. The result carries:

  • structuredContent (always a JSON object): a single value → { "value": … }; a record/composite (or a set collapsed with single) → the object itself; a set → { "items": [ … ] }. The text content block carries the same JSON, serialized (backward-compatibility).
  • outputSchema (declared on the tool) derived from the routine's return columns, nullable-aware so results always conform.
  • Two error channels: business failures → isError: true in the result; structural failures (unknown method/tool, malformed request) → JSON-RPC errors.

Authorization — OAuth 2.1 Resource Server (bring-your-own Authorization Server; token validation reuses the host's bearer authentication — NpgsqlRest is not an Authorization Server). Configured under McpOptions:Authorization:

  • Protected Resource Metadata (RFC 9728) served at /.well-known/oauth-protected-resource{UrlPath} when an Authorization Server is configured.
  • RequireAuthorization gates the endpoint → 401 with WWW-Authenticate: Bearer resource_metadata="…" (RFC 9728 §5.1) so the client can discover the AS; the PRM document itself stays anonymous.
  • Audience binding (RFC 8707): with a canonical Audience configured, a token must carry it (aud claim) or it is rejected with 401.
  • Per-tool authorization: the routine's authorize/role check runs on tools/call → 401 if called anonymously, 403 insufficient_scope if the role is missing (RFC 6750 §3.1; challenges include scope and resource_metadata). No authorization logic is duplicated in the plugin — it reuses core's check.

Configuration — NpgsqlRest:McpOptions, disabled by default, surfaced in --config, --config-schema, and the JSON schema: Enabled, UrlPath (/mcp), ServerName (null → database name → "NpgsqlRest"), ServerVersion ("1.0.0"), Instructions, ToolDescriptionSuffix, RateLimiterPolicy, AllowedOrigins, and the Authorization object (RequireAuthorization, AuthorizationServers, ScopesSupported, Audience, ProtectedResourceMetadataPath, FilterToolsByRole).

Diagnostics & current limitations.

  • Enabling MCP does not enable authentication — it is configured separately (the host's Auth section). If RequireAuthorization is on but no authentication scheme is registered, a startup warning is logged.
  • A routine annotated mcp that also uses a feature with no MCP equivalent (login, logout, basic auth, upload, SSE) logs a build-time warning.
  • A routine's rate_limiter annotation does not carry to MCP (tools/call bypasses route middleware); pairing it with mcp logs a build-time warning. Use McpOptions:RateLimiterPolicy (a host-registered ASP.NET rate-limiter policy) to throttle the whole /mcp endpoint.
  • tools/list lists every opted-in tool by default (keeping them discoverable); set Authorization.FilterToolsByRole to hide tools the caller can't run. Authorization is enforced on tools/call regardless.
  • The JSON-RPC layer is hand-rolled over System.Text.Json.Nodes with relaxed escaping (conventional application/json output) — no reflection-based serialization, AOT-safe (verified via dotnet publish -p:PublishAot=true).

Plugin extension points on RoutineEndpoint ​

Neutral, plugin-facing hooks were added so a plugin can own its comment annotations without leaking plugin concepts into core (both MCP and the OpenAPI plugin are now built on these):

  • IEndpointCreateHandler.HandleCommentLine(...) (new default-interface method) — core offers each unrecognized comment line to handlers within its single parse pass; a handler claims it by returning a CommentLineResult (a log label + RequestsEndpoint). Tokens are pre-split by core. Non-breaking.
  • RoutineEndpoint.Items (lazy IDictionary<string, object?>) + TryGetItem — a per-endpoint property bag for plugin metadata (the HttpContext.Items pattern), namespaced by key.
  • RoutineEndpoint.UnhandledCommentLines (string[]?) — comment prose that neither core nor any handler claimed.
  • CommentsMode.OnlyAnnotated (new) — creates an endpoint when the comment has an HTTP tag or a plugin requests one. An endpoint created solely by a plugin request (no HTTP tag) defaults to internal-only — the plugin asked for a projection (an MCP tool), not a route — so a bare mcp is MCP-only (a debug log notes the defaulting). The client now defaults to OnlyAnnotated; existing OnlyWithHttpTag configs are unaffected — it is kept as an identical-behavior alias.
  • IEndpointCreateHandler.EndpointRequestingAnnotations (new default-interface property, default empty) — the annotation keywords for which the handler requests endpoints (Mcp: mcp, mcp_name, mcp_description, mcp_desc). Lets sources with a cheap textual pre-gate recognize endpoint candidates: the SQL file source passes a file whose comment carries an HTTP tag or one of these keywords, so a bare-mcp .sql file becomes an MCP-only tool while scripts with neither are still skipped without ever being described.

{name} annotation substitution can resolve allowlisted environment variables ​

The {name} placeholders in annotation values (response headers, custom parameters, HTTP custom type URL/headers/body) could only resolve request parameters. They can now also resolve allowlisted environment variables, so e.g. an outbound API key or a per-pod server name doesn't have to be routed through a request parameter:

sql
sql
comment on type weather_api is 'GET https://api.example.com/v1/current?city={_city}
Authorization: Bearer {WEATHER_API_KEY}';
  • Opt-in allowlist NpgsqlRest:AvailableEnvVars (mirrors StaticFiles:ParseContentOptions:AvailableEnvVars): array of names, or an object of name → default. Only listed names are ever read from the environment — the allowlist is the security boundary. (C# API: NpgsqlRestOptions.SubstitutionEnvironmentVariables, a resolved name → value dictionary.)
  • Resolved once at startup, matched case-insensitively, injected as the raw value. A routine parameter of the same name takes precedence.
  • Security: a value substituted into a response header is sent to the client — reserve secrets for outbound HTTP-type calls / custom parameters, and use response headers only for non-secret values (e.g. server/environment name).

TsClient: ExportTypes — emit request/response interfaces with the export keyword ​

The TypeScript client generator (NpgsqlRest.TsClient) previously emitted its request/response (and composite) interfaces as plain interface declarations: module-private when inlined into the client file (CreateSeparateTypeFile: false), or ambient/global in the separate {name}Types.d.ts file (the default). Neither form could be imported by other modules. The new ExportTypes option (config NpgsqlRest:ClientCodeGen:ExportTypes, default false) emits them as export interface so they can be imported:

  • Inline (CreateSeparateTypeFile: false) — interfaces are emitted as export interface in the same file as the functions.
  • Separate file (CreateSeparateTypeFile: true) — the type file becomes an importable module {name}Types.ts (export interface …) instead of an ambient {name}Types.d.ts, and the generated client file gets an import type { … } from "./{name}Types"; referencing the named types.

Has no effect when SkipTypes is true. Defaulting to false keeps existing output byte-for-byte unchanged.

Breaking Changes ​

⚠️ Safer configuration defaults: CORS credentials, passkey requirements, connection testing ​

Three configuration defaults changed as part of a security/consistency audit of the shipped appsettings.json against the in-code defaults. You are affected only if your custom configuration omits these keys — set them explicitly to keep the old behavior.

  • Cors:AllowCredentials now defaults to false (was true). Credentials (cookies, authorization headers) in cross-origin requests must now be enabled deliberately, and only together with an explicit AllowedOrigins list. This only matters when Cors:Enabled is true.
  • Auth:PasskeyAuth:UserVerificationRequirement and ResidentKeyRequirement code defaults are now "required" (were "preferred"). The shipped appsettings.json already said "required" — the in-code fallback and --config defaults disagreed; they now match the stronger, documented posture.
  • ConnectionSettings:TestConnectionStrings code default is now true (was false). Same class of fix: the shipped appsettings.json already said true; the in-code default now agrees, so connection strings are tested at startup even when the key is omitted.

⚠️ OpenAPI annotation handling moved out of core (C# API only) ​

The public properties RoutineEndpoint.OpenApiHide and RoutineEndpoint.OpenApiTags were removed, along with the core openapi comment-annotation handler. The OpenAPI plugin now parses openapi hide / hidden / ignore / tag <…> itself (from UnhandledCommentLines).

  • No change for annotation users — the openapi … comment annotations behave exactly as before (and, as before, only take effect when the OpenAPI plugin is loaded).
  • Affected only if your code sets endpoint.OpenApiHide / endpoint.OpenApiTags directly (e.g. in an EndpointCreated callback) — use the openapi comment annotation instead.

Fixes ​

Internal-only endpoints are excluded from generated client artifacts and API docs ​

Endpoints marked internal (no public HTTP route — proxy/HTTP-type-callable, or now a bare-mcp MCP-only routine) were still emitted into the generated TypeScript client (a fetch wrapper), the generated .http file (a request line), and the generated OpenAPI document (a path entry). All target a route that returns 404, so the generated artifacts advertised endpoints that don't exist. The TsClient, HttpFiles, and OpenApi plugins now skip InternalOnly endpoints. (Surfaced by the new MCP-only mode, where a bare @mcp routine has no HTTP route.)

🔴 Security: SSE scope hints were not enforced — hint-scoped events were delivered to every subscriber ​

Events published with a per-event scope override — RAISE INFO ... USING HINT = 'authorize' or USING HINT = 'authorize <role-or-user> ...' — were delivered to all connected SSE subscribers, including subscribers without the named role and unauthenticated subscribers. The hint was parsed correctly, but a control-flow bug (else if chaining) skipped the authorization checks whenever a hint was present. Endpoint-level scoping via the sse_scope annotation (no hint) was NOT affected.

Impact: any deployment using the documented per-user/per-role USING HINT pattern (e.g. private user messages or role-targeted notifications over SSE) was broadcasting those events to every connected subscriber. Upgrade is strongly recommended for anyone using SSE with hint-based scoping.

Fixed by decoupling scope enforcement from hint parsing so the Matching/Authorize checks always run on the effective scope. Covered by new tests proving delivery-by-ordering: a role-scoped event reaches only matching subscribers, a bare authorize event reaches only authenticated subscribers, and anonymous subscribers receive neither.

Malformed JSON request body now returns 400 Bad Request (was 404 Not Found) ​

When an endpoint expects a JSON body and the request body is present but not a parseable JSON object (truncated JSON, a bare array/string, …), the response is now 400 Bad Request. Previously the failed parse fell through to parameter matching and surfaced as a misleading 404 Not Found. Parse failures were and still are logged; valid requests and empty-body handling are unchanged.

Passkey/WebAuthn diagnostics: CBOR decode failures are no longer silent ​

  • A malformed WebAuthn attestation object now logs a Warning naming the decode failure (exception + payload length — never the payload itself) instead of failing silently into a generic attestation_invalid error. This gives operators an audit trail for both debugging and attack detection.
  • Indefinite-length CBOR arrays (legal in the lax conformance mode the decoder uses) are now decoded correctly; previously they failed the whole attestation.
  • A startup warning is logged when Passkey authentication is enabled with an empty RelyingPartyOrigins list — in that state WebAuthn origin validation accepts any origin, which is not recommended for production.

{name} parameter-value placeholders: case-insensitive matching + typo warning ​

The {name} placeholders that inject a request's parameter values into annotation values (response headers incl. Content-Type, custom parameters such as upload paths, and HTTP custom type URL/headers/body) had two rough edges:

  • Case sensitivity was inconsistent. Substitution matched names case-sensitively, while the related resolved-parameter SQL expression resolver matched case-insensitively. Substitution is now case-insensitive too ({userId}, {USERID}, {userid} all resolve the same parameter), consistent with PostgreSQL identifier folding.
  • Typos were silent. An unknown placeholder is left as literal text at request time (unchanged), but a misspelling like {_fil} for {_file} shipped silently into a header/path. NpgsqlRest now logs a build-time warning naming the unknown placeholder. The check covers response headers and custom parameters and only flags identifier-shaped tokens, so {0} and JSON-like {"a":1} are never mistaken for placeholders.

Bare @cached (no parameter list) used only the routine name as the cache key ​

@cached without an explicit parameter list is documented to key on all routine parameters, but the implementation left the cache-key parameter set empty — so the key was just the routine identifier, and every call returned the first response cached for that routine regardless of inputs until the TTL expired (a search/filter endpoint would serve the first query's results to every subsequent query). Endpoints that listed parameters explicitly (@cached p1, p2) were unaffected. All cache backends (Memory, Redis, Hybrid) were affected. Fixed by treating "no list" as "every parameter" at annotation-parse time.

HybridCache Cache key contains invalid content on nullable cached params ​

When CacheOptions.Type was Hybrid and a cached routine had a nullable parameter, every call where that parameter was null logged Microsoft.Extensions.Caching.Hybrid: Cache key contains invalid content and silently bypassed the cache — the endpoint still ran against the DB and returned correct data, but lost the cache hit and stampede protection for that key. Root cause: NpgsqlRest's internal cache-key encoding used a null byte (\x00) in its null marker, which HybridCache rejects.

HybridCacheWrapper now hashes every key into a SHA-256 hex string before passing it to HybridCache, so keys are valid regardless of source content; the null marker source-side also no longer uses \x00 (it is delimited by the existing \x1F separator), which is friendlier to Redis keys and log collectors across all backends. The UseHashedCacheKeys / HashKeyThreshold options keep their original purpose (Redis-backend key length / memory) and are simply a no-op for the Hybrid backend now. No user action required — Hybrid in-memory entries are flushed on restart.

JSON command parameters accept json, jsonb, or text ​

JSON payloads passed to user-authored SQL commands were bound with a hardcoded json type, so a function declaring the receiving parameter as jsonb or text failed at runtime with PostgreSQL 42883 "function does not exist" — even though the documentation states all three are acceptable.

The binding now uses an untyped (unknown) parameter, which PostgreSQL resolves server-side via the target type's input function. Affected commands: external-auth Auth.External.LoginCommand ($4 provider data, $5 analytics), CSV/Excel upload row commands (per-row metadata, Excel JSON data), and the Passkey/Fido2 commands. Fully backward compatible — json-typed parameters are unchanged; jsonb and text now also work, and NULL / quoted / array values round-trip correctly.

Optional {NAME} and required {!NAME} environment-variable placeholders ​

With Config:ParseEnvironmentVariables enabled (the default), config values support two placeholder forms, for every value type (bool, int, string, enum, arrays, dictionaries):

  • {NAME} — optional. Substituted with the variable's value when set; left untouched when not — so typed bool/int reads fall back to their default instead of crashing, and legitimate non-env brace syntax (e.g. a Serilog OutputTemplate) is preserved.
  • {!NAME} — required. Substituted with the value, or throws a clear startup error naming the variable when it is not set.

This fixes a startup crash: previously a missing optional variable left an unresolved {NAME} token that a typed read (e.g. GetConfigBool) rejected. Genuinely invalid values (e.g. "maybe" for a bool) still throw.

jsonc
jsonc
"Enabled": "{GITHUB_AUTH_ENABLED}"   // env unset → feature defaults to off (no crash)
"Enabled": "{!GITHUB_AUTH_ENABLED}"  // env unset → startup error naming the variable

Tests ​

  • An MCP test suite covering the lifecycle, tools/list / tools/call, structuredContent and outputSchema across return shapes (scalar, record, set, array, custom composite), parameter mapping (query / body / path; typed, optional, null, and json arguments), authorization (PRM, 401/403, audience binding), transport rules, and protocol edge cases.
  • A binding-contract test locking the PostgreSQL/Npgsql resolution the JSON-parameter fix relies on (json-only for the old binding; json/jsonb/text for the new one, including NULL and round-trip integrity), plus end-to-end CSV upload tests for a row-command metadata parameter declared as json, jsonb, and text.
  • Config tests for optional {NAME} (resolves when set; left untouched / defaults when not — including Serilog-template preservation) and required {!NAME} (throws when unset) across GetConfigBool / GetConfigInt / GetConfigStr and the ResolveEnv resolver.
  • Malformed-JSON body tests: truncated JSON and non-object JSON → 400; valid body unchanged.
  • SSE hardening suite: hint-scope authorization (the security-fix regression test — role-scoped, authenticated-scoped, and unscoped delivery across three differently-authenticated subscribers), multi-subscriber exactly-once fan-out, per-stream publish ordering, and subscriber-disconnect resilience.
  • Cache concurrency races: TTL-expiry under concurrent bursts (exactly one execution per cache window) and concurrent invalidation + read storms (no errors, cache coherent after).
  • CRUD endpoint authorization parity: table-comment authorize/roles enforced across select/insert/update/delete variants (401 anonymous, 403 wrong role, full cycle with the right role).

Comments