Skip to main content

max / makenotwork

13.0 KB · 386 lines History Blame Raw
1 //! DB-layer contract tests for `db::items::bulk`, batch item operations.
2 //!
3 //! Audit Run 16 flagged `bulk` as a Testing cold spot. Every bulk mutation is
4 //! ownership-scoped via `project_id IN (SELECT id FROM projects WHERE user_id =
5 //! $n)`, an IDOR seal that was only exercised through the HTTP surface. These
6 //! pin that seal at the DB layer (a non-owner's bulk op touches zero rows), plus
7 //! the soft-delete idempotency, the tag ON CONFLICT dedup, `move_item`'s
8 //! sort-order swap, and `duplicate_item`'s savepoint slug-collision retry (the
9 //! Phase 3 TOCTOU fix): two concurrent duplicates of one source get distinct
10 //! slugs and both succeed.
11
12 use crate::harness::db::TestDb;
13 use makenotwork::db::items;
14 use makenotwork::db::{ItemId, PriceCents, ProjectId, TagId, UserId};
15
16 async fn seed_user(pool: &sqlx::PgPool, username: &str) -> UserId {
17 let hash = makenotwork::auth::hash_password("password123").expect("hash");
18 sqlx::query_scalar::<_, UserId>(
19 "INSERT INTO users (username, email, password_hash, email_verified)
20 VALUES ($1, $2, $3, true) RETURNING id",
21 )
22 .bind(username)
23 .bind(format!("{username}@test.com"))
24 .bind(&hash)
25 .fetch_one(pool)
26 .await
27 .expect("seed user")
28 }
29
30 async fn seed_project(pool: &sqlx::PgPool, user: UserId, slug: &str) -> ProjectId {
31 sqlx::query_scalar::<_, ProjectId>(
32 "INSERT INTO projects (user_id, slug, title) VALUES ($1, $2, 'P') RETURNING id",
33 )
34 .bind(user)
35 .bind(slug)
36 .fetch_one(pool)
37 .await
38 .expect("seed project")
39 }
40
41 /// Seed an item with an explicit sort_order and a unique slug.
42 async fn seed_item(pool: &sqlx::PgPool, project: ProjectId, slug: &str, sort: i32) -> ItemId {
43 // Explicit is_public = false: the column defaults to true, but these tests
44 // assert publish/unpublish transitions, so items start unpublished.
45 sqlx::query_scalar::<_, ItemId>(
46 "INSERT INTO items (project_id, title, item_type, price_cents, slug, sort_order, is_public)
47 VALUES ($1, $2, 'digital', 1000, $3, $4, false) RETURNING id",
48 )
49 .bind(project)
50 .bind(format!("Item {slug}"))
51 .bind(slug)
52 .bind(sort)
53 .fetch_one(pool)
54 .await
55 .expect("seed item")
56 }
57
58 async fn seed_tag(pool: &sqlx::PgPool, slug: &str) -> TagId {
59 sqlx::query_scalar::<_, TagId>(
60 "INSERT INTO tags (name, slug, path) VALUES ($1, $2, $2) RETURNING id",
61 )
62 .bind(slug)
63 .bind(slug)
64 .fetch_one(pool)
65 .await
66 .expect("seed tag")
67 }
68
69 async fn is_public(pool: &sqlx::PgPool, item: ItemId) -> bool {
70 sqlx::query_scalar::<_, bool>("SELECT is_public FROM items WHERE id = $1")
71 .bind(item)
72 .fetch_one(pool)
73 .await
74 .unwrap()
75 }
76
77 async fn sort_order(pool: &sqlx::PgPool, item: ItemId) -> i32 {
78 sqlx::query_scalar::<_, i32>("SELECT sort_order FROM items WHERE id = $1")
79 .bind(item)
80 .fetch_one(pool)
81 .await
82 .unwrap()
83 }
84
85 async fn price_of(pool: &sqlx::PgPool, item: ItemId) -> i32 {
86 sqlx::query_scalar::<_, i32>("SELECT price_cents FROM items WHERE id = $1")
87 .bind(item)
88 .fetch_one(pool)
89 .await
90 .unwrap()
91 }
92
93 // ── ownership scoping (IDOR seal) ────────────────────────────────────────────
94
95 #[tokio::test]
96 async fn bulk_publish_affects_only_owned_items_and_clears_schedule() {
97 let db = TestDb::new().await;
98 let owner = seed_user(&db.pool, "blk_pub_owner").await;
99 let attacker = seed_user(&db.pool, "blk_pub_attacker").await;
100 let project = seed_project(&db.pool, owner, "blk-pub").await;
101 let a = seed_item(&db.pool, project, "pa", 0).await;
102 let b = seed_item(&db.pool, project, "pb", 1).await;
103 // Give one a scheduled publish_at to prove bulk_publish clears it.
104 sqlx::query("UPDATE items SET publish_at = NOW() + INTERVAL '1 day' WHERE id = $1")
105 .bind(a)
106 .execute(&db.pool)
107 .await
108 .unwrap();
109
110 // A non-owner cannot publish the items, zero rows, nothing changes.
111 let hijack = items::bulk_publish(&db.pool, &[a, b], project, attacker)
112 .await
113 .unwrap();
114 assert_eq!(hijack, 0, "a non-owner bulk_publish must touch no rows");
115 assert!(!is_public(&db.pool, a).await);
116
117 // The owner publishes both and the schedule is cleared.
118 let n = items::bulk_publish(&db.pool, &[a, b], project, owner)
119 .await
120 .unwrap();
121 assert_eq!(n, 2);
122 assert!(is_public(&db.pool, a).await && is_public(&db.pool, b).await);
123 let sched: Option<chrono::DateTime<chrono::Utc>> =
124 sqlx::query_scalar("SELECT publish_at FROM items WHERE id = $1")
125 .bind(a)
126 .fetch_one(&db.pool)
127 .await
128 .unwrap();
129 assert!(
130 sched.is_none(),
131 "bulk_publish clears any scheduled publish_at"
132 );
133 }
134
135 #[tokio::test]
136 async fn bulk_unpublish_is_owner_scoped() {
137 let db = TestDb::new().await;
138 let owner = seed_user(&db.pool, "blk_unpub_owner").await;
139 let attacker = seed_user(&db.pool, "blk_unpub_attacker").await;
140 let project = seed_project(&db.pool, owner, "blk-unpub").await;
141 let item = seed_item(&db.pool, project, "ua", 0).await;
142 items::bulk_publish(&db.pool, &[item], project, owner)
143 .await
144 .unwrap();
145
146 assert_eq!(
147 items::bulk_unpublish(&db.pool, &[item], project, attacker)
148 .await
149 .unwrap(),
150 0
151 );
152 assert!(
153 is_public(&db.pool, item).await,
154 "a non-owner cannot unpublish"
155 );
156 assert_eq!(
157 items::bulk_unpublish(&db.pool, &[item], project, owner)
158 .await
159 .unwrap(),
160 1
161 );
162 assert!(!is_public(&db.pool, item).await);
163 }
164
165 #[tokio::test]
166 async fn bulk_delete_soft_deletes_once_and_is_owner_scoped() {
167 let db = TestDb::new().await;
168 let owner = seed_user(&db.pool, "blk_del_owner").await;
169 let attacker = seed_user(&db.pool, "blk_del_attacker").await;
170 let project = seed_project(&db.pool, owner, "blk-del").await;
171 let item = seed_item(&db.pool, project, "da", 0).await;
172
173 assert_eq!(
174 items::bulk_delete(&db.pool, &[item], project, attacker)
175 .await
176 .unwrap(),
177 0,
178 "a non-owner cannot delete"
179 );
180
181 // First delete soft-deletes; the second is a no-op (deleted_at IS NULL guard).
182 assert_eq!(
183 items::bulk_delete(&db.pool, &[item], project, owner)
184 .await
185 .unwrap(),
186 1
187 );
188 assert_eq!(
189 items::bulk_delete(&db.pool, &[item], project, owner)
190 .await
191 .unwrap(),
192 0,
193 "re-deleting an already-deleted item is a no-op"
194 );
195 let (deleted, public): (bool, bool) =
196 sqlx::query_as("SELECT deleted_at IS NOT NULL, is_public FROM items WHERE id = $1")
197 .bind(item)
198 .fetch_one(&db.pool)
199 .await
200 .unwrap();
201 assert!(
202 deleted && !public,
203 "soft-delete stamps deleted_at and unpublishes"
204 );
205 }
206
207 #[tokio::test]
208 async fn bulk_update_price_is_owner_scoped() {
209 let db = TestDb::new().await;
210 let owner = seed_user(&db.pool, "blk_price_owner").await;
211 let attacker = seed_user(&db.pool, "blk_price_attacker").await;
212 let project = seed_project(&db.pool, owner, "blk-price").await;
213 let item = seed_item(&db.pool, project, "pra", 0).await;
214
215 let new_price = PriceCents::new(4200).expect("valid price");
216 assert_eq!(
217 items::bulk_update_price(&db.pool, &[item], project, attacker, new_price)
218 .await
219 .unwrap(),
220 0
221 );
222 assert_eq!(
223 price_of(&db.pool, item).await,
224 1000,
225 "a non-owner cannot reprice"
226 );
227
228 assert_eq!(
229 items::bulk_update_price(&db.pool, &[item], project, owner, new_price)
230 .await
231 .unwrap(),
232 1
233 );
234 assert_eq!(price_of(&db.pool, item).await, 4200);
235 }
236
237 #[tokio::test]
238 async fn bulk_add_tag_dedups_and_is_owner_scoped() {
239 let db = TestDb::new().await;
240 let owner = seed_user(&db.pool, "blk_tag_owner").await;
241 let attacker = seed_user(&db.pool, "blk_tag_attacker").await;
242 let project = seed_project(&db.pool, owner, "blk-tag").await;
243 let a = seed_item(&db.pool, project, "ta", 0).await;
244 let b = seed_item(&db.pool, project, "tb", 1).await;
245 let tag = seed_tag(&db.pool, "genre").await;
246
247 assert_eq!(
248 items::bulk_add_tag(&db.pool, &[a, b], project, attacker, tag)
249 .await
250 .unwrap(),
251 0,
252 "a non-owner cannot tag"
253 );
254
255 // First tag both; a repeat is a no-op via ON CONFLICT (item_id, tag_id).
256 assert_eq!(
257 items::bulk_add_tag(&db.pool, &[a, b], project, owner, tag)
258 .await
259 .unwrap(),
260 2
261 );
262 assert_eq!(
263 items::bulk_add_tag(&db.pool, &[a, b], project, owner, tag)
264 .await
265 .unwrap(),
266 0,
267 "re-tagging is idempotent"
268 );
269 }
270
271 // ── move_item reorder ────────────────────────────────────────────────────────
272
273 #[tokio::test]
274 async fn move_item_swaps_sort_order_within_the_project() {
275 let db = TestDb::new().await;
276 let owner = seed_user(&db.pool, "blk_move_owner").await;
277 let project = seed_project(&db.pool, owner, "blk-move").await;
278 let first = seed_item(&db.pool, project, "m0", 0).await;
279 let middle = seed_item(&db.pool, project, "m1", 1).await;
280 let last = seed_item(&db.pool, project, "m2", 2).await;
281
282 // Move the middle item up: it swaps with the first.
283 items::move_item(&db.pool, project, owner, middle, "up")
284 .await
285 .unwrap();
286 assert_eq!(sort_order(&db.pool, middle).await, 0);
287 assert_eq!(sort_order(&db.pool, first).await, 1);
288 assert_eq!(
289 sort_order(&db.pool, last).await,
290 2,
291 "the untouched item keeps its slot"
292 );
293
294 // Moving the top item up again is a no-op (already first).
295 items::move_item(&db.pool, project, owner, middle, "up")
296 .await
297 .unwrap();
298 assert_eq!(sort_order(&db.pool, middle).await, 0);
299 }
300
301 // ── duplicate_item, metadata copy + slug-collision retry ────────────────────
302
303 #[tokio::test]
304 async fn duplicate_item_makes_a_draft_copy_with_metadata() {
305 let db = TestDb::new().await;
306 let owner = seed_user(&db.pool, "blk_dup_owner").await;
307 let attacker = seed_user(&db.pool, "blk_dup_attacker").await;
308 let project = seed_project(&db.pool, owner, "blk-dup").await;
309 let source = seed_item(&db.pool, project, "orig", 0).await;
310 let tag = seed_tag(&db.pool, "dup-tag").await;
311 sqlx::query("INSERT INTO item_tags (item_id, tag_id) VALUES ($1, $2)")
312 .bind(source)
313 .bind(tag)
314 .execute(&db.pool)
315 .await
316 .unwrap();
317 sqlx::query("UPDATE items SET is_public = true WHERE id = $1")
318 .bind(source)
319 .execute(&db.pool)
320 .await
321 .unwrap();
322
323 // A non-owner cannot duplicate (ownership verified via project subquery).
324 assert!(
325 items::duplicate_item(&db.pool, source, attacker)
326 .await
327 .is_err()
328 );
329
330 let copy = items::duplicate_item(&db.pool, source, owner)
331 .await
332 .unwrap();
333 assert!(
334 copy.title.starts_with("Copy of "),
335 "the copy is titled 'Copy of ...'"
336 );
337 assert!(
338 !copy.is_public,
339 "the copy is a private draft regardless of the source"
340 );
341 let tag_count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM item_tags WHERE item_id = $1")
342 .bind(copy.id)
343 .fetch_one(&db.pool)
344 .await
345 .unwrap();
346 assert_eq!(tag_count, 1, "tags are carried onto the copy");
347 }
348
349 /// The Phase 3 slug-TOCTOU fix: two duplicates of the same source race for the
350 /// base slug. The savepoint retry makes the unique index the arbiter, so both
351 /// succeed with distinct slugs rather than one 500-ing on a raw 23505.
352 #[tokio::test]
353 async fn concurrent_duplicates_get_distinct_slugs() {
354 let db = TestDb::new().await;
355 let owner = seed_user(&db.pool, "blk_dupr_owner").await;
356 let project = seed_project(&db.pool, owner, "blk-dupr").await;
357 let source = seed_item(&db.pool, project, "racey", 0).await;
358
359 let p1 = db.pool.clone();
360 let p2 = db.pool.clone();
361 let (a, b) = tokio::join!(
362 tokio::spawn(async move { items::duplicate_item(&p1, source, owner).await }),
363 tokio::spawn(async move { items::duplicate_item(&p2, source, owner).await }),
364 );
365 let a = a.unwrap().expect("first duplicate succeeds");
366 let b = b
367 .unwrap()
368 .expect("second duplicate succeeds under contention");
369
370 let slug_a: String = sqlx::query_scalar("SELECT slug FROM items WHERE id = $1")
371 .bind(a.id)
372 .fetch_one(&db.pool)
373 .await
374 .unwrap();
375 let slug_b: String = sqlx::query_scalar("SELECT slug FROM items WHERE id = $1")
376 .bind(b.id)
377 .fetch_one(&db.pool)
378 .await
379 .unwrap();
380 assert_ne!(
381 slug_a, slug_b,
382 "concurrent duplicates must land on distinct slugs"
383 );
384 assert_ne!(a.id, b.id);
385 }
386