Skip to main content

auth_framework/storage/
postgres.rs

1/// PostgreSQL storage implementation for auth-framework.
2/// This module provides a production-ready PostgreSQL backend for storing
3/// authentication tokens, sessions, and audit logs.
4use crate::errors::{AuthError, Result};
5use crate::storage::{AuthStorage, SessionData};
6use crate::tokens::AuthToken;
7use async_trait::async_trait;
8use sqlx::PgPool;
9use sqlx::Row;
10// use std::time::Duration;
11
12/// PostgreSQL storage backend
13pub struct PostgresStorage {
14    pool: PgPool,
15}
16
17impl PostgresStorage {
18    /// Create a new PostgreSQL storage instance.
19    ///
20    /// # Example
21    /// ```rust,ignore
22    /// let pool = PgPool::connect("postgres://user:pass@localhost/db").await?;
23    /// let storage = PostgresStorage::new(pool);
24    /// ```
25    pub fn new(pool: PgPool) -> Self {
26        Self { pool }
27    }
28
29    /// The token's profile as a JSONB value; `None` is stored as SQL NULL.
30    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    /// Initialize database tables.
38    ///
39    /// Creates the `auth_tokens`, `sessions`, and `kv_store` tables together with
40    /// their secondary indexes if they do not already exist.  Safe to call on every
41    /// application startup (`IF NOT EXISTS` / `IF NOT EXISTS` guards are idempotent).
42    ///
43    /// # Errors
44    /// Returns an error if any DDL statement fails (e.g. insufficient privileges).
45    pub async fn migrate(&self) -> Result<()> {
46        // Each statement is executed separately because sqlx::query() accepts
47        // exactly one SQL statement per call.
48
49        // --- auth_tokens table ---
50        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        // Secondary indexes for auth_tokens (PostgreSQL requires separate CREATE INDEX statements)
79        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        // `AuthToken` derives `FromRow` and `get_token` selects `*`, so a row must carry a column
92        // for every field or the read fails ("no column found for name: user_profile"). These
93        // columns upgrade existing tables, and `store_token` and `update_token` write them (#93).
94        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        // --- sessions table ---
107        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        // --- kv_store table ---
138        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    // ... implement other AuthStorage methods
217    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        // An expired token is not returned (the trait asks backends to honour expiry).
230        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        // One statement: insert, or take over an EXPIRED row; a live row is left alone.
445        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        // Remove expired tokens
541        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        // Remove expired sessions
552        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        // Remove expired kv entries
563        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        // `data` holds the whole session (as `store_session` writes it and `get_session` reads
578        // it); the table has no `last_activity`, `ip_address` or `user_agent` columns.
579        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}