koan_core/db/queries/
api_keys.rs

1//! Subsonic API keys (OpenSubsonic `apiKeyAuthentication`).
2//!
3//! A key stands in for a username and password: it acts as its user, at the
4//! user's current role, until revoked. Only `sha256(key)` is stored, so the key
5//! itself is shown once, when it is made.
6
7use rusqlite::{Connection, params};
8use subtle::ConstantTimeEq;
9
10use crate::auth::{self, Role};
11
12use super::auth::UserRow;
13
14/// `last_used_at` is written at most this often per key, so a client paging
15/// through a library is not a database write per request.
16const TOUCH_INTERVAL_SECS: i64 = 60;
17
18#[derive(Debug, Clone)]
19pub struct ApiKeyRow {
20    pub id: i64,
21    pub user_id: i64,
22    pub username: String,
23    pub name: String,
24    pub created_at: i64,
25    pub last_used_at: Option<i64>,
26}
27
28/// Make a key for `user_id`. Returns its row id and the key, which is not
29/// recoverable afterwards.
30pub fn create_api_key(
31    conn: &Connection,
32    user_id: i64,
33    name: &str,
34) -> Result<(i64, String), rusqlite::Error> {
35    let key =
36        auth::random_api_key().map_err(|e| rusqlite::Error::ToSqlConversionFailure(e.into()))?;
37    conn.execute(
38        "INSERT INTO api_keys (user_id, name, key_hash, created_at) VALUES (?1, ?2, ?3, ?4)",
39        params![
40            user_id,
41            name,
42            auth::sha256_hex(&key),
43            auth::now_unix() as i64
44        ],
45    )?;
46    Ok((conn.last_insert_rowid(), key))
47}
48
49/// Keys, oldest first — every user's, or one user's.
50pub fn list_api_keys(
51    conn: &Connection,
52    user_id: Option<i64>,
53) -> Result<Vec<ApiKeyRow>, rusqlite::Error> {
54    let mut stmt = conn.prepare(
55        "SELECT k.id, k.user_id, u.username, k.name, k.created_at, k.last_used_at
56         FROM api_keys k JOIN users u ON u.id = k.user_id
57         WHERE ?1 IS NULL OR k.user_id = ?1
58         ORDER BY k.id",
59    )?;
60    let rows = stmt.query_map(params![user_id], |row| {
61        Ok(ApiKeyRow {
62            id: row.get(0)?,
63            user_id: row.get(1)?,
64            username: row.get(2)?,
65            name: row.get(3)?,
66            created_at: row.get(4)?,
67            last_used_at: row.get(5)?,
68        })
69    })?;
70    rows.collect()
71}
72
73/// Revoke a key. With `user_id`, only that user's key can go. Returns whether
74/// one did.
75pub fn revoke_api_key(
76    conn: &Connection,
77    id: i64,
78    user_id: Option<i64>,
79) -> Result<bool, rusqlite::Error> {
80    let n = conn.execute(
81        "DELETE FROM api_keys WHERE id = ?1 AND (?2 IS NULL OR user_id = ?2)",
82        params![id, user_id],
83    )?;
84    Ok(n > 0)
85}
86
87/// Revoke every key a user has. Returns how many went.
88pub fn revoke_user_api_keys(conn: &Connection, user_id: i64) -> Result<usize, rusqlite::Error> {
89    conn.execute("DELETE FROM api_keys WHERE user_id = ?1", params![user_id])
90}
91
92/// The user a key belongs to, if it is a live key.
93///
94/// Every stored hash is compared, in constant time, rather than looking the
95/// hash up by index: an index lookup takes longer the more of the hash it
96/// matched. The table holds a handful of rows per user.
97pub fn authenticate_api_key(
98    conn: &Connection,
99    key: &str,
100) -> Result<Option<UserRow>, rusqlite::Error> {
101    let given = auth::sha256_hex(key);
102    let mut found = None;
103    {
104        let mut stmt = conn.prepare("SELECT id, key_hash FROM api_keys")?;
105        let rows = stmt.query_map([], |row| {
106            Ok((row.get::<_, i64>(0)?, row.get::<_, String>(1)?))
107        })?;
108        for row in rows {
109            let (id, hash) = row?;
110            if bool::from(hash.as_bytes().ct_eq(given.as_bytes())) {
111                found = Some(id);
112            }
113        }
114    }
115    let Some(id) = found else {
116        return Ok(None);
117    };
118
119    let now = auth::now_unix() as i64;
120    conn.execute(
121        "UPDATE api_keys SET last_used_at = ?1
122         WHERE id = ?2 AND (last_used_at IS NULL OR last_used_at <= ?3)",
123        params![now, id, now - TOUCH_INTERVAL_SECS],
124    )?;
125
126    let mut stmt = conn.prepare(
127        "SELECT u.id, u.username, u.password_hash, u.role, u.created_at
128         FROM api_keys k JOIN users u ON u.id = k.user_id WHERE k.id = ?1",
129    )?;
130    let mut rows = stmt.query_map(params![id], |row| {
131        let role: String = row.get(3)?;
132        Ok(UserRow {
133            id: row.get(0)?,
134            username: row.get(1)?,
135            password_hash: row.get(2)?,
136            role: role.parse().unwrap_or(Role::Readonly),
137            created_at: row.get(4)?,
138        })
139    })?;
140    rows.next().transpose()
141}
142
143#[cfg(test)]
144mod tests {
145    use super::*;
146    use crate::db::connection::Database;
147    use crate::db::queries::auth::{create_user, delete_user, update_role};
148
149    fn test_db() -> (Database, tempfile::TempDir) {
150        let tmp = tempfile::TempDir::new().unwrap();
151        let db = Database::open(&tmp.path().join("test.db")).unwrap();
152        (db, tmp)
153    }
154
155    #[test]
156    fn key_signs_in_as_its_user_at_their_current_role() {
157        let (db, _tmp) = test_db();
158        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
159        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
160        assert_eq!(key.len(), 43);
161
162        let user = authenticate_api_key(&db.conn, &key).unwrap().unwrap();
163        assert_eq!((user.username.as_str(), user.role), ("alice", Role::User));
164
165        update_role(&db.conn, "alice", Role::Readonly).unwrap();
166        let user = authenticate_api_key(&db.conn, &key).unwrap().unwrap();
167        assert_eq!(user.role, Role::Readonly);
168
169        assert!(authenticate_api_key(&db.conn, "nope").unwrap().is_none());
170    }
171
172    #[test]
173    fn only_the_hash_is_stored_and_use_is_recorded() {
174        let (db, _tmp) = test_db();
175        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
176        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
177        let stored: String = db
178            .conn
179            .query_row("SELECT key_hash FROM api_keys", [], |r| r.get(0))
180            .unwrap();
181        assert_eq!(stored, auth::sha256_hex(&key));
182
183        assert!(
184            list_api_keys(&db.conn, None).unwrap()[0]
185                .last_used_at
186                .is_none()
187        );
188        authenticate_api_key(&db.conn, &key).unwrap();
189        assert!(
190            list_api_keys(&db.conn, None).unwrap()[0]
191                .last_used_at
192                .is_some()
193        );
194    }
195
196    #[test]
197    fn revoke_is_scoped_and_users_take_their_keys_with_them() {
198        let (db, _tmp) = test_db();
199        let alice = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
200        let bob = create_user(&db.conn, "bob", "pw", Role::User).unwrap();
201        let (a, _) = create_api_key(&db.conn, alice, "a").unwrap();
202        let (_, bob_key) = create_api_key(&db.conn, bob, "b").unwrap();
203
204        assert!(!revoke_api_key(&db.conn, a, Some(bob)).unwrap());
205        assert!(revoke_api_key(&db.conn, a, Some(alice)).unwrap());
206        assert_eq!(list_api_keys(&db.conn, Some(alice)).unwrap().len(), 0);
207
208        delete_user(&db.conn, bob).unwrap();
209        assert!(authenticate_api_key(&db.conn, &bob_key).unwrap().is_none());
210        assert!(list_api_keys(&db.conn, None).unwrap().is_empty());
211    }
212}
koan_core/db/queries/api_keys.rs

koan_core/db/queries/
api_keys.rs
