store: ids are not reused after a delete !539

merged merged by cmc on 2026-09-29 15:59 UTC · krz/gitbay:id-reuse-306 into main

22 files changed, +1329 −23

Layout: unified · split

.gitbay/wiki/Architecture/09-Controls.org +1

		@@ -40,6 +40,7 @@ chapter names of OWASP ASVS 4.0 where one fits.
40	40	\| Admin functions isolated \| in place \| =admin= noun gated in =Dispatch=; =audit= admin-only \|
41	41	\| CSRF protection \| in place \| SameSite=Lax plus =checkOrigin= (=accounts.go=) \|
42	42	\| Typed confirmation for destructive web actions \| in place \| =internal/httpd/confirm.go= \|
	43	\| Deleted rows' ids never handed out again \| in place \| AUTOINCREMENT on accounts, orgs, repositories, keys, tokens and the delivery queues; build ids from a high-water mark (=internal/store/builds.go=), so a grant, deploy key, parked about text, token or claim naming a deleted row names nothing; deletes take the grants, deploy keys and about texts that name the row by id \|
43	44
44	45	** Input handling and output encoding (V5)
45	46

.gitbay/wiki/Architecture/10-Known-Gaps.org +1

		@@ -16,6 +16,7 @@ what the 2026-09-27 review found; remove a row when its issue closes.
16	16	\| Area \| Gap \| Severity \|
17	17	\|-------+-------------------------------------------------------------------------------------------------------------+----------\|
18	18	\| Audit \| The hash chain is unkeyed, so whoever can write the database can edit a row and recompute every later hash; removing the newest audit rows, or writing new rows under their freed ids, needs no recomputing at all. Neither is detectable from the database; only comparing =gitbayd admin audit verify='s last id and hash with the daemon's journal shows it. Rows written by =gitbayd shell= (=ssh.mode = "system"=) and host admin commands have no journal copy, and the refusal caps are per process, so under that mode each connection counts separately \| low \|
	19	\| Access \| Grants and parked profile about texts (=profile_about_backfill=) of deleted accounts and organizations, and deploy keys of deleted repositories, left by deletes before #306, are removed on upgrade and the "schema migrated" log line gives the counts. One whose id a later row had already taken is no longer an orphan and stays with that row; on gitbay.org the orphans found (two grants, one deploy key) name ids no later row had taken \| low \|
19	20	\| Availability \| Under =ssh.mode = "system"= each SSH session is a separate =gitbayd shell= process, so the pack-generation limit (=internal/packlimit=, #262) cannot count SSH clones across sessions; only HTTP and git:// share a budget there \| low \|
20	21	\| Availability \| Pushes have no concurrency limit; =max_pack_bytes= bounds each one, not how many run at once \| medium \|
21	22	\| Availability \| =repo download= (SSH, API) runs =git archive= outside the pack limit; only its two-minute deadline and 512 MiB cap bound it \| low \|

CHANGELOG.org +13

		@@ -23,6 +23,19 @@ anything beyond "replace the binary and restart" is needed.
23	23	- A reply whose header has a field name outside RFC 5322 =ftext=
24	24	(=From : x=), no single From, or a repeated To, Cc, Message-ID,
25	25	Content-Type or Content-Transfer-Encoding is refused. (#307)
	26	- Ids of accounts, organizations, repositories, SSH keys, API tokens,
	27	webhook deliveries, queued pushes and builds are no longer handed out
	28	again after a delete. Before, the next row took the id of the newest
	29	deleted one, with anything that still named it: a deleted account's
	30	repository grants, a deleted repository's deploy keys, signed LFS and
	31	reply-by-mail tokens, a runner's claimed build. The migration rebuilds
	32	those tables and starts each sequence above every id still named
	33	(#306).
	34	- Deleting an account or organization removes its repository grants
	35	and its parked profile about text, and deleting a repository removes
	36	its deploy keys. On upgrade, rows of these kinds left by earlier
	37	deletes are removed, and the "schema migrated" log line gives how many
	38	(#306).
26	39
27	40	* v1.39.0 — 2026-09-29
28	41

cmd/gitbayd/main.go +10 −1

		@@ -77,7 +77,16 @@ func openStore(cfg config.Config) (*store.Store, error) {
77	77	return nil, err
78	78	}
79	79	if after != before {
80		slog.Info("schema migrated", "from", before, "to", after)
	80	note, err := s.TakeMigrationNote()
	81	if err != nil {
	82	s.Close()
	83	return nil, err
	84	}
	85	args := []any{"from", before, "to", after}
	86	if note != "" {
	87	args = append(args, "note", note)
	88	}
	89	slog.Info("schema migrated", args...)
81	90	}
82	91	return s, nil
83	92	}

cmd/gitbayd/main_test.go +35

		@@ -3,9 +3,12 @@ package main
3	3	import (
4	4	"bytes"
5	5	"log/slog"
	6	"path/filepath"
6	7	"strconv"
7	8	"strings"
8	9	"testing"
	10
	11	"gitbay.org/gitbay/internal/store"
9	12	)
10	13
11	14	// A restart that moves the schema says so. Migrations used to run in silence,
		@@ -52,3 +55,35 @@ func TestOpenStoreLogsSchemaMigration(t *testing.T) {
52	55	t.Errorf("logged a migration on an up-to-date database:\n%s", buf.String())
53	56	}
54	57	}
	58
	59	// A note a migration leaves goes in the log line and is then dropped.
	60	func TestOpenStoreLogsMigrationNote(t *testing.T) {
	61	cfg := testConfig(t)
	62	st, err := store.Open(filepath.Join(cfg.Server.Root, "gitbay.db"))
	63	if err != nil {
	64	t.Fatal(err)
	65	}
	66	if err := st.MigrateTo(1); err != nil {
	67	t.Fatal(err)
	68	}
	69	if _, err := st.DB.Exec("INSERT INTO settings (key, value) VALUES ('migration_note', 'removed 2 things')"); err != nil {
	70	t.Fatal(err)
	71	}
	72	st.Close()
	73
	74	var buf bytes.Buffer
	75	prev := slog.Default()
	76	slog.SetDefault(slog.New(slog.NewTextHandler(&buf, nil)))
	77	defer slog.SetDefault(prev)
	78	s, err := openStore(cfg)
	79	if err != nil {
	80	t.Fatalf("openStore: %v", err)
	81	}
	82	defer s.Close()
	83	if !strings.Contains(buf.String(), `note="removed 2 things"`) {
	84	t.Fatalf("note not logged:\n%s", buf.String())
	85	}
	86	if note, err := s.TakeMigrationNote(); err != nil \|\| note != "" {
	87	t.Fatalf("note left behind: %q, %v", note, err)
	88	}
	89	}

internal/control/idreuse_test.go added +95

		@@ -0,0 +1,95 @@
	1	package control
	2
	3	import (
	4	"fmt"
	5	"testing"
	6	"time"
	7
	8	"gitbay.org/gitbay/internal/policy"
	9	)
	10
	11	// A deploy key names its repository by id in its scope. Deleting the
	12	// repository removes the key, and the next repository does not take the
	13	// id, so the key opens nothing either way (#306).
	14	func TestDeployKeyOfDeletedRepository(t *testing.T) {
	15	st, _, uid := newQueueTestRepo(t)
	16	goneID, err := st.CreateRepo("user", uid, "gone", "private")
	17	if err != nil {
	18	t.Fatal(err)
	19	}
	20	scope := fmt.Sprintf("deploy:%d:rw", goneID)
	21	if err := st.AddSSHKey(uid, "SHA256:deploy", "ssh-ed25519", []byte("d"), scope, ""); err != nil {
	22	t.Fatal(err)
	23	}
	24	if err := st.DeleteRepo(goneID); err != nil {
	25	t.Fatal(err)
	26	}
	27	nextID, err := st.CreateRepo("user", uid, "next", "private")
	28	if err != nil {
	29	t.Fatal(err)
	30	}
	31	if policy.DeployScopeAllows(scope, nextID, false) {
	32	t.Fatalf("the deleted repository's deploy key reads repository %d", nextID)
	33	}
	34	}
	35
	36	// A grant names its account by id. Deleting the account removes the
	37	// grant, and the next account does not take the id (#306).
	38	func TestGrantOfDeletedAccount(t *testing.T) {
	39	st, repo, _ := newQueueTestRepo(t)
	40	carol, err := st.CreateUser("carol", false)
	41	if err != nil {
	42	t.Fatal(err)
	43	}
	44	if err := st.GrantAccess(repo.ID, carol, "admin"); err != nil {
	45	t.Fatal(err)
	46	}
	47	if err := st.DeleteUser(carol); err != nil {
	48	t.Fatal(err)
	49	}
	50	dave, err := st.CreateUser("dave", false)
	51	if err != nil {
	52	t.Fatal(err)
	53	}
	54	if role, err := st.AccessRole(repo.ID, dave); err != nil \|\| role != "" {
	55	t.Fatalf("new account's role on the repository: %q, %v", role, err)
	56	}
	57	}
	58
	59	// A merge queued with a key that is then removed stays refused when a
	60	// new key is added: key_id is set to NULL on removal and the new key has
	61	// an id of its own (#289, #306).
	62	func TestQueuedMergeKeyRemovedThenNewKey(t *testing.T) {
	63	st, repo, uid := newQueueTestRepo(t)
	64	if err := st.AddSSHKey(uid, "SHA256:k1", "ssh-ed25519", []byte("k1"), "full", ""); err != nil {
	65	t.Fatal(err)
	66	}
	67	k1, err := st.SSHKeyByFingerprint("SHA256:k1")
	68	if err != nil {
	69	t.Fatal(err)
	70	}
	71	if _, err := st.CreateMR(repo.ID, uid, repo.ID, "feature", "main", "t", "", "abc", "md", false); err != nil {
	72	t.Fatal(err)
	73	}
	74	mr, err := st.MRByNumber(repo.ID, 1)
	75	if err != nil {
	76	t.Fatal(err)
	77	}
	78	mrID := mr.ID
	79	if err := st.QueueMerge(mrID, uid, "", k1.ID, 0); err != nil {
	80	t.Fatal(err)
	81	}
	82	if err := st.RemoveSSHKey(uid, "SHA256:k1"); err != nil {
	83	t.Fatal(err)
	84	}
	85	if err := st.AddSSHKey(uid, "SHA256:k2", "ssh-ed25519", []byte("k2"), "full", ""); err != nil {
	86	t.Fatal(err)
	87	}
	88	if k2, _ := st.SSHKeyByFingerprint("SHA256:k2"); k2.ID == k1.ID {
	89	t.Fatalf("new key took the removed key's id %d", k1.ID)
	90	}
	91	reason, err := queueCredentialLapsed(st, mrID, time.Now())
	92	if err != nil \|\| reason != "the key it was queued with was removed" {
	93	t.Fatalf("lapsed = %q, %v", reason, err)
	94	}
	95	}

internal/control/runnernext_test.go +48

		@@ -336,3 +336,51 @@ func TestRunnerDoneToleratesMissingOrBadStep(t *testing.T) {
336	336	}
337	337	}
338	338	}
	339
	340	// A runner still holding a build whose repository was deleted reports
	341	// on an id no later build takes: its runner done finds nothing rather
	342	// than finishing another repository's running build (#306).
	343	func TestRunnerDoneAfterRepositoryDeleted(t *testing.T) {
	344	st, keep, uid := newQueueTestRepo(t)
	345	goneID, err := st.CreateRepo("user", uid, "gone", "public")
	346	if err != nil {
	347	t.Fatal(err)
	348	}
	349	if _, err := st.CreateBuild(keep.ID, "unit", "aaa", "main", "[]", "", "", true); err != nil {
	350	t.Fatal(err)
	351	}
	352	if _, err := st.CreateBuild(goneID, "unit", "bbb", "main", "[]", "", "", true); err != nil {
	353	t.Fatal(err)
	354	}
	355	var stale store.Build
	356	for range 2 {
	357	b, ok, err := st.ClaimBuild(nil, false)
	358	if err != nil \|\| !ok {
	359	t.Fatalf("claim: %v ok=%v", err, ok)
	360	}
	361	if b.RepoID == goneID {
	362	stale = b
	363	}
	364	}
	365	if err := st.DeleteRepo(goneID); err != nil {
	366	t.Fatal(err)
	367	}
	368	if _, err := st.CreateBuild(keep.ID, "unit", "ccc", "main", "[]", "", "", true); err != nil {
	369	t.Fatal(err)
	370	}
	371	next, ok, err := st.ClaimBuild(nil, false)
	372	if err != nil \|\| !ok {
	373	t.Fatalf("claim: %v ok=%v", err, ok)
	374	}
	375	if next.ID == stale.ID {
	376	t.Fatalf("the next build took the deleted repository's build id %d", stale.ID)
	377	}
	378
	379	c, out := runnerCtx(st, uid, t.TempDir())
	380	if code := runRunnerDone(c, []string{fmt.Sprint(stale.ID), "success"}); code != protocol.ExitNotFound {
	381	t.Fatalf("runner done on the deleted build: exit %d, want %d: %s", code, protocol.ExitNotFound, out)
	382	}
	383	if b, err := st.BuildByID(next.ID); err != nil \|\| b.Status != "running" {
	384	t.Fatalf("the other build after the stale report: %+v, %v", b, err)
	385	}
	386	}

internal/httpd/lfsauth_test.go +38 −7

		@@ -253,9 +253,10 @@ func TestLFSUploadTokenRefusedOnceArchived(t *testing.T) {
253	253	}
254	254	}
255	255
256		// SQLite gives a new key the id of the highest deleted one. A token
257		// minted for the deleted key is refused when presented against the new
258		// key; the new key's own token works (#303).
	256	// A removed key's id is not handed to the next key (#306), so its token
	257	// names no live key. An id freed and taken before ids stopped being
	258	// reused is still refused by the fingerprint pin; the new key's own token
	259	// works (#303).
259	260	func TestLFSTokenRefusedOnReusedKeyID(t *testing.T) {
260	261	s, st, u := newTokenTestServer(t)
261	262	repo := lfsTestRepo(t, st, u.ID, "app", "private")
		@@ -269,14 +270,44 @@ func TestLFSTokenRefusedOnReusedKeyID(t *testing.T) {
269	270	if err := st.RemoveSSHKey(u.ID, "SHA256:old"); err != nil {
270	271	t.Fatal(err)
271	272	}
272		newID := lfsTestKey(t, st, u.ID, "SHA256:new", "full")
273		if newID != oldID {
274		t.Fatalf("new key got id %d, want the reused %d", newID, oldID)
	273	nextID := lfsTestKey(t, st, u.ID, "SHA256:next", "full")
	274	if nextID == oldID {
	275	t.Fatalf("new key took the removed key's id %d", oldID)
	276	}
	277	if op, _ := s.lfsAuth(lfsRequest(old), repo); op != "" {
	278	t.Errorf("removed key's token: %q", op)
	279	}
	280	if _, err := st.DB.Exec("INSERT INTO ssh_keys (id, user_id, fingerprint, algo, blob) VALUES (?, ?, 'SHA256:new', 'ssh-ed25519', x'00')",
	281	oldID, u.ID); err != nil {
	282	t.Fatal(err)
275	283	}
276	284	if op, _ := s.lfsAuth(lfsRequest(old), repo); op != "" {
277	285	t.Errorf("old key's token on the reused id: %q", op)
278	286	}
279		if op, _ := s.lfsAuth(lfsRequest(lfs.Sign(secret, repo.ID, newID, "SHA256:new", "upload", now)), repo); op != "upload" {
	287	if op, _ := s.lfsAuth(lfsRequest(lfs.Sign(secret, repo.ID, oldID, "SHA256:new", "upload", now)), repo); op != "upload" {
280	288	t.Errorf("new key's own token: %q", op)
281	289	}
282	290	}
	291
	292	// A token names its repository by id. Deleting the repository does not
	293	// let the next one take that id and the token with it (#306).
	294	func TestLFSTokenForDeletedRepository(t *testing.T) {
	295	s, st, u := newTokenTestServer(t)
	296	gone := lfsTestRepo(t, st, u.ID, "gone", "private")
	297	secret, err := s.lfsSecret()
	298	if err != nil {
	299	t.Fatal(err)
	300	}
	301	key := lfsTestKey(t, st, u.ID, "SHA256:owner", "full")
	302	tok := lfs.Sign(secret, gone.ID, key, "SHA256:owner", "upload", time.Now())
	303	if err := st.DeleteRepo(gone.ID); err != nil {
	304	t.Fatal(err)
	305	}
	306	next := lfsTestRepo(t, st, u.ID, "next", "private")
	307	if next.ID == gone.ID {
	308	t.Fatalf("new repository took the deleted one's id %d", gone.ID)
	309	}
	310	if op, _ := s.lfsAuth(lfsRequest(tok), next); op != "" {
	311	t.Errorf("deleted repository's token on the next repository: %q", op)
	312	}
	313	}

internal/lfs/lfs.go +4 −3

		@@ -130,9 +130,10 @@ const TokenTTL = time.Hour
130	130	// Sign mints a token for op ("download" or "upload") on repoID, bound
131	131	// to keyID: the SSH key, user or deploy, that asked for it, or 0 for an
132	132	// anonymous download of a public repository. fingerprint is that key's
133		// fingerprint, "" for key 0. SQLite reuses the id of a deleted key, so
134		// the token carries a hash of the fingerprint as well and a new key
135		// given the old id does not inherit the old key's tokens (#303).
	133	// fingerprint, "" for key 0. Key ids freed before they stopped being
	134	// reused (#306) may belong to a later key, so the token carries a hash
	135	// of the fingerprint as well and a new key given the old id does not
	136	// inherit the old key's tokens (#303).
136	137	func Sign(secret []byte, repoID, keyID int64, fingerprint, op string, now time.Time) string {
137	138	payload := fmt.Sprintf("%d:%d:%s:%s:%d", repoID, keyID, KeyPin(fingerprint), op, now.Add(TokenTTL).Unix())
138	139	mac := hmac.New(sha256.New, secret)

internal/mailin/mailin.go +3 −2

		@@ -187,8 +187,9 @@ func (p *Processor) Handle(raw []byte) Result {
187	187	case u.Pending:
188	188	return p.refuse(u.ID, msgID, "account not active")
189	189	}
190		// Ids are reused after a hard delete: an account created after the
191		// token was minted is not the one it named.
	190	// An id freed before ids stopped being reused (#306) may have been
	191	// taken: an account created after the token was minted is not the
	192	// one it named.
192	193	if code := p.createdAfter("users", u.ID, target, msgID, "account"); code != nil {
193	194	return *code
194	195	}

internal/mailin/mailin_test.go +25 −4

		@@ -336,7 +336,26 @@ func TestDrainRetriesTransientFailure(t *testing.T) {
336	336	}
337	337	}
338	338
339		// A repository id freed by a delete and taken by a later repository does
	339	// A deleted repository's id is not handed to the next repository
	340	// (#306), so a reply meant for it finds no repository.
	341	func TestDeletedRepositoryID(t *testing.T) {
	342	f := setup(t)
	343	m := []byte(f.message(t, "bob@example.test", "hi"))
	344	if err := f.st.DeleteRepo(f.repo.ID); err != nil {
	345	t.Fatal(err)
	346	}
	347	alice, _ := f.st.UserByUsername("alice")
	348	id, err := f.st.CreateRepo("user", alice.ID, "other", "public")
	349	if err != nil \|\| id == f.repo.ID {
	350	t.Fatalf("new repository has id %d (%v), the deleted one's", id, err)
	351	}
	352	res := f.p.Handle(m)
	353	if res.Posted \|\| !strings.Contains(res.Reason, "repository no longer exists") {
	354	t.Fatalf("result %+v", res)
	355	}
	356	}
	357
	358	// A repository id freed and taken before ids stopped being reused does
340	359	// not accept replies meant for the old one.
341	360	func TestReusedRepositoryID(t *testing.T) {
342	361	f := setup(t)
		@@ -345,9 +364,11 @@ func TestReusedRepositoryID(t *testing.T) {
345	364	t.Fatal(err)
346	365	}
347	366	alice, _ := f.st.UserByUsername("alice")
348		id, err := f.st.CreateRepo("user", alice.ID, "other", "public")
349		if err != nil \|\| id != f.repo.ID {
350		t.Fatalf("new repository has id %d (%v), want the freed %d", id, err, f.repo.ID)
	367	id := f.repo.ID
	368	if _, err := f.st.DB.Exec(
	369	"INSERT INTO repos (id, owner_kind, owner_id, name, visibility) VALUES (?, 'user', ?, 'other', 'public')",
	370	id, alice.ID); err != nil {
	371	t.Fatal(err)
351	372	}
352	373	f.st.CreateIssue(id, alice.ID, "t", "", "md")
353	374	// Created after the token, as it would be outside a fast test.

internal/store/builds.go +18 −2

		@@ -61,9 +61,25 @@ func (s *Store) CreateBuild(repoID int64, job, sha, ref, stepsJSON, image, tree
61	61	if err := tx.QueryRow("SELECT build_counter FROM repos WHERE id = ?", repoID).Scan(&n); err != nil {
62	62	return 0, err
63	63	}
	64	// A runner names a build by id in runner log and runner done, so an id
	65	// is never handed out twice, including after a repository's deletion
	66	// takes the newest builds with it (#306). builds is not AUTOINCREMENT
	67	// because rebuilding it would copy every stored log; the high-water
	68	// mark lives in settings instead.
	69	var id int64
	70	if err := tx.QueryRow(`SELECT MAX(
	71	COALESCE((SELECT MAX(id) FROM builds), 0),
	72	COALESCE((SELECT CAST(value AS INTEGER) FROM settings WHERE key = 'build_id_seq'), 0)) + 1`).
	73	Scan(&id); err != nil {
	74	return 0, err
	75	}
64	76	if _, err := tx.Exec(
65		"INSERT INTO builds (repo_id, number, job, sha, ref, steps, image, tree, trusted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
66		repoID, n, job, sha, ref, stepsJSON, image, tree, trusted); err != nil {
	77	"INSERT INTO builds (id, repo_id, number, job, sha, ref, steps, image, tree, trusted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
	78	id, repoID, n, job, sha, ref, stepsJSON, image, tree, trusted); err != nil {
	79	return 0, err
	80	}
	81	if _, err := tx.Exec(`INSERT INTO settings (key, value) VALUES ('build_id_seq', ?)
	82	ON CONFLICT (key) DO UPDATE SET value = excluded.value`, strconv.FormatInt(id, 10)); err != nil {
67	83	return 0, err
68	84	}
69	85	return n, tx.Commit()

internal/store/builds_test.go +48

		@@ -601,3 +601,51 @@ func TestSetBuildFailure(t *testing.T) {
601	601	t.Fatalf("rewrote a finished build: %v", err)
602	602	}
603	603	}
	604
	605	// A runner names its build by id in runner log and runner done. Deleting
	606	// the repository that holds the newest build must not hand that id to
	607	// the next build in another repository (#306).
	608	func TestBuildIDsNotReusedAfterRepoDelete(t *testing.T) {
	609	s := open(t)
	610	if err := s.MigrateUp(); err != nil {
	611	t.Fatal(err)
	612	}
	613	uid, err := s.CreateUser("cmc", true)
	614	if err != nil {
	615	t.Fatal(err)
	616	}
	617	keep, err := s.CreateRepo("user", uid, "keep", "public")
	618	if err != nil {
	619	t.Fatal(err)
	620	}
	621	gone, err := s.CreateRepo("user", uid, "gone", "public")
	622	if err != nil {
	623	t.Fatal(err)
	624	}
	625	newest := func() int64 {
	626	var id int64
	627	if err := s.DB.QueryRow("SELECT COALESCE(MAX(id), 0) FROM builds").Scan(&id); err != nil {
	628	t.Fatal(err)
	629	}
	630	return id
	631	}
	632	if _, err := s.CreateBuild(keep, "test", "abc", "main", `["true"]`, "", "", true); err != nil {
	633	t.Fatal(err)
	634	}
	635	if _, err := s.CreateBuild(gone, "test", "abc", "main", `["true"]`, "", "", true); err != nil {
	636	t.Fatal(err)
	637	}
	638	claimed := newest()
	639	if err := s.DeleteRepo(gone); err != nil {
	640	t.Fatal(err)
	641	}
	642	if newest() >= claimed {
	643	t.Fatal("the deleted repository's build survived")
	644	}
	645	if _, err := s.CreateBuild(keep, "test", "def", "main", `["true"]`, "", "", true); err != nil {
	646	t.Fatal(err)
	647	}
	648	if got := newest(); got <= claimed {
	649	t.Fatalf("next build took id %d; a runner may still hold %d", got, claimed)
	650	}
	651	}

internal/store/idreuse_test.go added +484

		@@ -0,0 +1,484 @@
	1	package store
	2
	3	import (
	4	"strconv"
	5	"strings"
	6	"testing"
	7	)
	8
	9	// idMigration is the version of the migration that makes ids
	10	// AUTOINCREMENT (#306), found by name so the test survives renumbering.
	11	func idMigration(t *testing.T) int {
	12	t.Helper()
	13	ms, err := loadMigrations()
	14	if err != nil {
	15	t.Fatal(err)
	16	}
	17	for _, m := range ms {
	18	if m.name == "id_autoincrement" {
	19	return m.version
	20	}
	21	}
	22	t.Fatal("no id_autoincrement migration")
	23	return 0
	24	}
	25
	26	var autoincTables = []string{"users", "orgs", "repos", "api_tokens", "ssh_keys", "webhook_deliveries", "push_queue"}
	27
	28	func mustExec(t testing.T, s Store, q string, args ...any) int64 {
	29	t.Helper()
	30	res, err := s.DB.Exec(q, args...)
	31	if err != nil {
	32	t.Fatalf("%s: %v", q, err)
	33	}
	34	id, _ := res.LastInsertId()
	35	return id
	36	}
	37
	38	func count(t testing.T, s Store, table string) int {
	39	t.Helper()
	40	var n int
	41	if err := s.DB.QueryRow("SELECT COUNT(*) FROM " + table).Scan(&n); err != nil {
	42	t.Fatal(err)
	43	}
	44	return n
	45	}
	46
	47	func fkClean(t testing.T, s Store) {
	48	t.Helper()
	49	rows, err := s.DB.Query("PRAGMA foreign_key_check")
	50	if err != nil {
	51	t.Fatal(err)
	52	}
	53	defer rows.Close()
	54	if rows.Next() {
	55	var table, parent string
	56	var rowid, fk any
	57	rows.Scan(&table, &rowid, &parent, &fk)
	58	t.Fatalf("foreign_key_check: %s row %v -> %s", table, rowid, parent)
	59	}
	60	}
	61
	62	// seedForIDs fills every rebuilt table and the rows that name their ids,
	63	// then deletes the newest repository and the newest account while a
	64	// deploy key and a grant still name them.
	65	func seedForIDs(t testing.T, s Store) (goneRepo, goneUser int64) {
	66	t.Helper()
	67	alice := mustExec(t, s, "INSERT INTO users (username) VALUES ('alice')")
	68	org := mustExec(t, s, "INSERT INTO orgs (name) VALUES ('acme')")
	69	repo := mustExec(t, s, "INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, 'app', 'public')", alice)
	70	mustExec(t, s, "INSERT INTO repos (owner_kind, owner_id, name, visibility, fork_of) VALUES ('org', ?, 'fork', 'private', ?)", org, repo)
	71	tok := mustExec(t, s, "INSERT INTO api_tokens (user_id, name, token_hash) VALUES (?, 't1', 'h1')", alice)
	72	mustExec(t, s, "INSERT INTO api_tokens (user_id, name, token_hash, created_by_token) VALUES (?, 't2', 'h2', ?)", alice, tok)
	73	mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, created_by_token) VALUES (?, 'SHA256:a', 'ssh-ed25519', x'00', ?)", alice, tok)
	74	hook := mustExec(t, s, "INSERT INTO webhooks (repo_id, url) VALUES (?, 'https://example.org/h')", repo)
	75	ev := mustExec(t, s, "INSERT INTO events (repo_id, actor_id, kind) VALUES (?, ?, 'push')", repo, alice)
	76	mustExec(t, s, "INSERT INTO webhook_deliveries (webhook_id, event_id) VALUES (?, ?)", hook, ev)
	77	dev := mustExec(t, s, "INSERT INTO push_devices (user_id, token) VALUES (?, 'devtok')", alice)
	78	mustExec(t, s, "INSERT INTO push_queue (device_id, title, body, path) VALUES (?, 't', 'b', 'p')", dev)
	79	mustExec(t, s, "INSERT INTO org_members (org_id, user_id, role) VALUES (?, ?, 'admin')", org, alice)
	80
	81	goneRepo = mustExec(t, s, "INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, 'gone', 'private')", alice)
	82	mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, 'SHA256:d', 'ssh-ed25519', x'00', ?)",
	83	alice, "deploy:"+itoa(goneRepo)+":rw")
	84	// Raw deletes: DeleteRepo and DeleteUser now take the deploy key and
	85	// the grant with them, and these orphans stand for ones left earlier.
	86	mustExec(t, s, "DELETE FROM repos WHERE id = ?", goneRepo)
	87	goneUser = mustExec(t, s, "INSERT INTO users (username) VALUES ('carol')")
	88	if err := s.GrantAccess(repo, goneUser, "write"); err != nil {
	89	t.Fatal(err)
	90	}
	91	mustExec(t, s, "DELETE FROM users WHERE id = ?", goneUser)
	92	return goneRepo, goneUser
	93	}
	94
	95	func itoa(n int64) string { return strconv.FormatInt(n, 10) }
	96
	97	func TestIDMigrationKeepsRowsAndForeignKeys(t *testing.T) {
	98	v := idMigration(t)
	99	s := open(t)
	100	if err := s.MigrateTo(v - 1); err != nil {
	101	t.Fatal(err)
	102	}
	103	goneRepo, goneUser := seedForIDs(t, s)
	104	before := map[string]int{}
	105	for _, tbl := range append(autoincTables, "org_members", "webhooks", "events", "push_devices", "repo_access") {
	106	before[tbl] = count(t, s, tbl)
	107	}
	108	if err := s.MigrateTo(v); err != nil {
	109	t.Fatal(err)
	110	}
	111	for tbl, n := range before {
	112	if got := count(t, s, tbl); got != n {
	113	t.Errorf("%s: %d rows after the migration, want %d", tbl, got, n)
	114	}
	115	}
	116	for _, tbl := range autoincTables {
	117	var sql string
	118	if err := s.DB.QueryRow("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?", tbl).Scan(&sql); err != nil {
	119	t.Fatal(err)
	120	}
	121	if !strings.Contains(sql, "AUTOINCREMENT") {
	122	t.Errorf("%s is not AUTOINCREMENT", tbl)
	123	}
	124	}
	125	fkClean(t, s)
	126
	127	// Children still name the rebuilt parents, not the *_old tables.
	128	var n int
	129	if err := s.DB.QueryRow(`SELECT COUNT(*) FROM sqlite_master m, pragma_foreign_key_list(m.name) f
	130	WHERE m.type = 'table' AND f."table" LIKE '%_old'`).Scan(&n); err != nil {
	131	t.Fatal(err)
	132	}
	133	if n != 0 {
	134	t.Fatalf("%d foreign keys name a *_old table", n)
	135	}
	136	for _, c := range []struct{ child, parent string }{
	137	{"ssh_keys", "users"}, {"ssh_keys", "api_tokens"}, {"api_tokens", "api_tokens"},
	138	{"repos", "repos"}, {"issues", "repos"}, {"teams", "orgs"}, {"mr_merge_queue", "ssh_keys"},
	139	{"push_queue", "push_devices"}, {"webhook_deliveries", "webhooks"},
	140	} {
	141	if err := s.DB.QueryRow(`SELECT COUNT(*) FROM pragma_foreign_key_list(?) WHERE "table" = ?`, c.child, c.parent).Scan(&n); err != nil \|\| n == 0 {
	142	t.Errorf("%s has no foreign key to %s (%v)", c.child, c.parent, err)
	143	}
	144	}
	145	for _, idx := range []string{"ssh_keys_user", "webhook_deliveries_due", "push_queue_due"} {
	146	if err := s.DB.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name = ?", idx).Scan(&n); err != nil \|\| n != 1 {
	147	t.Errorf("index %s missing", idx)
	148	}
	149	}
	150
	151	// The triggers still fire.
	152	alice, err := s.UserByUsername("alice")
	153	if err != nil {
	154	t.Fatal(err)
	155	}
	156	if _, err := s.DB.Exec("DELETE FROM users WHERE id = ?", alice.ID); err == nil \|\| !strings.Contains(err.Error(), "still owns repositories") {
	157	t.Fatalf("deleting a repository owner: %v", err)
	158	}
	159	if _, err := s.DB.Exec("DELETE FROM orgs WHERE name = 'acme'"); err == nil \|\| !strings.Contains(err.Error(), "still owns repositories") {
	160	t.Fatalf("deleting an owning org: %v", err)
	161	}
	162	// Cascades still reach the rebuilt tables' children.
	163	mustExec(t, s, "DELETE FROM push_devices WHERE token = 'devtok'")
	164	if got := count(t, s, "push_queue"); got != 0 {
	165	t.Fatalf("push_queue after its device went: %d rows", got)
	166	}
	167
	168	// The sequences start above the ids a deploy key and a grant still
	169	// name, though neither row survived.
	170	var seq int64
	171	s.DB.QueryRow("SELECT seq FROM sqlite_sequence WHERE name = 'repos'").Scan(&seq)
	172	if seq < goneRepo {
	173	t.Fatalf("repos sequence %d, want at least %d", seq, goneRepo)
	174	}
	175	r, err := s.CreateRepo("user", alice.ID, "new", "public")
	176	if err != nil {
	177	t.Fatal(err)
	178	}
	179	if r <= goneRepo {
	180	t.Fatalf("new repository took id %d; a deploy key still names %d", r, goneRepo)
	181	}
	182	u, err := s.CreateUser("dave", false)
	183	if err != nil {
	184	t.Fatal(err)
	185	}
	186	if u <= goneUser {
	187	t.Fatalf("new account took id %d; a grant still names %d", u, goneUser)
	188	}
	189
	190	// Down and up again keep the rows.
	191	if err := s.MigrateTo(v - 1); err != nil {
	192	t.Fatal(err)
	193	}
	194	if got := count(t, s, "users"); got != before["users"]+1 {
	195	t.Fatalf("users after down: %d", got)
	196	}
	197	if err := s.DB.QueryRow("SELECT COUNT(*) FROM sqlite_sequence WHERE name = 'users'").Scan(&n); err != nil \|\| n != 0 {
	198	t.Fatalf("sqlite_sequence keeps users after down: %d, %v", n, err)
	199	}
	200	fkClean(t, s)
	201	if err := s.MigrateUp(); err != nil {
	202	t.Fatal(err)
	203	}
	204	fkClean(t, s)
	205	}
	206
	207	// A parked profile about text names its owner by kind and id with no
	208	// foreign key; the sequences start above the ids it names (#306).
	209	func TestIDMigrationSeedsFromAboutBackfill(t *testing.T) {
	210	v := idMigration(t)
	211	s := open(t)
	212	if err := s.MigrateTo(v - 1); err != nil {
	213	t.Fatal(err)
	214	}
	215	mustExec(t, s, "INSERT INTO users (username) VALUES ('alice')")
	216	mustExec(t, s, "INSERT INTO orgs (name) VALUES ('acme')")
	217	mustExec(t, s, `INSERT INTO profile_about_backfill (owner_kind, owner_id, about, about_format)
	218	VALUES ('user', 50, 'a', 'md'), ('org', 40, 'b', 'md')`)
	219	if err := s.MigrateTo(v); err != nil {
	220	t.Fatal(err)
	221	}
	222	for table, want := range map[string]int64{"users": 50, "orgs": 40} {
	223	var seq int64
	224	if err := s.DB.QueryRow("SELECT seq FROM sqlite_sequence WHERE name = ?", table).Scan(&seq); err != nil {
	225	t.Fatal(err)
	226	}
	227	if seq < want {
	228	t.Errorf("%s sequence %d, want at least %d", table, seq, want)
	229	}
	230	}
	231	}
	232
	233	// Deleting the row with the highest id does not free that id, in any of
	234	// the rebuilt tables.
	235	func TestIDsNotReusedAfterDelete(t *testing.T) {
	236	s := open(t)
	237	if err := s.MigrateUp(); err != nil {
	238	t.Fatal(err)
	239	}
	240	u1 := mustExec(t, s, "INSERT INTO users (username) VALUES ('u1')")
	241	u2 := mustExec(t, s, "INSERT INTO users (username) VALUES ('u2')")
	242	repo := mustExec(t, s, "INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, 'r', 'public')", u1)
	243	hook := mustExec(t, s, "INSERT INTO webhooks (repo_id, url) VALUES (?, 'https://example.org/h')", repo)
	244	ev := mustExec(t, s, "INSERT INTO events (repo_id, kind) VALUES (?, 'push')", repo)
	245	dev := mustExec(t, s, "INSERT INTO push_devices (user_id, token) VALUES (?, 'devtok')", u1)
	246
	247	cases := []struct {
	248	table, insert string
	249	args func(i int) []any
	250	}{
	251	{"users", "INSERT INTO users (username) VALUES (?)", func(i int) []any { return []any{"x" + itoa(int64(i))} }},
	252	{"orgs", "INSERT INTO orgs (name) VALUES (?)", func(i int) []any { return []any{"o" + itoa(int64(i))} }},
	253	{"repos", "INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, ?, 'public')",
	254	func(i int) []any { return []any{u1, "n" + itoa(int64(i))} }},
	255	{"api_tokens", "INSERT INTO api_tokens (user_id, name, token_hash) VALUES (?, ?, ?)",
	256	func(i int) []any { return []any{u2, "t" + itoa(int64(i)), "h" + itoa(int64(i))} }},
	257	{"ssh_keys", "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob) VALUES (?, ?, 'ssh-ed25519', x'00')",
	258	func(i int) []any { return []any{u2, "SHA256:" + itoa(int64(i))} }},
	259	{"webhook_deliveries", "INSERT INTO webhook_deliveries (webhook_id, event_id) VALUES (?, ?)",
	260	func(int) []any { return []any{hook, ev} }},
	261	{"push_queue", "INSERT INTO push_queue (device_id, title, body, path) VALUES (?, 't', 'b', 'p')",
	262	func(int) []any { return []any{dev} }},
	263	}
	264	for _, c := range cases {
	265	first := mustExec(t, s, c.insert, c.args(1)...)
	266	mustExec(t, s, "DELETE FROM "+c.table+" WHERE id = ?", first)
	267	second := mustExec(t, s, c.insert, c.args(2)...)
	268	if second <= first {
	269	t.Errorf("%s: id %d handed out again after its row was deleted (got %d)", c.table, first, second)
	270	}
	271	}
	272	}
	273
	274	// A sender marks a webhook delivery or a push by id after its request
	275	// returns. If the hook or device was removed meanwhile, the cascade took
	276	// the row, and the next row must not take its id and its mark (#306).
	277	func TestInFlightMarksAfterCascade(t *testing.T) {
	278	s := open(t)
	279	if err := s.MigrateUp(); err != nil {
	280	t.Fatal(err)
	281	}
	282	u := mustExec(t, s, "INSERT INTO users (username) VALUES ('u')")
	283	repo := mustExec(t, s, "INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, 'r', 'public')", u)
	284	ev := mustExec(t, s, "INSERT INTO events (repo_id, kind) VALUES (?, 'push')", repo)
	285	h1 := mustExec(t, s, "INSERT INTO webhooks (repo_id, url) VALUES (?, 'https://example.org/1')", repo)
	286	h2 := mustExec(t, s, "INSERT INTO webhooks (repo_id, url) VALUES (?, 'https://example.org/2')", repo)
	287	inFlight := mustExec(t, s, "INSERT INTO webhook_deliveries (webhook_id, event_id) VALUES (?, ?)", h1, ev)
	288	if err := s.RemoveWebhook(repo, h1); err != nil {
	289	t.Fatal(err)
	290	}
	291	next := mustExec(t, s, "INSERT INTO webhook_deliveries (webhook_id, event_id) VALUES (?, ?)", h2, ev)
	292	if err := s.MarkDelivered(inFlight, 200); err != nil {
	293	t.Fatal(err)
	294	}
	295	var delivered *string
	296	if err := s.DB.QueryRow("SELECT delivered_at FROM webhook_deliveries WHERE id = ?", next).Scan(&delivered); err != nil {
	297	t.Fatal(err)
	298	}
	299	if delivered != nil {
	300	t.Fatal("the removed hook's delivery was marked on the next hook's")
	301	}
	302
	303	d1 := mustExec(t, s, "INSERT INTO push_devices (user_id, token) VALUES (?, 'd1')", u)
	304	d2 := mustExec(t, s, "INSERT INTO push_devices (user_id, token) VALUES (?, 'd2')", u)
	305	pushing := mustExec(t, s, "INSERT INTO push_queue (device_id, title, body, path) VALUES (?, 't', 'b', 'p')", d1)
	306	if err := s.RemovePushDevice(u, d1); err != nil {
	307	t.Fatal(err)
	308	}
	309	queued := mustExec(t, s, "INSERT INTO push_queue (device_id, title, body, path) VALUES (?, 't', 'b', 'p')", d2)
	310	if err := s.MarkPushSent(pushing); err != nil {
	311	t.Fatal(err)
	312	}
	313	var sent *string
	314	if err := s.DB.QueryRow("SELECT sent_at FROM push_queue WHERE id = ?", queued).Scan(&sent); err != nil {
	315	t.Fatal(err)
	316	}
	317	if sent != nil {
	318	t.Fatal("the removed device's push was marked on the next device's")
	319	}
	320	}
	321
	322	// Deleting a repository takes the deploy keys scoped to it, and only
	323	// those; deleting an account or an org takes its grants (#306).
	324	func TestDeletesTakeGrantsAndDeployKeys(t *testing.T) {
	325	s := open(t)
	326	if err := s.MigrateUp(); err != nil {
	327	t.Fatal(err)
	328	}
	329	var revoked []Revoked
	330	s.OnRevoke(func(r Revoked) { revoked = append(revoked, r) })
	331	alice := mustExec(t, s, "INSERT INTO users (username) VALUES ('alice')")
	332	var repos []int64
	333	for i := range 12 {
	334	repos = append(repos, mustExec(t, s,
	335	"INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, ?, 'public')", alice, "r"+itoa(int64(i))))
	336	}
	337	// repos[0] is 2 and repos[10] is 12: a prefix of one id must not
	338	// match the other.
	339	gone, other := repos[0], repos[len(repos)-2]
	340	if !strings.HasPrefix(itoa(other), itoa(gone)) {
	341	t.Fatalf("ids %d and %d do not share a prefix", gone, other)
	342	}
	343	goneKey := mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, 'SHA256:g', 'a', x'00', ?)",
	344	alice, "deploy:"+itoa(gone)+":rw")
	345	mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, 'SHA256:o', 'a', x'00', ?)",
	346	alice, "deploy:"+itoa(other)+":ro")
	347	if err := s.DeleteRepo(gone); err != nil {
	348	t.Fatal(err)
	349	}
	350	var n int
	351	s.DB.QueryRow("SELECT COUNT(*) FROM ssh_keys WHERE fingerprint = 'SHA256:g'").Scan(&n)
	352	if n != 0 {
	353	t.Fatal("the deleted repository's deploy key survived")
	354	}
	355	s.DB.QueryRow("SELECT COUNT(*) FROM ssh_keys WHERE fingerprint = 'SHA256:o'").Scan(&n)
	356	if n != 1 {
	357	t.Fatal("another repository's deploy key went with it")
	358	}
	359	if len(revoked) != 1 \|\| len(revoked[0].KeyIDs) != 1 \|\| revoked[0].KeyIDs[0] != goneKey {
	360	t.Fatalf("revocations announced: %+v", revoked)
	361	}
	362	if err := s.DeleteRepo(gone); err != ErrNotFound {
	363	t.Fatalf("deleting it again: %v", err)
	364	}
	365
	366	bob := mustExec(t, s, "INSERT INTO users (username) VALUES ('bob')")
	367	org := mustExec(t, s, "INSERT INTO orgs (name) VALUES ('acme')")
	368	if err := s.GrantAccess(other, bob, "write"); err != nil {
	369	t.Fatal(err)
	370	}
	371	mustExec(t, s, "INSERT INTO repo_access (repo_id, subject_kind, subject_id, role) VALUES (?, 'org', ?, 'read')", other, org)
	372	// An org with the same id as bob's keeps its grant when bob goes.
	373	mustExec(t, s, "INSERT INTO repo_access (repo_id, subject_kind, subject_id, role) VALUES (?, 'org', ?, 'read')", repos[1], bob)
	374	mustExec(t, s, `INSERT INTO profile_about_backfill (owner_kind, owner_id, about, about_format)
	375	VALUES ('user', ?, 'a', 'md'), ('org', ?, 'b', 'md'), ('org', ?, 'c', 'md')`, bob, org, bob)
	376	if err := s.DeleteUser(bob); err != nil {
	377	t.Fatal(err)
	378	}
	379	s.DB.QueryRow("SELECT COUNT(*) FROM repo_access WHERE subject_kind = 'user' AND subject_id = ?", bob).Scan(&n)
	380	if n != 0 {
	381	t.Fatal("the deleted account's grant survived")
	382	}
	383	s.DB.QueryRow("SELECT COUNT(*) FROM profile_about_backfill WHERE owner_kind = 'user' AND owner_id = ?", bob).Scan(&n)
	384	if n != 0 {
	385	t.Fatal("the deleted account's about text survived")
	386	}
	387	s.DB.QueryRow("SELECT COUNT(*) FROM repo_access WHERE subject_kind = 'org' AND subject_id = ?", bob).Scan(&n)
	388	if n != 1 {
	389	t.Fatal("an org grant went with the account of the same id")
	390	}
	391	if err := s.DeleteOrg(org); err != nil {
	392	t.Fatal(err)
	393	}
	394	s.DB.QueryRow("SELECT COUNT(*) FROM repo_access WHERE subject_kind = 'org' AND subject_id = ?", org).Scan(&n)
	395	if n != 0 {
	396	t.Fatal("the deleted org's grant survived")
	397	}
	398	s.DB.QueryRow("SELECT COUNT(*) FROM profile_about_backfill WHERE owner_kind = 'org'").Scan(&n)
	399	if n != 1 {
	400	t.Fatalf("org about texts after deleting acme: %d, want only the one with bob's id", n)
	401	}
	402	}
	403
	404	// The cleanup migration removes grants and deploy keys left by earlier
	405	// deletes, keeps live ones, and leaves a note with the counts.
	406	func TestOrphanCleanupMigration(t *testing.T) {
	407	ms, err := loadMigrations()
	408	if err != nil {
	409	t.Fatal(err)
	410	}
	411	v := 0
	412	for _, m := range ms {
	413	if m.name == "orphan_grants_deploy_keys" {
	414	v = m.version
	415	}
	416	}
	417	if v == 0 {
	418	t.Fatal("no orphan_grants_deploy_keys migration")
	419	}
	420	s := open(t)
	421	if err := s.MigrateTo(v - 1); err != nil {
	422	t.Fatal(err)
	423	}
	424	alice := mustExec(t, s, "INSERT INTO users (username) VALUES ('alice')")
	425	repo := mustExec(t, s, "INSERT INTO repos (owner_kind, owner_id, name, visibility) VALUES ('user', ?, 'r', 'public')", alice)
	426	mustExec(t, s, "INSERT INTO repo_access (repo_id, subject_kind, subject_id, role) VALUES (?, 'user', ?, 'read')", repo, alice)
	427	mustExec(t, s, "INSERT INTO repo_access (repo_id, subject_kind, subject_id, role) VALUES (?, 'user', 999, 'write')", repo)
	428	mustExec(t, s, "INSERT INTO repo_access (repo_id, subject_kind, subject_id, role) VALUES (?, 'org', 998, 'read')", repo)
	429	mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, 'SHA256:live', 'a', x'00', ?)",
	430	alice, "deploy:"+itoa(repo)+":rw")
	431	mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob, scope) VALUES (?, 'SHA256:dead', 'a', x'00', 'deploy:997:ro')", alice)
	432	mustExec(t, s, "INSERT INTO ssh_keys (user_id, fingerprint, algo, blob) VALUES (?, 'SHA256:user', 'a', x'00')", alice)
	433	mustExec(t, s, `INSERT INTO profile_about_backfill (owner_kind, owner_id, about, about_format)
	434	VALUES ('user', ?, 'live', 'md'), ('user', 996, 'dead', 'md'), ('org', 995, 'dead', 'md')`, alice)
	435	var epoch int
	436	s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&epoch)
	437	if err := s.MigrateTo(v); err != nil {
	438	t.Fatal(err)
	439	}
	440	if got := count(t, s, "repo_access"); got != 1 {
	441	t.Fatalf("repo_access: %d rows, want the live grant", got)
	442	}
	443	var about string
	444	if err := s.DB.QueryRow("SELECT group_concat(about) FROM profile_about_backfill").Scan(&about); err != nil \|\| about != "live" {
	445	t.Fatalf("about texts after cleanup: %q, %v", about, err)
	446	}
	447	var fps []string
	448	rows, err := s.DB.Query("SELECT fingerprint FROM ssh_keys ORDER BY fingerprint")
	449	if err != nil {
	450	t.Fatal(err)
	451	}
	452	for rows.Next() {
	453	var fp string
	454	rows.Scan(&fp)
	455	fps = append(fps, fp)
	456	}
	457	rows.Close()
	458	if strings.Join(fps, " ") != "SHA256:live SHA256:user" {
	459	t.Fatalf("keys after cleanup: %v", fps)
	460	}
	461	var after int
	462	s.DB.QueryRow("SELECT value FROM settings WHERE key = 'key_epoch'").Scan(&after)
	463	if after != epoch+1 {
	464	t.Fatalf("key_epoch %d, want %d", after, epoch+1)
	465	}
	466	note, err := s.TakeMigrationNote()
	467	if err != nil \|\| note != "removed grants of deleted accounts or organizations: 2; deploy keys of deleted repositories: 1; profile about texts of deleted accounts or organizations: 2" {
	468	t.Fatalf("note %q, %v", note, err)
	469	}
	470	if note, _ := s.TakeMigrationNote(); note != "" {
	471	t.Fatalf("note not cleared: %q", note)
	472	}
	473
	474	// Nothing to remove, no note.
	475	if err := s.MigrateTo(v - 1); err != nil {
	476	t.Fatal(err)
	477	}
	478	if err := s.MigrateTo(v); err != nil {
	479	t.Fatal(err)
	480	}
	481	if note, _ := s.TakeMigrationNote(); note != "" {
	482	t.Fatalf("note with nothing removed: %q", note)
	483	}
	484	}

internal/store/migrations/0074_id_autoincrement.down.sql added +175

		@@ -0,0 +1,175 @@
	1	-- foreign_keys: off
	2	-- Back to ids without AUTOINCREMENT: the same rebuild with the keyword
	3	-- dropped, and the tables' sqlite_sequence rows removed.
	4	PRAGMA legacy_alter_table = ON;
	5
	6	DROP TRIGGER users_owning_repos;
	7	DROP TRIGGER orgs_owning_repos;
	8
	9	ALTER TABLE users RENAME TO users_old;
	10	CREATE TABLE users (
	11	id INTEGER PRIMARY KEY,
	12	username TEXT NOT NULL UNIQUE,
	13	is_admin INTEGER NOT NULL DEFAULT 0,
	14	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	15	pending INTEGER NOT NULL DEFAULT 0,
	16	description TEXT NOT NULL DEFAULT '',
	17	website TEXT NOT NULL DEFAULT '',
	18	disabled INTEGER NOT NULL DEFAULT 0,
	19	links TEXT NOT NULL DEFAULT '',
	20	repo_limit INTEGER,
	21	byte_limit INTEGER,
	22	notify_mail INTEGER NOT NULL DEFAULT 1,
	23	notify_watch INTEGER NOT NULL DEFAULT 0,
	24	theme TEXT NOT NULL DEFAULT 'system',
	25	notify_push INTEGER NOT NULL DEFAULT 1,
	26	diff_layout TEXT NOT NULL DEFAULT 'unified',
	27	notify_reply INTEGER NOT NULL DEFAULT 0
	28	);
	29	INSERT INTO users (id, username, is_admin, created_at, pending, description, website, disabled,
	30	links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply)
	31	SELECT id, username, is_admin, created_at, pending, description, website, disabled,
	32	links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply
	33	FROM users_old;
	34	DROP TABLE users_old;
	35
	36	ALTER TABLE orgs RENAME TO orgs_old;
	37	CREATE TABLE orgs (
	38	id INTEGER PRIMARY KEY,
	39	name TEXT NOT NULL UNIQUE,
	40	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	41	description TEXT NOT NULL DEFAULT '',
	42	website TEXT NOT NULL DEFAULT '',
	43	members_role TEXT NOT NULL DEFAULT 'write'
	44	CHECK (members_role IN ('write', 'read', 'none')),
	45	links TEXT NOT NULL DEFAULT ''
	46	);
	47	INSERT INTO orgs (id, name, created_at, description, website, members_role, links)
	48	SELECT id, name, created_at, description, website, members_role, links FROM orgs_old;
	49	DROP TABLE orgs_old;
	50
	51	ALTER TABLE repos RENAME TO repos_old;
	52	CREATE TABLE repos (
	53	id INTEGER PRIMARY KEY,
	54	owner_kind TEXT NOT NULL CHECK (owner_kind IN ('user','org')),
	55	owner_id INTEGER NOT NULL,
	56	name TEXT NOT NULL,
	57	visibility TEXT NOT NULL CHECK (visibility IN ('public','private')),
	58	default_branch TEXT NOT NULL DEFAULT 'main',
	59	fork_of INTEGER REFERENCES repos(id) ON DELETE SET NULL,
	60	issue_counter INTEGER NOT NULL DEFAULT 0,
	61	mr_counter INTEGER NOT NULL DEFAULT 0,
	62	settings_json TEXT NOT NULL DEFAULT '{}',
	63	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	64	build_counter INTEGER NOT NULL DEFAULT 0,
	65	UNIQUE (owner_kind, owner_id, name)
	66	);
	67	INSERT INTO repos (id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
	68	issue_counter, mr_counter, settings_json, created_at, build_counter)
	69	SELECT id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
	70	issue_counter, mr_counter, settings_json, created_at, build_counter
	71	FROM repos_old;
	72	DROP TABLE repos_old;
	73
	74	ALTER TABLE api_tokens RENAME TO api_tokens_old;
	75	CREATE TABLE api_tokens (
	76	id INTEGER PRIMARY KEY,
	77	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	78	name TEXT NOT NULL,
	79	token_hash TEXT NOT NULL UNIQUE,
	80	scope TEXT NOT NULL DEFAULT 'full' CHECK (scope IN ('full','read')),
	81	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	82	expires_at TEXT,
	83	last_used_at TEXT,
	84	created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
	85	UNIQUE (user_id, name)
	86	);
	87	INSERT INTO api_tokens (id, user_id, name, token_hash, scope, created_at, expires_at,
	88	last_used_at, created_by_token)
	89	SELECT id, user_id, name, token_hash, scope, created_at, expires_at,
	90	last_used_at, created_by_token
	91	FROM api_tokens_old;
	92	DROP TABLE api_tokens_old;
	93
	94	ALTER TABLE ssh_keys RENAME TO ssh_keys_old;
	95	CREATE TABLE ssh_keys (
	96	id INTEGER PRIMARY KEY,
	97	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	98	fingerprint TEXT NOT NULL UNIQUE,
	99	algo TEXT NOT NULL,
	100	blob BLOB NOT NULL,
	101	scope TEXT NOT NULL DEFAULT 'full',
	102	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	103	last_used_at TEXT,
	104	label TEXT NOT NULL DEFAULT '',
	105	created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
	106	expires_at TEXT
	107	);
	108	INSERT INTO ssh_keys (id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
	109	label, created_by_token, expires_at)
	110	SELECT id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
	111	label, created_by_token, expires_at
	112	FROM ssh_keys_old;
	113	DROP TABLE ssh_keys_old;
	114	CREATE INDEX ssh_keys_user ON ssh_keys(user_id);
	115
	116	ALTER TABLE webhook_deliveries RENAME TO webhook_deliveries_old;
	117	CREATE TABLE webhook_deliveries (
	118	id INTEGER PRIMARY KEY,
	119	webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
	120	event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
	121	attempts INTEGER NOT NULL DEFAULT 0,
	122	next_attempt_at TEXT,
	123	delivered_at TEXT,
	124	failed_at TEXT,
	125	last_status INTEGER,
	126	last_error TEXT,
	127	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
	128	);
	129	INSERT INTO webhook_deliveries (id, webhook_id, event_id, attempts, next_attempt_at,
	130	delivered_at, failed_at, last_status, last_error, created_at)
	131	SELECT id, webhook_id, event_id, attempts, next_attempt_at,
	132	delivered_at, failed_at, last_status, last_error, created_at
	133	FROM webhook_deliveries_old;
	134	DROP TABLE webhook_deliveries_old;
	135	CREATE INDEX webhook_deliveries_due ON webhook_deliveries(next_attempt_at)
	136	WHERE delivered_at IS NULL AND failed_at IS NULL;
	137
	138	ALTER TABLE push_queue RENAME TO push_queue_old;
	139	CREATE TABLE push_queue (
	140	id INTEGER PRIMARY KEY,
	141	device_id INTEGER NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
	142	title TEXT NOT NULL,
	143	body TEXT NOT NULL,
	144	path TEXT NOT NULL,
	145	attempts INTEGER NOT NULL DEFAULT 0,
	146	next_attempt_at TEXT,
	147	sent_at TEXT,
	148	failed_at TEXT,
	149	last_error TEXT,
	150	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
	151	);
	152	INSERT INTO push_queue (id, device_id, title, body, path, attempts, next_attempt_at,
	153	sent_at, failed_at, last_error, created_at)
	154	SELECT id, device_id, title, body, path, attempts, next_attempt_at,
	155	sent_at, failed_at, last_error, created_at
	156	FROM push_queue_old;
	157	DROP TABLE push_queue_old;
	158	CREATE INDEX push_queue_due ON push_queue(next_attempt_at)
	159	WHERE sent_at IS NULL AND failed_at IS NULL;
	160
	161	CREATE TRIGGER users_owning_repos BEFORE DELETE ON users
	162	WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'user' AND owner_id = OLD.id)
	163	BEGIN
	164	SELECT RAISE(ABORT, 'user still owns repositories');
	165	END;
	166	CREATE TRIGGER orgs_owning_repos BEFORE DELETE ON orgs
	167	WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'org' AND owner_id = OLD.id)
	168	BEGIN
	169	SELECT RAISE(ABORT, 'organization still owns repositories');
	170	END;
	171
	172	PRAGMA legacy_alter_table = OFF;
	173
	174	DELETE FROM sqlite_sequence WHERE name IN
	175	('users', 'orgs', 'repos', 'api_tokens', 'ssh_keys', 'webhook_deliveries', 'push_queue');

internal/store/migrations/0074_id_autoincrement.up.sql added +211

		@@ -0,0 +1,211 @@
	1	-- foreign_keys: off
	2	-- Ids that are named after their row is gone are never handed out again
	3	-- (#306). Without AUTOINCREMENT SQLite gives a new row MAX(id)+1, so
	4	-- deleting the newest account, organization, repository, key or token
	5	-- let the next one take its id, and with it whatever still named that
	6	-- id: a deploy key's scope, a repo_access grant, a signed LFS or
	7	-- reply-by-mail token, a hook's environment. webhook_deliveries and
	8	-- push_queue rows are named by id by a sender that is mid-request when
	9	-- a cascade can remove them.
	10	--
	11	-- Each table is rebuilt the way 0052 rebuilds labels: foreign keys off
	12	-- for the step, legacy_alter_table so the children keep naming the
	13	-- table through the rename and bind to the new one, and the runner's
	14	-- foreign_key_check before commit. Rows keep their ids; indexes and
	15	-- triggers are recreated. sqlite_sequence starts at the highest id in
	16	-- the table or named anywhere else, so an id already freed and still
	17	-- named (a deploy key for a deleted repository, a grant, an audit row or
	18	-- a parked profile about text for a deleted owner) is not handed out
	19	-- either.
	20	PRAGMA legacy_alter_table = ON;
	21
	22	DROP TRIGGER users_owning_repos;
	23	DROP TRIGGER orgs_owning_repos;
	24
	25	ALTER TABLE users RENAME TO users_old;
	26	CREATE TABLE users (
	27	id INTEGER PRIMARY KEY AUTOINCREMENT,
	28	username TEXT NOT NULL UNIQUE,
	29	is_admin INTEGER NOT NULL DEFAULT 0,
	30	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	31	pending INTEGER NOT NULL DEFAULT 0,
	32	description TEXT NOT NULL DEFAULT '',
	33	website TEXT NOT NULL DEFAULT '',
	34	disabled INTEGER NOT NULL DEFAULT 0,
	35	links TEXT NOT NULL DEFAULT '',
	36	repo_limit INTEGER,
	37	byte_limit INTEGER,
	38	notify_mail INTEGER NOT NULL DEFAULT 1,
	39	notify_watch INTEGER NOT NULL DEFAULT 0,
	40	theme TEXT NOT NULL DEFAULT 'system',
	41	notify_push INTEGER NOT NULL DEFAULT 1,
	42	diff_layout TEXT NOT NULL DEFAULT 'unified',
	43	notify_reply INTEGER NOT NULL DEFAULT 0
	44	);
	45	INSERT INTO users (id, username, is_admin, created_at, pending, description, website, disabled,
	46	links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply)
	47	SELECT id, username, is_admin, created_at, pending, description, website, disabled,
	48	links, repo_limit, byte_limit, notify_mail, notify_watch, theme, notify_push, diff_layout, notify_reply
	49	FROM users_old;
	50	DROP TABLE users_old;
	51
	52	ALTER TABLE orgs RENAME TO orgs_old;
	53	CREATE TABLE orgs (
	54	id INTEGER PRIMARY KEY AUTOINCREMENT,
	55	name TEXT NOT NULL UNIQUE,
	56	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	57	description TEXT NOT NULL DEFAULT '',
	58	website TEXT NOT NULL DEFAULT '',
	59	members_role TEXT NOT NULL DEFAULT 'write'
	60	CHECK (members_role IN ('write', 'read', 'none')),
	61	links TEXT NOT NULL DEFAULT ''
	62	);
	63	INSERT INTO orgs (id, name, created_at, description, website, members_role, links)
	64	SELECT id, name, created_at, description, website, members_role, links FROM orgs_old;
	65	DROP TABLE orgs_old;
	66
	67	ALTER TABLE repos RENAME TO repos_old;
	68	CREATE TABLE repos (
	69	id INTEGER PRIMARY KEY AUTOINCREMENT,
	70	owner_kind TEXT NOT NULL CHECK (owner_kind IN ('user','org')),
	71	owner_id INTEGER NOT NULL,
	72	name TEXT NOT NULL,
	73	visibility TEXT NOT NULL CHECK (visibility IN ('public','private')),
	74	default_branch TEXT NOT NULL DEFAULT 'main',
	75	fork_of INTEGER REFERENCES repos(id) ON DELETE SET NULL,
	76	issue_counter INTEGER NOT NULL DEFAULT 0,
	77	mr_counter INTEGER NOT NULL DEFAULT 0,
	78	settings_json TEXT NOT NULL DEFAULT '{}',
	79	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	80	build_counter INTEGER NOT NULL DEFAULT 0,
	81	UNIQUE (owner_kind, owner_id, name)
	82	);
	83	INSERT INTO repos (id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
	84	issue_counter, mr_counter, settings_json, created_at, build_counter)
	85	SELECT id, owner_kind, owner_id, name, visibility, default_branch, fork_of,
	86	issue_counter, mr_counter, settings_json, created_at, build_counter
	87	FROM repos_old;
	88	DROP TABLE repos_old;
	89
	90	ALTER TABLE api_tokens RENAME TO api_tokens_old;
	91	CREATE TABLE api_tokens (
	92	id INTEGER PRIMARY KEY AUTOINCREMENT,
	93	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	94	name TEXT NOT NULL,
	95	token_hash TEXT NOT NULL UNIQUE,
	96	scope TEXT NOT NULL DEFAULT 'full' CHECK (scope IN ('full','read')),
	97	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	98	expires_at TEXT,
	99	last_used_at TEXT,
	100	created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
	101	UNIQUE (user_id, name)
	102	);
	103	INSERT INTO api_tokens (id, user_id, name, token_hash, scope, created_at, expires_at,
	104	last_used_at, created_by_token)
	105	SELECT id, user_id, name, token_hash, scope, created_at, expires_at,
	106	last_used_at, created_by_token
	107	FROM api_tokens_old;
	108	DROP TABLE api_tokens_old;
	109
	110	ALTER TABLE ssh_keys RENAME TO ssh_keys_old;
	111	CREATE TABLE ssh_keys (
	112	id INTEGER PRIMARY KEY AUTOINCREMENT,
	113	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	114	fingerprint TEXT NOT NULL UNIQUE,
	115	algo TEXT NOT NULL,
	116	blob BLOB NOT NULL,
	117	scope TEXT NOT NULL DEFAULT 'full',
	118	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
	119	last_used_at TEXT,
	120	label TEXT NOT NULL DEFAULT '',
	121	created_by_token INTEGER REFERENCES api_tokens(id) ON DELETE SET NULL,
	122	expires_at TEXT
	123	);
	124	INSERT INTO ssh_keys (id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
	125	label, created_by_token, expires_at)
	126	SELECT id, user_id, fingerprint, algo, blob, scope, created_at, last_used_at,
	127	label, created_by_token, expires_at
	128	FROM ssh_keys_old;
	129	DROP TABLE ssh_keys_old;
	130	CREATE INDEX ssh_keys_user ON ssh_keys(user_id);
	131
	132	ALTER TABLE webhook_deliveries RENAME TO webhook_deliveries_old;
	133	CREATE TABLE webhook_deliveries (
	134	id INTEGER PRIMARY KEY AUTOINCREMENT,
	135	webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
	136	event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
	137	attempts INTEGER NOT NULL DEFAULT 0,
	138	next_attempt_at TEXT,
	139	delivered_at TEXT,
	140	failed_at TEXT,
	141	last_status INTEGER,
	142	last_error TEXT,
	143	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
	144	);
	145	INSERT INTO webhook_deliveries (id, webhook_id, event_id, attempts, next_attempt_at,
	146	delivered_at, failed_at, last_status, last_error, created_at)
	147	SELECT id, webhook_id, event_id, attempts, next_attempt_at,
	148	delivered_at, failed_at, last_status, last_error, created_at
	149	FROM webhook_deliveries_old;
	150	DROP TABLE webhook_deliveries_old;
	151	CREATE INDEX webhook_deliveries_due ON webhook_deliveries(next_attempt_at)
	152	WHERE delivered_at IS NULL AND failed_at IS NULL;
	153
	154	ALTER TABLE push_queue RENAME TO push_queue_old;
	155	CREATE TABLE push_queue (
	156	id INTEGER PRIMARY KEY AUTOINCREMENT,
	157	device_id INTEGER NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
	158	title TEXT NOT NULL,
	159	body TEXT NOT NULL,
	160	path TEXT NOT NULL,
	161	attempts INTEGER NOT NULL DEFAULT 0,
	162	next_attempt_at TEXT,
	163	sent_at TEXT,
	164	failed_at TEXT,
	165	last_error TEXT,
	166	created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
	167	);
	168	INSERT INTO push_queue (id, device_id, title, body, path, attempts, next_attempt_at,
	169	sent_at, failed_at, last_error, created_at)
	170	SELECT id, device_id, title, body, path, attempts, next_attempt_at,
	171	sent_at, failed_at, last_error, created_at
	172	FROM push_queue_old;
	173	DROP TABLE push_queue_old;
	174	CREATE INDEX push_queue_due ON push_queue(next_attempt_at)
	175	WHERE sent_at IS NULL AND failed_at IS NULL;
	176
	177	CREATE TRIGGER users_owning_repos BEFORE DELETE ON users
	178	WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'user' AND owner_id = OLD.id)
	179	BEGIN
	180	SELECT RAISE(ABORT, 'user still owns repositories');
	181	END;
	182	CREATE TRIGGER orgs_owning_repos BEFORE DELETE ON orgs
	183	WHEN EXISTS (SELECT 1 FROM repos WHERE owner_kind = 'org' AND owner_id = OLD.id)
	184	BEGIN
	185	SELECT RAISE(ABORT, 'organization still owns repositories');
	186	END;
	187
	188	PRAGMA legacy_alter_table = OFF;
	189
	190	-- The copies above set each sequence to the table's own highest id; an
	191	-- empty table has no row yet.
	192	INSERT INTO sqlite_sequence (name, seq)
	193	SELECT t.name, 0 FROM (SELECT 'users' AS name UNION ALL SELECT 'orgs' UNION ALL SELECT 'repos'
	194	UNION ALL SELECT 'api_tokens' UNION ALL SELECT 'ssh_keys'
	195	UNION ALL SELECT 'webhook_deliveries' UNION ALL SELECT 'push_queue') t
	196	WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence s WHERE s.name = t.name);
	197
	198	UPDATE sqlite_sequence SET seq = MAX(seq,
	199	(SELECT COALESCE(MAX(subject_id), 0) FROM repo_access WHERE subject_kind = 'user'),
	200	(SELECT COALESCE(MAX(actor_ref), 0) FROM audit_log),
	201	(SELECT COALESCE(MAX(user_id), 0) FROM page_domains),
	202	(SELECT COALESCE(MAX(owner_id), 0) FROM profile_about_backfill WHERE owner_kind = 'user'))
	203	WHERE name = 'users';
	204	UPDATE sqlite_sequence SET seq = MAX(seq,
	205	(SELECT COALESCE(MAX(subject_id), 0) FROM repo_access WHERE subject_kind = 'org'),
	206	(SELECT COALESCE(MAX(owner_id), 0) FROM profile_about_backfill WHERE owner_kind = 'org'))
	207	WHERE name = 'orgs';
	208	UPDATE sqlite_sequence SET seq = MAX(seq,
	209	(SELECT COALESCE(MAX(CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)), 0)
	210	FROM ssh_keys WHERE scope LIKE 'deploy:%'))
	211	WHERE name = 'repos';

internal/store/migrations/0075_orphan_grants_deploy_keys.down.sql added +2

		@@ -0,0 +1,2 @@
	1	-- The removed rows are not restored.
	2	DELETE FROM settings WHERE key = 'migration_note';

internal/store/migrations/0075_orphan_grants_deploy_keys.up.sql added +36

		@@ -0,0 +1,36 @@
	1	-- Grants and parked profile about texts of deleted accounts and
	2	-- organizations, and deploy keys of deleted repositories, name their
	3	-- subject by id with no foreign key; deletes left them behind until #306. The counts go in a note the
	4	-- daemon logs with the migration and then drops.
	5	INSERT INTO settings (key, value)
	6	SELECT 'migration_note', 'removed grants of deleted accounts or organizations: ' \|\| g.n
	7	\|\| '; deploy keys of deleted repositories: ' \|\| k.n
	8	\|\| '; profile about texts of deleted accounts or organizations: ' \|\| b.n
	9	FROM (SELECT COUNT(*) AS n FROM repo_access a
	10	WHERE (a.subject_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = a.subject_id))
	11	OR (a.subject_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = a.subject_id))) g,
	12	(SELECT COUNT(*) AS n FROM ssh_keys
	13	WHERE scope LIKE 'deploy:%' AND CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)
	14	NOT IN (SELECT id FROM repos)) k,
	15	(SELECT COUNT(*) AS n FROM profile_about_backfill p
	16	WHERE (p.owner_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = p.owner_id))
	17	OR (p.owner_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = p.owner_id))) b
	18	WHERE g.n + k.n + b.n > 0
	19	ON CONFLICT (key) DO UPDATE SET value = excluded.value;
	20
	21	DELETE FROM repo_access
	22	WHERE (subject_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = repo_access.subject_id))
	23	OR (subject_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = repo_access.subject_id));
	24
	25	UPDATE settings SET value = value + 1
	26	WHERE key = 'key_epoch' AND EXISTS (SELECT 1 FROM ssh_keys
	27	WHERE scope LIKE 'deploy:%' AND CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)
	28	NOT IN (SELECT id FROM repos));
	29
	30	DELETE FROM ssh_keys
	31	WHERE scope LIKE 'deploy:%' AND CAST(substr(scope, 8, instr(substr(scope, 8), ':') - 1) AS INTEGER)
	32	NOT IN (SELECT id FROM repos);
	33
	34	DELETE FROM profile_about_backfill
	35	WHERE (owner_kind = 'user' AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = profile_about_backfill.owner_id))
	36	OR (owner_kind = 'org' AND NOT EXISTS (SELECT 1 FROM orgs o WHERE o.id = profile_about_backfill.owner_id));

internal/store/orgs.go +17 −2

		@@ -195,8 +195,23 @@ func (s *Store) DeleteOrg(orgID int64) error {
195	195	if n > 0 {
196	196	return fmt.Errorf("the organization still owns %d repositories; delete or transfer them first", n)
197	197	}
198		_, err := s.DB.Exec("DELETE FROM orgs WHERE id = ?", orgID)
199		return err
	198	// Grants and a parked about text name the org by id with no foreign
	199	// key (#306).
	200	tx, err := s.DB.Begin()
	201	if err != nil {
	202	return err
	203	}
	204	defer tx.Rollback()
	205	if _, err := tx.Exec("DELETE FROM repo_access WHERE subject_kind = 'org' AND subject_id = ?", orgID); err != nil {
	206	return err
	207	}
	208	if _, err := tx.Exec("DELETE FROM profile_about_backfill WHERE owner_kind = 'org' AND owner_id = ?", orgID); err != nil {
	209	return err
	210	}
	211	if _, err := tx.Exec("DELETE FROM orgs WHERE id = ?", orgID); err != nil {
	212	return err
	213	}
	214	return tx.Commit()
200	215	}
201	216
202	217	// RenameOrg changes an org's name, holding the shared owner-namespace

internal/store/repos.go +37 −1

		@@ -180,14 +180,50 @@ func (s *Store) SetForkOf(repoID, parentID int64) error {
180	180	return err
181	181	}
182	182
	183	// DeleteRepo removes the repository row, what cascades from it, and
	184	// the deploy keys scoped to it, which name it by id in their scope
	185	// rather than by a foreign key (#306).
183	186	func (s *Store) DeleteRepo(repoID int64) error {
184		res, err := s.DB.Exec("DELETE FROM repos WHERE id = ?", repoID)
	187	tx, err := s.DB.Begin()
	188	if err != nil {
	189	return err
	190	}
	191	defer tx.Rollback()
	192	rows, err := tx.Query("DELETE FROM ssh_keys WHERE scope LIKE 'deploy:' \|\| ? \|\| ':%' RETURNING id", repoID)
	193	if err != nil {
	194	return err
	195	}
	196	var keyIDs []int64
	197	for rows.Next() {
	198	var id int64
	199	if err := rows.Scan(&id); err != nil {
	200	rows.Close()
	201	return err
	202	}
	203	keyIDs = append(keyIDs, id)
	204	}
	205	rows.Close()
	206	if err := rows.Err(); err != nil {
	207	return err
	208	}
	209	res, err := tx.Exec("DELETE FROM repos WHERE id = ?", repoID)
185	210	if err != nil {
186	211	return err
187	212	}
188	213	if n, _ := res.RowsAffected(); n == 0 {
189	214	return ErrNotFound
190	215	}
	216	if len(keyIDs) > 0 {
	217	if err := bumpKeyEpoch(tx); err != nil {
	218	return err
	219	}
	220	}
	221	if err := tx.Commit(); err != nil {
	222	return err
	223	}
	224	if len(keyIDs) > 0 {
	225	s.announce(Revoked{KeyIDs: keyIDs})
	226	}
191	227	return nil
192	228	}
193	229

internal/store/store.go +11

		@@ -174,6 +174,17 @@ func (s *Store) Version() (int, error) {
174	174	return v, err
175	175	}
176	176
	177	// TakeMigrationNote returns and clears what a migration left to be
	178	// logged with it, "" for nothing.
	179	func (s *Store) TakeMigrationNote() (string, error) {
	180	var note string
	181	err := s.DB.QueryRow("DELETE FROM settings WHERE key = 'migration_note' RETURNING value").Scan(&note)
	182	if errors.Is(err, sql.ErrNoRows) {
	183	return "", nil
	184	}
	185	return note, err
	186	}
	187
177	188	// MigrateUp applies all pending migrations.
178	189	func (s *Store) MigrateUp() error { return s.migrateTo(-1) }
179	190

internal/store/users.go +17 −1

		@@ -99,13 +99,29 @@ func (s *Store) DeleteUser(id int64) error {
99	99	return fmt.Errorf("account still anchors: %s — transfer or delete those first, or disable the account instead",
100	100	strings.Join(blockers, ", "))
101	101	}
102		res, err := s.DB.Exec("DELETE FROM users WHERE id = ?", id)
	102	// Grants and a parked about text name the account by id with no
	103	// foreign key, so they go in the same transaction (#306).
	104	tx, err := s.DB.Begin()
	105	if err != nil {
	106	return err
	107	}
	108	defer tx.Rollback()
	109	if _, err := tx.Exec("DELETE FROM repo_access WHERE subject_kind = 'user' AND subject_id = ?", id); err != nil {
	110	return err
	111	}
	112	if _, err := tx.Exec("DELETE FROM profile_about_backfill WHERE owner_kind = 'user' AND owner_id = ?", id); err != nil {
	113	return err
	114	}
	115	res, err := tx.Exec("DELETE FROM users WHERE id = ?", id)
103	116	if err != nil {
104	117	return err
105	118	}
106	119	if n, _ := res.RowsAffected(); n == 0 {
107	120	return ErrNotFound
108	121	}
	122	if err := tx.Commit(); err != nil {
	123	return err
	124	}
109	125	s.announce(Revoked{UserID: id})
110	126	return nil
111	127	}