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    /// Initialize database tables.
30    ///
31    /// Creates the `auth_tokens`, `sessions`, and `kv_store` tables together with
32    /// their secondary indexes if they do not already exist.  Safe to call on every
33    /// application startup (`IF NOT EXISTS` / `IF NOT EXISTS` guards are idempotent).
34    ///
35    /// # Errors
36    /// Returns an error if any DDL statement fails (e.g. insufficient privileges).
37    pub async fn migrate(&self) -> Result<()> {
38        // Each statement is executed separately because sqlx::query() accepts
39        // exactly one SQL statement per call.
40
41        // --- auth_tokens table ---
42        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        // Secondary indexes for auth_tokens (PostgreSQL requires separate CREATE INDEX statements)
71        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        // `AuthToken` derives `FromRow` and `get_token` selects `*`, so a row must carry a column
84        // for every field or the read fails ("no column found for name: user_profile"). These
85        // columns upgrade existing tables too. (Main persists the values as well, #93; this line
86        // only makes the read work, the three fields come back as their defaults.)
87        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        // --- sessions table ---
100        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        // --- kv_store table ---
131        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    // ... implement other AuthStorage methods
204    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        // An expired token is not returned (the trait asks backends to honour expiry).
217        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        // Remove expired tokens
487        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        // Remove expired sessions
498        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        // Remove expired kv entries
509        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        // `data` holds the whole session (as `store_session` writes it and `get_session` reads
524        // it); the table has no `last_activity`, `ip_address` or `user_agent` columns.
525        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}