Filtering List Endpoints by Permission

Single-object endpoints are easy to authorise: load the record, check it, return it or refuse. List endpoints are where authorization and pagination collide, and the naive approaches produce either leaks or unusable pages. This page is part of the middleware patterns for permission validation guide.

Why Post-Filtering Breaks

The instinctive approach — fetch a page, then drop the rows the caller may not see — is wrong in three separate ways, and only the first is obvious.

Filter in the Query

The correct shape is to express the permission as a predicate the database applies, so pagination, counting and sorting all operate on the visible set.

// The permission predicate is part of the query, not a step after it.
export async function listInvoices(ctx: RequestContext, page: Cursor) {
  return db.invoices
    .where("tenant_id", ctx.tenantId)                    // always, from the session
    .where((q) => visibilityPredicate(q, ctx))           // one shared definition
    .orderBy("created_at", "desc")
    .after(page.cursor)
    .limit(page.size);
}

Define visibilityPredicate once and use it for the list, the count, the export and any aggregate. The bug that keeps recurring is a list endpoint and a count endpoint with slightly different rules, which produces a total that does not match what the user can page through — and occasionally an export that includes rows the list never showed.

Where the rule involves ownership or sharing, join rather than filtering in application code. A permission that says “invoices I own, plus invoices for projects I am a member of” is a join against the membership table, and expressing it in SQL keeps it index-friendly. Expressing it as a fetch-then-filter turns an indexed query into a scan.

When the Rule Cannot Be a Predicate

Some models cannot be reduced to a WHERE clause — most notably relationship-based systems where visibility comes from a graph traversal in another service.

Prefer the first when the visible set is bounded — a user with access to dozens or hundreds of projects — because it keeps the database doing the paging. Prefer the second when the set is large or unbounded, and accept that a page may need refilling; overfetch by a sensible factor, batch the checks, and stop when the page is full. The tuple-store version of this is covered in listing objects a user can access at scale.

Counts, Sorting and Search

Counting is where permission filtering becomes expensive, because an exact count over a filtered set cannot use the shortcuts an unfiltered count can. Three pragmatic options: return an exact count when the filtered set is small, return an approximate count with a “more than” indicator when it is large, or return no count at all and offer only “next page”. Most interfaces need far less precision than they ask for.

Sorting must happen after filtering, in the database, or the order the user sees is the order of an arbitrary subset. That sounds obvious and is violated regularly by code that sorts a fetched page in application memory.

Search indexes need the same treatment as the database. An index built without tenant and permission fields will happily return documents the caller cannot see, and post-filtering search results reintroduces every problem above — plus a relevance ranking computed over records that were never visible.

Testing It

Two tests cover the important properties. A two-user fixture with overlapping data asserts that each user’s list contains exactly their visible rows, that the count matches the number of rows they can actually page through, and that paging to the end yields no duplicates or gaps. A negative test requests a page with a cursor derived from another user’s data and asserts it does not leak.

Add an assertion that the list, count and export endpoints produce consistent totals for the same caller. That single check catches the most common regression in this area: someone adds a filter to one path and not the others.

Keeping the Predicate in One Place

The recurring failure in this area is not a wrong rule but several slightly different copies of the right one. A list endpoint, a count endpoint, a CSV export, a search handler and a background report all need the same visibility logic, and each is usually written by a different person at a different time.

Define the predicate once as a function that takes the request context and returns a query fragment, and make every read path compose it. If your data layer cannot express that, define it as a named database view or a reusable common table expression — the mechanism matters less than the fact that there is exactly one definition to review and to change.

Then add the consistency test: for the same caller and the same filters, assert that the list total, the count endpoint and the export row count agree. It is a three-line test that catches an entire class of regression, including the one where somebody adds a new visibility rule to the list query and forgets the export that finance uses once a quarter.

Finally, treat any read path that bypasses the shared predicate as a defect rather than an optimisation. Hand-written reporting queries are the usual offenders, and they are also the queries most likely to be run against production data by someone in a hurry.

Performance Considerations

Permission predicates change query plans, so they need to be part of your indexing strategy rather than an afterthought.

Lead composite indexes with the columns the predicate uses — tenant first in a multi-tenant system, then owner or status — because those are applied to every query. An index designed for the sort order alone will still be scanned in full once the predicate narrows the set, and the difference becomes visible exactly when a customer’s data grows.

Watch out for predicates that cannot use an index at all: a function applied to a column, a subquery evaluated per row, or a permission expressed as a NOT IN over a large set. Each turns a bounded lookup into a scan, and each is easy to introduce while making the rule read more clearly.

Where the visible set is very large, cursor pagination on an indexed sort column is the only approach that stays fast — offset pagination degrades linearly and does so quietly, so the complaint arrives from the customer with the most data rather than from your tests.

Frequently Asked Questions

Related

• Middleware patterns for permission validation — where the shared request context comes from.
• Preventing privilege escalation in API endpoints — the single-object counterpart of this problem.
• Multi-tenant authorization and data isolation — the tenant predicate every list query inherits.