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