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