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