sessions.rs 70.9%

73 of 103 lines run, 18 of 45 functions · run #537 on main · run missed

//! Browser sessions as server-side records (oxygenhub#223).

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

/// A session ends after this long unused.
pub const IDLE_MS: i64 = 14 * 24 * 60 * 60_000;
/// And after this long whatever happens.
pub const ABSOLUTE_MS: i64 = 30 * 24 * 60 * 60_000;
/// `seen_ms` is written at most this often per session.
const TOUCH_EVERY_MS: i64 = 60_000;

#[derive(Clone, Debug, FromRow)]
pub struct Session {
    pub id: i64,
    pub account_id: i64,
    pub salt: String,
    pub created_ms: i64,
    pub seen_ms: i64,
    pub address: String,
    pub agent: String,
    /// When it last proved a second factor, or signed in (oxygenhub#356):
    /// the sudo step asks again once this is older than its window.
    pub second_factor_ms: i64,
}

impl Session {
    /// Whether it is still good at `now`, for an account whose salt is `salt`.
    pub fn live(&self, salt: &str, now: i64) -> bool {
        self.salt == salt && now - self.seen_ms < IDLE_MS && now - self.created_ms < ABSOLUTE_MS
    }
}

const COLUMNS: &str = "id, account_id, salt, created_ms, seen_ms, address, agent, second_factor_ms";

pub async fn create(pool: &Pool, account_id: i64, hash: &str, salt: &str) -> sqlx::Result<i64> {
    let now = now_ms();
    // Signing in is as fresh as proof gets (oxygenhub#356).
    sqlx::query_scalar(
        "INSERT INTO sessions (account_id, hash, salt, created_ms, seen_ms, second_factor_ms) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id",
    )
    .bind(account_id)
    .bind(hash)
    .bind(salt)
    .bind(now)
    .bind(now)
    .bind(now)
    .fetch_one(pool)
    .await
}

/// The session proved a second factor (or the password, for an account
/// without one) at `now`: the sudo step lets it through for a while.
pub async fn mark_second_factor(pool: &Pool, session_id: i64, now: i64) -> sqlx::Result<()> {
    sqlx::query("UPDATE sessions SET second_factor_ms = $1 WHERE id = $2").bind(now).bind(session_id).execute(pool).await?;
    Ok(())
}

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

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

/// Note a use: the time (at most once a minute) and, the first time, the
/// address and browser it came from.
pub async fn touch(pool: &Pool, s: &Session, address: &str, agent: &str, now: i64) -> sqlx::Result<()> {
    if s.agent.is_empty() && s.address.is_empty() && (!agent.is_empty() || !address.is_empty()) {
        sqlx::query("UPDATE sessions SET seen_ms = $1, address = $2, agent = $3 WHERE id = $4")
            .bind(now)
            .bind(address)
            .bind(agent)
            .bind(s.id)
            .execute(pool)
            .await?;
    } else if now - s.seen_ms >= TOUCH_EVERY_MS {
        sqlx::query("UPDATE sessions SET seen_ms = $1 WHERE id = $2").bind(now).bind(s.id).execute(pool).await?;
    }
    Ok(())
}

/// An account's live sessions, most recently used first.
pub async fn of_account(pool: &Pool, account_id: i64, salt: &str) -> sqlx::Result<Vec<Session>> {
    let now = now_ms();
    let all: Vec<Session> = sqlx::query_as(&format!("SELECT {COLUMNS} FROM sessions WHERE account_id = $1 ORDER BY seen_ms DESC"))
        .bind(account_id)
        .fetch_all(pool)
        .await?;
    Ok(all.into_iter().filter(|s| s.live(salt, now)).collect())
}

/// End one session of an account's; whether there was one.
pub async fn end(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("DELETE FROM sessions WHERE id = $1 AND account_id = $2").bind(id).bind(account_id).execute(pool).await?;
    Ok(r.rows_affected() > 0)
}

pub async fn end_hash(pool: &Pool, hash: &str) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM sessions WHERE hash = $1").bind(hash).execute(pool).await?;
    Ok(())
}

/// Every session of an account's but `keep`.
pub async fn end_others(pool: &Pool, account_id: i64, keep: i64) -> sqlx::Result<()> {
    sqlx::query("DELETE FROM sessions WHERE account_id = $1 AND id != $2").bind(account_id).bind(keep).execute(pool).await?;
    Ok(())
}

/// Forget sessions that can no longer be used; how many.
pub async fn sweep(pool: &Pool) -> sqlx::Result<u64> {
    let now = now_ms();
    let r = sqlx::query(
        "DELETE FROM sessions WHERE seen_ms < $1 OR created_ms < $2 \
         OR salt != (SELECT session_salt FROM accounts WHERE accounts.id = sessions.account_id)",
    )
    .bind(now - IDLE_MS)
    .bind(now - ABSOLUTE_MS)
    .execute(pool)
    .await?;
    Ok(r.rows_affected())
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn a_session_ends_on_a_new_salt_when_idle_or_when_old() {
        let t0 = 1_000_000_000_000;
        let s = Session {
            id: 1,
            account_id: 1,
            salt: "a".into(),
            created_ms: t0,
            seen_ms: t0,
            address: String::new(),
            agent: String::new(),
            second_factor_ms: t0,
        };
        assert!(s.live("a", t0 + 1000));
        assert!(!s.live("b", t0 + 1000), "a new salt: signed out everywhere");
        assert!(!s.live("a", t0 + IDLE_MS), "unused for 14 days");
        let busy = Session { seen_ms: t0 + ABSOLUTE_MS - 1000, ..s.clone() };
        assert!(busy.live("a", t0 + ABSOLUTE_MS - 1));
        assert!(!busy.live("a", t0 + ABSOLUTE_MS), "30 days, however busy");
    }
}

1		//! Browser sessions as server-side records (oxygenhub#223).
2
3		use super::{now_ms, Pool};
4		use sqlx::FromRow;
5
6		/// A session ends after this long unused.
7		pub const IDLE_MS: i64 = 14 * 24 * 60 * 60_000;
8		/// And after this long whatever happens.
9		pub const ABSOLUTE_MS: i64 = 30 * 24 * 60 * 60_000;
10		/// `seen_ms` is written at most this often per session.
11		const TOUCH_EVERY_MS: i64 = 60_000;
12
13		#[derive(Clone, Debug, FromRow)]
14		pub struct Session {
15		pub id: i64,
16		pub account_id: i64,
17		pub salt: String,
18		pub created_ms: i64,
19		pub seen_ms: i64,
20		pub address: String,
21		pub agent: String,
22		/// When it last proved a second factor, or signed in (oxygenhub#356):
23		/// the sudo step asks again once this is older than its window.
24		pub second_factor_ms: i64,
25		}
26
27		impl Session {
28		/// Whether it is still good at `now`, for an account whose salt is `salt`.
29	1331×	pub fn live(&self, salt: &str, now: i64) -> bool {
30	1331×	self.salt == salt && now - self.seen_ms < IDLE_MS && now - self.created_ms < ABSOLUTE_MS
31	1331×	}
32		}
33
34		const COLUMNS: &str = "id, account_id, salt, created_ms, seen_ms, address, agent, second_factor_ms";
35
36	148×	pub async fn create(pool: &Pool, account_id: i64, hash: &str, salt: &str) -> sqlx::Result<i64> {
37	148×	let now = now_ms();
38		// Signing in is as fresh as proof gets (oxygenhub#356).
39	148×	sqlx::query_scalar(
40	148×	"INSERT INTO sessions (account_id, hash, salt, created_ms, seen_ms, second_factor_ms) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id",
41	148×	)
42	148×	.bind(account_id)
43	148×	.bind(hash)
44	148×	.bind(salt)
45	148×	.bind(now)
46	148×	.bind(now)
47	148×	.bind(now)
48	148×	.fetch_one(pool)
49	148×	.await
50	148×	}
51
52		/// The session proved a second factor (or the password, for an account
53		/// without one) at `now`: the sudo step lets it through for a while.
54	2×	pub async fn mark_second_factor(pool: &Pool, session_id: i64, now: i64) -> sqlx::Result<()> {
55	2×	sqlx::query("UPDATE sessions SET second_factor_ms = $1 WHERE id = $2").bind(now).bind(session_id).execute(pool).await?;
56	2×	Ok(())
57	2×	}
58
59	0×	pub async fn by_id(pool: &Pool, id: i64) -> sqlx::Result<Option<Session>> {
60	0×	sqlx::query_as(&format!("SELECT {COLUMNS} FROM sessions WHERE id = $1")).bind(id).fetch_optional(pool).await
61	0×	}
62
63	1315×	pub async fn by_hash(pool: &Pool, hash: &str) -> sqlx::Result<Option<Session>> {
64	1315×	sqlx::query_as(&format!("SELECT {COLUMNS} FROM sessions WHERE hash = $1")).bind(hash).fetch_optional(pool).await
65	1315×	}
66
67		/// Note a use: the time (at most once a minute) and, the first time, the
68		/// address and browser it came from.
69	1136×	pub async fn touch(pool: &Pool, s: &Session, address: &str, agent: &str, now: i64) -> sqlx::Result<()> {
70	1136×	if s.agent.is_empty() && s.address.is_empty() && (!agent.is_empty() \|\| !address.is_empty()) {
71	0×	sqlx::query("UPDATE sessions SET seen_ms = $1, address = $2, agent = $3 WHERE id = $4")
72	0×	.bind(now)
73	0×	.bind(address)
74	0×	.bind(agent)
75	0×	.bind(s.id)
76	0×	.execute(pool)
77	0×	.await?;
78	1136×	} else if now - s.seen_ms >= TOUCH_EVERY_MS {
79	0×	sqlx::query("UPDATE sessions SET seen_ms = $1 WHERE id = $2").bind(now).bind(s.id).execute(pool).await?;
80	1136×	}
81	1136×	Ok(())
82	1136×	}
83
84		/// An account's live sessions, most recently used first.
85	10×	pub async fn of_account(pool: &Pool, account_id: i64, salt: &str) -> sqlx::Result<Vec<Session>> {
86	10×	let now = now_ms();
87	10×	let all: Vec<Session> = sqlx::query_as(&format!("SELECT {COLUMNS} FROM sessions WHERE account_id = $1 ORDER BY seen_ms DESC"))
88	10×	.bind(account_id)
89	10×	.fetch_all(pool)
90	10×	.await?;
91	16×	Ok(all.into_iter().filter(\|s\| s.live(salt, now)).collect())
92	10×	}
93
94		/// End one session of an account's; whether there was one.
95	1×	pub async fn end(pool: &Pool, account_id: i64, id: i64) -> sqlx::Result<bool> {
96	1×	let r = sqlx::query("DELETE FROM sessions WHERE id = $1 AND account_id = $2").bind(id).bind(account_id).execute(pool).await?;
97	1×	Ok(r.rows_affected() > 0)
98	1×	}
99
100	1×	pub async fn end_hash(pool: &Pool, hash: &str) -> sqlx::Result<()> {
101	1×	sqlx::query("DELETE FROM sessions WHERE hash = $1").bind(hash).execute(pool).await?;
102	1×	Ok(())
103	1×	}
104
105		/// Every session of an account's but `keep`.
106	0×	pub async fn end_others(pool: &Pool, account_id: i64, keep: i64) -> sqlx::Result<()> {
107	0×	sqlx::query("DELETE FROM sessions WHERE account_id = $1 AND id != $2").bind(account_id).bind(keep).execute(pool).await?;
108	0×	Ok(())
109	0×	}
110
111		/// Forget sessions that can no longer be used; how many.
112	0×	pub async fn sweep(pool: &Pool) -> sqlx::Result<u64> {
113	0×	let now = now_ms();
114	0×	let r = sqlx::query(
115	0×	"DELETE FROM sessions WHERE seen_ms < $1 OR created_ms < $2 \
116	0×	OR salt != (SELECT session_salt FROM accounts WHERE accounts.id = sessions.account_id)",
117	0×	)
118	0×	.bind(now - IDLE_MS)
119	0×	.bind(now - ABSOLUTE_MS)
120	0×	.execute(pool)
121	0×	.await?;
122	0×	Ok(r.rows_affected())
123	0×	}
124
125		#[cfg(test)]
126		mod tests {
127		use super::*;
128
129		#[test]
130	1×	fn a_session_ends_on_a_new_salt_when_idle_or_when_old() {
131	1×	let t0 = 1_000_000_000_000;
132	1×	let s = Session {
133	1×	id: 1,
134	1×	account_id: 1,
135	1×	salt: "a".into(),
136	1×	created_ms: t0,
137	1×	seen_ms: t0,
138	1×	address: String::new(),
139	1×	agent: String::new(),
140	1×	second_factor_ms: t0,
141	1×	};
142	1×	assert!(s.live("a", t0 + 1000));
143	1×	assert!(!s.live("b", t0 + 1000), "a new salt: signed out everywhere");
144	1×	assert!(!s.live("a", t0 + IDLE_MS), "unused for 14 days");
145	1×	let busy = Session { seen_ms: t0 + ABSOLUTE_MS - 1000, ..s.clone() };
146	1×	assert!(busy.live("a", t0 + ABSOLUTE_MS - 1));
147	1×	assert!(!busy.live("a", t0 + ABSOLUTE_MS), "30 days, however busy");
148	1×	}
149		}

`/`	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/sessions.rs 70.9%

Keyboard