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