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