approvals.rs 95.9%

94 of 98 lines run, 18 of 40 functions · run #537 on main · run missed

//! Phones as a second factor (oxygenhub#356): their keys, and the
//! approvals they are asked for.

use super::{now_ms, Pool};
use sqlx::FromRow;

/// A phone that approves for its account: the Keystore key of the sign-in
/// (token) it made, as an uncompressed P-256 point in hex.
#[derive(Clone, Debug, FromRow)]
pub struct Authenticator {
    pub token_id: i64,
    pub account_id: i64,
    pub name: String,
    pub public_key: String,
    pub created_ms: i64,
    pub last_used_ms: i64,
}

const AUTH_COLUMNS: &str = "token_id, account_id, name, public_key, created_ms, last_used_ms";

/// Register (or replace) the key of the phone signed in with `token_id`.
pub async fn add_authenticator(pool: &Pool, token_id: i64, account_id: i64, name: &str, public_key: &str) -> sqlx::Result<()> {
    sqlx::query(
        "INSERT INTO authenticators (token_id, account_id, name, public_key, created_ms) VALUES ($1, $2, $3, $4, $5) \
         ON CONFLICT (token_id) DO UPDATE SET name = excluded.name, public_key = excluded.public_key, created_ms = excluded.created_ms",
    )
    .bind(token_id)
    .bind(account_id)
    .bind(name)
    .bind(public_key)
    .bind(now_ms())
    .execute(pool)
    .await?;
    Ok(())
}

pub async fn authenticators(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Authenticator>> {
    sqlx::query_as(&format!("SELECT {AUTH_COLUMNS} FROM authenticators WHERE account_id = $1 ORDER BY created_ms"))
        .bind(account_id)
        .fetch_all(pool)
        .await
}

pub async fn authenticator(pool: &Pool, token_id: i64) -> sqlx::Result<Option<Authenticator>> {
    sqlx::query_as(&format!("SELECT {AUTH_COLUMNS} FROM authenticators WHERE token_id = $1")).bind(token_id).fetch_optional(pool).await
}

/// Remove one of the account's phones; false when it had none by that id.
pub async fn remove_authenticator(pool: &Pool, account_id: i64, token_id: i64) -> sqlx::Result<bool> {
    Ok(sqlx::query("DELETE FROM authenticators WHERE account_id = $1 AND token_id = $2")
        .bind(account_id)
        .bind(token_id)
        .execute(pool)
        .await?
        .rows_affected()
        > 0)
}

/// An approval asked of the account's phones: what for, the number the
/// page shows among the three the phone offers, and what became of it.
#[derive(Clone, Debug, FromRow)]
pub struct Approval {
    pub id: String,
    pub account_id: i64,
    /// `signin` or `stepup`.
    pub kind: String,
    pub what: String,
    pub number: i64,
    /// The three numbers the phone offers, comma-separated.
    pub choices: String,
    /// For a step-up: the browser session it makes fresh.
    pub session_id: Option<i64>,
    pub created_ms: i64,
    pub expires_ms: i64,
    /// `pending`, `approved`, `denied`, or `used` once a sign-in took it.
    pub state: String,
    pub decided_ms: i64,
    pub decided_by: Option<i64>,
}

const COLUMNS: &str = "id, account_id, kind, what, number, choices, session_id, created_ms, expires_ms, state, decided_ms, decided_by";

pub struct NewApproval<'a> {
    pub id: &'a str,
    pub account_id: i64,
    pub kind: &'a str,
    pub what: &'a str,
    pub number: i64,
    pub choices: &'a str,
    pub session_id: Option<i64>,
    pub expires_ms: i64,
}

pub async fn create(pool: &Pool, a: NewApproval<'_>) -> sqlx::Result<()> {
    sqlx::query(
        "INSERT INTO approvals (id, account_id, kind, what, number, choices, session_id, created_ms, expires_ms) \
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)",
    )
    .bind(a.id)
    .bind(a.account_id)
    .bind(a.kind)
    .bind(a.what)
    .bind(a.number)
    .bind(a.choices)
    .bind(a.session_id)
    .bind(now_ms())
    .bind(a.expires_ms)
    .execute(pool)
    .await?;
    Ok(())
}

pub async fn get(pool: &Pool, id: &str) -> sqlx::Result<Option<Approval>> {
    sqlx::query_as(&format!("SELECT {COLUMNS} FROM approvals WHERE id = $1")).bind(id).fetch_optional(pool).await
}

/// The account's approvals still waiting at `now`, newest first.
pub async fn pending(pool: &Pool, account_id: i64, now: i64) -> sqlx::Result<Vec<Approval>> {
    sqlx::query_as(&format!(
        "SELECT {COLUMNS} FROM approvals WHERE account_id = $1 AND state = 'pending' AND expires_ms > $2 ORDER BY created_ms DESC"
    ))
    .bind(account_id)
    .bind(now)
    .fetch_all(pool)
    .await
}

/// Decide a pending approval, once: false when it was no longer pending
/// or had expired.
pub async fn decide(pool: &Pool, id: &str, state: &str, by: i64, now: i64) -> sqlx::Result<bool> {
    let done = sqlx::query(
        "UPDATE approvals SET state = $1, decided_ms = $2, decided_by = $3 WHERE id = $4 AND state = 'pending' AND expires_ms > $2",
    )
    .bind(state)
    .bind(now)
    .bind(by)
    .bind(id)
    .execute(pool)
    .await?
    .rows_affected()
        > 0;
    if done && state == "approved" {
        sqlx::query("UPDATE authenticators SET last_used_ms = $1 WHERE token_id = $2").bind(now).bind(by).execute(pool).await?;
    }
    Ok(done)
}

/// Take an approved sign-in approval for the sign-in it was made for,
/// once: false when it is not approved, or was taken already.
pub async fn take(pool: &Pool, id: &str, account_id: i64) -> sqlx::Result<bool> {
    Ok(sqlx::query("UPDATE approvals SET state = 'used' WHERE id = $1 AND account_id = $2 AND state = 'approved' AND kind = 'signin'")
        .bind(id)
        .bind(account_id)
        .execute(pool)
        .await?
        .rows_affected()
        > 0)
}

/// Approvals that ended a day ago or more are of no further use.
pub async fn sweep(pool: &Pool, now: i64) -> sqlx::Result<u64> {
    Ok(sqlx::query("DELETE FROM approvals WHERE expires_ms < $1").bind(now - 86_400_000).execute(pool).await?.rows_affected())
}

1		//! Phones as a second factor (oxygenhub#356): their keys, and the
2		//! approvals they are asked for.
3
4		use super::{now_ms, Pool};
5		use sqlx::FromRow;
6
7		/// A phone that approves for its account: the Keystore key of the sign-in
8		/// (token) it made, as an uncompressed P-256 point in hex.
9		#[derive(Clone, Debug, FromRow)]
10		pub struct Authenticator {
11		pub token_id: i64,
12		pub account_id: i64,
13		pub name: String,
14		pub public_key: String,
15		pub created_ms: i64,
16		pub last_used_ms: i64,
17		}
18
19		const AUTH_COLUMNS: &str = "token_id, account_id, name, public_key, created_ms, last_used_ms";
20
21		/// Register (or replace) the key of the phone signed in with `token_id`.
22	1×	pub async fn add_authenticator(pool: &Pool, token_id: i64, account_id: i64, name: &str, public_key: &str) -> sqlx::Result<()> {
23	1×	sqlx::query(
24	1×	"INSERT INTO authenticators (token_id, account_id, name, public_key, created_ms) VALUES ($1, $2, $3, $4, $5) \
25	1×	ON CONFLICT (token_id) DO UPDATE SET name = excluded.name, public_key = excluded.public_key, created_ms = excluded.created_ms",
26	1×	)
27	1×	.bind(token_id)
28	1×	.bind(account_id)
29	1×	.bind(name)
30	1×	.bind(public_key)
31	1×	.bind(now_ms())
32	1×	.execute(pool)
33	1×	.await?;
34	1×	Ok(())
35	1×	}
36
37	25×	pub async fn authenticators(pool: &Pool, account_id: i64) -> sqlx::Result<Vec<Authenticator>> {
38	25×	sqlx::query_as(&format!("SELECT {AUTH_COLUMNS} FROM authenticators WHERE account_id = $1 ORDER BY created_ms"))
39	25×	.bind(account_id)
40	25×	.fetch_all(pool)
41	25×	.await
42	25×	}
43
44	8×	pub async fn authenticator(pool: &Pool, token_id: i64) -> sqlx::Result<Option<Authenticator>> {
45	8×	sqlx::query_as(&format!("SELECT {AUTH_COLUMNS} FROM authenticators WHERE token_id = $1")).bind(token_id).fetch_optional(pool).await
46	8×	}
47
48		/// Remove one of the account's phones; false when it had none by that id.
49	1×	pub async fn remove_authenticator(pool: &Pool, account_id: i64, token_id: i64) -> sqlx::Result<bool> {
50	1×	Ok(sqlx::query("DELETE FROM authenticators WHERE account_id = $1 AND token_id = $2")
51	1×	.bind(account_id)
52	1×	.bind(token_id)
53	1×	.execute(pool)
54	1×	.await?
55	1×	.rows_affected()
56		> 0)
57	1×	}
58
59		/// An approval asked of the account's phones: what for, the number the
60		/// page shows among the three the phone offers, and what became of it.
61		#[derive(Clone, Debug, FromRow)]
62		pub struct Approval {
63		pub id: String,
64		pub account_id: i64,
65		/// `signin` or `stepup`.
66		pub kind: String,
67		pub what: String,
68		pub number: i64,
69		/// The three numbers the phone offers, comma-separated.
70		pub choices: String,
71		/// For a step-up: the browser session it makes fresh.
72		pub session_id: Option<i64>,
73		pub created_ms: i64,
74		pub expires_ms: i64,
75		/// `pending`, `approved`, `denied`, or `used` once a sign-in took it.
76		pub state: String,
77		pub decided_ms: i64,
78		pub decided_by: Option<i64>,
79		}
80
81		const COLUMNS: &str = "id, account_id, kind, what, number, choices, session_id, created_ms, expires_ms, state, decided_ms, decided_by";
82
83		pub struct NewApproval<'a> {
84		pub id: &'a str,
85		pub account_id: i64,
86		pub kind: &'a str,
87		pub what: &'a str,
88		pub number: i64,
89		pub choices: &'a str,
90		pub session_id: Option<i64>,
91		pub expires_ms: i64,
92		}
93
94	3×	pub async fn create(pool: &Pool, a: NewApproval<'_>) -> sqlx::Result<()> {
95	3×	sqlx::query(
96	3×	"INSERT INTO approvals (id, account_id, kind, what, number, choices, session_id, created_ms, expires_ms) \
97	3×	VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)",
98	3×	)
99	3×	.bind(a.id)
100	3×	.bind(a.account_id)
101	3×	.bind(a.kind)
102	3×	.bind(a.what)
103	3×	.bind(a.number)
104	3×	.bind(a.choices)
105	3×	.bind(a.session_id)
106	3×	.bind(now_ms())
107	3×	.bind(a.expires_ms)
108	3×	.execute(pool)
109	3×	.await?;
110	3×	Ok(())
111	3×	}
112
113	13×	pub async fn get(pool: &Pool, id: &str) -> sqlx::Result<Option<Approval>> {
114	13×	sqlx::query_as(&format!("SELECT {COLUMNS} FROM approvals WHERE id = $1")).bind(id).fetch_optional(pool).await
115	13×	}
116
117		/// The account's approvals still waiting at `now`, newest first.
118	3×	pub async fn pending(pool: &Pool, account_id: i64, now: i64) -> sqlx::Result<Vec<Approval>> {
119	3×	sqlx::query_as(&format!(
120	3×	"SELECT {COLUMNS} FROM approvals WHERE account_id = $1 AND state = 'pending' AND expires_ms > $2 ORDER BY created_ms DESC"
121	3×	))
122	3×	.bind(account_id)
123	3×	.bind(now)
124	3×	.fetch_all(pool)
125	3×	.await
126	3×	}
127
128		/// Decide a pending approval, once: false when it was no longer pending
129		/// or had expired.
130	3×	pub async fn decide(pool: &Pool, id: &str, state: &str, by: i64, now: i64) -> sqlx::Result<bool> {
131	3×	let done = sqlx::query(
132	3×	"UPDATE approvals SET state = $1, decided_ms = $2, decided_by = $3 WHERE id = $4 AND state = 'pending' AND expires_ms > $2",
133	3×	)
134	3×	.bind(state)
135	3×	.bind(now)
136	3×	.bind(by)
137	3×	.bind(id)
138	3×	.execute(pool)
139	3×	.await?
140	3×	.rows_affected()
141		> 0;
142	3×	if done && state == "approved" {
143	2×	sqlx::query("UPDATE authenticators SET last_used_ms = $1 WHERE token_id = $2").bind(now).bind(by).execute(pool).await?;
144	1×	}
145	3×	Ok(done)
146	3×	}
147
148		/// Take an approved sign-in approval for the sign-in it was made for,
149		/// once: false when it is not approved, or was taken already.
150	3×	pub async fn take(pool: &Pool, id: &str, account_id: i64) -> sqlx::Result<bool> {
151	3×	Ok(sqlx::query("UPDATE approvals SET state = 'used' WHERE id = $1 AND account_id = $2 AND state = 'approved' AND kind = 'signin'")
152	3×	.bind(id)
153	3×	.bind(account_id)
154	3×	.execute(pool)
155	3×	.await?
156	3×	.rows_affected()
157		> 0)
158	3×	}
159
160		/// Approvals that ended a day ago or more are of no further use.
161	0×	pub async fn sweep(pool: &Pool, now: i64) -> sqlx::Result<u64> {
162	0×	Ok(sqlx::query("DELETE FROM approvals WHERE expires_ms < $1").bind(now - 86_400_000).execute(pool).await?.rows_affected())
163	0×	}

`/`	Search
`g` `c`	Code
`g` `h`	History
`g` `b`	Bookmarks
`g` `t`	Trust
`y`	Permalink to this state (files and trees)
`?`	This sheet

Coverage / crates/hub/src/db/approvals.rs 95.9%

Keyboard