forked from sourcegraph/sourcegraph-public-snapshot
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathuser_emails.go
More file actions
334 lines (295 loc) · 12.8 KB
/
user_emails.go
File metadata and controls
334 lines (295 loc) · 12.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
package database
import (
"context"
"crypto/subtle"
"database/sql"
"fmt"
"time"
"github.com/keegancsmith/sqlf"
"github.com/sourcegraph/sourcegraph/internal/database/basestore"
"github.com/sourcegraph/sourcegraph/lib/errors"
)
// UserEmail represents a row in the `user_emails` table.
type UserEmail struct {
UserID int32
Email string
CreatedAt time.Time
VerificationCode *string
VerifiedAt *time.Time
LastVerificationSentAt *time.Time
Primary bool
}
// NeedsVerificationCoolDown returns true if the verification cooled down time is behind current time.
func (email *UserEmail) NeedsVerificationCoolDown() bool {
const defaultDur = 30 * time.Second
return email.LastVerificationSentAt != nil &&
time.Now().UTC().Before(email.LastVerificationSentAt.Add(defaultDur))
}
// userEmailNotFoundError is the error that is returned when a user email is not found.
type userEmailNotFoundError struct {
args []any
}
func (err userEmailNotFoundError) Error() string {
return fmt.Sprintf("user email not found: %v", err.args)
}
func (err userEmailNotFoundError) NotFound() bool {
return true
}
type UserEmailsStore interface {
Add(ctx context.Context, userID int32, email string, verificationCode *string) error
Done(error) error
Get(ctx context.Context, userID int32, email string) (emailCanonicalCase string, verified bool, err error)
GetInitialSiteAdminInfo(ctx context.Context) (email string, tosAccepted bool, err error)
GetLatestVerificationSentEmail(ctx context.Context, email string) (*UserEmail, error)
GetPrimaryEmail(ctx context.Context, id int32) (email string, verified bool, err error)
GetVerifiedEmails(ctx context.Context, emails ...string) ([]*UserEmail, error)
ListByUser(ctx context.Context, opt UserEmailsListOptions) ([]*UserEmail, error)
Remove(ctx context.Context, userID int32, email string) error
SetLastVerification(ctx context.Context, userID int32, email, code string) error
SetPrimaryEmail(ctx context.Context, userID int32, email string) error
SetVerified(ctx context.Context, userID int32, email string, verified bool) error
Transact(ctx context.Context) (UserEmailsStore, error)
Verify(ctx context.Context, userID int32, email, code string) (bool, error)
With(other basestore.ShareableStore) UserEmailsStore
basestore.ShareableStore
}
// userEmailsStore provides access to the `user_emails` table.
type userEmailsStore struct {
*basestore.Store
}
// UserEmailsWith instantiates and returns a new UserEmailsStore using the other store handle.
func UserEmailsWith(other basestore.ShareableStore) UserEmailsStore {
return &userEmailsStore{Store: basestore.NewWithHandle(other.Handle())}
}
func (s *userEmailsStore) With(other basestore.ShareableStore) UserEmailsStore {
return &userEmailsStore{Store: s.Store.With(other)}
}
func (s *userEmailsStore) Transact(ctx context.Context) (UserEmailsStore, error) {
txBase, err := s.Store.Transact(ctx)
return &userEmailsStore{Store: txBase}, err
}
// GetInitialSiteAdminInfo returns a best guess of the email and terms of service acceptance of the initial
// Sourcegraph installer/site admin. Because the initial site admin's email isn't marked, this returns the
// info of the active site admin with the lowest user ID.
//
// If the site has not yet been initialized, returns an empty string.
func (s *userEmailsStore) GetInitialSiteAdminInfo(ctx context.Context) (email string, tosAccepted bool, err error) {
if init, err := GlobalStateWith(s).SiteInitialized(ctx); err != nil || !init {
return "", false, err
}
if err := s.Handle().QueryRowContext(ctx, "SELECT email, tos_accepted FROM user_emails JOIN users ON user_emails.user_id=users.id WHERE users.site_admin AND users.deleted_at IS NULL ORDER BY users.id ASC LIMIT 1").Scan(&email, &tosAccepted); err != nil {
return "", false, errors.New("initial site admin email not found")
}
return email, tosAccepted, nil
}
// GetPrimaryEmail gets the oldest email associated with the user, preferring a verified email to an
// unverified email.
func (s *userEmailsStore) GetPrimaryEmail(ctx context.Context, id int32) (email string, verified bool, err error) {
if err := s.Handle().QueryRowContext(ctx, "SELECT email, verified_at IS NOT NULL AS verified FROM user_emails WHERE user_id=$1 AND is_primary",
id,
).Scan(&email, &verified); err != nil {
if err == sql.ErrNoRows {
return "", false, userEmailNotFoundError{[]any{fmt.Sprintf("id %d", id)}}
}
return "", false, err
}
return email, verified, nil
}
// SetPrimaryEmail sets the primary email for a user.
// The address must be verified.
// All other addresses for the user will be set as not primary.
func (s *userEmailsStore) SetPrimaryEmail(ctx context.Context, userID int32, email string) (err error) {
tx, err := s.Transact(ctx)
if err != nil {
return err
}
defer func() { err = tx.Done(err) }()
// Get the email. It needs to exist and be verified.
var verified bool
if err := tx.Handle().QueryRowContext(ctx, "SELECT verified_at IS NOT NULL AS verified FROM user_emails WHERE user_id=$1 AND email=$2",
userID, email,
).Scan(&verified); err != nil {
return err
}
if !verified {
return errors.New("primary email must be verified")
}
// We need to set all as non primary and then set the correct one as primary in two steps
// so that we don't violate our index.
// Set all as not primary
if _, err := tx.Handle().ExecContext(ctx, "UPDATE user_emails SET is_primary = false WHERE user_id=$1", userID); err != nil {
return err
}
// Set selected as primary
if _, err := tx.Handle().ExecContext(ctx, "UPDATE user_emails SET is_primary = true WHERE user_id=$1 AND email=$2", userID, email); err != nil {
return err
}
return nil
}
// Get gets information about the user's associated email address.
func (s *userEmailsStore) Get(ctx context.Context, userID int32, email string) (emailCanonicalCase string, verified bool, err error) {
if err := s.Handle().QueryRowContext(ctx, "SELECT email, verified_at IS NOT NULL AS verified FROM user_emails WHERE user_id=$1 AND email=$2",
userID, email,
).Scan(&emailCanonicalCase, &verified); err != nil {
return "", false, userEmailNotFoundError{[]any{fmt.Sprintf("userID %d email %q", userID, email)}}
}
return emailCanonicalCase, verified, nil
}
// Add adds new user email. When added, it is always unverified.
func (s *userEmailsStore) Add(ctx context.Context, userID int32, email string, verificationCode *string) error {
_, err := s.Handle().ExecContext(ctx, "INSERT INTO user_emails(user_id, email, verification_code) VALUES($1, $2, $3)", userID, email, verificationCode)
return err
}
// Remove removes a user email. It returns an error if there is no such email associated with the user or the email
// is the user's primary address
func (s *userEmailsStore) Remove(ctx context.Context, userID int32, email string) (err error) {
tx, err := s.Transact(ctx)
if err != nil {
return err
}
defer func() { err = tx.Done(err) }()
// Get the email. It needs to exist and be verified.
var isPrimary bool
if err := tx.Handle().QueryRowContext(ctx, "SELECT is_primary FROM user_emails WHERE user_id=$1 AND email=$2",
userID, email,
).Scan(&isPrimary); err != nil {
return errors.Errorf("fetching email address: %w", err)
}
if isPrimary {
return errors.New("can't delete primary email address")
}
_, err = tx.Handle().ExecContext(ctx, "DELETE FROM user_emails WHERE user_id=$1 AND email=$2", userID, email)
if err != nil {
return err
}
return nil
}
// Verify verifies the user's email address given the email verification code. If the code is not
// correct (not the one originally used when creating the user or adding the user email), then it
// returns false.
func (s *userEmailsStore) Verify(ctx context.Context, userID int32, email, code string) (bool, error) {
var dbCode sql.NullString
if err := s.Handle().QueryRowContext(ctx, "SELECT verification_code FROM user_emails WHERE user_id=$1 AND email=$2", userID, email).Scan(&dbCode); err != nil {
return false, err
}
if !dbCode.Valid {
return false, errors.New("email already verified")
}
// 🚨 SECURITY: Use constant-time comparisons to avoid leaking the verification code via timing attack. It is not important to avoid leaking the *length* of the code, because the length of verification codes is constant.
if len(dbCode.String) != len(code) || subtle.ConstantTimeCompare([]byte(dbCode.String), []byte(code)) != 1 {
return false, nil
}
if _, err := s.Handle().ExecContext(ctx, "UPDATE user_emails SET verification_code=null, verified_at=now() WHERE user_id=$1 AND email=$2", userID, email); err != nil {
return false, err
}
return true, nil
}
// SetVerified bypasses the normal email verification code process and manually sets the verified
// status for an email.
func (s *userEmailsStore) SetVerified(ctx context.Context, userID int32, email string, verified bool) error {
var res sql.Result
var err error
if verified {
// Mark as verified.
res, err = s.Handle().ExecContext(ctx, "UPDATE user_emails SET verification_code=null, verified_at=now() WHERE user_id=$1 AND email=$2", userID, email)
} else {
// Mark as unverified.
res, err = s.Handle().ExecContext(ctx, "UPDATE user_emails SET verification_code=null, verified_at=null WHERE user_id=$1 AND email=$2", userID, email)
}
if err != nil {
return err
}
nrows, err := res.RowsAffected()
if err != nil {
return err
}
if nrows == 0 {
return errors.New("user email not found")
}
return nil
}
// SetLastVerification sets the "last_verification_sent_at" column to now() and updates the verification code for given email of the user.
func (s *userEmailsStore) SetLastVerification(ctx context.Context, userID int32, email, code string) error {
res, err := s.Handle().ExecContext(ctx, "UPDATE user_emails SET last_verification_sent_at=now(), verification_code = $3 WHERE user_id=$1 AND email=$2", userID, email, code)
if err != nil {
return err
}
nrows, err := res.RowsAffected()
if err != nil {
return err
}
if nrows == 0 {
return errors.New("user email not found")
}
return nil
}
// GetLatestVerificationSentEmail returns the email with the lastest time of "last_verification_sent_at" column,
// it excludes rows with "last_verification_sent_at IS NULL".
func (s *userEmailsStore) GetLatestVerificationSentEmail(ctx context.Context, email string) (*UserEmail, error) {
q := sqlf.Sprintf(`
WHERE email=%s AND last_verification_sent_at IS NOT NULL
ORDER BY last_verification_sent_at DESC
LIMIT 1
`, email)
emails, err := s.getBySQL(ctx, q.Query(sqlf.PostgresBindVar), q.Args()...)
if err != nil {
return nil, err
} else if len(emails) < 1 {
return nil, userEmailNotFoundError{[]any{fmt.Sprintf("email %q", email)}}
}
return emails[0], nil
}
// GetVerifiedEmails returns a list of verified emails from the candidate list. Some emails are excluded
// from the results list because of unverified or simply don't exist.
func (s *userEmailsStore) GetVerifiedEmails(ctx context.Context, emails ...string) ([]*UserEmail, error) {
if len(emails) == 0 {
return []*UserEmail{}, nil
}
items := make([]*sqlf.Query, len(emails))
for i := range emails {
items[i] = sqlf.Sprintf("%s", emails[i])
}
q := sqlf.Sprintf("WHERE email IN (%s) AND verified_at IS NOT NULL", sqlf.Join(items, ","))
return s.getBySQL(ctx, q.Query(sqlf.PostgresBindVar), q.Args()...)
}
// UserEmailsListOptions specifies the options for listing user emails.
type UserEmailsListOptions struct {
// UserID specifies the id of the user for listing emails.
UserID int32
// OnlyVerified excludes unverified emails from the list.
OnlyVerified bool
}
// ListByUser returns a list of emails that are associated to the given user.
func (s *userEmailsStore) ListByUser(ctx context.Context, opt UserEmailsListOptions) ([]*UserEmail, error) {
conds := []*sqlf.Query{
sqlf.Sprintf("user_id=%s", opt.UserID),
}
if opt.OnlyVerified {
conds = append(conds, sqlf.Sprintf("verified_at IS NOT NULL"))
}
q := sqlf.Sprintf("WHERE %s ORDER BY created_at ASC, email ASC", sqlf.Join(conds, "AND"))
return s.getBySQL(ctx, q.Query(sqlf.PostgresBindVar), q.Args()...)
}
// getBySQL returns user emails matching the SQL query, if any exist.
func (s *userEmailsStore) getBySQL(ctx context.Context, query string, args ...any) ([]*UserEmail, error) {
rows, err := s.Handle().QueryContext(ctx,
`SELECT user_emails.user_id, user_emails.email, user_emails.created_at, user_emails.verification_code,
user_emails.verified_at, user_emails.last_verification_sent_at, user_emails.is_primary FROM user_emails `+query, args...)
if err != nil {
return nil, err
}
var userEmails []*UserEmail
defer rows.Close()
for rows.Next() {
var v UserEmail
err := rows.Scan(&v.UserID, &v.Email, &v.CreatedAt, &v.VerificationCode, &v.VerifiedAt, &v.LastVerificationSentAt, &v.Primary)
if err != nil {
return nil, err
}
userEmails = append(userEmails, &v)
}
if err = rows.Err(); err != nil {
return nil, err
}
return userEmails, nil
}