dovenest

dovenest/server/src/db.rs
db.rs
⎇
Raw
1use std::path::Path;
2use std::sync::Arc;
3
4pub use api_types::Mode;
5use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
6use rusqlite::{Connection, OptionalExtension, params};
7
8const SCHEMA_VERSION: i64 = 8;
9
10/// SQL adapter for [`Mode`]. A newtype is needed because both the rusqlite
11/// traits and `Mode` are foreign to this crate.
12///
13/// The stored strings are unchanged ("rw"/"ro"), so old databases still read.
14struct SqlMode(Mode);
15
16impl FromSql for SqlMode {
17 fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
18 let s = v.as_str()?;
19 Mode::from_wire(s)
20 .map(SqlMode)
21 .ok_or_else(|| FromSqlError::Other(format!("unknown mode {s:?}").into()))
22 }
23}
24
25impl ToSql for SqlMode {
26 fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
27 Ok(ToSqlOutput::from(self.0.as_str()))
28 }
29}
30
31#[derive(Debug, Clone)]
32pub struct User {
33 pub id: i64,
34 pub name: String,
35 pub is_admin: bool,
36 /// Disabled users cannot sign in and their sessions are rejected.
37 pub active: bool,
38 /// Profile setting: single click opens entries (off = click selects).
39 pub single_click: bool,
40 /// Profile setting: show thumbnails in the grid.
41 pub thumbnails: bool,
42 /// Preferred UI language tag ("en", "de", "fr"); None = follow the
43 /// browser.
44 pub language: Option<String>,
45}
46
47#[derive(Debug, Clone)]
48pub struct RootRow {
49 pub id: i64,
50 /// Path relative to the server root; "." means the whole root.
51 pub path: String,
52 pub mode: Mode,
53}
54
55#[derive(Debug, Clone)]
56pub struct ShareRow {
57 pub id: i64,
58 pub token: String,
59 pub creator_id: i64,
60 /// Path of the shared item relative to the server root.
61 pub target: String,
62 pub is_file: bool,
63 pub mode: Mode,
64 pub created_at: String,
65 pub expires_at: Option<String>,
66 /// Argon2 hash of the share's password, when it has one. Resolve,
67 /// listing and download all stay locked until the visitor enters it and
68 /// gets an unlock cookie.
69 pub password_hash: Option<String>,
70}
71
72impl ShareRow {
73 pub fn is_expired(&self) -> bool {
74 match &self.expires_at {
75 Some(e) => chrono::DateTime::parse_from_rfc3339(e)
76 .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
77 .unwrap_or(false),
78 None => false,
79 }
80 }
81}
82
83/// Every query can fail, and every caller decides what to do about it.
84///
85/// Earlier versions swallowed read errors and returned a default (an empty
86/// root list, a count of 0). That turned a broken database into a plausible
87/// answer: "you have no folders" instead of an error. One contract now.
88pub type DbResult<T> = Result<T, rusqlite::Error>;
89
90#[derive(Clone)]
91pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
92
93impl std::fmt::Debug for Db {
94 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
95 f.debug_struct("Db").finish()
96 }
97}
98
99impl Db {
100 pub async fn open(path: &Path) -> anyhow::Result<Self> {
101 if let Some(parent) = path.parent()
102 && !parent.as_os_str().is_empty()
103 {
104 std::fs::create_dir_all(parent)?;
105 }
106 let conn = Connection::open(path)?;
107 conn.pragma_update(None, "journal_mode", "WAL")?;
108 // WAL plus NORMAL: fsync only at checkpoints. A crash can lose the
109 // last commits, never the database file.
110 conn.pragma_update(None, "synchronous", "NORMAL")?;
111 conn.pragma_update(None, "foreign_keys", "ON")?;
112 conn.pragma_update(None, "busy_timeout", "5000")?;
113 Self::migrate(&conn)?;
114 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
115 }
116
117 /// Open a fresh in-memory database (used by tests — no temp file needed).
118 pub async fn open_in_memory() -> anyhow::Result<Self> {
119 let conn = Connection::open_in_memory()?;
120 conn.pragma_update(None, "foreign_keys", "ON")?;
121 conn.pragma_update(None, "busy_timeout", "5000")?;
122 Self::migrate(&conn)?;
123 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
124 }
125
126 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
127 conn.execute(
128 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
129 [],
130 )?;
131 let version: i64 = conn
132 .query_row(
133 "SELECT value FROM meta WHERE key = 'schema_version'",
134 [],
135 |r| r.get::<_, String>(0),
136 )
137 .optional()?
138 .and_then(|v| v.parse().ok())
139 .unwrap_or(0);
140
141 if version < 1 {
142 conn.execute_batch(SCHEMA_V1)?;
143 }
144 if version < 2 {
145 // User management (M7): a disabled flag so admins can suspend
146 // accounts without deleting them.
147 conn.execute_batch("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1")?;
148 }
149 if version < 3 {
150 // Per-user profile settings: click-to-open mode. No migration
151 // from the old behaviour — everyone starts on the new default
152 // (off: single click selects, double click opens).
153 conn.execute_batch(
154 "ALTER TABLE users ADD COLUMN single_click INTEGER NOT NULL DEFAULT 0",
155 )?;
156 }
157 if version < 4 {
158 // Per-user UI language preference; NULL means "follow the
159 // browser".
160 conn.execute_batch("ALTER TABLE users ADD COLUMN language TEXT")?;
161 }
162 if version < 5 {
163 // The share list is queried by creator on every shares page.
164 conn.execute_batch(
165 "CREATE INDEX IF NOT EXISTS idx_shares_creator ON shares(creator_id)",
166 )?;
167 }
168 if version < 6 {
169 // Unlocks cascade with their share, which cascades with its
170 // creator's account.
171 conn.execute_batch(
172 "ALTER TABLE shares ADD COLUMN password_hash TEXT;
173 CREATE TABLE IF NOT EXISTS share_unlocks (
174 token TEXT PRIMARY KEY,
175 share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
176 created_at TEXT NOT NULL
177 );",
178 )?;
179 }
180 if version < 7 {
181 // `delete_share` cascades into share_unlocks, which is a full
182 // scan of that table without this.
183 conn.execute_batch(
184 "CREATE INDEX IF NOT EXISTS idx_share_unlocks_share
185 ON share_unlocks(share_id)",
186 )?;
187 }
188 if version < 8 {
189 // On by default, so `--cache` is the only step needed to get
190 // thumbnails.
191 conn.execute_batch(
192 "ALTER TABLE users ADD COLUMN thumbnails INTEGER NOT NULL DEFAULT 1",
193 )?;
194 }
195 conn.execute(
196 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
197 [SCHEMA_VERSION.to_string()],
198 )?;
199 Ok(())
200 }
201
202 // ---------- users ----------
203
204 pub async fn user_count(&self) -> DbResult<i64> {
205 let c = self.0.lock().await;
206 let mut stmt = c.prepare_cached("SELECT COUNT(*) FROM users")?;
207 stmt.query_row([], |r| r.get(0))
208 }
209
210 /// Create the first admin account with the whole root visible (read-write).
211 ///
212 /// `None` means a user already existed. The `WHERE NOT EXISTS` guard runs
213 /// inside the same transaction as the insert, so two concurrent first-boot
214 /// setups cannot both win; a caller's earlier `user_count` check is only
215 /// an optimization, not the guarantee.
216 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<Option<User>> {
217 let mut c = self.0.lock().await;
218 let tx = c.transaction()?;
219 let inserted = tx.execute(
220 "INSERT INTO users (name, pass_hash, is_admin, created_at)
221 SELECT ?1, ?2, 1, ?3 WHERE NOT EXISTS (SELECT 1 FROM users)",
222 params![name, pass_hash, now()],
223 )?;
224 if inserted == 0 {
225 return Ok(None); // dropping `tx` rolls back
226 }
227 let user_id = tx.last_insert_rowid();
228 tx.execute(
229 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
230 params![user_id],
231 )?;
232 tx.commit()?;
233 Ok(Some(User {
234 id: user_id,
235 name: name.to_string(),
236 is_admin: true,
237 active: true,
238 single_click: false,
239 thumbnails: true,
240 language: None,
241 }))
242 }
243
244 pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
245 // The guard is scoped to the query alone. Argon2 below is slow by
246 // design; holding the single connection lock across it would make one
247 // login serialize every other database access.
248 type UserRow = (i64, String, bool, String, bool, bool, bool, Option<String>);
249 let row: Option<UserRow> = {
250 let c = self.0.lock().await;
251 c.query_row(
252 "SELECT id, name, is_admin != 0, pass_hash, active != 0, single_click != 0,
253 thumbnails != 0, language
254 FROM users WHERE name = ?1",
255 [name],
256 |r| {
257 Ok((
258 r.get(0)?,
259 r.get(1)?,
260 r.get(2)?,
261 r.get(3)?,
262 r.get(4)?,
263 r.get(5)?,
264 r.get(6)?,
265 r.get(7)?,
266 ))
267 },
268 )
269 .optional()?
270 };
271 // An unknown or disabled name still pays for one Argon2 verify, so the
272 // response time does not reveal which names exist.
273 let (row, hash) = match row {
274 Some((id, name, is_admin, hash, active, single_click, thumbnails, language))
275 if active =>
276 {
277 (
278 Some((id, name, is_admin, single_click, thumbnails, language)),
279 hash,
280 )
281 }
282 _ => (None, DUMMY_HASH.clone()),
283 };
284 let ok = crate::auth::verify_password_async(password, &hash).await;
285 let Some((id, name, is_admin, single_click, thumbnails, language)) = row else {
286 return Ok(None);
287 };
288 Ok(ok.then_some(User {
289 id,
290 name,
291 is_admin,
292 active: true,
293 single_click,
294 thumbnails,
295 language,
296 }))
297 }
298
299 // ---------- sessions ----------
300
301 pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
302 let c = self.0.lock().await;
303 c.execute(
304 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
305 VALUES (?1, ?2, ?3, ?4)",
306 params![token, user_id, now(), now()],
307 )?;
308 Ok(())
309 }
310
311 pub async fn delete_session(&self, token: &str) -> DbResult<()> {
312 let c = self.0.lock().await;
313 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
314 Ok(())
315 }
316
317 /// The session's user plus that user's roots, in one round trip. Every
318 /// authenticated request needs both, so they are not two queries.
319 pub async fn session_user_with_roots(
320 &self,
321 token: &str,
322 ) -> DbResult<Option<(User, Vec<RootRow>)>> {
323 let c = self.0.lock().await;
324 let mut stmt = c.prepare_cached(
325 "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
326 u.thumbnails != 0, u.language,
327 r.id, r.path, r.mode
328 FROM sessions s
329 JOIN users u ON u.id = s.user_id
330 LEFT JOIN user_roots r ON r.user_id = u.id
331 WHERE s.token = ?1 AND u.active = 1
332 ORDER BY r.id",
333 )?;
334 // One row per root; a user without roots still returns one row, with
335 // the root columns NULL.
336 let mut user: Option<User> = None;
337 let mut roots: Vec<RootRow> = Vec::new();
338 let mut rows = stmt.query([token])?;
339 while let Some(r) = rows.next()? {
340 if user.is_none() {
341 user = Some(map_user(r)?);
342 }
343 if let Some(id) = r.get::<_, Option<i64>>(7)? {
344 roots.push(RootRow {
345 id,
346 path: r.get(8)?,
347 mode: r.get::<_, SqlMode>(9)?.0,
348 });
349 }
350 }
351 Ok(user.map(|u| (u, roots)))
352 }
353
354 // ---------- roots ----------
355
356 pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
357 let c = self.0.lock().await;
358 let mut stmt = c.prepare_cached(
359 "SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id",
360 )?;
361 let rows = stmt.query_map([user_id], |r| {
362 Ok(RootRow {
363 id: r.get(0)?,
364 path: r.get(1)?,
365 mode: r.get::<_, SqlMode>(2)?.0,
366 })
367 })?;
368 rows.collect()
369 }
370
371 // ---------- admin: user management (M7) ----------
372
373 /// Every user with their roots, in one query. The admin user list needs
374 /// both, and a per-user roots query would be one round trip per user.
375 pub async fn all_users_with_roots(&self) -> DbResult<Vec<(User, Vec<RootRow>)>> {
376 let c = self.0.lock().await;
377 let mut stmt = c.prepare_cached(
378 "SELECT u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
379 u.thumbnails != 0, u.language,
380 r.id, r.path, r.mode
381 FROM users u
382 LEFT JOIN user_roots r ON r.user_id = u.id
383 ORDER BY u.id, r.id",
384 )?;
385 // Rows arrive grouped by user, so a new user id starts a new group.
386 let mut out: Vec<(User, Vec<RootRow>)> = Vec::new();
387 let mut rows = stmt.query([])?;
388 while let Some(r) = rows.next()? {
389 let uid: i64 = r.get(0)?;
390 if out.last().is_none_or(|(u, _)| u.id != uid) {
391 out.push((map_user(r)?, Vec::new()));
392 }
393 if let Some(id) = r.get::<_, Option<i64>>(7)? {
394 out.last_mut().expect("pushed above").1.push(RootRow {
395 id,
396 path: r.get(8)?,
397 mode: r.get::<_, SqlMode>(9)?.0,
398 });
399 }
400 }
401 Ok(out)
402 }
403
404 pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
405 let c = self.0.lock().await;
406 c.query_row(
407 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, thumbnails != 0,
408 language
409 FROM users WHERE id = ?1",
410 [id],
411 map_user,
412 )
413 .optional()
414 }
415
416 pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
417 let c = self.0.lock().await;
418 c.query_row(
419 "SELECT id, name, is_admin != 0, active != 0, single_click != 0, thumbnails != 0,
420 language
421 FROM users WHERE name = ?1",
422 [name],
423 map_user,
424 )
425 .optional()
426 }
427
428 pub async fn count_admins(&self) -> DbResult<i64> {
429 let c = self.0.lock().await;
430 c.query_row(
431 "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
432 [],
433 |r| r.get(0),
434 )
435 }
436
437 /// Create a user with the given roots (path, mode) pairs.
438 pub async fn create_user(
439 &self,
440 name: &str,
441 pass_hash: &str,
442 is_admin: bool,
443 roots: &[(String, Mode)],
444 ) -> DbResult<User> {
445 let mut c = self.0.lock().await;
446 let tx = c.transaction()?;
447 tx.execute(
448 "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
449 VALUES (?1, ?2, ?3, 1, ?4)",
450 params![name, pass_hash, is_admin as i64, now()],
451 )?;
452 let user_id = tx.last_insert_rowid();
453 for (path, mode) in roots {
454 tx.execute(
455 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
456 params![user_id, path, SqlMode(*mode)],
457 )?;
458 }
459 tx.commit()?;
460 Ok(User {
461 id: user_id,
462 name: name.to_string(),
463 is_admin,
464 active: true,
465 single_click: false,
466 thumbnails: true,
467 language: None,
468 })
469 }
470
471 pub async fn set_user_single_click(&self, id: i64, single_click: bool) -> DbResult<()> {
472 let c = self.0.lock().await;
473 c.execute(
474 "UPDATE users SET single_click = ?1 WHERE id = ?2",
475 params![single_click as i64, id],
476 )?;
477 Ok(())
478 }
479
480 pub async fn set_user_thumbnails(&self, id: i64, thumbnails: bool) -> DbResult<()> {
481 let c = self.0.lock().await;
482 c.execute(
483 "UPDATE users SET thumbnails = ?1 WHERE id = ?2",
484 params![thumbnails as i64, id],
485 )?;
486 Ok(())
487 }
488
489 pub async fn set_user_language(&self, id: i64, language: Option<&str>) -> DbResult<()> {
490 let c = self.0.lock().await;
491 c.execute(
492 "UPDATE users SET language = ?1 WHERE id = ?2",
493 params![language, id],
494 )?;
495 Ok(())
496 }
497
498 /// Apply an admin edit atomically: every `Some` field is written in one
499 /// transaction, so a failure midway leaves the user unchanged.
500 pub async fn update_user(
501 &self,
502 id: i64,
503 pass_hash: Option<&str>,
504 is_admin: Option<bool>,
505 active: Option<bool>,
506 roots: Option<&[(String, Mode)]>,
507 ) -> DbResult<()> {
508 let mut c = self.0.lock().await;
509 let tx = c.transaction()?;
510 if let Some(h) = pass_hash {
511 set_password(&tx, id, h)?;
512 }
513 if let Some(a) = is_admin {
514 tx.execute(
515 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
516 params![a as i64, id],
517 )?;
518 }
519 if let Some(a) = active {
520 tx.execute(
521 "UPDATE users SET active = ?1 WHERE id = ?2",
522 params![a as i64, id],
523 )?;
524 }
525 if let Some(roots) = roots {
526 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
527 for (path, mode) in roots {
528 tx.execute(
529 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
530 params![id, path, SqlMode(*mode)],
531 )?;
532 }
533 }
534 tx.commit()
535 }
536
537 /// Delete a user. `false` means no row matched.
538 pub async fn delete_user(&self, id: i64) -> DbResult<bool> {
539 let c = self.0.lock().await;
540 Ok(c.execute("DELETE FROM users WHERE id = ?1", [id])? > 0)
541 }
542
543 // ---------- shares ----------
544
545 #[allow(clippy::too_many_arguments)] // one row's columns, all required
546 pub async fn create_share(
547 &self,
548 creator_id: i64,
549 token: &str,
550 target: &str,
551 is_file: bool,
552 mode: Mode,
553 expires_at: Option<&str>,
554 password_hash: Option<&str>,
555 ) -> DbResult<ShareRow> {
556 let c = self.0.lock().await;
557 c.execute(
558 "INSERT INTO shares
559 (token, creator_id, target, is_file, mode, created_at, expires_at, password_hash)
560 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
561 params![
562 token,
563 creator_id,
564 target,
565 is_file as i64,
566 SqlMode(mode),
567 now(),
568 expires_at,
569 password_hash
570 ],
571 )?;
572 let id = c.last_insert_rowid();
573 Ok(ShareRow {
574 id,
575 token: token.to_string(),
576 creator_id,
577 target: target.to_string(),
578 is_file,
579 mode,
580 created_at: now(),
581 expires_at: expires_at.map(|s| s.to_string()),
582 password_hash: password_hash.map(|s| s.to_string()),
583 })
584 }
585
586 /// Record that a visitor entered `share_id`'s password, and return the
587 /// token that proves it (the value of their unlock cookie).
588 pub async fn create_share_unlock(&self, share_id: i64) -> DbResult<String> {
589 let token = crate::auth::random_token();
590 let c = self.0.lock().await;
591 // Old unlocks go first. The cookie carrying them is a session
592 // cookie, so it is already gone from every browser; without this the
593 // rows would accumulate forever, one per unlock.
594 c.execute(
595 "DELETE FROM share_unlocks WHERE created_at < ?1",
596 [expiry_cutoff()],
597 )?;
598 c.execute(
599 "INSERT INTO share_unlocks (token, share_id, created_at) VALUES (?1, ?2, ?3)",
600 params![token, share_id, now()],
601 )?;
602 Ok(token)
603 }
604
605 /// Whether `token` is a live unlock for `share_id`.
606 ///
607 /// The share id is part of the lookup, so an unlock for one share cannot
608 /// open another.
609 pub async fn share_unlock_valid(&self, token: &str, share_id: i64) -> DbResult<bool> {
610 let c = self.0.lock().await;
611 let mut stmt =
612 c.prepare_cached("SELECT 1 FROM share_unlocks WHERE token = ?1 AND share_id = ?2")?;
613 Ok(stmt
614 .query_row(params![token, share_id], |_| Ok(()))
615 .optional()?
616 .is_some())
617 }
618
619 pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
620 let c = self.0.lock().await;
621 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
622 password_hash
623 FROM shares WHERE token = ?1";
624 let mut stmt = c.prepare_cached(sql)?;
625 stmt.query_row([token], map_share).optional()
626 }
627
628 pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
629 let c = self.0.lock().await;
630 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
631 password_hash
632 FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
633 let mut stmt = c.prepare_cached(sql)?;
634 let rows = stmt.query_map([creator_id], map_share)?;
635 rows.collect()
636 }
637
638 /// Revoke every share on `target` or on anything beneath it. Returns how
639 /// many were dropped.
640 ///
641 /// Called when a path stops meaning what it meant: the item was deleted,
642 /// renamed, or moved away. A share names a path, and a path is not a
643 /// stable identity, so leaving the row behind would let a *new* item that
644 /// later takes the freed path inherit the old link's audience.
645 ///
646 /// `substr` rather than `LIKE`: a target containing `%` or `_` would make
647 /// a `LIKE` pattern over-match and revoke unrelated shares.
648 pub async fn revoke_shares_at(&self, target: &str) -> DbResult<usize> {
649 let c = self.0.lock().await;
650 c.execute(
651 "DELETE FROM shares
652 WHERE target = ?1 OR substr(target, 1, length(?1) + 1) = ?1 || '/'",
653 [target],
654 )
655 }
656
657 /// Delete one of `creator_id`'s shares. `false` means no row matched.
658 pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
659 let c = self.0.lock().await;
660 let n = c.execute(
661 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
662 params![id, creator_id],
663 )?;
664 Ok(n > 0)
665 }
666
667 // ---------- settings ----------
668
669 /// Folders excluded from search, as paths relative to the server root.
670 ///
671 /// Stored as one JSON array in a settings row. A table of its own would
672 /// be overkill for a hand-edited list read once per search.
673 pub async fn search_excludes(&self) -> DbResult<Vec<String>> {
674 let raw = self.get_setting("search_excludes").await?;
675 // Normalised on read as well as on write. A value edited straight
676 // into the database would otherwise never match: `is_excluded`
677 // compares against paths with no slash at either end.
678 let clean = |v: Vec<String>| -> Vec<String> {
679 v.into_iter()
680 .map(|p| p.trim().replace('\\', "/").trim_matches('/').to_string())
681 .filter(|p| !p.is_empty() && p != ".")
682 .collect()
683 };
684 // A hand-edited, unparseable value falls back to no exclusions,
685 // the same as an absent row.
686 Ok(raw
687 .as_deref()
688 .and_then(|v| serde_json::from_str::<Vec<String>>(v).ok())
689 .map(clean)
690 .unwrap_or_default())
691 }
692
693 pub async fn set_search_excludes(&self, paths: &[String]) -> DbResult<()> {
694 let json = serde_json::to_string(paths).unwrap_or_else(|_| "[]".to_string());
695 self.set_setting("search_excludes", &json).await
696 }
697
698 pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
699 let c = self.0.lock().await;
700 let mut stmt = c.prepare_cached("SELECT value FROM settings WHERE key = ?1")?;
701 stmt.query_row([key], |r| r.get(0)).optional()
702 }
703
704 pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
705 let c = self.0.lock().await;
706 c.execute(
707 "INSERT INTO settings (key, value) VALUES (?1, ?2)
708 ON CONFLICT(key) DO UPDATE SET value = ?2",
709 params![key, value],
710 )?;
711 Ok(())
712 }
713
714 /// Whether users may create writable (read-write) shares. Off by default;
715 /// the admin setting gates it.
716 pub async fn allow_writable_shares(&self) -> DbResult<bool> {
717 Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
718 }
719
720 pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
721 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
722 .await
723 }
724}
725
726/// Write a new password hash and drop every session that was opened with the
727/// old one.
728///
729/// The two belong together: a password is changed because the old one is
730/// suspect (an admin resetting a compromised account), and a session that
731/// survives the reset leaves whoever holds it signed in. Takes the
732/// transaction so the caller can bundle it with its other edits.
733fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
734 tx.execute(
735 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
736 params![pass_hash, id],
737 )?;
738 tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
739 Ok(())
740}
741
742/// Column order matched by the four `users` SELECTs above.
743fn map_user(r: &rusqlite::Row) -> DbResult<User> {
744 Ok(User {
745 id: r.get(0)?,
746 name: r.get(1)?,
747 is_admin: r.get(2)?,
748 active: r.get(3)?,
749 single_click: r.get(4)?,
750 thumbnails: r.get(5)?,
751 language: r.get(6)?,
752 })
753}
754
755/// Column order matched by the two `shares` SELECTs above.
756fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
757 Ok(ShareRow {
758 id: r.get(0)?,
759 token: r.get(1)?,
760 creator_id: r.get(2)?,
761 target: r.get(3)?,
762 is_file: r.get::<_, i64>(4)? != 0,
763 mode: r.get::<_, SqlMode>(5)?.0,
764 created_at: r.get(6)?,
765 expires_at: r.get(7)?,
766 password_hash: r.get(8)?,
767 })
768}
769
770/// A hash of a random string nobody knows. Verified against when the login
771/// name does not exist, so both paths cost one Argon2 run.
772static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
773 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
774});
775
776/// How long an unlock row outlives its cookie. The cookie dies with the
777/// browser, so this only bounds the rows left behind by closed sessions.
778const UNLOCK_MAX_AGE_DAYS: i64 = 7;
779
780/// The timestamp an unlock row must be newer than to survive a cleanup.
781fn expiry_cutoff() -> String {
782 (chrono::Utc::now() - chrono::Duration::days(UNLOCK_MAX_AGE_DAYS))
783 .to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
784}
785
786fn now() -> String {
787 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
788}
789
790const SCHEMA_V1: &str = r#"
791CREATE TABLE IF NOT EXISTS users (
792 id INTEGER PRIMARY KEY AUTOINCREMENT,
793 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
794 pass_hash TEXT NOT NULL,
795 is_admin INTEGER NOT NULL DEFAULT 0,
796 created_at TEXT NOT NULL
797);
798
799CREATE TABLE IF NOT EXISTS user_roots (
800 id INTEGER PRIMARY KEY AUTOINCREMENT,
801 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
802 path TEXT NOT NULL,
803 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
804 UNIQUE (user_id, path)
805);
806
807CREATE TABLE IF NOT EXISTS shares (
808 id INTEGER PRIMARY KEY AUTOINCREMENT,
809 token TEXT NOT NULL UNIQUE,
810 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
811 target TEXT NOT NULL,
812 is_file INTEGER NOT NULL,
813 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
814 created_at TEXT NOT NULL,
815 expires_at TEXT
816);
817
818CREATE TABLE IF NOT EXISTS sessions (
819 token TEXT PRIMARY KEY,
820 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
821 created_at TEXT NOT NULL,
822 last_seen_at TEXT NOT NULL
823);
824
825CREATE TABLE IF NOT EXISTS settings (
826 key TEXT PRIMARY KEY,
827 value TEXT NOT NULL
828);
829INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
830"#;
831
832#[cfg(test)]
833mod tests {
834 use super::*;
835
836 // Most tests use an in-memory DB (the file-based path is still covered
837 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
838 async fn mem() -> Db {
839 Db::open_in_memory().await.unwrap()
840 }
841
842 /// `update_user` is the only way production edits these fields, so the
843 /// tests exercise that path rather than per-field helpers.
844 async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
845 db.update_user(id, pass, admin, active, None).await.unwrap();
846 }
847
848 async fn db_with_admin() -> (Db, User) {
849 let db = mem().await;
850 let hash = crate::auth::hash_password("admin1234").unwrap();
851 let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
852 (db, admin)
853 }
854
855 #[tokio::test]
856 async fn fresh_db_state() {
857 let db = mem().await;
858 assert_eq!(db.user_count().await.unwrap(), 0);
859 assert_eq!(db.count_admins().await.unwrap(), 0);
860 assert!(!db.allow_writable_shares().await.unwrap());
861 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
862 assert!(db.find_user_by_id(1).await.unwrap().is_none());
863 assert!(db.all_users_with_roots().await.unwrap().is_empty());
864 }
865
866 #[tokio::test]
867 async fn v1_db_migrates_to_v2() {
868 let dir = tempfile::tempdir().unwrap();
869 let path = dir.path().join("legacy.sqlite");
870 {
871 let conn = rusqlite::Connection::open(&path).unwrap();
872 conn.execute_batch(SCHEMA_V1).unwrap();
873 conn.execute(
874 "INSERT INTO users (name, pass_hash, is_admin, created_at)
875 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
876 [],
877 )
878 .unwrap();
879 conn.execute(
880 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
881 [],
882 )
883 .unwrap();
884 }
885 let db = Db::open(&path).await.unwrap();
886 assert_eq!(db.user_count().await.unwrap(), 1);
887 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
888 assert!(u.active, "v2 migration must default active to true");
889 assert!(u.is_admin);
890 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
891 // Migrations are idempotent.
892 let db2 = Db::open(&path).await.unwrap();
893 assert_eq!(db2.user_count().await.unwrap(), 1);
894 assert!(
895 db2.find_user_by_name("legacy")
896 .await
897 .unwrap()
898 .unwrap()
899 .active
900 );
901 }
902
903 #[tokio::test]
904 async fn admin_user_and_passwords() {
905 let (db, admin) = db_with_admin().await;
906 assert!(admin.is_admin);
907 assert!(admin.active);
908 // Root "." rw is assigned by create_admin.
909 let roots = db.user_roots(admin.id).await.unwrap();
910 assert_eq!(roots.len(), 1);
911 assert_eq!(roots[0].path, ".");
912 assert_eq!(roots[0].mode, Mode::Rw);
913
914 assert!(
915 db.verify_password("admin", "admin1234")
916 .await
917 .unwrap()
918 .is_some()
919 );
920 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
921 // Name lookup is case-insensitive (COLLATE NOCASE).
922 assert!(
923 db.verify_password("ADMIN", "admin1234")
924 .await
925 .unwrap()
926 .is_some()
927 );
928 // Disabled users cannot verify.
929 edit(&db, admin.id, None, None, Some(false)).await;
930 assert!(
931 db.verify_password("admin", "admin1234")
932 .await
933 .unwrap()
934 .is_none()
935 );
936 edit(&db, admin.id, None, None, Some(true)).await;
937 assert!(
938 db.verify_password("admin", "admin1234")
939 .await
940 .unwrap()
941 .is_some()
942 );
943 }
944
945 #[tokio::test]
946 async fn setup_is_won_by_exactly_one_caller() {
947 let db = mem().await;
948 let hash = crate::auth::hash_password("admin1234").unwrap();
949 assert!(db.create_admin("first", &hash).await.unwrap().is_some());
950 // The guard lives in the insert, so a different name loses too.
951 assert!(db.create_admin("second", &hash).await.unwrap().is_none());
952 assert_eq!(db.user_count().await.unwrap(), 1);
953 // The loser rolled back cleanly: no orphaned root row.
954 let first = db.find_user_by_name("first").await.unwrap().unwrap();
955 assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
956 }
957
958 #[tokio::test]
959 async fn changing_a_password_drops_that_users_sessions() {
960 let (db, admin) = db_with_admin().await;
961 let h = crate::auth::hash_password("bobpass1").unwrap();
962 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
963 db.create_session(admin.id, "admin-tok").await.unwrap();
964 db.create_session(bob.id, "bob-tok-1").await.unwrap();
965 db.create_session(bob.id, "bob-tok-2").await.unwrap();
966
967 let new_h = crate::auth::hash_password("bobpass2").unwrap();
968 edit(&db, bob.id, Some(&new_h), None, None).await;
969 assert!(
970 db.session_user_with_roots("bob-tok-1")
971 .await
972 .unwrap()
973 .is_none()
974 );
975 assert!(
976 db.session_user_with_roots("bob-tok-2")
977 .await
978 .unwrap()
979 .is_none()
980 );
981 // Only the reset user is signed out.
982 assert!(
983 db.session_user_with_roots("admin-tok")
984 .await
985 .unwrap()
986 .is_some()
987 );
988
989 // The admin-edit path bundles the same rule into its transaction.
990 db.create_session(bob.id, "bob-tok-3").await.unwrap();
991 let h3 = crate::auth::hash_password("bobpass3").unwrap();
992 db.update_user(bob.id, Some(&h3), None, None, None)
993 .await
994 .unwrap();
995 assert!(
996 db.session_user_with_roots("bob-tok-3")
997 .await
998 .unwrap()
999 .is_none()
1000 );
1001 // An edit that leaves the password alone keeps the session.
1002 db.create_session(bob.id, "bob-tok-4").await.unwrap();
1003 db.update_user(bob.id, None, Some(true), None, None)
1004 .await
1005 .unwrap();
1006 assert!(
1007 db.session_user_with_roots("bob-tok-4")
1008 .await
1009 .unwrap()
1010 .is_some()
1011 );
1012 }
1013
1014 #[tokio::test]
1015 async fn sessions_lifecycle() {
1016 let (db, admin) = db_with_admin().await;
1017 assert!(
1018 db.session_user_with_roots("ghost-token")
1019 .await
1020 .unwrap()
1021 .is_none()
1022 );
1023 db.create_session(admin.id, "tok1").await.unwrap();
1024 let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
1025 assert_eq!(u.id, admin.id);
1026 // Disabling the user invalidates existing sessions.
1027 edit(&db, admin.id, None, None, Some(false)).await;
1028 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
1029 edit(&db, admin.id, None, None, Some(true)).await;
1030 assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
1031 db.delete_session("tok1").await.unwrap();
1032 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
1033 }
1034
1035 #[tokio::test]
1036 async fn user_crud_and_roots() {
1037 let (db, _admin) = db_with_admin().await;
1038 let h = crate::auth::hash_password("bobpass1").unwrap();
1039 let bob = db
1040 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
1041 .await
1042 .unwrap();
1043 assert!(!bob.is_admin);
1044 assert!(bob.active);
1045
1046 // Duplicate name (case-insensitive) is rejected.
1047 let h2 = crate::auth::hash_password("carolpass1").unwrap();
1048 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
1049 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
1050
1051 // Lookup helpers.
1052 assert_eq!(
1053 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
1054 bob.id
1055 );
1056 assert_eq!(
1057 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
1058 "bob"
1059 );
1060 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
1061 assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
1062
1063 // Root replacement semantics.
1064 let roots = db.user_roots(bob.id).await.unwrap();
1065 assert_eq!(roots.len(), 1);
1066 db.update_user(
1067 bob.id,
1068 None,
1069 None,
1070 None,
1071 Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
1072 )
1073 .await
1074 .unwrap();
1075 let roots = db.user_roots(bob.id).await.unwrap();
1076 assert_eq!(roots.len(), 2);
1077 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
1078 db.update_user(bob.id, None, None, None, Some(&[]))
1079 .await
1080 .unwrap();
1081 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
1082
1083 // Password update.
1084 let new_h = crate::auth::hash_password("bobpass2").unwrap();
1085 edit(&db, bob.id, Some(&new_h), None, None).await;
1086 assert!(
1087 db.verify_password("bob", "bobpass1")
1088 .await
1089 .unwrap()
1090 .is_none()
1091 );
1092 assert!(
1093 db.verify_password("bob", "bobpass2")
1094 .await
1095 .unwrap()
1096 .is_some()
1097 );
1098
1099 // Admin flag + count (only active admins count).
1100 edit(&db, bob.id, None, Some(true), None).await;
1101 assert_eq!(db.count_admins().await.unwrap(), 2);
1102 edit(&db, bob.id, None, None, Some(false)).await;
1103 assert_eq!(db.count_admins().await.unwrap(), 1);
1104 edit(&db, bob.id, None, Some(false), None).await;
1105
1106 // Deletion.
1107 assert!(db.delete_user(bob.id).await.unwrap());
1108 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
1109 assert!(!db.delete_user(bob.id).await.unwrap());
1110 assert_eq!(db.user_count().await.unwrap(), 2);
1111 }
1112
1113 fn share_row(expires_at: Option<&str>) -> ShareRow {
1114 ShareRow {
1115 id: 1,
1116 token: "t".into(),
1117 creator_id: 1,
1118 target: "docs".into(),
1119 is_file: false,
1120 mode: Mode::Ro,
1121 created_at: "2024-01-01T00:00:00Z".into(),
1122 expires_at: expires_at.map(str::to_string),
1123 password_hash: None,
1124 }
1125 }
1126
1127 #[test]
1128 fn share_expiry_logic() {
1129 assert!(!share_row(None).is_expired());
1130 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
1131 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
1132 // Unparseable expiry → treated as not expired (fail open for reads).
1133 assert!(!share_row(Some("not-a-date")).is_expired());
1134 }
1135
1136 #[tokio::test]
1137 async fn shares_crud() {
1138 let (db, admin) = db_with_admin().await;
1139 let s1 = db
1140 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
1141 .await
1142 .unwrap();
1143 let s2 = db
1144 .create_share(
1145 admin.id,
1146 "tok-b",
1147 "file.txt",
1148 true,
1149 Mode::Rw,
1150 Some("2999-01-01T00:00:00Z"),
1151 None,
1152 )
1153 .await
1154 .unwrap();
1155 assert!(s2.id > s1.id);
1156
1157 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
1158 assert!(found.is_file);
1159 assert_eq!(found.mode, Mode::Rw);
1160 assert!(db.share_by_token("nope").await.unwrap().is_none());
1161
1162 // Listed newest-first.
1163 let list = db.user_shares(admin.id).await.unwrap();
1164 assert_eq!(list.len(), 2);
1165 assert_eq!(list[0].id, s2.id);
1166 // Other users see nothing.
1167 let h = crate::auth::hash_password("bobpass1").unwrap();
1168 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
1169 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
1170
1171 // Only the creator can delete.
1172 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
1173 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
1174 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
1175 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
1176 }
1177
1178 /// The unlock token is what a visitor's cookie carries, so an unlock
1179 /// that opened the wrong share would be a full bypass of the password.
1180 #[tokio::test]
1181 async fn share_unlocks_are_bound_to_one_share() {
1182 let (db, admin) = db_with_admin().await;
1183 let a = db
1184 .create_share(
1185 admin.id,
1186 "tok-a",
1187 "docs",
1188 false,
1189 Mode::Ro,
1190 None,
1191 Some("hash"),
1192 )
1193 .await
1194 .unwrap();
1195 let b = db
1196 .create_share(
1197 admin.id,
1198 "tok-b",
1199 "other",
1200 false,
1201 Mode::Ro,
1202 None,
1203 Some("hash"),
1204 )
1205 .await
1206 .unwrap();
1207 assert_eq!(
1208 db.share_by_token("tok-a")
1209 .await
1210 .unwrap()
1211 .unwrap()
1212 .password_hash,
1213 Some("hash".to_string())
1214 );
1215
1216 let unlock = db.create_share_unlock(a.id).await.unwrap();
1217 assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
1218 assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
1219 assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
1220
1221 // Deleting the share takes its unlocks with it, so a re-created
1222 // share that happened to reuse the id could not inherit them.
1223 assert!(db.delete_share(a.id, admin.id).await.unwrap());
1224 assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
1225 }
1226
1227 #[tokio::test]
1228 async fn revoking_a_path_takes_its_descendants_only() {
1229 let (db, admin) = db_with_admin().await;
1230 let mk = async |token: &str, target: &str| {
1231 db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
1232 .await
1233 .unwrap();
1234 };
1235 mk("t-self", "docs").await;
1236 mk("t-child", "docs/a.txt").await;
1237 mk("t-deep", "docs/inner/b.txt").await;
1238 // A sibling whose name merely starts with "docs" must survive.
1239 mk("t-sibling", "docs2/c.txt").await;
1240 mk("t-other", "src").await;
1241 // SQL wildcards in a path are literal characters, not patterns.
1242 mk("t-wild", "do%s/d.txt").await;
1243
1244 assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
1245 for gone in ["t-self", "t-child", "t-deep"] {
1246 assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
1247 }
1248 for kept in ["t-sibling", "t-other", "t-wild"] {
1249 assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
1250 }
1251 // Revoking a path nobody shared is a no-op, not an error.
1252 assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
1253 }
1254
1255 #[tokio::test]
1256 async fn settings_round_trip() {
1257 let (db, _admin) = db_with_admin().await;
1258 assert!(!db.allow_writable_shares().await.unwrap());
1259 db.set_allow_writable_shares(true).await.unwrap();
1260 assert!(db.allow_writable_shares().await.unwrap());
1261 // Upsert semantics.
1262 db.set_allow_writable_shares(false).await.unwrap();
1263 assert!(!db.allow_writable_shares().await.unwrap());
1264 // Generic get/set.
1265 db.set_setting("custom", "v").await.unwrap();
1266 assert_eq!(
1267 db.get_setting("custom").await.unwrap().as_deref(),
1268 Some("v")
1269 );
1270 assert_eq!(db.get_setting("missing").await.unwrap(), None);
1271 }
1272}
1273