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