1use crate::errors::{AuthError, Result};
5use crate::storage::{AuthStorage, SessionData};
6use crate::tokens::AuthToken;
7use async_trait::async_trait;
8use sqlx::PgPool;
9use sqlx::Row;
10pub struct PostgresStorage {
14 pool: PgPool,
15}
16
17impl PostgresStorage {
18 pub fn new(pool: PgPool) -> Self {
26 Self { pool }
27 }
28
29 pub async fn migrate(&self) -> Result<()> {
38 sqlx::query(
43 r#"
44 CREATE TABLE IF NOT EXISTS auth_tokens (
45 token_id VARCHAR(255) PRIMARY KEY,
46 user_id VARCHAR(255) NOT NULL,
47 access_token TEXT NOT NULL UNIQUE,
48 refresh_token TEXT,
49 token_type VARCHAR(50),
50 expires_at TIMESTAMPTZ NOT NULL,
51 scopes TEXT[],
52 issued_at TIMESTAMPTZ NOT NULL,
53 auth_method VARCHAR(100) NOT NULL,
54 subject VARCHAR(255),
55 issuer VARCHAR(255),
56 client_id VARCHAR(255),
57 metadata JSONB,
58 created_at TIMESTAMPTZ DEFAULT NOW()
59 )
60 "#,
61 )
62 .execute(&self.pool)
63 .await
64 .map_err(|e| {
65 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
66 "Migration failed (auth_tokens): {e}"
67 )))
68 })?;
69
70 for stmt in [
72 "CREATE INDEX IF NOT EXISTS idx_auth_tokens_user_id ON auth_tokens (user_id)",
73 "CREATE INDEX IF NOT EXISTS idx_auth_tokens_access_token ON auth_tokens (access_token)",
74 "CREATE INDEX IF NOT EXISTS idx_auth_tokens_expires_at ON auth_tokens (expires_at)",
75 ] {
76 sqlx::query(stmt).execute(&self.pool).await.map_err(|e| {
77 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
78 "Migration failed (index): {e}"
79 )))
80 })?;
81 }
82
83 sqlx::query(
85 r#"
86 CREATE TABLE IF NOT EXISTS sessions (
87 session_id VARCHAR(255) PRIMARY KEY,
88 user_id VARCHAR(255) NOT NULL,
89 data JSONB NOT NULL,
90 expires_at TIMESTAMPTZ,
91 created_at TIMESTAMPTZ DEFAULT NOW()
92 )
93 "#,
94 )
95 .execute(&self.pool)
96 .await
97 .map_err(|e| {
98 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
99 "Migration failed (sessions): {e}"
100 )))
101 })?;
102
103 for stmt in [
104 "CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id)",
105 "CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions (expires_at)",
106 ] {
107 sqlx::query(stmt).execute(&self.pool).await.map_err(|e| {
108 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
109 "Migration failed (index): {e}"
110 )))
111 })?;
112 }
113
114 sqlx::query(
116 r#"
117 CREATE TABLE IF NOT EXISTS kv_store (
118 key VARCHAR(512) PRIMARY KEY,
119 value BYTEA NOT NULL,
120 expires_at TIMESTAMPTZ,
121 created_at TIMESTAMPTZ DEFAULT NOW()
122 )
123 "#,
124 )
125 .execute(&self.pool)
126 .await
127 .map_err(|e| {
128 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
129 "Migration failed (kv_store): {e}"
130 )))
131 })?;
132
133 sqlx::query("CREATE INDEX IF NOT EXISTS idx_kv_store_expires_at ON kv_store (expires_at)")
134 .execute(&self.pool)
135 .await
136 .map_err(|e| {
137 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
138 "Migration failed (index idx_kv_store_expires_at): {e}"
139 )))
140 })?;
141
142 Ok(())
143 }
144}
145
146#[async_trait]
147impl AuthStorage for PostgresStorage {
148 async fn store_token(&self, token: &AuthToken) -> Result<()> {
149 sqlx::query(
150 r#"
151 INSERT INTO auth_tokens (
152 token_id, user_id, access_token, refresh_token, token_type,
153 expires_at, scopes, issued_at, auth_method, subject, issuer,
154 client_id, metadata
155 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
156 ON CONFLICT (token_id) DO UPDATE SET
157 access_token = EXCLUDED.access_token,
158 refresh_token = EXCLUDED.refresh_token,
159 expires_at = EXCLUDED.expires_at
160 "#,
161 )
162 .bind(&token.token_id)
163 .bind(&token.user_id)
164 .bind(&token.access_token)
165 .bind(&token.refresh_token)
166 .bind(&token.token_type)
167 .bind(token.expires_at)
168 .bind(&token.scopes)
169 .bind(token.issued_at)
170 .bind(&token.auth_method)
171 .bind(&token.subject)
172 .bind(&token.issuer)
173 .bind(&token.client_id)
174 .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
175 .execute(&self.pool)
176 .await
177 .map_err(|e| {
178 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
179 "Failed to store token: {}",
180 e
181 )))
182 })?;
183
184 Ok(())
185 }
186
187 async fn get_token(&self, token_id: &str) -> Result<Option<AuthToken>> {
189 let row =
190 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE token_id = $1"#)
191 .bind(token_id)
192 .fetch_optional(&self.pool)
193 .await
194 .map_err(|e| {
195 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
196 "Failed to fetch token: {}",
197 e
198 )))
199 })?;
200 Ok(row)
201 }
202
203 async fn get_token_by_access_token(&self, access_token: &str) -> Result<Option<AuthToken>> {
204 let row =
205 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE access_token = $1"#)
206 .bind(access_token)
207 .fetch_optional(&self.pool)
208 .await
209 .map_err(|e| {
210 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
211 "Failed to fetch token by access_token: {}",
212 e
213 )))
214 })?;
215 Ok(row)
216 }
217
218 async fn update_token(&self, token: &AuthToken) -> Result<()> {
219 sqlx::query(
220 r#"
221 UPDATE auth_tokens SET
222 access_token = $1,
223 refresh_token = $2,
224 token_type = $3,
225 expires_at = $4,
226 scopes = $5,
227 issued_at = $6,
228 auth_method = $7,
229 subject = $8,
230 issuer = $9,
231 client_id = $10,
232 metadata = $11
233 WHERE token_id = $12
234 "#,
235 )
236 .bind(&token.access_token)
237 .bind(&token.refresh_token)
238 .bind(&token.token_type)
239 .bind(token.expires_at)
240 .bind(&token.scopes)
241 .bind(token.issued_at)
242 .bind(&token.auth_method)
243 .bind(&token.subject)
244 .bind(&token.issuer)
245 .bind(&token.client_id)
246 .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
247 .bind(&token.token_id)
248 .execute(&self.pool)
249 .await
250 .map_err(|e| {
251 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
252 "Failed to update token: {}",
253 e
254 )))
255 })?;
256 Ok(())
257 }
258
259 async fn delete_token(&self, token_id: &str) -> Result<()> {
260 sqlx::query(r#"DELETE FROM auth_tokens WHERE token_id = $1"#)
261 .bind(token_id)
262 .execute(&self.pool)
263 .await
264 .map_err(|e| {
265 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
266 "Failed to delete token: {}",
267 e
268 )))
269 })?;
270 Ok(())
271 }
272
273 async fn list_user_tokens(&self, user_id: &str) -> Result<Vec<AuthToken>> {
274 let tokens =
275 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE user_id = $1"#)
276 .bind(user_id)
277 .fetch_all(&self.pool)
278 .await
279 .map_err(|e| {
280 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
281 "Failed to list user tokens: {}",
282 e
283 )))
284 })?;
285 Ok(tokens)
286 }
287
288 async fn store_session(
289 &self,
290 session_id: &str,
291 data: &crate::storage::core::SessionData,
292 ) -> Result<()> {
293 sqlx::query(
294 r#"
295 INSERT INTO sessions (session_id, user_id, data, expires_at)
296 VALUES ($1, $2, $3, $4)
297 ON CONFLICT (session_id) DO UPDATE SET
298 data = EXCLUDED.data,
299 expires_at = EXCLUDED.expires_at
300 "#,
301 )
302 .bind(session_id)
303 .bind(&data.user_id)
304 .bind(serde_json::to_value(data).unwrap_or_default())
305 .bind(data.expires_at)
306 .execute(&self.pool)
307 .await
308 .map_err(|e| {
309 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
310 "Failed to store session: {}",
311 e
312 )))
313 })?;
314 Ok(())
315 }
316
317 async fn get_session(
318 &self,
319 session_id: &str,
320 ) -> Result<Option<crate::storage::core::SessionData>> {
321 let row = sqlx::query(r#"SELECT data FROM sessions WHERE session_id = $1"#)
322 .bind(session_id)
323 .fetch_optional(&self.pool)
324 .await
325 .map_err(|e| {
326 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
327 "Failed to fetch session: {}",
328 e
329 )))
330 })?;
331 if let Some(row) = row {
332 let data: serde_json::Value = row.try_get("data").map_err(|e| {
333 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
334 "Failed to deserialize session data: {}",
335 e
336 )))
337 })?;
338 let session: crate::storage::core::SessionData =
339 serde_json::from_value(data).map_err(|e| {
340 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
341 "Failed to parse session data: {}",
342 e
343 )))
344 })?;
345 Ok(Some(session))
346 } else {
347 Ok(None)
348 }
349 }
350
351 async fn delete_session(&self, session_id: &str) -> Result<()> {
352 sqlx::query(r#"DELETE FROM sessions WHERE session_id = $1"#)
353 .bind(session_id)
354 .execute(&self.pool)
355 .await
356 .map_err(|e| {
357 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
358 "Failed to delete session: {}",
359 e
360 )))
361 })?;
362 Ok(())
363 }
364
365 async fn store_kv(
366 &self,
367 key: &str,
368 value: &[u8],
369 ttl: Option<std::time::Duration>,
370 ) -> Result<()> {
371 let expires_at = ttl.map(|d| {
372 chrono::Utc::now()
373 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
374 });
375 sqlx::query(
376 r#"
377 INSERT INTO kv_store (key, value, expires_at)
378 VALUES ($1, $2, $3)
379 ON CONFLICT (key) DO UPDATE SET
380 value = EXCLUDED.value,
381 expires_at = EXCLUDED.expires_at
382 "#,
383 )
384 .bind(key)
385 .bind(value)
386 .bind(expires_at)
387 .execute(&self.pool)
388 .await
389 .map_err(|e| {
390 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
391 "Failed to store kv: {}",
392 e
393 )))
394 })?;
395 Ok(())
396 }
397
398 async fn store_kv_if_absent(
399 &self,
400 key: &str,
401 value: &[u8],
402 ttl: Option<std::time::Duration>,
403 ) -> Result<bool> {
404 let expires_at = ttl.map(|d| {
405 chrono::Utc::now()
406 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
407 });
408 let result = sqlx::query(
410 r#"
411 INSERT INTO kv_store (key, value, expires_at)
412 VALUES ($1, $2, $3)
413 ON CONFLICT (key) DO UPDATE SET
414 value = EXCLUDED.value,
415 expires_at = EXCLUDED.expires_at
416 WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
417 "#,
418 )
419 .bind(key)
420 .bind(value)
421 .bind(expires_at)
422 .execute(&self.pool)
423 .await
424 .map_err(|e| {
425 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
426 "Failed to store kv if absent: {}",
427 e
428 )))
429 })?;
430 Ok(result.rows_affected() == 1)
431 }
432
433 async fn get_kv(&self, key: &str) -> Result<Option<Vec<u8>>> {
434 let row = sqlx::query(
435 r#"SELECT value FROM kv_store WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())"#
436 )
437 .bind(key)
438 .fetch_optional(&self.pool)
439 .await
440 .map_err(|e| {
441 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
442 "Failed to fetch kv: {}", e
443 )))
444 })?;
445 if let Some(row) = row {
446 let value: Vec<u8> = row.try_get("value").map_err(|e| {
447 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
448 "Failed to deserialize kv value: {}",
449 e
450 )))
451 })?;
452 Ok(Some(value))
453 } else {
454 Ok(None)
455 }
456 }
457
458 async fn get_kv_ttl(&self, key: &str) -> Result<Option<std::time::Duration>> {
459 let row = sqlx::query(
460 r#"SELECT EXTRACT(EPOCH FROM (expires_at - NOW()))::float8 AS seconds
461 FROM kv_store
462 WHERE key = $1 AND expires_at IS NOT NULL AND expires_at > NOW()"#,
463 )
464 .bind(key)
465 .fetch_optional(&self.pool)
466 .await
467 .map_err(|e| {
468 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
469 "Failed to fetch kv ttl: {}",
470 e
471 )))
472 })?;
473 Ok(row
474 .and_then(|row| row.try_get::<f64, _>("seconds").ok())
475 .filter(|seconds| *seconds > 0.0)
476 .map(std::time::Duration::from_secs_f64))
477 }
478
479 fn tracks_kv_ttl(&self) -> bool {
480 true
481 }
482
483 async fn delete_kv(&self, key: &str) -> Result<()> {
484 sqlx::query(r#"DELETE FROM kv_store WHERE key = $1"#)
485 .bind(key)
486 .execute(&self.pool)
487 .await
488 .map_err(|e| {
489 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
490 "Failed to delete kv: {}",
491 e
492 )))
493 })?;
494 Ok(())
495 }
496
497 async fn list_kv_keys(&self, prefix: &str) -> Result<Vec<String>> {
498 let rows = sqlx::query(
499 r#"
500 SELECT key
501 FROM kv_store
502 WHERE key LIKE $1 AND (expires_at IS NULL OR expires_at > NOW())
503 ORDER BY key
504 "#,
505 )
506 .bind(format!("{prefix}%"))
507 .fetch_all(&self.pool)
508 .await
509 .map_err(|e| {
510 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
511 "Failed to list kv keys: {}",
512 e
513 )))
514 })?;
515
516 rows.into_iter()
517 .map(|row| {
518 row.try_get("key").map_err(|e| {
519 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
520 "Failed to decode kv key: {}",
521 e
522 )))
523 })
524 })
525 .collect()
526 }
527
528 async fn cleanup_expired(&self) -> Result<()> {
529 sqlx::query("DELETE FROM auth_tokens WHERE expires_at < NOW()")
531 .execute(&self.pool)
532 .await
533 .map_err(|e| {
534 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
535 "Failed to cleanup expired tokens: {}",
536 e
537 )))
538 })?;
539
540 sqlx::query("DELETE FROM sessions WHERE expires_at IS NOT NULL AND expires_at < NOW()")
542 .execute(&self.pool)
543 .await
544 .map_err(|e| {
545 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
546 "Failed to cleanup expired sessions: {}",
547 e
548 )))
549 })?;
550
551 sqlx::query("DELETE FROM kv_store WHERE expires_at IS NOT NULL AND expires_at < NOW()")
553 .execute(&self.pool)
554 .await
555 .map_err(|e| {
556 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
557 "Failed to cleanup expired kv: {}",
558 e
559 )))
560 })?;
561
562 Ok(())
563 }
564
565 async fn list_user_sessions(&self, user_id: &str) -> Result<Vec<SessionData>> {
566 let rows = sqlx::query(
567 r#"
568 SELECT session_id, user_id, data, expires_at, created_at, last_activity, ip_address, user_agent
569 FROM sessions
570 WHERE user_id = $1 AND (expires_at IS NULL OR expires_at > NOW())
571 "#,
572 )
573 .bind(user_id)
574 .fetch_all(&self.pool)
575 .await
576 .map_err(|e| {
577 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
578 "Failed to list user sessions: {}",
579 e
580 )))
581 })?;
582
583 let sessions = rows
584 .into_iter()
585 .map(|row| SessionData {
586 session_id: row.try_get("session_id").unwrap_or_default(),
587 user_id: row.try_get("user_id").unwrap_or_default(),
588 created_at: row.try_get("created_at").unwrap_or_default(),
589 data: {
590 let json_value: serde_json::Value = row.try_get("data").unwrap_or_default();
591 if let serde_json::Value::Object(map) = json_value {
592 map.into_iter().collect()
593 } else {
594 std::collections::HashMap::new()
595 }
596 },
597 expires_at: row.try_get("expires_at").unwrap_or_default(),
598 last_activity: row.try_get("last_activity").unwrap_or_default(),
599 ip_address: row.try_get("ip_address").ok(),
600 user_agent: row.try_get("user_agent").ok(),
601 })
602 .collect();
603
604 Ok(sessions)
605 }
606
607 async fn count_active_sessions(&self) -> Result<u64> {
608 let row = sqlx::query(
609 "SELECT COUNT(*) as count FROM sessions WHERE expires_at IS NULL OR expires_at > NOW()",
610 )
611 .fetch_one(&self.pool)
612 .await
613 .map_err(|e| {
614 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
615 "Failed to count active sessions: {}",
616 e
617 )))
618 })?;
619
620 let count: i64 = row.try_get("count").map_err(|e| {
621 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
622 "Failed to parse session count: {}",
623 e
624 )))
625 })?;
626
627 Ok(count as u64)
628 }
629}