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