amadan

carlos / platform Public

Clone 
git clone https://amadan.net/carlos/platform

Plain git — no account needed to clone.

Download

Download this file

1package console
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "path/filepath"
9 "strings"
10 "sync/atomic"
11 "testing"
12 "time"
13)
14
15func allowAllClaimSQL(context.Context, claimSQLScope) error { return nil }
16
17func gateCtx(t *testing.T) context.Context {
18 t.Helper()
19 ctx, cancel := context.WithTimeout(context.Background(), 20*time.Second)
20 t.Cleanup(cancel)
21 return ctx
22}
23
24func openGatedStore(t *testing.T) *Store {
25 t.Helper()
26 s, err := OpenGated(filepath.Join(t.TempDir(), "console.db"))
27 if err != nil {
28 t.Fatal(err)
29 }
30 t.Cleanup(func() { s.Close() })
31 return s
32}
33
34func openGatedCustom(t *testing.T, tables map[string]claimSQLTable, ddl ...string) *Store {
35 t.Helper()
36 path := filepath.Join(t.TempDir(), "custom.db")
37 s, err := openGated(path, "file:"+path+"?_pragma=foreign_keys(1)", tables, func(s *Store) error {
38 for _, d := range ddl {
39 if _, err := s.db.Exec(d); err != nil {
40 return err
41 }
42 }
43 return nil
44 })
45 if err != nil {
46 t.Fatal(err)
47 }
48 t.Cleanup(func() { s.Close() })
49 return s
50}
51
52func requireDenied(t *testing.T, what string, err error) {
53 t.Helper()
54 if err == nil {
55 t.Fatalf("%s: succeeded, want a claim denial", what)
56 }
57 if !strings.Contains(err.Error(), claimDenyMessage) {
58 t.Fatalf("%s: error %v, want %q", what, err, claimDenyMessage)
59 }
60}
61
62func drainRows(rows *sql.Rows, err error) error {
63 if err != nil {
64 return err
65 }
66 defer rows.Close()
67 for rows.Next() {
68 }
69 return rows.Err()
70}
71
72func countWhere(t *testing.T, s *Store, table, where string, args ...any) int {
73 t.Helper()
74 var n int
75 if err := s.db.QueryRow(`SELECT count(*) FROM `+table+` WHERE `+where, args...).Scan(&n); err != nil {
76 t.Fatal(err)
77 }
78 return n
79}
80
81const gateAcct = int64(7)
82
83var gateScope = claimSQLScope{AccountID: gateAcct, App: "blog", AppID: "app-A", Mode: claimModeEffect}
84
85// seedGateApp writes the account row (not app-scoped) and a full set of app
86// rows for gateScope, under a permit.
87func seedGateApp(t *testing.T, s *Store) {
88 t.Helper()
89 if _, err := s.db.Exec(`INSERT INTO accounts (id, sqid, name, created_at) VALUES (?, 'acct7', 'seven', 1)`, gateAcct); err != nil {
90 t.Fatal(err)
91 }
92 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
93 for _, q := range []string{
94 `INSERT INTO apps (app, account_id, created_by, created_at, app_id) VALUES ('blog', 7, 'p@x', 1, 'app-A')`,
95 `INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p@x', 'seed', 1, 7)`,
96 `INSERT INTO door_keys (account_id, app, id, sha256, created_at) VALUES (7, 'blog', 'k1', 'h', 1)`,
97 } {
98 if _, err := v.db.Exec(q); err != nil {
99 return err
100 }
101 }
102 return nil
103 })
104 if err != nil {
105 t.Fatal(err)
106 }
107}
108
109// ---- unauthorized writes ------------------------------------------------------
110
111func TestClaimSQLUnauthorizedWritesDenied(t *testing.T) {
112 s := openGatedStore(t)
113 seedGateApp(t, s)
114 ctx := gateCtx(t)
115
116 t.Run("Exec", func(t *testing.T) {
117 _, err := s.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('evil', 7, 'x', 1)`)
118 requireDenied(t, "insert", err)
119 _, err = s.db.Exec(`UPDATE apps SET placement = 'z' WHERE app = 'blog'`)
120 requireDenied(t, "update", err)
121 _, err = s.db.Exec(`DELETE FROM apps WHERE app = 'blog'`)
122 requireDenied(t, "delete", err)
123 _, err = s.db.Exec(`INSERT INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 1)`)
124 requireDenied(t, "insert owners", err)
125 })
126 t.Run("ExecContext", func(t *testing.T) {
127 _, err := s.db.ExecContext(ctx, `UPDATE audit SET action = 'forged'`)
128 requireDenied(t, "update audit", err)
129 _, err = s.db.ExecContext(ctx, `DELETE FROM door_keys`)
130 requireDenied(t, "delete door_keys", err)
131 })
132 t.Run("prepared", func(t *testing.T) {
133 st, err := s.db.Prepare(`DELETE FROM audit WHERE app = ?`)
134 if err != nil {
135 t.Fatal(err)
136 }
137 defer st.Close()
138 _, err = st.Exec("blog")
139 requireDenied(t, "prepared delete", err)
140 st2, err := s.db.PrepareContext(ctx, `INSERT INTO audit (app, address, action, created_at, account_id) VALUES (?, 'x', 'a', 1, 7)`)
141 if err != nil {
142 t.Fatal(err)
143 }
144 defer st2.Close()
145 _, err = st2.ExecContext(ctx, "blog")
146 requireDenied(t, "prepared insert", err)
147 })
148 t.Run("transaction", func(t *testing.T) {
149 tx, err := s.db.Begin()
150 if err != nil {
151 t.Fatal(err)
152 }
153 _, err = tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'x', 'tx', 1, 7)`)
154 requireDenied(t, "tx insert", err)
155 if err := tx.Rollback(); err != nil {
156 t.Fatal(err)
157 }
158 })
159 t.Run("QueryRETURNING", func(t *testing.T) {
160 err := drainRows(s.db.QueryContext(ctx, `INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'x', 'ret', 1, 7) RETURNING id`))
161 requireDenied(t, "insert returning", err)
162 err = drainRows(s.db.Query(`DELETE FROM audit RETURNING id`))
163 requireDenied(t, "delete returning", err)
164 })
165 t.Run("REPLACE", func(t *testing.T) {
166 _, err := s.db.Exec(`INSERT OR REPLACE INTO apps (app, account_id, created_by, created_at, app_id) VALUES ('blog', 7, 'x', 1, 'app-A')`)
167 requireDenied(t, "replace", err)
168 _, err = s.db.Exec(`REPLACE INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 9)`)
169 requireDenied(t, "replace owners", err)
170 })
171 t.Run("mail telemetry is gated too", func(t *testing.T) {
172 _, err := s.db.Exec(`INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at)
173VALUES ('acct7', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`)
174 requireDenied(t, "mail_events", err)
175 _, err = s.db.Exec(`INSERT INTO mail_sender_counts (account, app, sender, day) VALUES ('acct7', 'blog', 's', '2026-01-01')`)
176 requireDenied(t, "mail_sender_counts", err)
177 })
178 t.Run("app-scoped token", func(t *testing.T) {
179 _, err := s.db.Exec(`INSERT INTO cli_tokens (token_hash, address, created_at, expires_at, kind, account_id, app) VALUES ('h1', 'a', 1, 0, 'app', 7, 'blog')`)
180 requireDenied(t, "app token", err)
181 // A user token carries no app: it is not app-scoped, so login still works.
182 if _, err := s.db.Exec(`INSERT INTO cli_tokens (token_hash, address, created_at, expires_at) VALUES ('h2', 'a', 1, 0)`); err != nil {
183 t.Fatalf("user token insert: %v", err)
184 }
185 _, err = s.db.Exec(`UPDATE cli_tokens SET app = 'blog' WHERE token_hash = 'h2'`)
186 requireDenied(t, "moving a token into an app", err)
187 })
188 t.Run("non-app tables stay writable", func(t *testing.T) {
189 if _, err := s.db.Exec(`INSERT INTO users (address, created_at) VALUES ('u@x', 1)`); err != nil {
190 t.Fatal(err)
191 }
192 if _, err := s.db.Exec(`INSERT INTO sessions (token_hash, address, created_at, expires_at) VALUES ('t', 'u@x', 1, 2)`); err != nil {
193 t.Fatal(err)
194 }
195 })
196
197 if n := countWhere(t, s, "apps", "1=1"); n != 1 {
198 t.Fatalf("apps rows = %d, want 1", n)
199 }
200 if n := countWhere(t, s, "audit", "action <> 'seed'"); n != 0 {
201 t.Fatalf("%d unauthorized audit rows landed", n)
202 }
203 if n := countWhere(t, s, "audit", "action = 'seed'"); n != 1 {
204 t.Fatalf("seed audit rows = %d, want 1", n)
205 }
206}
207
208func TestClaimSQLViaTableNeedsParentScope(t *testing.T) {
209 s := openGatedStore(t)
210 seedGateApp(t, s)
211 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
212 if _, err := v.db.Exec(`INSERT INTO pending_changes (id, account_id, app, kind, payload, quorum, proposer, created_at, expires_at) VALUES ('c1', 7, 'blog', 'k', '{}', 1, 'p', 1, 2)`); err != nil {
213 return err
214 }
215 if _, err := v.db.Exec(`INSERT INTO pending_confirms (change_id, address, confirmed_at) VALUES ('c1', 'p', 1)`); err != nil {
216 return err
217 }
218 // A confirm for a change that does not exist has no scope: refused.
219 _, err := v.db.Exec(`INSERT INTO pending_confirms (change_id, address, confirmed_at) VALUES ('ghost', 'p', 1)`)
220 requireDenied(t, "ghost confirm", err)
221 return nil
222 })
223 if err != nil {
224 t.Fatal(err)
225 }
226 _, err = s.db.Exec(`DELETE FROM pending_confirms WHERE change_id = 'c1'`)
227 requireDenied(t, "confirm delete outside scope", err)
228}
229
230// ---- admitted writes ---------------------------------------------------------
231
232func TestClaimSQLAdmittedWritesPassAndAreScoped(t *testing.T) {
233 s := openGatedStore(t)
234 seedGateApp(t, s)
235 var calls atomic.Int64
236 check := func(context.Context, claimSQLScope) error { calls.Add(1); return nil }
237 err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
238 for _, q := range []string{
239 `UPDATE apps SET placement = 'eu' WHERE account_id = 7 AND app = 'blog'`,
240 `INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p@x', 'edit', 2, 7)`,
241 `INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at) VALUES ('acct7', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`,
242 } {
243 if _, err := v.db.Exec(q); err != nil {
244 t.Errorf("%s: %v", q, err)
245 }
246 }
247 // Reads need no check and see the writes.
248 var placement string
249 if err := v.db.QueryRow(`SELECT placement FROM apps WHERE app = 'blog'`).Scan(&placement); err != nil || placement != "eu" {
250 t.Errorf("placement = %q, %v", placement, err)
251 }
252 // The permit is for blog only.
253 _, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('other', 'p@x', 'x', 1, 7)`)
254 requireDenied(t, "other app", err)
255 _, err = v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p@x', 'x', 1, 8)`)
256 requireDenied(t, "other account", err)
257 _, err = v.db.Exec(`INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at) VALUES ('acct8', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`)
258 requireDenied(t, "other account sqid", err)
259 return nil
260 })
261 if err != nil {
262 t.Fatal(err)
263 }
264 // 1 admission + 3 writes + 3 refused writes (the callback runs before each write).
265 if got := calls.Load(); got != 7 {
266 t.Fatalf("check ran %d times, want 7 (admission + every write)", got)
267 }
268 if n := countWhere(t, s, "audit", "action = 'edit'"); n != 1 {
269 t.Fatalf("edit audit rows = %d, want 1", n)
270 }
271 // Outside the callback the scope is gone again.
272 _, err = s.db.Exec(`UPDATE apps SET placement = 'us' WHERE app = 'blog'`)
273 requireDenied(t, "after callback", err)
274}
275
276func TestClaimSQLAdmissionRefusalRunsNothing(t *testing.T) {
277 s := openGatedStore(t)
278 boom := errors.New("not yours")
279 ran := false
280 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, func(context.Context, claimSQLScope) error { return boom }, func(*Store) error {
281 ran = true
282 return nil
283 })
284 if !errors.Is(err, ErrClaimSQLDenied) || !errors.Is(err, boom) || ran {
285 t.Fatalf("err = %v ran = %v, want ErrClaimSQLDenied wrapping boom and no callback", err, ran)
286 }
287 for _, bad := range [][]claimSQLScope{nil, {{AccountID: 0, App: "a", Mode: claimModeEffect}}, {{AccountID: 1, App: "", Mode: claimModeEffect}},
288 {{AccountID: 1, App: "a", AppID: "x"}}, {{AccountID: 1, App: "a", AppID: "x", Mode: "admin"}}, {{AccountID: 1, App: "a", Mode: claimModeRestore}}} {
289 if err := s.withClaimSQL(gateCtx(t), bad, allowAllClaimSQL, func(*Store) error { return nil }); !errors.Is(err, ErrClaimSQLScope) {
290 t.Fatalf("scopes %v: err = %v, want ErrClaimSQLScope", bad, err)
291 }
292 }
293}
294
295func TestClaimSQLFlagOffStoreIsUngated(t *testing.T) {
296 s := openTestStore(t)
297 if s.claimGated() {
298 t.Fatal("Open must not arm the gate")
299 }
300 if _, err := s.db.Exec(`INSERT INTO owners (address, app, created_at) VALUES ('a', 'b', 1)`); err != nil {
301 t.Fatal(err)
302 }
303 ran := false
304 if err := s.withClaimSQL(gateCtx(t), nil, nil, func(v *Store) error { ran = v == s; return nil }); err != nil || !ran {
305 t.Fatalf("withClaimSQL flag-off: ran=%v err=%v", ran, err)
306 }
307}
308
309// ---- revocation, expiry, successor ids ---------------------------------------
310
311func TestClaimSQLRevokedCheckStopsMutation(t *testing.T) {
312 s := openGatedStore(t)
313 seedGateApp(t, s)
314 var revoked atomic.Bool
315 check := func(context.Context, claimSQLScope) error {
316 if revoked.Load() {
317 return errors.New("operation revoked")
318 }
319 return nil
320 }
321 err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
322 if _, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'first', 1, 7)`); err != nil {
323 t.Fatalf("first write: %v", err)
324 }
325 revoked.Store(true)
326 _, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'second', 1, 7)`)
327 if !errors.Is(err, ErrClaimSQLDenied) || !strings.Contains(err.Error(), "operation revoked") {
328 t.Fatalf("second write err = %v, want denied by the callback", err)
329 }
330 // Even with the callback relenting, the scope stays dead.
331 revoked.Store(false)
332 _, err = v.db.Exec(`DELETE FROM apps WHERE app = 'blog'`)
333 if err == nil {
334 t.Fatal("write after a refused check must stay refused")
335 }
336 return nil
337 })
338 if err != nil {
339 t.Fatal(err)
340 }
341 if countWhere(t, s, "audit", "action = 'first'") != 1 || countWhere(t, s, "audit", "action = 'second'") != 0 || countWhere(t, s, "apps", "app = 'blog'") != 1 {
342 t.Fatal("revoked scope mutated state")
343 }
344}
345
346func TestClaimSQLRevokedBeforeCommitRollsBack(t *testing.T) {
347 s := openGatedStore(t)
348 seedGateApp(t, s)
349 var revoked atomic.Bool
350 check := func(context.Context, claimSQLScope) error {
351 if revoked.Load() {
352 return errors.New("expired")
353 }
354 return nil
355 }
356 err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
357 tx, err := v.db.Begin()
358 if err != nil {
359 return err
360 }
361 if _, err := tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'in-tx', 1, 7)`); err != nil {
362 t.Fatalf("tx write: %v", err)
363 }
364 revoked.Store(true)
365 err = tx.Commit()
366 if !errors.Is(err, ErrClaimSQLDenied) {
367 t.Fatalf("commit err = %v, want ErrClaimSQLDenied", err)
368 }
369 return nil
370 })
371 if err != nil {
372 t.Fatal(err)
373 }
374 if n := countWhere(t, s, "audit", "action = 'in-tx'"); n != 0 {
375 t.Fatalf("%d rows survived a refused commit", n)
376 }
377 // The pool is healthy and the permit is gone.
378 _, err = s.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'late', 1, 7)`)
379 requireDenied(t, "after refused commit", err)
380}
381
382func TestClaimSQLExpiryCheckedOnEveryWrite(t *testing.T) {
383 s := openGatedStore(t)
384 seedGateApp(t, s)
385 now := time.Unix(1000, 0)
386 expires := time.Unix(1010, 0)
387 check := func(context.Context, claimSQLScope) error {
388 if !now.Before(expires) {
389 return errors.New("permit expired")
390 }
391 return nil
392 }
393 err := s.withPreparedClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, check, check, nil, false, func(v *Store) error {
394 if _, err := v.db.Exec(`UPDATE apps SET placement = 'a' WHERE app = 'blog'`); err != nil {
395 t.Fatal(err)
396 }
397 now = time.Unix(1011, 0)
398 if _, err := v.db.Exec(`UPDATE apps SET placement = 'b' WHERE app = 'blog'`); err == nil {
399 t.Fatal("write after expiry succeeded")
400 }
401 return nil
402 })
403 if err != nil {
404 t.Fatal(err)
405 }
406 var placement string
407 if err := s.db.QueryRow(`SELECT placement FROM apps WHERE app = 'blog'`).Scan(&placement); err != nil || placement != "a" {
408 t.Fatalf("placement = %q (%v), want a", placement, err)
409 }
410}
411
412func TestClaimSQLSuccessorAppIDMismatchRefused(t *testing.T) {
413 s := openGatedStore(t)
414 seedGateApp(t, s) // apps row blog is incarnation app-A
415 successor := claimSQLScope{AccountID: gateAcct, App: "blog", AppID: "app-B", Mode: claimModeEffect}
416 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{successor}, allowAllClaimSQL, func(v *Store) error {
417 _, err := v.db.Exec(`UPDATE apps SET placement = 'x' WHERE app = 'blog'`)
418 requireDenied(t, "successor update of predecessor row", err)
419 _, err = v.db.Exec(`DELETE FROM apps WHERE app = 'blog'`)
420 requireDenied(t, "successor delete of predecessor row", err)
421 _, err = v.db.Exec(`UPDATE apps SET app_id = 'app-B' WHERE app = 'blog'`)
422 requireDenied(t, "successor taking over the id", err)
423 return nil
424 })
425 if err != nil {
426 t.Fatal(err)
427 }
428 // A permit never covers another app's rows.
429 err = s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
430 _, err := v.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('legacy', 7, 'x', 1)`)
431 requireDenied(t, "row for an app the permit does not name", err)
432 return nil
433 })
434 if err != nil {
435 t.Fatal(err)
436 }
437 // An unidentified (app_id '') row is adopted by a permit that names its app.
438 adopt := claimSQLScope{AccountID: gateAcct, App: "adopt", AppID: "app-C", Mode: claimModeEffect}
439 err = s.withClaimSQL(gateCtx(t), []claimSQLScope{adopt}, allowAllClaimSQL, func(v *Store) error {
440 if _, err := v.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('adopt', 7, 'x', 1)`); err != nil {
441 return err
442 }
443 _, err := v.db.Exec(`UPDATE apps SET app_id = 'app-C' WHERE app = 'adopt'`)
444 return err
445 })
446 if err != nil {
447 t.Fatal(err)
448 }
449 if n := countWhere(t, s, "apps", "app = 'adopt' AND app_id = 'app-C'"); n != 1 {
450 t.Fatal("adoption did not land")
451 }
452 if n := countWhere(t, s, "apps", "app = 'blog' AND placement = '' AND app_id = 'app-A'"); n != 1 {
453 t.Fatal("predecessor row changed under another incarnation's permit")
454 }
455}
456
457// ---- handles that outlive the callback ---------------------------------------
458
459func TestClaimSQLRetainedHandlesDieWithTheCallback(t *testing.T) {
460 s := openGatedStore(t)
461 seedGateApp(t, s)
462 var tx *sql.Tx
463 var stmt *sql.Stmt
464 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
465 var err error
466 if tx, err = v.db.Begin(); err != nil {
467 return err
468 }
469 if _, err = tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'retained-tx', 1, 7)`); err != nil {
470 return err
471 }
472 if stmt, err = v.db.Prepare(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'retained-stmt', 1, 7)`); err != nil {
473 return err
474 }
475 return nil // tx neither committed nor rolled back, stmt and tx leaked
476 })
477 // The forced rollback lost the tx's write, so the scope must not succeed.
478 if !errors.Is(err, ErrClaimSQLScope) {
479 t.Fatalf("open tx at scope end: %v, want ErrClaimSQLScope", err)
480 }
481 // No pool deadlock: the single connection is back.
482 short, cancel := context.WithTimeout(context.Background(), 5*time.Second)
483 defer cancel()
484 if _, err := s.db.ExecContext(short, `INSERT INTO users (address, created_at) VALUES ('after@x', 1)`); err != nil {
485 t.Fatalf("pool wedged after callback: %v", err)
486 }
487 if _, err := tx.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'zombie', 1, 7)`); err == nil {
488 t.Fatal("retained tx still writes")
489 }
490 if err := tx.Commit(); err == nil {
491 t.Fatal("retained tx committed")
492 }
493 if _, err := stmt.Exec(); err == nil {
494 t.Fatal("retained statement still writes")
495 }
496 if n := countWhere(t, s, "audit", "action IN ('retained-tx','retained-stmt','zombie')"); n != 0 {
497 t.Fatalf("%d rows written through retained handles", n)
498 }
499}
500
501func TestClaimSQLRetainedRowsDoNotWedgeThePool(t *testing.T) {
502 s := openGatedStore(t)
503 seedGateApp(t, s)
504 var rows *sql.Rows
505 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
506 var err error
507 rows, err = v.db.Query(`SELECT address FROM owners`)
508 return err
509 })
510 if err != nil {
511 t.Fatal(err)
512 }
513 defer rows.Close()
514 short, cancel := context.WithTimeout(context.Background(), 5*time.Second)
515 defer cancel()
516 if _, err := s.db.ExecContext(short, `INSERT INTO users (address, created_at) VALUES ('after2@x', 1)`); err != nil {
517 t.Fatalf("pool wedged by retained rows: %v", err)
518 }
519}
520
521func TestClaimSQLViewCannotNestOrClose(t *testing.T) {
522 s := openGatedStore(t)
523 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error {
524 if err := v.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(*Store) error { return nil }); !errors.Is(err, ErrClaimSQLScope) {
525 t.Errorf("nested withClaimSQL err = %v, want ErrClaimSQLScope", err)
526 }
527 if err := v.Close(); !errors.Is(err, ErrClaimSQLScope) {
528 t.Errorf("view Close err = %v, want ErrClaimSQLScope", err)
529 }
530 return nil
531 })
532 if err != nil {
533 t.Fatal(err)
534 }
535 // An outer-store call inside the callback is refused at once rather than
536 // waiting for the connection the view holds (review m-5).
537 err = s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope}, allowAllClaimSQL, func(*Store) error {
538 short, cancel := context.WithTimeout(context.Background(), 200*time.Millisecond)
539 defer cancel()
540 _, err := s.db.ExecContext(short, `INSERT INTO users (address, created_at) VALUES ('outer@x', 1)`)
541 if !errors.Is(err, ErrClaimSQLScope) {
542 t.Errorf("outer call err = %v, want ErrClaimSQLScope", err)
543 }
544 return nil
545 })
546 if err != nil {
547 t.Fatal(err)
548 }
549}
550
551// ---- the wrapper refuses what would defeat the gate --------------------------
552
553func TestClaimSQLOrdinaryHandleRefusesDefeatingStatements(t *testing.T) {
554 s := openGatedStore(t)
555 seedGateApp(t, s)
556 stmts := []string{
557 `PRAGMA recursive_triggers = OFF`,
558 `pragma writable_schema = 1`,
559 `CREATE TABLE x (a)`,
560 `CREATE TEMP TABLE x (a)`,
561 `DROP TRIGGER claim_delete_apps`,
562 `DROP TABLE apps`,
563 `ALTER TABLE apps ADD COLUMN z TEXT`,
564 `ATTACH DATABASE ':memory:' AS m`,
565 `DETACH DATABASE m`,
566 `VACUUM`,
567 `BEGIN`,
568 `COMMIT`,
569 `SAVEPOINT a`,
570 `EXPLAIN SELECT 1`,
571 `DELETE FROM temp.claim_permit`,
572 `INSERT INTO claim_permit (account_id, sqid, app, app_id) VALUES (7, NULL, 'other', '')`,
573 `INSERT INTO "claim_permit" VALUES (7, NULL, 'other', '')`,
574 `INSERT INTO [claim_permit] VALUES (7, NULL, 'other', '')`,
575 "INSERT INTO `claim_permit` VALUES (7, NULL, 'other', '')",
576 `INSERT INTO 'claim_permit' VALUES (7, NULL, 'other', '')`,
577 `UPDATE temp."CLAIM_PERMIT" SET app = 'blog'`,
578 `WITH x AS (SELECT 1) DELETE FROM claim_permit`,
579 `SELECT * FROM sqlite_temp_master`,
580 `DELETE FROM sqlite_sequence`,
581 `SELECT * FROM pragma_table_info('apps')`,
582 // multi-statement loopholes
583 `SELECT 1; PRAGMA recursive_triggers = OFF`,
584 `SELECT 1; DELETE FROM claim_permit`,
585 `SELECT ';'; PRAGMA recursive_triggers = OFF`,
586 "SELECT 1 /* ; */; DROP TRIGGER claim_delete_apps",
587 "SELECT 1 -- ;\n; DROP TRIGGER claim_delete_apps",
588 `SELECT "a;b"; DROP TRIGGER claim_delete_apps`,
589 `INSERT INTO users (address, created_at) VALUES ('m@x', 1); ATTACH DATABASE ':memory:' AS m`,
590 `SELECT 1;;; CREATE TABLE y (a)`,
591 }
592 ctx := gateCtx(t)
593 check := func(name string, err error) {
594 t.Helper()
595 if !errors.Is(err, ErrClaimSQLForbidden) {
596 t.Errorf("%s: err = %v, want ErrClaimSQLForbidden", name, err)
597 }
598 }
599 scoped := func(f func(v *Store)) {
600 if err := s.withClaimSQL(ctx, []claimSQLScope{gateScope}, allowAllClaimSQL, func(v *Store) error { f(v); return nil }); err != nil {
601 t.Fatal(err)
602 }
603 }
604 for _, q := range stmts {
605 _, err := s.db.Exec(q)
606 check("Exec "+q, err)
607 _, err = s.db.ExecContext(ctx, q)
608 check("ExecContext "+q, err)
609 check("Query "+q, drainRows(s.db.Query(q)))
610 check("QueryRow "+q, s.db.QueryRow(q).Scan(new(any)))
611 st, err := s.db.Prepare(q)
612 if err == nil {
613 st.Close()
614 }
615 check("Prepare "+q, err)
616 tx, err := s.db.Begin()
617 if err != nil {
618 t.Fatal(err)
619 }
620 _, err = tx.Exec(q)
621 check("tx.Exec "+q, err)
622 tx.Rollback()
623 scoped(func(v *Store) {
624 _, err := v.db.Exec(q)
625 check("view Exec "+q, err)
626 tx, err := v.db.Begin()
627 if err != nil {
628 t.Error(err)
629 return
630 }
631 _, err = tx.Exec(q)
632 check("view tx.Exec "+q, err)
633 tx.Rollback()
634 })
635 }
636 // None of that disarmed anything.
637 _, err := s.db.Exec(`DELETE FROM apps`)
638 requireDenied(t, "after the loophole attempts", err)
639 if n := countWhere(t, s, "apps", "1=1"); n != 1 {
640 t.Fatalf("apps rows = %d, want 1", n)
641 }
642}
643
644func TestInspectClaimSQL(t *testing.T) {
645 ok := []struct {
646 q string
647 write bool
648 }{
649 {`SELECT 1`, false},
650 {`SELECT ';'`, false},
651 {`SELECT 'a' || "b" FROM t -- trailing; comment`, false},
652 {`SELECT 1;`, false},
653 {` select * from apps where app = ? ; `, false},
654 {`WITH x AS (SELECT 1) SELECT * FROM x`, false},
655 {`INSERT INTO t VALUES ('temp')`, true},
656 {`INSERT OR REPLACE INTO t VALUES (1)`, true},
657 {`UPDATE t SET a = 1`, true},
658 {`DELETE FROM t`, true},
659 {`WITH x AS (SELECT 1) DELETE FROM t WHERE a IN x`, true},
660 {`SELECT replace(a, 'x', 'y') FROM t`, true}, // conservative
661 {`INSERT INTO t VALUES (1); SELECT 2`, true},
662 }
663 for _, c := range ok {
664 info, err := inspectClaimSQL(c.q)
665 if err != nil {
666 t.Errorf("%q: %v", c.q, err)
667 continue
668 }
669 if info.write != c.write {
670 t.Errorf("%q: write = %v, want %v", c.q, info.write, c.write)
671 }
672 }
673 for _, q := range []string{
674 `PRAGMA user_version`, `CREATE INDEX i ON t (a)`, `SELECT 1; VACUUM`, `SELECT * FROM claim_permit`,
675 `SELECT 'claim_permit'`, `SELECT * FROM "Claim_Permit"`, `SELECT * FROM sqlite_master`, `SELECT load_extension('x')`,
676 } {
677 if _, err := inspectClaimSQL(q); !errors.Is(err, ErrClaimSQLForbidden) {
678 t.Errorf("%q: err = %v, want ErrClaimSQLForbidden", q, err)
679 }
680 }
681}
682
683// ---- the census --------------------------------------------------------------
684
685func TestClaimSQLTableCensus(t *testing.T) {
686 s := openGatedStore(t)
687 present, err := claimSQLCensus(s.bootDB(), claimSQLTables)
688 if err != nil {
689 t.Fatal(err)
690 }
691 // The default schema really is covered, and every app-referencing table
692 // is gated: re-derive that from sqlite_master and pragma_table_info.
693 rows, err := s.bootDB().Query(`SELECT m.name, p.name FROM main.sqlite_master m, pragma_table_info(m.name) p WHERE m.type = 'table' AND m.name NOT LIKE 'sqlite\_%' ESCAPE '\'`)
694 if err != nil {
695 t.Fatal(err)
696 }
697 type tc struct{ table, col string }
698 var all []tc
699 for rows.Next() {
700 var x tc
701 if err := rows.Scan(&x.table, &x.col); err != nil {
702 t.Fatal(err)
703 }
704 all = append(all, x)
705 }
706 rows.Close()
707 if len(all) == 0 {
708 t.Fatal("census saw no tables")
709 }
710 for _, x := range all {
711 spec, ok := claimSQLTables[x.table]
712 if !ok {
713 t.Errorf("table %q is not classified", x.table)
714 continue
715 }
716 if spec.NonApp != "" && claimAppish(x.col) {
717 t.Errorf("NonApp table %q has app column %q", x.table, x.col)
718 }
719 }
720 for _, name := range []string{"apps", "owners", "audit", "cli_tokens", "door_keys", "door_key_revisions", "pending_changes", "pending_confirms",
721 "mail_counts", "mail_sender_counts", "mail_events", "mail_event_hooks", "mail_event_deliveries"} {
722 if sp, ok := present[name]; !ok || sp.NonApp != "" {
723 t.Errorf("%s must be present and gated", name)
724 }
725 }
726 for name, sp := range claimSQLTables {
727 if sp.NonApp == "" && sp.App == "" {
728 t.Errorf("gated table %q has no App column", name)
729 }
730 }
731 var n int
732 if err := s.bootDB().QueryRow(`SELECT count(*) FROM main.sqlite_master WHERE type = 'trigger'`).Scan(&n); err != nil {
733 t.Fatal(err)
734 }
735 if n != 0 {
736 t.Fatalf("%d persistent triggers in the main schema; the gate's triggers must be TEMP only", n)
737 }
738}
739
740func TestClaimSQLCensusRefusesNewAppTable(t *testing.T) {
741 path := filepath.Join(t.TempDir(), "c.db")
742 _, err := openGated(path, path, claimSQLTables, func(s *Store) error {
743 if err := s.migrate(); err != nil {
744 return err
745 }
746 if err := s.migrateClaimAppIdentity(); err != nil {
747 return err
748 }
749 _, err := s.db.Exec(`CREATE TABLE sneaky_app_data (account_id INTEGER, app TEXT, v TEXT)`)
750 return err
751 })
752 if !errors.Is(err, ErrClaimSQLCensus) || !strings.Contains(err.Error(), "sneaky_app_data") {
753 t.Fatalf("err = %v, want a census refusal naming sneaky_app_data", err)
754 }
755}
756
757func TestClaimSQLCensusRefusals(t *testing.T) {
758 plain := openTestStore(t)
759 if err := plain.migrateClaimAppIdentity(); err != nil {
760 t.Fatal(err)
761 }
762 clone := func(edit func(map[string]claimSQLTable)) map[string]claimSQLTable {
763 m := map[string]claimSQLTable{}
764 for k, v := range claimSQLTables {
765 m[k] = v
766 }
767 edit(m)
768 return m
769 }
770 cases := []struct {
771 name string
772 setup string
773 edit func(map[string]claimSQLTable)
774 want string
775 }{
776 {"unclassified table", `CREATE TABLE brand_new (x)`, func(map[string]claimSQLTable) {}, "brand_new"},
777 {"view", `CREATE VIEW v AS SELECT 1`, func(map[string]claimSQLTable) {}, "view"},
778 {"persistent trigger", `CREATE TRIGGER trg AFTER INSERT ON users BEGIN SELECT 1; END`, func(map[string]claimSQLTable) {}, "trigger"},
779 {"reserved name", `CREATE TABLE claim_permit (x)`, func(m map[string]claimSQLTable) { m["claim_permit"] = claimSQLTable{NonApp: "x"} }, "reserved"},
780 {"app column hidden in a NonApp table", ``, func(m map[string]claimSQLTable) { m["owners"] = claimSQLTable{NonApp: "claimed harmless"} }, "app column"},
781 {"app_id not enforced", ``, func(m map[string]claimSQLTable) { m["apps"] = claimSQLTable{Account: "account_id", App: "app"} }, "app_id"},
782 {"missing column", ``, func(m map[string]claimSQLTable) { m["audit"] = claimSQLTable{Account: "no_such", App: "app"} }, "no_such"},
783 {"empty spec", ``, func(m map[string]claimSQLTable) { m["users"] = claimSQLTable{} }, "no App column"},
784 }
785 for _, c := range cases {
786 t.Run(c.name, func(t *testing.T) {
787 s := openTestStore(t)
788 if err := s.migrateClaimAppIdentity(); err != nil {
789 t.Fatal(err)
790 }
791 if c.setup != "" {
792 if _, err := s.db.Exec(c.setup); err != nil {
793 t.Fatal(err)
794 }
795 }
796 _, err := claimSQLCensus(s.bootDB(), clone(c.edit))
797 if !errors.Is(err, ErrClaimSQLCensus) || !strings.Contains(err.Error(), c.want) {
798 t.Fatalf("err = %v, want a census refusal mentioning %q", err, c.want)
799 }
800 })
801 }
802 // The unmodified schema passes on an unarmed store.
803 if _, err := claimSQLCensus(plain.bootDB(), claimSQLTables); err != nil {
804 t.Fatal(err)
805 }
806}
807
808// ---- REPLACE and cascade -----------------------------------------------------
809
810var customGateTables = map[string]claimSQLTable{
811 "fam_parent": {Account: "account_id", App: "app"},
812 "fam_child": {Account: "account_id", App: "app"},
813 "fam_uniq": {Account: "account_id", App: "app"},
814}
815
816const (
817 famParentDDL = `CREATE TABLE fam_parent (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, app TEXT NOT NULL)`
818 famChildDDL = `CREATE TABLE fam_child (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, app TEXT NOT NULL, pid INTEGER NOT NULL REFERENCES fam_parent(id) ON DELETE CASCADE)`
819 famUniqDDL = `CREATE TABLE fam_uniq (account_id INTEGER NOT NULL, app TEXT NOT NULL, k TEXT NOT NULL UNIQUE, v TEXT)`
820)
821
822var (
823 scopeA = claimSQLScope{AccountID: 1, App: "a", Mode: claimModeEffect}
824 scopeB = claimSQLScope{AccountID: 1, App: "b", Mode: claimModeEffect}
825)
826
827func TestClaimSQLReplaceImplicitDeleteIsGated(t *testing.T) {
828 s := openGatedCustom(t, customGateTables, famUniqDDL)
829 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeB}, allowAllClaimSQL, func(v *Store) error {
830 _, err := v.db.Exec(`INSERT INTO fam_uniq (account_id, app, k, v) VALUES (1, 'b', 'k', 'old')`)
831 return err
832 })
833 if err != nil {
834 t.Fatal(err)
835 }
836 // A's own row passes the INSERT check, but REPLACE would implicitly delete
837 // B's row: only recursive_triggers makes that visible to the DELETE trigger.
838 err = s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeA}, allowAllClaimSQL, func(v *Store) error {
839 _, err := v.db.Exec(`INSERT OR REPLACE INTO fam_uniq (account_id, app, k, v) VALUES (1, 'a', 'k', 'new')`)
840 requireDenied(t, "replace over another app's row", err)
841 return nil
842 })
843 if err != nil {
844 t.Fatal(err)
845 }
846 if n := countWhere(t, s, "fam_uniq", "app = 'b' AND v = 'old'"); n != 1 {
847 t.Fatal("REPLACE deleted another app's row")
848 }
849 // Control: with both scopes the same statement is allowed.
850 err = s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeA, scopeB}, allowAllClaimSQL, func(v *Store) error {
851 _, err := v.db.Exec(`INSERT OR REPLACE INTO fam_uniq (account_id, app, k, v) VALUES (1, 'a', 'k', 'new')`)
852 return err
853 })
854 if err != nil {
855 t.Fatal(err)
856 }
857 if n := countWhere(t, s, "fam_uniq", "app = 'a' AND v = 'new'"); n != 1 {
858 t.Fatal("control REPLACE did not land")
859 }
860}
861
862func TestClaimSQLCascadeDeleteIsGated(t *testing.T) {
863 s := openGatedCustom(t, customGateTables, famParentDDL, famChildDDL)
864 both := []claimSQLScope{scopeA, scopeB}
865 err := s.withClaimSQL(gateCtx(t), both, allowAllClaimSQL, func(v *Store) error {
866 for _, q := range []string{
867 `INSERT INTO fam_parent (id, account_id, app) VALUES (1, 1, 'a')`,
868 // Owned by B, but cascade-attached to A's parent.
869 `INSERT INTO fam_child (id, account_id, app, pid) VALUES (1, 1, 'b', 1)`,
870 } {
871 if _, err := v.db.Exec(q); err != nil {
872 return err
873 }
874 }
875 return nil
876 })
877 if err != nil {
878 t.Fatal(err)
879 }
880 err = s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeA}, allowAllClaimSQL, func(v *Store) error {
881 _, err := v.db.Exec(`DELETE FROM fam_parent WHERE id = 1`)
882 requireDenied(t, "cascade into another app's rows", err)
883 return nil
884 })
885 if err != nil {
886 t.Fatal(err)
887 }
888 if countWhere(t, s, "fam_parent", "id = 1") != 1 || countWhere(t, s, "fam_child", "id = 1") != 1 {
889 t.Fatal("cascade removed rows the permit does not cover")
890 }
891 // Control: with both scopes the cascade is allowed.
892 err = s.withClaimSQL(gateCtx(t), both, allowAllClaimSQL, func(v *Store) error {
893 _, err := v.db.Exec(`DELETE FROM fam_parent WHERE id = 1`)
894 return err
895 })
896 if err != nil {
897 t.Fatal(err)
898 }
899 if countWhere(t, s, "fam_child", "1=1") != 0 {
900 t.Fatal("control cascade did not run (foreign keys off?)")
901 }
902}
903
904// A connection opened before arming must not survive it: every connection the
905// pool hands out afterwards is armed.
906func TestClaimSQLEveryConnectionIsArmed(t *testing.T) {
907 s := openGatedStore(t)
908 seedGateApp(t, s)
909 for i := 0; i < 3; i++ {
910 conn, err := s.rawPool.Conn(gateCtx(t))
911 if err != nil {
912 t.Fatal(err)
913 }
914 _, err = conn.ExecContext(gateCtx(t), `DELETE FROM apps`)
915 requireDenied(t, "fresh pool connection", err)
916 conn.Close()
917 // Force a new physical connection next round.
918 s.rawPool.SetMaxIdleConns(0)
919 s.rawPool.SetMaxIdleConns(2)
920 }
921}
922
923// ---- permit modes ------------------------------------------------------------
924
925func scopeMode(sc claimSQLScope, mode string) claimSQLScope { sc.Mode = mode; return sc }
926
927// owners rows name no account, so no permit of any mode may write them.
928func TestClaimSQLOwnersRefusedUnderEveryPermit(t *testing.T) {
929 s := openGatedStore(t)
930 seedGateApp(t, s)
931 for _, mode := range []string{claimModeEffect, claimModePurge, claimModeRestore, claimModeTrash} {
932 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, mode)}, allowAllClaimSQL, func(v *Store) error {
933 _, err := v.db.Exec(`INSERT INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 1)`)
934 requireDenied(t, mode+" owners insert", err)
935 _, err = v.db.Exec(`REPLACE INTO owners (address, app, created_at) VALUES ('p@x', 'blog', 2)`)
936 requireDenied(t, mode+" owners replace", err)
937 return nil
938 })
939 if err != nil {
940 t.Fatal(err)
941 }
942 }
943 if n := countWhere(t, s, "owners", "1=1"); n != 0 {
944 t.Fatalf("owners rows = %d, want 0", n)
945 }
946}
947
948// seedTerminalWorld adds, under effect permits: gateScope's row trashed at
949// 100 with restoring set, an unidentified (empty app_id) row "blog8" in
950// account 8, a door-key revision, a mail event and an app token.
951func seedTerminalWorld(t *testing.T, s *Store) {
952 t.Helper()
953 seedGateApp(t, s)
954 if _, err := s.db.Exec(`INSERT INTO accounts (id, sqid, name, created_at) VALUES (8, 'acct8', 'eight', 1)`); err != nil {
955 t.Fatal(err)
956 }
957 legacy := claimSQLScope{AccountID: 8, App: "blog8", AppID: "app-L", Mode: claimModeEffect}
958 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{gateScope, legacy}, allowAllClaimSQL, func(v *Store) error {
959 for _, q := range []string{
960 `UPDATE apps SET deleted_at = 100, restoring = 1 WHERE account_id = 7 AND app = 'blog'`,
961 `INSERT INTO apps (app, account_id, created_by, created_at) VALUES ('blog8', 8, 'x', 1)`,
962 `INSERT INTO door_key_revisions (account_id, app, revision) VALUES (7, 'blog', 3)`,
963 `INSERT INTO mail_events (account, app, notice, recipient, message_id, type, at, received_at) VALUES ('acct7', 'blog', 'n', 'r', 'm', 'Bounce', 1, 1)`,
964 `INSERT INTO cli_tokens (token_hash, address, created_at, expires_at, kind, account_id, app) VALUES ('ht', 'a', 1, 0, 'app', 7, 'blog')`,
965 } {
966 if _, err := v.db.Exec(q); err != nil {
967 return fmt.Errorf("%s: %w", q, err)
968 }
969 }
970 return nil
971 })
972 if err != nil {
973 t.Fatal(err)
974 }
975}
976
977// Terminal grants: their callbacks try every statement outside their family
978// (INSERT, a column they do not own, DELETE, re-binding app_id, adopting an
979// unidentified row) and SQLite refuses each one, through Exec, prepared
980// statements, Query ... RETURNING and transactions.
981func TestClaimSQLTerminalModesRefuseOutsideTheirFamily(t *testing.T) {
982 for _, mode := range []string{claimModeRestore, claimModeTrash, claimModePurge} {
983 t.Run(mode, func(t *testing.T) {
984 s := openGatedStore(t)
985 seedTerminalWorld(t, s)
986 ctx := gateCtx(t)
987 unbound := claimSQLScope{AccountID: 8, App: "blog8", AppID: "app-U", Mode: mode}
988 err := s.withClaimSQL(ctx, []claimSQLScope{scopeMode(gateScope, mode), unbound}, allowAllClaimSQL, func(v *Store) error {
989 _, err := v.db.Exec(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'forged', 1, 7)`)
990 requireDenied(t, "insert", err)
991 _, err = v.db.Exec(`INSERT INTO apps (app, account_id, created_by, created_at, app_id) VALUES ('blog2', 7, 'x', 1, 'app-A')`)
992 requireDenied(t, "insert apps", err)
993 err = drainRows(v.db.Query(`INSERT INTO audit (app, address, action, created_at, account_id) VALUES ('blog', 'p', 'ret', 1, 7) RETURNING id`))
994 requireDenied(t, "insert returning", err)
995 _, err = v.db.Exec(`UPDATE apps SET placement = 'evil' WHERE account_id = 7 AND app = 'blog'`)
996 requireDenied(t, "update a column the mode does not own", err)
997 st, perr := v.db.Prepare(`UPDATE apps SET region = 'evil' WHERE account_id = 7 AND app = 'blog'`)
998 if perr != nil {
999 t.Fatal(perr)
1000 }
1001 _, err = st.Exec()
1002 st.Close()
1003 requireDenied(t, "prepared update of a foreign column", err)
1004 _, err = v.db.Exec(`UPDATE apps SET app_id = 'app-Z' WHERE account_id = 7 AND app = 'blog'`)
1005 requireDenied(t, "re-binding app_id", err)
1006 _, err = v.db.Exec(`UPDATE audit SET action = 'forged' WHERE app = 'blog'`)
1007 requireDenied(t, "update of another table", err)
1008 // Adoption: the unidentified row of a name the permit names.
1009 _, err = v.db.Exec(`UPDATE apps SET app_id = 'app-U' WHERE account_id = 8 AND app = 'blog8'`)
1010 requireDenied(t, "adopting an unidentified row", err)
1011 _, err = v.db.Exec(`UPDATE apps SET deleted_at = 5 WHERE account_id = 8 AND app = 'blog8'`)
1012 requireDenied(t, "projecting onto an unidentified row", err)
1013 _, err = v.db.Exec(`DELETE FROM apps WHERE account_id = 8 AND app = 'blog8'`)
1014 requireDenied(t, "deleting an unidentified row", err)
1015 if mode != claimModePurge {
1016 _, err = v.db.Exec(`DELETE FROM apps WHERE account_id = 7 AND app = 'blog'`)
1017 requireDenied(t, "delete", err)
1018 _, err = v.db.Exec(`DELETE FROM audit WHERE account_id = 7`)
1019 requireDenied(t, "delete side rows", err)
1020 tx, terr := v.db.Begin()
1021 if terr != nil {
1022 t.Fatal(terr)
1023 }
1024 _, err = tx.Exec(`DELETE FROM door_keys WHERE account_id = 7`)
1025 requireDenied(t, "tx delete", err)
1026 tx.Rollback()
1027 } else {
1028 _, err = v.db.Exec(`UPDATE door_key_revisions SET revision = revision - 1 WHERE account_id = 7`)
1029 requireDenied(t, "lowering the door-key revision", err)
1030 _, err = v.db.Exec(`UPDATE door_keys SET label = 'x' WHERE account_id = 7`)
1031 requireDenied(t, "door key label", err)
1032 }
1033 return nil
1034 })
1035 if err != nil {
1036 t.Fatal(err)
1037 }
1038 if countWhere(t, s, "audit", "action <> 'seed'") != 0 || countWhere(t, s, "audit", "action = 'seed'") != 1 ||
1039 countWhere(t, s, "apps", "placement <> '' OR region <> ''") != 0 ||
1040 countWhere(t, s, "apps", "app = 'blog8' AND app_id = '' AND deleted_at = 0") != 1 || countWhere(t, s, "apps", "app = 'blog' AND app_id = 'app-A'") != 1 {
1041 t.Fatal("a terminal permit wrote outside its family")
1042 }
1043 })
1044 }
1045}
1046
1047// Each terminal mode's own family passes, on its own incarnation only.
1048func TestClaimSQLTerminalModesAllowTheirFamily(t *testing.T) {
1049 t.Run("trash", func(t *testing.T) {
1050 s := openGatedStore(t)
1051 seedTerminalWorld(t, s)
1052 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, claimModeTrash)}, allowAllClaimSQL, func(v *Store) error {
1053 _, err := v.SetClaimRestoring(gateAcct, "blog", "app-A", false)
1054 return err
1055 })
1056 if err != nil || countWhere(t, s, "apps", "app = 'blog' AND restoring = 0") != 1 {
1057 t.Fatalf("trash projection: %v", err)
1058 }
1059 })
1060 t.Run("restore", func(t *testing.T) {
1061 s := openGatedStore(t)
1062 seedTerminalWorld(t, s)
1063 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, claimModeRestore)}, allowAllClaimSQL, func(v *Store) error {
1064 if changed, err := v.FinishClaimRestoreRow(gateAcct, "blog", "app-A", 99, time.Unix(1000, 0)); err != nil || changed {
1065 return fmt.Errorf("a receipt for another trash restored the row: %v %v", changed, err)
1066 }
1067 _, err := v.FinishClaimRestoreRow(gateAcct, "blog", "app-A", 100, time.Unix(1000, 0))
1068 return err
1069 })
1070 if err != nil || countWhere(t, s, "apps", "app = 'blog' AND deleted_at = 0 AND restoring = 0") != 1 {
1071 t.Fatalf("restore projection: %v", err)
1072 }
1073 })
1074 t.Run("purge", func(t *testing.T) {
1075 s := openGatedStore(t)
1076 seedTerminalWorld(t, s)
1077 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{scopeMode(gateScope, claimModePurge)}, allowAllClaimSQL, func(v *Store) error {
1078 if err := v.BindClaimAppOperation(gateAcct, "blog", "app-A", "purge-1", 9); err != nil {
1079 return err
1080 }
1081 if err := v.MarkClaimAppRetired(gateAcct, "blog", "app-A", time.Unix(1000, 0)); err != nil {
1082 return err
1083 }
1084 return v.PurgeClaimAppRow(gateCtx(t), gateAcct, "blog", "app-A", "purge-1")
1085 })
1086 if err != nil {
1087 t.Fatalf("purge replay: %v", err)
1088 }
1089 if countWhere(t, s, "apps", "app = 'blog'") != 0 || countWhere(t, s, "mail_events", "app = 'blog'") != 0 || countWhere(t, s, "cli_tokens", "app = 'blog'") != 0 ||
1090 countWhere(t, s, "door_keys", "app = 'blog' AND revoked_at = 0") != 0 || countWhere(t, s, "door_key_revisions", "app = 'blog' AND revision = 4") != 1 {
1091 t.Fatal("purge replay left the retired incarnation's rows")
1092 }
1093 if countWhere(t, s, "apps", "app = 'blog8'") != 1 {
1094 t.Fatal("purge reached another app")
1095 }
1096 })
1097}
1098
1099// A purge permit for a retired incarnation cannot reach a successor
1100// incarnation's rows: not its apps row (other app_id), and not the
1101// app-keyed side rows while a successor apps row exists.
1102func TestClaimSQLPurgeModeSparesSuccessor(t *testing.T) {
1103 s := openGatedStore(t)
1104 seedTerminalWorld(t, s)
1105 retired := claimSQLScope{AccountID: gateAcct, App: "blog", AppID: "app-OLD", Mode: claimModePurge}
1106 err := s.withClaimSQL(gateCtx(t), []claimSQLScope{retired}, allowAllClaimSQL, func(v *Store) error {
1107 _, err := v.db.Exec(`DELETE FROM apps WHERE account_id = 7 AND app = 'blog'`)
1108 requireDenied(t, "successor apps row", err)
1109 _, err = v.db.Exec(`DELETE FROM mail_events WHERE account = 'acct7' AND app = 'blog'`)
1110 requireDenied(t, "side rows while a successor row exists", err)
1111 _, err = v.db.Exec(`UPDATE door_keys SET revoked_at = 5 WHERE account_id = 7 AND app = 'blog'`)
1112 requireDenied(t, "successor door keys", err)
1113 _, err = v.db.Exec(`UPDATE apps SET deleted_at = 1 WHERE account_id = 7 AND app = 'blog'`)
1114 requireDenied(t, "retiring the successor row", err)
1115 return nil
1116 })
1117 if err != nil {
1118 t.Fatal(err)
1119 }
1120 if countWhere(t, s, "apps", "app = 'blog'") != 1 || countWhere(t, s, "mail_events", "app = 'blog'") != 1 || countWhere(t, s, "door_keys", "app = 'blog' AND revoked_at = 0") != 1 {
1121 t.Fatal("a retired incarnation's purge permit reached its successor")
1122 }
1123}
1124