internal/store/users.go
304 lines · 8261 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
49func (s *Store) UserByUsername(name string) (User, error) {
50 var u User
51 var admin, pending, disabled int
52 err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled FROM users WHERE username = ?", name).
53 Scan(&u.ID, &u.Username, &admin, &pending, &disabled)
54 if errors.Is(err, sql.ErrNoRows) {
55 return u, ErrNotFound
56 }
57 u.IsAdmin = admin != 0
58 u.Pending = pending != 0
59 u.Disabled = disabled != 0
60 return u, err
61}
62
63// UserEmailAddresses returns every address on the account, verified or not.
64func (s *Store) UserEmailAddresses(userID int64) ([]string, error) {
65 rows, err := s.DB.Query("SELECT address FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address", userID)
66 if err != nil {
67 return nil, err
68 }
69 defer rows.Close()
70 var out []string
71 for rows.Next() {
72 var a string
73 if err := rows.Scan(&a); err != nil {
74 return nil, err
75 }
76 out = append(out, a)
77 }
78 return out, rows.Err()
79}
80
81// SetUserDisabled suspends or restores an account. Disabling also drops
82// the user's web sessions; their keys and tokens stay registered but are
83// refused at every entry point until re-enabled.
84func (s *Store) SetUserDisabled(userID int64, disabled bool) error {
85 v := 0
86 if disabled {
87 v = 1
88 }
89 res, err := s.DB.Exec("UPDATE users SET disabled = ? WHERE id = ?", v, userID)
90 if err != nil {
91 return err
92 }
93 if n, _ := res.RowsAffected(); n == 0 {
94 return ErrNotFound
95 }
96 if disabled {
97 _, err = s.DB.Exec("DELETE FROM web_sessions WHERE user_id = ?", userID)
98 }
99 return err
100}
101
102func (s *Store) UserByID(id int64) (User, error) {
103 var u User
104 var admin, pending, disabled int
105 err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled FROM users WHERE id = ?", id).
106 Scan(&u.ID, &u.Username, &admin, &pending, &disabled)
107 if errors.Is(err, sql.ErrNoRows) {
108 return u, ErrNotFound
109 }
110 u.IsAdmin = admin != 0
111 u.Pending = pending != 0
112 u.Disabled = disabled != 0
113 return u, err
114}
115
116// AddSSHKey registers a key and bumps the key epoch in one transaction.
117func (s *Store) AddSSHKey(userID int64, fingerprint, algo string, blob []byte, scope string) error {
118 tx, err := s.DB.Begin()
119 if err != nil {
120 return err
121 }
122 defer tx.Rollback()
123 if _, err := tx.Exec(
124 "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, ?, ?, ?, ?)",
125 userID, fingerprint, algo, blob, scope); err != nil {
126 if isUniqueErr(err) {
127 return ErrDuplicateKey
128 }
129 return err
130 }
131 if err := bumpKeyEpoch(tx); err != nil {
132 return err
133 }
134 return tx.Commit()
135}
136
137// RemoveSSHKey removes a key owned by userID and bumps the key epoch.
138func (s *Store) RemoveSSHKey(userID int64, fingerprint string) error {
139 tx, err := s.DB.Begin()
140 if err != nil {
141 return err
142 }
143 defer tx.Rollback()
144 res, err := tx.Exec("DELETE FROM ssh_keys WHERE user_id = ? AND fingerprint = ?", userID, fingerprint)
145 if err != nil {
146 return err
147 }
148 if n, _ := res.RowsAffected(); n == 0 {
149 return ErrNotFound
150 }
151 if err := bumpKeyEpoch(tx); err != nil {
152 return err
153 }
154 return tx.Commit()
155}
156
157func (s *Store) SSHKeyByFingerprint(fingerprint string) (SSHKey, error) {
158 var k SSHKey
159 err := s.DB.QueryRow(
160 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE fingerprint = ?",
161 fingerprint).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope)
162 if errors.Is(err, sql.ErrNoRows) {
163 return k, ErrNotFound
164 }
165 return k, err
166}
167
168func (s *Store) ListSSHKeys(userID int64) ([]SSHKey, error) {
169 rows, err := s.DB.Query(
170 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE user_id = ? ORDER BY id",
171 userID)
172 if err != nil {
173 return nil, err
174 }
175 defer rows.Close()
176 var keys []SSHKey
177 for rows.Next() {
178 var k SSHKey
179 if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope); err != nil {
180 return nil, err
181 }
182 keys = append(keys, k)
183 }
184 return keys, rows.Err()
185}
186
187// TouchSSHKey records key use; best-effort, callers ignore the error.
188func (s *Store) TouchSSHKey(id int64) error {
189 _, err := s.DB.Exec(
190 "UPDATE ssh_keys SET last_used_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id)
191 return err
192}
193
194// AddEmail adds an address; verifiedBy is "" (unverified), "smtp", or "admin".
195// Adding an already-verified address bumps the key epoch: it is a trust input
196// for signature states.
197func (s *Store) AddEmail(userID int64, address, verifiedBy string, primary bool) error {
198 tx, err := s.DB.Begin()
199 if err != nil {
200 return err
201 }
202 defer tx.Rollback()
203 var vAt, vBy any
204 if verifiedBy != "" {
205 vAt = "now"
206 vBy = verifiedBy
207 }
208 _, err = tx.Exec(
209 `INSERT INTO emails (user_id, address, verified_at, verified_by, is_primary)
210 VALUES (?, ?, CASE WHEN ? IS NULL THEN NULL ELSE strftime('%Y-%m-%dT%H:%M:%fZ','now') END, ?, ?)`,
211 userID, address, vAt, vBy, boolInt(primary))
212 if isUniqueErr(err) {
213 return fmt.Errorf("address %q is already in use", address)
214 }
215 if err != nil {
216 return err
217 }
218 if verifiedBy != "" {
219 if err := bumpKeyEpoch(tx); err != nil {
220 return err
221 }
222 }
223 return tx.Commit()
224}
225
226func (s *Store) KeyEpoch() (int64, error) {
227 var v int64
228 err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&v)
229 return v, err
230}
231
232type execer interface {
233 Exec(query string, args ...any) (sql.Result, error)
234}
235
236func bumpKeyEpoch(tx execer) error {
237 _, err := tx.Exec("UPDATE settings SET value = value + 1 WHERE key = 'key_epoch'")
238 return err
239}
240
241func boolInt(b bool) int {
242 if b {
243 return 1
244 }
245 return 0
246}
247
248func isUniqueErr(err error) bool {
249 return err != nil && strings.Contains(err.Error(), "UNIQUE constraint failed")
250}
251
252func (s *Store) SSHKeyByID(id int64) (SSHKey, error) {
253 var k SSHKey
254 err := s.DB.QueryRow(
255 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE id = ?",
256 id).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
263// ListDeployKeys returns the deploy keys bound to a repository.
264func (s *Store) ListDeployKeys(repoID int64) ([]SSHKey, error) {
265 rows, err := s.DB.Query(
266 "SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE scope LIKE 'deploy:' || ? || ':%' ORDER BY id",
267 repoID)
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); err != nil {
276 return nil, err
277 }
278 keys = append(keys, k)
279 }
280 return keys, rows.Err()
281}
282
283// RemoveDeployKey removes a deploy key from a repository by fingerprint;
284// any repo admin may remove it regardless of who added it.
285func (s *Store) RemoveDeployKey(repoID int64, fingerprint string) error {
286 tx, err := s.DB.Begin()
287 if err != nil {
288 return err
289 }
290 defer tx.Rollback()
291 res, err := tx.Exec(
292 "DELETE FROM ssh_keys WHERE fingerprint = ? AND scope LIKE 'deploy:' || ? || ':%'",
293 fingerprint, repoID)
294 if err != nil {
295 return err
296 }
297 if n, _ := res.RowsAffected(); n == 0 {
298 return ErrNotFound
299 }
300 if err := bumpKeyEpoch(tx); err != nil {
301 return err
302 }
303 return tx.Commit()
304}