Skip to main content

max / makenotwork

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