OXYGENHUB
# 6a1780558353 oxygenhub / crates / hub / src / db / accounts.rs
30.9 KiB · Rust · last changed Admins disable, enable and delete accounts 1 month ago source blame history raw
19bb31ff Andy McCall ↶ 1 
//! Accounts, organisations, teams, collaborators, identities, tokens,
2 
//! invitations, settings and the audit log (hub spec §5).
3
4 
use super::{now_ms, Pool};
5 
use sqlx::FromRow;
6
7 
#[derive(Clone, Debug, FromRow)]
8 
pub struct Account {
9 
    pub id: i64,
10 
    pub username: String,
11 
    pub display_name: String,
12 
    pub email: String,
13 
    pub email_verified: bool,
14 
    pub password_hash: String,
15 
    pub session_salt: String,
16 
    pub totp_secret: Option<String>,
17 
    pub recovery_codes: String,
18 
    pub is_admin: bool,
19 
    pub banned: bool,
6a178055 Andy McCall ↶ 20 
    /// Disabled by an admin: refused like a ban, and enabled again (0031).
21 
    #[sqlx(default)]
22 
    pub disabled: bool,
19bb31ff Andy McCall ↶ 23 
    pub created_ms: i64,
24 
    pub last_seen_ms: i64,
18e381ea Andy McCall ↶ 25 
    pub email_notifications: bool,
26 
    pub unsubscribe_key: String,
10174045 Andy McCall ↶ 27 
    /// Shown on the profile page (migration 0008).
28 
    #[sqlx(default)]
29 
    pub bio: String,
30 
    #[sqlx(default)]
31 
    pub website: String,
32 
    #[sqlx(default)]
33 
    pub location: String,
a160b98f Andy McCall ↶ 34 
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
35 
    pub avatar: String,
19bb31ff Andy McCall ↶ 36 
}
37
38 
impl Account {
6a178055 Andy McCall ↶ 39 
    /// Whether the account may not sign in or act: banned or disabled.
40 
    pub fn blocked(&self) -> bool {
41 
        self.banned || self.disabled
42 
    }
43
19bb31ff Andy McCall ↶ 44 
    pub fn name(&self) -> &str {
45 
        if self.display_name.is_empty() {
46 
            &self.username
47 
        } else {
48 
            &self.display_name
49 
        }
50 
    }
51
52 
    pub fn totp_enabled(&self) -> bool {
53 
        self.totp_secret.is_some()
54 
    }
55 
}
56
57 
#[derive(Clone, Debug, FromRow)]
58 
pub struct Organisation {
59 
    pub id: i64,
60 
    pub name: String,
61 
    pub display_name: String,
62 
    pub description: String,
63 
    pub default_role: String,
64 
    pub created_ms: i64,
a160b98f Andy McCall ↶ 65 
    /// `png:<hash>` or `jpeg:<hash>` when a picture was uploaded.
66 
    pub avatar: String,
19bb31ff Andy McCall ↶ 67 
}
68
69 
#[derive(Clone, Debug, FromRow)]
70 
pub struct Team {
71 
    pub id: i64,
72 
    pub org_id: i64,
73 
    pub name: String,
74 
}
75
76 
#[derive(Clone, Debug, FromRow)]
77 
pub struct Token {
78 
    pub id: i64,
79 
    pub account_id: i64,
80 
    pub name: String,
81 
    pub hash: String,
82 
    pub scopes: String,
83 
    pub repos: String,
84 
    pub created_ms: i64,
85 
    pub expires_ms: Option<i64>,
86 
    pub last_used_ms: Option<i64>,
60f11b71 Andy McCall ↶ 87 
    /// A renewing token's lifetime: each use extends it to now plus this.
88 
    pub renew_ms: Option<i64>,
19bb31ff Andy McCall ↶ 89 
}
90
91 
impl Token {
92 
    pub fn scope_list(&self) -> Vec<&str> {
93 
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
94 
    }
95
96 
    pub fn has_scope(&self, scope: &str) -> bool {
97 
        self.scope_list().contains(&scope)
98 
    }
99
100 
    /// Whether the token may touch `owner/name`: every repository when the
101 
    /// list is empty.
102 
    pub fn covers(&self, slug: &str) -> bool {
103 
        self.repos.is_empty() || self.repos.split(',').any(|r| r.trim().eq_ignore_ascii_case(slug))
104 
    }
105
106 
    pub fn expired(&self, now: i64) -> bool {
107 
        self.expires_ms.is_some_and(|e| e < now)
108 
    }
109 
}
110
111 
#[derive(Clone, Debug, FromRow)]
112 
pub struct LinkedIdentity {
113 
    pub key: String,
114 
    pub account_id: i64,
115 
    pub name: String,
116 
    pub email: String,
117 
    pub object: Vec<u8>,
118 
    pub linked_ms: i64,
119 
}
120
121 
#[derive(Clone, Debug, FromRow)]
122 
pub struct AuditRow {
123 
    pub id: i64,
124 
    pub at_ms: i64,
125 
    pub actor: String,
126 
    pub action: String,
127 
    pub target: String,
128 
    pub detail: String,
129 
}
130
131 
// ---------------------------------------------------------------- accounts
132
133 
pub async fn account_by_name(pool: &Pool, username: &str) -> sqlx::Result<Option<Account>> {
134 
    sqlx::query_as("SELECT * FROM accounts WHERE username = ?").bind(username).fetch_optional(pool).await
135 
}
136
137 
pub async fn account_by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Account>> {
138 
    sqlx::query_as("SELECT * FROM accounts WHERE id = ?").bind(id).fetch_optional(pool).await
139 
}
140
141 
pub async fn accounts(pool: &Pool) -> sqlx::Result<Vec<Account>> {
142 
    sqlx::query_as("SELECT * FROM accounts ORDER BY username").fetch_all(pool).await
143 
}
144
145 
pub async fn account_count(pool: &Pool) -> sqlx::Result<i64> {
146 
    sqlx::query_scalar("SELECT COUNT(*) FROM accounts").fetch_one(pool).await
147 
}
148
149 
pub async fn create_account(
150 
    pool: &Pool,
151 
    username: &str,
152 
    email: &str,
153 
    password_hash: &str,
154 
    session_salt: &str,
155 
    is_admin: bool,
156 
) -> sqlx::Result<Account> {
157 
    sqlx::query_as(
158 
        "INSERT INTO accounts (username, email, password_hash, session_salt, is_admin, created_ms) VALUES (?, ?, ?, ?, ?, ?) RETURNING *",
159 
    )
160 
    .bind(username)
161 
    .bind(email)
162 
    .bind(password_hash)
163 
    .bind(session_salt)
164 
    .bind(is_admin)
165 
    .bind(now_ms())
166 
    .fetch_one(pool)
167 
    .await
168 
}
169
170 
pub async fn set_profile(pool: &Pool, id: i64, display_name: &str, email: &str) -> sqlx::Result<()> {
34beb1a7 Andy McCall ↶ 171 
    // A changed address is unverified until its own link is used.
172 
    sqlx::query(
173 
        "UPDATE accounts SET display_name = ?, email_verified = CASE WHEN email = ? THEN email_verified ELSE 0 END, email = ? WHERE id = ?",
174 
    )
175 
    .bind(display_name)
176 
    .bind(email)
177 
    .bind(email)
178 
    .bind(id)
179 
    .execute(pool)
180 
    .await?;
19bb31ff Andy McCall ↶ 181 
    Ok(())
182 
}
183
10174045 Andy McCall ↶ 184 
/// The public profile fields.
185 
pub async fn set_bio(pool: &Pool, id: i64, bio: &str, website: &str, location: &str) -> sqlx::Result<()> {
186 
    sqlx::query("UPDATE accounts SET bio = ?, website = ?, location = ? WHERE id = ?")
187 
        .bind(bio)
188 
        .bind(website)
189 
        .bind(location)
190 
        .bind(id)
191 
        .execute(pool)
192 
        .await?;
193 
    Ok(())
194 
}
195
19bb31ff Andy McCall ↶ 196 
pub async fn set_password(pool: &Pool, id: i64, password_hash: &str, session_salt: &str) -> sqlx::Result<()> {
197 
    sqlx::query("UPDATE accounts SET password_hash = ?, session_salt = ? WHERE id = ?")
198 
        .bind(password_hash)
199 
        .bind(session_salt)
200 
        .bind(id)
201 
        .execute(pool)
202 
        .await?;
203 
    Ok(())
204 
}
205
206 
pub async fn set_session_salt(pool: &Pool, id: i64, session_salt: &str) -> sqlx::Result<()> {
207 
    sqlx::query("UPDATE accounts SET session_salt = ? WHERE id = ?").bind(session_salt).bind(id).execute(pool).await?;
208 
    Ok(())
209 
}
210
211 
pub async fn set_totp(pool: &Pool, id: i64, secret: Option<&str>, recovery_codes: &str) -> sqlx::Result<()> {
212 
    sqlx::query("UPDATE accounts SET totp_secret = ?, recovery_codes = ? WHERE id = ?")
213 
        .bind(secret)
214 
        .bind(recovery_codes)
215 
        .bind(id)
216 
        .execute(pool)
217 
        .await?;
218 
    Ok(())
219 
}
220
221 
pub async fn set_admin(pool: &Pool, id: i64, is_admin: bool) -> sqlx::Result<()> {
222 
    sqlx::query("UPDATE accounts SET is_admin = ? WHERE id = ?").bind(is_admin).bind(id).execute(pool).await?;
223 
    Ok(())
224 
}
225
226 
pub async fn set_banned(pool: &Pool, id: i64, banned: bool) -> sqlx::Result<()> {
227 
    sqlx::query("UPDATE accounts SET banned = ? WHERE id = ?").bind(banned).bind(id).execute(pool).await?;
6a178055 Andy McCall ↶ 228 
    Ok(())
229 
}
230
231 
pub async fn set_disabled(pool: &Pool, id: i64, disabled: bool) -> sqlx::Result<()> {
232 
    sqlx::query("UPDATE accounts SET disabled = ? WHERE id = ?").bind(disabled).bind(id).execute(pool).await?;
19bb31ff Andy McCall ↶ 233 
    Ok(())
6a178055 Andy McCall ↶ 234 
}
235
236 
/// Delete an account (oxygenhub#81): its row goes, and with it (by the
237 
/// schema's cascades) its tokens, keys, sessions' salt, memberships, stars
238 
/// and notifications; what it wrote keeps its author's name. The username
239 
/// is retired so nobody can register it and pass for the account.
240 
pub async fn delete_account(pool: &Pool, id: i64, username: &str) -> sqlx::Result<()> {
241 
    let mut tx = super::begin_write(pool).await?;
242 
    sqlx::query("INSERT OR IGNORE INTO retired_usernames (username, retired_ms) VALUES (?, ?)")
243 
        .bind(username)
244 
        .bind(now_ms())
245 
        .execute(&mut *tx)
246 
        .await?;
247 
    sqlx::query("DELETE FROM accounts WHERE id = ?").bind(id).execute(&mut *tx).await?;
248 
    tx.commit().await
249 
}
250
251 
/// The organisations whose only owner is this account: deleting it would
252 
/// leave them with nobody to manage them.
253 
pub async fn orgs_owned_only_by(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<String>> {
254 
    sqlx::query_scalar(
255 
        "SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id \
256 
         WHERE m.account_id = ? AND m.role = 'owner' \
257 
           AND (SELECT COUNT(*) FROM org_members x WHERE x.org_id = o.id AND x.role = 'owner') = 1 \
258 
         ORDER BY o.name",
259 
    )
260 
    .bind(account_id)
261 
    .fetch_all(pool)
262 
    .await
263 
}
264
265 
/// Whether `name` belonged to a deleted account.
266 
pub async fn username_retired(pool: &Pool, name: &str) -> sqlx::Result<bool> {
267 
    Ok(sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM retired_usernames WHERE username = ?").bind(name).fetch_one(pool).await? > 0)
19bb31ff Andy McCall ↶ 268 
}
269
270 
pub async fn touch_seen(pool: &Pool, id: i64) -> sqlx::Result<()> {
271 
    sqlx::query("UPDATE accounts SET last_seen_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
272 
    Ok(())
273 
}
274
275 
// ----------------------------------------------------------- organisations
276
277 
pub async fn org_by_name(pool: &Pool, name: &str) -> sqlx::Result<Option<Organisation>> {
278 
    sqlx::query_as("SELECT * FROM organisations WHERE name = ?").bind(name).fetch_optional(pool).await
279 
}
280
281 
pub async fn orgs(pool: &Pool) -> sqlx::Result<Vec<Organisation>> {
282 
    sqlx::query_as("SELECT * FROM organisations ORDER BY name").fetch_all(pool).await
283 
}
284
a160b98f Andy McCall ↶ 285 
/// An organisation with the account's role in it.
286 
#[derive(FromRow)]
287 
struct Membership {
288 
    #[sqlx(flatten)]
289 
    org: Organisation,
290 
    role: String,
291 
}
292
19bb31ff Andy McCall ↶ 293 
pub async fn orgs_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<(Organisation, String)>> {
a160b98f Andy McCall ↶ 294 
    let rows: Vec<Membership> = sqlx::query_as(
295 
        "SELECT o.*, m.role FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ? ORDER BY o.name",
19bb31ff Andy McCall ↶ 296 
    )
297 
    .bind(account_id)
298 
    .fetch_all(pool)
299 
    .await?;
a160b98f Andy McCall ↶ 300 
    Ok(rows.into_iter().map(|m| (m.org, m.role)).collect())
19bb31ff Andy McCall ↶ 301 
}
302
303 
pub async fn create_org(pool: &Pool, name: &str, display_name: &str, owner: i64) -> sqlx::Result<Organisation> {
941abdd6 Andy McCall ↶ 304 
    let mut tx = super::begin_write(pool).await?;
19bb31ff Andy McCall ↶ 305 
    let org: Organisation = sqlx::query_as("INSERT INTO organisations (name, display_name, created_ms) VALUES (?, ?, ?) RETURNING *")
306 
        .bind(name)
307 
        .bind(display_name)
308 
        .bind(now_ms())
309 
        .fetch_one(&mut *tx)
310 
        .await?;
311 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, 'owner')")
312 
        .bind(org.id)
313 
        .bind(owner)
314 
        .execute(&mut *tx)
315 
        .await?;
316 
    tx.commit().await?;
317 
    Ok(org)
318 
}
319
320 
pub async fn set_org(pool: &Pool, id: i64, display_name: &str, description: &str, default_role: &str) -> sqlx::Result<()> {
321 
    sqlx::query("UPDATE organisations SET display_name = ?, description = ?, default_role = ? WHERE id = ?")
322 
        .bind(display_name)
323 
        .bind(description)
324 
        .bind(default_role)
325 
        .bind(id)
326 
        .execute(pool)
327 
        .await?;
328 
    Ok(())
329 
}
330
331 
/// `(account, role)` for every member.
332 
pub async fn org_members(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<(Account, String)>> {
333 
    let rows: Vec<(i64, String)> =
334 
        sqlx::query_as("SELECT account_id, role FROM org_members WHERE org_id = ?").bind(org_id).fetch_all(pool).await?;
335 
    let mut out = Vec::new();
336 
    for (id, role) in rows {
337 
        if let Some(a) = account_by_id(pool, id).await? {
338 
            out.push((a, role));
339 
        }
340 
    }
341 
    out.sort_by(|a, b| a.0.username.cmp(&b.0.username));
342 
    Ok(out)
343 
}
344
345 
pub async fn org_role(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
346 
    sqlx::query_scalar("SELECT role FROM org_members WHERE org_id = ? AND account_id = ?")
347 
        .bind(org_id)
348 
        .bind(account_id)
349 
        .fetch_optional(pool)
350 
        .await
351 
}
352
353 
pub async fn set_org_member(pool: &Pool, org_id: i64, account_id: i64, role: &str) -> sqlx::Result<()> {
354 
    sqlx::query("INSERT INTO org_members (org_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (org_id, account_id) DO UPDATE SET role = excluded.role")
355 
        .bind(org_id)
356 
        .bind(account_id)
357 
        .bind(role)
358 
        .execute(pool)
359 
        .await?;
360 
    Ok(())
361 
}
362
363 
pub async fn remove_org_member(pool: &Pool, org_id: i64, account_id: i64) -> sqlx::Result<()> {
364 
    sqlx::query("DELETE FROM org_members WHERE org_id = ? AND account_id = ?").bind(org_id).bind(account_id).execute(pool).await?;
365 
    Ok(())
366 
}
367
368 
pub async fn teams(pool: &Pool, org_id: i64) -> sqlx::Result<Vec<Team>> {
369 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? ORDER BY name").bind(org_id).fetch_all(pool).await
370 
}
371
372 
pub async fn team_by_name(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Option<Team>> {
373 
    sqlx::query_as("SELECT * FROM teams WHERE org_id = ? AND name = ?").bind(org_id).bind(name).fetch_optional(pool).await
374 
}
375
376 
pub async fn create_team(pool: &Pool, org_id: i64, name: &str) -> sqlx::Result<Team> {
377 
    sqlx::query_as("INSERT INTO teams (org_id, name) VALUES (?, ?) RETURNING *").bind(org_id).bind(name).fetch_one(pool).await
378 
}
379
380 
pub async fn delete_team(pool: &Pool, id: i64) -> sqlx::Result<()> {
381 
    sqlx::query("DELETE FROM teams WHERE id = ?").bind(id).execute(pool).await?;
382 
    Ok(())
383 
}
384
385 
pub async fn team_members(pool: &Pool, team_id: i64) -> sqlx::Result<Vec<Account>> {
386 
    sqlx::query_as("SELECT a.* FROM accounts a JOIN team_members t ON t.account_id = a.id WHERE t.team_id = ? ORDER BY a.username")
387 
        .bind(team_id)
388 
        .fetch_all(pool)
389 
        .await
390 
}
391
392 
pub async fn add_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
393 
    sqlx::query("INSERT OR IGNORE INTO team_members (team_id, account_id) VALUES (?, ?)")
394 
        .bind(team_id)
395 
        .bind(account_id)
396 
        .execute(pool)
397 
        .await?;
398 
    Ok(())
399 
}
400
401 
pub async fn remove_team_member(pool: &Pool, team_id: i64, account_id: i64) -> sqlx::Result<()> {
402 
    sqlx::query("DELETE FROM team_members WHERE team_id = ? AND account_id = ?").bind(team_id).bind(account_id).execute(pool).await?;
403 
    Ok(())
404 
}
405
406 
// ----------------------------------------------------------- collaborators
407
408 
#[derive(Clone, Debug)]
409 
pub struct Collaborator {
410 
    pub account: Option<Account>,
411 
    pub team: Option<Team>,
412 
    pub role: String,
413 
}
414
415 
pub async fn collaborators(pool: &Pool, repo_id: i64) -> sqlx::Result<Vec<Collaborator>> {
416 
    let rows: Vec<(Option<i64>, Option<i64>, String)> =
417 
        sqlx::query_as("SELECT account_id, team_id, role FROM collaborators WHERE repo_id = ?").bind(repo_id).fetch_all(pool).await?;
418 
    let mut out = Vec::new();
419 
    for (account_id, team_id, role) in rows {
420 
        let account = match account_id {
421 
            Some(id) => account_by_id(pool, id).await?,
422 
            None => None,
423 
        };
424 
        let team = match team_id {
425 
            Some(id) => sqlx::query_as("SELECT * FROM teams WHERE id = ?").bind(id).fetch_optional(pool).await?,
426 
            None => None,
427 
        };
428 
        out.push(Collaborator { account, team, role });
429 
    }
430 
    Ok(out)
431 
}
432
433 
pub async fn set_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>, role: &str) -> sqlx::Result<()> {
434 
    if let Some(a) = account_id {
435 
        sqlx::query("INSERT INTO collaborators (repo_id, account_id, role) VALUES (?, ?, ?) ON CONFLICT (repo_id, account_id) WHERE account_id IS NOT NULL DO UPDATE SET role = excluded.role")
436 
            .bind(repo_id)
437 
            .bind(a)
438 
            .bind(role)
439 
            .execute(pool)
440 
            .await?;
441 
    } else if let Some(t) = team_id {
442 
        sqlx::query("INSERT INTO collaborators (repo_id, team_id, role) VALUES (?, ?, ?) ON CONFLICT (repo_id, team_id) WHERE team_id IS NOT NULL DO UPDATE SET role = excluded.role")
443 
            .bind(repo_id)
444 
            .bind(t)
445 
            .bind(role)
446 
            .execute(pool)
447 
            .await?;
448 
    }
449 
    Ok(())
450 
}
451
452 
pub async fn remove_collaborator(pool: &Pool, repo_id: i64, account_id: Option<i64>, team_id: Option<i64>) -> sqlx::Result<()> {
453 
    sqlx::query("DELETE FROM collaborators WHERE repo_id = ? AND ((account_id IS NOT NULL AND account_id = ?) OR (team_id IS NOT NULL AND team_id = ?))")
454 
        .bind(repo_id)
455 
        .bind(account_id)
456 
        .bind(team_id)
457 
        .execute(pool)
458 
        .await?;
459 
    Ok(())
460 
}
461
462 
/// The direct or team role an account holds on a repository, the highest.
463 
pub async fn collaborator_role(pool: &Pool, repo_id: i64, account_id: i64) -> sqlx::Result<Option<String>> {
464 
    let rows: Vec<String> = sqlx::query_scalar(
465 
        "SELECT role FROM collaborators WHERE repo_id = ? AND (account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
466 
    )
467 
    .bind(repo_id)
468 
    .bind(account_id)
469 
    .bind(account_id)
470 
    .fetch_all(pool)
471 
    .await?;
472 
    Ok(rows.into_iter().max_by_key(|r| crate::auth::Role::parse(r).map(|r| r as u8).unwrap_or(0)))
473 
}
474
475 
// ------------------------------------------------------------------ tokens
476
477 
pub async fn tokens_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Token>> {
478 
    sqlx::query_as("SELECT * FROM tokens WHERE account_id = ? ORDER BY created_ms DESC").bind(account_id).fetch_all(pool).await
479 
}
480
481 
pub async fn token_by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Token>> {
482 
    sqlx::query_as("SELECT * FROM tokens WHERE hash = ?").bind(hash).fetch_optional(pool).await
483 
}
484
485 
pub async fn create_token(
486 
    pool: &Pool,
487 
    account_id: i64,
488 
    name: &str,
489 
    hash: &str,
490 
    scopes: &str,
491 
    repos: &str,
492 
    expires_ms: Option<i64>,
493 
) -> sqlx::Result<Token> {
494 
    sqlx::query_as(
495 
        "INSERT INTO tokens (account_id, name, hash, scopes, repos, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
496 
    )
497 
    .bind(account_id)
498 
    .bind(name)
499 
    .bind(hash)
500 
    .bind(scopes)
501 
    .bind(repos)
502 
    .bind(now_ms())
503 
    .bind(expires_ms)
504 
    .fetch_one(pool)
505 
    .await
506 
}
507
508 
pub async fn delete_token(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<()> {
509 
    sqlx::query("DELETE FROM tokens WHERE id = ? AND account_id = ?").bind(id).bind(account_id).execute(pool).await?;
510 
    Ok(())
511 
}
512
60f11b71 Andy McCall ↶ 513 
/// Record a use; a renewing token's expiry moves to now plus its lifetime.
19bb31ff Andy McCall ↶ 514 
pub async fn touch_token(pool: &Pool, id: i64) -> sqlx::Result<()> {
60f11b71 Andy McCall ↶ 515 
    sqlx::query(
516 
        "UPDATE tokens SET last_used_ms = ?1, \
517 
         expires_ms = CASE WHEN renew_ms IS NULL THEN expires_ms ELSE MAX(COALESCE(expires_ms, 0), ?1 + renew_ms) END \
518 
         WHERE id = ?2",
519 
    )
520 
    .bind(now_ms())
521 
    .bind(id)
522 
    .execute(pool)
523 
    .await?;
524 
    Ok(())
525 
}
526
527 
/// Make a token renewing: it lasts `renew_ms` past its last use.
528 
pub async fn set_token_renewal(pool: &Pool, id: i64, renew_ms: i64) -> sqlx::Result<()> {
529 
    sqlx::query("UPDATE tokens SET renew_ms = ? WHERE id = ?").bind(renew_ms).bind(id).execute(pool).await?;
19bb31ff Andy McCall ↶ 530 
    Ok(())
531 
}
532
df71c789 Andy McCall ↶ 533 
// ------------------------------------------------------- device sign-in
534
535 
/// A device code an app asked for (oxygenhub#67): pending until the user
536 
/// approves or denies it on the web.
537 
#[derive(Clone, Debug, FromRow)]
538 
pub struct DeviceCode {
539 
    pub id: i64,
540 
    pub device_hash: String,
541 
    pub user_code: String,
542 
    pub client: String,
543 
    pub scopes: String,
544 
    pub created_ms: i64,
545 
    pub expires_ms: i64,
546 
    pub last_poll_ms: i64,
547 
    pub account_id: Option<i64>,
548 
    pub approved_scopes: String,
549 
    pub denied: bool,
60f11b71 Andy McCall ↶ 550 
    /// The address that asked for the code, as the hub saw it.
551 
    pub requester: String,
df71c789 Andy McCall ↶ 552 
}
553
554 
impl DeviceCode {
555 
    pub fn scope_list(&self) -> Vec<&str> {
556 
        self.scopes.split(',').filter(|s| !s.is_empty()).collect()
557 
    }
558 
}
559
560 
pub async fn create_device_code(
561 
    pool: &Pool,
562 
    device_hash: &str,
563 
    user_code: &str,
564 
    client: &str,
565 
    scopes: &str,
566 
    expires_ms: i64,
60f11b71 Andy McCall ↶ 567 
    requester: &str,
df71c789 Andy McCall ↶ 568 
) -> sqlx::Result<DeviceCode> {
569 
    // Expired codes go whenever a new one is made.
570 
    sqlx::query("DELETE FROM device_codes WHERE expires_ms < ?").bind(now_ms()).execute(pool).await?;
571 
    sqlx::query_as(
60f11b71 Andy McCall ↶ 572 
        "INSERT INTO device_codes (device_hash, user_code, client, scopes, created_ms, expires_ms, requester) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING *",
df71c789 Andy McCall ↶ 573 
    )
574 
    .bind(device_hash)
575 
    .bind(user_code)
576 
    .bind(client)
577 
    .bind(scopes)
578 
    .bind(now_ms())
579 
    .bind(expires_ms)
60f11b71 Andy McCall ↶ 580 
    .bind(requester)
df71c789 Andy McCall ↶ 581 
    .fetch_one(pool)
582 
    .await
583 
}
584
585 
pub async fn device_by_user_code(pool: &Pool, user_code: &str) -> sqlx::Result<Option<DeviceCode>> {
586 
    sqlx::query_as("SELECT * FROM device_codes WHERE user_code = ?").bind(user_code).fetch_optional(pool).await
587 
}
588
589 
pub async fn device_by_hash(pool: &Pool, device_hash: &str) -> sqlx::Result<Option<DeviceCode>> {
590 
    sqlx::query_as("SELECT * FROM device_codes WHERE device_hash = ?").bind(device_hash).fetch_optional(pool).await
591 
}
592
593 
/// Approve a pending code for an account with these scopes; false when it
594 
/// was no longer pending.
595 
pub async fn approve_device(pool: &Pool, id: i64, account_id: i64, scopes: &str) -> sqlx::Result<bool> {
596 
    let r = sqlx::query(
597 
        "UPDATE device_codes SET account_id = ?, approved_scopes = ? WHERE id = ? AND account_id IS NULL AND denied = 0 AND expires_ms >= ?",
598 
    )
599 
    .bind(account_id)
600 
    .bind(scopes)
601 
    .bind(id)
602 
    .bind(now_ms())
603 
    .execute(pool)
604 
    .await?;
605 
    Ok(r.rows_affected() == 1)
606 
}
607
608 
pub async fn deny_device(pool: &Pool, id: i64) -> sqlx::Result<()> {
609 
    sqlx::query("UPDATE device_codes SET denied = 1 WHERE id = ? AND account_id IS NULL").bind(id).execute(pool).await?;
610 
    Ok(())
611 
}
612
613 
pub async fn device_polled(pool: &Pool, id: i64) -> sqlx::Result<()> {
614 
    sqlx::query("UPDATE device_codes SET last_poll_ms = ? WHERE id = ?").bind(now_ms()).bind(id).execute(pool).await?;
615 
    Ok(())
616 
}
617
618 
/// Remove a code; true when this call removed it (so one poll gets the
619 
/// token, however many race).
620 
pub async fn delete_device(pool: &Pool, id: i64) -> sqlx::Result<bool> {
621 
    let r = sqlx::query("DELETE FROM device_codes WHERE id = ?").bind(id).execute(pool).await?;
622 
    Ok(r.rows_affected() == 1)
623 
}
624
19bb31ff Andy McCall ↶ 625 
// -------------------------------------------------------------- identities
626
627 
pub async fn identities_of(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<LinkedIdentity>> {
628 
    sqlx::query_as("SELECT * FROM identities WHERE account_id = ? ORDER BY linked_ms DESC").bind(account_id).fetch_all(pool).await
629 
}
630
631 
pub async fn identity_by_key(pool: &Pool, key: &str) -> sqlx::Result<Option<LinkedIdentity>> {
632 
    sqlx::query_as("SELECT * FROM identities WHERE key = ?").bind(key).fetch_optional(pool).await
633 
}
634
635 
/// Every linked key with its account's username, for attribution.
636 
pub async fn identity_accounts(pool: &Pool) -> sqlx::Result<Vec<(String, String)>> {
637 
    sqlx::query_as("SELECT i.key, a.username FROM identities i JOIN accounts a ON a.id = i.account_id").fetch_all(pool).await
638 
}
639
640 
pub async fn link_identity(pool: &Pool, account_id: i64, key: &str, name: &str, email: &str, object: &[u8]) -> sqlx::Result<()> {
641 
    sqlx::query("INSERT INTO identities (key, account_id, name, email, object, linked_ms) VALUES (?, ?, ?, ?, ?, ?)")
642 
        .bind(key)
643 
        .bind(account_id)
644 
        .bind(name)
645 
        .bind(email)
646 
        .bind(object)
647 
        .bind(now_ms())
648 
        .execute(pool)
649 
        .await?;
650 
    Ok(())
651 
}
652
653 
pub async fn unlink_identity(pool: &Pool, account_id: i64, key: &str) -> sqlx::Result<()> {
654 
    sqlx::query("DELETE FROM identities WHERE key = ? AND account_id = ?").bind(key).bind(account_id).execute(pool).await?;
655 
    Ok(())
656 
}
657
658 
pub async fn create_challenge(pool: &Pool, account_id: i64, bytes_hex: &str) -> sqlx::Result<i64> {
659 
    sqlx::query("DELETE FROM challenges WHERE created_ms < ?").bind(now_ms() - 10 * 60 * 1000).execute(pool).await?;
660 
    let id: i64 = sqlx::query_scalar("INSERT INTO challenges (account_id, bytes, created_ms) VALUES (?, ?, ?) RETURNING id")
661 
        .bind(account_id)
662 
        .bind(bytes_hex)
663 
        .bind(now_ms())
664 
        .fetch_one(pool)
665 
        .await?;
666 
    Ok(id)
667 
}
668
669 
/// Take (and delete) a pending challenge of this account.
670 
pub async fn take_challenge(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<Option<String>> {
671 
    let bytes: Option<String> = sqlx::query_scalar("SELECT bytes FROM challenges WHERE id = ? AND account_id = ? AND created_ms >= ?")
672 
        .bind(id)
673 
        .bind(account_id)
674 
        .bind(now_ms() - 10 * 60 * 1000)
675 
        .fetch_optional(pool)
676 
        .await?;
677 
    sqlx::query("DELETE FROM challenges WHERE id = ?").bind(id).execute(pool).await?;
678 
    Ok(bytes)
679 
}
680
681 
// ------------------------------------------------------------- invitations
682
683 
pub async fn create_invitation(pool: &Pool, code: &str, created_by: Option<i64>) -> sqlx::Result<()> {
684 
    sqlx::query("INSERT INTO invitations (code, created_by, created_ms) VALUES (?, ?, ?)")
685 
        .bind(code)
686 
        .bind(created_by)
687 
        .bind(now_ms())
688 
        .execute(pool)
689 
        .await?;
690 
    Ok(())
691 
}
692
693 
pub async fn invitation_unused(pool: &Pool, code: &str) -> sqlx::Result<bool> {
694 
    let n: i64 =
695 
        sqlx::query_scalar("SELECT COUNT(*) FROM invitations WHERE code = ? AND used_by IS NULL").bind(code).fetch_one(pool).await?;
696 
    Ok(n > 0)
697 
}
698
699 
pub async fn use_invitation(pool: &Pool, code: &str, account_id: i64) -> sqlx::Result<()> {
700 
    sqlx::query("UPDATE invitations SET used_by = ? WHERE code = ?").bind(account_id).bind(code).execute(pool).await?;
701 
    Ok(())
702 
}
703
704 
pub async fn invitations(pool: &Pool) -> sqlx::Result<Vec<(String, Option<i64>, i64)>> {
705 
    sqlx::query_as("SELECT code, used_by, created_ms FROM invitations ORDER BY created_ms DESC").fetch_all(pool).await
706 
}
707
708 
// ---------------------------------------------------------------- settings
709
710 
pub async fn setting(pool: &Pool, key: &str) -> sqlx::Result<Option<String>> {
711 
    sqlx::query_scalar("SELECT value FROM settings WHERE key = ?").bind(key).fetch_optional(pool).await
712 
}
713
714 
pub async fn set_setting(pool: &Pool, key: &str, value: &str) -> sqlx::Result<()> {
715 
    sqlx::query("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT (key) DO UPDATE SET value = excluded.value")
716 
        .bind(key)
717 
        .bind(value)
718 
        .execute(pool)
719 
        .await?;
720 
    Ok(())
721 
}
722
723 
// ------------------------------------------------------------------- audit
724
725 
pub async fn audit(pool: &Pool, actor: Option<&Account>, action: &str, target: &str, detail: &str) -> sqlx::Result<()> {
726 
    sqlx::query("INSERT INTO audit (at_ms, actor_id, actor, action, target, detail) VALUES (?, ?, ?, ?, ?, ?)")
727 
        .bind(now_ms())
728 
        .bind(actor.map(|a| a.id))
729 
        .bind(actor.map(|a| a.username.as_str()).unwrap_or(""))
730 
        .bind(action)
731 
        .bind(target)
732 
        .bind(detail)
733 
        .execute(pool)
734 
        .await?;
735 
    Ok(())
736 
}
737
738 
pub async fn audit_rows(pool: &Pool, limit: i64) -> sqlx::Result<Vec<AuditRow>> {
739 
    sqlx::query_as("SELECT id, at_ms, actor, action, target, detail FROM audit ORDER BY at_ms DESC, id DESC LIMIT ?")
740 
        .bind(limit)
741 
        .fetch_all(pool)
742 
        .await
743 
}
744
745 
/// The repositories an account can see beyond the public ones: its own,
746 
/// its organisations', and those it collaborates on.
747 
pub async fn visible_repo_ids(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<i64>> {
748 
    sqlx::query_scalar(
749 
        "SELECT r.id FROM repositories r WHERE r.owner IN (SELECT username FROM accounts WHERE id = ?) \
750 
         OR r.owner IN (SELECT o.name FROM organisations o JOIN org_members m ON m.org_id = o.id WHERE m.account_id = ?) \
751 
         OR r.id IN (SELECT repo_id FROM collaborators WHERE account_id = ? OR team_id IN (SELECT team_id FROM team_members WHERE account_id = ?))",
752 
    )
753 
    .bind(account_id)
754 
    .bind(account_id)
755 
    .bind(account_id)
756 
    .bind(account_id)
757 
    .fetch_all(pool)
758 
    .await
34beb1a7 Andy McCall ↶ 759 
}
760
761 
// ------------------------------------------------------ account links
762
763 
/// A verification or reset link (hub spec §5.1.1).
764 
#[derive(Clone, Debug, FromRow)]
765 
pub struct AccountLink {
766 
    pub id: i64,
767 
    pub account_id: i64,
768 
    pub kind: String,
769 
    pub hash: String,
770 
    pub email: String,
771 
    pub created_by: Option<i64>,
772 
    pub created_ms: i64,
773 
    pub expires_ms: i64,
774 
    pub used_ms: Option<i64>,
775 
}
776
777 
/// Make a link; returns its secret (shown or sent once, stored as a hash).
778 
pub async fn create_link(
779 
    pool: &Pool,
780 
    account_id: i64,
781 
    kind: &str,
782 
    email: &str,
783 
    created_by: Option<i64>,
784 
    ttl_ms: i64,
785 
) -> sqlx::Result<String> {
786 
    let secret = crate::auth::random_hex(32);
787 
    let now = now_ms();
788 
    sqlx::query(
789 
        "INSERT INTO account_links (account_id, kind, hash, email, created_by, created_ms, expires_ms) VALUES (?, ?, ?, ?, ?, ?, ?)",
790 
    )
791 
    .bind(account_id)
792 
    .bind(kind)
793 
    .bind(crate::auth::token_hash(&secret))
794 
    .bind(email)
795 
    .bind(created_by)
796 
    .bind(now)
797 
    .bind(now + ttl_ms)
798 
    .execute(pool)
799 
    .await?;
800 
    Ok(secret)
801 
}
802
803 
/// An unused, unexpired link of this kind with this secret.
804 
pub async fn live_link(pool: &Pool, kind: &str, secret: &str) -> sqlx::Result<Option<AccountLink>> {
805 
    sqlx::query_as("SELECT * FROM account_links WHERE kind = ? AND hash = ? AND used_ms IS NULL AND expires_ms > ?")
806 
        .bind(kind)
807 
        .bind(crate::auth::token_hash(secret))
808 
        .bind(now_ms())
809 
        .fetch_optional(pool)
810 
        .await
811 
}
812
813 
/// Consume a link; false when it was already used (a race lost).
814 
pub async fn use_link(pool: &Pool, id: i64) -> sqlx::Result<bool> {
815 
    let r =
816 
        sqlx::query("UPDATE account_links SET used_ms = ? WHERE id = ? AND used_ms IS NULL").bind(now_ms()).bind(id).execute(pool).await?;
817 
    Ok(r.rows_affected() == 1)
818 
}
819
820 
/// Void every unused link of a kind for an account.
821 
pub async fn void_links(pool: &Pool, account_id: i64, kind: &str) -> sqlx::Result<()> {
822 
    sqlx::query("UPDATE account_links SET used_ms = ? WHERE account_id = ? AND kind = ? AND used_ms IS NULL")
823 
        .bind(now_ms())
824 
        .bind(account_id)
825 
        .bind(kind)
826 
        .execute(pool)
827 
        .await?;
828 
    Ok(())
829 
}
830
831 
/// When the account's newest link of a kind (for this address, when given) was made, for rate limits.
832 
pub async fn last_link_ms(pool: &Pool, account_id: i64, kind: &str, email: Option<&str>) -> sqlx::Result<Option<i64>> {
833 
    match email {
834 
        Some(e) => {
835 
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ? AND email = ?")
836 
                .bind(account_id)
837 
                .bind(kind)
838 
                .bind(e)
839 
                .fetch_one(pool)
840 
                .await
841 
        }
842 
        None => {
843 
            sqlx::query_scalar("SELECT MAX(created_ms) FROM account_links WHERE account_id = ? AND kind = ?")
844 
                .bind(account_id)
845 
                .bind(kind)
846 
                .fetch_one(pool)
847 
                .await
848 
        }
849 
    }
850 
}
851
852 
/// How many links of a kind the account got since `since_ms`.
853 
pub async fn links_since(pool: &Pool, account_id: i64, kind: &str, since_ms: i64) -> sqlx::Result<i64> {
854 
    sqlx::query_scalar("SELECT COUNT(*) FROM account_links WHERE account_id = ? AND kind = ? AND created_ms >= ?")
855 
        .bind(account_id)
856 
        .bind(kind)
857 
        .bind(since_ms)
858 
        .fetch_one(pool)
859 
        .await
860 
}
861
862 
pub async fn set_email_verified(pool: &Pool, id: i64, verified: bool) -> sqlx::Result<()> {
863 
    sqlx::query("UPDATE accounts SET email_verified = ? WHERE id = ?").bind(verified).bind(id).execute(pool).await?;
864 
    Ok(())
865 
}
866
867 
/// The account with this verified email (case-insensitive), if exactly one.
868 
pub async fn account_by_verified_email(pool: &Pool, email: &str) -> sqlx::Result<Option<Account>> {
869 
    let found: Vec<Account> = sqlx::query_as("SELECT * FROM accounts WHERE email_verified = 1 AND lower(email) = lower(?) LIMIT 2")
870 
        .bind(email)
871 
        .fetch_all(pool)
872 
        .await?;
873 
    Ok(if found.len() == 1 { found.into_iter().next() } else { None })
a160b98f Andy McCall ↶ 874 
}
875
876 
/// Set or clear the picture of an account (`account`) or organisation (`org`).
877 
pub async fn set_avatar(pool: &Pool, kind: &str, id: i64, value: &str) -> sqlx::Result<()> {
878 
    let table = if kind == "org" { "organisations" } else { "accounts" };
879 
    sqlx::query(&format!("UPDATE {table} SET avatar = ? WHERE id = ?")).bind(value).bind(id).execute(pool).await?;
880 
    Ok(())
19bb31ff Andy McCall ↶ 881 
}