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