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