The M5 row gives the measured content::text size; §15 and §16.4 point the e2e:sheet-assets rerun to the owner; the walk log carries its script and the HTTP timing lines are committed.
162 lines
9.8 KiB
Markdown
162 lines
9.8 KiB
Markdown
# V1-2.1 corrections after the independent audit: evidence
|
||
|
||
Owner brief 2026-10-28 « V1-2.1 : corrections après audit indépendant » (verdict CHANGES
|
||
REQUIRED on 3914eaa). Every run below used the isolated `pnpm db:sandbox` stack only
|
||
(containers `faunosteo_dbsandbox_*`, ports 57xxx); DEV was not read or written.
|
||
|
||
## 1. Defects F1-F19 on the old schema (before M4)
|
||
|
||
Script `2026-10-28-v1-2-1-repro-old-schema.sql` (one rolled-back transaction) on a sandbox of
|
||
the 12 base migrations of develop, run during the first V1-2.1 pass. Output:
|
||
|
||
```
|
||
F1 plain account created a personal PUBLIC sheet for species other
|
||
F5 an account with an UNCONFIRMED email created a sheet
|
||
F6 set_sheet_visibility changes visibility with no gate
|
||
F15 another plain account reads the public personal sheet: 1 row
|
||
F9 editor rewrote draft content / renamed the sheet by direct UPDATE
|
||
F4 version published with a drawing never confirmed
|
||
F2 the draft of an ARCHIVED sheet was published; create_sheet_draft opened a draft on it
|
||
F8 after archiving, another account reads 0 published versions
|
||
F13 a DEPRECATED version was deleted physically
|
||
F3 copied asset rows not confirmed
|
||
F14 a platform admin at aal1 published an official sheet
|
||
F19 grant_platform_role(owner) accepted at aal1; the new owner holds 0 verified factors
|
||
F7 upload confirmed with no object in Storage
|
||
F12 service_role published a draft by direct UPDATE
|
||
F17 service_role inserted an owner row directly into profile_roles
|
||
F16 sha256 of an asset used by a published version changed
|
||
```
|
||
|
||
Re-run on 2026-10-28 for the final decisions pass, raw psql output committed as
|
||
`2026-10-28-v1-2-1-repro-old-schema.out` (sandbox `up fresh` from a copy holding only the 12
|
||
migrations before M4; the same NOTICE lines, `repro exit 0`, the transaction rolled back).
|
||
|
||
F18 (last-owner race) was reproduced with two sessions; its test is
|
||
`packages/supabase/tests/sandbox/sheets.sandbox.ts`.
|
||
|
||
## 2. service_role effective table privileges (§16.2 prerequisite)
|
||
|
||
Measured on the M7 rehearsal stack (DEV path), before and after M4+M5:
|
||
|
||
- before: ALL (DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE) on
|
||
`species`, `anatomical_sheets`, `anatomical_sheet_versions`, `anatomical_sheet_assets`,
|
||
`profiles`, `profile_roles`;
|
||
- after: none on those six tables (pgTAP 005 and 018 assert it, with the EXECUTE census).
|
||
|
||
## 3. New tests red on 3914eaa, green on the corrected HEAD
|
||
|
||
Stack built from the base migrations plus the M4/M5 of 3914eaa, then the new and changed
|
||
files of this pass:
|
||
|
||
| File | On 3914eaa | On the corrected HEAD |
|
||
|---|---|---|
|
||
| 006 | 2 not ok: another editor uploads a pending key it did not register (L5) | 16/16 |
|
||
| 016 | 1 not ok: a draft from a deprecated version takes it as base (M2) | 50/50 |
|
||
| 017 | M1 approval on an archived sheet succeeds; M2 base from a deprecated version; the file aborts there | 85/85 (111/111 after the final decisions pass, §4) |
|
||
| 018 | 93 not ok of 337: capability holders at aal1 get `rights.capability` instead of `rights.mfa` (§6.7); `delete_sheet_asset` answers a raw code | 337/337 |
|
||
| 019 | 38/38 (lost coverage of behaviour that was already correct) | 38/38 |
|
||
| sheets-concurrency | 5 failed of 15: publish against withdraw (no version lock), two deprecations (no sheet lock), archive against approve (no sheet lock) | 15/15 |
|
||
|
||
## 4. Real-stack results on the corrected HEAD
|
||
|
||
Final decisions pass (owner brief « V1-2.1 : décisions finales du propriétaire »), code at
|
||
`2379d28` (M6 option A, text size measure with the bounded, linear number walk, pgTAP
|
||
015/017/020; only `.claude/` differs at the final HEAD). Earlier figures (1 333 pgTAP) were the `2af61e7`
|
||
state; intermediate passes at `7eca441` (1 352) and `5620399` (1 807) preceded the review findings below.
|
||
|
||
| Run | Fresh stack | Upgraded stack (M3 + Git fixture, then M4+M5) |
|
||
|---|---|---|
|
||
| pgTAP (`test-db`, 18 files) | 1 808/1 808 | 1 808/1 808 |
|
||
| sandbox suite, run 1 | 87/87 | 87/87 |
|
||
| sandbox suite, run 2 (same stack) | 87/87 | 87/87 |
|
||
| of which two-session concurrency (`sheets-concurrency`) | 16/16 each run | 16/16 each run |
|
||
| pgTAP again after both suite runs | 1 808/1 808 | 1 808/1 808 |
|
||
| `types --check` | types match | types match |
|
||
|
||
PGlite: 128/128 (packages/supabase: the 18 pgTAP files and the closed-code census).
|
||
|
||
New in this pass:
|
||
|
||
- 017 M6: owner self-grants `sheet_reviewer`; owner-reviewer acknowledges and approves a version
|
||
written by E, E publishes; owner refused on a round it authored (`review.separation`);
|
||
publication after an author's approval refused (`publish.approval.invalid`).
|
||
- 017 size: cap − 1 and cap saved and measured exactly; cap + 1 refused; a 2 MiB document of
|
||
3-byte characters saved; cap + 1 in 4-byte characters refused; a dense document within the cap
|
||
as text and over it as binary jsonb saved whole; creation over the cap refused; numbers that
|
||
JavaScript prints with an exponent stored and measured in plain form; a document under the cap
|
||
in compact JavaScript form but over it as measured refused; 9 000 numbers of 131 072 digits
|
||
refused with the closed code; 600 000 small numbers within the cap saved (the number walk is
|
||
linear); the table CHECK definition. Every refusal answers
|
||
`document.too_large` (23514), never a raw error.
|
||
- 017 `publish.species.other` refused alone.
|
||
- 020 (449): the AR-1 B matrix of §15 in full (6 caller classes × aal1/aal2 × grant/revoke × 4
|
||
roles × self/other, each refusal with « nothing written »), the regression chain on the real
|
||
owner and admin at aal1, and the rights RPCs (`grant_catalog_right` on itself and on another,
|
||
`revoke_catalog_right`, `lookup_catalog_account`, `list_catalog_grants`) refused to every
|
||
non-owner class at aal2 (`rights.owner`) and at aal1 (`rights.mfa`).
|
||
|
||
Number walk, linear (security gate on `7c4bf12`: `jsonb_path_query` row by row is quadratic on
|
||
PostgreSQL 17). Raw psql timings on the sandbox, committed as
|
||
`2026-10-28-v1-2-1-number-walk.out` (one transaction ending in `rollback`; script in its header line below): row by row 138 / 506 / 2 172 ms for 50k /
|
||
100k / 200k numbers; the shipped walk (one `jsonb_path_query_array` call) 69 / 135 / 267 / 421 ms
|
||
for 100k / 200k / 400k / 600k. Over
|
||
HTTP on both stacks, each run (lines extracted in `2026-10-28-v1-2-1-sandbox-timings.out`): 600 000 numbers (1 801 760 bytes of text) saved in 677-695 ms;
|
||
1 000 000 numbers answered `document.too_large` in 1 019-1 078 ms (budget 2 000 ms).
|
||
|
||
Number bomb on the real image (sandbox, re-run on `2379d28`): the shipped function answers
|
||
`document.too_large`; the same payload under the previous body (`octet_length(p_doc::text)`
|
||
alone, rolled back) fails with `out of memory` after a 1 GiB buffer.
|
||
|
||
## 5. Timing at the caps (§15, budget 2 000 ms for save, submit, publish)
|
||
|
||
Synthetic document: 64 views, 1 024 annotations, 8 192 finding nodes (depth 8), 1 089 review
|
||
groups, 4 drawings of 2 000 000 bytes. The labels are calibrated on the contract's text measure
|
||
alone: text 2 076 707 bytes (cap 2 097 152), binary jsonb 2 346 648 bytes, over the cap and
|
||
accepted, as the measure requires. HTTP wall-clock through PostgREST on the development machine,
|
||
code `2379d28`, milliseconds (min / median / max):
|
||
|
||
| Stack, run | save (7) | submit (5) | approve (3) | publish (3) |
|
||
|---|---|---|---|---|
|
||
| fresh, 1 | 127 / 137 / 155 | 4 / 5 / 6 | 190 / 285 / 292 | 195 / 288 / 303 |
|
||
| fresh, 2 | 130 / 142 / 155 | 4 / 7 / 13 | 196 / 284 / 297 | 205 / 302 / 306 |
|
||
| upgraded, 1 | 135 / 139 / 148 | 5 / 6 / 6 | 192 / 282 / 294 | 196 / 292 / 296 |
|
||
| upgraded, 2 | 127 / 137 / 151 | 5 / 5 / 7 | 199 / 286 / 295 | 220 / 292 / 299 |
|
||
|
||
The core's at-caps fixture is V1-2.2 work; the contract fixes the budgets at the V1-2.3
|
||
checkpoint and measures verification (120 s) there.
|
||
|
||
## 6. M7: upgrade rehearsal with a Git fixture of the drawing object
|
||
|
||
Accepted by the owner for V1-2.1 (brief « V1-2.1 : décisions finales du propriétaire »,
|
||
2026-10-28; contract §12.3, §17.3). What this rehearsal is, and is not:
|
||
|
||
- It uses a representative Git fixture, not a copy of the DEV object: the committed file
|
||
`supabase/seed/anatomical-sheets/equine/draft/assets/horse.svg` (132 997 bytes), whose sha256
|
||
`0ea5202f…08541` equals the sha256 referenced by the DEV metadata (the asset row
|
||
`7f767f74…` of the historical sheet, seeded from `equine_draft.sql`).
|
||
- Its bytes, its Storage metadata and its relations (asset row, historical sheet, version) are
|
||
compared before and after the migration.
|
||
- No file was read from the real Storage of DEV, and nothing on DEV was read or written in
|
||
V1-2.1. The current integrity of the live DEV object is therefore **not demonstrated**; it is
|
||
checked when the owner applies M4 to M6 on DEV (contract §16.11).
|
||
|
||
DEV path: `up upgrade 20261028100000` from a copy whose migrations stop at M3, the fixture
|
||
placed as the operator tool placed the drawing on DEV (service-key upload, pre-M4 confirmation
|
||
RPC), then M4+M5 by `supabase migration up`.
|
||
|
||
- `storage.objects` row (id, owner, metadata, version, timestamps), the asset row, the
|
||
historical sheet row and its version (content md5, updated_at): identical before and after.
|
||
- Changed, as M4/M5 intend: bucket limit 20 971 520 → 2 097 152; service_role privileges
|
||
ALL → none.
|
||
- Bytes through the Storage API before and after: identical to the Git file.
|
||
- After M4+M5, through the Storage API with real tokens: an editor at aal2 reads the 132 997
|
||
bytes (sha256 equal to the key); the same editor at aal1, a catalogue reader and an anonymous
|
||
caller are refused (the historical sheet has no published version).
|
||
|
||
The rehearsal is reproducible from the repository, re-run on `7eca441`, `5620399` and `2379d28` (final decisions pass)
|
||
with the same results, the only diff lines being the bucket limit and the service_role
|
||
privileges: `bash .claude/audits/2026-10-28-v1-2-1-m7/m7-rehearsal.sh [work dir]` (sandbox only; it applies
|
||
M4+M5 with `supabase migration up --db-url` on the sandbox port, a step `scripts/db-sandbox.sh`
|
||
has no subcommand for) and `m7-read.mjs` (the reads with real tokens).
|