mcpg-plugin-backend-sql MCP server

SQL Backend — dev.mcpg.backend.sql

class backend · native · package mcpg-plugin-backend-sql · artifact libmcpg_plugin_backend_sql.so

SQL database backend plugin for mcpg. Exposes relational databases (Postgres, MySQL/MariaDB, SQLite) as MCP tools, resources, and pipeline steps via the standard BackendPlugin contract plus SQL-specific surfaces. Reach for it to query a database directly instead of wrapping it behind a REST service.

One cdylib ships four entities under dev.mcpg.backend.sql:

backend.kind	Entity manifest id	Surface
sql	dev.mcpg.backend.sql	tool / resource / pipeline-step dispatch
sql_polling	dev.mcpg.watch.sql_polling	interval-hash resource watch strategy
postgres_listen_notify	dev.mcpg.watch.postgres_listen_notify	Postgres LISTEN/NOTIFY watch strategy
sql_changes	dev.mcpg.watch.sql_changes	each inserted, updated or deleted row a keyset query reads, as an MCP Events source that resumes

Configuration

The plugins: entry just loads the cdylib; per-call config lives in each tool/resource binding's backend: { kind: sql, ... } block (the plugin owns its own SqlBackendConfig shape and validates it at boot).

plugins:
  - id: dev.mcpg.backend.sql
    class: backend
    source: { path: ./plugins/libmcpg_plugin_backend_sql.so }

The binding backend.kind: sql block must carry at least driver, url, and query (see "Minimal config" below for the full shape).

Status

Shipped. Synchronous request/response calls against PostgreSQL, MySQL/MariaDB, and SQLite. All six row modes ship: single | many | scalar | affected_rows | resource_contents | stream. Parameters are always bound through sqlx's prepared-statement interface — no string interpolation, ever.

Additional capabilities shipped beyond the MVP:

• list_query — keyset-paginated dynamic resources/list provider for kind: resource_template backends.
• await: — fire-and-wait runtime: optional trigger → poll check → CEL predicate → return the matched row or Timeout.
• sql_tx pipeline step — transactional grouping of N statements on a pinned pool connection; rollback on any nested-step failure Postgres + SQLite; MySQL returns InvalidSpec until the driver-side SqlTxHandle impl lands.
• Driver-level cancel — Postgres via pg_cancel_backend on a side connection; MySQL via KILL QUERY <connection_id> (requires PROCESS / CONNECTION_ADMIN privilege on the pool user — probed at registration via SHOW GRANTS FOR CURRENT_USER; opt out with pool.require_cancel_privilege: false).
• Watch strategies — sql_polling (interval hash), postgres_listen_notify (push via LISTEN/NOTIFY) and sql_changes (a keyset query over an id, a change marker or a trigger outbox; one sql.row.changed event per row with op insert, update or delete; resumes after the last stored key) as pluggable WatchStrategyPlugins; and postgres_cdc, every change a PostgreSQL publication carries, from a logical replication slot (see PostgreSQL change data capture).
• Schema-drift retry — automatic one-shot retry on Postgres SQLSTATEs 26000/42P18/0A000 and MySQL 1615 ER_NEED_REPREPARE when a cached prepared statement goes stale after concurrent DDL.
• CEL-computed params via param_exprs, schema derivation from prepared-statement metadata, per-backend circuit breaker, in-flight progress heartbeats, and structured mcpg::sql::audit tracing.

Not yet shipped: row_mode: stream cursor continuation (first-chunk shape ships today; fetch_more is the follow-up), poll-mode content-fetcher bridge for the WatchEngine, and MSSQL driver.

Session vars work on Postgres (via SET LOCAL inside a transaction) and MySQL/MariaDB (bound SET @var statements on a pinned connection); SQLite ignores them.

Supported engines

Driver	Scheme(s)	Backend	Session vars	INSERT/UPDATE rows_affected
Postgres	postgres, postgresql	sqlx/postgres	yes (SET LOCAL)	yes
MySQL	mysql, mariadb	sqlx/mysql	yes (SET @var)	yes
SQLite	sqlite	sqlx/sqlite	no	yes

Each driver is gated behind a Cargo feature of the same name. The default build enables all three.

SQLite extensions

SQLite runs inside the gateway process; the plugin compiles it in, so the host needs no libsqlite3. The compiled-in SQLite already has FTS3, FTS4 and FTS5, JSON1, R-Tree, DBSTAT, STAT4 and SOUNDEX — no extension is needed for those.

A SQLite binding can also load run-time loadable extensions (sqlite-vec, SpatiaLite, and others). An extension is native code that runs in the gateway process with the gateway's privileges, so the binding config alone cannot load one: whoever starts the gateway must also approve the directory it comes from.

Turn it on

Set MCPG_SQLITE_EXTENSION_DIRS in the gateway's environment to the directories that hold approved extensions, separated like PATH (: on Linux and macOS). Every entry must be an absolute path. Unset or empty, loading is off, and a binding that lists an extension fails to register. The variable is read once, when the plugin loads.

The variable belongs to the deployment, never to the gateway config:

• Docker: -e MCPG_SQLITE_EXTENSION_DIRS=/opt/sqlite-ext, with the directory mounted read-only.
• Helm (mcpg chart): extraEnv for the variable, extraVolumes and extraVolumeMounts (readOnly: true) for the directory.
• systemd: Environment=MCPG_SQLITE_EXTENSION_DIRS=/opt/sqlite-ext.

The public gateway images contain no extensions. Mount them, or build an image of your own on top.

Configure a binding

backend:
  kind: sql
  driver: sqlite
  url: "sqlite:/data/app.db?mode=rwc"
  sqlite:
    extensions:
      - path: /opt/sqlite-ext/vec0.so
        sha256: 5f1c…  # sha256sum /opt/sqlite-ext/vec0.so
      - path: /opt/sqlite-ext/mod_spatialite.so
        sha256: 9be2…
        entrypoint: sqlite3_modspatialite_init
  query:
    sql: "SELECT vec_version() AS version"
    row_mode: single

Field	Required	Meaning
path	yes	Absolute path of the shared library, with its file extension. No ...
sha256	yes	SHA-256 of the file, 64 hex digits (either case).
entrypoint	no	Entry-point symbol, a C identifier. Absent: SQLite tries sqlite3_extension_init, then sqlite3_<name>_init, where <name> is the file name's letters, lower-cased, after any lib prefix and before the first . (vec0.so → sqlite3_vec_init).

A binding lists at most 16 extensions, each file once. Config validation refuses the sqlite: block on any driver other than sqlite.

What the plugin checks

When the binding registers, and again before each new pool connection loads the files, every extension must pass all of these:

1. Its canonical path (symbolic links resolved) is inside a canonical approved directory. A link that leads out of the directory is refused.
2. It is a regular file of at most 256 MiB.
3. The file and every directory above it, up to /, are owned by root or by the gateway's user. None is writable by group or others, except a directory with the sticky bit (such as /tmp).
4. Its SHA-256 equals sha256. A new connection hashes the file again only when its device, inode, size, mtime or ctime changed.

At registration the plugin then loads the extensions into one trial connection, so a file SQLite cannot load, or a missing entry point, fails registration with SQLite's own reason.

How the load works

Each new pool connection loads the extensions in the order listed. The plugin turns on SQLite's C loading interface for the load only, and turns it off again before the connection serves a query. The SQL function load_extension() stays off on every connection, so a query cannot load a library: it fails with not authorized.

If a file fails a check after registration, a new connection is refused. The caller sees only a pool timeout, which does not disclose server paths. The gateway log has the reason, at warn level on target mcpg::sql::sqlite_ext.

Replace an extension

1. Put the new file in an approved directory under a new name, or rename it over the old one. Do not write into a loaded file: the gateway has it mapped as code, and changing it in place can crash the gateway.
2. Change sha256 (and path, if the name changed).
3. Restart the gateway. A process keeps a library it has loaded, so a new file at the same path can go on running the old code until a restart.

Limits

• Linux and macOS only. On Windows every load is refused.
• The file must match the gateway's OS, CPU and C library: a glibc gateway needs a glibc build, the musl image a musl build.
• The checks run before the load. Whoever can write the file as its owner can still swap it between the check and the load, so keep the files and their directories owned by root and read-only to the gateway's user.

Credentials

The connection URL is the sole credential surface. Operators embed secrets directly in the URL and use the gateway's string interpolator (${env.VAR} at config-load time; future vault:… / aws-sm:… schemes via a plugin-provided resolver) so cleartext never lives in YAML source. The earlier backends.sql.credentials.{password_env, password_file, password_ref} block was removed in favor of a single gateway-wide secret resolver (see commit c5e6e97).

Minimal config

A tool entry under mcp.capabilities.tools[] whose backend selects the SQL plugin via backend.kind: sql:

mcp:
  capabilities:
    tools:
      - name: get-order
        description: Fetch an order by ID.
        backend:
          kind: sql
          driver: postgres
          url: "postgres://mcpg:${env.ORDERS_DB_PASSWORD}@db.internal/orders?sslmode=require"
          query:
            sql: "SELECT id, total FROM orders WHERE id = :order_id"
            params: [order_id]
            row_mode: single

Per-entry kind: was dropped in Layout #4 Slice B2 — list membership (tools[] vs prompts[] vs resources[] vs resource_templates[]) carries the capability kind; only backend.kind selects the implementation.

Stored procedure

- name: settle-invoice
  description: Run the settle_invoice stored procedure.
  backend:
    kind: sql
    driver: postgres
    url: "postgres://mcpg:${env.ORDERS_DB_PASSWORD}@db.internal/orders"
    query:
      procedure: "orders.settle_invoice"
      params: [invoice_id, amount_cents]
      row_mode: affected_rows

The driver emits CALL orders.settle_invoice($1, $2) for Postgres or CALL orders.settle_invoice(?, ?) for MySQL. Procedures that return a result row can use row_mode: single.

Multi-tenant RLS (Postgres)

- name: list-tickets
  description: List tickets; tenant-scoped via Postgres RLS.
  backend:
    kind: sql
    driver: postgres
    url: "postgres://mcpg:${env.SUPPORT_DB_PASSWORD}@db.internal/support"
    query:
      sql: "SELECT id, title, status FROM tickets"
      row_mode: many
      max_rows: 100
    session_vars:
      # Values are parameter-bound via `set_config()`; identifier
      # safety is enforced at config parse (`is_safe_sql_identifier`).
      # A Postgres RLS policy keyed on
      # `current_setting('app.current_tenant')` now sees the
      # authenticated principal's tenant on every call.
      "app.current_tenant": "${identity.tenant}"

Row mode cheat sheet

row_mode	Returns	Use case
single	one JSON object keyed by column, or null	get_order_by_id
many	JSON array of objects (capped by max_rows)	list_open_tickets
scalar	first column of first row as a naked value	SELECT count(*)
affected_rows	{"rows_affected": N} from the driver	INSERT / UPDATE / DELETE
resource_contents	`{"contents": [{"uri", "text"	"blob", "mimeType"?}]}` envelope
stream	{"rows": [...], "next_cursor": null, "truncated": bool}	iterable SELECTs — keyset continuation

max_rows still applies to every read mode — oversized results are truncated at the row boundary and truncated: true surfaces in the response payload (for stream) or on _meta (for the other modes).

For the full config surface, YAML examples, metrics, and design rationale see the backends reference. This README stays deliberately short so it doesn't drift with every doc update.

Threat model: SQL injection

Parameter values always flow through sqlx's prepared-statement binding — never string-interpolated. That single rule prevents the classic injection vector regardless of what the caller sends in arguments.

Config-side fields that the plugin does inline into the statement text are defense-in-depth'd at register_profile:

Surface	Inlined where	Validator	Reject pattern
query.procedure	CALL <procedure>(...)	is_safe_sql_identifier	;, spaces, quotes, operators
query.sql / sql_file	executed verbatim	reject_multi_statement	unquoted ; between statements
session_vars keys	set_config('<key>', $1, true)	is_safe_sql_identifier	same as procedure
session_vars values	bound as $1	n/a	n/a — parameterized
param_exprs results	bound as parameters	n/a	n/a — parameterized
list_query.cursor_column	inlined into ORDER BY	is_safe_sql_identifier	same as procedure

Operator-supplied SQL bodies are trusted by design: operators declare their queries intentionally, and the backend can't second- guess legitimate DELETE/UPDATE intent. What the plugin can guarantee is that no config-side input reshapes an operator's statement. Multi-statement bodies are rejected because they defeat placeholder binding anyway; atomic multi-step work belongs in a kind: sql_tx pipeline step.

Privileged DDL is rejected at config parse. Statements leading with GRANT, REVOKE, or CREATE|ALTER|DROP {USER,ROLE, DATABASE,GROUP} are refused — the SQL backend is for application-scoped data access, not role/user/grant administration. Operators who need those must configure them on a separately-scoped admin backend gated behind governance.minimum_trust = "verified"

• an allowlist CEL. Regular schema DDL (CREATE TABLE, ALTER TABLE, DROP INDEX, …) still works. Leading comments (--, /* */) are stripped before the check so hiding -- innocent\nGRANT … doesn't bypass it.

PostgreSQL LISTEN/NOTIFY watch (postgres_listen_notify)

A resource watch that LISTENs on a channel. Every watch on the same url and channel shares one connection, open while one of them runs.

Without uri_match, every NOTIFY on the channel updates every resource watching it. With uri_match, a NOTIFY updates a resource only when one of the uris patterns, with {value} replaced by the payload's payload_field, is that resource's URI. A payload that is not JSON or lacks the field updates nothing.

watch:
  strategy:
    type: plugin
    kind: postgres_listen_notify
    url: "postgres://app:${env.DB_PW}@db/app"
    channel: imd_refs
    uri_match:
      payload_field: i                       # NOTIFY imd_refs, '{"i":"abc",...}'
      uris: ["imd://instructions/{value}"]   # updates imd://instructions/abc only

Field	Type	Description
url	string (required)	Connection URL.
channel	string (required)	ASCII letters, digits and _.
uri_match.payload_field	string	A top-level string or number field of the JSON payload.
uri_match.uris	string[]	URI patterns, each with {value}; exact match.

A watch that falls more than 1024 notifications behind reports one change.

PostgreSQL change data capture (postgres_cdc)

The postgres_cdc watch strategy reads every change a publication carries from a logical replication slot. It decodes the built-in pgoutput plugin through the SQL slot functions (pg_logical_slot_peek_binary_changes), so it needs no extension and no replication connection, and works on managed PostgreSQL (RDS, Aurora, Cloud SQL, Azure, Supabase, Neon).

mcp:
  capabilities:
    events:
      - name: orders.changed
        description: A row of the orders table was inserted, updated or deleted.
        source:
          watch:
            strategy:
              type: plugin
              kind: postgres_cdc
              url: "postgres://mcpg_cdc:${env.PG_CDC_PASSWORD}@db.internal/shop?sslmode=require"
              publication: mcpg_orders
              tables: [public.orders]
            event_type: sql.row.changed

Field	Default	Notes
url	—	postgres://, with its password and sslmode; resolved before the watch starts and never logged.
publication	—	The publication whose tables are read.
tables	[]	Create the publication FOR TABLE these when it is missing; without them a missing publication is an error.
slot	derived from the source	The replication slot, created when missing.
poll_interval_ms	1000	How often the slot is read when it had nothing new (100 to 20000).
max_in_flight, ack_timeout_ms	256, 60000	Changes out at a time, and how long one waits for the gateway before it is read again.

Each insert, update or delete is a sql.row.changed event: op, schema, table, row (the row after the change; for a delete the old key, or the old row under REPLICA IDENTITY FULL), old (an update's old row, when the replica identity sends it), unchanged (TOASTed columns the change did not send), lsn, xid and commitTime. Integers, floats, booleans and json/jsonb are JSON values; numeric and other types are PostgreSQL's text. A TRUNCATE is sql.table.truncated.

Delivery is at-least-once, in commit order: the slot advances (pg_replication_slot_advance) only once the gateway has stored every change of a transaction and of every transaction before it, and a change it did not take is read again under the same slot/lsn event id.

Requirements and care:

• wal_level = logical on the server, and a role with replication rights (REPLICATION, rds_replication, cloudsqlsuperuser, …) that may create the slot and, with tables, the publication.
• A slot keeps WAL on the server until it advances. Drop the slot of a source you remove (SELECT pg_drop_replication_slot('…')), and consider max_slot_wal_keep_size as a guard.
• A slot that is gone when the source restarts (dropped, or lost in a failover) is created again, and the event log records a gap: what changed in between is not known.

Environment expectations

Integration tests look for the following (Linux only):

• POSTGRES_TEST_URL — e.g. postgres://mcpg:mcpg@localhost/mcpg_test
• MYSQL_TEST_URL — e.g. mysql://mcpg:mcpg@127.0.0.1/mcpg_test

Both are set by the SQL / postgres and SQL / mysql rows of .github/workflows/integration.yml, which also set MCPG_REQUIRE_DB=1 and run postgres with wal_level = logical. Without the env vars a test skips with a note, unless MCPG_REQUIRE_DB is set, which makes the skip a failure. SQLite tests always run (in-memory). The cdc_postgres suite also needs wal_level = logical, and skips on a server without it unless MCPG_REQUIRE_DB is set:

docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=mcpg postgres:16-alpine \
  postgres -c wal_level=logical
POSTGRES_TEST_URL=postgres://postgres:mcpg@127.0.0.1:5432/postgres \
  cargo test -p mcpg-plugin-backend-sql --test cdc_postgres

Benches

Criterion benches cover the hot paths (sql_backend_hot_paths). They live in the development tree and are not part of the published crate, so measure against your own workload rather than a shipped baseline: bench numbers taken on shared CI runners are too noisy to assert thresholds against.

Build

cargo build -p mcpg-plugin-backend-sql --features cdylib-export --release   # → target/release/libmcpg_plugin_backend_sql.so

The default build enables all three drivers (postgres, mysql, sqlite); cloud-DB auth schemes (sql-rds-iam, sql-azure-ad, sql-gcp-iam, sql-aurora-failover) are opt-in Cargo features.

Sign & load (production)

Sign the artifact, pin/verify via the entry's signature: block, and honour revocations — see plugin security.

References

• Plugin system overview: apps/gateway/docs/plugins.md
• Full config reference: apps/gateway/config.example.yaml
• Operator reference: apps/gateway/docs/backends.md (SQL section)
• Operator cookbook: apps/gateway/docs/sql/cookbook.md — 26 worked recipes
• Migration guide: apps/gateway/docs/sql/migration.md — REST-wrapped DB → direct SQL backend
• Troubleshooting: apps/gateway/docs/sql/troubleshooting.md — failure modes + fixes
• Samples: examples/26-sql-sqlite-todos, 27-sql-dynamic-resource-listings, 28-sql-pipeline-tx, 29-sql-await-job
• Trait surface: libs/plugin-api/src/backend.rs
• Sibling plugins: libs/plugins/backends/nats, libs/plugins/backends/kafka