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.filter(|t| !t.is_expired()))
260 }
261
262 async fn get_token_by_access_token(&self, access_token: &str) -> Result<Option<AuthToken>> {
263 let row =
264 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE access_token = $1"#)
265 .bind(access_token)
266 .fetch_optional(&self.pool)
267 .await
268 .map_err(|e| {
269 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
270 "Failed to fetch token by access_token: {}",
271 e
272 )))
273 })?;
274 Ok(row.filter(|t| !t.is_expired()))
275 }
276
277 async fn update_token(&self, token: &AuthToken) -> Result<()> {
278 sqlx::query(
279 r#"
280 UPDATE auth_tokens SET
281 access_token = $1,
282 refresh_token = $2,
283 token_type = $3,
284 expires_at = $4,
285 scopes = $5,
286 issued_at = $6,
287 auth_method = $7,
288 subject = $8,
289 issuer = $9,
290 client_id = $10,
291 metadata = $11,
292 user_profile = $12,
293 permissions = $13,
294 roles = $14
295 WHERE token_id = $15
296 "#,
297 )
298 .bind(&token.access_token)
299 .bind(&token.refresh_token)
300 .bind(&token.token_type)
301 .bind(token.expires_at)
302 .bind(&token.scopes)
303 .bind(token.issued_at)
304 .bind(&token.auth_method)
305 .bind(&token.subject)
306 .bind(&token.issuer)
307 .bind(&token.client_id)
308 .bind(serde_json::to_value(&token.metadata).unwrap_or_default())
309 .bind(Self::profile_json(token))
310 .bind(&token.permissions)
311 .bind(&token.roles)
312 .bind(&token.token_id)
313 .execute(&self.pool)
314 .await
315 .map_err(|e| {
316 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
317 "Failed to update token: {}",
318 e
319 )))
320 })?;
321 Ok(())
322 }
323
324 async fn delete_token(&self, token_id: &str) -> Result<()> {
325 sqlx::query(r#"DELETE FROM auth_tokens WHERE token_id = $1"#)
326 .bind(token_id)
327 .execute(&self.pool)
328 .await
329 .map_err(|e| {
330 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
331 "Failed to delete token: {}",
332 e
333 )))
334 })?;
335 Ok(())
336 }
337
338 async fn list_user_tokens(&self, user_id: &str) -> Result<Vec<AuthToken>> {
339 let tokens =
340 sqlx::query_as::<_, AuthToken>(r#"SELECT * FROM auth_tokens WHERE user_id = $1"#)
341 .bind(user_id)
342 .fetch_all(&self.pool)
343 .await
344 .map_err(|e| {
345 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
346 "Failed to list user tokens: {}",
347 e
348 )))
349 })?;
350 Ok(tokens)
351 }
352
353 async fn store_session(
354 &self,
355 session_id: &str,
356 data: &crate::storage::core::SessionData,
357 ) -> Result<()> {
358 sqlx::query(
359 r#"
360 INSERT INTO sessions (session_id, user_id, data, expires_at)
361 VALUES ($1, $2, $3, $4)
362 ON CONFLICT (session_id) DO UPDATE SET
363 data = EXCLUDED.data,
364 expires_at = EXCLUDED.expires_at
365 "#,
366 )
367 .bind(session_id)
368 .bind(&data.user_id)
369 .bind(serde_json::to_value(data).unwrap_or_default())
370 .bind(data.expires_at)
371 .execute(&self.pool)
372 .await
373 .map_err(|e| {
374 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
375 "Failed to store session: {}",
376 e
377 )))
378 })?;
379 Ok(())
380 }
381
382 async fn get_session(
383 &self,
384 session_id: &str,
385 ) -> Result<Option<crate::storage::core::SessionData>> {
386 let row = sqlx::query(r#"SELECT data FROM sessions WHERE session_id = $1"#)
387 .bind(session_id)
388 .fetch_optional(&self.pool)
389 .await
390 .map_err(|e| {
391 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
392 "Failed to fetch session: {}",
393 e
394 )))
395 })?;
396 if let Some(row) = row {
397 let data: serde_json::Value = row.try_get("data").map_err(|e| {
398 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
399 "Failed to deserialize session data: {}",
400 e
401 )))
402 })?;
403 let session: crate::storage::core::SessionData =
404 serde_json::from_value(data).map_err(|e| {
405 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
406 "Failed to parse session data: {}",
407 e
408 )))
409 })?;
410 Ok(Some(session).filter(|s| s.expires_at > chrono::Utc::now()))
411 } else {
412 Ok(None)
413 }
414 }
415
416 async fn delete_session(&self, session_id: &str) -> Result<()> {
417 sqlx::query(r#"DELETE FROM sessions WHERE session_id = $1"#)
418 .bind(session_id)
419 .execute(&self.pool)
420 .await
421 .map_err(|e| {
422 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
423 "Failed to delete session: {}",
424 e
425 )))
426 })?;
427 Ok(())
428 }
429
430 async fn store_kv(
431 &self,
432 key: &str,
433 value: &[u8],
434 ttl: Option<std::time::Duration>,
435 ) -> Result<()> {
436 let expires_at = ttl.map(|d| {
437 chrono::Utc::now()
438 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
439 });
440 sqlx::query(
441 r#"
442 INSERT INTO kv_store (key, value, expires_at)
443 VALUES ($1, $2, $3)
444 ON CONFLICT (key) DO UPDATE SET
445 value = EXCLUDED.value,
446 expires_at = EXCLUDED.expires_at
447 "#,
448 )
449 .bind(key)
450 .bind(value)
451 .bind(expires_at)
452 .execute(&self.pool)
453 .await
454 .map_err(|e| {
455 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
456 "Failed to store kv: {}",
457 e
458 )))
459 })?;
460 Ok(())
461 }
462
463 async fn store_kv_if_absent(
464 &self,
465 key: &str,
466 value: &[u8],
467 ttl: Option<std::time::Duration>,
468 ) -> Result<bool> {
469 let expires_at = ttl.map(|d| {
470 chrono::Utc::now()
471 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
472 });
473 let result = sqlx::query(
475 r#"
476 INSERT INTO kv_store (key, value, expires_at)
477 VALUES ($1, $2, $3)
478 ON CONFLICT (key) DO UPDATE SET
479 value = EXCLUDED.value,
480 expires_at = EXCLUDED.expires_at
481 WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
482 "#,
483 )
484 .bind(key)
485 .bind(value)
486 .bind(expires_at)
487 .execute(&self.pool)
488 .await
489 .map_err(|e| {
490 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
491 "Failed to store kv if absent: {}",
492 e
493 )))
494 })?;
495 Ok(result.rows_affected() == 1)
496 }
497
498 async fn swap_kv(
499 &self,
500 remove: &[String],
501 key: &str,
502 value: &[u8],
503 ttl: Option<std::time::Duration>,
504 ) -> Result<bool> {
505 if remove.is_empty() {
506 return Ok(false);
507 }
508 let expires_at = ttl.map(|d| {
509 chrono::Utc::now()
510 + chrono::Duration::from_std(d).unwrap_or(chrono::Duration::seconds(0))
511 });
512 let fail = |what: &str, e: sqlx::Error| {
513 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
514 "Failed to swap kv ({what}): {e}"
515 )))
516 };
517 let mut tx = self.pool.begin().await.map_err(|e| fail("begin", e))?;
518
519 let removed = sqlx::query(
522 r#"
523 DELETE FROM kv_store
524 WHERE key = ANY($1) AND (expires_at IS NULL OR expires_at > NOW())
525 "#,
526 )
527 .bind(remove)
528 .execute(&mut *tx)
529 .await
530 .map_err(|e| fail("remove", e))?
531 .rows_affected();
532 if removed == 0 {
533 tx.rollback().await.map_err(|e| fail("rollback", e))?;
534 return Ok(false);
535 }
536
537 let inserted = sqlx::query(
539 r#"
540 INSERT INTO kv_store (key, value, expires_at)
541 VALUES ($1, $2, $3)
542 ON CONFLICT (key) DO UPDATE SET
543 value = EXCLUDED.value,
544 expires_at = EXCLUDED.expires_at
545 WHERE kv_store.expires_at IS NOT NULL AND kv_store.expires_at <= NOW()
546 "#,
547 )
548 .bind(key)
549 .bind(value)
550 .bind(expires_at)
551 .execute(&mut *tx)
552 .await
553 .map_err(|e| fail("store", e))?
554 .rows_affected();
555 if inserted != 1 {
556 tx.rollback().await.map_err(|e| fail("rollback", e))?;
557 return Ok(false);
558 }
559 tx.commit().await.map_err(|e| fail("commit", e))?;
560 Ok(true)
561 }
562
563 fn supports_atomic_swap(&self) -> bool {
564 true
565 }
566
567 async fn get_kv(&self, key: &str) -> Result<Option<Vec<u8>>> {
568 let row = sqlx::query(
569 r#"SELECT value FROM kv_store WHERE key = $1 AND (expires_at IS NULL OR expires_at > NOW())"#
570 )
571 .bind(key)
572 .fetch_optional(&self.pool)
573 .await
574 .map_err(|e| {
575 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
576 "Failed to fetch kv: {}", e
577 )))
578 })?;
579 if let Some(row) = row {
580 let value: Vec<u8> = row.try_get("value").map_err(|e| {
581 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
582 "Failed to deserialize kv value: {}",
583 e
584 )))
585 })?;
586 Ok(Some(value))
587 } else {
588 Ok(None)
589 }
590 }
591
592 async fn get_kv_ttl(&self, key: &str) -> Result<Option<std::time::Duration>> {
593 let row = sqlx::query(
594 r#"SELECT EXTRACT(EPOCH FROM (expires_at - NOW()))::float8 AS seconds
595 FROM kv_store
596 WHERE key = $1 AND expires_at IS NOT NULL AND expires_at > NOW()"#,
597 )
598 .bind(key)
599 .fetch_optional(&self.pool)
600 .await
601 .map_err(|e| {
602 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
603 "Failed to fetch kv ttl: {}",
604 e
605 )))
606 })?;
607 Ok(row
608 .and_then(|row| row.try_get::<f64, _>("seconds").ok())
609 .filter(|seconds| *seconds > 0.0)
610 .map(std::time::Duration::from_secs_f64))
611 }
612
613 fn tracks_kv_ttl(&self) -> bool {
614 true
615 }
616
617 async fn delete_kv(&self, key: &str) -> Result<()> {
618 sqlx::query(r#"DELETE FROM kv_store WHERE key = $1"#)
619 .bind(key)
620 .execute(&self.pool)
621 .await
622 .map_err(|e| {
623 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
624 "Failed to delete kv: {}",
625 e
626 )))
627 })?;
628 Ok(())
629 }
630
631 async fn list_kv_keys(&self, prefix: &str) -> Result<Vec<String>> {
632 let rows = sqlx::query(
633 r#"
634 SELECT key
635 FROM kv_store
636 WHERE key LIKE $1 AND (expires_at IS NULL OR expires_at > NOW())
637 ORDER BY key
638 "#,
639 )
640 .bind(format!("{prefix}%"))
641 .fetch_all(&self.pool)
642 .await
643 .map_err(|e| {
644 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
645 "Failed to list kv keys: {}",
646 e
647 )))
648 })?;
649
650 rows.into_iter()
651 .map(|row| {
652 row.try_get("key").map_err(|e| {
653 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
654 "Failed to decode kv key: {}",
655 e
656 )))
657 })
658 })
659 .collect()
660 }
661
662 async fn cleanup_expired(&self) -> Result<()> {
663 sqlx::query("DELETE FROM auth_tokens WHERE expires_at < NOW()")
665 .execute(&self.pool)
666 .await
667 .map_err(|e| {
668 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
669 "Failed to cleanup expired tokens: {}",
670 e
671 )))
672 })?;
673
674 sqlx::query("DELETE FROM sessions WHERE expires_at IS NOT NULL AND expires_at < NOW()")
676 .execute(&self.pool)
677 .await
678 .map_err(|e| {
679 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
680 "Failed to cleanup expired sessions: {}",
681 e
682 )))
683 })?;
684
685 sqlx::query("DELETE FROM kv_store WHERE expires_at IS NOT NULL AND expires_at < NOW()")
687 .execute(&self.pool)
688 .await
689 .map_err(|e| {
690 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
691 "Failed to cleanup expired kv: {}",
692 e
693 )))
694 })?;
695
696 Ok(())
697 }
698
699 async fn list_user_sessions(&self, user_id: &str) -> Result<Vec<SessionData>> {
700 let rows = sqlx::query(
703 r#"
704 SELECT data
705 FROM sessions
706 WHERE user_id = $1 AND (expires_at IS NULL OR expires_at > NOW())
707 "#,
708 )
709 .bind(user_id)
710 .fetch_all(&self.pool)
711 .await
712 .map_err(|e| {
713 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
714 "Failed to list user sessions: {}",
715 e
716 )))
717 })?;
718
719 rows.into_iter()
720 .map(|row| {
721 let data: serde_json::Value = row.try_get("data").map_err(|e| {
722 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
723 "Failed to read session data: {}",
724 e
725 )))
726 })?;
727 serde_json::from_value::<SessionData>(data).map_err(|e| {
728 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
729 "Failed to parse session data: {}",
730 e
731 )))
732 })
733 })
734 .collect()
735 }
736
737 async fn count_active_sessions(&self) -> Result<u64> {
738 let row = sqlx::query(
739 "SELECT COUNT(*) as count FROM sessions WHERE expires_at IS NULL OR expires_at > NOW()",
740 )
741 .fetch_one(&self.pool)
742 .await
743 .map_err(|e| {
744 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
745 "Failed to count active sessions: {}",
746 e
747 )))
748 })?;
749
750 let count: i64 = row.try_get("count").map_err(|e| {
751 AuthError::Storage(crate::errors::StorageError::operation_failed(format!(
752 "Failed to parse session count: {}",
753 e
754 )))
755 })?;
756
757 Ok(count as u64)
758 }
759}