dovenest

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