dovenest

dovenest/server/src/db.rs
db.rs
⎇
Raw
1use std::path::Path;
2use std::sync::Arc;
3
4pub use api_types::{AuthMode, Mode};
5use rusqlite::types::{FromSql, FromSqlError, FromSqlResult, ToSql, ToSqlOutput, ValueRef};
6use rusqlite::{Connection, OptionalExtension, params};
7
8const SCHEMA_VERSION: i64 = 10;
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/// SQL adapter for [`AuthMode`], for the same reason as [`SqlMode`].
32struct SqlAuthMode(AuthMode);
33
34impl FromSql for SqlAuthMode {
35 fn column_result(v: ValueRef<'_>) -> FromSqlResult<Self> {
36 let s = v.as_str()?;
37 AuthMode::from_wire(s)
38 .map(SqlAuthMode)
39 .ok_or_else(|| FromSqlError::Other(format!("unknown auth mode {s:?}").into()))
40 }
41}
42
43impl ToSql for SqlAuthMode {
44 fn to_sql(&self) -> rusqlite::Result<ToSqlOutput<'_>> {
45 Ok(ToSqlOutput::from(self.0.as_str()))
46 }
47}
48
49#[derive(Debug, Clone)]
50pub struct User {
51 pub id: i64,
52 pub name: String,
53 pub is_admin: bool,
54 /// Disabled users cannot sign in and their sessions are rejected.
55 pub active: bool,
56 /// Profile setting: single click opens entries (off = click selects).
57 pub single_click: bool,
58 /// Profile setting: show thumbnails in the grid.
59 pub thumbnails: bool,
60 /// Preferred UI language tag ("en", "de", "fr"); None = follow the
61 /// browser.
62 pub language: Option<String>,
63 /// Profile setting: the root the UI opens by default. May point at a
64 /// root the user no longer has; the API filters that out.
65 pub default_root_id: Option<i64>,
66 /// What this account needs to sign in.
67 pub auth_mode: AuthMode,
68 /// Whether a password is set. False means the account signs in with
69 /// passkeys only. See [`NO_PASSWORD`].
70 pub has_password: bool,
71}
72
73/// The `pass_hash` of an account with no password.
74///
75/// A sentinel rather than NULL: `users.pass_hash` is `NOT NULL`, and SQLite
76/// cannot drop that constraint without rebuilding the table. Nothing verifies
77/// against it — [`Db::verify_password`] swaps in [`DUMMY_HASH`] so the reject
78/// costs the same as a wrong password, and `PasswordHash::new("")` fails
79/// anyway.
80pub const NO_PASSWORD: &str = "";
81
82/// How many passkeys one account may hold.
83///
84/// Also the exact number of credentials a named sign-in challenge lists. The
85/// two are one number on purpose: the challenge pads a short list with decoys
86/// so its length says nothing about the account, and that only works while no
87/// account can push past the padding.
88pub const PASSKEY_LIMIT: usize = 8;
89
90/// What [`Db::delete_passkey`] did.
91#[derive(Debug, Clone, Copy, PartialEq, Eq)]
92pub enum PasskeyDeleted {
93 Gone,
94 /// No such passkey, or it belongs to someone else.
95 NotFound,
96 /// Removing it would have left the account unreachable.
97 LastCredential,
98}
99
100/// One registered passkey. `passkey` is the serialized `webauthn-rs`
101/// credential; everything else is for the settings list.
102#[derive(Debug, Clone)]
103pub struct PasskeyRow {
104 pub id: i64,
105 pub name: String,
106 pub created_at: String,
107 pub last_used_at: Option<String>,
108 pub discoverable: Option<bool>,
109 /// `webauthn_rs::prelude::Passkey` as JSON.
110 pub passkey: String,
111}
112
113#[derive(Debug, Clone)]
114pub struct RootRow {
115 pub id: i64,
116 /// Path relative to the server root; "." means the whole root.
117 pub path: String,
118 pub mode: Mode,
119}
120
121#[derive(Debug, Clone)]
122pub struct ShareRow {
123 pub id: i64,
124 pub token: String,
125 pub creator_id: i64,
126 /// Path of the shared item relative to the server root.
127 pub target: String,
128 pub is_file: bool,
129 pub mode: Mode,
130 pub created_at: String,
131 pub expires_at: Option<String>,
132 /// Argon2 hash of the share's password, when it has one. Resolve,
133 /// listing and download all stay locked until the visitor enters it and
134 /// gets an unlock cookie.
135 pub password_hash: Option<String>,
136}
137
138impl ShareRow {
139 pub fn is_expired(&self) -> bool {
140 match &self.expires_at {
141 Some(e) => chrono::DateTime::parse_from_rfc3339(e)
142 .map(|t| chrono::Utc::now() >= t.with_timezone(&chrono::Utc))
143 .unwrap_or(false),
144 None => false,
145 }
146 }
147}
148
149/// A [`ShareRow`] together with the account that created it.
150#[derive(Debug, Clone)]
151pub struct ShareWithCreator {
152 pub share: ShareRow,
153 pub creator_name: String,
154 /// Whether that account can still sign in. Deactivating an account leaves
155 /// its shares live.
156 pub creator_active: bool,
157}
158
159/// The columns [`map_user`] reads, in order. Every SELECT that builds a
160/// [`User`] uses one of these two, so a new column is added in one place.
161/// `USER_COLS_U` is the same list qualified for the queries that join
162/// `users u` against `user_roots`.
163const USER_COLS: &str = "id, name, is_admin != 0, active != 0, single_click != 0,
164 thumbnails != 0, language, default_root_id, auth_mode, pass_hash != ''";
165const USER_COLS_U: &str = "u.id, u.name, u.is_admin != 0, u.active != 0, u.single_click != 0,
166 u.thumbnails != 0, u.language, u.default_root_id, u.auth_mode, u.pass_hash != ''";
167/// How many columns the two lists above cover. The joined queries read the
168/// root columns starting here.
169const USER_COL_COUNT: usize = 10;
170
171/// Every query can fail, and every caller decides what to do about it.
172///
173/// Earlier versions swallowed read errors and returned a default (an empty
174/// root list, a count of 0). That turned a broken database into a plausible
175/// answer: "you have no folders" instead of an error. One contract now.
176pub type DbResult<T> = Result<T, rusqlite::Error>;
177
178#[derive(Clone)]
179pub struct Db(Arc<tokio::sync::Mutex<Connection>>);
180
181impl std::fmt::Debug for Db {
182 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
183 f.debug_struct("Db").finish()
184 }
185}
186
187impl Db {
188 pub async fn open(path: &Path) -> anyhow::Result<Self> {
189 if let Some(parent) = path.parent()
190 && !parent.as_os_str().is_empty()
191 {
192 std::fs::create_dir_all(parent)?;
193 }
194 let conn = Connection::open(path)?;
195 conn.pragma_update(None, "journal_mode", "WAL")?;
196 // WAL plus NORMAL: fsync only at checkpoints. A crash can lose the
197 // last commits, never the database file.
198 conn.pragma_update(None, "synchronous", "NORMAL")?;
199 conn.pragma_update(None, "foreign_keys", "ON")?;
200 conn.pragma_update(None, "busy_timeout", "5000")?;
201 Self::migrate(&conn)?;
202 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
203 }
204
205 /// Open a fresh in-memory database (used by tests — no temp file needed).
206 pub async fn open_in_memory() -> anyhow::Result<Self> {
207 let conn = Connection::open_in_memory()?;
208 conn.pragma_update(None, "foreign_keys", "ON")?;
209 conn.pragma_update(None, "busy_timeout", "5000")?;
210 Self::migrate(&conn)?;
211 Ok(Self(Arc::new(tokio::sync::Mutex::new(conn))))
212 }
213
214 fn migrate(conn: &Connection) -> rusqlite::Result<()> {
215 conn.execute(
216 "CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL)",
217 [],
218 )?;
219 let version: i64 = conn
220 .query_row(
221 "SELECT value FROM meta WHERE key = 'schema_version'",
222 [],
223 |r| r.get::<_, String>(0),
224 )
225 .optional()?
226 .and_then(|v| v.parse().ok())
227 .unwrap_or(0);
228
229 if version < 1 {
230 conn.execute_batch(SCHEMA_V1)?;
231 }
232 if version < 2 {
233 // User management (M7): a disabled flag so admins can suspend
234 // accounts without deleting them.
235 conn.execute_batch("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1")?;
236 }
237 if version < 3 {
238 // Per-user profile settings: click-to-open mode. No migration
239 // from the old behaviour — everyone starts on the new default
240 // (off: single click selects, double click opens).
241 conn.execute_batch(
242 "ALTER TABLE users ADD COLUMN single_click INTEGER NOT NULL DEFAULT 0",
243 )?;
244 }
245 if version < 4 {
246 // Per-user UI language preference; NULL means "follow the
247 // browser".
248 conn.execute_batch("ALTER TABLE users ADD COLUMN language TEXT")?;
249 }
250 if version < 5 {
251 // The share list is queried by creator on every shares page.
252 conn.execute_batch(
253 "CREATE INDEX IF NOT EXISTS idx_shares_creator ON shares(creator_id)",
254 )?;
255 }
256 if version < 6 {
257 // Unlocks cascade with their share, which cascades with its
258 // creator's account.
259 conn.execute_batch(
260 "ALTER TABLE shares ADD COLUMN password_hash TEXT;
261 CREATE TABLE IF NOT EXISTS share_unlocks (
262 token TEXT PRIMARY KEY,
263 share_id INTEGER NOT NULL REFERENCES shares(id) ON DELETE CASCADE,
264 created_at TEXT NOT NULL
265 );",
266 )?;
267 }
268 if version < 7 {
269 // `delete_share` cascades into share_unlocks, which is a full
270 // scan of that table without this.
271 conn.execute_batch(
272 "CREATE INDEX IF NOT EXISTS idx_share_unlocks_share
273 ON share_unlocks(share_id)",
274 )?;
275 }
276 if version < 8 {
277 // On by default, so `--cache` is the only step needed to get
278 // thumbnails.
279 conn.execute_batch(
280 "ALTER TABLE users ADD COLUMN thumbnails INTEGER NOT NULL DEFAULT 1",
281 )?;
282 }
283 if version < 9 {
284 // Per-user default root. No foreign key on purpose: removing a
285 // root must not fail because of this column.
286 conn.execute_batch("ALTER TABLE users ADD COLUMN default_root_id INTEGER")?;
287 }
288 if version < 10 {
289 // Passkeys, and how they combine with the password.
290 //
291 // `webauthn_id` is the WebAuthn user handle: a random uuid the
292 // authenticator stores inside a discoverable credential and hands
293 // back at sign-in. It must never change once a passkey exists, or
294 // that passkey can no longer be traced to its account. Filled in
295 // lazily on the first registration, so accounts that never use a
296 // passkey keep it NULL.
297 conn.execute_batch(
298 "ALTER TABLE users ADD COLUMN auth_mode TEXT NOT NULL DEFAULT 'either';
299 ALTER TABLE users ADD COLUMN webauthn_id TEXT;
300 CREATE TABLE IF NOT EXISTS passkeys (
301 id INTEGER PRIMARY KEY AUTOINCREMENT,
302 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
303 cred_id BLOB NOT NULL UNIQUE,
304 passkey TEXT NOT NULL,
305 name TEXT NOT NULL,
306 discoverable INTEGER,
307 created_at TEXT NOT NULL,
308 last_used_at TEXT
309 );
310 CREATE INDEX IF NOT EXISTS idx_passkeys_user ON passkeys(user_id);
311 CREATE UNIQUE INDEX IF NOT EXISTS idx_users_webauthn_id
312 ON users(webauthn_id) WHERE webauthn_id IS NOT NULL;",
313 )?;
314 }
315 conn.execute(
316 "INSERT OR REPLACE INTO meta (key, value) VALUES ('schema_version', ?1)",
317 [SCHEMA_VERSION.to_string()],
318 )?;
319 Ok(())
320 }
321
322 // ---------- users ----------
323
324 pub async fn user_count(&self) -> DbResult<i64> {
325 let c = self.0.lock().await;
326 let mut stmt = c.prepare_cached("SELECT COUNT(*) FROM users")?;
327 stmt.query_row([], |r| r.get(0))
328 }
329
330 /// Create the first admin account with the whole root visible (read-write).
331 ///
332 /// `None` means a user already existed. The `WHERE NOT EXISTS` guard runs
333 /// inside the same transaction as the insert, so two concurrent first-boot
334 /// setups cannot both win; a caller's earlier `user_count` check is only
335 /// an optimization, not the guarantee.
336 pub async fn create_admin(&self, name: &str, pass_hash: &str) -> DbResult<Option<User>> {
337 let mut c = self.0.lock().await;
338 let tx = c.transaction()?;
339 let inserted = tx.execute(
340 "INSERT INTO users (name, pass_hash, is_admin, created_at)
341 SELECT ?1, ?2, 1, ?3 WHERE NOT EXISTS (SELECT 1 FROM users)",
342 params![name, pass_hash, now()],
343 )?;
344 if inserted == 0 {
345 return Ok(None); // dropping `tx` rolls back
346 }
347 let user_id = tx.last_insert_rowid();
348 tx.execute(
349 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, '.', 'rw')",
350 params![user_id],
351 )?;
352 tx.commit()?;
353 Ok(Some(User {
354 id: user_id,
355 name: name.to_string(),
356 is_admin: true,
357 active: true,
358 single_click: false,
359 thumbnails: true,
360 language: None,
361 default_root_id: None,
362 auth_mode: AuthMode::Either,
363 has_password: true,
364 }))
365 }
366
367 pub async fn verify_password(&self, name: &str, password: &str) -> DbResult<Option<User>> {
368 // The guard is scoped to the query alone. Argon2 below is slow by
369 // design; holding the single connection lock across it would make one
370 // login serialize every other database access.
371 let row: Option<(User, String)> = {
372 let c = self.0.lock().await;
373 c.query_row(
374 &format!("SELECT {USER_COLS}, pass_hash FROM users WHERE name = ?1"),
375 [name],
376 |r| Ok((map_user(r)?, r.get(USER_COL_COUNT)?)),
377 )
378 .optional()?
379 };
380 // An unknown name, a disabled account and a passkey-only account all
381 // still pay for one Argon2 verify, so the response time does not tell
382 // them apart from a real account with a wrong password.
383 let (user, hash) = match row {
384 Some((u, h)) if u.active && u.has_password => (Some(u), h),
385 _ => (None, DUMMY_HASH.clone()),
386 };
387 let ok = crate::auth::verify_password_async(password, &hash).await;
388 Ok(user.filter(|_| ok))
389 }
390
391 pub async fn create_session(&self, user_id: i64, token: &str) -> DbResult<()> {
392 let c = self.0.lock().await;
393 c.execute(
394 "INSERT INTO sessions (token, user_id, created_at, last_seen_at)
395 VALUES (?1, ?2, ?3, ?4)",
396 params![token, user_id, now(), now()],
397 )?;
398 Ok(())
399 }
400
401 pub async fn delete_session(&self, token: &str) -> DbResult<()> {
402 let c = self.0.lock().await;
403 c.execute("DELETE FROM sessions WHERE token = ?1", [token])?;
404 Ok(())
405 }
406
407 /// The session's user plus that user's roots, in one round trip. Every
408 /// authenticated request needs both, so they are not two queries.
409 pub async fn session_user_with_roots(
410 &self,
411 token: &str,
412 ) -> DbResult<Option<(User, Vec<RootRow>)>> {
413 let c = self.0.lock().await;
414 let mut stmt = c.prepare_cached(&format!(
415 "SELECT {USER_COLS_U}, r.id, r.path, r.mode
416 FROM sessions s
417 JOIN users u ON u.id = s.user_id
418 LEFT JOIN user_roots r ON r.user_id = u.id
419 WHERE s.token = ?1 AND u.active = 1
420 ORDER BY r.id",
421 ))?;
422 // One row per root; a user without roots still returns one row, with
423 // the root columns NULL.
424 let mut user: Option<User> = None;
425 let mut roots: Vec<RootRow> = Vec::new();
426 let mut rows = stmt.query([token])?;
427 while let Some(r) = rows.next()? {
428 if user.is_none() {
429 user = Some(map_user(r)?);
430 }
431 if let Some(id) = r.get::<_, Option<i64>>(USER_COL_COUNT)? {
432 roots.push(RootRow {
433 id,
434 path: r.get(USER_COL_COUNT + 1)?,
435 mode: r.get::<_, SqlMode>(USER_COL_COUNT + 2)?.0,
436 });
437 }
438 }
439 Ok(user.map(|u| (u, roots)))
440 }
441
442 // ---------- roots ----------
443
444 pub async fn user_roots(&self, user_id: i64) -> DbResult<Vec<RootRow>> {
445 let c = self.0.lock().await;
446 let mut stmt = c.prepare_cached(
447 "SELECT id, path, mode FROM user_roots WHERE user_id = ?1 ORDER BY id",
448 )?;
449 let rows = stmt.query_map([user_id], |r| {
450 Ok(RootRow {
451 id: r.get(0)?,
452 path: r.get(1)?,
453 mode: r.get::<_, SqlMode>(2)?.0,
454 })
455 })?;
456 rows.collect()
457 }
458
459 // ---------- admin: user management (M7) ----------
460
461 /// Every user with their roots, in one query. The admin user list needs
462 /// both, and a per-user roots query would be one round trip per user.
463 pub async fn all_users_with_roots(&self) -> DbResult<Vec<(User, Vec<RootRow>)>> {
464 let c = self.0.lock().await;
465 let mut stmt = c.prepare_cached(&format!(
466 "SELECT {USER_COLS_U}, r.id, r.path, r.mode
467 FROM users u
468 LEFT JOIN user_roots r ON r.user_id = u.id
469 ORDER BY u.id, r.id",
470 ))?;
471 // Rows arrive grouped by user, so a new user id starts a new group.
472 let mut out: Vec<(User, Vec<RootRow>)> = Vec::new();
473 let mut rows = stmt.query([])?;
474 while let Some(r) = rows.next()? {
475 let uid: i64 = r.get(0)?;
476 if out.last().is_none_or(|(u, _)| u.id != uid) {
477 out.push((map_user(r)?, Vec::new()));
478 }
479 if let Some(id) = r.get::<_, Option<i64>>(USER_COL_COUNT)? {
480 out.last_mut().expect("pushed above").1.push(RootRow {
481 id,
482 path: r.get(USER_COL_COUNT + 1)?,
483 mode: r.get::<_, SqlMode>(USER_COL_COUNT + 2)?.0,
484 });
485 }
486 }
487 Ok(out)
488 }
489
490 pub async fn find_user_by_id(&self, id: i64) -> DbResult<Option<User>> {
491 let c = self.0.lock().await;
492 c.query_row(
493 &format!("SELECT {USER_COLS} FROM users WHERE id = ?1"),
494 [id],
495 map_user,
496 )
497 .optional()
498 }
499
500 pub async fn find_user_by_name(&self, name: &str) -> DbResult<Option<User>> {
501 let c = self.0.lock().await;
502 c.query_row(
503 &format!("SELECT {USER_COLS} FROM users WHERE name = ?1"),
504 [name],
505 map_user,
506 )
507 .optional()
508 }
509
510 pub async fn count_admins(&self) -> DbResult<i64> {
511 let c = self.0.lock().await;
512 c.query_row(
513 "SELECT COUNT(*) FROM users WHERE is_admin = 1 AND active = 1",
514 [],
515 |r| r.get(0),
516 )
517 }
518
519 /// Create a user with the given roots (path, mode) pairs.
520 pub async fn create_user(
521 &self,
522 name: &str,
523 pass_hash: &str,
524 is_admin: bool,
525 roots: &[(String, Mode)],
526 ) -> DbResult<User> {
527 let mut c = self.0.lock().await;
528 let tx = c.transaction()?;
529 tx.execute(
530 "INSERT INTO users (name, pass_hash, is_admin, active, created_at)
531 VALUES (?1, ?2, ?3, 1, ?4)",
532 params![name, pass_hash, is_admin as i64, now()],
533 )?;
534 let user_id = tx.last_insert_rowid();
535 for (path, mode) in roots {
536 tx.execute(
537 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
538 params![user_id, path, SqlMode(*mode)],
539 )?;
540 }
541 tx.commit()?;
542 Ok(User {
543 id: user_id,
544 name: name.to_string(),
545 is_admin,
546 active: true,
547 single_click: false,
548 thumbnails: true,
549 language: None,
550 default_root_id: None,
551 auth_mode: AuthMode::Either,
552 has_password: true,
553 })
554 }
555
556 pub async fn set_user_single_click(&self, id: i64, single_click: bool) -> DbResult<()> {
557 let c = self.0.lock().await;
558 c.execute(
559 "UPDATE users SET single_click = ?1 WHERE id = ?2",
560 params![single_click as i64, id],
561 )?;
562 Ok(())
563 }
564
565 pub async fn set_user_thumbnails(&self, id: i64, thumbnails: bool) -> DbResult<()> {
566 let c = self.0.lock().await;
567 c.execute(
568 "UPDATE users SET thumbnails = ?1 WHERE id = ?2",
569 params![thumbnails as i64, id],
570 )?;
571 Ok(())
572 }
573
574 pub async fn set_user_default_root(&self, id: i64, root_id: Option<i64>) -> DbResult<()> {
575 let c = self.0.lock().await;
576 c.execute(
577 "UPDATE users SET default_root_id = ?1 WHERE id = ?2",
578 params![root_id, id],
579 )?;
580 Ok(())
581 }
582
583 pub async fn set_user_language(&self, id: i64, language: Option<&str>) -> DbResult<()> {
584 let c = self.0.lock().await;
585 c.execute(
586 "UPDATE users SET language = ?1 WHERE id = ?2",
587 params![language, id],
588 )?;
589 Ok(())
590 }
591
592 /// Apply an admin edit atomically: every `Some` field is written in one
593 /// transaction, so a failure midway leaves the user unchanged.
594 pub async fn update_user(
595 &self,
596 id: i64,
597 pass_hash: Option<&str>,
598 is_admin: Option<bool>,
599 active: Option<bool>,
600 roots: Option<&[(String, Mode)]>,
601 ) -> DbResult<()> {
602 let mut c = self.0.lock().await;
603 let tx = c.transaction()?;
604 // An admin sets a password to get someone back into a locked-out
605 // account, so the passkeys and the requirement to use one go with it.
606 // The account is left with exactly one way in, which is the one the
607 // admin just handed over. Doing it always rather than on request means
608 // a reset cannot half-happen: there is no way to set a password and
609 // leave a second factor the user no longer has.
610 if let Some(h) = pass_hash {
611 set_password(&tx, id, h)?;
612 tx.execute("DELETE FROM passkeys WHERE user_id = ?1", [id])?;
613 tx.execute(
614 "UPDATE users SET auth_mode = ?1 WHERE id = ?2",
615 params![SqlAuthMode(AuthMode::Either), id],
616 )?;
617 }
618 if let Some(a) = is_admin {
619 tx.execute(
620 "UPDATE users SET is_admin = ?1 WHERE id = ?2",
621 params![a as i64, id],
622 )?;
623 }
624 if let Some(a) = active {
625 tx.execute(
626 "UPDATE users SET active = ?1 WHERE id = ?2",
627 params![a as i64, id],
628 )?;
629 }
630 if let Some(roots) = roots {
631 tx.execute("DELETE FROM user_roots WHERE user_id = ?1", [id])?;
632 for (path, mode) in roots {
633 tx.execute(
634 "INSERT INTO user_roots (user_id, path, mode) VALUES (?1, ?2, ?3)",
635 params![id, path, SqlMode(*mode)],
636 )?;
637 }
638 }
639 tx.commit()
640 }
641
642 // ---------- credentials: password, passkeys, sign-in mode ----------
643
644 /// Set the password. Leaves every session alone, so the caller must pair
645 /// this with [`Db::delete_other_sessions`] — see
646 /// [`crate::api::passkeys`]'s `invalidate_elsewhere`. The admin path uses
647 /// [`set_password`] instead, which ends every session including its own.
648 ///
649 /// Only for setting a real one. Clearing it is [`Db::clear_user_password`],
650 /// which has a rule to keep.
651 pub async fn set_password_keeping_sessions(&self, id: i64, pass_hash: &str) -> DbResult<()> {
652 let c = self.0.lock().await;
653 c.execute(
654 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
655 params![pass_hash, id],
656 )?;
657 Ok(())
658 }
659
660 /// Leave the account on its passkeys alone. `false` means that would have
661 /// locked it out, so nothing changed.
662 pub async fn clear_user_password(&self, id: i64) -> DbResult<bool> {
663 let mut c = self.0.lock().await;
664 let tx = c.transaction()?;
665 tx.execute(
666 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
667 params![NO_PASSWORD, id],
668 )?;
669 commit_if_reachable(tx, id)
670 }
671
672 /// `false` means the account does not satisfy the new mode, so it stands.
673 pub async fn set_user_auth_mode(&self, id: i64, mode: AuthMode) -> DbResult<bool> {
674 let mut c = self.0.lock().await;
675 let tx = c.transaction()?;
676 tx.execute(
677 "UPDATE users SET auth_mode = ?1 WHERE id = ?2",
678 params![SqlAuthMode(mode), id],
679 )?;
680 commit_if_reachable(tx, id)
681 }
682
683 /// Drop every session of this user except `keep`.
684 ///
685 /// Called after any credential change. Otherwise a session stolen before
686 /// the change keeps working for its full 30 days.
687 pub async fn delete_other_sessions(&self, user_id: i64, keep: &str) -> DbResult<()> {
688 let c = self.0.lock().await;
689 c.execute(
690 "DELETE FROM sessions WHERE user_id = ?1 AND token != ?2",
691 params![user_id, keep],
692 )?;
693 Ok(())
694 }
695
696 /// This account's WebAuthn user handle, creating it on first use.
697 ///
698 /// Stable for the lifetime of the account: a discoverable passkey stores
699 /// this value and hands it back at sign-in, so changing it would orphan
700 /// every existing passkey.
701 pub async fn user_webauthn_id(&self, id: i64) -> DbResult<uuid::Uuid> {
702 let c = self.0.lock().await;
703 let existing: Option<String> = c
704 .query_row("SELECT webauthn_id FROM users WHERE id = ?1", [id], |r| {
705 r.get(0)
706 })
707 .optional()?
708 .flatten();
709 if let Some(parsed) = existing
710 .as_deref()
711 .and_then(|s| uuid::Uuid::parse_str(s).ok())
712 {
713 return Ok(parsed);
714 }
715 let fresh = uuid::Uuid::new_v4();
716 c.execute(
717 "UPDATE users SET webauthn_id = ?1 WHERE id = ?2",
718 params![fresh.to_string(), id],
719 )?;
720 Ok(fresh)
721 }
722
723 /// The account a discoverable credential's user handle points at.
724 pub async fn find_user_by_webauthn_id(&self, wid: &uuid::Uuid) -> DbResult<Option<User>> {
725 let c = self.0.lock().await;
726 c.query_row(
727 &format!("SELECT {USER_COLS} FROM users WHERE webauthn_id = ?1"),
728 [wid.to_string()],
729 map_user,
730 )
731 .optional()
732 }
733
734 pub async fn user_passkeys(&self, user_id: i64) -> DbResult<Vec<PasskeyRow>> {
735 let c = self.0.lock().await;
736 let mut stmt = c.prepare_cached(
737 "SELECT id, name, created_at, last_used_at, discoverable, passkey
738 FROM passkeys WHERE user_id = ?1 ORDER BY id",
739 )?;
740 let rows = stmt.query_map([user_id], map_passkey)?;
741 rows.collect()
742 }
743
744 pub async fn count_passkeys(&self, user_id: i64) -> DbResult<i64> {
745 let c = self.0.lock().await;
746 c.query_row(
747 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
748 [user_id],
749 |r| r.get(0),
750 )
751 }
752
753 /// The per-install secret behind the decoy credentials a named passkey
754 /// challenge is padded with. Created on first use, so no migration.
755 pub async fn decoy_secret(&self) -> DbResult<String> {
756 let c = self.0.lock().await;
757 let existing: Option<String> = c
758 .query_row(
759 "SELECT value FROM meta WHERE key = 'decoy_secret'",
760 [],
761 |r| r.get(0),
762 )
763 .optional()?;
764 if let Some(secret) = existing {
765 return Ok(secret);
766 }
767 let fresh = crate::auth::random_token();
768 c.execute(
769 "INSERT OR REPLACE INTO meta (key, value) VALUES ('decoy_secret', ?1)",
770 [&fresh],
771 )?;
772 Ok(fresh)
773 }
774
775 /// The length in bytes of every credential id registered on this server,
776 /// one entry per credential.
777 ///
778 /// Decoys draw their length from this list. Authenticators disagree on it —
779 /// a security key emits 64 bytes, a platform key often 16 or 32 — so a
780 /// decoy of one fixed length would stand out next to the real entries.
781 ///
782 /// Duplicates are kept deliberately. Drawing from the distinct lengths
783 /// would make a rare length as likely as a common one, and decoys that do
784 /// not match how the install actually looks are the thing worth avoiding.
785 pub async fn cred_id_lengths(&self) -> DbResult<Vec<usize>> {
786 let c = self.0.lock().await;
787 let mut stmt = c.prepare("SELECT length(cred_id) FROM passkeys ORDER BY id")?;
788 let rows = stmt.query_map([], |r| r.get::<_, i64>(0))?;
789 rows.map(|r| r.map(|n| n.max(1) as usize))
790 .collect::<Result<Vec<_>, _>>()
791 }
792
793 /// Store a freshly registered passkey. A duplicate `cred_id` is a unique
794 /// violation, which is the intended answer: the same credential must not
795 /// be registered twice, not even to a second account.
796 ///
797 /// `None` means the account is already at [`PASSKEY_LIMIT`]. The count and
798 /// the insert share one transaction, so two registrations landing together
799 /// cannot put the account one over and give its sign-in challenge a
800 /// telltale length.
801 pub async fn add_passkey(
802 &self,
803 user_id: i64,
804 cred_id: &[u8],
805 passkey: &str,
806 name: &str,
807 discoverable: Option<bool>,
808 ) -> DbResult<Option<PasskeyRow>> {
809 let mut c = self.0.lock().await;
810 let tx = c.transaction()?;
811 let held: i64 = tx.query_row(
812 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
813 [user_id],
814 |r| r.get(0),
815 )?;
816 if held as usize >= PASSKEY_LIMIT {
817 return Ok(None);
818 }
819 tx.execute(
820 "INSERT INTO passkeys (user_id, cred_id, passkey, name, discoverable, created_at)
821 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
822 params![user_id, cred_id, passkey, name, discoverable, now()],
823 )?;
824 let id = tx.last_insert_rowid();
825 let row = tx.query_row(
826 "SELECT id, name, created_at, last_used_at, discoverable, passkey
827 FROM passkeys WHERE id = ?1",
828 [id],
829 map_passkey,
830 )?;
831 tx.commit()?;
832 Ok(Some(row))
833 }
834
835 pub async fn delete_passkey(&self, id: i64, user_id: i64) -> DbResult<PasskeyDeleted> {
836 let mut c = self.0.lock().await;
837 let tx = c.transaction()?;
838 let hit = tx.execute(
839 "DELETE FROM passkeys WHERE id = ?1 AND user_id = ?2",
840 params![id, user_id],
841 )? > 0;
842 if !hit {
843 tx.rollback()?;
844 return Ok(PasskeyDeleted::NotFound);
845 }
846 Ok(if commit_if_reachable(tx, user_id)? {
847 PasskeyDeleted::Gone
848 } else {
849 PasskeyDeleted::LastCredential
850 })
851 }
852
853 /// Record a successful assertion: the re-serialized credential (its
854 /// signature counter and backup flags may have moved) and the time.
855 pub async fn passkey_used(&self, id: i64, passkey: &str) -> DbResult<()> {
856 let c = self.0.lock().await;
857 c.execute(
858 "UPDATE passkeys SET passkey = ?1, last_used_at = ?2 WHERE id = ?3",
859 params![passkey, now(), id],
860 )?;
861 Ok(())
862 }
863
864 /// Delete a user. `false` means no row matched.
865 pub async fn delete_user(&self, id: i64) -> DbResult<bool> {
866 let c = self.0.lock().await;
867 Ok(c.execute("DELETE FROM users WHERE id = ?1", [id])? > 0)
868 }
869
870 // ---------- shares ----------
871
872 #[allow(clippy::too_many_arguments)] // one row's columns, all required
873 pub async fn create_share(
874 &self,
875 creator_id: i64,
876 token: &str,
877 target: &str,
878 is_file: bool,
879 mode: Mode,
880 expires_at: Option<&str>,
881 password_hash: Option<&str>,
882 ) -> DbResult<ShareRow> {
883 let c = self.0.lock().await;
884 c.execute(
885 "INSERT INTO shares
886 (token, creator_id, target, is_file, mode, created_at, expires_at, password_hash)
887 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
888 params![
889 token,
890 creator_id,
891 target,
892 is_file as i64,
893 SqlMode(mode),
894 now(),
895 expires_at,
896 password_hash
897 ],
898 )?;
899 let id = c.last_insert_rowid();
900 Ok(ShareRow {
901 id,
902 token: token.to_string(),
903 creator_id,
904 target: target.to_string(),
905 is_file,
906 mode,
907 created_at: now(),
908 expires_at: expires_at.map(|s| s.to_string()),
909 password_hash: password_hash.map(|s| s.to_string()),
910 })
911 }
912
913 /// Record that a visitor entered `share_id`'s password, and return the
914 /// token that proves it (the value of their unlock cookie).
915 pub async fn create_share_unlock(&self, share_id: i64) -> DbResult<String> {
916 let token = crate::auth::random_token();
917 let c = self.0.lock().await;
918 // Old unlocks go first. The cookie carrying them is a session
919 // cookie, so it is already gone from every browser; without this the
920 // rows would accumulate forever, one per unlock.
921 c.execute(
922 "DELETE FROM share_unlocks WHERE created_at < ?1",
923 [expiry_cutoff()],
924 )?;
925 c.execute(
926 "INSERT INTO share_unlocks (token, share_id, created_at) VALUES (?1, ?2, ?3)",
927 params![token, share_id, now()],
928 )?;
929 Ok(token)
930 }
931
932 /// Whether `token` is a live unlock for `share_id`.
933 ///
934 /// The share id is part of the lookup, so an unlock for one share cannot
935 /// open another.
936 pub async fn share_unlock_valid(&self, token: &str, share_id: i64) -> DbResult<bool> {
937 let c = self.0.lock().await;
938 let mut stmt =
939 c.prepare_cached("SELECT 1 FROM share_unlocks WHERE token = ?1 AND share_id = ?2")?;
940 Ok(stmt
941 .query_row(params![token, share_id], |_| Ok(()))
942 .optional()?
943 .is_some())
944 }
945
946 pub async fn share_by_token(&self, token: &str) -> DbResult<Option<ShareRow>> {
947 let c = self.0.lock().await;
948 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
949 password_hash
950 FROM shares WHERE token = ?1";
951 let mut stmt = c.prepare_cached(sql)?;
952 stmt.query_row([token], map_share).optional()
953 }
954
955 pub async fn user_shares(&self, creator_id: i64) -> DbResult<Vec<ShareRow>> {
956 let c = self.0.lock().await;
957 let sql = "SELECT id, token, creator_id, target, is_file, mode, created_at, expires_at,
958 password_hash
959 FROM shares WHERE creator_id = ?1 ORDER BY id DESC";
960 let mut stmt = c.prepare_cached(sql)?;
961 let rows = stmt.query_map([creator_id], map_share)?;
962 rows.collect()
963 }
964
965 /// Revoke every share on `target` or on anything beneath it. Returns how
966 /// many were dropped.
967 ///
968 /// Called when a path stops meaning what it meant: the item was deleted,
969 /// renamed, or moved away. A share names a path, and a path is not a
970 /// stable identity, so leaving the row behind would let a *new* item that
971 /// later takes the freed path inherit the old link's audience.
972 ///
973 /// `substr` rather than `LIKE`: a target containing `%` or `_` would make
974 /// a `LIKE` pattern over-match and revoke unrelated shares.
975 pub async fn revoke_shares_at(&self, target: &str) -> DbResult<usize> {
976 let c = self.0.lock().await;
977 c.execute(
978 "DELETE FROM shares
979 WHERE target = ?1 OR substr(target, 1, length(?1) + 1) = ?1 || '/'",
980 [target],
981 )
982 }
983
984 /// Delete one of `creator_id`'s shares. `false` means no row matched.
985 pub async fn delete_share(&self, id: i64, creator_id: i64) -> DbResult<bool> {
986 let c = self.0.lock().await;
987 let n = c.execute(
988 "DELETE FROM shares WHERE id = ?1 AND creator_id = ?2",
989 params![id, creator_id],
990 )?;
991 Ok(n > 0)
992 }
993
994 /// Every share on the server with its creator. Grouped by account name,
995 /// newest link within an account first.
996 ///
997 /// The join cannot miss: `shares.creator_id` cascades on delete, so a share
998 /// never outlives the account that made it.
999 pub async fn all_shares_with_creators(&self) -> DbResult<Vec<ShareWithCreator>> {
1000 let c = self.0.lock().await;
1001 // Columns 0..8 are `map_share`'s order, unchanged from `user_shares`.
1002 let sql = "SELECT s.id, s.token, s.creator_id, s.target, s.is_file, s.mode,
1003 s.created_at, s.expires_at, s.password_hash,
1004 u.name, u.active != 0
1005 FROM shares s
1006 JOIN users u ON u.id = s.creator_id
1007 ORDER BY u.name COLLATE NOCASE, s.id DESC";
1008 let mut stmt = c.prepare_cached(sql)?;
1009 let rows = stmt.query_map([], |r| {
1010 Ok(ShareWithCreator {
1011 share: map_share(r)?,
1012 creator_name: r.get(9)?,
1013 creator_active: r.get(10)?,
1014 })
1015 })?;
1016 rows.collect()
1017 }
1018
1019 /// Revoke a share whoever created it. The owner-scoped
1020 /// [`Self::delete_share`] is what the user-facing API uses.
1021 pub async fn admin_delete_share(&self, id: i64) -> DbResult<bool> {
1022 let c = self.0.lock().await;
1023 Ok(c.execute("DELETE FROM shares WHERE id = ?1", [id])? > 0)
1024 }
1025
1026 // ---------- settings ----------
1027
1028 /// Folders excluded from search, as paths relative to the server root.
1029 ///
1030 /// Stored as one JSON array in a settings row. A table of its own would
1031 /// be overkill for a hand-edited list read once per search.
1032 pub async fn search_excludes(&self) -> DbResult<Vec<String>> {
1033 let raw = self.get_setting("search_excludes").await?;
1034 // Normalised on read as well as on write. A value edited straight
1035 // into the database would otherwise never match: `is_excluded`
1036 // compares against paths with no slash at either end.
1037 let clean = |v: Vec<String>| -> Vec<String> {
1038 v.into_iter()
1039 .map(|p| p.trim().replace('\\', "/").trim_matches('/').to_string())
1040 .filter(|p| !p.is_empty() && p != ".")
1041 .collect()
1042 };
1043 // A hand-edited, unparseable value falls back to no exclusions,
1044 // the same as an absent row.
1045 Ok(raw
1046 .as_deref()
1047 .and_then(|v| serde_json::from_str::<Vec<String>>(v).ok())
1048 .map(clean)
1049 .unwrap_or_default())
1050 }
1051
1052 pub async fn set_search_excludes(&self, paths: &[String]) -> DbResult<()> {
1053 let json = serde_json::to_string(paths).unwrap_or_else(|_| "[]".to_string());
1054 self.set_setting("search_excludes", &json).await
1055 }
1056
1057 pub async fn get_setting(&self, key: &str) -> DbResult<Option<String>> {
1058 let c = self.0.lock().await;
1059 let mut stmt = c.prepare_cached("SELECT value FROM settings WHERE key = ?1")?;
1060 stmt.query_row([key], |r| r.get(0)).optional()
1061 }
1062
1063 pub async fn set_setting(&self, key: &str, value: &str) -> DbResult<()> {
1064 let c = self.0.lock().await;
1065 c.execute(
1066 "INSERT INTO settings (key, value) VALUES (?1, ?2)
1067 ON CONFLICT(key) DO UPDATE SET value = ?2",
1068 params![key, value],
1069 )?;
1070 Ok(())
1071 }
1072
1073 /// Whether users may create writable (read-write) shares. Off by default;
1074 /// the admin setting gates it.
1075 pub async fn allow_writable_shares(&self) -> DbResult<bool> {
1076 Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
1077 }
1078
1079 pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
1080 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
1081 .await
1082 }
1083}
1084
1085/// Write a new password hash and drop every session that was opened with the
1086/// old one.
1087///
1088/// The two belong together: a password is changed because the old one is
1089/// suspect (an admin resetting a compromised account), and a session that
1090/// survives the reset leaves whoever holds it signed in. Takes the
1091/// transaction so the caller can bundle it with its other edits.
1092fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
1093 tx.execute(
1094 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
1095 params![pass_hash, id],
1096 )?;
1097 tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
1098 Ok(())
1099}
1100
1101/// Commit a credential change, or undo it if the account would be left with
1102/// no way to sign in.
1103///
1104/// The handlers check the same rules first, so the person gets a message that
1105/// says which rule. This is the guarantee behind those checks: a count read
1106/// before the write can already be stale when the write lands, and two
1107/// requests racing could otherwise take away the last credential between them.
1108/// `false` means the change was rolled back.
1109fn commit_if_reachable(tx: rusqlite::Transaction<'_>, user_id: i64) -> DbResult<bool> {
1110 let (has_password, mode) = tx.query_row(
1111 "SELECT pass_hash != '', auth_mode FROM users WHERE id = ?1",
1112 [user_id],
1113 |r| Ok((r.get::<_, bool>(0)?, r.get::<_, SqlAuthMode>(1)?)),
1114 )?;
1115 let passkeys: i64 = tx.query_row(
1116 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
1117 [user_id],
1118 |r| r.get(0),
1119 )?;
1120 let reachable = match mode.0 {
1121 AuthMode::Either => has_password || passkeys > 0,
1122 AuthMode::Both => has_password && passkeys > 0,
1123 };
1124 if !reachable {
1125 tx.rollback()?;
1126 return Ok(false);
1127 }
1128 tx.commit()?;
1129 Ok(true)
1130}
1131
1132/// Column order matched by [`USER_COLS`] and [`USER_COLS_U`].
1133fn map_user(r: &rusqlite::Row) -> DbResult<User> {
1134 Ok(User {
1135 id: r.get(0)?,
1136 name: r.get(1)?,
1137 is_admin: r.get(2)?,
1138 active: r.get(3)?,
1139 single_click: r.get(4)?,
1140 thumbnails: r.get(5)?,
1141 language: r.get(6)?,
1142 default_root_id: r.get(7)?,
1143 auth_mode: r.get::<_, SqlAuthMode>(8)?.0,
1144 has_password: r.get(9)?,
1145 })
1146}
1147
1148/// Column order matched by the `passkeys` SELECTs above.
1149fn map_passkey(r: &rusqlite::Row) -> DbResult<PasskeyRow> {
1150 Ok(PasskeyRow {
1151 id: r.get(0)?,
1152 name: r.get(1)?,
1153 created_at: r.get(2)?,
1154 last_used_at: r.get(3)?,
1155 discoverable: r.get(4)?,
1156 passkey: r.get(5)?,
1157 })
1158}
1159
1160/// Column order matched by the two `shares` SELECTs above.
1161fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
1162 Ok(ShareRow {
1163 id: r.get(0)?,
1164 token: r.get(1)?,
1165 creator_id: r.get(2)?,
1166 target: r.get(3)?,
1167 is_file: r.get::<_, i64>(4)? != 0,
1168 mode: r.get::<_, SqlMode>(5)?.0,
1169 created_at: r.get(6)?,
1170 expires_at: r.get(7)?,
1171 password_hash: r.get(8)?,
1172 })
1173}
1174
1175/// A hash of a random string nobody knows. Verified against when the login
1176/// name does not exist, so both paths cost one Argon2 run.
1177static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
1178 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
1179});
1180
1181/// How long an unlock row outlives its cookie. The cookie dies with the
1182/// browser, so this only bounds the rows left behind by closed sessions.
1183const UNLOCK_MAX_AGE_DAYS: i64 = 7;
1184
1185/// The timestamp an unlock row must be newer than to survive a cleanup.
1186fn expiry_cutoff() -> String {
1187 (chrono::Utc::now() - chrono::Duration::days(UNLOCK_MAX_AGE_DAYS))
1188 .to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
1189}
1190
1191fn now() -> String {
1192 chrono::Utc::now().to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
1193}
1194
1195const SCHEMA_V1: &str = r#"
1196CREATE TABLE IF NOT EXISTS users (
1197 id INTEGER PRIMARY KEY AUTOINCREMENT,
1198 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
1199 pass_hash TEXT NOT NULL,
1200 is_admin INTEGER NOT NULL DEFAULT 0,
1201 created_at TEXT NOT NULL
1202);
1203
1204CREATE TABLE IF NOT EXISTS user_roots (
1205 id INTEGER PRIMARY KEY AUTOINCREMENT,
1206 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1207 path TEXT NOT NULL,
1208 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
1209 UNIQUE (user_id, path)
1210);
1211
1212CREATE TABLE IF NOT EXISTS shares (
1213 id INTEGER PRIMARY KEY AUTOINCREMENT,
1214 token TEXT NOT NULL UNIQUE,
1215 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1216 target TEXT NOT NULL,
1217 is_file INTEGER NOT NULL,
1218 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
1219 created_at TEXT NOT NULL,
1220 expires_at TEXT
1221);
1222
1223CREATE TABLE IF NOT EXISTS sessions (
1224 token TEXT PRIMARY KEY,
1225 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1226 created_at TEXT NOT NULL,
1227 last_seen_at TEXT NOT NULL
1228);
1229
1230CREATE TABLE IF NOT EXISTS settings (
1231 key TEXT PRIMARY KEY,
1232 value TEXT NOT NULL
1233);
1234INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
1235"#;
1236
1237#[cfg(test)]
1238mod tests {
1239 use super::*;
1240
1241 // Most tests use an in-memory DB (the file-based path is still covered
1242 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
1243 async fn mem() -> Db {
1244 Db::open_in_memory().await.unwrap()
1245 }
1246
1247 /// `update_user` is the only way production edits these fields, so the
1248 /// tests exercise that path rather than per-field helpers.
1249 async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
1250 db.update_user(id, pass, admin, active, None).await.unwrap();
1251 }
1252
1253 async fn db_with_admin() -> (Db, User) {
1254 let db = mem().await;
1255 let hash = crate::auth::hash_password("admin1234").unwrap();
1256 let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
1257 (db, admin)
1258 }
1259
1260 #[tokio::test]
1261 async fn fresh_db_state() {
1262 let db = mem().await;
1263 assert_eq!(db.user_count().await.unwrap(), 0);
1264 assert_eq!(db.count_admins().await.unwrap(), 0);
1265 assert!(!db.allow_writable_shares().await.unwrap());
1266 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
1267 assert!(db.find_user_by_id(1).await.unwrap().is_none());
1268 assert!(db.all_users_with_roots().await.unwrap().is_empty());
1269 }
1270
1271 #[tokio::test]
1272 async fn v1_db_migrates_to_v2() {
1273 let dir = tempfile::tempdir().unwrap();
1274 let path = dir.path().join("legacy.sqlite");
1275 {
1276 let conn = rusqlite::Connection::open(&path).unwrap();
1277 conn.execute_batch(SCHEMA_V1).unwrap();
1278 conn.execute(
1279 "INSERT INTO users (name, pass_hash, is_admin, created_at)
1280 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
1281 [],
1282 )
1283 .unwrap();
1284 conn.execute(
1285 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
1286 [],
1287 )
1288 .unwrap();
1289 }
1290 let db = Db::open(&path).await.unwrap();
1291 assert_eq!(db.user_count().await.unwrap(), 1);
1292 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
1293 assert!(u.active, "v2 migration must default active to true");
1294 assert!(u.is_admin);
1295 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
1296 // Migrations are idempotent.
1297 let db2 = Db::open(&path).await.unwrap();
1298 assert_eq!(db2.user_count().await.unwrap(), 1);
1299 assert!(
1300 db2.find_user_by_name("legacy")
1301 .await
1302 .unwrap()
1303 .unwrap()
1304 .active
1305 );
1306 }
1307
1308 #[tokio::test]
1309 async fn admin_user_and_passwords() {
1310 let (db, admin) = db_with_admin().await;
1311 assert!(admin.is_admin);
1312 assert!(admin.active);
1313 // Root "." rw is assigned by create_admin.
1314 let roots = db.user_roots(admin.id).await.unwrap();
1315 assert_eq!(roots.len(), 1);
1316 assert_eq!(roots[0].path, ".");
1317 assert_eq!(roots[0].mode, Mode::Rw);
1318
1319 assert!(
1320 db.verify_password("admin", "admin1234")
1321 .await
1322 .unwrap()
1323 .is_some()
1324 );
1325 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
1326 // Name lookup is case-insensitive (COLLATE NOCASE).
1327 assert!(
1328 db.verify_password("ADMIN", "admin1234")
1329 .await
1330 .unwrap()
1331 .is_some()
1332 );
1333 // Disabled users cannot verify.
1334 edit(&db, admin.id, None, None, Some(false)).await;
1335 assert!(
1336 db.verify_password("admin", "admin1234")
1337 .await
1338 .unwrap()
1339 .is_none()
1340 );
1341 edit(&db, admin.id, None, None, Some(true)).await;
1342 assert!(
1343 db.verify_password("admin", "admin1234")
1344 .await
1345 .unwrap()
1346 .is_some()
1347 );
1348 }
1349
1350 #[tokio::test]
1351 async fn setup_is_won_by_exactly_one_caller() {
1352 let db = mem().await;
1353 let hash = crate::auth::hash_password("admin1234").unwrap();
1354 assert!(db.create_admin("first", &hash).await.unwrap().is_some());
1355 // The guard lives in the insert, so a different name loses too.
1356 assert!(db.create_admin("second", &hash).await.unwrap().is_none());
1357 assert_eq!(db.user_count().await.unwrap(), 1);
1358 // The loser rolled back cleanly: no orphaned root row.
1359 let first = db.find_user_by_name("first").await.unwrap().unwrap();
1360 assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
1361 }
1362
1363 #[tokio::test]
1364 async fn changing_a_password_drops_that_users_sessions() {
1365 let (db, admin) = db_with_admin().await;
1366 let h = crate::auth::hash_password("bobpass1").unwrap();
1367 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
1368 db.create_session(admin.id, "admin-tok").await.unwrap();
1369 db.create_session(bob.id, "bob-tok-1").await.unwrap();
1370 db.create_session(bob.id, "bob-tok-2").await.unwrap();
1371
1372 let new_h = crate::auth::hash_password("bobpass2").unwrap();
1373 edit(&db, bob.id, Some(&new_h), None, None).await;
1374 assert!(
1375 db.session_user_with_roots("bob-tok-1")
1376 .await
1377 .unwrap()
1378 .is_none()
1379 );
1380 assert!(
1381 db.session_user_with_roots("bob-tok-2")
1382 .await
1383 .unwrap()
1384 .is_none()
1385 );
1386 // Only the reset user is signed out.
1387 assert!(
1388 db.session_user_with_roots("admin-tok")
1389 .await
1390 .unwrap()
1391 .is_some()
1392 );
1393
1394 // The admin-edit path bundles the same rule into its transaction.
1395 db.create_session(bob.id, "bob-tok-3").await.unwrap();
1396 let h3 = crate::auth::hash_password("bobpass3").unwrap();
1397 db.update_user(bob.id, Some(&h3), None, None, None)
1398 .await
1399 .unwrap();
1400 assert!(
1401 db.session_user_with_roots("bob-tok-3")
1402 .await
1403 .unwrap()
1404 .is_none()
1405 );
1406 // An edit that leaves the password alone keeps the session.
1407 db.create_session(bob.id, "bob-tok-4").await.unwrap();
1408 db.update_user(bob.id, None, Some(true), None, None)
1409 .await
1410 .unwrap();
1411 assert!(
1412 db.session_user_with_roots("bob-tok-4")
1413 .await
1414 .unwrap()
1415 .is_some()
1416 );
1417 }
1418
1419 #[tokio::test]
1420 async fn sessions_lifecycle() {
1421 let (db, admin) = db_with_admin().await;
1422 assert!(
1423 db.session_user_with_roots("ghost-token")
1424 .await
1425 .unwrap()
1426 .is_none()
1427 );
1428 db.create_session(admin.id, "tok1").await.unwrap();
1429 let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
1430 assert_eq!(u.id, admin.id);
1431 // Disabling the user invalidates existing sessions.
1432 edit(&db, admin.id, None, None, Some(false)).await;
1433 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
1434 edit(&db, admin.id, None, None, Some(true)).await;
1435 assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
1436 db.delete_session("tok1").await.unwrap();
1437 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
1438 }
1439
1440 #[tokio::test]
1441 async fn user_crud_and_roots() {
1442 let (db, _admin) = db_with_admin().await;
1443 let h = crate::auth::hash_password("bobpass1").unwrap();
1444 let bob = db
1445 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
1446 .await
1447 .unwrap();
1448 assert!(!bob.is_admin);
1449 assert!(bob.active);
1450
1451 // Duplicate name (case-insensitive) is rejected.
1452 let h2 = crate::auth::hash_password("carolpass1").unwrap();
1453 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
1454 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
1455
1456 // Lookup helpers.
1457 assert_eq!(
1458 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
1459 bob.id
1460 );
1461 assert_eq!(
1462 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
1463 "bob"
1464 );
1465 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
1466 assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
1467
1468 // Root replacement semantics.
1469 let roots = db.user_roots(bob.id).await.unwrap();
1470 assert_eq!(roots.len(), 1);
1471 db.update_user(
1472 bob.id,
1473 None,
1474 None,
1475 None,
1476 Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
1477 )
1478 .await
1479 .unwrap();
1480 let roots = db.user_roots(bob.id).await.unwrap();
1481 assert_eq!(roots.len(), 2);
1482 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
1483 db.update_user(bob.id, None, None, None, Some(&[]))
1484 .await
1485 .unwrap();
1486 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
1487
1488 // Password update.
1489 let new_h = crate::auth::hash_password("bobpass2").unwrap();
1490 edit(&db, bob.id, Some(&new_h), None, None).await;
1491 assert!(
1492 db.verify_password("bob", "bobpass1")
1493 .await
1494 .unwrap()
1495 .is_none()
1496 );
1497 assert!(
1498 db.verify_password("bob", "bobpass2")
1499 .await
1500 .unwrap()
1501 .is_some()
1502 );
1503
1504 // Admin flag + count (only active admins count).
1505 edit(&db, bob.id, None, Some(true), None).await;
1506 assert_eq!(db.count_admins().await.unwrap(), 2);
1507 edit(&db, bob.id, None, None, Some(false)).await;
1508 assert_eq!(db.count_admins().await.unwrap(), 1);
1509 edit(&db, bob.id, None, Some(false), None).await;
1510
1511 // Deletion.
1512 assert!(db.delete_user(bob.id).await.unwrap());
1513 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
1514 assert!(!db.delete_user(bob.id).await.unwrap());
1515 assert_eq!(db.user_count().await.unwrap(), 2);
1516 }
1517
1518 fn share_row(expires_at: Option<&str>) -> ShareRow {
1519 ShareRow {
1520 id: 1,
1521 token: "t".into(),
1522 creator_id: 1,
1523 target: "docs".into(),
1524 is_file: false,
1525 mode: Mode::Ro,
1526 created_at: "2024-01-01T00:00:00Z".into(),
1527 expires_at: expires_at.map(str::to_string),
1528 password_hash: None,
1529 }
1530 }
1531
1532 #[test]
1533 fn share_expiry_logic() {
1534 assert!(!share_row(None).is_expired());
1535 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
1536 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
1537 // Unparseable expiry → treated as not expired (fail open for reads).
1538 assert!(!share_row(Some("not-a-date")).is_expired());
1539 }
1540
1541 #[tokio::test]
1542 async fn shares_crud() {
1543 let (db, admin) = db_with_admin().await;
1544 let s1 = db
1545 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
1546 .await
1547 .unwrap();
1548 let s2 = db
1549 .create_share(
1550 admin.id,
1551 "tok-b",
1552 "file.txt",
1553 true,
1554 Mode::Rw,
1555 Some("2999-01-01T00:00:00Z"),
1556 None,
1557 )
1558 .await
1559 .unwrap();
1560 assert!(s2.id > s1.id);
1561
1562 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
1563 assert!(found.is_file);
1564 assert_eq!(found.mode, Mode::Rw);
1565 assert!(db.share_by_token("nope").await.unwrap().is_none());
1566
1567 // Listed newest-first.
1568 let list = db.user_shares(admin.id).await.unwrap();
1569 assert_eq!(list.len(), 2);
1570 assert_eq!(list[0].id, s2.id);
1571 // Other users see nothing.
1572 let h = crate::auth::hash_password("bobpass1").unwrap();
1573 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
1574 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
1575
1576 // Only the creator can delete.
1577 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
1578 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
1579 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
1580 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
1581 }
1582
1583 /// The unlock token is what a visitor's cookie carries, so an unlock
1584 /// that opened the wrong share would be a full bypass of the password.
1585 #[tokio::test]
1586 async fn share_unlocks_are_bound_to_one_share() {
1587 let (db, admin) = db_with_admin().await;
1588 let a = db
1589 .create_share(
1590 admin.id,
1591 "tok-a",
1592 "docs",
1593 false,
1594 Mode::Ro,
1595 None,
1596 Some("hash"),
1597 )
1598 .await
1599 .unwrap();
1600 let b = db
1601 .create_share(
1602 admin.id,
1603 "tok-b",
1604 "other",
1605 false,
1606 Mode::Ro,
1607 None,
1608 Some("hash"),
1609 )
1610 .await
1611 .unwrap();
1612 assert_eq!(
1613 db.share_by_token("tok-a")
1614 .await
1615 .unwrap()
1616 .unwrap()
1617 .password_hash,
1618 Some("hash".to_string())
1619 );
1620
1621 let unlock = db.create_share_unlock(a.id).await.unwrap();
1622 assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
1623 assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
1624 assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
1625
1626 // Deleting the share takes its unlocks with it, so a re-created
1627 // share that happened to reuse the id could not inherit them.
1628 assert!(db.delete_share(a.id, admin.id).await.unwrap());
1629 assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
1630 }
1631
1632 #[tokio::test]
1633 async fn revoking_a_path_takes_its_descendants_only() {
1634 let (db, admin) = db_with_admin().await;
1635 let mk = async |token: &str, target: &str| {
1636 db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
1637 .await
1638 .unwrap();
1639 };
1640 mk("t-self", "docs").await;
1641 mk("t-child", "docs/a.txt").await;
1642 mk("t-deep", "docs/inner/b.txt").await;
1643 // A sibling whose name merely starts with "docs" must survive.
1644 mk("t-sibling", "docs2/c.txt").await;
1645 mk("t-other", "src").await;
1646 // SQL wildcards in a path are literal characters, not patterns.
1647 mk("t-wild", "do%s/d.txt").await;
1648
1649 assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
1650 for gone in ["t-self", "t-child", "t-deep"] {
1651 assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
1652 }
1653 for kept in ["t-sibling", "t-other", "t-wild"] {
1654 assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
1655 }
1656 // Revoking a path nobody shared is a no-op, not an error.
1657 assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
1658 }
1659
1660 #[tokio::test]
1661 async fn settings_round_trip() {
1662 let (db, _admin) = db_with_admin().await;
1663 assert!(!db.allow_writable_shares().await.unwrap());
1664 db.set_allow_writable_shares(true).await.unwrap();
1665 assert!(db.allow_writable_shares().await.unwrap());
1666 // Upsert semantics.
1667 db.set_allow_writable_shares(false).await.unwrap();
1668 assert!(!db.allow_writable_shares().await.unwrap());
1669 // Generic get/set.
1670 db.set_setting("custom", "v").await.unwrap();
1671 assert_eq!(
1672 db.get_setting("custom").await.unwrap().as_deref(),
1673 Some("v")
1674 );
1675 assert_eq!(db.get_setting("missing").await.unwrap(), None);
1676 }
1677}
1678