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