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
17const MIGRATION_LOCK_KEY: i64 = 0x6175_7468_666d_776b;
19
20impl PostgresStorage {
21 pub fn new(pool: PgPool) -> Self {
29 Self { pool }
30 }
31
32 fn profile_json(token: &AuthToken) -> Option<serde_json::Value> {
34 token
35 .user_profile
36 .as_ref()
37 .and_then(|profile| serde_json::to_value(profile).ok())
38 }
39
40 pub async fn migrate(&self) -> Result<()> {
49 let mut tx = self.pool.begin().await.map_err(|e| {
56 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
57 "Migration failed (begin): {e}"
58 )))
59 })?;
60 sqlx::query("SELECT pg_advisory_xact_lock($1)")
61 .bind(MIGRATION_LOCK_KEY)
62 .execute(&mut *tx)
63 .await
64 .map_err(|e| {
65 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
66 "Migration failed (lock): {e}"
67 )))
68 })?;
69
70 sqlx::query(
75 r#"
76 CREATE TABLE IF NOT EXISTS auth_tokens (
77 token_id VARCHAR(255) PRIMARY KEY,
78 user_id VARCHAR(255) NOT NULL,
79 access_token TEXT NOT NULL UNIQUE,
80 refresh_token TEXT,
81 token_type VARCHAR(50),
82 expires_at TIMESTAMPTZ NOT NULL,
83 scopes TEXT[],
84 issued_at TIMESTAMPTZ NOT NULL,
85 auth_method VARCHAR(100) NOT NULL,
86 subject VARCHAR(255),
87 issuer VARCHAR(255),
88 client_id VARCHAR(255),
89 metadata JSONB,
90 created_at TIMESTAMPTZ DEFAULT NOW()
91 )
92 "#,
93 )
94 .execute(&mut *tx)
95 .await
96 .map_err(|e| {
97 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
98 "Migration failed (auth_tokens): {e}"
99 )))
100 })?;
101
102 for stmt in [
104 "CREATE INDEX IF NOT EXISTS idx_auth_tokens_user_id ON auth_tokens (user_id)",
105 "CREATE INDEX IF NOT EXISTS idx_auth_tokens_access_token ON auth_tokens (access_token)",
106 "CREATE INDEX IF NOT EXISTS idx_auth_tokens_expires_at ON auth_tokens (expires_at)",
107 ] {
108 sqlx::query(stmt).execute(&mut *tx).await.map_err(|e| {
109 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
110 "Migration failed (index): {e}"
111 )))
112 })?;
113 }
114
115 for stmt in [
118 "ALTER TABLE auth_tokens ADD COLUMN IF NOT EXISTS user_profile JSONB",
119 "ALTER TABLE auth_tokens ADD COLUMN IF NOT EXISTS permissions TEXT[] NOT NULL DEFAULT '{}'",
120 "ALTER TABLE auth_tokens ADD COLUMN IF NOT EXISTS roles TEXT[] NOT NULL DEFAULT '{}'",
121 ] {
122 sqlx::query(stmt).execute(&mut *tx).await.map_err(|e| {
123 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
124 "Migration failed (auth_tokens column): {e}"
125 )))
126 })?;
127 }
128
129 sqlx::query(
131 r#"
132 CREATE TABLE IF NOT EXISTS sessions (
133 session_id VARCHAR(255) PRIMARY KEY,
134 user_id VARCHAR(255) NOT NULL,
135 data JSONB NOT NULL,
136 expires_at TIMESTAMPTZ,
137 created_at TIMESTAMPTZ DEFAULT NOW()
138 )
139 "#,
140 )
141 .execute(&mut *tx)
142 .await
143 .map_err(|e| {
144 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
145 "Migration failed (sessions): {e}"
146 )))
147 })?;
148
149 for stmt in [
150 "CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id)",
151 "CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions (expires_at)",
152 ] {
153 sqlx::query(stmt).execute(&mut *tx).await.map_err(|e| {
154 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
155 "Migration failed (index): {e}"
156 )))
157 })?;
158 }
159
160 sqlx::query(
162 r#"
163 CREATE TABLE IF NOT EXISTS kv_store (
164 key VARCHAR(512) PRIMARY KEY,
165 value BYTEA NOT NULL,
166 expires_at TIMESTAMPTZ,
167 created_at TIMESTAMPTZ DEFAULT NOW()
168 )
169 "#,
170 )
171 .execute(&mut *tx)
172 .await
173 .map_err(|e| {
174 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
175 "Migration failed (kv_store): {e}"
176 )))
177 })?;
178
179 sqlx::query("CREATE INDEX IF NOT EXISTS idx_kv_store_expires_at ON kv_store (expires_at)")
180 .execute(&mut *tx)
181 .await
182 .map_err(|e| {
183 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
184 "Migration failed (index idx_kv_store_expires_at): {e}"
185 )))
186 })?;
187
188 tx.commit().await.map_err(|e| {
189 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
190 "Migration failed (commit): {e}"
191 )))
192 })?;
193
194 Ok(())
195 }
196}
197
198#[async_trait]
199impl AuthStorage for PostgresStorage {
200 async fn store_token(&self, token: &AuthToken) -> Result<()> {
201 sqlx::query(
202 r#"
203 INSERT INTO auth_tokens (
204 token_id, user_id, access_token, refresh_token, token_type,
205 expires_at, scopes, issued_at, auth_method, subject, issuer,
206 client_id, metadata, user_profile, permissions, roles
207 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
208 ON CONFLICT (token_id) DO UPDATE SET
209 access_token = EXCLUDED.access_token,
210 refresh_token = EXCLUDED.refresh_token,
211 expires_at = EXCLUDED.expires_at,
212 user_profile = EXCLUDED.user_profile,
213 permissions = EXCLUDED.permissions,
214 roles = EXCLUDED.roles
215 "#,
216 )
217 .bind(&token.token_id)
218 .bind(&token.user_id)
219 .bind(&token.access_token)
220 .bind(&token.refresh_token)
221 .bind(&token.token_type)
222 .bind(token.expires_at)
223 .bind(&token.scopes)
224 .bind(token.issued_at)
225 .bind(&token.auth_method)
226 .bind(&token.subject)
227 .bind(&token.issuer)
228 .bind(&token.client_id)
229 .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
230 .bind(Self::profile_json(token))
231 .bind(&token.permissions)
232 .bind(&token.roles)
233 .execute(&self.pool)
234 .await
235 .map_err(|e| {
236 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
237 "Failed to store token: {}",
238 e
239 )))
240 })?;
241
242 Ok(())
243 }
244
245 async fn get_token(&self, token_id: &str) -> Result<Option<AuthToken>> {
247 let row =
248 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE token_id = $1"#)
249 .bind(token_id)
250 .fetch_optional(&self.pool)
251 .await
252 .map_err(|e| {
253 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
254 "Failed to fetch token: {}",
255 e
256 )))
257 })?;
258 Ok(row)
259 }
260
261 async fn get_token_by_access_token(&self, access_token: &str) -> Result<Option<AuthToken>> {
262 let row =
263 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE access_token = $1"#)
264 .bind(access_token)
265 .fetch_optional(&self.pool)
266 .await
267 .map_err(|e| {
268 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
269 "Failed to fetch token by access_token: {}",
270 e
271 )))
272 })?;
273 Ok(row)
274 }
275
276 async fn update_token(&self, token: &AuthToken) -> Result<()> {
277 sqlx::query(
278 r#"
279 UPDATE auth_tokens SET
280 access_token = $1,
281 refresh_token = $2,
282 token_type = $3,
283 expires_at = $4,
284 scopes = $5,
285 issued_at = $6,
286 auth_method = $7,
287 subject = $8,
288 issuer = $9,
289 client_id = $10,
290 metadata = $11,
291 user_profile = $12,
292 permissions = $13,
293 roles = $14
294 WHERE token_id = $15
295 "#,
296 )
297 .bind(&token.access_token)
298 .bind(&token.refresh_token)
299 .bind(&token.token_type)
300 .bind(token.expires_at)
301 .bind(&token.scopes)
302 .bind(token.issued_at)
303 .bind(&token.auth_method)
304 .bind(&token.subject)
305 .bind(&token.issuer)
306 .bind(&token.client_id)
307 .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
308 .bind(Self::profile_json(token))
309 .bind(&token.permissions)
310 .bind(&token.roles)
311 .bind(&token.token_id)
312 .execute(&self.pool)
313 .await
314 .map_err(|e| {
315 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
316 "Failed to update token: {}",
317 e
318 )))
319 })?;
320 Ok(())
321 }
322
323 async fn delete_token(&self, token_id: &str) -> Result<()> {
324 sqlx::query(r#"DELETE FROM auth_tokens WHERE token_id = $1"#)
325 .bind(token_id)
326 .execute(&self.pool)
327 .await
328 .map_err(|e| {
329 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
330 "Failed to delete token: {}",
331 e
332 )))
333 })?;
334 Ok(())
335 }
336
337 async fn list_user_tokens(&self, user_id: &str) -> Result<Vec<AuthToken>> {
338 let tokens =
339 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE user_id = $1"#)
340 .bind(user_id)
341 .fetch_all(&self.pool)
342 .await
343 .map_err(|e| {
344 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
345 "Failed to list user tokens: {}",
346 e
347 )))
348 })?;
349 Ok(tokens)
350 }
351
352 async fn store_session(
353 &self,
354 session_id: &str,
355 data: &crate::storage::core::SessionData,
356 ) -> Result<()> {
357 sqlx::query(
358 r#"
359 INSERT INTO sessions (session_id, user_id, data, expires_at)
360 VALUES ($1, $2, $3, $4)
361 ON CONFLICT (session_id) DO UPDATE SET
362 data = EXCLUDED.data,
363 expires_at = EXCLUDED.expires_at
364 "#,
365 )
366 .bind(session_id)
367 .bind(&data.user_id)
368 .bind(serde_json::to_value(data).unwrap_or_default())
369 .bind(data.expires_at)
370 .execute(&self.pool)
371 .await
372 .map_err(|e| {
373 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
374 "Failed to store session: {}",
375 e
376 )))
377 })?;
378 Ok(())
379 }
380
381 async fn get_session(
382 &self,
383 session_id: &str,
384 ) -> Result<Option<crate::storage::core::SessionData>> {
385 let row = sqlx::query(r#"SELECT data FROM sessions WHERE session_id = $1"#)
386 .bind(session_id)
387 .fetch_optional(&self.pool)
388 .await
389 .map_err(|e| {
390 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
391 "Failed to fetch session: {}",
392 e
393 )))
394 })?;
395 if let Some(row) = row {
396 let data: serde_json::Value = row.try_get("data").map_err(|e| {
397 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
398 "Failed to deserialize session data: {}",
399 e
400 )))
401 })?;
402 let session: crate::storage::core::SessionData =
403 serde_json::from_value(data).map_err(|e| {
404 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
405 "Failed to parse session data: {}",
406 e
407 )))
408 })?;
409 Ok(Some(session))
410 } else {
411 Ok(None)
412 }
413 }
414
415 async fn delete_session(&self, session_id: &str) -> Result<()> {
416 sqlx::query(r#"DELETE FROM sessions WHERE session_id = $1"#)
417 .bind(session_id)
418 .execute(&self.pool)
419 .await
420 .map_err(|e| {
421 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
422 "Failed to delete session: {}",
423 e
424 )))
425 })?;
426 Ok(())
427 }
428
429 async fn store_kv(
430 &self,
431 key: &str,
432 value: &[u8],
433 ttl: Option<std::time::Duration>,
434 ) -> Result<()> {
435 let expires_at = ttl.map(|d| {
436 chrono::Utc::now()
437 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
438 });
439 sqlx::query(
440 r#"
441 INSERT INTO kv_store (key, value, expires_at)
442 VALUES ($1, $2, $3)
443 ON CONFLICT (key) DO UPDATE SET
444 value = EXCLUDED.value,
445 expires_at = EXCLUDED.expires_at
446 "#,
447 )
448 .bind(key)
449 .bind(value)
450 .bind(expires_at)
451 .execute(&self.pool)
452 .await
453 .map_err(|e| {
454 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
455 "Failed to store kv: {}",
456 e
457 )))
458 })?;
459 Ok(())
460 }
461
462 async fn store_kv_if_absent(
463 &self,
464 key: &str,
465 value: &[u8],
466 ttl: Option<std::time::Duration>,
467 ) -> Result<bool> {
468 let expires_at = ttl.map(|d| {
469 chrono::Utc::now()
470 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
471 });
472 let result = sqlx::query(
474 r#"
475 INSERT INTO kv_store (key, value, expires_at)
476 VALUES ($1, $2, $3)
477 ON CONFLICT (key) DO UPDATE SET
478 value = EXCLUDED.value,
479 expires_at = EXCLUDED.expires_at
480 WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
481 "#,
482 )
483 .bind(key)
484 .bind(value)
485 .bind(expires_at)
486 .execute(&self.pool)
487 .await
488 .map_err(|e| {
489 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
490 "Failed to store kv if absent: {}",
491 e
492 )))
493 })?;
494 Ok(result.rows_affected() == 1)
495 }
496
497 async fn swap_kv(
498 &self,
499 remove: &[String],
500 key: &str,
501 value: &[u8],
502 ttl: Option<std::time::Duration>,
503 ) -> Result<bool> {
504 if remove.is_empty() {
505 return Ok(false);
506 }
507 let expires_at = ttl.map(|d| {
508 chrono::Utc::now()
509 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
510 });
511 let fail = |what: &str, e: sqlx::Error| {
512 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
513 "Failed to swap kv ({what}): {e}"
514 )))
515 };
516 let mut tx = self.pool.begin().await.map_err(|e| fail("begin", e))?;
517
518 let removed = sqlx::query(
521 r#"
522 DELETE FROM kv_store
523 WHERE key = ANY($1) AND (expires_at IS NULL OR expires_at > NOW())
524 "#,
525 )
526 .bind(remove)
527 .execute(&mut *tx)
528 .await
529 .map_err(|e| fail("remove", e))?
530 .rows_affected();
531 if removed == 0 {
532 tx.rollback().await.map_err(|e| fail("rollback", e))?;
533 return Ok(false);
534 }
535
536 let inserted = sqlx::query(
538 r#"
539 INSERT INTO kv_store (key, value, expires_at)
540 VALUES ($1, $2, $3)
541 ON CONFLICT (key) DO UPDATE SET
542 value = EXCLUDED.value,
543 expires_at = EXCLUDED.expires_at
544 WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
545 "#,
546 )
547 .bind(key)
548 .bind(value)
549 .bind(expires_at)
550 .execute(&mut *tx)
551 .await
552 .map_err(|e| fail("store", e))?
553 .rows_affected();
554 if inserted != 1 {
555 tx.rollback().await.map_err(|e| fail("rollback", e))?;
556 return Ok(false);
557 }
558 tx.commit().await.map_err(|e| fail("commit", e))?;
559 Ok(true)
560 }
561
562 fn supports_atomic_swap(&self) -> bool {
563 true
564 }
565
566 async fn get_kv(&self, key: &str) -> Result<Option<Vec<u8>>> {
567 let row = sqlx::query(
568 r#"SELECT value FROM kv_store WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())"#
569 )
570 .bind(key)
571 .fetch_optional(&self.pool)
572 .await
573 .map_err(|e| {
574 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
575 "Failed to fetch kv: {}", e
576 )))
577 })?;
578 if let Some(row) = row {
579 let value: Vec<u8> = row.try_get("value").map_err(|e| {
580 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
581 "Failed to deserialize kv value: {}",
582 e
583 )))
584 })?;
585 Ok(Some(value))
586 } else {
587 Ok(None)
588 }
589 }
590
591 async fn get_kv_ttl(&self, key: &str) -> Result<Option<std::time::Duration>> {
592 let row = sqlx::query(
593 r#"SELECT EXTRACT(EPOCH FROM (expires_at - NOW()))::float8 AS seconds
594 FROM kv_store
595 WHERE key = $1 AND expires_at IS NOT NULL AND expires_at > NOW()"#,
596 )
597 .bind(key)
598 .fetch_optional(&self.pool)
599 .await
600 .map_err(|e| {
601 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
602 "Failed to fetch kv ttl: {}",
603 e
604 )))
605 })?;
606 Ok(row
607 .and_then(|row| row.try_get::<f64, _>("seconds").ok())
608 .filter(|seconds| *seconds > 0.0)
609 .map(std::time::Duration::from_secs_f64))
610 }
611
612 fn tracks_kv_ttl(&self) -> bool {
613 true
614 }
615
616 async fn delete_kv(&self, key: &str) -> Result<()> {
617 sqlx::query(r#"DELETE FROM kv_store WHERE key = $1"#)
618 .bind(key)
619 .execute(&self.pool)
620 .await
621 .map_err(|e| {
622 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
623 "Failed to delete kv: {}",
624 e
625 )))
626 })?;
627 Ok(())
628 }
629
630 async fn list_kv_keys(&self, prefix: &str) -> Result<Vec<String>> {
631 let rows = sqlx::query(
632 r#"
633 SELECT key
634 FROM kv_store
635 WHERE key LIKE $1 AND (expires_at IS NULL OR expires_at > NOW())
636 ORDER BY key
637 "#,
638 )
639 .bind(format!("{prefix}%"))
640 .fetch_all(&self.pool)
641 .await
642 .map_err(|e| {
643 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
644 "Failed to list kv keys: {}",
645 e
646 )))
647 })?;
648
649 rows.into_iter()
650 .map(|row| {
651 row.try_get("key").map_err(|e| {
652 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
653 "Failed to decode kv key: {}",
654 e
655 )))
656 })
657 })
658 .collect()
659 }
660
661 async fn cleanup_expired(&self) -> Result<()> {
662 sqlx::query("DELETE FROM auth_tokens WHERE expires_at < NOW()")
664 .execute(&self.pool)
665 .await
666 .map_err(|e| {
667 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
668 "Failed to cleanup expired tokens: {}",
669 e
670 )))
671 })?;
672
673 sqlx::query("DELETE FROM sessions WHERE expires_at IS NOT NULL AND expires_at < NOW()")
675 .execute(&self.pool)
676 .await
677 .map_err(|e| {
678 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
679 "Failed to cleanup expired sessions: {}",
680 e
681 )))
682 })?;
683
684 sqlx::query("DELETE FROM kv_store WHERE expires_at IS NOT NULL AND expires_at < NOW()")
686 .execute(&self.pool)
687 .await
688 .map_err(|e| {
689 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
690 "Failed to cleanup expired kv: {}",
691 e
692 )))
693 })?;
694
695 Ok(())
696 }
697
698 async fn list_user_sessions(&self, user_id: &str) -> Result<Vec<SessionData>> {
699 let rows = sqlx::query(
700 r#"
701 SELECT session_id, user_id, data, expires_at, created_at, last_activity, ip_address, user_agent
702 FROM sessions
703 WHERE user_id = $1 AND (expires_at IS NULL OR expires_at > NOW())
704 "#,
705 )
706 .bind(user_id)
707 .fetch_all(&self.pool)
708 .await
709 .map_err(|e| {
710 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
711 "Failed to list user sessions: {}",
712 e
713 )))
714 })?;
715
716 let sessions = rows
717 .into_iter()
718 .map(|row| SessionData {
719 session_id: row.try_get("session_id").unwrap_or_default(),
720 user_id: row.try_get("user_id").unwrap_or_default(),
721 created_at: row.try_get("created_at").unwrap_or_default(),
722 data: {
723 let json_value: serde_json::Value = row.try_get("data").unwrap_or_default();
724 if let serde_json::Value::Object(map) = json_value {
725 map.into_iter().collect()
726 } else {
727 std::collections::HashMap::new()
728 }
729 },
730 expires_at: row.try_get("expires_at").unwrap_or_default(),
731 last_activity: row.try_get("last_activity").unwrap_or_default(),
732 ip_address: row.try_get("ip_address").ok(),
733 user_agent: row.try_get("user_agent").ok(),
734 })
735 .collect();
736
737 Ok(sessions)
738 }
739
740 async fn count_active_sessions(&self) -> Result<u64> {
741 let row = sqlx::query(
742 "SELECT COUNT(*) as count FROM sessions WHERE expires_at IS NULL OR expires_at > NOW()",
743 )
744 .fetch_one(&self.pool)
745 .await
746 .map_err(|e| {
747 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
748 "Failed to count active sessions: {}",
749 e
750 )))
751 })?;
752
753 let count: i64 = row.try_get("count").map_err(|e| {
754 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
755 "Failed to parse session count: {}",
756 e
757 )))
758 })?;
759
760 Ok(count as u64)
761 }
762}