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