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