laterite_auth/
store.rs

1//! Data access for the auth schema.
2//!
3//! Free functions over a [`Db`] (pool plus backend). Queries are built with
4//! `sea-query` and bound through `laterite_core::query`, so they run on any
5//! supported backend. Portability at the boundary: ids are `bigint`
6//! auto-increment keys (the database assigns them, read back through
7//! [`laterite_core::query::insert_returning_id`]), timestamps are stored as text
8//! and converted to `DateTime<Utc>` here, and permission collections are stored
9//! as JSON text.
10//!
11//! Session rows are the exception to the module being public. Reading one is
12//! what pushes its idle clock forward, so a caller that reached the row directly
13//! would authenticate a request and leave the session ageing as though the
14//! request never happened. The session functions are therefore crate-visible and
15//! [`crate::AuthService`] is the only way in: the renewal cannot be skipped
16//! because there is no path that skips it.
17
18use std::collections::HashMap;
19
20use chrono::{DateTime, SecondsFormat, Utc};
21use laterite_core::query::{
22    bind_values, bind_values_as, build, insert_returning_id, on_conflict_ignore,
23};
24use laterite_core::{AnyRowExt, Db};
25use sea_query::{Expr, Order, Query};
26use sqlx::any::AnyRow;
27use sqlx::Row;
28
29use crate::error::AuthError;
30use crate::models::{AccessEvent, BackendUser, BackendUserSummary};
31use crate::schema::{
32    BackendAccessLog, BackendAuditLog, BackendRememberTokens, BackendRoles, BackendSessions,
33    BackendUserPreferences, BackendUserRoles, BackendUsers,
34};
35
36fn now_ts() -> String {
37    Utc::now().to_rfc3339_opts(SecondsFormat::Micros, true)
38}
39
40fn ts(dt: DateTime<Utc>) -> String {
41    dt.to_rfc3339_opts(SecondsFormat::Micros, true)
42}
43
44fn parse_ts(s: &str) -> Result<DateTime<Utc>, AuthError> {
45    DateTime::parse_from_rfc3339(s)
46        .map(|d| d.with_timezone(&Utc))
47        .map_err(|e| AuthError::Data(format!("timestamp `{s}`: {e}")))
48}
49
50/// Normalises a user-facing key (username or email) so lookups, uniqueness, and
51/// login throttling behave identically on every backend. MySQL's default
52/// collation is case-insensitive (and trailing-space-insensitive) while Postgres
53/// and SQLite are case-sensitive, so the framework lower-cases and trims these
54/// keys before storing or matching. Apply it on every write and lookup of a
55/// username or email so `Root` and `root ` resolve to the same account anywhere.
56fn normalize_key(s: &str) -> String {
57    s.trim().to_lowercase()
58}
59
60fn user_from_row(row: &AnyRow) -> Result<BackendUser, AuthError> {
61    Ok(BackendUser {
62        id: row.try_get::<i64, _>("id")?,
63        username: row.get_text("username")?,
64        email: row.get_text("email")?,
65        first_name: row.get_text("first_name")?,
66        last_name: row.get_text_opt("last_name")?,
67        password_hash: row.get_text("password_hash")?,
68        is_superuser: row.get_bool("is_superuser")?,
69        is_active: row.get_bool("is_active")?,
70        timezone: row.get_text_opt("timezone")?,
71        locale: row.get_text_opt("locale")?,
72        created_at: parse_ts(&row.get_text("created_at")?)?,
73        updated_at: parse_ts(&row.get_text("updated_at")?)?,
74    })
75}
76
77fn summary_from_row(row: &AnyRow) -> Result<BackendUserSummary, AuthError> {
78    Ok(BackendUserSummary {
79        id: row.try_get::<i64, _>("id")?,
80        username: row.get_text("username")?,
81        email: row.get_text("email")?,
82        first_name: row.get_text("first_name")?,
83        last_name: row.get_text_opt("last_name")?,
84        is_superuser: row.get_bool("is_superuser")?,
85        is_active: row.get_bool("is_active")?,
86        created_at: parse_ts(&row.get_text("created_at")?)?,
87    })
88}
89
90const USER_COLS: [BackendUsers; 12] = [
91    BackendUsers::Id,
92    BackendUsers::Username,
93    BackendUsers::Email,
94    BackendUsers::FirstName,
95    BackendUsers::LastName,
96    BackendUsers::PasswordHash,
97    BackendUsers::IsSuperuser,
98    BackendUsers::IsActive,
99    BackendUsers::Timezone,
100    BackendUsers::Locale,
101    BackendUsers::CreatedAt,
102    BackendUsers::UpdatedAt,
103];
104
105/// Looks up a user by username without filtering on active state.
106pub async fn find_user_by_username(
107    db: &Db,
108    username: &str,
109) -> Result<Option<BackendUser>, AuthError> {
110    let (sql, values) = build(
111        db.backend,
112        Query::select()
113            .columns(USER_COLS)
114            .from(BackendUsers::Table)
115            .and_where(Expr::col(BackendUsers::Username).eq(normalize_key(username)))
116            .to_owned(),
117    );
118    let row = bind_values(sqlx::query(&sql), values)
119        .fetch_optional(&db.pool)
120        .await?;
121    row.map(|r| user_from_row(&r)).transpose()
122}
123
124/// Looks up an active user by id, used when resolving a session to an identity.
125/// A user by id whatever their state. Distinct from
126/// [`find_active_user_by_id`], which is the authentication path: this one is for
127/// administering an account, where a deactivated one still has to be readable.
128pub(crate) async fn find_user_by_id(db: &Db, id: i64) -> Result<Option<BackendUser>, AuthError> {
129    let (sql, values) = build(
130        db.backend,
131        Query::select()
132            .columns(USER_COLS)
133            .from(BackendUsers::Table)
134            .and_where(Expr::col(BackendUsers::Id).eq(id))
135            .to_owned(),
136    );
137    let row = bind_values(sqlx::query(&sql), values)
138        .fetch_optional(&db.pool)
139        .await?;
140    row.map(|r| user_from_row(&r)).transpose()
141}
142
143pub async fn find_active_user_by_id(db: &Db, id: i64) -> Result<Option<BackendUser>, AuthError> {
144    let (sql, values) = build(
145        db.backend,
146        Query::select()
147            .columns(USER_COLS)
148            .from(BackendUsers::Table)
149            .and_where(Expr::col(BackendUsers::Id).eq(id))
150            .and_where(Expr::col(BackendUsers::IsActive).eq(true))
151            .to_owned(),
152    );
153    let row = bind_values(sqlx::query(&sql), values)
154        .fetch_optional(&db.pool)
155        .await?;
156    row.map(|r| user_from_row(&r)).transpose()
157}
158
159/// Returns the permission lists of every role assigned to a user (each stored
160/// as a JSON array), for the service to flatten into a permission set.
161pub async fn load_role_permissions(db: &Db, user_id: i64) -> Result<Vec<Vec<String>>, AuthError> {
162    let (sql, values) = build(
163        db.backend,
164        Query::select()
165            .column((BackendRoles::Table, BackendRoles::Permissions))
166            .from(BackendUserRoles::Table)
167            .inner_join(
168                BackendRoles::Table,
169                Expr::col((BackendRoles::Table, BackendRoles::Id))
170                    .equals((BackendUserRoles::Table, BackendUserRoles::BackendRoleId)),
171            )
172            .and_where(
173                Expr::col((BackendUserRoles::Table, BackendUserRoles::BackendUserId)).eq(user_id),
174            )
175            .to_owned(),
176    );
177    let rows = bind_values(sqlx::query(&sql), values)
178        .fetch_all(&db.pool)
179        .await?;
180    let mut out = Vec::with_capacity(rows.len());
181    for row in rows {
182        let json = row.get_text("permissions")?;
183        let perms: Vec<String> = serde_json::from_str(&json)
184            .map_err(|e| AuthError::Data(format!("role permissions: {e}")))?;
185        out.push(perms);
186    }
187    Ok(out)
188}
189
190/// Loads a user's per-permission overrides: a map of permission code to `1`
191/// (allow) or `-1` (deny).
192pub async fn load_user_permission_overrides(
193    db: &Db,
194    user_id: i64,
195) -> Result<HashMap<String, i64>, AuthError> {
196    let (sql, values) = build(
197        db.backend,
198        Query::select()
199            .column(BackendUsers::Permissions)
200            .from(BackendUsers::Table)
201            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
202            .to_owned(),
203    );
204    let row = bind_values(sqlx::query(&sql), values)
205        .fetch_optional(&db.pool)
206        .await?;
207    let overrides = match row {
208        Some(r) => {
209            let json = r.get_text("permissions")?;
210            serde_json::from_str(&json).unwrap_or_default()
211        }
212        None => HashMap::new(),
213    };
214    Ok(overrides)
215}
216
217/// Replaces a user's per-permission overrides (stored as a JSON object).
218pub async fn set_user_permissions(
219    db: &Db,
220    user_id: i64,
221    overrides: &HashMap<String, i64>,
222) -> Result<(), AuthError> {
223    let json = serde_json::to_string(overrides).unwrap_or_else(|_| "{}".to_string());
224    let (sql, values) = build(
225        db.backend,
226        Query::update()
227            .table(BackendUsers::Table)
228            .value(BackendUsers::Permissions, json)
229            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
230            .to_owned(),
231    );
232    bind_values(sqlx::query(&sql), values)
233        .execute(&db.pool)
234        .await?;
235    Ok(())
236}
237
238pub(crate) async fn insert_session(
239    db: &Db,
240    token_hash: &str,
241    user_id: i64,
242    expires_at: DateTime<Utc>,
243    ip_address: Option<&str>,
244    user_agent: Option<&str>,
245) -> Result<(), AuthError> {
246    let now = now_ts();
247    let (sql, values) = build(
248        db.backend,
249        Query::insert()
250            .into_table(BackendSessions::Table)
251            .columns([
252                BackendSessions::TokenHash,
253                BackendSessions::BackendUserId,
254                BackendSessions::CreatedAt,
255                BackendSessions::LastSeenAt,
256                BackendSessions::ExpiresAt,
257                BackendSessions::IpAddress,
258                BackendSessions::UserAgent,
259            ])
260            .values_panic([
261                token_hash.into(),
262                user_id.into(),
263                now.clone().into(),
264                now.into(),
265                ts(expires_at).into(),
266                ip_address.into(),
267                user_agent.into(),
268            ])
269            .to_owned(),
270    );
271    bind_values(sqlx::query(&sql), values)
272        .execute(&db.pool)
273        .await?;
274    Ok(())
275}
276
277/// A resolved non-expired session: its owning user and opaque data blob.
278pub struct ValidSession {
279    pub user_id: i64,
280    pub data: Option<String>,
281    /// When the session was issued. Fixes the absolute ceiling.
282    pub created_at: DateTime<Utc>,
283    /// Last request on this session. Fixes the idle deadline.
284    pub last_seen_at: DateTime<Utc>,
285}
286
287/// Returns a non-expired session (owner id plus its data blob), if any. The
288/// blob is read in the same query, so exposing session state costs no extra
289/// round-trip.
290pub(crate) async fn find_valid_session(
291    db: &Db,
292    token_hash: &str,
293    now: DateTime<Utc>,
294) -> Result<Option<ValidSession>, AuthError> {
295    let (sql, values) = build(
296        db.backend,
297        Query::select()
298            .columns([
299                BackendSessions::BackendUserId,
300                BackendSessions::Data,
301                BackendSessions::CreatedAt,
302                BackendSessions::LastSeenAt,
303            ])
304            .from(BackendSessions::Table)
305            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
306            .and_where(Expr::col(BackendSessions::ExpiresAt).gt(ts(now)))
307            .and_where(Expr::col(BackendSessions::RevokedReason).is_null())
308            .to_owned(),
309    );
310    let row = bind_values(sqlx::query(&sql), values)
311        .fetch_optional(&db.pool)
312        .await?;
313    match row {
314        Some(r) => Ok(Some(ValidSession {
315            user_id: r.try_get::<i64, _>("backend_user_id")?,
316            data: r.get_text_opt("data")?,
317            created_at: parse_ts(&r.get_text("created_at")?)?,
318            last_seen_at: parse_ts(&r.get_text("last_seen_at")?)?,
319        })),
320        None => Ok(None),
321    }
322}
323
324/// Overwrites a session's opaque data blob. Callers write only when the blob
325/// changed, so an unchanged request adds no write.
326pub(crate) async fn set_session_data(
327    db: &Db,
328    token_hash: &str,
329    data: &str,
330) -> Result<(), AuthError> {
331    let (sql, values) = build(
332        db.backend,
333        Query::update()
334            .table(BackendSessions::Table)
335            .value(BackendSessions::Data, data)
336            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
337            .to_owned(),
338    );
339    bind_values(sqlx::query(&sql), values)
340        .execute(&db.pool)
341        .await?;
342    Ok(())
343}
344
345/// Pushes a session's idle clock and its effective deadline out together. The
346/// two are written in one statement so a reader can never see a refreshed
347/// last-seen against a stale deadline.
348pub(crate) async fn renew_session(
349    db: &Db,
350    token_hash: &str,
351    now: DateTime<Utc>,
352    expires_at: DateTime<Utc>,
353) -> Result<(), AuthError> {
354    let (sql, values) = build(
355        db.backend,
356        Query::update()
357            .table(BackendSessions::Table)
358            .value(BackendSessions::LastSeenAt, ts(now))
359            .value(BackendSessions::ExpiresAt, ts(expires_at))
360            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
361            .to_owned(),
362    );
363    bind_values(sqlx::query(&sql), values)
364        .execute(&db.pool)
365        .await?;
366    Ok(())
367}
368
369/// A live "stay signed in" credential. `verifier_hash` is checked by the
370/// caller; the row is found by selector alone so a wrong verifier still costs
371/// one indexed lookup and nothing more.
372pub(crate) struct RememberToken {
373    pub user_id: i64,
374    pub verifier_hash: String,
375}
376
377pub(crate) async fn insert_remember_token(
378    db: &Db,
379    selector: &str,
380    verifier_hash: &str,
381    user_id: i64,
382    expires_at: DateTime<Utc>,
383) -> Result<(), AuthError> {
384    let (sql, values) = build(
385        db.backend,
386        Query::insert()
387            .into_table(BackendRememberTokens::Table)
388            .columns([
389                BackendRememberTokens::Selector,
390                BackendRememberTokens::VerifierHash,
391                BackendRememberTokens::BackendUserId,
392                BackendRememberTokens::CreatedAt,
393                BackendRememberTokens::ExpiresAt,
394            ])
395            .values_panic([
396                selector.into(),
397                verifier_hash.into(),
398                user_id.into(),
399                now_ts().into(),
400                ts(expires_at).into(),
401            ])
402            .to_owned(),
403    );
404    bind_values(sqlx::query(&sql), values)
405        .execute(&db.pool)
406        .await?;
407    Ok(())
408}
409
410pub(crate) async fn find_remember_token(
411    db: &Db,
412    selector: &str,
413    now: DateTime<Utc>,
414) -> Result<Option<RememberToken>, AuthError> {
415    let (sql, values) = build(
416        db.backend,
417        Query::select()
418            .columns([
419                BackendRememberTokens::BackendUserId,
420                BackendRememberTokens::VerifierHash,
421            ])
422            .from(BackendRememberTokens::Table)
423            .and_where(Expr::col(BackendRememberTokens::Selector).eq(selector))
424            .and_where(Expr::col(BackendRememberTokens::ExpiresAt).gt(ts(now)))
425            .and_where(Expr::col(BackendRememberTokens::RevokedReason).is_null())
426            .to_owned(),
427    );
428    let row = bind_values(sqlx::query(&sql), values)
429        .fetch_optional(&db.pool)
430        .await?;
431    match row {
432        Some(r) => Ok(Some(RememberToken {
433            user_id: r.try_get::<i64, _>("backend_user_id")?,
434            verifier_hash: r.get_text("verifier_hash")?,
435        })),
436        None => Ok(None),
437    }
438}
439
440/// Swaps in a new secret for a credential, keeping its selector. The selector
441/// is the series: holding it stable is what lets a stale secret be recognised
442/// as a copy rather than mistaken for an unknown credential.
443pub(crate) async fn rotate_remember_token(
444    db: &Db,
445    selector: &str,
446    verifier_hash: &str,
447    expires_at: DateTime<Utc>,
448) -> Result<(), AuthError> {
449    let (sql, values) = build(
450        db.backend,
451        Query::update()
452            .table(BackendRememberTokens::Table)
453            .value(BackendRememberTokens::VerifierHash, verifier_hash)
454            .value(BackendRememberTokens::ExpiresAt, ts(expires_at))
455            .and_where(Expr::col(BackendRememberTokens::Selector).eq(selector))
456            .to_owned(),
457    );
458    bind_values(sqlx::query(&sql), values)
459        .execute(&db.pool)
460        .await?;
461    Ok(())
462}
463
464pub(crate) async fn delete_remember_token(db: &Db, selector: &str) -> Result<(), AuthError> {
465    let (sql, values) = build(
466        db.backend,
467        Query::delete()
468            .from_table(BackendRememberTokens::Table)
469            .and_where(Expr::col(BackendRememberTokens::Selector).eq(selector))
470            .to_owned(),
471    );
472    bind_values(sqlx::query(&sql), values)
473        .execute(&db.pool)
474        .await?;
475    Ok(())
476}
477
478/// Ends a user's stay-signed-in credentials on purpose, recording why.
479pub(crate) async fn revoke_user_remember_tokens(
480    db: &Db,
481    user_id: i64,
482    reason: RevokeReason,
483) -> Result<(), AuthError> {
484    let (sql, values) = revoke_user_remember_tokens_query(db.backend, user_id, reason);
485    bind_values(sqlx::query(&sql), values)
486        .execute(&db.pool)
487        .await?;
488    Ok(())
489}
490
491fn revoke_user_remember_tokens_query(
492    backend: laterite_core::DbBackend,
493    user_id: i64,
494    reason: RevokeReason,
495) -> (String, sea_query::Values) {
496    build(
497        backend,
498        Query::update()
499            .table(BackendRememberTokens::Table)
500            .value(BackendRememberTokens::RevokedReason, reason.as_str())
501            .and_where(Expr::col(BackendRememberTokens::BackendUserId).eq(user_id))
502            .and_where(Expr::col(BackendRememberTokens::RevokedReason).is_null())
503            .to_owned(),
504    )
505}
506
507/// A revoked credential by selector: its verifier hash and why it ended.
508pub(crate) async fn revoked_remember_token(
509    db: &Db,
510    selector: &str,
511) -> Result<Option<(String, RevokeReason)>, AuthError> {
512    let (sql, values) = build(
513        db.backend,
514        Query::select()
515            .columns([
516                BackendRememberTokens::VerifierHash,
517                BackendRememberTokens::RevokedReason,
518            ])
519            .from(BackendRememberTokens::Table)
520            .and_where(Expr::col(BackendRememberTokens::Selector).eq(selector))
521            .and_where(Expr::col(BackendRememberTokens::RevokedReason).is_not_null())
522            .to_owned(),
523    );
524    let Some(row) = bind_values(sqlx::query(&sql), values)
525        .fetch_optional(&db.pool)
526        .await?
527    else {
528        return Ok(None);
529    };
530    let reason = row
531        .get_text_opt("revoked_reason")?
532        .as_deref()
533        .and_then(RevokeReason::parse);
534    Ok(reason.map(|r| (row.get_text("verifier_hash").unwrap_or_default(), r)))
535}
536
537/// Drops every remember credential a user holds. The answer to a verifier
538/// mismatch, which means a copy of a cookie is in circulation, and to a
539/// deliberate "sign out everywhere".
540pub(crate) async fn delete_user_remember_tokens(db: &Db, user_id: i64) -> Result<(), AuthError> {
541    let (sql, values) = build(
542        db.backend,
543        Query::delete()
544            .from_table(BackendRememberTokens::Table)
545            .and_where(Expr::col(BackendRememberTokens::BackendUserId).eq(user_id))
546            .to_owned(),
547    );
548    bind_values(sqlx::query(&sql), values)
549        .execute(&db.pool)
550        .await?;
551    Ok(())
552}
553
554/// One live session of a user, for the account's own sessions list.
555pub(crate) struct SessionSummary {
556    pub token_hash: String,
557    pub created_at: DateTime<Utc>,
558    pub last_seen_at: DateTime<Utc>,
559    pub expires_at: DateTime<Utc>,
560    pub ip_address: Option<String>,
561    pub user_agent: Option<String>,
562}
563
564pub(crate) async fn list_user_sessions(
565    db: &Db,
566    user_id: i64,
567    now: DateTime<Utc>,
568) -> Result<Vec<SessionSummary>, AuthError> {
569    let (sql, values) = build(
570        db.backend,
571        Query::select()
572            .columns([
573                BackendSessions::TokenHash,
574                BackendSessions::CreatedAt,
575                BackendSessions::LastSeenAt,
576                BackendSessions::ExpiresAt,
577                BackendSessions::IpAddress,
578                BackendSessions::UserAgent,
579            ])
580            .from(BackendSessions::Table)
581            .and_where(Expr::col(BackendSessions::BackendUserId).eq(user_id))
582            .and_where(Expr::col(BackendSessions::ExpiresAt).gt(ts(now)))
583            .and_where(Expr::col(BackendSessions::RevokedReason).is_null())
584            .order_by(BackendSessions::LastSeenAt, Order::Desc)
585            .to_owned(),
586    );
587    let rows = bind_values(sqlx::query(&sql), values)
588        .fetch_all(&db.pool)
589        .await?;
590    rows.iter()
591        .map(|r| {
592            Ok(SessionSummary {
593                token_hash: r.get_text("token_hash")?,
594                created_at: parse_ts(&r.get_text("created_at")?)?,
595                last_seen_at: parse_ts(&r.get_text("last_seen_at")?)?,
596                expires_at: parse_ts(&r.get_text("expires_at")?)?,
597                ip_address: r.get_text_opt("ip_address")?,
598                user_agent: r.get_text_opt("user_agent")?,
599            })
600        })
601        .collect()
602}
603
604/// Deletes one of a user's sessions. Scoped by owner, so a forged id can only
605/// ever end one of the caller's own sessions.
606/// Ends one of a user's sessions with a reason, so its holder is told.
607pub(crate) async fn revoke_user_session(
608    db: &Db,
609    user_id: i64,
610    token_hash: &str,
611    reason: RevokeReason,
612) -> Result<u64, AuthError> {
613    let (sql, values) = build(
614        db.backend,
615        Query::update()
616            .table(BackendSessions::Table)
617            .value(BackendSessions::RevokedReason, reason.as_str())
618            .and_where(Expr::col(BackendSessions::BackendUserId).eq(user_id))
619            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
620            .and_where(Expr::col(BackendSessions::RevokedReason).is_null())
621            .to_owned(),
622    );
623    let done = bind_values(sqlx::query(&sql), values)
624        .execute(&db.pool)
625        .await?;
626    Ok(done.rows_affected())
627}
628
629/// Why a session was ended deliberately. The set is closed: it becomes a code
630/// on a redirect and a sentence on the login screen, so nothing a caller wrote
631/// is ever shown back to anyone.
632#[derive(Debug, Clone, Copy, PartialEq, Eq)]
633#[non_exhaustive]
634pub enum RevokeReason {
635    /// The account's password changed.
636    PasswordChanged,
637    /// The account was deactivated.
638    Deactivated,
639    /// Someone chose to sign out every other device.
640    SignedOutElsewhere,
641}
642
643impl RevokeReason {
644    pub fn as_str(self) -> &'static str {
645        match self {
646            RevokeReason::PasswordChanged => "password_changed",
647            RevokeReason::Deactivated => "deactivated",
648            RevokeReason::SignedOutElsewhere => "signed_out_elsewhere",
649        }
650    }
651
652    pub fn parse(code: &str) -> Option<Self> {
653        match code {
654            "password_changed" => Some(RevokeReason::PasswordChanged),
655            "deactivated" => Some(RevokeReason::Deactivated),
656            "signed_out_elsewhere" => Some(RevokeReason::SignedOutElsewhere),
657            _ => None,
658        }
659    }
660}
661
662/// How a session that no longer works came to an end.
663#[derive(Debug, Clone, Copy, PartialEq, Eq)]
664#[non_exhaustive]
665pub enum SessionEnd {
666    /// It ran out: idle too long, or past its ceiling.
667    Expired,
668    /// It was ended on purpose.
669    Revoked(RevokeReason),
670}
671
672impl SessionEnd {
673    /// A stable code for the ending: `expired`, or the revoke reason's code.
674    pub fn code(self) -> &'static str {
675        match self {
676            SessionEnd::Expired => "expired",
677            SessionEnd::Revoked(reason) => reason.as_str(),
678        }
679    }
680}
681
682/// Ends a user's sessions on purpose, recording why, except `keep` when given.
683///
684/// The row stays until it is next presented, so whoever holds it can be told
685/// why they were signed out.
686pub(crate) async fn revoke_user_sessions(
687    db: &Db,
688    user_id: i64,
689    keep: Option<&str>,
690    reason: RevokeReason,
691) -> Result<u64, AuthError> {
692    let (sql, values) = revoke_user_sessions_query(db.backend, user_id, keep, reason);
693    let done = bind_values(sqlx::query(&sql), values)
694        .execute(&db.pool)
695        .await?;
696    Ok(done.rows_affected())
697}
698
699fn revoke_user_sessions_query(
700    backend: laterite_core::DbBackend,
701    user_id: i64,
702    keep: Option<&str>,
703    reason: RevokeReason,
704) -> (String, sea_query::Values) {
705    let mut update = Query::update();
706    update
707        .table(BackendSessions::Table)
708        .value(BackendSessions::RevokedReason, reason.as_str())
709        .and_where(Expr::col(BackendSessions::BackendUserId).eq(user_id))
710        .and_where(Expr::col(BackendSessions::RevokedReason).is_null());
711    if let Some(keep) = keep {
712        update.and_where(Expr::col(BackendSessions::TokenHash).ne(keep));
713    }
714    build(backend, update.to_owned())
715}
716
717/// Why a presented session no longer works, or `None` for one never issued.
718pub(crate) async fn session_end(
719    db: &Db,
720    token_hash: &str,
721    now: DateTime<Utc>,
722) -> Result<Option<SessionEnd>, AuthError> {
723    let (sql, values) = build(
724        db.backend,
725        Query::select()
726            .columns([BackendSessions::RevokedReason, BackendSessions::ExpiresAt])
727            .from(BackendSessions::Table)
728            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
729            .to_owned(),
730    );
731    let Some(row) = bind_values(sqlx::query(&sql), values)
732        .fetch_optional(&db.pool)
733        .await?
734    else {
735        return Ok(None);
736    };
737    if let Some(reason) = row
738        .get_text_opt("revoked_reason")?
739        .as_deref()
740        .and_then(RevokeReason::parse)
741    {
742        return Ok(Some(SessionEnd::Revoked(reason)));
743    }
744    let expires = parse_ts(&row.get_text("expires_at")?)?;
745    Ok((expires <= now).then_some(SessionEnd::Expired))
746}
747
748/// Sets a user's password hash, ends every other session and stay-signed-in
749/// credential with [`RevokeReason::PasswordChanged`], and writes the audit entry,
750/// all in one transaction: a change that half-landed would leave a new password
751/// beside sessions the old one opened. Returns how many user rows matched;
752/// nothing is written for an unknown id.
753pub(crate) async fn change_password(
754    db: &Db,
755    user_id: i64,
756    password_hash: &str,
757    keep: Option<&str>,
758    actor_user_id: Option<i64>,
759    actor_username: &str,
760) -> Result<u64, AuthError> {
761    let mut tx = db.pool.begin().await?;
762    let (sql, values) = build(
763        db.backend,
764        Query::update()
765            .table(BackendUsers::Table)
766            .value(BackendUsers::PasswordHash, password_hash)
767            .value(BackendUsers::PasswordChangedAt, now_ts())
768            .value(BackendUsers::UpdatedAt, now_ts())
769            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
770            .to_owned(),
771    );
772    let done = bind_values(sqlx::query(&sql), values)
773        .execute(&mut *tx)
774        .await?
775        .rows_affected();
776    if done == 0 {
777        return Ok(0);
778    }
779    let (sql, values) =
780        revoke_user_sessions_query(db.backend, user_id, keep, RevokeReason::PasswordChanged);
781    bind_values(sqlx::query(&sql), values)
782        .execute(&mut *tx)
783        .await?;
784    let (sql, values) =
785        revoke_user_remember_tokens_query(db.backend, user_id, RevokeReason::PasswordChanged);
786    bind_values(sqlx::query(&sql), values)
787        .execute(&mut *tx)
788        .await?;
789    let target = user_id.to_string();
790    let (sql, values) = insert_audit_log_query(
791        db.backend,
792        actor_user_id,
793        actor_username,
794        "backend.user.password_change",
795        Some("backend_user"),
796        Some(&target),
797        None,
798    );
799    bind_values(sqlx::query(&sql), values)
800        .execute(&mut *tx)
801        .await?;
802    tx.commit().await?;
803    Ok(done)
804}
805
806/// When a user's password was last set, `None` if it has not changed since
807/// the time was first recorded.
808pub(crate) async fn password_changed_at(
809    db: &Db,
810    user_id: i64,
811) -> Result<Option<DateTime<Utc>>, AuthError> {
812    let (sql, values) = build(
813        db.backend,
814        Query::select()
815            .column(BackendUsers::PasswordChangedAt)
816            .from(BackendUsers::Table)
817            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
818            .to_owned(),
819    );
820    let row = bind_values(sqlx::query(&sql), values)
821        .fetch_optional(&db.pool)
822        .await?;
823    match row {
824        Some(row) => row
825            .get_text_opt("password_changed_at")?
826            .map(|s| parse_ts(&s))
827            .transpose(),
828        None => Ok(None),
829    }
830}
831
832pub(crate) async fn delete_session(db: &Db, token_hash: &str) -> Result<(), AuthError> {
833    let (sql, values) = build(
834        db.backend,
835        Query::delete()
836            .from_table(BackendSessions::Table)
837            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
838            .to_owned(),
839    );
840    bind_values(sqlx::query(&sql), values)
841        .execute(&db.pool)
842        .await?;
843    Ok(())
844}
845
846pub async fn insert_access_log(
847    db: &Db,
848    user_id: Option<i64>,
849    username_attempted: &str,
850    event: AccessEvent,
851    ip_address: Option<&str>,
852    user_agent: Option<&str>,
853) -> Result<(), AuthError> {
854    let (sql, values) = build(
855        db.backend,
856        Query::insert()
857            .into_table(BackendAccessLog::Table)
858            .columns([
859                BackendAccessLog::BackendUserId,
860                BackendAccessLog::UsernameAttempted,
861                BackendAccessLog::Event,
862                BackendAccessLog::IpAddress,
863                BackendAccessLog::UserAgent,
864                BackendAccessLog::CreatedAt,
865            ])
866            .values_panic([
867                user_id.into(),
868                normalize_key(username_attempted).into(),
869                event.as_str().into(),
870                ip_address.map(str::to_string).into(),
871                user_agent.map(str::to_string).into(),
872                now_ts().into(),
873            ])
874            .to_owned(),
875    );
876    bind_values(sqlx::query(&sql), values)
877        .execute(&db.pool)
878        .await?;
879    Ok(())
880}
881
882/// One row of the audit log, newest-first from [`recent_audit`].
883#[derive(Debug, Clone)]
884pub struct AuditRecord {
885    pub actor_user_id: Option<i64>,
886    pub actor_username: String,
887    pub action: String,
888    pub target_type: Option<String>,
889    pub target_id: Option<String>,
890    pub detail: Option<String>,
891    pub created_at: DateTime<Utc>,
892}
893
894/// Appends one entry to the append-only audit log.
895#[allow(clippy::too_many_arguments)]
896pub async fn insert_audit_log(
897    db: &Db,
898    actor_user_id: Option<i64>,
899    actor_username: &str,
900    action: &str,
901    target_type: Option<&str>,
902    target_id: Option<&str>,
903    detail: Option<&str>,
904) -> Result<(), AuthError> {
905    let (sql, values) = insert_audit_log_query(
906        db.backend,
907        actor_user_id,
908        actor_username,
909        action,
910        target_type,
911        target_id,
912        detail,
913    );
914    bind_values(sqlx::query(&sql), values)
915        .execute(&db.pool)
916        .await?;
917    Ok(())
918}
919
920#[allow(clippy::too_many_arguments)]
921fn insert_audit_log_query(
922    backend: laterite_core::DbBackend,
923    actor_user_id: Option<i64>,
924    actor_username: &str,
925    action: &str,
926    target_type: Option<&str>,
927    target_id: Option<&str>,
928    detail: Option<&str>,
929) -> (String, sea_query::Values) {
930    build(
931        backend,
932        Query::insert()
933            .into_table(BackendAuditLog::Table)
934            .columns([
935                BackendAuditLog::ActorUserId,
936                BackendAuditLog::ActorUsername,
937                BackendAuditLog::Action,
938                BackendAuditLog::TargetType,
939                BackendAuditLog::TargetId,
940                BackendAuditLog::Detail,
941                BackendAuditLog::CreatedAt,
942            ])
943            .values_panic([
944                actor_user_id.into(),
945                actor_username.to_string().into(),
946                action.to_string().into(),
947                target_type.map(str::to_string).into(),
948                target_id.map(str::to_string).into(),
949                detail.map(str::to_string).into(),
950                now_ts().into(),
951            ])
952            .to_owned(),
953    )
954}
955
956/// The most recent audit entries, newest first (id descending, matching insert
957/// order). Feeds the admin audit view.
958pub async fn recent_audit(db: &Db, limit: u64) -> Result<Vec<AuditRecord>, AuthError> {
959    let (sql, values) = build(
960        db.backend,
961        Query::select()
962            .columns([
963                BackendAuditLog::ActorUserId,
964                BackendAuditLog::ActorUsername,
965                BackendAuditLog::Action,
966                BackendAuditLog::TargetType,
967                BackendAuditLog::TargetId,
968                BackendAuditLog::Detail,
969                BackendAuditLog::CreatedAt,
970            ])
971            .from(BackendAuditLog::Table)
972            .order_by(BackendAuditLog::Id, Order::Desc)
973            .limit(limit)
974            .to_owned(),
975    );
976    let rows = bind_values(sqlx::query(&sql), values)
977        .fetch_all(&db.pool)
978        .await?;
979    rows.iter().map(audit_from_row).collect()
980}
981
982fn audit_from_row(row: &AnyRow) -> Result<AuditRecord, AuthError> {
983    Ok(AuditRecord {
984        actor_user_id: row.get_int_opt("actor_user_id")?,
985        actor_username: row.get_text("actor_username")?,
986        action: row.get_text("action")?,
987        target_type: row.get_text_opt("target_type")?,
988        target_id: row.get_text_opt("target_id")?,
989        detail: row.get_text_opt("detail")?,
990        created_at: parse_ts(&row.get_text("created_at")?)?,
991    })
992}
993
994/// Counts recent failed login attempts for a username, for throttling.
995pub async fn count_recent_failures(
996    db: &Db,
997    username: &str,
998    since: DateTime<Utc>,
999) -> Result<i64, AuthError> {
1000    let (sql, values) = build(
1001        db.backend,
1002        Query::select()
1003            .expr(Expr::col(BackendAccessLog::Id).count())
1004            .from(BackendAccessLog::Table)
1005            .and_where(Expr::col(BackendAccessLog::UsernameAttempted).eq(normalize_key(username)))
1006            .and_where(Expr::col(BackendAccessLog::Event).eq(AccessEvent::LoginFailure.as_str()))
1007            .and_where(Expr::col(BackendAccessLog::CreatedAt).gte(ts(since)))
1008            .to_owned(),
1009    );
1010    let count: i64 = bind_values_as(sqlx::query_as::<_, (i64,)>(&sql), values)
1011        .fetch_one(&db.pool)
1012        .await?
1013        .0;
1014    Ok(count)
1015}
1016
1017/// Creates a backend user, returning the id the database assigned. Timestamps
1018/// are generated here (no database-side defaults) so the insert is portable.
1019#[allow(clippy::too_many_arguments)]
1020pub async fn create_user(
1021    db: &Db,
1022    username: &str,
1023    email: &str,
1024    first_name: &str,
1025    last_name: Option<&str>,
1026    password_hash: &str,
1027    is_superuser: bool,
1028) -> Result<i64, AuthError> {
1029    let now = now_ts();
1030    let stmt = Query::insert()
1031        .into_table(BackendUsers::Table)
1032        .columns([
1033            BackendUsers::Username,
1034            BackendUsers::Email,
1035            BackendUsers::FirstName,
1036            BackendUsers::LastName,
1037            BackendUsers::PasswordHash,
1038            BackendUsers::IsSuperuser,
1039            BackendUsers::PasswordChangedAt,
1040            BackendUsers::CreatedAt,
1041            BackendUsers::UpdatedAt,
1042        ])
1043        .values_panic([
1044            normalize_key(username).into(),
1045            normalize_key(email).into(),
1046            first_name.into(),
1047            last_name.map(str::to_string).into(),
1048            password_hash.into(),
1049            is_superuser.into(),
1050            now.clone().into(),
1051            now.clone().into(),
1052            now.into(),
1053        ])
1054        .to_owned();
1055    Ok(insert_returning_id(db, stmt, BackendUsers::Id).await?)
1056}
1057
1058/// Whether any backend user exists.
1059pub async fn any_user_exists(db: &Db) -> Result<bool, AuthError> {
1060    let (sql, values) = build(
1061        db.backend,
1062        Query::select()
1063            .expr(Expr::col(BackendUsers::Id).count())
1064            .from(BackendUsers::Table)
1065            .to_owned(),
1066    );
1067    let count: i64 = bind_values_as(sqlx::query_as::<_, (i64,)>(&sql), values)
1068        .fetch_one(&db.pool)
1069        .await?
1070        .0;
1071    Ok(count > 0)
1072}
1073
1074/// Sets an operator's own display timezone, or clears it with `None`.
1075pub async fn set_user_timezone(
1076    db: &Db,
1077    user_id: i64,
1078    timezone: Option<&str>,
1079) -> Result<(), AuthError> {
1080    let (sql, values) = build(
1081        db.backend,
1082        Query::update()
1083            .table(BackendUsers::Table)
1084            .value(BackendUsers::Timezone, timezone.map(str::to_string))
1085            .value(BackendUsers::UpdatedAt, now_ts())
1086            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
1087            .to_owned(),
1088    );
1089    bind_values(sqlx::query(&sql), values)
1090        .execute(&db.pool)
1091        .await?;
1092    Ok(())
1093}
1094
1095/// Sets an operator's own UI locale, or clears it with `None`.
1096pub async fn set_user_locale(db: &Db, user_id: i64, locale: Option<&str>) -> Result<(), AuthError> {
1097    let (sql, values) = build(
1098        db.backend,
1099        Query::update()
1100            .table(BackendUsers::Table)
1101            .value(BackendUsers::Locale, locale.map(str::to_string))
1102            .value(BackendUsers::UpdatedAt, now_ts())
1103            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
1104            .to_owned(),
1105    );
1106    bind_values(sqlx::query(&sql), values)
1107        .execute(&db.pool)
1108        .await?;
1109    Ok(())
1110}
1111
1112pub async fn create_role(
1113    db: &Db,
1114    code: &str,
1115    name: &str,
1116    permissions: &[String],
1117) -> Result<i64, AuthError> {
1118    let perms = serde_json::to_string(permissions).unwrap_or_else(|_| "[]".to_string());
1119    let stmt = Query::insert()
1120        .into_table(BackendRoles::Table)
1121        .columns([
1122            BackendRoles::Code,
1123            BackendRoles::Name,
1124            BackendRoles::Permissions,
1125            BackendRoles::CreatedAt,
1126        ])
1127        .values_panic([code.into(), name.into(), perms.into(), now_ts().into()])
1128        .to_owned();
1129    Ok(insert_returning_id(db, stmt, BackendRoles::Id).await?)
1130}
1131
1132/// A role the framework owns: its permissions come from the registry, not from
1133/// the database, so they cannot drift as modules add permissions.
1134pub struct SystemRole<'a> {
1135    pub code: &'a str,
1136    pub name: &'a str,
1137    pub description: &'a str,
1138    pub permissions: Vec<String>,
1139}
1140
1141/// Writes the system roles, creating what is missing and rewriting what exists.
1142///
1143/// Runs at every boot: a module that registers a new permission has it in the
1144/// right role the moment it loads, and an operator cannot leave a system role
1145/// holding a permission the registry no longer declares. Only the framework's
1146/// own rows are touched; a role an operator made is never rewritten.
1147pub async fn sync_system_roles(db: &Db, roles: &[SystemRole<'_>]) -> Result<(), AuthError> {
1148    for role in roles {
1149        let perms = serde_json::to_string(&role.permissions).unwrap_or_else(|_| "[]".to_string());
1150        // Insert-then-update rather than select-then-insert: two instances booting
1151        // together both reach the insert, and the loser is ignored instead of
1152        // failing the unique key and taking its boot down with it.
1153        let (sql, values) = build(
1154            db.backend,
1155            Query::insert()
1156                .into_table(BackendRoles::Table)
1157                .columns([
1158                    BackendRoles::Code,
1159                    BackendRoles::Name,
1160                    BackendRoles::Description,
1161                    BackendRoles::Permissions,
1162                    BackendRoles::IsSystem,
1163                    BackendRoles::CreatedAt,
1164                ])
1165                .values_panic([
1166                    role.code.into(),
1167                    role.name.into(),
1168                    role.description.into(),
1169                    perms.clone().into(),
1170                    true.into(),
1171                    now_ts().into(),
1172                ])
1173                .on_conflict(on_conflict_ignore([BackendRoles::Code]))
1174                .to_owned(),
1175        );
1176        bind_values(sqlx::query(&sql), values)
1177            .execute(&db.pool)
1178            .await?;
1179        let (sql, values) = build(
1180            db.backend,
1181            Query::update()
1182                .table(BackendRoles::Table)
1183                .values([
1184                    (BackendRoles::Name, role.name.into()),
1185                    (BackendRoles::Description, role.description.into()),
1186                    (BackendRoles::Permissions, perms.into()),
1187                    (BackendRoles::IsSystem, true.into()),
1188                ])
1189                .and_where(Expr::col(BackendRoles::Code).eq(role.code))
1190                .to_owned(),
1191        );
1192        bind_values(sqlx::query(&sql), values)
1193            .execute(&db.pool)
1194            .await?;
1195    }
1196    Ok(())
1197}
1198
1199/// The id of the role with this code, if there is one.
1200pub async fn role_id_by_code(db: &Db, code: &str) -> Result<Option<i64>, AuthError> {
1201    let (sql, values) = build(
1202        db.backend,
1203        Query::select()
1204            .column(BackendRoles::Id)
1205            .from(BackendRoles::Table)
1206            .and_where(Expr::col(BackendRoles::Code).eq(code))
1207            .to_owned(),
1208    );
1209    let row = bind_values(sqlx::query(&sql), values)
1210        .fetch_optional(&db.pool)
1211        .await?;
1212    Ok(match row {
1213        Some(row) => Some(row.try_get::<i64, _>("id")?),
1214        None => None,
1215    })
1216}
1217
1218/// The permission codes this role grants.
1219pub async fn role_permissions(db: &Db, id: i64) -> Result<Vec<String>, AuthError> {
1220    let (sql, values) = build(
1221        db.backend,
1222        Query::select()
1223            .column(BackendRoles::Permissions)
1224            .from(BackendRoles::Table)
1225            .and_where(Expr::col(BackendRoles::Id).eq(id))
1226            .to_owned(),
1227    );
1228    let row = bind_values(sqlx::query(&sql), values)
1229        .fetch_optional(&db.pool)
1230        .await?;
1231    Ok(match row {
1232        Some(row) => serde_json::from_str(&row.get_text("permissions")?).unwrap_or_default(),
1233        None => Vec::new(),
1234    })
1235}
1236
1237/// Whether this role is one the framework owns.
1238pub async fn role_is_system(db: &Db, id: i64) -> Result<bool, AuthError> {
1239    let (sql, values) = is_system_query(db.backend, id);
1240    let row = bind_values(sqlx::query(&sql), values)
1241        .fetch_optional(&db.pool)
1242        .await?;
1243    read_is_system(row)
1244}
1245
1246/// The same question asked on an open transaction, for a listener that must see
1247/// the state the write is running against.
1248pub async fn role_is_system_on(
1249    conn: &mut sqlx::AnyConnection,
1250    backend: laterite_core::DbBackend,
1251    id: i64,
1252) -> Result<bool, AuthError> {
1253    let (sql, values) = is_system_query(backend, id);
1254    let row = bind_values(sqlx::query(&sql), values)
1255        .fetch_optional(&mut *conn)
1256        .await?;
1257    read_is_system(row)
1258}
1259
1260fn is_system_query(backend: laterite_core::DbBackend, id: i64) -> (String, sea_query::Values) {
1261    build(
1262        backend,
1263        Query::select()
1264            .column(BackendRoles::IsSystem)
1265            .from(BackendRoles::Table)
1266            .and_where(Expr::col(BackendRoles::Id).eq(id))
1267            .to_owned(),
1268    )
1269}
1270
1271/// A missing row is not a system role: it is nothing at all.
1272fn read_is_system(row: Option<AnyRow>) -> Result<bool, AuthError> {
1273    Ok(match row {
1274        Some(row) => row.get_bool("is_system")?,
1275        None => false,
1276    })
1277}
1278
1279/// Rewrites renamed permission codes wherever an operator's choices hold them.
1280///
1281/// A system role is rebuilt from the registry at every boot, so it needs no
1282/// repair. A role someone made, and a per-user override, hold whatever code was
1283/// current when they were saved: without this they keep granting a string
1284/// nothing checks any more. Returns how many rows changed, so a boot can say so
1285/// once and stay quiet afterwards.
1286///
1287/// Idempotent: a second run finds no old code and writes nothing.
1288pub async fn rename_permissions(db: &Db, renames: &[(String, String)]) -> Result<usize, AuthError> {
1289    if renames.is_empty() {
1290        return Ok(0);
1291    }
1292    let mut changed = 0;
1293
1294    // Roles hold a JSON array of codes.
1295    let (sql, values) = build(
1296        db.backend,
1297        Query::select()
1298            .columns([BackendRoles::Id, BackendRoles::Permissions])
1299            .from(BackendRoles::Table)
1300            .to_owned(),
1301    );
1302    for row in bind_values(sqlx::query(&sql), values)
1303        .fetch_all(&db.pool)
1304        .await?
1305    {
1306        let id = row.try_get::<i64, _>("id")?;
1307        let held: Vec<String> =
1308            serde_json::from_str(&row.get_text("permissions")?).unwrap_or_default();
1309        let mut next: Vec<String> = held
1310            .iter()
1311            .map(|code| apply_rename(code, renames))
1312            .collect();
1313        // A rename can land on a code the role already held.
1314        next.dedup_by(|a, b| a == b);
1315        let mut seen = std::collections::HashSet::new();
1316        next.retain(|code| seen.insert(code.clone()));
1317        if next != held {
1318            let json = serde_json::to_string(&next).unwrap_or_else(|_| "[]".to_string());
1319            let (sql, values) = build(
1320                db.backend,
1321                Query::update()
1322                    .table(BackendRoles::Table)
1323                    .value(BackendRoles::Permissions, json)
1324                    .and_where(Expr::col(BackendRoles::Id).eq(id))
1325                    .to_owned(),
1326            );
1327            bind_values(sqlx::query(&sql), values)
1328                .execute(&db.pool)
1329                .await?;
1330            changed += 1;
1331        }
1332    }
1333
1334    // A user's overrides are a JSON object keyed by code.
1335    let (sql, values) = build(
1336        db.backend,
1337        Query::select()
1338            .columns([BackendUsers::Id, BackendUsers::Permissions])
1339            .from(BackendUsers::Table)
1340            .to_owned(),
1341    );
1342    for row in bind_values(sqlx::query(&sql), values)
1343        .fetch_all(&db.pool)
1344        .await?
1345    {
1346        let id = row.try_get::<i64, _>("id")?;
1347        let held: HashMap<String, i64> =
1348            serde_json::from_str(&row.get_text("permissions")?).unwrap_or_default();
1349        let next: HashMap<String, i64> = held
1350            .iter()
1351            .map(|(code, state)| (apply_rename(code, renames), *state))
1352            .collect();
1353        if next != held {
1354            let json = serde_json::to_string(&next).unwrap_or_else(|_| "{}".to_string());
1355            let (sql, values) = build(
1356                db.backend,
1357                Query::update()
1358                    .table(BackendUsers::Table)
1359                    .value(BackendUsers::Permissions, json)
1360                    .and_where(Expr::col(BackendUsers::Id).eq(id))
1361                    .to_owned(),
1362            );
1363            bind_values(sqlx::query(&sql), values)
1364                .execute(&db.pool)
1365                .await?;
1366            changed += 1;
1367        }
1368    }
1369    Ok(changed)
1370}
1371
1372/// The code this one became, or itself.
1373fn apply_rename(code: &str, renames: &[(String, String)]) -> String {
1374    renames
1375        .iter()
1376        .find(|(old, _)| old == code)
1377        .map(|(_, new)| new.clone())
1378        .unwrap_or_else(|| code.to_string())
1379}
1380
1381/// A role as the assignment screen lists it.
1382pub struct RoleSummary {
1383    pub id: i64,
1384    pub code: String,
1385    pub name: String,
1386    pub is_system: bool,
1387    /// What holding it grants, so a screen can refuse to offer a role that
1388    /// would grant more than the operator assigning it holds.
1389    pub permissions: Vec<String>,
1390}
1391
1392/// Every role, by name.
1393pub async fn list_roles(db: &Db) -> Result<Vec<RoleSummary>, AuthError> {
1394    let (sql, values) = build(
1395        db.backend,
1396        Query::select()
1397            .columns([
1398                BackendRoles::Id,
1399                BackendRoles::Code,
1400                BackendRoles::Name,
1401                BackendRoles::IsSystem,
1402                BackendRoles::Permissions,
1403            ])
1404            .from(BackendRoles::Table)
1405            .order_by(BackendRoles::Name, Order::Asc)
1406            .to_owned(),
1407    );
1408    let rows = bind_values(sqlx::query(&sql), values)
1409        .fetch_all(&db.pool)
1410        .await?;
1411    rows.into_iter()
1412        .map(|row| {
1413            Ok(RoleSummary {
1414                id: row.try_get::<i64, _>("id")?,
1415                code: row.get_text("code")?,
1416                name: row.get_text("name")?,
1417                is_system: row.get_bool("is_system")?,
1418                permissions: serde_json::from_str(&row.get_text("permissions")?)
1419                    .unwrap_or_default(),
1420            })
1421        })
1422        .collect()
1423}
1424
1425/// The roles this operator holds.
1426pub async fn user_role_ids(db: &Db, user_id: i64) -> Result<Vec<i64>, AuthError> {
1427    let (sql, values) = build(
1428        db.backend,
1429        Query::select()
1430            .column(BackendUserRoles::BackendRoleId)
1431            .from(BackendUserRoles::Table)
1432            .and_where(Expr::col(BackendUserRoles::BackendUserId).eq(user_id))
1433            .to_owned(),
1434    );
1435    let rows = bind_values(sqlx::query(&sql), values)
1436        .fetch_all(&db.pool)
1437        .await?;
1438    rows.into_iter()
1439        .map(|row| Ok(row.try_get::<i64, _>("backend_role_id")?))
1440        .collect()
1441}
1442
1443/// Replaces this operator's roles with exactly `role_ids`.
1444///
1445/// One transaction, so an operator is never briefly left holding none of them.
1446pub async fn set_user_roles(db: &Db, user_id: i64, role_ids: &[i64]) -> Result<(), AuthError> {
1447    let mut tx = db.pool.begin().await?;
1448    let (sql, values) = build(
1449        db.backend,
1450        Query::delete()
1451            .from_table(BackendUserRoles::Table)
1452            .and_where(Expr::col(BackendUserRoles::BackendUserId).eq(user_id))
1453            .to_owned(),
1454    );
1455    bind_values(sqlx::query(&sql), values)
1456        .execute(&mut *tx)
1457        .await?;
1458    for role_id in role_ids {
1459        let (sql, values) = build(
1460            db.backend,
1461            Query::insert()
1462                .into_table(BackendUserRoles::Table)
1463                .columns([
1464                    BackendUserRoles::BackendUserId,
1465                    BackendUserRoles::BackendRoleId,
1466                ])
1467                .values_panic([user_id.into(), (*role_id).into()])
1468                .on_conflict(on_conflict_ignore([
1469                    BackendUserRoles::BackendUserId,
1470                    BackendUserRoles::BackendRoleId,
1471                ]))
1472                .to_owned(),
1473        );
1474        bind_values(sqlx::query(&sql), values)
1475            .execute(&mut *tx)
1476            .await?;
1477    }
1478    tx.commit().await?;
1479    Ok(())
1480}
1481
1482pub async fn assign_role(db: &Db, user_id: i64, role_id: i64) -> Result<(), AuthError> {
1483    let (sql, values) = build(
1484        db.backend,
1485        Query::insert()
1486            .into_table(BackendUserRoles::Table)
1487            .columns([
1488                BackendUserRoles::BackendUserId,
1489                BackendUserRoles::BackendRoleId,
1490            ])
1491            .values_panic([user_id.into(), role_id.into()])
1492            .on_conflict(on_conflict_ignore([
1493                BackendUserRoles::BackendUserId,
1494                BackendUserRoles::BackendRoleId,
1495            ]))
1496            .to_owned(),
1497    );
1498    bind_values(sqlx::query(&sql), values)
1499        .execute(&db.pool)
1500        .await?;
1501    Ok(())
1502}
1503
1504/// Sets whether a user may sign in. Returns rows affected, so a caller can tell
1505/// a no-op from a missing user.
1506/// Sets whether an account may sign in. Deactivating also ends every session
1507/// and stay-signed-in credential with [`RevokeReason::Deactivated`], in the same
1508/// transaction, so the account is never blocked from signing in while still
1509/// signed in somewhere.
1510pub(crate) async fn set_user_active(db: &Db, user_id: i64, active: bool) -> Result<u64, AuthError> {
1511    let mut tx = db.pool.begin().await?;
1512    let (sql, values) = build(
1513        db.backend,
1514        Query::update()
1515            .table(BackendUsers::Table)
1516            .value(BackendUsers::IsActive, active)
1517            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
1518            .to_owned(),
1519    );
1520    let done = bind_values(sqlx::query(&sql), values)
1521        .execute(&mut *tx)
1522        .await?
1523        .rows_affected();
1524    if done > 0 && !active {
1525        let (sql, values) =
1526            revoke_user_sessions_query(db.backend, user_id, None, RevokeReason::Deactivated);
1527        bind_values(sqlx::query(&sql), values)
1528            .execute(&mut *tx)
1529            .await?;
1530        let (sql, values) =
1531            revoke_user_remember_tokens_query(db.backend, user_id, RevokeReason::Deactivated);
1532        bind_values(sqlx::query(&sql), values)
1533            .execute(&mut *tx)
1534            .await?;
1535    }
1536    tx.commit().await?;
1537    Ok(done)
1538}
1539
1540/// How many active superusers there are besides `excluding`.
1541///
1542/// Deactivating the last one would leave a panel nobody can fully administer,
1543/// recoverable only from the command line, so the caller refuses it.
1544pub(crate) async fn other_active_superusers(db: &Db, excluding: i64) -> Result<i64, AuthError> {
1545    let (sql, values) = build(
1546        db.backend,
1547        Query::select()
1548            .expr(Expr::col(BackendUsers::Id).count())
1549            .from(BackendUsers::Table)
1550            .and_where(Expr::col(BackendUsers::IsSuperuser).eq(true))
1551            .and_where(Expr::col(BackendUsers::IsActive).eq(true))
1552            .and_where(Expr::col(BackendUsers::Id).ne(excluding))
1553            .to_owned(),
1554    );
1555    let row = bind_values(sqlx::query(&sql), values)
1556        .fetch_one(&db.pool)
1557        .await?;
1558    Ok(row.try_get::<i64, _>(0).unwrap_or(0))
1559}
1560
1561/// Lists backend users for admin tooling, ordered by creation time.
1562pub async fn list_backend_users(db: &Db) -> Result<Vec<BackendUserSummary>, AuthError> {
1563    let (sql, values) = build(
1564        db.backend,
1565        Query::select()
1566            .columns([
1567                BackendUsers::Id,
1568                BackendUsers::Username,
1569                BackendUsers::Email,
1570                BackendUsers::FirstName,
1571                BackendUsers::LastName,
1572                BackendUsers::IsSuperuser,
1573                BackendUsers::IsActive,
1574                BackendUsers::CreatedAt,
1575            ])
1576            .from(BackendUsers::Table)
1577            .order_by(BackendUsers::CreatedAt, Order::Asc)
1578            .to_owned(),
1579    );
1580    let rows = bind_values(sqlx::query(&sql), values)
1581        .fetch_all(&db.pool)
1582        .await?;
1583    rows.iter().map(summary_from_row).collect()
1584}
1585
1586/// Sets a new password hash for a user by username, returning rows affected.
1587pub async fn update_password_by_username(
1588    db: &Db,
1589    username: &str,
1590    password_hash: &str,
1591) -> Result<u64, AuthError> {
1592    let (sql, values) = build(
1593        db.backend,
1594        Query::update()
1595            .table(BackendUsers::Table)
1596            .value(BackendUsers::PasswordHash, password_hash)
1597            .value(BackendUsers::PasswordChangedAt, now_ts())
1598            .value(BackendUsers::UpdatedAt, now_ts())
1599            .and_where(Expr::col(BackendUsers::Username).eq(normalize_key(username)))
1600            .to_owned(),
1601    );
1602    let result = bind_values(sqlx::query(&sql), values)
1603        .execute(&db.pool)
1604        .await?;
1605    Ok(result.rows_affected())
1606}
1607
1608/// Clears a user's failed-login records, releasing a lockout.
1609pub async fn clear_failed_attempts(db: &Db, username: &str) -> Result<u64, AuthError> {
1610    let (sql, values) = build(
1611        db.backend,
1612        Query::delete()
1613            .from_table(BackendAccessLog::Table)
1614            .and_where(Expr::col(BackendAccessLog::UsernameAttempted).eq(normalize_key(username)))
1615            .and_where(Expr::col(BackendAccessLog::Event).eq(AccessEvent::LoginFailure.as_str()))
1616            .to_owned(),
1617    );
1618    let result = bind_values(sqlx::query(&sql), values)
1619        .execute(&db.pool)
1620        .await?;
1621    Ok(result.rows_affected())
1622}
1623
1624/// One operator's stored choice for `key`, or `None` if they have not made one.
1625pub async fn user_preference(
1626    db: &Db,
1627    user_id: i64,
1628    key: &str,
1629) -> Result<Option<String>, AuthError> {
1630    let (sql, values) = build(
1631        db.backend,
1632        Query::select()
1633            .column(BackendUserPreferences::Value)
1634            .from(BackendUserPreferences::Table)
1635            .and_where(Expr::col(BackendUserPreferences::UserId).eq(user_id))
1636            .and_where(Expr::col(BackendUserPreferences::PreferenceKey).eq(key))
1637            .to_owned(),
1638    );
1639    let row = bind_values(sqlx::query(&sql), values)
1640        .fetch_optional(&db.pool)
1641        .await?;
1642    Ok(row.and_then(|r| r.get_text_opt("value").ok().flatten()))
1643}
1644
1645/// Stores one operator's choice for `key`, replacing any previous one.
1646///
1647/// Written as delete-then-insert rather than an upsert: the three backends spell
1648/// `ON CONFLICT` differently, and a preference write is rare and already inside
1649/// its own request, so the portable pair costs nothing worth optimising.
1650pub async fn set_user_preference(
1651    db: &Db,
1652    user_id: i64,
1653    key: &str,
1654    value: &str,
1655) -> Result<(), AuthError> {
1656    let mut tx = db.pool.begin().await?;
1657    let (sql, values) = build(
1658        db.backend,
1659        Query::delete()
1660            .from_table(BackendUserPreferences::Table)
1661            .and_where(Expr::col(BackendUserPreferences::UserId).eq(user_id))
1662            .and_where(Expr::col(BackendUserPreferences::PreferenceKey).eq(key))
1663            .to_owned(),
1664    );
1665    bind_values(sqlx::query(&sql), values)
1666        .execute(&mut *tx)
1667        .await?;
1668    let (sql, values) = build(
1669        db.backend,
1670        Query::insert()
1671            .into_table(BackendUserPreferences::Table)
1672            .columns([
1673                BackendUserPreferences::UserId,
1674                BackendUserPreferences::PreferenceKey,
1675                BackendUserPreferences::Value,
1676            ])
1677            .values_panic([
1678                user_id.into(),
1679                key.to_string().into(),
1680                value.to_string().into(),
1681            ])
1682            .to_owned(),
1683    );
1684    bind_values(sqlx::query(&sql), values)
1685        .execute(&mut *tx)
1686        .await?;
1687    tx.commit().await?;
1688    Ok(())
1689}
1690
1691/// Removes one operator's choice for `key`, so they fall back to the default.
1692pub async fn clear_user_preference(db: &Db, user_id: i64, key: &str) -> Result<(), AuthError> {
1693    let (sql, values) = build(
1694        db.backend,
1695        Query::delete()
1696            .from_table(BackendUserPreferences::Table)
1697            .and_where(Expr::col(BackendUserPreferences::UserId).eq(user_id))
1698            .and_where(Expr::col(BackendUserPreferences::PreferenceKey).eq(key))
1699            .to_owned(),
1700    );
1701    bind_values(sqlx::query(&sql), values)
1702        .execute(&db.pool)
1703        .await?;
1704    Ok(())
1705}
1706
1707#[cfg(test)]
1708mod tests {
1709    use super::*;
1710    use laterite_core::testing::connect_test;
1711
1712    async fn operator(db: &Db) -> i64 {
1713        let svc = crate::AuthService::new(db.clone(), crate::AuthConfig::default());
1714        svc.create_superuser(crate::NewOperator {
1715            username: "root",
1716            email: "root@acme.test",
1717            first_name: "Root",
1718            last_name: None,
1719            password: "rootpw12345",
1720            timezone: None,
1721        })
1722        .await
1723        .unwrap()
1724    }
1725
1726    #[tokio::test]
1727    async fn a_preference_round_trips_and_replaces() {
1728        let (db, _guard) = connect_test(&[crate::migrations()]).await;
1729        let id = operator(&db).await;
1730
1731        // Unset reads as absent, not as an empty value.
1732        assert_eq!(
1733            user_preference(&db, id, "list.columns./roles")
1734                .await
1735                .unwrap(),
1736            None
1737        );
1738
1739        set_user_preference(&db, id, "list.columns./roles", "code,name")
1740            .await
1741            .unwrap();
1742        assert_eq!(
1743            user_preference(&db, id, "list.columns./roles")
1744                .await
1745                .unwrap(),
1746            Some("code,name".to_string())
1747        );
1748
1749        // A second write replaces rather than accumulating, which the unique
1750        // index would otherwise refuse.
1751        set_user_preference(&db, id, "list.columns./roles", "code")
1752            .await
1753            .unwrap();
1754        assert_eq!(
1755            user_preference(&db, id, "list.columns./roles")
1756                .await
1757                .unwrap(),
1758            Some("code".to_string())
1759        );
1760
1761        clear_user_preference(&db, id, "list.columns./roles")
1762            .await
1763            .unwrap();
1764        assert_eq!(
1765            user_preference(&db, id, "list.columns./roles")
1766                .await
1767                .unwrap(),
1768            None
1769        );
1770    }
1771
1772    /// Preferences are per operator and per key: one does not leak into another.
1773    #[tokio::test]
1774    async fn preferences_are_scoped() {
1775        let (db, _guard) = connect_test(&[crate::migrations()]).await;
1776        let root = operator(&db).await;
1777        let hash = crate::password::hash_password("otherpw12345").unwrap();
1778        let other = create_user(&db, "other", "other@acme.test", "Other", None, &hash, false)
1779            .await
1780            .unwrap();
1781
1782        set_user_preference(&db, root, "list.columns./roles", "code")
1783            .await
1784            .unwrap();
1785        assert_eq!(
1786            user_preference(&db, other, "list.columns./roles")
1787                .await
1788                .unwrap(),
1789            None
1790        );
1791        assert_eq!(
1792            user_preference(&db, root, "list.columns./users")
1793                .await
1794                .unwrap(),
1795            None
1796        );
1797    }
1798}
laterite_auth/store.rs

laterite_auth/
store.rs
