Documentation/Access control/Your field-level permissions probably leak through ORDER BY
Your field-level permissions probably leak through ORDER BY
Field-level permissions that mask the API response do not protect the field. If a caller can still sort, filter or group by it, they reconstruct it.
- evergreen
- security
- rbac
- flagship
Overview
**Short answer:** masking a field in the API response does not protect it. If a caller can still use that field in
ORDER BY, WHERE, DISTINCT or GROUP BY, they can reconstruct its value — a request like ?order_by=-salary&limit=1 returns the highest-paid employee's *name* without ever returning a salary. This is an inference oracle. The fix is to move the permission decision out of the serializer and into the query planner, so the set of fields a caller may filter, sort, group and aggregate by is exactly the set they may see, computed once per caller.**When it applies:** your API accepts *caller-supplied field names* in sort, filter or aggregate parameters. If your sort parameter is an enum of three approved columns, you are structurally immune and can stop reading. If it takes arbitrary field names — what every generic query API does, ours included — read on.
(Disclosure: I work on Supero. The first two thirds apply to any stack. The attack works whether or not you believe the last third.)
---
Test your own API. Five minutes.
Before the argument, the experiment. Pick a field some role cannot see. As that role:
text
?order_by=-that_field&limit=1
?filter[that_field][gt]=<guess>&count=true
?distinct=that_field
?count_by=that_fieldSubstitute your own parameter names; every API spells these differently, ours included. If a parameter is simply ignored you will get a
200 that means nothing, so confirm the sort actually changed the order before you conclude anything.A
200 that genuinely honours the hidden field means you have an inference oracle, and the fix is not in your serializer. Go and run it; the rest explains why they work.---
The bug
salary is on employee. Managers see it; staff do not. You do the correct obvious thing: strip salary from the response for non-managers, and write a test asserting "salary" not in response.json(). It passes.Now I am a staff member with a valid token, asking something I am authorised to ask:
http
GET /api/v1/employee?order_by=-salary&limit=1You never send a salary. You send a name — the highest-paid person in the company.
The four vectors
| # | Vector | Request | Recovers |
|---|---|---|---|
| 1 | **Ordering** | ?order_by=-hidden&limit=1 | An argmax. Walk limit upward for the full ranking. |
| 2 | **Filtering** | ?filter[hidden][gt]=X&count=true | A comparison oracle over the visible set. |
| 3 | **Cardinality** | ?distinct=hidden | The value set, no rows attached. For a diagnosis code or plan tier, the value set *is* the secret. |
| 4 | **Grouping** | ?count_by=hidden | The histogram, and with one permitted filter, the joint distribution. |
Vector 2 needs a clarification that matters. Alone, it binary-searches an *order statistic over the visible set*: it gives you the maximum, not a named person's salary. To read a specific individual, conjoin an identifying predicate you are allowed to use, so the count collapses to 0 or 1:
text
?employee_id=42&filter[salary][gt]=X&count=trueBinary search then converges in log₂(range) requests. For a salary between 0 and 500,000 to the nearest dollar: nineteen.
A fifth deserves its own line, because it is where "we fixed it" fixes fail: **the raw query escape hatch.** If your API accepts a MongoDB pipeline, a JSON filter tree, or anything nestable, the guard must walk
$or, $and and $nor recursively. A guard that checks the top level and stops is not a guard. It is a speed bump with good documentation.---
This is old, and it has a CWE number
None of this is novel, so before the vendor section, the prior art:
- •**CWE-202** — Exposure of Sensitive Information Through Data Queries. This bug, by name.
- •**OWASP API Security Top 10, API3:2023** — Broken Object Property Level Authorization.
- •The **inference problem** in multi-level-secure database literature, worked over since the 1980s, and every k-anonymity paper since.
- •**Salesforce** shipped this at enterprise scale, and the fix took two attempts, which is the instructive part. Field-level security did not apply to SOQL
WHEREandORDER BY;WITH SECURITY_ENFORCEDchecks only theSELECTandFROMclauses, so the gap survived it, andSecurity.stripInaccessibleis response-side masking, the anti-pattern this article opens by demolishing.WITH USER_MODEis what actually closed it. If a platform that size needed three mechanisms, your serializer is not going to be enough either.
What changed is not the class. It is that we all started shipping APIs taking arbitrary field names in sort, filter and aggregate parameters, which made a forty-year-old attack cost nineteen HTTP requests.
Why it survives review
**The check and the leak live in different layers.** Permission logic runs in a serializer, a response middleware, a
to_dict(). By then the database has done the work: the ORDER BY executed, the row count is what it is. Masking the output cannot un-ring that bell, because the information was never in the output — it was in *which rows came back, and in what order*.Where the common stacks actually sit:
| Stack | Position |
|---|---|
| **Django REST Framework** | No field-permission primitive; teams build it in the serializer. DRF's own OrderingFilter derives valid keys from serializer fields when ordering_fields is unset, so it strips with you. The leak appears with ordering_fields = '__all__', or with django-filter, whose filterset_fields is a separate concern nothing reconciles. |
| **GraphQL** | Field auth (Amplify @auth, graphql-shield) resolves at resolution time, while the generated orderBy enum comes from the schema and can include fields the caller cannot resolve. |
| **Postgres — RLS** | Row-level. Decides which rows; says nothing about a column within rows you may see. |
| **Postgres — column GRANTs** | **Already correct.** GRANT SELECT (col) is checked against every column *referenced anywhere in the query*, so SELECT name FROM employee ORDER BY salary is refused — permission denied for table employee; Postgres does not name the column. Shipped in 8.4, so this has been the right answer since 2009. |
| **PostgREST** | No permission system of its own; it delegates to GRANTs and RLS. With column grants, correct by construction. |
| **Hasura** | Generates a per-role schema, so a column outside a role's select permission is absent from that role's order_by and bool_exp input types, and allow_aggregations is a separate per-role flag. |
The last three rows deserve a flat statement, because a comparison that insinuates is worthless: **the database-boundary tools mostly get this right.** This is not a property of Postgres, PostgREST or Hasura. The leak appears when you mask fields in *your own application layer*: a Node or Next.js API in front of the database, built the moment JWT claims and per-tenant logic arrive, with the query surface left ungoverned. That is the common case, and the one to check.
---
What a fix requires
Not subtle. Invasive, which is why few systems do it.
**Move the decision from the response layer into the query planner.** Not "strip it on the way out." Refuse to plan a query that reads it on the way in. For a given caller, the fields permitted in
WHERE, ORDER BY, DISTINCT, GROUP BY and every aggregate argument must be **the same set** permitted in the response. Computed once, per caller, per request. Computed twice, they drift, and the drift is the vulnerability.Three properties separate a fix from the appearance of one:
1. **Same key, both paths.** Two lists "kept in sync" are two lists that diverge on a Friday. 2. **Fail closed on an unresolvable policy**, with
403. Not 503: SDKs and proxies retry it, turning a policy-store blip into a thundering herd. (We return 503 on that path today. On reflection that is wrong, and I have raised it internally.) 3. **Recurse into everything nestable.** $or is where guards fail.**The mitigations that require buying nothing.** Whitelist sort keys to an enum. Don't expose
count on filtered queries. Enforce a minimum bucket size on aggregates. Rate-limit. The statistical-disclosure literature has been answering this since before I was writing software, and presenting "mask the response" or "gate the planner" as the only two options would be a vendor-shaped lie.Two places every guard I have seen breaks: **relationship traversal** (
?filter[manager.salary][gt]=X) and **expressions** (order_by=salary+0, or a generated column derived from a hidden one). A name-based guard loses to any expression grammar. If your API accepts only bare field names, that constraint is what makes the guard tractable. Say so; it is a strength.---
How we do it, and what that is worth
Supero enforces this on the request path, before the query is built. The check runs at the route layer, against the same per-caller hidden set the response strip uses. It is not inside a database planner the way Postgres column grants are: one decision, two enforcement points, both sitting above the storage engine. Partial coverage is the bug this whole article is about, so here is our own coverage, vector by vector.
**On our own storage, today:** a field hidden from a caller cannot be
distinct-ed, count_by-ed, group_by-ed or aggregated over, including through a raw MongoDB pipeline. Vectors 3 and 4 are closed.**On a live external database:** the same refusal additionally covers filter, sort and projection, keyed to the same per-caller resolution as the response strip and recursing through nested boolean filters.
**The residual, plainly. Vectors 1 and 2,
order_by and filter over a hidden field, were open on our own storage until 23 September 2026.** They were guarded on the live path only. Run this article's opening request against a stock Supero app before that date and you got a 200 and the argmax row.The detail worth keeping is how long it stayed open after the fix existed. The guard that refuses those requests was written, tested and deployed on 31 August. It shipped in **monitor** mode, which records that it would have denied the read and then returns the rows anyway. Monitor is the right first setting for a guard that has never run on live traffic, and it is also a guard that is off. For three weeks ours read as done in every place a person would look: merged, deployed, tests green, an entry in the changelog.
It enforces now. Since 23 September a sort or filter on a field the caller cannot read returns
400. The reads that should still work do: the same request from a role that *can* read the field returns 200 with the field, and sorting on a column nobody restricted is untouched. Nine claims, two roles, four requests, and the asc-versus-desc control below.Confirm it rather than trusting the paragraph. Sort by a hidden field as a role that cannot read it and you now get a
400. A column nobody restricted still returns your rows. Before the flip, the tell was that ascending came back as the exact reverse of descending, which is the whole oracle in two requests.That is the drift this article is about, caught in our own system: one decision, enforced at two different widths on two paths, with the tighter one behind a switch that logged instead of refusing. I would rather you read it here than find it with the five-minute test, which you should still run.
One second-order check worth running on your own system, since this article is about not fooling yourself: **your refusals are themselves a channel.** If a hidden field returns
400 and a field that does not exist returns 200, probing enumerates your schema. Whether that matters depends on what you already disclose. If your client is told which fields are hidden so it can grey out a column, the refusal leaks nothing new. Ours does disclose that, so the asymmetry is moot for us; yours may not. It is a two-request test and almost nobody runs it.On the live path the guard applies to the SQL we are about to generate against a customer's own server, in their VPC, where no column or row data is copied into us.
The evidence, and where it runs out
The guard logic is exhaustively **unit**-tested, in
test_live_field_guard.py and test_live_field_guard_routes.py in platform-core.The aggregate coverage on our own storage is asserted end-to-end by
test_53_access_policy_e2e, which checks that distinct, count_by, stats and a raw $group pipeline over a hidden field all return 400 for a tenant_user, that an admin bypasses, and that a non-hidden field is not over-blocked. That is the one to run.One result from a running application rather than a test run, since those are rarer and worth more: on an app with a real policy configured,
tenant_admin sees fraud_score in 8 of 9 rows and tenant_user sees it in 0 of 9. Read that narrowly. It is field redaction between two roles **inside one tenant**. It says nothing about the wall between tenants, and all but two of our showcase apps are single-tenant, so nobody should be citing it as isolation evidence. Including me.The two **live-path** end-to-end tests currently **skip** in our SaaS configuration. Live-binding detection lists connectors with the caller's own token, so a non-admin
tenant_user gets an empty binding map, is served the local store, and never reaches a confirmed-live read. They skip rather than falsely pass, which is the right engineering call, and it means **I cannot invite you to run those two and see green.** (Their live source is public Rfam **MySQL**. An earlier draft of this article said Oracle. That was a misreading of a filename in which "oracle" means *inference oracle*.)The tension in this article, named before you name it
I wrote above that a fail-open default in a permission system is a bug with a deployment date. That principle indicts us. **Supero's access policy is permissive by default:** an entity with no policy attached is readable by the non-viewer roles in that tenant, and the admin tier bypasses access policies entirely. Generated apps do ship policies (I said otherwise in an earlier draft, and it was wrong), but some entities carry
default_access="full", so read the generated policy set rather than assuming it. The field axis shipped first because it leaks without anyone making a mistake; the entity axis requires you to set a policy, and we should be generating a deny-by-default scaffold for you. We do not yet.There is a fail-open branch in the guard itself: with no session or domain on the request it returns allow. I checked whether anything reaches it. Nothing does. Every read handler returns
401 before the guard runs (22 such guards), and a session cannot be minted without a domain: it is a required field that, unlike project and tenant, is not backfilled with a default. So it is unreachable defence-in-depth rather than a live hole. I am telling you it exists anyway, because "fails closed on an unresolvable policy" is the accurate sentence and "fail-closed is the invariant" is not, and you should be able to tell those apart in anyone's marketing.And we have write-side audit logging but not read-side. We can tell you who wrote a record, not who read one.
---
Frequently asked questions
Is this SQL injection?
No. Every request is well-formed, parameterised and fully authorised. The caller uses documented parameters exactly as intended, which is what makes it hard to spot.
Does Postgres row-level security fix it?
No, and it is not meant to: RLS decides which rows. Postgres **column-level GRANTs** do fix it, and are checked against every column referenced in the query, including in
ORDER BY.Can I fix it by removing sort and filter parameters?
A real mitigation and often the right first move. Not a fix: it removes the capability rather than governing it, and aggregate endpoints usually survive the cull and keep leaking.
Does an allowlist of sortable fields work?
Only if computed **per caller** from the same decision that masks the response. A static allowlist is global, so it either blocks managers or lets staff through.
How many requests does the binary search take?
log₂ of the value range, once you have conjoined an identifying predicate. Nineteen for a salary in a 0–500,000 range.
What should I log to detect it?
Queries referencing a field the caller cannot read. Enforce in the planner and you get that signal free: the refusal is the log line.
---
What to do next
**Run the four requests against your own API.** That is the point of this page.
**Do not run them against our public demos.** Those are showcase apps, all but two of them single-tenant, with no hidden-field policies configured, so vectors 1 and 2 will return
200, for the reason stated above rather than because the guard failed. Testing a guard against a system with nothing to guard proves nothing in either direction, and I would rather say that than let you conclude something false in either direction.What is worth doing, if this is your problem: get in touch and ask for the aggregate vectors against a schema with a real hidden field, or read
test_53_access_policy_e2e, which is the assertion that actually runs. If you want to see what the platform *generates* rather than how it enforces, the showcase estate is at api.supero.dev/api/v1/public/showcase.**See it enforced, not asserted.** One showcase app — the insurance demo
sentinel — *does* ship a hidden-field policy, and the repo carries a verify/ suite that runs the exact comparison above against it in about ten seconds, no signup: github.com/supero-platform/supero-apps. It is built to abort rather than report a pass if the privileged role cannot see the field it is checking — so a green result means the guard held, not that the column was empty. That suite also probes the type / obj_type key alias that this class of bug hides behind; a request-key alias skipped these same controls on our live demo as recently as September 2026, which is the sharpest real example of the point this article makes.Choosing a platform rather than fixing one: Bubble · Retool · Supabase · Lovable, Bolt and v0. The decisions behind it: the multi-tenant SaaS checklist.
On this page