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