internal/store/users.go
400 lines · 11806 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 also drops
171// the user's web sessions; their keys and tokens stay registered but are
172// refused at every entry point until re-enabled.
173func (s *Store) SetUserDisabled(userID int64, disabled bool) error {
174 v := 0
175 if disabled {
176 v = 1
177 }
178 res, err := s.DB.Exec("UPDATE users SET disabled = ? WHERE id = ?", v, userID)
179 if err != nil {
180 return err
181 }
182 if n, _ := res.RowsAffected(); n == 0 {
183 return ErrNotFound
184 }
185 if disabled {
186 // Every credential the account holds goes with it: browser
187 // sessions and API tokens. Re-enabling means minting again.
188 for _, table := range []string{"web_sessions", "api_tokens"} {
189 if _, err = s.DB.Exec("DELETE FROM "+table+" WHERE user_id = ?", userID); err != nil {
190 return err
191 }
192 }
193 }
194 return err
195}
196
197func (s *Store) UserByID(id int64) (User, error) {
198 var u User
199 var admin, pending, disabled int
200 err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled FROM users WHERE id = ?", id).
201 Scan(&u.ID, &u.Username, &admin, &pending, &disabled)
202 if errors.Is(err, sql.ErrNoRows) {
203 return u, ErrNotFound
204 }
205 u.IsAdmin = admin != 0
206 u.Pending = pending != 0
207 u.Disabled = disabled != 0
208 return u, err
209}
210
211// AddSSHKey registers a key and bumps the key epoch in one transaction.
212func (s *Store) AddSSHKey(userID int64, fingerprint, algo string, blob []byte, scope string) error {
213 tx, err := s.DB.Begin()
214 if err != nil {
215 return err
216 }
217 defer tx.Rollback()
218 if _, err := tx.Exec(
219 "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, ?, ?, ?, ?)",
220 userID, fingerprint, algo, blob, scope); err != nil {
221 if isUniqueErr(err) {
222 return ErrDuplicateKey
223 }
224 return err
225 }
226 if err := bumpKeyEpoch(tx); err != nil {
227 return err
228 }
229 return tx.Commit()
230}
231
232// RemoveSSHKey removes a key owned by userID and bumps the key epoch.
233func (s *Store) RemoveSSHKey(userID int64, fingerprint string) error {
234 tx, err := s.DB.Begin()
235 if err != nil {
236 return err
237 }
238 defer tx.Rollback()
239 res, err := tx.Exec("DELETE FROM ssh_keys WHERE user_id = ? AND fingerprint = ?", userID, fingerprint)
240 if err != nil {
241 return err
242 }
243 if n, _ := res.RowsAffected(); n == 0 {
244 return ErrNotFound
245 }
246 if err := bumpKeyEpoch(tx); err != nil {
247 return err
248 }
249 return tx.Commit()
250}
251
252func (s *Store) SSHKeyByFingerprint(fingerprint string) (SSHKey, error) {
253 var k SSHKey
254 err := s.DB.QueryRow(
255 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE fingerprint = ?",
256 fingerprint).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope)
257 if errors.Is(err, sql.ErrNoRows) {
258 return k, ErrNotFound
259 }
260 return k, err
261}
262
263func (s *Store) ListSSHKeys(userID int64) ([]SSHKey, error) {
264 rows, err := s.DB.Query(
265 `SELECT id, user_id, fingerprint, algo, blob, scope, created_at, COALESCE(last_used_at, '')
266 FROM ssh_keys WHERE user_id = ? ORDER BY id`,
267 userID)
268 if err != nil {
269 return nil, err
270 }
271 defer rows.Close()
272 var keys []SSHKey
273 for rows.Next() {
274 var k SSHKey
275 if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.CreatedAt, &k.LastUsedAt); err != nil {
276 return nil, err
277 }
278 keys = append(keys, k)
279 }
280 return keys, rows.Err()
281}
282
283// TouchSSHKey records key use; best-effort, callers ignore the error.
284func (s *Store) TouchSSHKey(id int64) error {
285 _, err := s.DB.Exec(
286 "UPDATE ssh_keys SET last_used_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id)
287 return err
288}
289
290// AddEmail adds an address; verifiedBy is "" (unverified), "smtp", or "admin".
291// Adding an already-verified address bumps the key epoch: it is a trust input
292// for signature states.
293func (s *Store) AddEmail(userID int64, address, verifiedBy string, primary bool) error {
294 tx, err := s.DB.Begin()
295 if err != nil {
296 return err
297 }
298 defer tx.Rollback()
299 var vAt, vBy any
300 if verifiedBy != "" {
301 vAt = "now"
302 vBy = verifiedBy
303 }
304 _, err = tx.Exec(
305 `INSERT INTO emails (user_id, address, verified_at, verified_by, is_primary)
306 VALUES (?, ?, CASE WHEN ? IS NULL THEN NULL ELSE strftime('%Y-%m-%dT%H:%M:%fZ','now') END, ?, ?)`,
307 userID, address, vAt, vBy, boolInt(primary))
308 if isUniqueErr(err) {
309 return fmt.Errorf("address %q is already in use", address)
310 }
311 if err != nil {
312 return err
313 }
314 if verifiedBy != "" {
315 if err := bumpKeyEpoch(tx); err != nil {
316 return err
317 }
318 }
319 return tx.Commit()
320}
321
322func (s *Store) KeyEpoch() (int64, error) {
323 var v int64
324 err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&v)
325 return v, err
326}
327
328type execer interface {
329 Exec(query string, args ...any) (sql.Result, error)
330}
331
332func bumpKeyEpoch(tx execer) error {
333 _, err := tx.Exec("UPDATE settings SET value = value + 1 WHERE key = 'key_epoch'")
334 return err
335}
336
337func boolInt(b bool) int {
338 if b {
339 return 1
340 }
341 return 0
342}
343
344func isUniqueErr(err error) bool {
345 return err != nil && strings.Contains(err.Error(), "UNIQUE constraint failed")
346}
347
348func (s *Store) SSHKeyByID(id int64) (SSHKey, error) {
349 var k SSHKey
350 err := s.DB.QueryRow(
351 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE id = ?",
352 id).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope)
353 if errors.Is(err, sql.ErrNoRows) {
354 return k, ErrNotFound
355 }
356 return k, err
357}
358
359// ListDeployKeys returns the deploy keys bound to a repository.
360func (s *Store) ListDeployKeys(repoID int64) ([]SSHKey, error) {
361 rows, err := s.DB.Query(
362 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE scope LIKE 'deploy:' || ? || ':%' ORDER BY id",
363 repoID)
364 if err != nil {
365 return nil, err
366 }
367 defer rows.Close()
368 var keys []SSHKey
369 for rows.Next() {
370 var k SSHKey
371 if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope); err != nil {
372 return nil, err
373 }
374 keys = append(keys, k)
375 }
376 return keys, rows.Err()
377}
378
379// RemoveDeployKey removes a deploy key from a repository by fingerprint;
380// any repo admin may remove it regardless of who added it.
381func (s *Store) RemoveDeployKey(repoID int64, fingerprint string) error {
382 tx, err := s.DB.Begin()
383 if err != nil {
384 return err
385 }
386 defer tx.Rollback()
387 res, err := tx.Exec(
388 "DELETE FROM ssh_keys WHERE fingerprint = ? AND scope LIKE 'deploy:' || ? || ':%'",
389 fingerprint, repoID)
390 if err != nil {
391 return err
392 }
393 if n, _ := res.RowsAffected(); n == 0 {
394 return ErrNotFound
395 }
396 if err := bumpKeyEpoch(tx); err != nil {
397 return err
398 }
399 return tx.Commit()
400}