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, OptionalExtension, 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/// Make a key for `user_id` in place of any it already has named `name`: what
50/// a device signing in again is given, so a reinstall or a sign-out leaves no
51/// key behind that nothing holds any more.
52pub fn replace_api_key(
53    conn: &Connection,
54    user_id: i64,
55    name: &str,
56) -> Result<(i64, String), rusqlite::Error> {
57    let tx = conn.unchecked_transaction()?;
58    let replaced = tx.execute(
59        "DELETE FROM api_keys WHERE user_id = ?1 AND name = ?2",
60        params![user_id, name],
61    )?;
62    let made = create_api_key(&tx, user_id, name)?;
63    tx.commit()?;
64    if replaced > 0 {
65        auth::account_changed(user_id);
66    }
67    Ok(made)
68}
69
70/// Keys, oldest first — every user's, or one user's.
71pub fn list_api_keys(
72    conn: &Connection,
73    user_id: Option<i64>,
74) -> Result<Vec<ApiKeyRow>, rusqlite::Error> {
75    let mut stmt = conn.prepare(
76        "SELECT k.id, k.user_id, u.username, k.name, k.created_at, k.last_used_at
77         FROM api_keys k JOIN users u ON u.id = k.user_id
78         WHERE ?1 IS NULL OR k.user_id = ?1
79         ORDER BY k.id",
80    )?;
81    let rows = stmt.query_map(params![user_id], |row| {
82        Ok(ApiKeyRow {
83            id: row.get(0)?,
84            user_id: row.get(1)?,
85            username: row.get(2)?,
86            name: row.get(3)?,
87            created_at: row.get(4)?,
88            last_used_at: row.get(5)?,
89        })
90    })?;
91    rows.collect()
92}
93
94/// Revoke a key. With `user_id`, only that user's key can go. Returns whether
95/// one did.
96pub fn revoke_api_key(
97    conn: &Connection,
98    id: i64,
99    user_id: Option<i64>,
100) -> Result<bool, rusqlite::Error> {
101    let owner: Option<i64> = conn
102        .query_row(
103            "DELETE FROM api_keys WHERE id = ?1 AND (?2 IS NULL OR user_id = ?2) RETURNING user_id",
104            params![id, user_id],
105            |row| row.get(0),
106        )
107        .optional()?;
108    if let Some(owner) = owner {
109        auth::account_changed(owner);
110    }
111    Ok(owner.is_some())
112}
113
114/// Revoke the key `key` itself: for a client giving up a key it holds, signed
115/// in with that key. Returns whether one went.
116pub fn revoke_api_key_value(conn: &Connection, key: &str) -> Result<bool, rusqlite::Error> {
117    let owner: Option<i64> = conn
118        .query_row(
119            "DELETE FROM api_keys WHERE key_hash = ?1 RETURNING user_id",
120            params![auth::sha256_hex(key)],
121            |row| row.get(0),
122        )
123        .optional()?;
124    if let Some(owner) = owner {
125        auth::account_changed(owner);
126    }
127    Ok(owner.is_some())
128}
129
130/// The row id of the key `key`, if it is a live key: which of a user's keys a
131/// request signed in with.
132pub fn id_of(conn: &Connection, key: &str) -> Result<Option<i64>, rusqlite::Error> {
133    conn.query_row(
134        "SELECT id FROM api_keys WHERE key_hash = ?1",
135        params![auth::sha256_hex(key)],
136        |row| row.get(0),
137    )
138    .optional()
139}
140
141/// Revoke every key a user has. Returns how many went.
142pub fn revoke_user_api_keys(conn: &Connection, user_id: i64) -> Result<usize, rusqlite::Error> {
143    let n = conn.execute("DELETE FROM api_keys WHERE user_id = ?1", params![user_id])?;
144    if n > 0 {
145        auth::account_changed(user_id);
146    }
147    Ok(n)
148}
149
150/// The user a key belongs to, if it is a live key.
151///
152/// Every stored hash is compared, in constant time, rather than looking the
153/// hash up by index: an index lookup takes longer the more of the hash it
154/// matched. The table holds a handful of rows per user.
155pub fn authenticate_api_key(
156    conn: &Connection,
157    key: &str,
158) -> Result<Option<UserRow>, rusqlite::Error> {
159    let given = auth::sha256_hex(key);
160    let mut found = None;
161    {
162        let mut stmt = conn.prepare_cached("SELECT id, key_hash, last_used_at FROM api_keys")?;
163        let rows = stmt.query_map([], |row| {
164            Ok((
165                row.get::<_, i64>(0)?,
166                row.get::<_, String>(1)?,
167                row.get::<_, Option<i64>>(2)?,
168            ))
169        })?;
170        for row in rows {
171            let (id, hash, last_used) = row?;
172            if bool::from(hash.as_bytes().ct_eq(given.as_bytes())) {
173                found = Some((id, last_used));
174            }
175        }
176    }
177    let Some((id, last_used)) = found else {
178        return Ok(None);
179    };
180
181    // Decided here rather than in the UPDATE's WHERE: an UPDATE takes the
182    // write lock even when it matches nothing. A busy lock skips the stamp
183    // rather than holding up the request.
184    let now = auth::now_unix() as i64;
185    if last_used.is_none_or(|t| t <= now - TOUCH_INTERVAL_SECS)
186        && let Err(e) = crate::db::connection::without_waiting(conn, |conn| {
187            conn.execute(
188                "UPDATE api_keys SET last_used_at = ?1 WHERE id = ?2",
189                params![now, id],
190            )
191        })
192    {
193        log::debug!("api key {id}: last use not recorded: {e}");
194    }
195
196    let mut stmt = conn.prepare_cached(
197        "SELECT u.id, u.username, u.password_hash, u.role, u.created_at
198         FROM api_keys k JOIN users u ON u.id = k.user_id WHERE k.id = ?1",
199    )?;
200    let mut rows = stmt.query_map(params![id], |row| {
201        let role: String = row.get(3)?;
202        Ok(UserRow {
203            id: row.get(0)?,
204            username: row.get(1)?,
205            password_hash: row.get(2)?,
206            role: role.parse().unwrap_or(Role::Readonly),
207            created_at: row.get(4)?,
208        })
209    })?;
210    rows.next().transpose()
211}
212
213#[cfg(test)]
214mod tests {
215    use super::*;
216    use crate::db::connection::Database;
217    use crate::db::queries::auth::{create_user, delete_user, update_role};
218
219    fn test_db() -> (Database, tempfile::TempDir) {
220        let tmp = tempfile::TempDir::new().unwrap();
221        let db = Database::open(&tmp.path().join("test.db")).unwrap();
222        (db, tmp)
223    }
224
225    #[test]
226    fn key_signs_in_as_its_user_at_their_current_role() {
227        let (db, _tmp) = test_db();
228        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
229        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
230        assert_eq!(key.len(), 43);
231
232        let user = authenticate_api_key(&db.conn, &key).unwrap().unwrap();
233        assert_eq!((user.username.as_str(), user.role), ("alice", Role::User));
234
235        update_role(&db.conn, "alice", Role::Readonly).unwrap();
236        let user = authenticate_api_key(&db.conn, &key).unwrap().unwrap();
237        assert_eq!(user.role, Role::Readonly);
238
239        assert!(authenticate_api_key(&db.conn, "nope").unwrap().is_none());
240    }
241
242    #[test]
243    fn only_the_hash_is_stored_and_use_is_recorded() {
244        let (db, _tmp) = test_db();
245        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
246        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
247        let stored: String = db
248            .conn
249            .query_row("SELECT key_hash FROM api_keys", [], |r| r.get(0))
250            .unwrap();
251        assert_eq!(stored, auth::sha256_hex(&key));
252
253        assert!(
254            list_api_keys(&db.conn, None).unwrap()[0]
255                .last_used_at
256                .is_none()
257        );
258        authenticate_api_key(&db.conn, &key).unwrap();
259        assert!(
260            list_api_keys(&db.conn, None).unwrap()[0]
261                .last_used_at
262                .is_some()
263        );
264    }
265
266    /// Signing in must not wait for a scan or sync to finish writing: the
267    /// stamp is skipped while the lock is held, and not attempted while fresh.
268    #[test]
269    fn a_held_write_lock_does_not_hold_up_sign_in() {
270        let (db, tmp) = test_db();
271        let uid = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
272        let (_, key) = create_api_key(&db.conn, uid, "phone").unwrap();
273
274        let writer = Database::open_existing(&tmp.path().join("test.db")).unwrap();
275        writer.conn.execute_batch("BEGIN IMMEDIATE").unwrap();
276        let started = std::time::Instant::now();
277        assert!(authenticate_api_key(&db.conn, &key).unwrap().is_some());
278        assert!(started.elapsed() < std::time::Duration::from_secs(5));
279        writer.conn.execute_batch("ROLLBACK").unwrap();
280
281        authenticate_api_key(&db.conn, &key).unwrap();
282        writer.conn.execute_batch("BEGIN IMMEDIATE").unwrap();
283        let started = std::time::Instant::now();
284        assert!(authenticate_api_key(&db.conn, &key).unwrap().is_some());
285        assert!(started.elapsed() < std::time::Duration::from_secs(5));
286        writer.conn.execute_batch("ROLLBACK").unwrap();
287    }
288
289    #[test]
290    fn revoke_is_scoped_and_users_take_their_keys_with_them() {
291        let (db, _tmp) = test_db();
292        let alice = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
293        let bob = create_user(&db.conn, "bob", "pw", Role::User).unwrap();
294        let (a, _) = create_api_key(&db.conn, alice, "a").unwrap();
295        let (_, bob_key) = create_api_key(&db.conn, bob, "b").unwrap();
296
297        assert!(!revoke_api_key(&db.conn, a, Some(bob)).unwrap());
298        assert!(revoke_api_key(&db.conn, a, Some(alice)).unwrap());
299        assert_eq!(list_api_keys(&db.conn, Some(alice)).unwrap().len(), 0);
300
301        delete_user(&db.conn, bob).unwrap();
302        assert!(authenticate_api_key(&db.conn, &bob_key).unwrap().is_none());
303        assert!(list_api_keys(&db.conn, None).unwrap().is_empty());
304    }
305
306    #[test]
307    fn replacing_a_devices_key_ends_what_the_old_one_signed() {
308        use std::future::Future;
309        use std::task::{Context, Waker};
310        let changed_since = |user_id, mark| {
311            let fut = std::pin::pin!(auth::account_changed_since(user_id, mark));
312            fut.poll(&mut Context::from_waker(Waker::noop())).is_ready()
313        };
314        let (db, _tmp) = test_db();
315        let alice = create_user(&db.conn, "alice", "pw", Role::User).unwrap();
316
317        let (_, old) = replace_api_key(&db.conn, alice, "phone").unwrap();
318        let mark = auth::account_mark();
319        let (_, new) = replace_api_key(&db.conn, alice, "phone").unwrap();
320        assert!(changed_since(alice, mark));
321        assert!(authenticate_api_key(&db.conn, &old).unwrap().is_none());
322        assert!(authenticate_api_key(&db.conn, &new).unwrap().is_some());
323    }
324}
325
326/// A device's public key, as the account's devices are sent it.
327#[derive(Debug, Clone, PartialEq, Eq)]
328pub struct DeviceKey {
329    pub device: String,
330    /// Base64 of the device's 32-byte Ed25519 public key.
331    pub key: String,
332    /// The account it belongs to, for a device shared with `username`;
333    /// `None` for its own.
334    pub owner: Option<String>,
335}
336
337/// Record that the API key `raw_key` signed in `device`, which proves itself
338/// with `public_key`. Whether it was kept.
339///
340/// A key row is bound to the first device it records, and a device id to the
341/// one standing key row of its account that claimed it. Otherwise a thief
342/// holding one device's key could link as another device's id and have its
343/// own public key published in that device's place. A device signing in again
344/// is given a new key in place of its old one (`replace_api_key`), so the
345/// rightful claim is never refused; a new keypair on the same key replaces
346/// the old public key.
347pub fn set_device_key(
348    conn: &Connection,
349    raw_key: &str,
350    device: &str,
351    public_key: &str,
352) -> rusqlite::Result<bool> {
353    let changed = conn.execute(
354        "UPDATE api_keys SET device = ?1, device_key = ?2
355          WHERE key_hash = ?3
356            AND (device IS NULL OR device = ?1)
357            AND NOT EXISTS (SELECT 1 FROM api_keys other
358                             WHERE other.user_id = api_keys.user_id
359                               AND other.device = ?1
360                               AND other.id != api_keys.id)",
361        params![device, public_key, auth::sha256_hex(raw_key)],
362    )?;
363    Ok(changed > 0)
364}
365
366/// The public keys `username`'s devices prove themselves with, and those of
367/// the devices other accounts share with `username`: the newest key each
368/// device registered under a key still standing.
369pub fn device_keys(conn: &Connection, username: &str) -> rusqlite::Result<Vec<DeviceKey>> {
370    let mut stmt = conn.prepare_cached(
371        "SELECT k.device, k.device_key, u.username
372           FROM api_keys k JOIN users u ON u.id = k.user_id
373          WHERE k.device IS NOT NULL AND k.device_key IS NOT NULL
374            AND (u.username = ?1
375                 OR EXISTS (SELECT 1 FROM link_grants g
376                             WHERE g.grantee = ?1 AND g.owner = u.username
377                               AND g.device = k.device))
378          ORDER BY k.created_at, k.id",
379    )?;
380    let rows = stmt.query_map([username], |r| {
381        Ok((
382            r.get::<_, String>(0)?,
383            r.get::<_, String>(1)?,
384            r.get::<_, String>(2)?,
385        ))
386    })?;
387    // Oldest first, so a later key for the same device replaces it.
388    let mut keys: Vec<DeviceKey> = Vec::new();
389    for row in rows {
390        let (device, key, owner) = row?;
391        let owner = (owner != username).then_some(owner);
392        keys.retain(|k| !(k.device == device && k.owner == owner));
393        keys.push(DeviceKey { device, key, owner });
394    }
395    Ok(keys)
396}
397
398#[cfg(test)]
399mod device_key_tests {
400    use super::*;
401
402    fn db() -> Connection {
403        let conn = Connection::open_in_memory().unwrap();
404        crate::db::schema::create_tables(&conn).unwrap();
405        for name in ["jo", "kim"] {
406            crate::db::queries::auth::create_user(&conn, name, "pw", auth::Role::User).unwrap();
407        }
408        conn
409    }
410
411    fn user(conn: &Connection, name: &str) -> i64 {
412        crate::db::queries::auth::get_user_by_username(conn, name)
413            .unwrap()
414            .unwrap()
415            .id
416    }
417
418    #[test]
419    fn a_key_records_its_device_and_is_published_until_revoked() {
420        let conn = db();
421        let jo = user(&conn, "jo");
422        let (_, phone) = create_api_key(&conn, jo, "phone").unwrap();
423        let (mac_id, mac) = create_api_key(&conn, jo, "mac").unwrap();
424        assert!(set_device_key(&conn, &phone, "dev-phone", "PHONE1").unwrap());
425        assert!(set_device_key(&conn, &mac, "dev-mac", "MAC").unwrap());
426        assert!(!set_device_key(&conn, "not a key", "dev-x", "X").unwrap());
427        // Signing in again on the phone: its key replaced, a new keypair.
428        let (_, phone2) = replace_api_key(&conn, jo, "phone").unwrap();
429        assert!(set_device_key(&conn, &phone2, "dev-phone", "PHONE2").unwrap());
430
431        let keys = device_keys(&conn, "jo").unwrap();
432        let of = |d: &str| keys.iter().find(|k| k.device == d).map(|k| k.key.as_str());
433        assert_eq!(of("dev-phone"), Some("PHONE2"));
434        assert_eq!(of("dev-mac"), Some("MAC"));
435        assert!(keys.iter().all(|k| k.owner.is_none()));
436        assert!(device_keys(&conn, "kim").unwrap().is_empty());
437
438        revoke_api_key(&conn, mac_id, None).unwrap();
439        let keys = device_keys(&conn, "jo").unwrap();
440        assert_eq!(keys.len(), 1, "the Mac's key went with its API key");
441    }
442
443    /// The phone's key, stolen, cannot take the Mac's place: a device id
444    /// belongs to the key row that claimed it, and a key row to its device.
445    #[test]
446    fn a_key_cannot_claim_another_devices_id() {
447        let conn = db();
448        let jo = user(&conn, "jo");
449        let (_, phone) = create_api_key(&conn, jo, "phone").unwrap();
450        let (_, mac) = create_api_key(&conn, jo, "mac").unwrap();
451        assert!(set_device_key(&conn, &mac, "dev-mac", "MAC").unwrap());
452        assert!(set_device_key(&conn, &phone, "dev-phone", "PHONE").unwrap());
453        assert!(!set_device_key(&conn, &phone, "dev-mac", "THIEF").unwrap());
454        assert!(!set_device_key(&conn, &phone, "dev-new", "THIEF").unwrap());
455        // Its own id, with a new keypair, is still its to change.
456        assert!(set_device_key(&conn, &phone, "dev-phone", "PHONE2").unwrap());
457        let keys = device_keys(&conn, "jo").unwrap();
458        let of = |d: &str| keys.iter().find(|k| k.device == d).map(|k| k.key.as_str());
459        assert_eq!(of("dev-mac"), Some("MAC"));
460        assert_eq!(of("dev-phone"), Some("PHONE2"));
461        assert_eq!(keys.len(), 2);
462    }
463
464    #[test]
465    fn a_shared_device_is_published_to_its_grantee_with_its_owner() {
466        let conn = db();
467        let jo = user(&conn, "jo");
468        let (_, phone) = create_api_key(&conn, jo, "phone").unwrap();
469        let (_, mac) = create_api_key(&conn, jo, "mac").unwrap();
470        set_device_key(&conn, &phone, "dev-phone", "PHONE").unwrap();
471        set_device_key(&conn, &mac, "dev-mac", "MAC").unwrap();
472        conn.execute(
473            "INSERT INTO link_grants (device, owner, grantee, created_at)
474             VALUES ('dev-phone', 'jo', 'kim', 0)",
475            [],
476        )
477        .unwrap();
478        assert_eq!(
479            device_keys(&conn, "kim").unwrap(),
480            vec![DeviceKey {
481                device: "dev-phone".into(),
482                key: "PHONE".into(),
483                owner: Some("jo".into()),
484            }]
485        );
486    }
487}
koan_core/db/queries/api_keys.rs

koan_core/db/queries/
api_keys.rs
