pylon_runtime/
api_key_backend.rs

1//! Persistent API key stores. SQLite + Postgres backends behind the
2//! [`pylon_auth::api_key::ApiKeyBackend`] trait.
3//!
4//! API keys are long-lived (months to never-expire), so unlike magic
5//! codes there's no aggressive expiry sweep — `last_used_at` plus
6//! the user-facing management UI handle the "remove unused" pattern.
7//!
8//! Storage shape mirrors the SQLite default — same column types both
9//! sides so a SQLite → Postgres migration is `pg_dump`-style copy
10//! without coercions. `secret_hash` is an Argon2 PHC string so old
11//! keys keep verifying after a hash-param bump.
12
13use std::sync::{Arc, Mutex};
14
15use pylon_auth::api_key::{ApiKey, ApiKeyBackend};
16use rusqlite::Connection;
17
18const SQLITE_TABLE: &str = "_pylon_api_keys";
19const PG_TABLE: &str = "_pylon_api_keys";
20
21// ---------------------------------------------------------------------------
22// SQLite backend
23// ---------------------------------------------------------------------------
24
25pub struct SqliteApiKeyBackend {
26    conn: Arc<Mutex<Connection>>,
27}
28
29impl SqliteApiKeyBackend {
30    pub fn open(path: &str) -> Result<Self, String> {
31        let conn = Connection::open(path).map_err(|e| format!("open: {e}"))?;
32        Self::from_connection(conn)
33    }
34    pub fn in_memory() -> Result<Self, String> {
35        let conn = Connection::open_in_memory().map_err(|e| format!("open: {e}"))?;
36        Self::from_connection(conn)
37    }
38    fn from_connection(conn: Connection) -> Result<Self, String> {
39        conn.execute_batch(&format!(
40            "CREATE TABLE IF NOT EXISTS {SQLITE_TABLE} (
41                id TEXT PRIMARY KEY,
42                user_id TEXT NOT NULL,
43                name TEXT NOT NULL DEFAULT '',
44                prefix TEXT NOT NULL DEFAULT '',
45                secret_hash TEXT NOT NULL,
46                scopes TEXT,
47                expires_at INTEGER,
48                last_used_at INTEGER,
49                created_at INTEGER NOT NULL
50            );
51            CREATE INDEX IF NOT EXISTS {SQLITE_TABLE}_user_idx ON {SQLITE_TABLE}(user_id);"
52        ))
53        .map_err(|e| format!("init schema: {e}"))?;
54        Ok(Self {
55            conn: Arc::new(Mutex::new(conn)),
56        })
57    }
58}
59
60impl ApiKeyBackend for SqliteApiKeyBackend {
61    fn put(&self, key: &ApiKey) {
62        if let Ok(guard) = self.conn.lock() {
63            let _ = guard.execute(
64                &format!(
65                    "INSERT INTO {SQLITE_TABLE}
66                       (id, user_id, name, prefix, secret_hash, scopes, expires_at, last_used_at, created_at)
67                     VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)
68                     ON CONFLICT(id) DO UPDATE SET
69                       name=excluded.name,
70                       prefix=excluded.prefix,
71                       secret_hash=excluded.secret_hash,
72                       scopes=excluded.scopes,
73                       expires_at=excluded.expires_at,
74                       last_used_at=excluded.last_used_at"
75                ),
76                rusqlite::params![
77                    key.id,
78                    key.user_id,
79                    key.name,
80                    key.prefix,
81                    key.secret_hash,
82                    key.scopes,
83                    key.expires_at.map(|v| v as i64),
84                    key.last_used_at.map(|v| v as i64),
85                    key.created_at as i64,
86                ],
87            );
88        }
89    }
90
91    fn get(&self, id: &str) -> Option<ApiKey> {
92        let guard = self.conn.lock().ok()?;
93        guard
94            .query_row(
95                &format!(
96                    "SELECT id, user_id, name, prefix, secret_hash, scopes, expires_at, last_used_at, created_at
97                     FROM {SQLITE_TABLE} WHERE id = ?1"
98                ),
99                rusqlite::params![id],
100                row_to_key,
101            )
102            .ok()
103    }
104
105    fn delete(&self, id: &str) -> bool {
106        let Ok(guard) = self.conn.lock() else {
107            return false;
108        };
109        guard
110            .execute(
111                &format!("DELETE FROM {SQLITE_TABLE} WHERE id = ?1"),
112                rusqlite::params![id],
113            )
114            .map(|n| n > 0)
115            .unwrap_or(false)
116    }
117
118    fn list_for_user(&self, user_id: &str) -> Vec<ApiKey> {
119        let Ok(guard) = self.conn.lock() else {
120            return vec![];
121        };
122        let mut stmt = match guard.prepare(&format!(
123            "SELECT id, user_id, name, prefix, secret_hash, scopes, expires_at, last_used_at, created_at
124             FROM {SQLITE_TABLE} WHERE user_id = ?1 ORDER BY created_at DESC"
125        )) {
126            Ok(s) => s,
127            Err(_) => return vec![],
128        };
129        let iter = match stmt.query_map(rusqlite::params![user_id], row_to_key) {
130            Ok(it) => it,
131            Err(_) => return vec![],
132        };
133        iter.filter_map(|r| r.ok()).collect()
134    }
135
136    fn touch(&self, id: &str, now: u64) {
137        if let Ok(guard) = self.conn.lock() {
138            let _ = guard.execute(
139                &format!("UPDATE {SQLITE_TABLE} SET last_used_at = ?2 WHERE id = ?1"),
140                rusqlite::params![id, now as i64],
141            );
142        }
143    }
144}
145
146fn row_to_key(row: &rusqlite::Row<'_>) -> rusqlite::Result<ApiKey> {
147    Ok(ApiKey {
148        id: row.get(0)?,
149        user_id: row.get(1)?,
150        name: row.get(2)?,
151        prefix: row.get(3)?,
152        secret_hash: row.get(4)?,
153        scopes: row.get(5)?,
154        expires_at: row.get::<_, Option<i64>>(6)?.map(|v| v as u64),
155        last_used_at: row.get::<_, Option<i64>>(7)?.map(|v| v as u64),
156        created_at: row.get::<_, i64>(8)? as u64,
157    })
158}
159
160// ---------------------------------------------------------------------------
161// Postgres backend
162// ---------------------------------------------------------------------------
163
164pub use pg::PostgresApiKeyBackend;
165
166mod pg {
167    use super::*;
168    use postgres::Client;
169
170    pub struct PostgresApiKeyBackend {
171        client: Mutex<Client>,
172    }
173
174    impl PostgresApiKeyBackend {
175        pub fn connect(url: &str) -> Result<Self, String> {
176            let mut client = pylon_storage::postgres::live::connect_pg(url)?;
177            client
178                .batch_execute(&format!(
179                    "CREATE TABLE IF NOT EXISTS {PG_TABLE} (
180                        id TEXT PRIMARY KEY,
181                        user_id TEXT NOT NULL,
182                        name TEXT NOT NULL DEFAULT '',
183                        prefix TEXT NOT NULL DEFAULT '',
184                        secret_hash TEXT NOT NULL,
185                        scopes TEXT,
186                        expires_at BIGINT,
187                        last_used_at BIGINT,
188                        created_at BIGINT NOT NULL
189                    );
190                    CREATE INDEX IF NOT EXISTS {PG_TABLE}_user_idx ON {PG_TABLE}(user_id);"
191                ))
192                .map_err(|e| format!("PG init schema: {e}"))?;
193            Ok(Self {
194                client: Mutex::new(client),
195            })
196        }
197    }
198
199    impl ApiKeyBackend for PostgresApiKeyBackend {
200        fn put(&self, key: &ApiKey) {
201            if let Ok(mut c) = self.client.lock() {
202                let _ = c.execute(
203                    &format!(
204                        "INSERT INTO {PG_TABLE}
205                           (id, user_id, name, prefix, secret_hash, scopes, expires_at, last_used_at, created_at)
206                         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
207                         ON CONFLICT (id) DO UPDATE SET
208                           name = EXCLUDED.name,
209                           prefix = EXCLUDED.prefix,
210                           secret_hash = EXCLUDED.secret_hash,
211                           scopes = EXCLUDED.scopes,
212                           expires_at = EXCLUDED.expires_at,
213                           last_used_at = EXCLUDED.last_used_at"
214                    ),
215                    &[
216                        &key.id,
217                        &key.user_id,
218                        &key.name,
219                        &key.prefix,
220                        &key.secret_hash,
221                        &key.scopes,
222                        &key.expires_at.map(|v| v as i64),
223                        &key.last_used_at.map(|v| v as i64),
224                        &(key.created_at as i64),
225                    ],
226                );
227            }
228        }
229
230        fn get(&self, id: &str) -> Option<ApiKey> {
231            let mut c = self.client.lock().ok()?;
232            let row = c
233                .query_opt(
234                    &format!(
235                        "SELECT id, user_id, name, prefix, secret_hash, scopes, expires_at, last_used_at, created_at
236                         FROM {PG_TABLE} WHERE id = $1"
237                    ),
238                    &[&id],
239                )
240                .ok()??;
241            Some(pg_row_to_key(&row))
242        }
243
244        fn delete(&self, id: &str) -> bool {
245            let mut c = match self.client.lock() {
246                Ok(c) => c,
247                Err(_) => return false,
248            };
249            c.execute(&format!("DELETE FROM {PG_TABLE} WHERE id = $1"), &[&id])
250                .map(|n| n > 0)
251                .unwrap_or(false)
252        }
253
254        fn list_for_user(&self, user_id: &str) -> Vec<ApiKey> {
255            let Ok(mut c) = self.client.lock() else {
256                return vec![];
257            };
258            let rows = match c.query(
259                &format!(
260                    "SELECT id, user_id, name, prefix, secret_hash, scopes, expires_at, last_used_at, created_at
261                     FROM {PG_TABLE} WHERE user_id = $1 ORDER BY created_at DESC"
262                ),
263                &[&user_id],
264            ) {
265                Ok(r) => r,
266                Err(_) => return vec![],
267            };
268            rows.iter().map(pg_row_to_key).collect()
269        }
270
271        fn touch(&self, id: &str, now: u64) {
272            if let Ok(mut c) = self.client.lock() {
273                let _ = c.execute(
274                    &format!("UPDATE {PG_TABLE} SET last_used_at = $2 WHERE id = $1"),
275                    &[&id, &(now as i64)],
276                );
277            }
278        }
279    }
280
281    fn pg_row_to_key(row: &postgres::Row) -> ApiKey {
282        ApiKey {
283            id: row.get(0),
284            user_id: row.get(1),
285            name: row.get(2),
286            prefix: row.get(3),
287            secret_hash: row.get(4),
288            scopes: row.get(5),
289            expires_at: row.get::<_, Option<i64>>(6).map(|v| v as u64),
290            last_used_at: row.get::<_, Option<i64>>(7).map(|v| v as u64),
291            created_at: row.get::<_, i64>(8) as u64,
292        }
293    }
294}
295
296#[cfg(test)]
297mod tests {
298    use super::*;
299
300    #[test]
301    fn sqlite_roundtrip() {
302        let backend = SqliteApiKeyBackend::in_memory().unwrap();
303        let key = ApiKey {
304            id: "key_test".into(),
305            user_id: "user_1".into(),
306            name: "ci".into(),
307            prefix: "pk.key_test.…".into(),
308            secret_hash: "$argon2id$...".into(),
309            scopes: Some("read".into()),
310            expires_at: Some(123),
311            last_used_at: None,
312            created_at: 100,
313        };
314        backend.put(&key);
315        let got = backend.get("key_test").unwrap();
316        assert_eq!(got.user_id, "user_1");
317        assert_eq!(got.name, "ci");
318        assert_eq!(got.scopes.as_deref(), Some("read"));
319        assert_eq!(got.expires_at, Some(123));
320        // touch updates last_used_at
321        backend.touch("key_test", 999);
322        assert_eq!(backend.get("key_test").unwrap().last_used_at, Some(999));
323        // delete removes
324        assert!(backend.delete("key_test"));
325        assert!(backend.get("key_test").is_none());
326    }
327
328    #[test]
329    fn sqlite_list_for_user_orders_newest_first() {
330        let backend = SqliteApiKeyBackend::in_memory().unwrap();
331        for (i, name) in ["a", "b", "c"].iter().enumerate() {
332            backend.put(&ApiKey {
333                id: format!("key_{i}"),
334                user_id: "u".into(),
335                name: name.to_string(),
336                prefix: "p".into(),
337                secret_hash: "h".into(),
338                scopes: None,
339                expires_at: None,
340                last_used_at: None,
341                created_at: 100 + i as u64,
342            });
343        }
344        let list = backend.list_for_user("u");
345        // Newest first → "c", "b", "a"
346        let names: Vec<_> = list.iter().map(|k| k.name.clone()).collect();
347        assert_eq!(names, vec!["c", "b", "a"]);
348    }
349}
pylon_runtime/api_key_backend.rs

pylon_runtime/
api_key_backend.rs
