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_cached("SELECT id, key_hash, last_used_at FROM api_keys")?;
105        let rows = stmt.query_map([], |row| {
106            Ok((
107                row.get::<_, i64>(0)?,
108                row.get::<_, String>(1)?,
109                row.get::<_, Option<i64>>(2)?,
110            ))
111        })?;
112        for row in rows {
113            let (id, hash, last_used) = row?;
114            if bool::from(hash.as_bytes().ct_eq(given.as_bytes())) {
115                found = Some((id, last_used));
116            }
117        }
118    }
119    let Some((id, last_used)) = found else {
120        return Ok(None);
121    };
122
123    // Decided here rather than in the UPDATE's WHERE: an UPDATE takes the
124    // write lock even when it matches nothing. A busy lock skips the stamp
125    // rather than holding up the request.
126    let now = auth::now_unix() as i64;
127    if last_used.is_none_or(|t| t <= now - TOUCH_INTERVAL_SECS)
128        && let Err(e) = crate::db::connection::without_waiting(conn, |conn| {
129            conn.execute(
130                "UPDATE api_keys SET last_used_at = ?1 WHERE id = ?2",
131                params![now, id],
132            )
133        })
134    {
135        log::debug!("api key {id}: last use not recorded: {e}");
136    }
137
138    let mut stmt = conn.prepare_cached(
139        "SELECT u.id, u.username, u.password_hash, u.role, u.created_at
140         FROM api_keys k JOIN users u ON u.id = k.user_id WHERE k.id = ?1",
141    )?;
142    let mut rows = stmt.query_map(params![id], |row| {
143        let role: String = row.get(3)?;
144        Ok(UserRow {
145            id: row.get(0)?,
146            username: row.get(1)?,
147            password_hash: row.get(2)?,
148            role: role.parse().unwrap_or(Role::Readonly),
149            created_at: row.get(4)?,
150        })
151    })?;
152    rows.next().transpose()
153}
154
155#[cfg(test)]
156mod tests {
157    use super::*;
158    use crate::db::connection::Database;
159    use crate::db::queries::auth::{create_user, delete_user, update_role};
160
161    fn test_db() -> (Database, tempfile::TempDir) {
162        let tmp = tempfile::TempDir::new().unwrap();
163        let db = Database::open(&tmp.path().join("test.db")).unwrap();
164        (db, tmp)
165    }
166
167    #[test]
168    fn key_signs_in_as_its_user_at_their_current_role() {
169        let (db, _tmp) = test_db();
170        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
171        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
172        assert_eq!(key.len(), 43);
173
174        let user = authenticate_api_key(&db.conn, &key).unwrap().unwrap();
175        assert_eq!((user.username.as_str(), user.role), ("alice", Role::User));
176
177        update_role(&db.conn, "alice", Role::Readonly).unwrap();
178        let user = authenticate_api_key(&db.conn, &key).unwrap().unwrap();
179        assert_eq!(user.role, Role::Readonly);
180
181        assert!(authenticate_api_key(&db.conn, "nope").unwrap().is_none());
182    }
183
184    #[test]
185    fn only_the_hash_is_stored_and_use_is_recorded() {
186        let (db, _tmp) = test_db();
187        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
188        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
189        let stored: String = db
190            .conn
191            .query_row("SELECT key_hash FROM api_keys", [], |r| r.get(0))
192            .unwrap();
193        assert_eq!(stored, auth::sha256_hex(&key));
194
195        assert!(
196            list_api_keys(&db.conn, None).unwrap()[0]
197                .last_used_at
198                .is_none()
199        );
200        authenticate_api_key(&db.conn, &key).unwrap();
201        assert!(
202            list_api_keys(&db.conn, None).unwrap()[0]
203                .last_used_at
204                .is_some()
205        );
206    }
207
208    /// Signing in must not wait for a scan or sync to finish writing: the
209    /// stamp is skipped while the lock is held, and not attempted while fresh.
210    #[test]
211    fn a_held_write_lock_does_not_hold_up_sign_in() {
212        let (db, tmp) = test_db();
213        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
214        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
215
216        let writer = Database::open_existing(&tmp.path().join("test.db")).unwrap();
217        writer.conn.execute_batch("BEGIN IMMEDIATE").unwrap();
218        let started = std::time::Instant::now();
219        assert!(authenticate_api_key(&db.conn, &key).unwrap().is_some());
220        assert!(started.elapsed() < std::time::Duration::from_secs(5));
221        writer.conn.execute_batch("ROLLBACK").unwrap();
222
223        authenticate_api_key(&db.conn, &key).unwrap();
224        writer.conn.execute_batch("BEGIN IMMEDIATE").unwrap();
225        let started = std::time::Instant::now();
226        assert!(authenticate_api_key(&db.conn, &key).unwrap().is_some());
227        assert!(started.elapsed() < std::time::Duration::from_secs(5));
228        writer.conn.execute_batch("ROLLBACK").unwrap();
229    }
230
231    #[test]
232    fn revoke_is_scoped_and_users_take_their_keys_with_them() {
233        let (db, _tmp) = test_db();
234        let alice = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
235        let bob = create_user(&db.conn, "bob", "pw", Role::User).unwrap();
236        let (a, _) = create_api_key(&db.conn, alice, "a").unwrap();
237        let (_, bob_key) = create_api_key(&db.conn, bob, "b").unwrap();
238
239        assert!(!revoke_api_key(&db.conn, a, Some(bob)).unwrap());
240        assert!(revoke_api_key(&db.conn, a, Some(alice)).unwrap());
241        assert_eq!(list_api_keys(&db.conn, Some(alice)).unwrap().len(), 0);
242
243        delete_user(&db.conn, bob).unwrap();
244        assert!(authenticate_api_key(&db.conn, &bob_key).unwrap().is_none());
245        assert!(list_api_keys(&db.conn, None).unwrap().is_empty());
246    }
247}
koan_core/db/queries/api_keys.rs

koan_core/db/queries/
api_keys.rs
