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![
1275 user_id,
1276 "cal",
1277 "Calendar",
1278 "VEVENT,VTODO,VJOURNAL",
1279 now
1280 ])?;
1281 stmt.execute(params![user_id, "card", "Contacts", "", now])?;
1282 Ok(())
1283 }
1284
1285 pub async fn pim_collections(
1286 &self,
1287 user_id: i64,
1288 kind: PimKind,
1289 ) -> DbResult<Vec<PimCollection>> {
1290 let c = self.0.lock().await;
1291 let mut stmt = c.prepare_cached(&format!(
1292 "SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1293 WHERE user_id = ?1 AND kind = ?2 ORDER BY id"
1294 ))?;
1295 stmt.query_map(params![user_id, kind.as_str()], map_pim_collection)?
1296 .collect()
1297 }
1298
1299 pub async fn pim_collection(
1300 &self,
1301 user_id: i64,
1302 kind: PimKind,
1303 slug: &str,
1304 ) -> DbResult<Option<PimCollection>> {
1305 let c = self.0.lock().await;
1306 let mut stmt = c.prepare_cached(&format!(
1307 "SELECT {PIM_COLLECTION_COLS} FROM pim_collections
1308 WHERE user_id = ?1 AND kind = ?2 AND slug = ?3"
1309 ))?;
1310 stmt.query_row(params![user_id, kind.as_str(), slug], map_pim_collection)
1311 .optional()
1312 }
1313
1314 /// `false` if the slug is taken. `id` and `seq` of `new` are ignored.
1315 pub async fn pim_create_collection(
1316 &self,
1317 user_id: i64,
1318 kind: PimKind,
1319 new: &PimCollection,
1320 ) -> DbResult<bool> {
1321 let c = self.0.lock().await;
1322 let n = c.execute(
1323 "INSERT OR IGNORE INTO pim_collections (user_id, kind, slug, displayname,
1324 description, color, timezone, sort_order, components, created_at)
1325 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10)",
1326 params![
1327 user_id,
1328 kind.as_str(),
1329 new.slug,
1330 new.displayname,
1331 new.description,
1332 new.color,
1333 new.timezone,
1334 new.sort_order,
1335 new.components,
1336 now()
1337 ],
1338 )?;
1339 Ok(n > 0)
1340 }
1341
1342 /// Writes the properties of `col` and counts it as a change.
1343 pub async fn pim_update_collection(&self, col: &PimCollection) -> DbResult<()> {
1344 let c = self.0.lock().await;
1345 c.execute(
1346 "UPDATE pim_collections SET displayname = ?2, description = ?3, color = ?4,
1347 timezone = ?5, sort_order = ?6, seq = seq + 1
1348 WHERE id = ?1",
1349 params![
1350 col.id,
1351 col.displayname,
1352 col.description,
1353 col.color,
1354 col.timezone,
1355 col.sort_order
1356 ],
1357 )?;
1358 Ok(())
1359 }
1360
1361 pub async fn pim_delete_collection(&self, id: i64) -> DbResult<()> {
1362 let c = self.0.lock().await;
1363 c.execute("DELETE FROM pim_collections WHERE id = ?1", [id])?;
1364 Ok(())
1365 }
1366
1367 pub async fn pim_objects(&self, collection_id: i64) -> DbResult<Vec<PimObject>> {
1368 let c = self.0.lock().await;
1369 let mut stmt = c.prepare_cached(&format!(
1370 "SELECT {PIM_OBJECT_COLS} FROM pim_objects WHERE collection_id = ?1 ORDER BY name"
1371 ))?;
1372 stmt.query_map([collection_id], map_pim_object)?.collect()
1373 }
1374
1375 pub async fn pim_object(
1376 &self,
1377 collection_id: i64,
1378 name: &str,
1379 ) -> DbResult<Option<(PimObject, Vec<u8>)>> {
1380 let c = self.0.lock().await;
1381 let mut stmt = c.prepare_cached(&format!(
1382 "SELECT {PIM_OBJECT_COLS}, data FROM pim_objects
1383 WHERE collection_id = ?1 AND name = ?2"
1384 ))?;
1385 stmt.query_row(params![collection_id, name], |r| {
1386 Ok((map_pim_object(r)?, r.get(6)?))
1387 })
1388 .optional()
1389 }
1390
1391 pub async fn pim_objects_with_data(
1392 &self,
1393 collection_id: i64,
1394 ) -> DbResult<Vec<(PimObject, Vec<u8>)>> {
1395 let c = self.0.lock().await;
1396 let mut stmt = c.prepare_cached(&format!(
1397 "SELECT {PIM_OBJECT_COLS}, data FROM pim_objects WHERE collection_id = ?1 ORDER BY name"
1398 ))?;
1399 stmt.query_map([collection_id], |r| Ok((map_pim_object(r)?, r.get(6)?)))?
1400 .collect()
1401 }
1402
1403 /// `(name, seq, deleted)` of the members changed after `since`, oldest
1404 /// first. Without `since`, the members that exist.
1405 pub async fn pim_changes(
1406 &self,
1407 collection_id: i64,
1408 since: Option<i64>,
1409 ) -> DbResult<Vec<(String, i64, bool)>> {
1410 let c = self.0.lock().await;
1411 let mut stmt = c.prepare_cached(
1412 "SELECT name, seq, deleted != 0 FROM pim_changes
1413 WHERE collection_id = ?1 AND (?2 IS NULL AND deleted = 0 OR seq > ?2)
1414 ORDER BY seq",
1415 )?;
1416 stmt.query_map(params![collection_id, since], |r| {
1417 Ok((r.get(0)?, r.get(1)?, r.get(2)?))
1418 })?
1419 .collect()
1420 }
1421
1422 /// Moves an object to `to_name` in collection `to`, which may be the
1423 /// same collection. `Updated` means an object at the destination was
1424 /// replaced.
1425 pub async fn pim_move_object(
1426 &self,
1427 from: i64,
1428 name: &str,
1429 to: i64,
1430 to_name: &str,
1431 overwrite: bool,
1432 cond: &Precondition,
1433 ) -> DbResult<PimWrite> {
1434 let mut c = self.0.lock().await;
1435 let tx = c.transaction()?;
1436 let source: Option<(String, String)> = tx
1437 .query_row(
1438 "SELECT etag, uid FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1439 params![from, name],
1440 |r| Ok((r.get(0)?, r.get(1)?)),
1441 )
1442 .optional()?;
1443 let Some((etag, uid)) = source else {
1444 return Ok(PimWrite::NotFound);
1445 };
1446 if !cond.allows(Some(&etag)) {
1447 return Ok(PimWrite::PreconditionFailed);
1448 }
1449 let taken: bool = tx.query_row(
1450 "SELECT EXISTS (SELECT 1 FROM pim_objects WHERE collection_id = ?1 AND name = ?2)",
1451 params![to, to_name],
1452 |r| r.get(0),
1453 )?;
1454 if taken && !overwrite {
1455 return Ok(PimWrite::PreconditionFailed);
1456 }
1457 let holder: Option<String> = tx
1458 .query_row(
1459 "SELECT name FROM pim_objects WHERE collection_id = ?1 AND uid = ?2
1460 AND name != ?3 AND NOT (collection_id = ?4 AND name = ?5)",
1461 params![to, uid, to_name, from, name],
1462 |r| r.get(0),
1463 )
1464 .optional()?;
1465 if let Some(holder) = holder {
1466 return Ok(PimWrite::UidConflict(holder));
1467 }
1468 tx.execute(
1469 "DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1470 params![to, to_name],
1471 )?;
1472 tx.execute(
1473 "UPDATE pim_objects SET collection_id = ?3, name = ?4, modified_at = ?5
1474 WHERE collection_id = ?1 AND name = ?2",
1475 params![from, name, to, to_name, now()],
1476 )?;
1477 record_pim_change(&tx, from, name, true)?;
1478 record_pim_change(&tx, to, to_name, false)?;
1479 tx.commit()?;
1480 Ok(if taken {
1481 PimWrite::Updated
1482 } else {
1483 PimWrite::Created
1484 })
1485 }
1486
1487 /// Stores an object under `obj.name`. The precondition and the UID check
1488 /// run in the same transaction as the write.
1489 pub async fn pim_put_object(
1490 &self,
1491 collection_id: i64,
1492 obj: &PimObject,
1493 data: &[u8],
1494 cond: &Precondition,
1495 ) -> DbResult<PimWrite> {
1496 let mut c = self.0.lock().await;
1497 let tx = c.transaction()?;
1498 let current: Option<String> = tx
1499 .query_row(
1500 "SELECT etag FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1501 params![collection_id, obj.name],
1502 |r| r.get(0),
1503 )
1504 .optional()?;
1505 if !cond.allows(current.as_deref()) {
1506 return Ok(PimWrite::PreconditionFailed);
1507 }
1508 let holder: Option<String> = tx
1509 .query_row(
1510 "SELECT name FROM pim_objects
1511 WHERE collection_id = ?1 AND uid = ?2 AND name != ?3",
1512 params![collection_id, obj.uid, obj.name],
1513 |r| r.get(0),
1514 )
1515 .optional()?;
1516 if let Some(holder) = holder {
1517 return Ok(PimWrite::UidConflict(holder));
1518 }
1519 tx.execute(
1520 "INSERT INTO pim_objects (collection_id, name, uid, component, data, etag, modified_at)
1521 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)
1522 ON CONFLICT (collection_id, name) DO UPDATE SET uid = ?3, component = ?4,
1523 data = ?5, etag = ?6, modified_at = ?7",
1524 params![
1525 collection_id,
1526 obj.name,
1527 obj.uid,
1528 obj.component,
1529 data,
1530 obj.etag,
1531 now()
1532 ],
1533 )?;
1534 record_pim_change(&tx, collection_id, &obj.name, false)?;
1535 tx.commit()?;
1536 Ok(match current {
1537 Some(_) => PimWrite::Updated,
1538 None => PimWrite::Created,
1539 })
1540 }
1541
1542 pub async fn pim_delete_object(
1543 &self,
1544 collection_id: i64,
1545 name: &str,
1546 cond: &Precondition,
1547 ) -> DbResult<PimWrite> {
1548 let mut c = self.0.lock().await;
1549 let tx = c.transaction()?;
1550 let current: Option<String> = tx
1551 .query_row(
1552 "SELECT etag FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1553 params![collection_id, name],
1554 |r| r.get(0),
1555 )
1556 .optional()?;
1557 if current.is_none() {
1558 return Ok(PimWrite::NotFound);
1559 }
1560 if !cond.allows(current.as_deref()) {
1561 return Ok(PimWrite::PreconditionFailed);
1562 }
1563 tx.execute(
1564 "DELETE FROM pim_objects WHERE collection_id = ?1 AND name = ?2",
1565 params![collection_id, name],
1566 )?;
1567 record_pim_change(&tx, collection_id, name, true)?;
1568 tx.commit()?;
1569 Ok(PimWrite::Deleted)
1570 }
1571
1572 /// Whether users may create writable (read-write) shares. Off by default;
1573 /// the admin setting gates it.
1574 pub async fn allow_writable_shares(&self) -> DbResult<bool> {
1575 Ok(self.get_setting("allow_writable_shares").await?.as_deref() == Some("1"))
1576 }
1577
1578 pub async fn set_allow_writable_shares(&self, v: bool) -> DbResult<()> {
1579 self.set_setting("allow_writable_shares", if v { "1" } else { "0" })
1580 .await
1581 }
1582}
1583
1584/// Write a new password hash and drop every session that was opened with the
1585/// old one.
1586///
1587/// The two belong together: a password is changed because the old one is
1588/// suspect (an admin resetting a compromised account), and a session that
1589/// survives the reset leaves whoever holds it signed in. Takes the
1590/// transaction so the caller can bundle it with its other edits.
1591fn set_password(tx: &rusqlite::Transaction<'_>, id: i64, pass_hash: &str) -> DbResult<()> {
1592 tx.execute(
1593 "UPDATE users SET pass_hash = ?1 WHERE id = ?2",
1594 params![pass_hash, id],
1595 )?;
1596 tx.execute("DELETE FROM sessions WHERE user_id = ?1", [id])?;
1597 Ok(())
1598}
1599
1600/// Commit a credential change, or undo it if the account would be left with
1601/// no way to sign in.
1602///
1603/// The handlers check the same rules first, so the person gets a message that
1604/// says which rule. This is the guarantee behind those checks: a count read
1605/// before the write can already be stale when the write lands, and two
1606/// requests racing could otherwise take away the last credential between them.
1607/// `false` means the change was rolled back.
1608fn commit_if_reachable(tx: rusqlite::Transaction<'_>, user_id: i64) -> DbResult<bool> {
1609 let (has_password, mode) = tx.query_row(
1610 "SELECT pass_hash != '', auth_mode FROM users WHERE id = ?1",
1611 [user_id],
1612 |r| Ok((r.get::<_, bool>(0)?, r.get::<_, SqlAuthMode>(1)?)),
1613 )?;
1614 let passkeys: i64 = tx.query_row(
1615 "SELECT COUNT(*) FROM passkeys WHERE user_id = ?1",
1616 [user_id],
1617 |r| r.get(0),
1618 )?;
1619 let reachable = match mode.0 {
1620 AuthMode::Either => has_password || passkeys > 0,
1621 AuthMode::Both => has_password && passkeys > 0,
1622 };
1623 if !reachable {
1624 tx.rollback()?;
1625 return Ok(false);
1626 }
1627 tx.commit()?;
1628 Ok(true)
1629}
1630
1631/// Column order matched by [`USER_COLS`] and [`USER_COLS_U`].
1632fn map_user(r: &rusqlite::Row) -> DbResult<User> {
1633 Ok(User {
1634 id: r.get(0)?,
1635 name: r.get(1)?,
1636 is_admin: r.get(2)?,
1637 active: r.get(3)?,
1638 single_click: r.get(4)?,
1639 thumbnails: r.get(5)?,
1640 language: r.get(6)?,
1641 default_root_id: r.get(7)?,
1642 auth_mode: r.get::<_, SqlAuthMode>(8)?.0,
1643 has_password: r.get(9)?,
1644 })
1645}
1646
1647/// Column order matched by the `passkeys` SELECTs above.
1648fn map_passkey(r: &rusqlite::Row) -> DbResult<PasskeyRow> {
1649 Ok(PasskeyRow {
1650 id: r.get(0)?,
1651 name: r.get(1)?,
1652 created_at: r.get(2)?,
1653 last_used_at: r.get(3)?,
1654 discoverable: r.get(4)?,
1655 passkey: r.get(5)?,
1656 })
1657}
1658
1659/// A just-created account: the column defaults of `users`.
1660fn new_user(id: i64, name: &str, is_admin: bool) -> User {
1661 User {
1662 id,
1663 name: name.to_string(),
1664 is_admin,
1665 active: true,
1666 single_click: false,
1667 thumbnails: true,
1668 language: None,
1669 default_root_id: None,
1670 auth_mode: AuthMode::Either,
1671 has_password: true,
1672 }
1673}
1674
1675/// Column order matched by the `app_passwords` SELECTs above.
1676fn map_app_password(r: &rusqlite::Row) -> DbResult<AppPasswordInfo> {
1677 Ok(AppPasswordInfo {
1678 id: r.get(0)?,
1679 name: r.get(1)?,
1680 created_at: r.get(2)?,
1681 last_used_at: r.get(3)?,
1682 })
1683}
1684
1685/// Bumps the collection's `seq` and records it as the latest change of `name`.
1686fn record_pim_change(
1687 tx: &rusqlite::Transaction,
1688 collection_id: i64,
1689 name: &str,
1690 deleted: bool,
1691) -> DbResult<()> {
1692 let seq: i64 = tx.query_row(
1693 "UPDATE pim_collections SET seq = seq + 1 WHERE id = ?1 RETURNING seq",
1694 [collection_id],
1695 |r| r.get(0),
1696 )?;
1697 tx.execute(
1698 "INSERT INTO pim_changes (collection_id, name, seq, deleted) VALUES (?1, ?2, ?3, ?4)
1699 ON CONFLICT (collection_id, name) DO UPDATE SET seq = ?3, deleted = ?4",
1700 params![collection_id, name, seq, deleted],
1701 )?;
1702 Ok(())
1703}
1704
1705const PIM_COLLECTION_COLS: &str = "id, slug, displayname, description, color, timezone,
1706 sort_order, components, seq";
1707
1708fn map_pim_collection(r: &rusqlite::Row) -> DbResult<PimCollection> {
1709 Ok(PimCollection {
1710 id: r.get(0)?,
1711 slug: r.get(1)?,
1712 displayname: r.get(2)?,
1713 description: r.get(3)?,
1714 color: r.get(4)?,
1715 timezone: r.get(5)?,
1716 sort_order: r.get(6)?,
1717 components: r.get(7)?,
1718 seq: r.get(8)?,
1719 })
1720}
1721
1722const PIM_OBJECT_COLS: &str = "name, uid, component, etag, length(data), modified_at";
1723
1724fn map_pim_object(r: &rusqlite::Row) -> DbResult<PimObject> {
1725 Ok(PimObject {
1726 name: r.get(0)?,
1727 uid: r.get(1)?,
1728 component: r.get(2)?,
1729 etag: r.get(3)?,
1730 size: r.get(4)?,
1731 modified_at: r.get(5)?,
1732 })
1733}
1734
1735/// Column order matched by the two `shares` SELECTs above.
1736fn map_share(r: &rusqlite::Row) -> DbResult<ShareRow> {
1737 Ok(ShareRow {
1738 id: r.get(0)?,
1739 token: r.get(1)?,
1740 creator_id: r.get(2)?,
1741 target: r.get(3)?,
1742 is_file: r.get::<_, i64>(4)? != 0,
1743 mode: r.get::<_, SqlMode>(5)?.0,
1744 created_at: r.get(6)?,
1745 expires_at: r.get(7)?,
1746 password_hash: r.get(8)?,
1747 })
1748}
1749
1750/// A hash of a random string nobody knows. Verified against when the login
1751/// name does not exist, so both paths cost one Argon2 run.
1752static DUMMY_HASH: std::sync::LazyLock<String> = std::sync::LazyLock::new(|| {
1753 crate::auth::hash_password(&crate::auth::random_token()).expect("argon2 hash")
1754});
1755
1756/// How long an unlock row outlives its cookie. The cookie dies with the
1757/// browser, so this only bounds the rows left behind by closed sessions.
1758const UNLOCK_MAX_AGE_DAYS: i64 = 7;
1759
1760/// How often [`Db::sweep`] runs. A share the picker can only set to the
1761/// minute is gone from the owner's list about when it says it is.
1762const SWEEP_EVERY: std::time::Duration = std::time::Duration::from_secs(60);
1763
1764/// Run [`Db::sweep`] until the process ends. A failed pass is logged and
1765/// retried on the next one: nothing downstream depends on it having run.
1766pub async fn sweep_forever(db: Db) {
1767 loop {
1768 tokio::time::sleep(SWEEP_EVERY).await;
1769 match db.sweep().await {
1770 Ok((0, 0)) => {}
1771 Ok((shares, unlocks)) => tracing::debug!(shares, unlocks, "swept expired shares"),
1772 Err(e) => tracing::warn!(error = %e, "the share sweep failed"),
1773 }
1774 }
1775}
1776
1777/// The time `ago` in the past as a stored stamp. The format sorts
1778/// lexicographically, so SQL can compare stamps as text.
1779fn stamp(ago: chrono::Duration) -> String {
1780 (chrono::Utc::now() - ago).to_rfc3339_opts(chrono::SecondsFormat::Secs, true)
1781}
1782
1783fn now() -> String {
1784 stamp(chrono::Duration::zero())
1785}
1786
1787const SCHEMA_V1: &str = r#"
1788CREATE TABLE IF NOT EXISTS users (
1789 id INTEGER PRIMARY KEY AUTOINCREMENT,
1790 name TEXT NOT NULL UNIQUE COLLATE NOCASE,
1791 pass_hash TEXT NOT NULL,
1792 is_admin INTEGER NOT NULL DEFAULT 0,
1793 created_at TEXT NOT NULL
1794);
1795
1796CREATE TABLE IF NOT EXISTS user_roots (
1797 id INTEGER PRIMARY KEY AUTOINCREMENT,
1798 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1799 path TEXT NOT NULL,
1800 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
1801 UNIQUE (user_id, path)
1802);
1803
1804CREATE TABLE IF NOT EXISTS shares (
1805 id INTEGER PRIMARY KEY AUTOINCREMENT,
1806 token TEXT NOT NULL UNIQUE,
1807 creator_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1808 target TEXT NOT NULL,
1809 is_file INTEGER NOT NULL,
1810 mode TEXT NOT NULL CHECK (mode IN ('rw','ro')),
1811 created_at TEXT NOT NULL,
1812 expires_at TEXT
1813);
1814
1815CREATE TABLE IF NOT EXISTS sessions (
1816 token TEXT PRIMARY KEY,
1817 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
1818 created_at TEXT NOT NULL,
1819 last_seen_at TEXT NOT NULL
1820);
1821
1822CREATE TABLE IF NOT EXISTS settings (
1823 key TEXT PRIMARY KEY,
1824 value TEXT NOT NULL
1825);
1826INSERT OR IGNORE INTO settings (key, value) VALUES ('allow_writable_shares', '0');
1827"#;
1828
1829#[cfg(test)]
1830mod tests {
1831 use super::*;
1832
1833 // Most tests use an in-memory DB (the file-based path is still covered
1834 // by `v1_db_migrates_to_v2` and the integration harness' `Db::open`).
1835 async fn mem() -> Db {
1836 Db::open(Path::new(":memory:")).await.unwrap()
1837 }
1838
1839 /// `update_user` is the only way production edits these fields, so the
1840 /// tests exercise that path rather than per-field helpers.
1841 async fn edit(db: &Db, id: i64, pass: Option<&str>, admin: Option<bool>, active: Option<bool>) {
1842 db.update_user(id, pass, admin, active, None).await.unwrap();
1843 }
1844
1845 /// Backdate a stamp, which production code has no reason to do.
1846 async fn set_last_used(db: &Db, secret_hash: &str, at: &str) {
1847 db.0.lock()
1848 .await
1849 .execute(
1850 "UPDATE app_passwords SET last_used_at = ?1 WHERE secret_hash = ?2",
1851 params![at, secret_hash],
1852 )
1853 .unwrap();
1854 }
1855
1856 async fn db_with_admin() -> (Db, User) {
1857 let db = mem().await;
1858 let hash = crate::auth::hash_password("admin1234").unwrap();
1859 let admin = db.create_admin("admin", &hash).await.unwrap().unwrap();
1860 (db, admin)
1861 }
1862
1863 #[tokio::test]
1864 async fn fresh_db_state() {
1865 let db = mem().await;
1866 assert_eq!(db.user_count().await.unwrap(), 0);
1867 assert_eq!(db.count_admins().await.unwrap(), 0);
1868 assert!(!db.allow_writable_shares().await.unwrap());
1869 assert!(db.find_user_by_name("nobody").await.unwrap().is_none());
1870 assert!(db.find_user_by_id(1).await.unwrap().is_none());
1871 assert!(db.all_users_with_roots().await.unwrap().is_empty());
1872 }
1873
1874 #[tokio::test]
1875 async fn app_passwords_open_one_account_and_stamp_their_use() {
1876 let (db, admin) = db_with_admin().await;
1877 let secret = crate::auth::short_token();
1878 let hash = crate::auth::app_password_hash(&secret);
1879 let row = db
1880 .add_app_password(admin.id, "laptop", &hash)
1881 .await
1882 .unwrap()
1883 .unwrap();
1884 assert_eq!(row.name, "laptop");
1885 assert!(row.last_used_at.is_none());
1886
1887 let hit = db.user_by_app_password(&hash).await.unwrap().unwrap();
1888 assert_eq!(hit.id, admin.id);
1889 let listed = |db: &Db| {
1890 let db = db.clone();
1891 async move {
1892 db.app_passwords(admin.id).await.unwrap()[0]
1893 .last_used_at
1894 .clone()
1895 }
1896 };
1897 assert!(listed(&db).await.is_some(), "first use must stamp the row");
1898
1899 // Ten minutes back is inside the hour window, and far enough from
1900 // `now()` that a rewrite would show at second resolution.
1901 let inside = stamp(chrono::Duration::minutes(10));
1902 set_last_used(&db, &hash, &inside).await;
1903 db.user_by_app_password(&hash).await.unwrap().unwrap();
1904 assert_eq!(
1905 listed(&db).await.as_deref(),
1906 Some(inside.as_str()),
1907 "a second use inside the hour wrote the stamp again"
1908 );
1909
1910 let outside = stamp(chrono::Duration::hours(2));
1911 set_last_used(&db, &hash, &outside).await;
1912 db.user_by_app_password(&hash).await.unwrap().unwrap();
1913 assert_ne!(
1914 listed(&db).await.as_deref(),
1915 Some(outside.as_str()),
1916 "a use after the window left the stamp stale"
1917 );
1918
1919 // The raw secret is not the key, and an inactive account does not match.
1920 let other = crate::auth::app_password_hash(&crate::auth::short_token());
1921 assert!(db.user_by_app_password(&other).await.unwrap().is_none());
1922 assert!(db.user_by_app_password(&secret).await.unwrap().is_none());
1923 edit(&db, admin.id, None, None, Some(false)).await;
1924 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
1925 edit(&db, admin.id, None, None, Some(true)).await;
1926 assert!(db.user_by_app_password(&hash).await.unwrap().is_some());
1927
1928 for i in 1..APP_PASSWORD_LIMIT {
1929 let h = crate::auth::app_password_hash(&crate::auth::short_token());
1930 assert!(
1931 db.add_app_password(admin.id, &format!("c{i}"), &h)
1932 .await
1933 .unwrap()
1934 .is_some()
1935 );
1936 }
1937 let h = crate::auth::app_password_hash(&crate::auth::short_token());
1938 assert!(
1939 db.add_app_password(admin.id, "one-too-many", &h)
1940 .await
1941 .unwrap()
1942 .is_none()
1943 );
1944
1945 assert!(db.delete_app_password(row.id, admin.id).await.unwrap());
1946 assert!(!db.delete_app_password(row.id, admin.id).await.unwrap());
1947 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
1948 }
1949
1950 #[tokio::test]
1951 async fn an_admin_password_reset_revokes_the_app_passwords() {
1952 let (db, admin) = db_with_admin().await;
1953 let hash = crate::auth::app_password_hash(&crate::auth::short_token());
1954 db.add_app_password(admin.id, "mount", &hash)
1955 .await
1956 .unwrap()
1957 .unwrap();
1958
1959 // An edit that sets no password leaves them alone.
1960 edit(&db, admin.id, None, None, Some(true)).await;
1961 assert_eq!(db.app_passwords(admin.id).await.unwrap().len(), 1);
1962
1963 // A reset is meant to revoke every way in that existed before it.
1964 let fresh = crate::auth::hash_password("rescued12").unwrap();
1965 edit(&db, admin.id, Some(&fresh), None, None).await;
1966 assert!(db.app_passwords(admin.id).await.unwrap().is_empty());
1967 assert!(db.user_by_app_password(&hash).await.unwrap().is_none());
1968 }
1969
1970 #[tokio::test]
1971 async fn v1_db_migrates_to_v2() {
1972 let dir = tempfile::tempdir().unwrap();
1973 let path = dir.path().join("legacy.sqlite");
1974 {
1975 let conn = rusqlite::Connection::open(&path).unwrap();
1976 conn.execute_batch(SCHEMA_V1).unwrap();
1977 conn.execute(
1978 "INSERT INTO users (name, pass_hash, is_admin, created_at)
1979 VALUES ('legacy', 'hash', 1, '2024-01-01T00:00:00Z')",
1980 [],
1981 )
1982 .unwrap();
1983 conn.execute(
1984 "INSERT INTO user_roots (user_id, path, mode) VALUES (1, 'docs', 'rw')",
1985 [],
1986 )
1987 .unwrap();
1988 }
1989 let db = Db::open(&path).await.unwrap();
1990 assert_eq!(db.user_count().await.unwrap(), 1);
1991 let u = db.find_user_by_name("legacy").await.unwrap().unwrap();
1992 assert!(u.active, "v2 migration must default active to true");
1993 assert!(u.is_admin);
1994 assert_eq!(db.user_roots(u.id).await.unwrap().len(), 1);
1995 // Migrations are idempotent.
1996 let db2 = Db::open(&path).await.unwrap();
1997 assert_eq!(db2.user_count().await.unwrap(), 1);
1998 assert!(
1999 db2.find_user_by_name("legacy")
2000 .await
2001 .unwrap()
2002 .unwrap()
2003 .active
2004 );
2005 }
2006
2007 #[tokio::test]
2008 async fn admin_user_and_passwords() {
2009 let (db, admin) = db_with_admin().await;
2010 assert!(admin.is_admin);
2011 assert!(admin.active);
2012 // Root "." rw is assigned by create_admin.
2013 let roots = db.user_roots(admin.id).await.unwrap();
2014 assert_eq!(roots.len(), 1);
2015 assert_eq!(roots[0].path, ".");
2016 assert_eq!(roots[0].mode, Mode::Rw);
2017
2018 assert!(
2019 db.verify_password("admin", "admin1234")
2020 .await
2021 .unwrap()
2022 .is_some()
2023 );
2024 assert!(db.verify_password("admin", "nope").await.unwrap().is_none());
2025 // Name lookup is case-insensitive (COLLATE NOCASE).
2026 assert!(
2027 db.verify_password("ADMIN", "admin1234")
2028 .await
2029 .unwrap()
2030 .is_some()
2031 );
2032 // Disabled users cannot verify.
2033 edit(&db, admin.id, None, None, Some(false)).await;
2034 assert!(
2035 db.verify_password("admin", "admin1234")
2036 .await
2037 .unwrap()
2038 .is_none()
2039 );
2040 edit(&db, admin.id, None, None, Some(true)).await;
2041 assert!(
2042 db.verify_password("admin", "admin1234")
2043 .await
2044 .unwrap()
2045 .is_some()
2046 );
2047 }
2048
2049 #[tokio::test]
2050 async fn setup_is_won_by_exactly_one_caller() {
2051 let db = mem().await;
2052 let hash = crate::auth::hash_password("admin1234").unwrap();
2053 assert!(db.create_admin("first", &hash).await.unwrap().is_some());
2054 // The guard lives in the insert, so a different name loses too.
2055 assert!(db.create_admin("second", &hash).await.unwrap().is_none());
2056 assert_eq!(db.user_count().await.unwrap(), 1);
2057 // The loser rolled back cleanly: no orphaned root row.
2058 let first = db.find_user_by_name("first").await.unwrap().unwrap();
2059 assert_eq!(db.user_roots(first.id).await.unwrap().len(), 1);
2060 }
2061
2062 #[tokio::test]
2063 async fn changing_a_password_drops_that_users_sessions() {
2064 let (db, admin) = db_with_admin().await;
2065 let h = crate::auth::hash_password("bobpass1").unwrap();
2066 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
2067 db.create_session(admin.id, "admin-tok").await.unwrap();
2068 db.create_session(bob.id, "bob-tok-1").await.unwrap();
2069 db.create_session(bob.id, "bob-tok-2").await.unwrap();
2070
2071 let new_h = crate::auth::hash_password("bobpass2").unwrap();
2072 edit(&db, bob.id, Some(&new_h), None, None).await;
2073 assert!(
2074 db.session_user_with_roots("bob-tok-1")
2075 .await
2076 .unwrap()
2077 .is_none()
2078 );
2079 assert!(
2080 db.session_user_with_roots("bob-tok-2")
2081 .await
2082 .unwrap()
2083 .is_none()
2084 );
2085 // Only the reset user is signed out.
2086 assert!(
2087 db.session_user_with_roots("admin-tok")
2088 .await
2089 .unwrap()
2090 .is_some()
2091 );
2092
2093 // The admin-edit path bundles the same rule into its transaction.
2094 db.create_session(bob.id, "bob-tok-3").await.unwrap();
2095 let h3 = crate::auth::hash_password("bobpass3").unwrap();
2096 db.update_user(bob.id, Some(&h3), None, None, None)
2097 .await
2098 .unwrap();
2099 assert!(
2100 db.session_user_with_roots("bob-tok-3")
2101 .await
2102 .unwrap()
2103 .is_none()
2104 );
2105 // An edit that leaves the password alone keeps the session.
2106 db.create_session(bob.id, "bob-tok-4").await.unwrap();
2107 db.update_user(bob.id, None, Some(true), None, None)
2108 .await
2109 .unwrap();
2110 assert!(
2111 db.session_user_with_roots("bob-tok-4")
2112 .await
2113 .unwrap()
2114 .is_some()
2115 );
2116 }
2117
2118 #[tokio::test]
2119 async fn sessions_lifecycle() {
2120 let (db, admin) = db_with_admin().await;
2121 assert!(
2122 db.session_user_with_roots("ghost-token")
2123 .await
2124 .unwrap()
2125 .is_none()
2126 );
2127 db.create_session(admin.id, "tok1").await.unwrap();
2128 let (u, _) = db.session_user_with_roots("tok1").await.unwrap().unwrap();
2129 assert_eq!(u.id, admin.id);
2130 // Disabling the user invalidates existing sessions.
2131 edit(&db, admin.id, None, None, Some(false)).await;
2132 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
2133 edit(&db, admin.id, None, None, Some(true)).await;
2134 assert!(db.session_user_with_roots("tok1").await.unwrap().is_some());
2135 db.delete_session("tok1").await.unwrap();
2136 assert!(db.session_user_with_roots("tok1").await.unwrap().is_none());
2137 }
2138
2139 #[tokio::test]
2140 async fn user_crud_and_roots() {
2141 let (db, _admin) = db_with_admin().await;
2142 let h = crate::auth::hash_password("bobpass1").unwrap();
2143 let bob = db
2144 .create_user("bob", &h, false, &[("docs".into(), Mode::Rw)])
2145 .await
2146 .unwrap();
2147 assert!(!bob.is_admin);
2148 assert!(bob.active);
2149
2150 // Duplicate name (case-insensitive) is rejected.
2151 let h2 = crate::auth::hash_password("carolpass1").unwrap();
2152 assert!(db.create_user("BOB", &h2, false, &[]).await.is_err());
2153 assert!(db.create_user("carol", &h2, false, &[]).await.is_ok());
2154
2155 // Lookup helpers.
2156 assert_eq!(
2157 db.find_user_by_name("Bob").await.unwrap().unwrap().id,
2158 bob.id
2159 );
2160 assert_eq!(
2161 db.find_user_by_id(bob.id).await.unwrap().unwrap().name,
2162 "bob"
2163 );
2164 assert!(db.find_user_by_name("dave").await.unwrap().is_none());
2165 assert_eq!(db.all_users_with_roots().await.unwrap().len(), 3);
2166
2167 // Root replacement semantics.
2168 let roots = db.user_roots(bob.id).await.unwrap();
2169 assert_eq!(roots.len(), 1);
2170 db.update_user(
2171 bob.id,
2172 None,
2173 None,
2174 None,
2175 Some(&[(".".into(), Mode::Ro), ("docs".into(), Mode::Rw)]),
2176 )
2177 .await
2178 .unwrap();
2179 let roots = db.user_roots(bob.id).await.unwrap();
2180 assert_eq!(roots.len(), 2);
2181 assert!(roots.iter().any(|r| r.path == "." && r.mode == Mode::Ro));
2182 db.update_user(bob.id, None, None, None, Some(&[]))
2183 .await
2184 .unwrap();
2185 assert!(db.user_roots(bob.id).await.unwrap().is_empty());
2186
2187 // Password update.
2188 let new_h = crate::auth::hash_password("bobpass2").unwrap();
2189 edit(&db, bob.id, Some(&new_h), None, None).await;
2190 assert!(
2191 db.verify_password("bob", "bobpass1")
2192 .await
2193 .unwrap()
2194 .is_none()
2195 );
2196 assert!(
2197 db.verify_password("bob", "bobpass2")
2198 .await
2199 .unwrap()
2200 .is_some()
2201 );
2202
2203 // Admin flag + count (only active admins count).
2204 edit(&db, bob.id, None, Some(true), None).await;
2205 assert_eq!(db.count_admins().await.unwrap(), 2);
2206 edit(&db, bob.id, None, None, Some(false)).await;
2207 assert_eq!(db.count_admins().await.unwrap(), 1);
2208 edit(&db, bob.id, None, Some(false), None).await;
2209
2210 // Deletion.
2211 assert!(db.delete_user(bob.id).await.unwrap());
2212 assert!(db.find_user_by_id(bob.id).await.unwrap().is_none());
2213 assert!(!db.delete_user(bob.id).await.unwrap());
2214 assert_eq!(db.user_count().await.unwrap(), 2);
2215 }
2216
2217 fn share_row(expires_at: Option<&str>) -> ShareRow {
2218 ShareRow {
2219 id: 1,
2220 token: "t".into(),
2221 creator_id: 1,
2222 target: "docs".into(),
2223 is_file: false,
2224 mode: Mode::Ro,
2225 created_at: "2024-01-01T00:00:00Z".into(),
2226 expires_at: expires_at.map(str::to_string),
2227 password_hash: None,
2228 }
2229 }
2230
2231 #[test]
2232 fn share_expiry_logic() {
2233 assert!(!share_row(None).is_expired());
2234 assert!(!share_row(Some("2999-01-01T00:00:00Z")).is_expired());
2235 assert!(share_row(Some("2000-01-01T00:00:00Z")).is_expired());
2236 // Unparseable expiry → treated as not expired (fail open for reads).
2237 assert!(!share_row(Some("not-a-date")).is_expired());
2238 }
2239
2240 #[tokio::test]
2241 async fn shares_crud() {
2242 let (db, admin) = db_with_admin().await;
2243 let s1 = db
2244 .create_share(admin.id, "tok-a", "docs", false, Mode::Ro, None, None)
2245 .await
2246 .unwrap();
2247 let s2 = db
2248 .create_share(
2249 admin.id,
2250 "tok-b",
2251 "file.txt",
2252 true,
2253 Mode::Rw,
2254 Some("2999-01-01T00:00:00Z"),
2255 None,
2256 )
2257 .await
2258 .unwrap();
2259 assert!(s2.id > s1.id);
2260
2261 let found = db.share_by_token("tok-b").await.unwrap().unwrap();
2262 assert!(found.is_file);
2263 assert_eq!(found.mode, Mode::Rw);
2264 assert!(db.share_by_token("nope").await.unwrap().is_none());
2265
2266 // Listed newest-first.
2267 let list = db.user_shares(admin.id).await.unwrap();
2268 assert_eq!(list.len(), 2);
2269 assert_eq!(list[0].id, s2.id);
2270 // Other users see nothing.
2271 let h = crate::auth::hash_password("bobpass1").unwrap();
2272 let bob = db.create_user("bob", &h, false, &[]).await.unwrap();
2273 assert!(db.user_shares(bob.id).await.unwrap().is_empty());
2274
2275 // Only the creator can delete.
2276 assert!(!db.delete_share(s1.id, bob.id).await.unwrap());
2277 assert!(db.delete_share(s1.id, admin.id).await.unwrap());
2278 assert!(db.share_by_token("tok-a").await.unwrap().is_none());
2279 assert!(!db.delete_share(s1.id, admin.id).await.unwrap());
2280 }
2281
2282 /// The sweep decides which timestamps are past, and `expires_at` is
2283 /// stored in whatever RFC 3339 shape the client sent.
2284 #[tokio::test]
2285 async fn the_sweep_drops_expired_shares_and_stale_unlocks() {
2286 let (db, admin) = db_with_admin().await;
2287 let make = async |token: &str, expires: Option<&str>| {
2288 db.create_share(admin.id, token, "docs", false, Mode::Ro, expires, None)
2289 .await
2290 .unwrap()
2291 };
2292 let past_offset = make("gone-offset", Some("2000-01-01T00:00:00+02:00")).await;
2293 make("gone-utc", Some("2000-01-01T00:00:00Z")).await;
2294 let future = make("stays-future", Some("2999-01-01T00:00:00Z")).await;
2295 make("stays-forever", None).await;
2296 // `is_expired` keeps an unreadable timestamp; the sweep must agree,
2297 // or the two disagree about the same row.
2298 make("stays-garbage", Some("not-a-date")).await;
2299
2300 let fresh = db.create_share_unlock(future.id).await.unwrap();
2301 let stale = db.create_share_unlock(future.id).await.unwrap();
2302 let doomed = db.create_share_unlock(past_offset.id).await.unwrap();
2303 {
2304 let c = db.0.lock().await;
2305 c.execute(
2306 "UPDATE share_unlocks SET created_at = '2000-01-01T00:00:00Z' WHERE token = ?1",
2307 [&stale],
2308 )
2309 .unwrap();
2310 }
2311
2312 let (shares, unlocks) = db.sweep().await.unwrap();
2313 assert_eq!(shares, 2, "only the two past timestamps");
2314 // The stale one, plus the cascade from the expired share it opened.
2315 assert_eq!(unlocks, 1, "the cascade runs with the share, not here");
2316
2317 for token in ["gone-offset", "gone-utc"] {
2318 assert!(db.share_by_token(token).await.unwrap().is_none(), "{token}");
2319 }
2320 for token in ["stays-future", "stays-forever", "stays-garbage"] {
2321 assert!(db.share_by_token(token).await.unwrap().is_some(), "{token}");
2322 }
2323
2324 assert!(db.share_unlock_valid(&fresh, future.id).await.unwrap());
2325 assert!(!db.share_unlock_valid(&stale, future.id).await.unwrap());
2326 assert!(
2327 !db.share_unlock_valid(&doomed, past_offset.id)
2328 .await
2329 .unwrap(),
2330 "an unlock must not outlive the share it opened"
2331 );
2332
2333 assert_eq!(
2334 db.sweep().await.unwrap(),
2335 (0, 0),
2336 "a second pass is a no-op"
2337 );
2338 }
2339
2340 /// The unlock token is what a visitor's cookie carries, so an unlock
2341 /// that opened the wrong share would be a full bypass of the password.
2342 #[tokio::test]
2343 async fn share_unlocks_are_bound_to_one_share() {
2344 let (db, admin) = db_with_admin().await;
2345 let a = db
2346 .create_share(
2347 admin.id,
2348 "tok-a",
2349 "docs",
2350 false,
2351 Mode::Ro,
2352 None,
2353 Some("hash"),
2354 )
2355 .await
2356 .unwrap();
2357 let b = db
2358 .create_share(
2359 admin.id,
2360 "tok-b",
2361 "other",
2362 false,
2363 Mode::Ro,
2364 None,
2365 Some("hash"),
2366 )
2367 .await
2368 .unwrap();
2369 assert_eq!(
2370 db.share_by_token("tok-a")
2371 .await
2372 .unwrap()
2373 .unwrap()
2374 .password_hash,
2375 Some("hash".to_string())
2376 );
2377
2378 let unlock = db.create_share_unlock(a.id).await.unwrap();
2379 assert!(db.share_unlock_valid(&unlock, a.id).await.unwrap());
2380 assert!(!db.share_unlock_valid(&unlock, b.id).await.unwrap());
2381 assert!(!db.share_unlock_valid("nonsense", a.id).await.unwrap());
2382
2383 // Deleting the share takes its unlocks with it, so a re-created
2384 // share that happened to reuse the id could not inherit them.
2385 assert!(db.delete_share(a.id, admin.id).await.unwrap());
2386 assert!(!db.share_unlock_valid(&unlock, a.id).await.unwrap());
2387 }
2388
2389 #[tokio::test]
2390 async fn revoking_a_path_takes_its_descendants_only() {
2391 let (db, admin) = db_with_admin().await;
2392 let mk = async |token: &str, target: &str| {
2393 db.create_share(admin.id, token, target, false, Mode::Ro, None, None)
2394 .await
2395 .unwrap();
2396 };
2397 mk("t-self", "docs").await;
2398 mk("t-child", "docs/a.txt").await;
2399 mk("t-deep", "docs/inner/b.txt").await;
2400 // A sibling whose name merely starts with "docs" must survive.
2401 mk("t-sibling", "docs2/c.txt").await;
2402 mk("t-other", "src").await;
2403 // SQL wildcards in a path are literal characters, not patterns.
2404 mk("t-wild", "do%s/d.txt").await;
2405
2406 assert_eq!(db.revoke_shares_at("docs").await.unwrap(), 3);
2407 for gone in ["t-self", "t-child", "t-deep"] {
2408 assert!(db.share_by_token(gone).await.unwrap().is_none(), "{gone}");
2409 }
2410 for kept in ["t-sibling", "t-other", "t-wild"] {
2411 assert!(db.share_by_token(kept).await.unwrap().is_some(), "{kept}");
2412 }
2413 // Revoking a path nobody shared is a no-op, not an error.
2414 assert_eq!(db.revoke_shares_at("nothing/here").await.unwrap(), 0);
2415 }
2416
2417 #[tokio::test]
2418 async fn settings_round_trip() {
2419 let (db, _admin) = db_with_admin().await;
2420 assert!(!db.allow_writable_shares().await.unwrap());
2421 db.set_allow_writable_shares(true).await.unwrap();
2422 assert!(db.allow_writable_shares().await.unwrap());
2423 // Upsert semantics.
2424 db.set_allow_writable_shares(false).await.unwrap();
2425 assert!(!db.allow_writable_shares().await.unwrap());
2426 // Generic get/set.
2427 db.set_setting("custom", "v").await.unwrap();
2428 assert_eq!(
2429 db.get_setting("custom").await.unwrap().as_deref(),
2430 Some("v")
2431 );
2432 assert_eq!(db.get_setting("missing").await.unwrap(), None);
2433 }
2434}
2435