A community based topic aggregation platform built on atproto
bretton.dev
1package oauth
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "time"
8)
9
10// PostgresSessionStore implements SessionStore using PostgreSQL
11type PostgresSessionStore struct {
12 db *sql.DB
13}
14
15// NewPostgresSessionStore creates a new PostgreSQL-backed session store
16func NewPostgresSessionStore(db *sql.DB) SessionStore {
17 return &PostgresSessionStore{db: db}
18}
19
20// SaveRequest stores a temporary OAuth request state
21func (s *PostgresSessionStore) SaveRequest(req *OAuthRequest) error {
22 query := `
23 INSERT INTO oauth_requests (
24 state, did, handle, pds_url, pkce_verifier,
25 dpop_private_jwk, dpop_authserver_nonce, auth_server_iss, return_url
26 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
27 `
28
29 _, err := s.db.Exec(
30 query,
31 req.State,
32 req.DID,
33 req.Handle,
34 req.PDSURL,
35 req.PKCEVerifier,
36 req.DPoPPrivateJWK,
37 req.DPoPAuthServerNonce,
38 req.AuthServerIss,
39 req.ReturnURL,
40 )
41
42 if err != nil {
43 return fmt.Errorf("failed to save OAuth request: %w", err)
44 }
45
46 return nil
47}
48
49// GetRequestByState retrieves an OAuth request by state parameter
50func (s *PostgresSessionStore) GetRequestByState(state string) (*OAuthRequest, error) {
51 query := `
52 SELECT
53 state, did, handle, pds_url, pkce_verifier,
54 dpop_private_jwk, dpop_authserver_nonce, auth_server_iss,
55 COALESCE(return_url, ''), created_at
56 FROM oauth_requests
57 WHERE state = $1
58 `
59
60 var req OAuthRequest
61 err := s.db.QueryRow(query, state).Scan(
62 &req.State,
63 &req.DID,
64 &req.Handle,
65 &req.PDSURL,
66 &req.PKCEVerifier,
67 &req.DPoPPrivateJWK,
68 &req.DPoPAuthServerNonce,
69 &req.AuthServerIss,
70 &req.ReturnURL,
71 &req.CreatedAt,
72 )
73
74 if err == sql.ErrNoRows {
75 return nil, fmt.Errorf("OAuth request not found for state: %s", state)
76 }
77 if err != nil {
78 return nil, fmt.Errorf("failed to get OAuth request: %w", err)
79 }
80
81 return &req, nil
82}
83
84// GetAndDeleteRequest atomically retrieves and deletes an OAuth request to prevent replay attacks
85// This ensures the state parameter can only be used once
86func (s *PostgresSessionStore) GetAndDeleteRequest(state string) (*OAuthRequest, error) {
87 query := `
88 DELETE FROM oauth_requests
89 WHERE state = $1
90 RETURNING
91 state, did, handle, pds_url, pkce_verifier,
92 dpop_private_jwk, dpop_authserver_nonce, auth_server_iss,
93 COALESCE(return_url, ''), created_at
94 `
95
96 var req OAuthRequest
97 err := s.db.QueryRow(query, state).Scan(
98 &req.State,
99 &req.DID,
100 &req.Handle,
101 &req.PDSURL,
102 &req.PKCEVerifier,
103 &req.DPoPPrivateJWK,
104 &req.DPoPAuthServerNonce,
105 &req.AuthServerIss,
106 &req.ReturnURL,
107 &req.CreatedAt,
108 )
109
110 if err == sql.ErrNoRows {
111 return nil, fmt.Errorf("OAuth request not found or already used: %s", state)
112 }
113 if err != nil {
114 return nil, fmt.Errorf("failed to get and delete OAuth request: %w", err)
115 }
116
117 return &req, nil
118}
119
120// DeleteRequest removes an OAuth request (cleanup after callback)
121func (s *PostgresSessionStore) DeleteRequest(state string) error {
122 query := `DELETE FROM oauth_requests WHERE state = $1`
123
124 _, err := s.db.Exec(query, state)
125 if err != nil {
126 return fmt.Errorf("failed to delete OAuth request: %w", err)
127 }
128
129 return nil
130}
131
132// SaveSession stores a new OAuth session (upsert on DID)
133func (s *PostgresSessionStore) SaveSession(session *OAuthSession) error {
134 query := `
135 INSERT INTO oauth_sessions (
136 did, handle, pds_url, access_token, refresh_token,
137 dpop_private_jwk, dpop_authserver_nonce, dpop_pds_nonce,
138 auth_server_iss, expires_at
139 ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
140 ON CONFLICT (did) DO UPDATE SET
141 handle = EXCLUDED.handle,
142 pds_url = EXCLUDED.pds_url,
143 access_token = EXCLUDED.access_token,
144 refresh_token = EXCLUDED.refresh_token,
145 dpop_private_jwk = EXCLUDED.dpop_private_jwk,
146 dpop_authserver_nonce = EXCLUDED.dpop_authserver_nonce,
147 dpop_pds_nonce = EXCLUDED.dpop_pds_nonce,
148 auth_server_iss = EXCLUDED.auth_server_iss,
149 expires_at = EXCLUDED.expires_at,
150 updated_at = CURRENT_TIMESTAMP
151 `
152
153 _, err := s.db.Exec(
154 query,
155 session.DID,
156 session.Handle,
157 session.PDSURL,
158 session.AccessToken,
159 session.RefreshToken,
160 session.DPoPPrivateJWK,
161 session.DPoPAuthServerNonce,
162 session.DPoPPDSNonce,
163 session.AuthServerIss,
164 session.ExpiresAt,
165 )
166
167 if err != nil {
168 return fmt.Errorf("failed to save OAuth session: %w", err)
169 }
170
171 return nil
172}
173
174// GetSession retrieves an OAuth session by DID
175func (s *PostgresSessionStore) GetSession(did string) (*OAuthSession, error) {
176 query := `
177 SELECT
178 did, handle, pds_url, access_token, refresh_token,
179 dpop_private_jwk,
180 COALESCE(dpop_authserver_nonce, ''),
181 COALESCE(dpop_pds_nonce, ''),
182 auth_server_iss, expires_at, created_at, updated_at
183 FROM oauth_sessions
184 WHERE did = $1
185 `
186
187 var session OAuthSession
188 err := s.db.QueryRow(query, did).Scan(
189 &session.DID,
190 &session.Handle,
191 &session.PDSURL,
192 &session.AccessToken,
193 &session.RefreshToken,
194 &session.DPoPPrivateJWK,
195 &session.DPoPAuthServerNonce,
196 &session.DPoPPDSNonce,
197 &session.AuthServerIss,
198 &session.ExpiresAt,
199 &session.CreatedAt,
200 &session.UpdatedAt,
201 )
202
203 if err == sql.ErrNoRows {
204 return nil, fmt.Errorf("session not found for DID: %s", did)
205 }
206 if err != nil {
207 return nil, fmt.Errorf("failed to get OAuth session: %w", err)
208 }
209
210 return &session, nil
211}
212
213// UpdateSession updates an existing OAuth session
214func (s *PostgresSessionStore) UpdateSession(session *OAuthSession) error {
215 query := `
216 UPDATE oauth_sessions SET
217 handle = $2,
218 pds_url = $3,
219 access_token = $4,
220 refresh_token = $5,
221 dpop_private_jwk = $6,
222 dpop_authserver_nonce = $7,
223 dpop_pds_nonce = $8,
224 auth_server_iss = $9,
225 expires_at = $10,
226 updated_at = CURRENT_TIMESTAMP
227 WHERE did = $1
228 `
229
230 result, err := s.db.Exec(
231 query,
232 session.DID,
233 session.Handle,
234 session.PDSURL,
235 session.AccessToken,
236 session.RefreshToken,
237 session.DPoPPrivateJWK,
238 session.DPoPAuthServerNonce,
239 session.DPoPPDSNonce,
240 session.AuthServerIss,
241 session.ExpiresAt,
242 )
243
244 if err != nil {
245 return fmt.Errorf("failed to update OAuth session: %w", err)
246 }
247
248 rows, err := result.RowsAffected()
249 if err != nil {
250 return fmt.Errorf("failed to check rows affected: %w", err)
251 }
252 if rows == 0 {
253 return fmt.Errorf("session not found for DID: %s", session.DID)
254 }
255
256 return nil
257}
258
259// DeleteSession removes an OAuth session (logout)
260func (s *PostgresSessionStore) DeleteSession(did string) error {
261 query := `DELETE FROM oauth_sessions WHERE did = $1`
262
263 _, err := s.db.Exec(query, did)
264 if err != nil {
265 return fmt.Errorf("failed to delete OAuth session: %w", err)
266 }
267
268 return nil
269}
270
271// RefreshSession updates access and refresh tokens after a token refresh
272func (s *PostgresSessionStore) RefreshSession(did, newAccessToken, newRefreshToken string, expiresAt time.Time) error {
273 query := `
274 UPDATE oauth_sessions SET
275 access_token = $2,
276 refresh_token = $3,
277 expires_at = $4,
278 updated_at = CURRENT_TIMESTAMP
279 WHERE did = $1
280 `
281
282 result, err := s.db.Exec(query, did, newAccessToken, newRefreshToken, expiresAt)
283 if err != nil {
284 return fmt.Errorf("failed to refresh OAuth session: %w", err)
285 }
286
287 rows, err := result.RowsAffected()
288 if err != nil {
289 return fmt.Errorf("failed to check rows affected: %w", err)
290 }
291 if rows == 0 {
292 return fmt.Errorf("session not found for DID: %s", did)
293 }
294
295 return nil
296}
297
298// UpdateAuthServerNonce updates the DPoP nonce for the auth server token endpoint
299func (s *PostgresSessionStore) UpdateAuthServerNonce(did, nonce string) error {
300 query := `
301 UPDATE oauth_sessions SET
302 dpop_authserver_nonce = $2,
303 updated_at = CURRENT_TIMESTAMP
304 WHERE did = $1
305 `
306
307 _, err := s.db.Exec(query, did, nonce)
308 if err != nil {
309 return fmt.Errorf("failed to update auth server nonce: %w", err)
310 }
311
312 return nil
313}
314
315// UpdatePDSNonce updates the DPoP nonce for PDS requests
316func (s *PostgresSessionStore) UpdatePDSNonce(did, nonce string) error {
317 query := `
318 UPDATE oauth_sessions SET
319 dpop_pds_nonce = $2,
320 updated_at = CURRENT_TIMESTAMP
321 WHERE did = $1
322 `
323
324 _, err := s.db.Exec(query, did, nonce)
325 if err != nil {
326 return fmt.Errorf("failed to update PDS nonce: %w", err)
327 }
328
329 return nil
330}
331
332// CleanupExpiredRequests removes OAuth requests older than 30 minutes
333// Should be called periodically (e.g., via cron job or background goroutine)
334func (s *PostgresSessionStore) CleanupExpiredRequests(ctx context.Context) error {
335 query := `DELETE FROM oauth_requests WHERE created_at < NOW() - INTERVAL '30 minutes'`
336
337 _, err := s.db.ExecContext(ctx, query)
338 if err != nil {
339 return fmt.Errorf("failed to cleanup expired requests: %w", err)
340 }
341
342 return nil
343}
344
345// CleanupExpiredSessions removes OAuth sessions that have been expired for > 7 days
346// Gives users time to refresh their tokens before permanent deletion
347func (s *PostgresSessionStore) CleanupExpiredSessions(ctx context.Context) error {
348 query := `DELETE FROM oauth_sessions WHERE expires_at < NOW() - INTERVAL '7 days'`
349
350 _, err := s.db.ExecContext(ctx, query)
351 if err != nil {
352 return fmt.Errorf("failed to cleanup expired sessions: %w", err)
353 }
354
355 return nil
356}