Build1 publisher3 min readPublished
Table-level role restrictions leave analyst role unable to answer five of thirteen questions
A dev.to post counts the questions whose correct answer needs a table the caller may not read. On a 42-object demo schema with an ordinary role split, the analyst's share is 38.5% and the CFO's is zero.
The Engineer · Build desk
What happened
- When row-level security strips every row from a correct query, the application gets an empty result set and reports no records; there is no exception to catch, so no alert fires and nothing lands in the logs.
- On a 42-object demo schema with thirteen labelled questions, five of the analyst's questions need a table the analyst may not read, a silent-denial rate of 38.5%, while the cfo role scores 0.0%.
- The rate is computed from gold question-to-table labels of the kind Spider and BIRD already publish, with no model run and no SQL executed.
- A full-schema pipeline detected none of the ten blocked cases before SQL ran; a pipeline that scoped the schema by caller identity detected all ten.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Runtime monitoring cannot buy its way out of this one, because the signals a detector would key on are the same as those of an honest empty result. The check has to move ahead of query generation.
- decision The design choice is now explicit: hand the model the whole schema, or hand it the caller's slice. The second converts a confidently wrong answer into a refusal the user can escalate.
- exposure Whoever acts on "no unpaid invoices" carries the error alone, because the query succeeded and there is nothing in the logs to review afterwards.
- capability Since no model runs and no query executes, the rate can be recomputed on every schema or role change without paying for inference.
Thirteen labelled questions is the entire denominator, so one question is worth 7.7 points of the rate [1]. The analyst's 38.5% is five questions on a 42-object demo schema [9][8]. Cross the four callers with the same thirteen questions and you get 52 caller-question pairs, ten of them unanswerable, or 19.2% [3]. The ten in "0 of 10" and "10 of 10" are those pairs summed: five for the analyst, one for finance, four for hr, none for the cfo [2].
The scoped pipeline's 10 of 10 is close to a definition. A question counts as unanswerable when the tables its correct answer needs include at least one the caller may not read [4], and a caller-scoped pipeline notices exactly that, because the table is not in the set it was shown [11]. Detection follows from the construction, not from anything a model did. The figure worth testing on another schema is the reachability one: probe questions built from each restricted table's own name, hint and description put all five restricted tables at rank 1 in an unscoped selection [14][15].
The post is explicit that the rate is a property of the schema, the role model and the question mix, and not of whichever model is wired up that week [7]. The demo's role model gives finance tables to a finance role and salary to payroll, and leaves everything else open [8]. That combination produces the spread. On the same schema and the same thirteen questions the cfo row is 0.0% [9]. If your restricted tables are the ones nobody asks about, your rate is near zero and the number is honest.
The instrument failed before the measurement did. The first reachability run reported 0 of 5 tables reachable, with clean output and total confidence, because it was run with principal=None, and a caller with no roles is denied, since absence of a role is absence of permission [17]. The probe removed exactly the tables it was looking for [17]. "A measurement whose apparatus deletes the thing being measured will report zero, every time, with no error," the author wrote on dev.to [18]. The post notes this is the same shape as the bug the tool exists to find [19]. There is now a named regression test, and every test in the suite is paired: one case where the metric must be zero, one where it must not, so the metric can be shown to move at all [20].
Shops that do not have gold question-to-table labels can run the probe instead, which the post offers because most databases do not have the labels [21][6]. Both checks run before any SQL is generated. In a full-schema pipeline, which the post says is what nearly every text-to-SQL stack does, the permission check happens after generation [12][13].
What to watch
- Whether the same metric, run against Spider or BIRD labels and a production role model, lands anywhere near 38.5%.
- Whether any text-to-SQL framework makes caller-scoped schema retrieval its default, given that the post says nearly every stack hands the model the whole schema.
- Whether the tool, the 42-object schema and the thirteen labelled questions get published so the counts can be checked.