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