API lists: `limit` has no upper bound, so one call can shape a whole account inside the write lock #434

New issue

Closed

opened 2026-10-01 10:46:40 +00:00 by tiagoagueda · 1 comment

tiagoagueda commented

2026-10-01 10:46:40 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the API's pagination and the SQLite transaction settings.

Checked again on 2026-10-01 against main at 0a5cad587: still present. The list in api/api.py is now at api/api.py:632; api/paging.py, the routers and the settings have not moved.

Every paginated API list accepts any limit: Page extends django-ninja's LimitOffsetPagination, whose input is limit: int = Field(100, ge=1), and no NINJA_PAGINATION_MAX_LIMIT is set, so ninja's ceiling is infinity. #230 moved row shaping after the cut so that a page's work is one page's work, but ?limit=1000000 makes the page the whole account. The call runs under ATOMIC_REQUESTS, and on SQLite every transaction is IMMEDIATE, so the instance's write lock is held while every row is loaded, shaped and serialised.

Where: api/paging.py:45-62 (Page, inheriting ninja's unbounded Input); config/settings/base.py:140 (ATOMIC_REQUESTS, and no NINJA_PAGINATION_MAX_LIMIT anywhere in config/); config/sqlite.py:49 ("transaction_mode": "IMMEDIATE"). The eleven @paginate(Page, ...) lists in api/api.py:630 and api/routers/ all inherit it. api/routers/search.py:17 does bound its own limit (le=50), so the API is already inconsistent.
What happens: a client syncing a large account asks GET /api/v1/applications?limit=100000, or /listings, /captures and the rest. Each row is built by its row function (absolute URLs, the display annotations, the status log) for the whole account in one request, while the scheduler, db_worker and every other person's writes on the instance wait for the lock; past the twenty-second busy timeout they fail with database is locked. The per-token allowance (POSTULO_API_RATE, 600 an hour by default) lets it repeat.
Who can trigger it: any holder of a token with the list's scope, for their own account; on a shared instance the cost falls on the other accounts.
Why it matters: it undoes for API reads what #220 and config/sqlite.py set out to prevent, a single request holding the write lock for as long as it likes.
Fix: set NINJA_PAGINATION_MAX_LIMIT in config/settings/base.py (200, say), or give Page its own Input with limit: int = Field(100, ge=1, le=200) so the bound shows in the OpenAPI schema. State the maximum in the wiki's pagination paragraph of The capture API.

Done when

A test in tests/test_api_lists.py that asks GET /api/v1/applications?limit=10000 gets either a 422 or a page of at most the documented maximum, and the OpenAPI schema declares the bound on limit.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the API's pagination and the SQLite transaction settings. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The list in `api/api.py` is now at `api/api.py:632`; `api/paging.py`, the routers and the settings have not moved. Every paginated API list accepts any `limit`: `Page` extends django-ninja's `LimitOffsetPagination`, whose input is `limit: int = Field(100, ge=1)`, and no `NINJA_PAGINATION_MAX_LIMIT` is set, so ninja's ceiling is infinity. #230 moved row shaping after the cut so that a page's work is one page's work, but `?limit=1000000` makes the page the whole account. The call runs under `ATOMIC_REQUESTS`, and on SQLite every transaction is `IMMEDIATE`, so the instance's write lock is held while every row is loaded, shaped and serialised. - **Where:** `api/paging.py:45-62` (`Page`, inheriting ninja's unbounded `Input`); `config/settings/base.py:140` (`ATOMIC_REQUESTS`, and no `NINJA_PAGINATION_MAX_LIMIT` anywhere in `config/`); `config/sqlite.py:49` (`"transaction_mode": "IMMEDIATE"`). The eleven `@paginate(Page, ...)` lists in `api/api.py:630` and `api/routers/` all inherit it. `api/routers/search.py:17` does bound its own `limit` (`le=50`), so the API is already inconsistent. - **What happens:** a client syncing a large account asks `GET /api/v1/applications?limit=100000`, or `/listings`, `/captures` and the rest. Each row is built by its `row` function (absolute URLs, the display annotations, the status log) for the whole account in one request, while the scheduler, `db_worker` and every other person's writes on the instance wait for the lock; past the twenty-second busy timeout they fail with *database is locked*. The per-token allowance (`POSTULO_API_RATE`, 600 an hour by default) lets it repeat. - **Who can trigger it:** any holder of a token with the list's scope, for their own account; on a shared instance the cost falls on the other accounts. - **Why it matters:** it undoes for API reads what #220 and `config/sqlite.py` set out to prevent, a single request holding the write lock for as long as it likes. - **Fix:** set `NINJA_PAGINATION_MAX_LIMIT` in `config/settings/base.py` (200, say), or give `Page` its own `Input` with `limit: int = Field(100, ge=1, le=200)` so the bound shows in the OpenAPI schema. State the maximum in the wiki's pagination paragraph of *The capture API*. ## Done when - A test in `tests/test_api_lists.py` that asks `GET /api/v1/applications?limit=10000` gets either a 422 or a page of at most the documented maximum, and the OpenAPI schema declares the bound on `limit`.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:46:40 +00:00

tiagoagueda added the

enhancement

tier

labels

2026-10-01 10:46:40 +00:00

tiagoagueda referenced this issue from a commit

2026-10-05 23:54:30 +00:00

Bound the limit of every API list at 200

tiagoagueda commented

2026-10-06 09:32:04 +00:00