krz/gitbay

  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}
 16
 17type SSHKey struct {
 18	ID          int64
 19	UserID      int64
 20	Fingerprint string
 21	Algo        string
 22	Blob        []byte
 23	Scope       string
 24}
 25
 26// ErrDuplicateKey carries the exact user-facing message from the spec. It
 27// deliberately does not name the owning account (enumeration oracle).
 28var ErrDuplicateKey = errors.New("that key is already registered to another account; remove it there first or use a different key")
 29
 30var ErrNotFound = errors.New("not found")
 31
 32func (s *Store) CreateUser(username string, isAdmin bool) (int64, error) {
 33	if taken, err := ownerNameTaken(s.DB, username); err != nil {
 34		return 0, err
 35	} else if taken {
 36		return 0, fmt.Errorf("username %q is taken", username)
 37	}
 38	res, err := s.DB.Exec("INSERT INTO users (username, is_admin) VALUES (?, ?)", username, boolInt(isAdmin))
 39	if err != nil {
 40		if isUniqueErr(err) {
 41			return 0, fmt.Errorf("username %q is taken", username)
 42		}
 43		return 0, err
 44	}
 45	return res.LastInsertId()
 46}
 47
 48func (s *Store) UserByUsername(name string) (User, error) {
 49	var u User
 50	var admin, pending int
 51	err := s.DB.QueryRow("SELECT id, username, is_admin, pending FROM users WHERE username = ?", name).
 52		Scan(&u.ID, &u.Username, &admin, &pending)
 53	if errors.Is(err, sql.ErrNoRows) {
 54		return u, ErrNotFound
 55	}
 56	u.IsAdmin = admin != 0
 57	u.Pending = pending != 0
 58	return u, err
 59}
 60
 61func (s *Store) UserByID(id int64) (User, error) {
 62	var u User
 63	var admin, pending int
 64	err := s.DB.QueryRow("SELECT id, username, is_admin, pending FROM users WHERE id = ?", id).
 65		Scan(&u.ID, &u.Username, &admin, &pending)
 66	if errors.Is(err, sql.ErrNoRows) {
 67		return u, ErrNotFound
 68	}
 69	u.IsAdmin = admin != 0
 70	u.Pending = pending != 0
 71	return u, err
 72}
 73
 74// AddSSHKey registers a key and bumps the key epoch in one transaction.
 75func (s *Store) AddSSHKey(userID int64, fingerprint, algo string, blob []byte, scope string) error {
 76	tx, err := s.DB.Begin()
 77	if err != nil {
 78		return err
 79	}
 80	defer tx.Rollback()
 81	if _, err := tx.Exec(
 82		"INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, ?, ?, ?, ?)",
 83		userID, fingerprint, algo, blob, scope); err != nil {
 84		if isUniqueErr(err) {
 85			return ErrDuplicateKey
 86		}
 87		return err
 88	}
 89	if err := bumpKeyEpoch(tx); err != nil {
 90		return err
 91	}
 92	return tx.Commit()
 93}
 94
 95// RemoveSSHKey removes a key owned by userID and bumps the key epoch.
 96func (s *Store) RemoveSSHKey(userID int64, fingerprint string) error {
 97	tx, err := s.DB.Begin()
 98	if err != nil {
 99		return err
100	}
101	defer tx.Rollback()
102	res, err := tx.Exec("DELETE FROM ssh_keys WHERE user_id = ? AND fingerprint = ?", userID, fingerprint)
103	if err != nil {
104		return err
105	}
106	if n, _ := res.RowsAffected(); n == 0 {
107		return ErrNotFound
108	}
109	if err := bumpKeyEpoch(tx); err != nil {
110		return err
111	}
112	return tx.Commit()
113}
114
115func (s *Store) SSHKeyByFingerprint(fingerprint string) (SSHKey, error) {
116	var k SSHKey
117	err := s.DB.QueryRow(
118		"SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE fingerprint = ?",
119		fingerprint).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope)
120	if errors.Is(err, sql.ErrNoRows) {
121		return k, ErrNotFound
122	}
123	return k, err
124}
125
126func (s *Store) ListSSHKeys(userID int64) ([]SSHKey, error) {
127	rows, err := s.DB.Query(
128		"SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE user_id = ? ORDER BY id",
129		userID)
130	if err != nil {
131		return nil, err
132	}
133	defer rows.Close()
134	var keys []SSHKey
135	for rows.Next() {
136		var k SSHKey
137		if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope); err != nil {
138			return nil, err
139		}
140		keys = append(keys, k)
141	}
142	return keys, rows.Err()
143}
144
145// TouchSSHKey records key use; best-effort, callers ignore the error.
146func (s *Store) TouchSSHKey(id int64) error {
147	_, err := s.DB.Exec(
148		"UPDATE ssh_keys SET last_used_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id)
149	return err
150}
151
152// AddEmail adds an address; verifiedBy is "" (unverified), "smtp", or "admin".
153// Adding an already-verified address bumps the key epoch: it is a trust input
154// for signature states.
155func (s *Store) AddEmail(userID int64, address, verifiedBy string, primary bool) error {
156	tx, err := s.DB.Begin()
157	if err != nil {
158		return err
159	}
160	defer tx.Rollback()
161	var vAt, vBy any
162	if verifiedBy != "" {
163		vAt = "now"
164		vBy = verifiedBy
165	}
166	_, err = tx.Exec(
167		`INSERT INTO emails (user_id, address, verified_at, verified_by, is_primary)
168		 VALUES (?, ?, CASE WHEN ? IS NULL THEN NULL ELSE strftime('%Y-%m-%dT%H:%M:%fZ','now') END, ?, ?)`,
169		userID, address, vAt, vBy, boolInt(primary))
170	if isUniqueErr(err) {
171		return fmt.Errorf("address %q is already in use", address)
172	}
173	if err != nil {
174		return err
175	}
176	if verifiedBy != "" {
177		if err := bumpKeyEpoch(tx); err != nil {
178			return err
179		}
180	}
181	return tx.Commit()
182}
183
184func (s *Store) KeyEpoch() (int64, error) {
185	var v int64
186	err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&v)
187	return v, err
188}
189
190type execer interface {
191	Exec(query string, args ...any) (sql.Result, error)
192}
193
194func bumpKeyEpoch(tx execer) error {
195	_, err := tx.Exec("UPDATE settings SET value = value + 1 WHERE key = 'key_epoch'")
196	return err
197}
198
199func boolInt(b bool) int {
200	if b {
201		return 1
202	}
203	return 0
204}
205
206func isUniqueErr(err error) bool {
207	return err != nil && strings.Contains(err.Error(), "UNIQUE constraint failed")
208}
209
210func (s *Store) SSHKeyByID(id int64) (SSHKey, error) {
211	var k SSHKey
212	err := s.DB.QueryRow(
213		"SELECT id, user_id, fingerprint, algo, blob, scope FROM ssh_keys WHERE id = ?",
214		id).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope)
215	if errors.Is(err, sql.ErrNoRows) {
216		return k, ErrNotFound
217	}
218	return k, err
219}