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