internal/store/users.go

  1package store
  2
  3import (
  4	"database/sql"
  5	"errors"
  6	"fmt"
  7	"strings"
  8	"time"
  9)
 10
 11type User struct {
 12	ID       int64
 13	Username string
 14	IsAdmin  bool
 15	Pending  bool // self-registered, email not yet verified
 16	Disabled bool // administratively suspended, or scheduled for deletion
 17	// DeleteAfter is when a scheduled deletion purges the account, ""
 18	// when none is scheduled. A scheduled account is also Disabled.
 19	DeleteAfter string
 20	Ghost       bool // stands in as the author of deleted accounts' content
 21	// SignedInAt is when the browser session this user came from was
 22	// created by a login. Set by WebSessionUser only; zero elsewhere.
 23	SignedInAt time.Time
 24}
 25
 26type SSHKey struct {
 27	ID          int64
 28	UserID      int64
 29	Fingerprint string
 30	Algo        string
 31	Blob        []byte
 32	Scope       string
 33	Label       string // "" when the key was added with no name
 34	CreatedAt   string
 35	LastUsedAt  string     // "" when the key has never authenticated
 36	CreatedBy   string     // name of the API token that added the key; "" for none. ListSSHKeys only.
 37	ExpiresAt   *time.Time // nil when the key never expires
 38}
 39
 40// Expired reports whether the key has lapsed at now.
 41func (k SSHKey) Expired(now time.Time) bool {
 42	return k.ExpiresAt != nil && !k.ExpiresAt.After(now)
 43}
 44
 45// ErrDuplicateKey carries the exact user-facing message from the spec. It
 46// deliberately does not name the owning account (enumeration oracle).
 47var ErrDuplicateKey = errors.New("that key is already registered to another account; remove it there first or use a different key")
 48
 49var ErrNotFound = errors.New("not found")
 50
 51func (s *Store) CreateUser(username string, isAdmin bool) (int64, error) {
 52	if taken, err := ownerNameTaken(s.DB, username); err != nil {
 53		return 0, err
 54	} else if taken {
 55		return 0, fmt.Errorf("username %q is taken", username)
 56	}
 57	res, err := s.DB.Exec("INSERT INTO users (username, is_admin) VALUES (?, ?)", username, boolInt(isAdmin))
 58	if err != nil {
 59		if isUniqueErr(err) {
 60			return 0, fmt.Errorf("username %q is taken", username)
 61		}
 62		return 0, err
 63	}
 64	return res.LastInsertId()
 65}
 66
 67// DeleteUser removes an account whose removal orphans nothing: no owned
 68// repositories, no authored issues, MRs, comments, or reviews, and not the
 69// only admin of an org. Everything else (keys, emails, sessions, tokens,
 70// pins, memberships, activity) cascades. Blockers come back as an error
 71// naming what stands in the way, so the operator can transfer, delete, or
 72// disable instead.
 73func (s *Store) DeleteUser(id int64) error {
 74	var blockers []string
 75	var checkErr error
 76	count := func(q string, what string) {
 77		var n int
 78		if err := s.DB.QueryRow(q, id).Scan(&n); err != nil {
 79			if checkErr == nil {
 80				checkErr = fmt.Errorf("checking %s: %w", what, err)
 81			}
 82			return
 83		}
 84		if n > 0 {
 85			blockers = append(blockers, fmt.Sprintf("%d %s", n, what))
 86		}
 87	}
 88	count("SELECT COUNT(*) FROM repos WHERE owner_kind = 'user' AND owner_id = ?", "owned repositories")
 89	count("SELECT COUNT(*) FROM issues WHERE author_id = ?", "authored issues")
 90	count("SELECT COUNT(*) FROM merge_requests WHERE author_id = ?", "authored merge requests")
 91	count("SELECT COUNT(*) FROM issue_comments WHERE author_id = ?", "issue comments")
 92	count("SELECT COUNT(*) FROM mr_comments WHERE author_id = ?", "MR comments")
 93	count("SELECT COUNT(*) FROM mr_diff_comments WHERE author_id = ?", "diff comments")
 94	count("SELECT COUNT(*) FROM mr_reviews WHERE reviewer_id = ?", "reviews")
 95	count(`SELECT COUNT(*) FROM org_members m WHERE m.user_id = ? AND m.role = 'admin'
 96		AND NOT EXISTS (SELECT 1 FROM org_members o
 97			WHERE o.org_id = m.org_id AND o.role = 'admin' AND o.user_id != m.user_id)`,
 98		"organizations with no other admin")
 99	if checkErr != nil {
100		return checkErr
101	}
102	if len(blockers) > 0 {
103		return fmt.Errorf("account still anchors: %s — transfer or delete those first, or disable the account instead",
104			strings.Join(blockers, ", "))
105	}
106	// Grants and a parked about text name the account by id with no
107	// foreign key, so they go in the same transaction (#306).
108	tx, err := s.DB.Begin()
109	if err != nil {
110		return err
111	}
112	defer tx.Rollback()
113	if _, err := tx.Exec("DELETE FROM repo_access WHERE subject_kind = 'user' AND subject_id = ?", id); err != nil {
114		return err
115	}
116	if _, err := tx.Exec("DELETE FROM profile_about_backfill WHERE owner_kind = 'user' AND owner_id = ?", id); err != nil {
117		return err
118	}
119	res, err := tx.Exec("DELETE FROM users WHERE id = ?", id)
120	if err != nil {
121		return err
122	}
123	if n, _ := res.RowsAffected(); n == 0 {
124		return ErrNotFound
125	}
126	if err := tx.Commit(); err != nil {
127		return err
128	}
129	s.announce(Revoked{UserID: id})
130	return nil
131}
132
133// OwnerExists reports whether a user or org owns the name — the ACME host
134// policy check for pages subdomains.
135func (s *Store) OwnerExists(name string) bool {
136	var n int
137	s.DB.QueryRow(`SELECT (SELECT COUNT(*) FROM users WHERE username = ?1)
138		+ (SELECT COUNT(*) FROM orgs WHERE name = ?1)`, name).Scan(&n)
139	return n > 0
140}
141
142func (s *Store) UserByUsername(name string) (User, error) {
143	var u User
144	var admin, pending, disabled, ghost int
145	var deleteAfter sql.NullString
146	err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled, delete_after, ghost FROM users WHERE username = ?", name).
147		Scan(&u.ID, &u.Username, &admin, &pending, &disabled, &deleteAfter, &ghost)
148	if errors.Is(err, sql.ErrNoRows) {
149		return u, ErrNotFound
150	}
151	u.IsAdmin = admin != 0
152	u.Pending = pending != 0
153	u.Disabled = disabled != 0
154	u.DeleteAfter = deleteAfter.String
155	u.Ghost = ghost != 0
156	return u, err
157}
158
159// UserEmailAddresses returns every address on the account, verified or not.
160func (s *Store) UserEmailAddresses(userID int64) ([]string, error) {
161	rows, err := s.DB.Query("SELECT address FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address", userID)
162	if err != nil {
163		return nil, err
164	}
165	defer rows.Close()
166	var out []string
167	for rows.Next() {
168		var a string
169		if err := rows.Scan(&a); err != nil {
170			return nil, err
171		}
172		out = append(out, a)
173	}
174	return out, rows.Err()
175}
176
177// Email is one address on an account, with the state the signature rules
178// and notification routing depend on.
179type Email struct {
180	Address    string
181	Verified   bool
182	VerifiedBy string // smtp | admin, empty when unverified
183	Primary    bool
184}
185
186// ListEmails returns every address on the account with its state.
187func (s *Store) ListEmails(userID int64) ([]Email, error) {
188	rows, err := s.DB.Query(`SELECT address, verified_at IS NOT NULL,
189		COALESCE(verified_by, ''), is_primary
190		FROM emails WHERE user_id = ? ORDER BY is_primary DESC, address`, userID)
191	if err != nil {
192		return nil, err
193	}
194	defer rows.Close()
195	var out []Email
196	for rows.Next() {
197		var e Email
198		if err := rows.Scan(&e.Address, &e.Verified, &e.VerifiedBy, &e.Primary); err != nil {
199			return nil, err
200		}
201		out = append(out, e)
202	}
203	return out, rows.Err()
204}
205
206// SetUserDisabled suspends or restores an account. Disabling drops every
207// credential that would grant a session on its own — web sessions, API
208// tokens, unclaimed login links — and leaves the SSH keys registered but
209// refused at every entry point until re-enabled; connections they opened
210// are closed.
211func (s *Store) SetUserDisabled(userID int64, disabled bool) error {
212	v := 0
213	if _, err := s.DB.Exec("DELETE FROM account_deletions WHERE user_id = ?", userID); err != nil {
214		return err
215	}
216	if disabled {
217		v = 1
218	}
219	// An admin's decision replaces a scheduled or requested deletion
220	// either way: a disabled account stays disabled and is not purged,
221	// an enabled one is back in use. A purge already under way finishes.
222	res, err := s.DB.Exec("UPDATE users SET disabled = ?, delete_after = CASE WHEN delete_after = ? THEN delete_after END WHERE id = ?", v, Purging, userID)
223	if err != nil {
224		return err
225	}
226	if n, _ := res.RowsAffected(); n == 0 {
227		return ErrNotFound
228	}
229	if disabled {
230		// A pending login link is a session in waiting, so it goes with
231		// the sessions and API tokens. Re-enabling means minting again.
232		for _, table := range []string{"web_sessions", "api_tokens", "login_tokens"} {
233			if _, err = s.DB.Exec("DELETE FROM "+table+" WHERE user_id = ?", userID); err != nil {
234				return err
235			}
236		}
237		s.announce(Revoked{UserID: userID})
238	}
239	return err
240}
241
242// MailEnabled reports whether activity notifications reach the account
243// by mail as well as the inbox.
244func (s *Store) MailEnabled(userID int64) (bool, error) {
245	var on int
246	err := s.DB.QueryRow("SELECT notify_mail FROM users WHERE id = ?", userID).Scan(&on)
247	if errors.Is(err, sql.ErrNoRows) {
248		return false, ErrNotFound
249	}
250	return on != 0, err
251}
252
253func (s *Store) SetMailEnabled(userID int64, on bool) error {
254	v := 0
255	if on {
256		v = 1
257	}
258	_, err := s.DB.Exec("UPDATE users SET notify_mail = ? WHERE id = ?", v, userID)
259	return err
260}
261
262// ReplyEnabled reports whether the account's issue and merge request
263// mail carries a reply address (#295).
264func (s *Store) ReplyEnabled(userID int64) (bool, error) {
265	var on int
266	err := s.DB.QueryRow("SELECT notify_reply FROM users WHERE id = ?", userID).Scan(&on)
267	if errors.Is(err, sql.ErrNoRows) {
268		return false, ErrNotFound
269	}
270	return on != 0, err
271}
272
273func (s *Store) SetReplyEnabled(userID int64, on bool) error {
274	v := 0
275	if on {
276		v = 1
277	}
278	_, err := s.DB.Exec("UPDATE users SET notify_reply = ? WHERE id = ?", v, userID)
279	return err
280}
281
282// WatchEnabled reports whether the account hears about every issue and
283// merge request on the repositories it can write to, without a
284// repo_watchers row on each (#194).
285func (s *Store) WatchEnabled(userID int64) (bool, error) {
286	var on int
287	err := s.DB.QueryRow("SELECT notify_watch FROM users WHERE id = ?", userID).Scan(&on)
288	if errors.Is(err, sql.ErrNoRows) {
289		return false, ErrNotFound
290	}
291	return on != 0, err
292}
293
294func (s *Store) SetWatchEnabled(userID int64, on bool) error {
295	v := 0
296	if on {
297		v = 1
298	}
299	_, err := s.DB.Exec("UPDATE users SET notify_watch = ? WHERE id = ?", v, userID)
300	return err
301}
302
303// Theme is the web colour scheme the account chose: system, light or
304// dark (#232).
305func (s *Store) Theme(userID int64) (string, error) {
306	var theme string
307	err := s.DB.QueryRow("SELECT theme FROM users WHERE id = ?", userID).Scan(&theme)
308	if errors.Is(err, sql.ErrNoRows) {
309		return "", ErrNotFound
310	}
311	return theme, err
312}
313
314func (s *Store) SetTheme(userID int64, theme string) error {
315	_, err := s.DB.Exec("UPDATE users SET theme = ? WHERE id = ?", theme, userID)
316	return err
317}
318
319// DiffLayout is how the account wants diffs drawn on the web: unified or
320// split (#290).
321func (s *Store) DiffLayout(userID int64) (string, error) {
322	var l string
323	err := s.DB.QueryRow("SELECT diff_layout FROM users WHERE id = ?", userID).Scan(&l)
324	if errors.Is(err, sql.ErrNoRows) {
325		return "", ErrNotFound
326	}
327	return l, err
328}
329
330func (s *Store) SetDiffLayout(userID int64, layout string) error {
331	_, err := s.DB.Exec("UPDATE users SET diff_layout = ? WHERE id = ?", layout, userID)
332	return err
333}
334
335func (s *Store) UserByID(id int64) (User, error) {
336	var u User
337	var admin, pending, disabled, ghost int
338	var deleteAfter sql.NullString
339	err := s.DB.QueryRow("SELECT id, username, is_admin, pending, disabled, delete_after, ghost FROM users WHERE id = ?", id).
340		Scan(&u.ID, &u.Username, &admin, &pending, &disabled, &deleteAfter, &ghost)
341	if errors.Is(err, sql.ErrNoRows) {
342		return u, ErrNotFound
343	}
344	u.IsAdmin = admin != 0
345	u.Pending = pending != 0
346	u.Disabled = disabled != 0
347	u.DeleteAfter = deleteAfter.String
348	u.Ghost = ghost != 0
349	return u, err
350}
351
352// KeyOrigin is how a key came to be.
353type KeyOrigin struct {
354	CreatedByToken int64      // the API token that added it; 0 for none
355	ExpiresAt      *time.Time // when it stops authenticating; nil for never
356}
357
358// AddSSHKey registers a key and bumps the key epoch in one transaction.
359func (s *Store) AddSSHKey(userID int64, fingerprint, algo string, blob []byte, scope, label string) error {
360	return s.AddSSHKeyFrom(userID, fingerprint, algo, blob, scope, label, KeyOrigin{})
361}
362
363// AddSSHKeyFrom is AddSSHKey recording where the key came from.
364func (s *Store) AddSSHKeyFrom(userID int64, fingerprint, algo string, blob []byte, scope, label string, o KeyOrigin) error {
365	tx, err := s.DB.Begin()
366	if err != nil {
367		return err
368	}
369	defer tx.Rollback()
370	var exp any
371	if o.ExpiresAt != nil {
372		exp = fmtTime(*o.ExpiresAt)
373	}
374	if _, err := tx.Exec(
375		"INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope, label, created_by_token, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
376		userID, fingerprint, algo, blob, scope, label, nullID(o.CreatedByToken), exp); err != nil {
377		if isUniqueErr(err) {
378			return ErrDuplicateKey
379		}
380		return err
381	}
382	if err := bumpKeyEpoch(tx); err != nil {
383		return err
384	}
385	return tx.Commit()
386}
387
388// RemoveSSHKey removes a key owned by userID, bumps the key epoch, and
389// announces the revocation.
390func (s *Store) RemoveSSHKey(userID int64, fingerprint string) error {
391	tx, err := s.DB.Begin()
392	if err != nil {
393		return err
394	}
395	defer tx.Rollback()
396	var id int64
397	err = tx.QueryRow("DELETE FROM ssh_keys WHERE user_id = ? AND fingerprint = ? RETURNING id", userID, fingerprint).Scan(&id)
398	if errors.Is(err, sql.ErrNoRows) {
399		return ErrNotFound
400	}
401	if err != nil {
402		return err
403	}
404	if err := bumpKeyEpoch(tx); err != nil {
405		return err
406	}
407	if err := tx.Commit(); err != nil {
408		return err
409	}
410	s.announce(Revoked{KeyIDs: []int64{id}})
411	return nil
412}
413
414// SetSSHKeyLabel renames a key owned by userID. Labels do not touch the
415// key epoch: nothing about authentication changes.
416func (s *Store) SetSSHKeyLabel(userID int64, fingerprint, label string) error {
417	res, err := s.DB.Exec("UPDATE ssh_keys SET label = ? WHERE user_id = ? AND fingerprint = ?", label, userID, fingerprint)
418	if err != nil {
419		return err
420	}
421	if n, _ := res.RowsAffected(); n == 0 {
422		return ErrNotFound
423	}
424	return nil
425}
426
427func (s *Store) SSHKeyByFingerprint(fingerprint string) (SSHKey, error) {
428	var k SSHKey
429	var exp sql.NullString
430	err := s.DB.QueryRow(
431		"SELECT id, user_id, fingerprint, algo, blob, scope, label, expires_at FROM ssh_keys WHERE fingerprint = ?",
432		fingerprint).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &exp)
433	if errors.Is(err, sql.ErrNoRows) {
434		return k, ErrNotFound
435	}
436	k.ExpiresAt = parseTime(exp)
437	return k, err
438}
439
440func (s *Store) ListSSHKeys(userID int64) ([]SSHKey, error) {
441	rows, err := s.DB.Query(
442		`SELECT k.id, k.user_id, k.fingerprint, k.algo, k.blob, k.scope, k.label, k.created_at,
443		        COALESCE(k.last_used_at, ''), COALESCE(t.name, ''), k.expires_at
444		 FROM ssh_keys k LEFT JOIN api_tokens t ON t.id = k.created_by_token
445		 WHERE k.user_id = ? ORDER BY k.id`,
446		userID)
447	if err != nil {
448		return nil, err
449	}
450	defer rows.Close()
451	var keys []SSHKey
452	for rows.Next() {
453		var k SSHKey
454		var exp sql.NullString
455		if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &k.CreatedAt, &k.LastUsedAt, &k.CreatedBy, &exp); err != nil {
456			return nil, err
457		}
458		k.ExpiresAt = parseTime(exp)
459		keys = append(keys, k)
460	}
461	return keys, rows.Err()
462}
463
464// TouchSSHKey records key use; best-effort, callers ignore the error.
465func (s *Store) TouchSSHKey(id int64) error {
466	_, err := s.DB.Exec(
467		"UPDATE ssh_keys SET last_used_at = strftime('%Y-%m-%dT%H:%M:%fZ','now') WHERE id = ?", id)
468	return err
469}
470
471// AddEmail adds an address; verifiedBy is "" (unverified), "smtp", or "admin".
472// Adding an already-verified address bumps the key epoch: it is a trust input
473// for signature states.
474func (s *Store) AddEmail(userID int64, address, verifiedBy string, primary bool) error {
475	tx, err := s.DB.Begin()
476	if err != nil {
477		return err
478	}
479	defer tx.Rollback()
480	var vAt, vBy any
481	if verifiedBy != "" {
482		vAt = "now"
483		vBy = verifiedBy
484	}
485	_, err = tx.Exec(
486		`INSERT INTO emails (user_id, address, verified_at, verified_by, is_primary)
487		 VALUES (?, ?, CASE WHEN ? IS NULL THEN NULL ELSE strftime('%Y-%m-%dT%H:%M:%fZ','now') END, ?, ?)`,
488		userID, address, vAt, vBy, boolInt(primary))
489	if isUniqueErr(err) {
490		return fmt.Errorf("address %q is already in use", address)
491	}
492	if err != nil {
493		return err
494	}
495	if verifiedBy != "" {
496		if err := bumpKeyEpoch(tx); err != nil {
497			return err
498		}
499	}
500	return tx.Commit()
501}
502
503var (
504	ErrPrimaryEmail      = errors.New("that is the primary address; make another address primary first")
505	ErrLastVerifiedEmail = errors.New("that is the only verified address on the account; verify another first")
506	ErrUnverifiedEmail   = errors.New("that address is not verified")
507)
508
509// RemoveEmail drops an address from the account, and any verification
510// code pending for it. The primary and the last verified address stay:
511// activation, login links and commit identity all resolve through
512// verified addresses. Removing a verified address bumps the key epoch,
513// since the signature cache keys on verified addresses too.
514func (s *Store) RemoveEmail(userID int64, address string) error {
515	tx, err := s.DB.Begin()
516	if err != nil {
517		return err
518	}
519	defer tx.Rollback()
520	var primary, verified bool
521	err = tx.QueryRow("SELECT is_primary, verified_at IS NOT NULL FROM emails WHERE user_id = ? AND address = ?",
522		userID, address).Scan(&primary, &verified)
523	if errors.Is(err, sql.ErrNoRows) {
524		return ErrNotFound
525	}
526	if err != nil {
527		return err
528	}
529	if primary {
530		return ErrPrimaryEmail
531	}
532	if verified {
533		var others int
534		if err := tx.QueryRow("SELECT count(*) FROM emails WHERE user_id = ? AND verified_at IS NOT NULL AND address != ?",
535			userID, address).Scan(&others); err != nil {
536			return err
537		}
538		if others == 0 {
539			return ErrLastVerifiedEmail
540		}
541	}
542	if _, err := tx.Exec("DELETE FROM email_tokens WHERE user_id = ? AND address = ?", userID, address); err != nil {
543		return err
544	}
545	if _, err := tx.Exec("DELETE FROM emails WHERE user_id = ? AND address = ?", userID, address); err != nil {
546		return err
547	}
548	if verified {
549		if err := bumpKeyEpoch(tx); err != nil {
550			return err
551		}
552	}
553	return tx.Commit()
554}
555
556// SetPrimaryEmail makes a verified address the account's primary. The
557// verified set is unchanged, so the key epoch is not.
558func (s *Store) SetPrimaryEmail(userID int64, address string) error {
559	tx, err := s.DB.Begin()
560	if err != nil {
561		return err
562	}
563	defer tx.Rollback()
564	var verified bool
565	err = tx.QueryRow("SELECT verified_at IS NOT NULL FROM emails WHERE user_id = ? AND address = ?",
566		userID, address).Scan(&verified)
567	if errors.Is(err, sql.ErrNoRows) {
568		return ErrNotFound
569	}
570	if err != nil {
571		return err
572	}
573	if !verified {
574		return ErrUnverifiedEmail
575	}
576	if _, err := tx.Exec("UPDATE emails SET is_primary = 0 WHERE user_id = ?", userID); err != nil {
577		return err
578	}
579	if _, err := tx.Exec("UPDATE emails SET is_primary = 1 WHERE user_id = ? AND address = ?", userID, address); err != nil {
580		return err
581	}
582	return tx.Commit()
583}
584
585func (s *Store) KeyEpoch() (int64, error) {
586	var v int64
587	err := s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&v)
588	return v, err
589}
590
591type execer interface {
592	Exec(query string, args ...any) (sql.Result, error)
593}
594
595func bumpKeyEpoch(tx execer) error {
596	_, err := tx.Exec("UPDATE settings SET value = value + 1 WHERE key = 'key_epoch'")
597	return err
598}
599
600func boolInt(b bool) int {
601	if b {
602		return 1
603	}
604	return 0
605}
606
607func isUniqueErr(err error) bool {
608	return err != nil && strings.Contains(err.Error(), "UNIQUE constraint failed")
609}
610
611func (s *Store) SSHKeyByID(id int64) (SSHKey, error) {
612	var k SSHKey
613	var exp sql.NullString
614	err := s.DB.QueryRow(
615		"SELECT id, user_id, fingerprint, algo, blob, scope, label, expires_at FROM ssh_keys WHERE id = ?",
616		id).Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &exp)
617	if errors.Is(err, sql.ErrNoRows) {
618		return k, ErrNotFound
619	}
620	k.ExpiresAt = parseTime(exp)
621	return k, err
622}
623
624// ListDeployKeys returns the deploy keys bound to a repository.
625func (s *Store) ListDeployKeys(repoID int64) ([]SSHKey, error) {
626	rows, err := s.DB.Query(
627		`SELECT id, user_id, fingerprint, algo, blob, scope, label, COALESCE(last_used_at, ''), expires_at
628		 FROM ssh_keys WHERE scope LIKE 'deploy:' || ? || ':%' ORDER BY id`,
629		repoID)
630	if err != nil {
631		return nil, err
632	}
633	defer rows.Close()
634	var keys []SSHKey
635	for rows.Next() {
636		var k SSHKey
637		var exp sql.NullString
638		if err := rows.Scan(&k.ID, &k.UserID, &k.Fingerprint, &k.Algo, &k.Blob, &k.Scope, &k.Label, &k.LastUsedAt, &exp); err != nil {
639			return nil, err
640		}
641		k.ExpiresAt = parseTime(exp)
642		keys = append(keys, k)
643	}
644	return keys, rows.Err()
645}
646
647// RemoveDeployKey removes a deploy key from a repository by fingerprint;
648// any repo admin may remove it regardless of who added it.
649func (s *Store) RemoveDeployKey(repoID int64, fingerprint string) error {
650	tx, err := s.DB.Begin()
651	if err != nil {
652		return err
653	}
654	defer tx.Rollback()
655	var id int64
656	err = tx.QueryRow(
657		"DELETE FROM ssh_keys WHERE fingerprint = ? AND scope LIKE 'deploy:' || ? || ':%' RETURNING id",
658		fingerprint, repoID).Scan(&id)
659	if errors.Is(err, sql.ErrNoRows) {
660		return ErrNotFound
661	}
662	if err != nil {
663		return err
664	}
665	if err := bumpKeyEpoch(tx); err != nil {
666		return err
667	}
668	if err := tx.Commit(); err != nil {
669		return err
670	}
671	s.announce(Revoked{KeyIDs: []int64{id}})
672	return nil
673}