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

koan_core/db/queries/
api_keys.rs
