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