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