internal/store/users.go

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