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