krz/gitbay

  1package store
  2
  3import (
  4	"database/sql"
  5	"errors"
  6	"time"
  7
  8	"gitbay.org/gitbay/internal/sig"
  9)
 10
 11// AddPGPKey registers an OpenPGP key and bumps the key epoch.
 12func (s *Store) AddPGPKey(userID int64, fingerprint, armored, uidsJSON string, expiresAt, revokedAt *time.Time) error {
 13	tx, err := s.DB.Begin()
 14	if err != nil {
 15		return err
 16	}
 17	defer tx.Rollback()
 18	if _, err := tx.Exec(
 19		"INSERT INTO pgp_keys (user_id, fingerprint, armored, uids_json, expires_at, revoked_at) VALUES (?, ?, ?, ?, ?, ?)",
 20		userID, fingerprint, armored, uidsJSON, timePtr(expiresAt), timePtr(revokedAt)); err != nil {
 21		if isUniqueErr(err) {
 22			return ErrDuplicateKey
 23		}
 24		return err
 25	}
 26	if err := bumpKeyEpoch(tx); err != nil {
 27		return err
 28	}
 29	return tx.Commit()
 30}
 31
 32func (s *Store) RemovePGPKey(userID int64, fingerprint string) error {
 33	tx, err := s.DB.Begin()
 34	if err != nil {
 35		return err
 36	}
 37	defer tx.Rollback()
 38	res, err := tx.Exec("DELETE FROM pgp_keys WHERE user_id = ? AND fingerprint = ?", userID, fingerprint)
 39	if err != nil {
 40		return err
 41	}
 42	if n, _ := res.RowsAffected(); n == 0 {
 43		return ErrNotFound
 44	}
 45	if err := bumpKeyEpoch(tx); err != nil {
 46		return err
 47	}
 48	return tx.Commit()
 49}
 50
 51type PGPKey struct {
 52	Fingerprint string
 53	UIDsJSON    string
 54	ExpiresAt   *time.Time
 55	RevokedAt   *time.Time
 56}
 57
 58func (s *Store) ListPGPKeys(userID int64) ([]PGPKey, error) {
 59	rows, err := s.DB.Query(
 60		"SELECT fingerprint, uids_json, expires_at, revoked_at FROM pgp_keys WHERE user_id = ? ORDER BY id", userID)
 61	if err != nil {
 62		return nil, err
 63	}
 64	defer rows.Close()
 65	var out []PGPKey
 66	for rows.Next() {
 67		var k PGPKey
 68		var exp, rev sql.NullString
 69		if err := rows.Scan(&k.Fingerprint, &k.UIDsJSON, &exp, &rev); err != nil {
 70			return nil, err
 71		}
 72		k.ExpiresAt = parseTime(exp)
 73		k.RevokedAt = parseTime(rev)
 74		out = append(out, k)
 75	}
 76	return out, rows.Err()
 77}
 78
 79// VerifyEmail marks an address verified and bumps the key epoch (email
 80// verification is a trust input for signature states).
 81func (s *Store) VerifyEmail(userID int64, address, by string) error {
 82	tx, err := s.DB.Begin()
 83	if err != nil {
 84		return err
 85	}
 86	defer tx.Rollback()
 87	res, err := tx.Exec(
 88		`UPDATE emails SET verified_at = strftime('%Y-%m-%dT%H:%M:%fZ','now'), verified_by = ?
 89		 WHERE user_id = ? AND address = ?`, by, userID, address)
 90	if err != nil {
 91		return err
 92	}
 93	if n, _ := res.RowsAffected(); n == 0 {
 94		return ErrNotFound
 95	}
 96	if err := bumpKeyEpoch(tx); err != nil {
 97		return err
 98	}
 99	return tx.Commit()
100}
101
102// SigDB adapts Store to the verifier's interface and owns the epoch cache.
103type SigDB struct{ *Store }
104
105func (d SigDB) PGPKeyByIssuer(keyIDHex string) (sig.PGPKeyInfo, string, bool, error) {
106	var info sig.PGPKeyInfo
107	var fpr string
108	var exp, rev sql.NullString
109	err := d.DB.QueryRow(
110		"SELECT user_id, fingerprint, armored, expires_at, revoked_at FROM pgp_keys WHERE fingerprint LIKE '%' || ?",
111		keyIDHex).Scan(&info.UserID, &fpr, &info.Armored, &exp, &rev)
112	if errors.Is(err, sql.ErrNoRows) {
113		return info, "", false, nil
114	}
115	if err != nil {
116		return info, "", false, err
117	}
118	info.ExpiresAt = parseTime(exp)
119	info.RevokedAt = parseTime(rev)
120	return info, fpr, true, nil
121}
122
123func (d SigDB) SSHSignerByFingerprint(fp string) (sig.SSHKeyInfo, bool, error) {
124	k, err := d.SSHKeyByFingerprint(fp)
125	if errors.Is(err, ErrNotFound) {
126		return sig.SSHKeyInfo{}, false, nil
127	}
128	if err != nil {
129		return sig.SSHKeyInfo{}, false, err
130	}
131	return sig.SSHKeyInfo{UserID: k.UserID, Fingerprint: k.Fingerprint}, true, nil
132}
133
134func (d SigDB) VerifiedEmails(userID int64) ([]string, error) {
135	rows, err := d.DB.Query(
136		"SELECT address FROM emails WHERE user_id = ? AND verified_at IS NOT NULL", userID)
137	if err != nil {
138		return nil, err
139	}
140	defer rows.Close()
141	var out []string
142	for rows.Next() {
143		var a string
144		if err := rows.Scan(&a); err != nil {
145			return nil, err
146		}
147		out = append(out, a)
148	}
149	return out, rows.Err()
150}
151
152// CachedSignature returns a cached result and whether it is current at the
153// given epoch.
154func (s *Store) CachedSignature(repoID int64, sha string, epoch int64) (sig.Result, bool, error) {
155	var r sig.Result
156	var state string
157	var signer sql.NullInt64
158	var fpr sql.NullString
159	var rowEpoch int64
160	err := s.DB.QueryRow(
161		"SELECT state, signer_user_id, key_fingerprint, key_epoch FROM commit_signatures WHERE repo_id = ? AND commit_sha = ?",
162		repoID, sha).Scan(&state, &signer, &fpr, &rowEpoch)
163	if errors.Is(err, sql.ErrNoRows) {
164		return r, false, nil
165	}
166	if err != nil {
167		return r, false, err
168	}
169	if rowEpoch < epoch {
170		return r, false, nil // stale: trust inputs changed since this was computed
171	}
172	r.State = sig.State(state)
173	r.SignerUserID = signer.Int64
174	r.KeyFingerprint = fpr.String
175	return r, true, nil
176}
177
178func (s *Store) StoreSignature(repoID int64, sha string, r sig.Result, epoch int64) error {
179	var signer any
180	if r.SignerUserID != 0 {
181		signer = r.SignerUserID
182	}
183	var fpr any
184	if r.KeyFingerprint != "" {
185		fpr = r.KeyFingerprint
186	}
187	_, err := s.DB.Exec(`
188		INSERT INTO commit_signatures (repo_id, commit_sha, state, signer_user_id, key_fingerprint, key_epoch)
189		VALUES (?, ?, ?, ?, ?, ?)
190		ON CONFLICT (repo_id, commit_sha) DO UPDATE SET
191			state = excluded.state, signer_user_id = excluded.signer_user_id,
192			key_fingerprint = excluded.key_fingerprint, key_epoch = excluded.key_epoch,
193			checked_at = strftime('%Y-%m-%dT%H:%M:%fZ','now')`,
194		repoID, sha, string(r.State), signer, fpr, epoch)
195	return err
196}
197
198func timePtr(t *time.Time) any {
199	if t == nil {
200		return nil
201	}
202	return t.UTC().Format("2006-01-02T15:04:05.000Z")
203}
204
205func parseTime(s sql.NullString) *time.Time {
206	if !s.Valid {
207		return nil
208	}
209	t, err := time.Parse("2006-01-02T15:04:05.000Z", s.String)
210	if err != nil {
211		return nil
212	}
213	return &t
214}