internal/store/users.go
641 lines · 19694 bytes
1package store
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "strings"
8 "time"
9)
10
11type User struct {
12 ID int64
13 Username string
14 IsAdmin bool
15 Pending bool // self-registered, email not yet verified
16 Disabled bool // administratively suspended
17 // SignedInAt is when the browser session this user came from was
18 // created by a login. Set by WebSessionUser only; zero elsewhere.
19 SignedInAt time.Time
20}
21
22type SSHKey struct {
23 ID int64
24 UserID int64
25 Fingerprint string
26 Algo string
27 Blob []byte
28 Scope string
29 Label string // "" when the key was added with no name
30 CreatedAt string
31 LastUsedAt string // "" when the key has never authenticated
32 CreatedBy string // name of the API token that added the key; "" for none. ListSSHKeys only.
33 ExpiresAt *time.Time // nil when the key never expires
34}
35
36// Expired reports whether the key has lapsed at now.
37func (k SSHKey) Expired(now time.Time) bool {
38 return k.ExpiresAt != nil && !k.ExpiresAt.After(now)
39}
40
41// ErrDuplicateKey carries the exact user-facing message from the spec. It
42// deliberately does not name the owning account (enumeration oracle).
43var ErrDuplicateKey = errors.New("that key is already registered to another account; remove it there first or use a different key")
44
45var ErrNotFound = errors.New("not found")
46
47func (s *Store) CreateUser(username string, isAdmin bool) (int64, error) {
48 if taken, err := ownerNameTaken(s.DB, username); err != nil {
49 return 0, err
50 } else if taken {
51 return 0, fmt.Errorf("username %q is taken", username)
52 }
53 res, err := s.DB.Exec("INSERT INTO users (username, is_admin) VALUES (?, ?)", username, boolInt(isAdmin))
54 if err != nil {
55 if isUniqueErr(err) {
56 return 0, fmt.Errorf("username %q is taken", username)
57 }
58 return 0, err
59 }
60 return res.LastInsertId()
61}
62
63// DeleteUser removes an account whose removal orphans nothing: no owned
64// repositories, no authored issues, MRs, comments, or reviews, and not the
65// only admin of an org. Everything else (keys, emails, sessions, tokens,
66// pins, memberships, activity) cascades. Blockers come back as an error
67// naming what stands in the way, so the operator can transfer, delete, or
68// disable instead.
69func (s *Store) DeleteUser(id int64) error {
70 var blockers []string
71 var checkErr error
72 count := func(q string, what string) {
73 var n int
74 if err := s.DB.QueryRow(q, id).Scan(&n); err != nil {
75 if checkErr == nil {
76 checkErr = fmt.Errorf("checking %s: %w", what, err)
77 }
78 return
79 }
80 if n > 0 {
81 blockers = append(blockers, fmt.Sprintf("%d %s", n, what))
82 }
83 }
84 count("SELECT COUNT(*) FROM repos WHERE owner_kind = 'user' AND owner_id = ?", "owned repositories")
85 count("SELECT COUNT(*) FROM issues WHERE author_id = ?", "authored issues")
86 count("SELECT COUNT(*) FROM merge_requests WHERE author_id = ?", "authored merge requests")
87 count("SELECT COUNT(*) FROM issue_comments WHERE author_id = ?", "issue comments")
88 count("SELECT COUNT(*) FROM mr_comments WHERE author_id = ?", "MR comments")
89 count("SELECT COUNT(*) FROM mr_diff_comments WHERE author_id = ?", "diff comments")
90 count("SELECT COUNT(*) FROM mr_reviews WHERE reviewer_id = ?", "reviews")
91 count(`SELECT COUNT(*) FROM org_members m WHERE m.user_id = ? AND m.role = 'admin'
92 AND NOT EXISTS (SELECT 1 FROM org_members o
93 WHERE o.org_id = m.org_id AND o.role = 'admin' AND o.user_id != m.user_id)`,
94 "organizations with no other admin")
95 if checkErr != nil {
96 return checkErr
97 }
98 if len(blockers) > 0 {
99 return fmt.Errorf("account still anchors: %s — transfer or delete those first, or disable the account instead",
100 strings.Join(blockers, ", "))
101 }
102 res, err := s.DB.Exec("DELETE FROM users WHERE id = ?", id)
103 if err != nil {
104 return err
105 }
106 if n, _ := res.RowsAffected(); n == 0 {
107 return ErrNotFound
108 }
109 s.announce(Revoked{UserID: id})
110 return nil
111}
112
113// OwnerExists reports whether a user or org owns the name — the ACME host
114// policy check for pages subdomains.
115func (s *Store) OwnerExists(name string) bool {
116 var n int
117 s.DB.QueryRow(`SELECT (SELECT COUNT(*) FROM users WHERE username = ?1)
118 + (SELECT COUNT(*) FROM orgs WHERE name = ?1)`, name).Scan(&n)
119 return n > 0
120}
121
122func (s *Store) UserByUsername(name string) (User, error) {
123 var u User
124 var admin, pending, disabled int
125 err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled FROM users WHERE username = ?", name).
126 Scan(&u.ID, &u.Username, &admin, &pending, &disabled)
127 if errors.Is(err, sql.ErrNoRows) {
128 return u, ErrNotFound
129 }
130 u.IsAdmin = admin != 0
131 u.Pending = pending != 0
132 u.Disabled = disabled != 0
133 return u, err
134}
135
136// UserEmailAddresses returns every address on the account, verified or not.
137func (s *Store) UserEmailAddresses(userID int64) ([]string, error) {
138 rows, err := s.DB.Query("SELECT address FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address", userID)
139 if err != nil {
140 return nil, err
141 }
142 defer rows.Close()
143 var out []string
144 for rows.Next() {
145 var a string
146 if err := rows.Scan(&a); err != nil {
147 return nil, err
148 }
149 out = append(out, a)
150 }
151 return out, rows.Err()
152}
153
154// Email is one address on an account, with the state the signature rules
155// and notification routing depend on.
156type Email struct {
157 Address string
158 Verified bool
159 VerifiedBy string // smtp | admin, empty when unverified
160 Primary bool
161}
162
163// ListEmails returns every address on the account with its state.
164func (s *Store) ListEmails(userID int64) ([]Email, error) {
165 rows, err := s.DB.Query(`SELECT address, verified_at IS NOT NULL,
166 COALESCE(verified_by, ''), is_primary
167 FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address`, userID)
168 if err != nil {
169 return nil, err
170 }
171 defer rows.Close()
172 var out []Email
173 for rows.Next() {
174 var e Email
175 if err := rows.Scan(&e.Address, &e.Verified, &e.VerifiedBy, &e.Primary); err != nil {
176 return nil, err
177 }
178 out = append(out, e)
179 }
180 return out, rows.Err()
181}
182
183// SetUserDisabled suspends or restores an account. Disabling drops every
184// credential that would grant a session on its own — web sessions, API
185// tokens, unclaimed login links — and leaves the SSH keys registered but
186// refused at every entry point until re-enabled; connections they opened
187// are closed.
188func (s *Store) SetUserDisabled(userID int64, disabled bool) error {
189 v := 0
190 if disabled {
191 v = 1
192 }
193 res, err := s.DB.Exec("UPDATE users SET disabled = ? WHERE id = ?", v, userID)
194 if err != nil {
195 return err
196 }
197 if n, _ := res.RowsAffected(); n == 0 {
198 return ErrNotFound
199 }
200 if disabled {
201 // A pending login link is a session in waiting, so it goes with
202 // the sessions and API tokens. Re-enabling means minting again.
203 for _, table := range []string{"web_sessions", "api_tokens", "login_tokens"} {
204 if _, err = s.DB.Exec("DELETE FROM "+table+" WHERE user_id = ?", userID); err != nil {
205 return err
206 }
207 }
208 s.announce(Revoked{UserID: userID})
209 }
210 return err
211}
212
213// MailEnabled reports whether activity notifications reach the account
214// by mail as well as the inbox.
215func (s *Store) MailEnabled(userID int64) (bool, error) {
216 var on int
217 err := s.DB.QueryRow("SELECT notify_mail FROM users WHERE id = ?", userID).Scan(&on)
218 if errors.Is(err, sql.ErrNoRows) {
219 return false, ErrNotFound
220 }
221 return on != 0, err
222}
223
224func (s *Store) SetMailEnabled(userID int64, on bool) error {
225 v := 0
226 if on {
227 v = 1
228 }
229 _, err := s.DB.Exec("UPDATE users SET notify_mail = ? WHERE id = ?", v, userID)
230 return err
231}
232
233// ReplyEnabled reports whether the account's issue and merge request
234// mail carries a reply address (#295).
235func (s *Store) ReplyEnabled(userID int64) (bool, error) {
236 var on int
237 err := s.DB.QueryRow("SELECT notify_reply FROM users WHERE id = ?", userID).Scan(&on)
238 if errors.Is(err, sql.ErrNoRows) {
239 return false, ErrNotFound
240 }
241 return on != 0, err
242}
243
244func (s *Store) SetReplyEnabled(userID int64, on bool) error {
245 v := 0
246 if on {
247 v = 1
248 }
249 _, err := s.DB.Exec("UPDATE users SET notify_reply = ? WHERE id = ?", v, userID)
250 return err
251}
252
253// WatchEnabled reports whether the account hears about every issue and
254// merge request on the repositories it can write to, without a
255// repo_watchers row on each (#194).
256func (s *Store) WatchEnabled(userID int64) (bool, error) {
257 var on int
258 err := s.DB.QueryRow("SELECT notify_watch FROM users WHERE id = ?", userID).Scan(&on)
259 if errors.Is(err, sql.ErrNoRows) {
260 return false, ErrNotFound
261 }
262 return on != 0, err
263}
264
265func (s *Store) SetWatchEnabled(userID int64, on bool) error {
266 v := 0
267 if on {
268 v = 1
269 }
270 _, err := s.DB.Exec("UPDATE users SET notify_watch = ? WHERE id = ?", v, userID)
271 return err
272}
273
274// Theme is the web colour scheme the account chose: system, light or
275// dark (#232).
276func (s *Store) Theme(userID int64) (string, error) {
277 var theme string
278 err := s.DB.QueryRow("SELECT theme FROM users WHERE id = ?", userID).Scan(&theme)
279 if errors.Is(err, sql.ErrNoRows) {
280 return "", ErrNotFound
281 }
282 return theme, err
283}
284
285func (s *Store) SetTheme(userID int64, theme string) error {
286 _, err := s.DB.Exec("UPDATE users SET theme = ? WHERE id = ?", theme, userID)
287 return err
288}
289
290// DiffLayout is how the account wants diffs drawn on the web: unified or
291// split (#290).
292func (s *Store) DiffLayout(userID int64) (string, error) {
293 var l string
294 err := s.DB.QueryRow("SELECT diff_layout FROM users WHERE id = ?", userID).Scan(&l)
295 if errors.Is(err, sql.ErrNoRows) {
296 return "", ErrNotFound
297 }
298 return l, err
299}
300
301func (s *Store) SetDiffLayout(userID int64, layout string) error {
302 _, err := s.DB.Exec("UPDATE users SET diff_layout = ? WHERE id = ?", layout, userID)
303 return err
304}
305
306func (s *Store) UserByID(id int64) (User, error) {
307 var u User
308 var admin, pending, disabled int
309 err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled FROM users WHERE id = ?", id).
310 Scan(&u.ID, &u.Username, &admin, &pending, &disabled)
311 if errors.Is(err, sql.ErrNoRows) {
312 return u, ErrNotFound
313 }
314 u.IsAdmin = admin != 0
315 u.Pending = pending != 0
316 u.Disabled = disabled != 0
317 return u, err
318}
319
320// KeyOrigin is how a key came to be.
321type KeyOrigin struct {
322 CreatedByToken int64 // the API token that added it; 0 for none
323 ExpiresAt *time.Time // when it stops authenticating; nil for never
324}
325
326// AddSSHKey registers a key and bumps the key epoch in one transaction.
327func (s *Store) AddSSHKey(userID int64, fingerprint, algo string, blob []byte, scope, label string) error {
328 return s.AddSSHKeyFrom(userID, fingerprint, algo, blob, scope, label, KeyOrigin{})
329}
330
331// AddSSHKeyFrom is AddSSHKey recording where the key came from.
332func (s *Store) AddSSHKeyFrom(userID int64, fingerprint, algo string, blob []byte, scope, label string, o KeyOrigin) error {
333 tx, err := s.DB.Begin()
334 if err != nil {
335 return err
336 }
337 defer tx.Rollback()
338 var exp any
339 if o.ExpiresAt != nil {
340 exp = fmtTime(*o.ExpiresAt)
341 }
342 if _, err := tx.Exec(
343 "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope, label, created_by_token, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
344 userID, fingerprint, algo, blob, scope, label, nullID(o.CreatedByToken), exp); err != nil {
345 if isUniqueErr(err) {
346 return ErrDuplicateKey
347 }
348 return err
349 }
350 if err := bumpKeyEpoch(tx); err != nil {
351 return err
352 }
353 return tx.Commit()
354}
355
356// RemoveSSHKey removes a key owned by userID, bumps the key epoch, and
357// announces the revocation.
358func (s *Store) RemoveSSHKey(userID int64, fingerprint string) error {
359 tx, err := s.DB.Begin()
360 if err != nil {
361 return err
362 }
363 defer tx.Rollback()
364 var id int64
365 err = tx.QueryRow("DELETE FROM ssh_keys WHERE user_id = ? AND fingerprint = ? RETURNING id", userID, fingerprint).Scan(&id)
366 if errors.Is(err, sql.ErrNoRows) {
367 return ErrNotFound
368 }
369 if err != nil {
370 return err
371 }
372 if err := bumpKeyEpoch(tx); err != nil {
373 return err
374 }
375 if err := tx.Commit(); err != nil {
376 return err
377 }
378 s.announce(Revoked{KeyIDs: []int64{id}})
379 return nil
380}
381
382// SetSSHKeyLabel renames a key owned by userID. Labels do not touch the
383// key epoch: nothing about authentication changes.
384func (s *Store) SetSSHKeyLabel(userID int64, fingerprint, label string) error {
385 res, err := s.DB.Exec("UPDATE ssh_keys SET label = ? WHERE user_id = ? AND fingerprint = ?", label, userID, fingerprint)
386 if err != nil {
387 return err
388 }
389 if n, _ := res.RowsAffected(); n == 0 {
390 return ErrNotFound
391 }
392 return nil
393}
394
395func (s *Store) SSHKeyByFingerprint(fingerprint string) (SSHKey, error) {
396 var k SSHKey
397 var exp sql.NullString
398 err := s.DB.QueryRow(
399 "SELECT id, user_id, fingerprint, algo, blob, scope, label, expires_at FROM ssh_keys WHERE fingerprint = ?",
400 fingerprint).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &exp)
401 if errors.Is(err, sql.ErrNoRows) {
402 return k, ErrNotFound
403 }
404 k.ExpiresAt = parseTime(exp)
405 return k, err
406}
407
408func (s *Store) ListSSHKeys(userID int64) ([]SSHKey, error) {
409 rows, err := s.DB.Query(
410 `SELECT k.id, k.user_id, k.fingerprint, k.algo, k.blob, k.scope, k.label, k.created_at,
411 COALESCE(k.last_used_at, ''), COALESCE(t.name, ''), k.expires_at
412 FROM ssh_keys k LEFT JOIN api_tokens t ON t.id = k.created_by_token
413 WHERE k.user_id = ? ORDER BY k.id`,
414 userID)
415 if err != nil {
416 return nil, err
417 }
418 defer rows.Close()
419 var keys []SSHKey
420 for rows.Next() {
421 var k SSHKey
422 var exp sql.NullString
423 if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &k.CreatedAt, &k.LastUsedAt, &k.CreatedBy, &exp); err != nil {
424 return nil, err
425 }
426 k.ExpiresAt = parseTime(exp)
427 keys = append(keys, k)
428 }
429 return keys, rows.Err()
430}
431
432// TouchSSHKey records key use; best-effort, callers ignore the error.
433func (s *Store) TouchSSHKey(id int64) error {
434 _, err := s.DB.Exec(
435 "UPDATE ssh_keys SET last_used_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id)
436 return err
437}
438
439// AddEmail adds an address; verifiedBy is "" (unverified), "smtp", or "admin".
440// Adding an already-verified address bumps the key epoch: it is a trust input
441// for signature states.
442func (s *Store) AddEmail(userID int64, address, verifiedBy string, primary bool) error {
443 tx, err := s.DB.Begin()
444 if err != nil {
445 return err
446 }
447 defer tx.Rollback()
448 var vAt, vBy any
449 if verifiedBy != "" {
450 vAt = "now"
451 vBy = verifiedBy
452 }
453 _, err = tx.Exec(
454 `INSERT INTO emails (user_id, address, verified_at, verified_by, is_primary)
455 VALUES (?, ?, CASE WHEN ? IS NULL THEN NULL ELSE strftime('%Y-%m-%dT%H:%M:%fZ','now') END, ?, ?)`,
456 userID, address, vAt, vBy, boolInt(primary))
457 if isUniqueErr(err) {
458 return fmt.Errorf("address %q is already in use", address)
459 }
460 if err != nil {
461 return err
462 }
463 if verifiedBy != "" {
464 if err := bumpKeyEpoch(tx); err != nil {
465 return err
466 }
467 }
468 return tx.Commit()
469}
470
471var (
472 ErrPrimaryEmail = errors.New("that is the primary address; make another address primary first")
473 ErrLastVerifiedEmail = errors.New("that is the only verified address on the account; verify another first")
474 ErrUnverifiedEmail = errors.New("that address is not verified")
475)
476
477// RemoveEmail drops an address from the account, and any verification
478// code pending for it. The primary and the last verified address stay:
479// activation, login links and commit identity all resolve through
480// verified addresses. Removing a verified address bumps the key epoch,
481// since the signature cache keys on verified addresses too.
482func (s *Store) RemoveEmail(userID int64, address string) error {
483 tx, err := s.DB.Begin()
484 if err != nil {
485 return err
486 }
487 defer tx.Rollback()
488 var primary, verified bool
489 err = tx.QueryRow("SELECT is_primary, verified_at IS NOT NULL FROM emails WHERE user_id = ? AND address = ?",
490 userID, address).Scan(&primary, &verified)
491 if errors.Is(err, sql.ErrNoRows) {
492 return ErrNotFound
493 }
494 if err != nil {
495 return err
496 }
497 if primary {
498 return ErrPrimaryEmail
499 }
500 if verified {
501 var others int
502 if err := tx.QueryRow("SELECT count(*) FROM emails WHERE user_id = ? AND verified_at IS NOT NULL AND address != ?",
503 userID, address).Scan(&others); err != nil {
504 return err
505 }
506 if others == 0 {
507 return ErrLastVerifiedEmail
508 }
509 }
510 if _, err := tx.Exec("DELETE FROM email_tokens WHERE user_id = ? AND address = ?", userID, address); err != nil {
511 return err
512 }
513 if _, err := tx.Exec("DELETE FROM emails WHERE user_id = ? AND address = ?", userID, address); err != nil {
514 return err
515 }
516 if verified {
517 if err := bumpKeyEpoch(tx); err != nil {
518 return err
519 }
520 }
521 return tx.Commit()
522}
523
524// SetPrimaryEmail makes a verified address the account's primary. The
525// verified set is unchanged, so the key epoch is not.
526func (s *Store) SetPrimaryEmail(userID int64, address string) error {
527 tx, err := s.DB.Begin()
528 if err != nil {
529 return err
530 }
531 defer tx.Rollback()
532 var verified bool
533 err = tx.QueryRow("SELECT verified_at IS NOT NULL FROM emails WHERE user_id = ? AND address = ?",
534 userID, address).Scan(&verified)
535 if errors.Is(err, sql.ErrNoRows) {
536 return ErrNotFound
537 }
538 if err != nil {
539 return err
540 }
541 if !verified {
542 return ErrUnverifiedEmail
543 }
544 if _, err := tx.Exec("UPDATE emails SET is_primary = 0 WHERE user_id = ?", userID); err != nil {
545 return err
546 }
547 if _, err := tx.Exec("UPDATE emails SET is_primary = 1 WHERE user_id = ? AND address = ?", userID, address); err != nil {
548 return err
549 }
550 return tx.Commit()
551}
552
553func (s *Store) KeyEpoch() (int64, error) {
554 var v int64
555 err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&v)
556 return v, err
557}
558
559type execer interface {
560 Exec(query string, args ...any) (sql.Result, error)
561}
562
563func bumpKeyEpoch(tx execer) error {
564 _, err := tx.Exec("UPDATE settings SET value = value + 1 WHERE key = 'key_epoch'")
565 return err
566}
567
568func boolInt(b bool) int {
569 if b {
570 return 1
571 }
572 return 0
573}
574
575func isUniqueErr(err error) bool {
576 return err != nil && strings.Contains(err.Error(), "UNIQUE constraint failed")
577}
578
579func (s *Store) SSHKeyByID(id int64) (SSHKey, error) {
580 var k SSHKey
581 var exp sql.NullString
582 err := s.DB.QueryRow(
583 "SELECT id, user_id, fingerprint, algo, blob, scope, label, expires_at FROM ssh_keys WHERE id = ?",
584 id).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &exp)
585 if errors.Is(err, sql.ErrNoRows) {
586 return k, ErrNotFound
587 }
588 k.ExpiresAt = parseTime(exp)
589 return k, err
590}
591
592// ListDeployKeys returns the deploy keys bound to a repository.
593func (s *Store) ListDeployKeys(repoID int64) ([]SSHKey, error) {
594 rows, err := s.DB.Query(
595 `SELECT id, user_id, fingerprint, algo, blob, scope, label, COALESCE(last_used_at, ''), expires_at
596 FROM ssh_keys WHERE scope LIKE 'deploy:' || ? || ':%' ORDER BY id`,
597 repoID)
598 if err != nil {
599 return nil, err
600 }
601 defer rows.Close()
602 var keys []SSHKey
603 for rows.Next() {
604 var k SSHKey
605 var exp sql.NullString
606 if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &k.LastUsedAt, &exp); err != nil {
607 return nil, err
608 }
609 k.ExpiresAt = parseTime(exp)
610 keys = append(keys, k)
611 }
612 return keys, rows.Err()
613}
614
615// RemoveDeployKey removes a deploy key from a repository by fingerprint;
616// any repo admin may remove it regardless of who added it.
617func (s *Store) RemoveDeployKey(repoID int64, fingerprint string) error {
618 tx, err := s.DB.Begin()
619 if err != nil {
620 return err
621 }
622 defer tx.Rollback()
623 var id int64
624 err = tx.QueryRow(
625 "DELETE FROM ssh_keys WHERE fingerprint = ? AND scope LIKE 'deploy:' || ? || ':%' RETURNING id",
626 fingerprint, repoID).Scan(&id)
627 if errors.Is(err, sql.ErrNoRows) {
628 return ErrNotFound
629 }
630 if err != nil {
631 return err
632 }
633 if err := bumpKeyEpoch(tx); err != nil {
634 return err
635 }
636 if err := tx.Commit(); err != nil {
637 return err
638 }
639 s.announce(Revoked{KeyIDs: []int64{id}})
640 return nil
641}