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