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
11use std::collections::HashMap;
12
13use chrono::{DateTime, SecondsFormat, Utc};
14use laterite_core::query::{
15    bind_values, bind_values_as, build, insert_returning_id, on_conflict_ignore,
16};
17use laterite_core::{AnyRowExt, Db};
18use sea_query::{Expr, Order, Query};
19use sqlx::any::AnyRow;
20use sqlx::Row;
21
22use crate::error::AuthError;
23use crate::models::{AccessEvent, BackendUser, BackendUserSummary};
24use crate::schema::{
25    BackendAccessLog, BackendRoles, BackendSessions, BackendUserRoles, BackendUsers,
26};
27
28fn now_ts() -> String {
29    Utc::now().to_rfc3339_opts(SecondsFormat::Micros, true)
30}
31
32fn ts(dt: DateTime<Utc>) -> String {
33    dt.to_rfc3339_opts(SecondsFormat::Micros, true)
34}
35
36fn parse_ts(s: &str) -> Result<DateTime<Utc>, AuthError> {
37    DateTime::parse_from_rfc3339(s)
38        .map(|d| d.with_timezone(&Utc))
39        .map_err(|e| AuthError::Data(format!("timestamp `{s}`: {e}")))
40}
41
42/// Normalises a user-facing key (username or email) so lookups, uniqueness, and
43/// login throttling behave identically on every backend. MySQL's default
44/// collation is case-insensitive (and trailing-space-insensitive) while Postgres
45/// and SQLite are case-sensitive, so the framework lower-cases and trims these
46/// keys before storing or matching. Apply it on every write and lookup of a
47/// username or email so `Root` and `root ` resolve to the same account anywhere.
48fn normalize_key(s: &str) -> String {
49    s.trim().to_lowercase()
50}
51
52fn user_from_row(row: &AnyRow) -> Result<BackendUser, AuthError> {
53    Ok(BackendUser {
54        id: row.try_get::<i64, _>("id")?,
55        username: row.get_text("username")?,
56        email: row.get_text("email")?,
57        first_name: row.get_text("first_name")?,
58        last_name: row.get_text_opt("last_name")?,
59        password_hash: row.get_text("password_hash")?,
60        is_superuser: row.get_bool("is_superuser")?,
61        is_active: row.get_bool("is_active")?,
62        timezone: row.get_text_opt("timezone")?,
63        created_at: parse_ts(&row.get_text("created_at")?)?,
64        updated_at: parse_ts(&row.get_text("updated_at")?)?,
65    })
66}
67
68fn summary_from_row(row: &AnyRow) -> Result<BackendUserSummary, AuthError> {
69    Ok(BackendUserSummary {
70        id: row.try_get::<i64, _>("id")?,
71        username: row.get_text("username")?,
72        email: row.get_text("email")?,
73        first_name: row.get_text("first_name")?,
74        last_name: row.get_text_opt("last_name")?,
75        is_superuser: row.get_bool("is_superuser")?,
76        is_active: row.get_bool("is_active")?,
77        created_at: parse_ts(&row.get_text("created_at")?)?,
78    })
79}
80
81const USER_COLS: [BackendUsers; 11] = [
82    BackendUsers::Id,
83    BackendUsers::Username,
84    BackendUsers::Email,
85    BackendUsers::FirstName,
86    BackendUsers::LastName,
87    BackendUsers::PasswordHash,
88    BackendUsers::IsSuperuser,
89    BackendUsers::IsActive,
90    BackendUsers::Timezone,
91    BackendUsers::CreatedAt,
92    BackendUsers::UpdatedAt,
93];
94
95/// Looks up a user by username without filtering on active state.
96pub async fn find_user_by_username(
97    db: &Db,
98    username: &str,
99) -> Result<Option<BackendUser>, AuthError> {
100    let (sql, values) = build(
101        db.backend,
102        Query::select()
103            .columns(USER_COLS)
104            .from(BackendUsers::Table)
105            .and_where(Expr::col(BackendUsers::Username).eq(normalize_key(username)))
106            .to_owned(),
107    );
108    let row = bind_values(sqlx::query(&sql), values)
109        .fetch_optional(&db.pool)
110        .await?;
111    row.map(|r| user_from_row(&r)).transpose()
112}
113
114/// Looks up an active user by id, used when resolving a session to an identity.
115pub async fn find_active_user_by_id(db: &Db, id: i64) -> Result<Option<BackendUser>, AuthError> {
116    let (sql, values) = build(
117        db.backend,
118        Query::select()
119            .columns(USER_COLS)
120            .from(BackendUsers::Table)
121            .and_where(Expr::col(BackendUsers::Id).eq(id))
122            .and_where(Expr::col(BackendUsers::IsActive).eq(true))
123            .to_owned(),
124    );
125    let row = bind_values(sqlx::query(&sql), values)
126        .fetch_optional(&db.pool)
127        .await?;
128    row.map(|r| user_from_row(&r)).transpose()
129}
130
131/// Returns the permission lists of every role assigned to a user (each stored
132/// as a JSON array), for the service to flatten into a permission set.
133pub async fn load_role_permissions(db: &Db, user_id: i64) -> Result<Vec<Vec<String>>, AuthError> {
134    let (sql, values) = build(
135        db.backend,
136        Query::select()
137            .column((BackendRoles::Table, BackendRoles::Permissions))
138            .from(BackendUserRoles::Table)
139            .inner_join(
140                BackendRoles::Table,
141                Expr::col((BackendRoles::Table, BackendRoles::Id))
142                    .equals((BackendUserRoles::Table, BackendUserRoles::BackendRoleId)),
143            )
144            .and_where(
145                Expr::col((BackendUserRoles::Table, BackendUserRoles::BackendUserId)).eq(user_id),
146            )
147            .to_owned(),
148    );
149    let rows = bind_values(sqlx::query(&sql), values)
150        .fetch_all(&db.pool)
151        .await?;
152    let mut out = Vec::with_capacity(rows.len());
153    for row in rows {
154        let json = row.get_text("permissions")?;
155        let perms: Vec<String> = serde_json::from_str(&json)
156            .map_err(|e| AuthError::Data(format!("role permissions: {e}")))?;
157        out.push(perms);
158    }
159    Ok(out)
160}
161
162/// Loads a user's per-permission overrides: a map of permission code to `1`
163/// (allow) or `-1` (deny).
164pub async fn load_user_permission_overrides(
165    db: &Db,
166    user_id: i64,
167) -> Result<HashMap<String, i64>, AuthError> {
168    let (sql, values) = build(
169        db.backend,
170        Query::select()
171            .column(BackendUsers::Permissions)
172            .from(BackendUsers::Table)
173            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
174            .to_owned(),
175    );
176    let row = bind_values(sqlx::query(&sql), values)
177        .fetch_optional(&db.pool)
178        .await?;
179    let overrides = match row {
180        Some(r) => {
181            let json = r.get_text("permissions")?;
182            serde_json::from_str(&json).unwrap_or_default()
183        }
184        None => HashMap::new(),
185    };
186    Ok(overrides)
187}
188
189/// Replaces a user's per-permission overrides (stored as a JSON object).
190pub async fn set_user_permissions(
191    db: &Db,
192    user_id: i64,
193    overrides: &HashMap<String, i64>,
194) -> Result<(), AuthError> {
195    let json = serde_json::to_string(overrides).unwrap_or_else(|_| "{}".to_string());
196    let (sql, values) = build(
197        db.backend,
198        Query::update()
199            .table(BackendUsers::Table)
200            .value(BackendUsers::Permissions, json)
201            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
202            .to_owned(),
203    );
204    bind_values(sqlx::query(&sql), values)
205        .execute(&db.pool)
206        .await?;
207    Ok(())
208}
209
210pub async fn insert_session(
211    db: &Db,
212    token_hash: &str,
213    user_id: i64,
214    expires_at: DateTime<Utc>,
215) -> Result<(), AuthError> {
216    let now = now_ts();
217    let (sql, values) = build(
218        db.backend,
219        Query::insert()
220            .into_table(BackendSessions::Table)
221            .columns([
222                BackendSessions::TokenHash,
223                BackendSessions::BackendUserId,
224                BackendSessions::CreatedAt,
225                BackendSessions::LastSeenAt,
226                BackendSessions::ExpiresAt,
227            ])
228            .values_panic([
229                token_hash.into(),
230                user_id.into(),
231                now.clone().into(),
232                now.into(),
233                ts(expires_at).into(),
234            ])
235            .to_owned(),
236    );
237    bind_values(sqlx::query(&sql), values)
238        .execute(&db.pool)
239        .await?;
240    Ok(())
241}
242
243/// Returns the owning user id of a non-expired session, if any.
244pub async fn find_valid_session(
245    db: &Db,
246    token_hash: &str,
247    now: DateTime<Utc>,
248) -> Result<Option<i64>, AuthError> {
249    let (sql, values) = build(
250        db.backend,
251        Query::select()
252            .column(BackendSessions::BackendUserId)
253            .from(BackendSessions::Table)
254            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
255            .and_where(Expr::col(BackendSessions::ExpiresAt).gt(ts(now)))
256            .to_owned(),
257    );
258    let row = bind_values(sqlx::query(&sql), values)
259        .fetch_optional(&db.pool)
260        .await?;
261    match row {
262        Some(r) => Ok(Some(r.try_get::<i64, _>("backend_user_id")?)),
263        None => Ok(None),
264    }
265}
266
267pub async fn touch_session(db: &Db, token_hash: &str, now: DateTime<Utc>) -> Result<(), AuthError> {
268    let (sql, values) = build(
269        db.backend,
270        Query::update()
271            .table(BackendSessions::Table)
272            .value(BackendSessions::LastSeenAt, ts(now))
273            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
274            .to_owned(),
275    );
276    bind_values(sqlx::query(&sql), values)
277        .execute(&db.pool)
278        .await?;
279    Ok(())
280}
281
282pub async fn delete_session(db: &Db, token_hash: &str) -> Result<(), AuthError> {
283    let (sql, values) = build(
284        db.backend,
285        Query::delete()
286            .from_table(BackendSessions::Table)
287            .and_where(Expr::col(BackendSessions::TokenHash).eq(token_hash))
288            .to_owned(),
289    );
290    bind_values(sqlx::query(&sql), values)
291        .execute(&db.pool)
292        .await?;
293    Ok(())
294}
295
296pub async fn insert_access_log(
297    db: &Db,
298    user_id: Option<i64>,
299    username_attempted: &str,
300    event: AccessEvent,
301    ip_address: Option<&str>,
302    user_agent: Option<&str>,
303) -> Result<(), AuthError> {
304    let (sql, values) = build(
305        db.backend,
306        Query::insert()
307            .into_table(BackendAccessLog::Table)
308            .columns([
309                BackendAccessLog::BackendUserId,
310                BackendAccessLog::UsernameAttempted,
311                BackendAccessLog::Event,
312                BackendAccessLog::IpAddress,
313                BackendAccessLog::UserAgent,
314                BackendAccessLog::CreatedAt,
315            ])
316            .values_panic([
317                user_id.into(),
318                normalize_key(username_attempted).into(),
319                event.as_str().into(),
320                ip_address.map(str::to_string).into(),
321                user_agent.map(str::to_string).into(),
322                now_ts().into(),
323            ])
324            .to_owned(),
325    );
326    bind_values(sqlx::query(&sql), values)
327        .execute(&db.pool)
328        .await?;
329    Ok(())
330}
331
332/// Counts recent failed login attempts for a username, for throttling.
333pub async fn count_recent_failures(
334    db: &Db,
335    username: &str,
336    since: DateTime<Utc>,
337) -> Result<i64, AuthError> {
338    let (sql, values) = build(
339        db.backend,
340        Query::select()
341            .expr(Expr::col(BackendAccessLog::Id).count())
342            .from(BackendAccessLog::Table)
343            .and_where(Expr::col(BackendAccessLog::UsernameAttempted).eq(normalize_key(username)))
344            .and_where(Expr::col(BackendAccessLog::Event).eq(AccessEvent::LoginFailure.as_str()))
345            .and_where(Expr::col(BackendAccessLog::CreatedAt).gte(ts(since)))
346            .to_owned(),
347    );
348    let count: i64 = bind_values_as(sqlx::query_as::<_, (i64,)>(&sql), values)
349        .fetch_one(&db.pool)
350        .await?
351        .0;
352    Ok(count)
353}
354
355/// Creates a backend user, returning the id the database assigned. Timestamps
356/// are generated here (no database-side defaults) so the insert is portable.
357#[allow(clippy::too_many_arguments)]
358pub async fn create_user(
359    db: &Db,
360    username: &str,
361    email: &str,
362    first_name: &str,
363    last_name: Option<&str>,
364    password_hash: &str,
365    is_superuser: bool,
366) -> Result<i64, AuthError> {
367    let now = now_ts();
368    let stmt = Query::insert()
369        .into_table(BackendUsers::Table)
370        .columns([
371            BackendUsers::Username,
372            BackendUsers::Email,
373            BackendUsers::FirstName,
374            BackendUsers::LastName,
375            BackendUsers::PasswordHash,
376            BackendUsers::IsSuperuser,
377            BackendUsers::CreatedAt,
378            BackendUsers::UpdatedAt,
379        ])
380        .values_panic([
381            normalize_key(username).into(),
382            normalize_key(email).into(),
383            first_name.into(),
384            last_name.map(str::to_string).into(),
385            password_hash.into(),
386            is_superuser.into(),
387            now.clone().into(),
388            now.into(),
389        ])
390        .to_owned();
391    Ok(insert_returning_id(db, stmt, BackendUsers::Id).await?)
392}
393
394/// Whether any backend user exists.
395pub async fn any_user_exists(db: &Db) -> Result<bool, AuthError> {
396    let (sql, values) = build(
397        db.backend,
398        Query::select()
399            .expr(Expr::col(BackendUsers::Id).count())
400            .from(BackendUsers::Table)
401            .to_owned(),
402    );
403    let count: i64 = bind_values_as(sqlx::query_as::<_, (i64,)>(&sql), values)
404        .fetch_one(&db.pool)
405        .await?
406        .0;
407    Ok(count > 0)
408}
409
410/// Sets an operator's own display timezone, or clears it with `None`.
411pub async fn set_user_timezone(
412    db: &Db,
413    user_id: i64,
414    timezone: Option<&str>,
415) -> Result<(), AuthError> {
416    let (sql, values) = build(
417        db.backend,
418        Query::update()
419            .table(BackendUsers::Table)
420            .value(BackendUsers::Timezone, timezone.map(str::to_string))
421            .value(BackendUsers::UpdatedAt, now_ts())
422            .and_where(Expr::col(BackendUsers::Id).eq(user_id))
423            .to_owned(),
424    );
425    bind_values(sqlx::query(&sql), values)
426        .execute(&db.pool)
427        .await?;
428    Ok(())
429}
430
431pub async fn create_role(
432    db: &Db,
433    code: &str,
434    name: &str,
435    permissions: &[String],
436) -> Result<i64, AuthError> {
437    let perms = serde_json::to_string(permissions).unwrap_or_else(|_| "[]".to_string());
438    let stmt = Query::insert()
439        .into_table(BackendRoles::Table)
440        .columns([
441            BackendRoles::Code,
442            BackendRoles::Name,
443            BackendRoles::Permissions,
444            BackendRoles::CreatedAt,
445        ])
446        .values_panic([code.into(), name.into(), perms.into(), now_ts().into()])
447        .to_owned();
448    Ok(insert_returning_id(db, stmt, BackendRoles::Id).await?)
449}
450
451pub async fn assign_role(db: &Db, user_id: i64, role_id: i64) -> Result<(), AuthError> {
452    let (sql, values) = build(
453        db.backend,
454        Query::insert()
455            .into_table(BackendUserRoles::Table)
456            .columns([
457                BackendUserRoles::BackendUserId,
458                BackendUserRoles::BackendRoleId,
459            ])
460            .values_panic([user_id.into(), role_id.into()])
461            .on_conflict(on_conflict_ignore([
462                BackendUserRoles::BackendUserId,
463                BackendUserRoles::BackendRoleId,
464            ]))
465            .to_owned(),
466    );
467    bind_values(sqlx::query(&sql), values)
468        .execute(&db.pool)
469        .await?;
470    Ok(())
471}
472
473/// Lists backend users for admin tooling, ordered by creation time.
474pub async fn list_backend_users(db: &Db) -> Result<Vec<BackendUserSummary>, AuthError> {
475    let (sql, values) = build(
476        db.backend,
477        Query::select()
478            .columns([
479                BackendUsers::Id,
480                BackendUsers::Username,
481                BackendUsers::Email,
482                BackendUsers::FirstName,
483                BackendUsers::LastName,
484                BackendUsers::IsSuperuser,
485                BackendUsers::IsActive,
486                BackendUsers::CreatedAt,
487            ])
488            .from(BackendUsers::Table)
489            .order_by(BackendUsers::CreatedAt, Order::Asc)
490            .to_owned(),
491    );
492    let rows = bind_values(sqlx::query(&sql), values)
493        .fetch_all(&db.pool)
494        .await?;
495    rows.iter().map(summary_from_row).collect()
496}
497
498/// Sets a new password hash for a user by username, returning rows affected.
499pub async fn update_password_by_username(
500    db: &Db,
501    username: &str,
502    password_hash: &str,
503) -> Result<u64, AuthError> {
504    let (sql, values) = build(
505        db.backend,
506        Query::update()
507            .table(BackendUsers::Table)
508            .value(BackendUsers::PasswordHash, password_hash)
509            .value(BackendUsers::UpdatedAt, now_ts())
510            .and_where(Expr::col(BackendUsers::Username).eq(normalize_key(username)))
511            .to_owned(),
512    );
513    let result = bind_values(sqlx::query(&sql), values)
514        .execute(&db.pool)
515        .await?;
516    Ok(result.rows_affected())
517}
518
519/// Clears a user's failed-login records, releasing a lockout.
520pub async fn clear_failed_attempts(db: &Db, username: &str) -> Result<u64, AuthError> {
521    let (sql, values) = build(
522        db.backend,
523        Query::delete()
524            .from_table(BackendAccessLog::Table)
525            .and_where(Expr::col(BackendAccessLog::UsernameAttempted).eq(normalize_key(username)))
526            .and_where(Expr::col(BackendAccessLog::Event).eq(AccessEvent::LoginFailure.as_str()))
527            .to_owned(),
528    );
529    let result = bind_values(sqlx::query(&sql), values)
530        .execute(&db.pool)
531        .await?;
532    Ok(result.rows_affected())
533}
laterite_auth/store.rs

laterite_auth/
store.rs
