coilyco-flight-deck/umbra

Watch

sql grants: named statements with bound parameters, so a database is reachable without a reachable-by-construction surface #308

New issue

Closed

opened 2026-08-19 08:40:48 +00:00 by coilyco-ops · 1 comment

coilyco-ops commented

2026-08-19 08:40:48 +00:00

Member

Filed by Angie (engineer, claude seat) on Kai's call, as the umbra half of mcp-beaver#71. That issue is an empty-bodied autonomy/async-consult on mcp-beaver; Kai's call is to teach umbra SQL, so the design questions are settled here.

The gap

Neither engine can reach a database. Measured on main: grep -rn "database/sql\|pgx\|sqlite\|mysql" over umbra returns nothing, and the only transport in mcp-beaver's runtime is net/http. Every database Kai runs is reachable only if something already fronts it with HTTP.

The deny-by-absence problem, and the answer

The product claim is "everything you declared works, nothing else is reachable". A SQL surface can be built two ways and only one of them survives that claim:

Caller-supplied SQL, which would be this engine's first reachable-by-construction surface. A tool taking a query string reaches every table the credential can see. Rejected.
Named statements with bound parameters. The guardfile authors each statement. The caller supplies only declared parameters, which bind through database/sql placeholders and are never interpolated into the text. A statement nobody wrote does not exist.

The second is the same shape graphql (#306) just took for documents, and the same shape grants have always had: the authored thing is fixed, the caller fills declared holes.

Shape

wrap ward mcp analytics {
    database pgx { value env "DATABASE_URL" }

    can list orders {
        sql {
            statement "SELECT id, total FROM orders WHERE customer = $1 ORDER BY created_at DESC LIMIT $2"
            param "customer" type="string" required=#true
            param "limit" type="integer" minimum=1 maximum=100
            max-rows "200"
        }
    }
}

Zero new dependencies, which is the load-bearing design call

umbra names the driver and opens it through stdlib database/sql. It imports no driver. The consumer binary registers one for its side effect (_ "github.com/jackc/pgx/v5/stdlib"), exactly as any database/sql program does.

That keeps umbra policy-free and driver-agnostic, matching the repo boundary that no consumer-specific type or default leaks in, and it means adding MySQL or SQLite later is a consumer import rather than a change here. Building the runtime checks the named driver is actually registered and fails closed with a message naming it, so a missing import is a startup error rather than a first-call surprise.

Where it lives

In opcore, as a sql grant child and a Descriptor.SQL, not a new top-level surface. opcore is already the inline grammar plus descriptor core rather than a purely HTTP package: Descriptor.Proxy is a second execution kind with a completely different path. A consumer that already drives ParseInline and Operation.Execute then serves SQL tools with no change.

Safety rules, all fail-closed at build

The statement is authored and never appears in the input schema, as with a graphql document.
Parameters bind as placeholders. Nothing is interpolated into statement text, ever.
One statement per grant. A ; separating statements is refused, so a sloppy authored statement cannot become a stacked query.
Placeholder count must equal the declared parameter count, and the two styles ($N and ?) cannot be mixed.
A read verb must carry a read statement. can list with DELETE is refused, so the served verb cannot lie about what it does.
max-rows bounds the result, defaulting rather than ceilinged into, and the response states truncation honestly rather than implying it read everything.

Acceptance

A guardfile expresses a named statement with typed, bound parameters.
The statement is not caller-influenceable and never enters the tool schema.
Parameters surface as individually typed inputs with bounds.
An undeclared input cannot reach the database.
A missing driver, a mixed or miscounted placeholder set, a multi-statement string, and a verb/statement mismatch are all build or startup errors.
Verified end to end against a real database/sql driver, not only a stub.

Refs mcp-beaver#71, #306.

**Filed by Angie (engineer, `claude` seat) on Kai's call**, as the umbra half of `mcp-beaver#71`. That issue is an empty-bodied `autonomy/async-consult` on mcp-beaver; Kai's call is to teach umbra SQL, so the design questions are settled here. ## The gap Neither engine can reach a database. Measured on `main`: `grep -rn "database/sql\|pgx\|sqlite\|mysql"` over umbra returns nothing, and the only transport in mcp-beaver's runtime is `net/http`. Every database Kai runs is reachable only if something already fronts it with HTTP. ## The deny-by-absence problem, and the answer The product claim is "everything you declared works, nothing else is reachable". A SQL surface can be built two ways and only one of them survives that claim: * **Caller-supplied SQL**, which would be this engine's first reachable-by-construction surface. A tool taking a query string reaches every table the credential can see. Rejected. * **Named statements with bound parameters.** The guardfile authors each statement. The caller supplies only declared parameters, which bind through `database/sql` placeholders and are never interpolated into the text. A statement nobody wrote does not exist. The second is the same shape `graphql` (#306) just took for documents, and the same shape grants have always had: the authored thing is fixed, the caller fills declared holes. ## Shape ```kdl wrap ward mcp analytics { database pgx { value env "DATABASE_URL" } can list orders { sql { statement "SELECT id, total FROM orders WHERE customer = $1 ORDER BY created_at DESC LIMIT $2" param "customer" type="string" required=#true param "limit" type="integer" minimum=1 maximum=100 max-rows "200" } } } ``` ## Zero new dependencies, which is the load-bearing design call umbra names the driver and opens it through stdlib `database/sql`. **It imports no driver.** The consumer binary registers one for its side effect (`_ "github.com/jackc/pgx/v5/stdlib"`), exactly as any `database/sql` program does. That keeps umbra policy-free and driver-agnostic, matching the repo boundary that no consumer-specific type or default leaks in, and it means adding MySQL or SQLite later is a consumer import rather than a change here. Building the runtime checks the named driver is actually registered and fails closed with a message naming it, so a missing import is a startup error rather than a first-call surprise. ## Where it lives In `opcore`, as a `sql` grant child and a `Descriptor.SQL`, not a new top-level surface. opcore is already the inline grammar plus descriptor core rather than a purely HTTP package: `Descriptor.Proxy` is a second execution kind with a completely different path. A consumer that already drives `ParseInline` and `Operation.Execute` then serves SQL tools with no change. ## Safety rules, all fail-closed at build * The statement is authored and never appears in the input schema, as with a graphql document. * Parameters bind as placeholders. Nothing is interpolated into statement text, ever. * One statement per grant. A `;` separating statements is refused, so a sloppy authored statement cannot become a stacked query. * Placeholder count must equal the declared parameter count, and the two styles (`$N` and `?`) cannot be mixed. * A read verb must carry a read statement. `can list` with `DELETE` is refused, so the served verb cannot lie about what it does. * `max-rows` bounds the result, defaulting rather than ceilinged into, and the response states truncation honestly rather than implying it read everything. ## Acceptance * A guardfile expresses a named statement with typed, bound parameters. * The statement is not caller-influenceable and never enters the tool schema. * Parameters surface as individually typed inputs with bounds. * An undeclared input cannot reach the database. * A missing driver, a mixed or miscounted placeholder set, a multi-statement string, and a verb/statement mismatch are all build or startup errors. * Verified end to end against a real `database/sql` driver, not only a stub. Refs `mcp-beaver#71`, `#306`.

coilyco-ops referenced this issue from a commit

2026-08-19 08:50:59 +00:00

feat(opcore): sql grants, named statements with bound parameters

coilyco-ops closed this issue

2026-08-19 08:50:59 +00:00

coilyco-ops commented

2026-08-19 08:51:36 +00:00

Author

Member

Landed on main as 8b098f7. Built by Angie (engineer, claude seat).

Acceptance

A named statement with typed, bound parameters. database <driver> { value ... } at wrap level, sql { statement ...; param ...; max-rows ... } per grant.
The statement is not caller-influenceable and never enters the schema. The schema carries exactly the declared params and nothing else.
Parameters surface as individually typed inputs with bounds, and bind positionally as placeholders.
An undeclared input cannot reach the database. Only declared names are read, so an extra key is inert.
A missing driver, a mixed or miscounted placeholder set, a multi-statement string, and a verb/statement mismatch are all build or startup errors.
Verified end to end against a real driver, not only a stub.

Verified against real SQLite

modernc.org/sqlite in a throwaway module, so umbra's own dependency set is untouched. This exercised the ? placeholder style, real binding, and real type mapping:

schema for list_orders: { "customer": {"type":"string"} }   // statement absent
bounded read (max-rows 2 of 3 matching): 2 rows, "truncated": true
customer = "ada'; DROP TABLE orders; --"  ->  0 rows
orders row count afterwards: 4            // table intact
write -> {"rows_affected": 1}

The in-tree tests use a recording database/sql driver that captures what the engine handed it, so "nothing is interpolated" is asserted against the actual statement and args rather than asserted about.

The dependency call held

umbra imports no driver. It opens the named driver through stdlib database/sql; the consumer registers one. Zero new dependencies in go.mod, and adding Postgres is _ "github.com/jackc/pgx/v5/stdlib" in the consumer. An unregistered driver errors with its name and the list of registered ones.

I deliberately did not check driver registration at parse time. It would have made lint fail on a valid SQL guardfile in any binary that does not link a driver, which is exactly what mcp-beaver lint is.

Beyond the filed scope

A CTE hiding a mutation is caught. WITH gone AS (DELETE ... RETURNING id) SELECT id FROM gone opens with WITH, so a naive leading-keyword check reads it as a read and would let can list serve a delete. The scanner walks for a write keyword at code position.
auth is no longer required for a wrap serving only sql grants. The DSN carries the credential and there is no HTTP upstream to authenticate to. Previously validate() refused such a wrap outright.
Text renders as a string. Drivers return []byte for text, which would serialize as base64 and be unreadable to a model.

Not done, and why

No verification against Postgres. The docker daemon is down on this host, and the running Postgres instances are in Kai's sirens namespaces. Connecting an agent to a live database is a DevOps action rather than an engineering one, and nothing about this change needed it: SQLite exercised the same database/sql path, and the $N style is covered by the in-tree driver. Worth one real Postgres run before a guardfile ships against one.

make test, make lint (0 issues), and pre-commit run --all-files all clean.

**Landed on `main` as `8b098f7`.** Built by Angie (engineer, `claude` seat). ## Acceptance * **A named statement with typed, bound parameters.** `database <driver> { value ... }` at wrap level, `sql { statement ...; param ...; max-rows ... }` per grant. * **The statement is not caller-influenceable and never enters the schema.** The schema carries exactly the declared params and nothing else. * **Parameters surface as individually typed inputs with bounds**, and bind positionally as placeholders. * **An undeclared input cannot reach the database.** Only declared names are read, so an extra key is inert. * **A missing driver, a mixed or miscounted placeholder set, a multi-statement string, and a verb/statement mismatch are all build or startup errors.** * **Verified end to end against a real driver**, not only a stub. ## Verified against real SQLite `modernc.org/sqlite` in a throwaway module, so umbra's own dependency set is untouched. This exercised the `?` placeholder style, real binding, and real type mapping: ``` schema for list_orders: { "customer": {"type":"string"} } // statement absent bounded read (max-rows 2 of 3 matching): 2 rows, "truncated": true customer = "ada'; DROP TABLE orders; --" -> 0 rows orders row count afterwards: 4 // table intact write -> {"rows_affected": 1} ``` The in-tree tests use a recording `database/sql` driver that captures what the engine handed it, so "nothing is interpolated" is asserted against the actual statement and args rather than asserted about. ## The dependency call held **umbra imports no driver.** It opens the named driver through stdlib `database/sql`; the consumer registers one. Zero new dependencies in `go.mod`, and adding Postgres is `_ "github.com/jackc/pgx/v5/stdlib"` in the consumer. An unregistered driver errors with its name and the list of registered ones. I deliberately did **not** check driver registration at parse time. It would have made `lint` fail on a valid SQL guardfile in any binary that does not link a driver, which is exactly what `mcp-beaver lint` is. ## Beyond the filed scope * **A CTE hiding a mutation is caught.** `WITH gone AS (DELETE ... RETURNING id) SELECT id FROM gone` opens with `WITH`, so a naive leading-keyword check reads it as a read and would let `can list` serve a delete. The scanner walks for a write keyword at code position. * **`auth` is no longer required for a wrap serving only sql grants.** The DSN carries the credential and there is no HTTP upstream to authenticate to. Previously `validate()` refused such a wrap outright. * **Text renders as a string.** Drivers return `[]byte` for text, which would serialize as base64 and be unreadable to a model. ## Not done, and why **No verification against Postgres.** The docker daemon is down on this host, and the running Postgres instances are in Kai's sirens namespaces. Connecting an agent to a live database is a DevOps action rather than an engineering one, and nothing about this change needed it: SQLite exercised the same `database/sql` path, and the `$N` style is covered by the in-tree driver. Worth one real Postgres run before a guardfile ships against one. `make test`, `make lint` (0 issues), and `pre-commit run --all-files` all clean.

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

coilyco-flight-deck/umbra#308

No description provided.

Rows
Columns