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