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