orchard

  1package main
  2
  3// The database holds only what git cannot: push tokens, description and topics,
  4// and mirror state. Refs, commits, trees, sizes and dates are never copied here,
  5// since a cache of something git can answer is a second source of truth.
  6
  7import (
  8	"crypto/rand"
  9	"crypto/subtle"
 10	"database/sql"
 11	"encoding/base64"
 12	"encoding/json"
 13	"errors"
 14	"fmt"
 15	"os"
 16	"path/filepath"
 17	"regexp"
 18	"strings"
 19	"time"
 20
 21	"golang.org/x/crypto/argon2"
 22
 23	_ "modernc.org/sqlite"
 24)
 25
 26const schema = `
 27-- One row per repository this site knows about, whether it arrived by push or
 28-- by mirror. The row is metadata only: delete it and the repository still
 29-- serves, it just loses its description.
 30CREATE TABLE IF NOT EXISTS repos (
 31    name          TEXT PRIMARY KEY,
 32    description   TEXT    NOT NULL DEFAULT '',
 33    topics        TEXT    NOT NULL DEFAULT '[]',
 34    homepage      TEXT    NOT NULL DEFAULT '',
 35    -- A mirror is pulled from upstream and refuses pushes. A pushed repo is
 36    -- the opposite. The column is what the wire checks before receive-pack.
 37    mirror        INTEGER NOT NULL DEFAULT 0,
 38    upstream      TEXT    NOT NULL DEFAULT '',
 39    -- Set when upstream reports the repository archived, and shown as a
 40    -- badge. Twenty of the twenty-one repositories on the account are
 41    -- archived, so this is the common case rather than an exception.
 42    archived      INTEGER NOT NULL DEFAULT 0,
 43    -- The point of the mirror half: a repository that vanished upstream but
 44    -- still lives here. Loud on the index, because it is the only copy.
 45    upstream_gone INTEGER NOT NULL DEFAULT 0,
 46    last_sync     INTEGER NOT NULL DEFAULT 0,
 47    last_sync_err TEXT    NOT NULL DEFAULT '',
 48    created       INTEGER NOT NULL DEFAULT 0,
 49    hidden        INTEGER NOT NULL DEFAULT 0
 50);
 51
 52-- Push credentials. The token itself is never stored, only an Argon2id hash of
 53-- it, so a copy of this database does not let anybody push.
 54--
 55-- salt is per token rather than global. A single site-wide salt would mean two
 56-- tokens with the same value hash identically, which leaks nothing useful here
 57-- with one operator but costs nothing to do properly.
 58CREATE TABLE IF NOT EXISTS tokens (
 59    id        INTEGER PRIMARY KEY AUTOINCREMENT,
 60    label     TEXT    NOT NULL,
 61    -- The first eight characters of the token, stored in clear. Not a
 62    -- secret and not enough to authenticate with; it is what lets the UI
 63    -- show "which of these three is the one on my laptop" without keeping
 64    -- the token itself.
 65    prefix    TEXT    NOT NULL,
 66    hash      BLOB    NOT NULL,
 67    salt      BLOB    NOT NULL,
 68    created   INTEGER NOT NULL,
 69    last_used INTEGER NOT NULL DEFAULT 0,
 70    revoked   INTEGER NOT NULL DEFAULT 0
 71);
 72
 73CREATE INDEX IF NOT EXISTS tokens_prefix ON tokens(prefix) WHERE revoked = 0;
 74
 75-- What the mirror lane pulls from. Rows rather than a constant, because which
 76-- account or repository to back up is an operational decision that changes, and
 77-- changing it should not need a rebuild.
 78--
 79-- kind is 'account' (everything owned by that login) or 'repo' (one named
 80-- repository). name is empty for an account. The unique constraint is what
 81-- makes adding the same source twice a no-op rather than a duplicate fetch.
 82CREATE TABLE IF NOT EXISTS mirror_sources (
 83    id      INTEGER PRIMARY KEY AUTOINCREMENT,
 84    kind    TEXT    NOT NULL,
 85    owner   TEXT    NOT NULL,
 86    name    TEXT    NOT NULL DEFAULT '',
 87    created INTEGER NOT NULL,
 88    UNIQUE(kind, owner, name)
 89);
 90
 91-- Small key/value store for facts about this installation rather than about a
 92-- repository. It exists for the seed marker below and is the right home for the
 93-- next flag of that shape.
 94CREATE TABLE IF NOT EXISTS settings (
 95    key   TEXT PRIMARY KEY,
 96    value TEXT NOT NULL
 97);
 98`
 99
100// DB wraps the connection. One writer, so no pool tuning beyond the pragmas.
101type DB struct {
102	sql *sql.DB
103}
104
105// OpenDB opens or creates the database beside the repositories.
106func OpenDB(dir string) (*DB, error) {
107	if err := os.MkdirAll(dir, 0o755); err != nil {
108		return nil, fmt.Errorf("create data dir: %w", err)
109	}
110	path := filepath.Join(dir, "repos.db")
111
112	// WAL so a mirror sync writing state does not block a page read, and a
113	// busy_timeout because a push-to-create and a sync tick can land together.
114	dsn := path + "?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)"
115
116	conn, err := sql.Open("sqlite", dsn)
117	if err != nil {
118		return nil, fmt.Errorf("open db: %w", err)
119	}
120	if err := conn.Ping(); err != nil {
121		return nil, fmt.Errorf("open db: %w", err)
122	}
123	if _, err := conn.Exec(schema); err != nil {
124		return nil, fmt.Errorf("apply schema: %w", err)
125	}
126	return &DB{sql: conn}, nil
127}
128
129func (d *DB) Close() error { return d.sql.Close() }
130
131// RepoMeta is the row, with topics already decoded.
132type RepoMeta struct {
133	Name         string
134	Description  string
135	Topics       []string
136	Homepage     string
137	Mirror       bool
138	Upstream     string
139	Archived     bool
140	UpstreamGone bool
141	LastSync     time.Time
142	LastSyncErr  string
143	Created      time.Time
144	Hidden       bool
145}
146
147// Repo reads one row. No row is not an error; it is a repository pushed and
148// never described.
149func (d *DB) Repo(name string) (RepoMeta, error) {
150	row := d.sql.QueryRow(`
151        SELECT name, description, topics, homepage, mirror, upstream,
152               archived, upstream_gone, last_sync, last_sync_err, created, hidden
153        FROM repos WHERE name = ?`, name)
154	return scanRepo(row)
155}
156
157type scanner interface{ Scan(...any) error }
158
159func scanRepo(row scanner) (RepoMeta, error) {
160	var (
161		m                              RepoMeta
162		topics                         string
163		mirror, archived, gone, hidden int
164		lastSync, created              int64
165	)
166	err := row.Scan(&m.Name, &m.Description, &topics, &m.Homepage, &mirror,
167		&m.Upstream, &archived, &gone, &lastSync, &m.LastSyncErr, &created, &hidden)
168	if err != nil {
169		return RepoMeta{}, err
170	}
171	_ = json.Unmarshal([]byte(topics), &m.Topics)
172	m.Mirror = mirror == 1
173	m.Archived = archived == 1
174	m.UpstreamGone = gone == 1
175	m.Hidden = hidden == 1
176	if lastSync > 0 {
177		m.LastSync = time.Unix(lastSync, 0).UTC()
178	}
179	if created > 0 {
180		m.Created = time.Unix(created, 0).UTC()
181	}
182	return m, nil
183}
184
185// AllRepos reads every row keyed by name, so the index joins against the on-disk
186// list in one pass.
187func (d *DB) AllRepos() (map[string]RepoMeta, error) {
188	rows, err := d.sql.Query(`
189        SELECT name, description, topics, homepage, mirror, upstream,
190               archived, upstream_gone, last_sync, last_sync_err, created, hidden
191        FROM repos`)
192	if err != nil {
193		return nil, err
194	}
195	defer rows.Close()
196
197	out := make(map[string]RepoMeta)
198	for rows.Next() {
199		m, err := scanRepo(rows)
200		if err != nil {
201			return nil, err
202		}
203		out[m.Name] = m
204	}
205	return out, rows.Err()
206}
207
208// EnsureRepo creates the metadata row if it is missing.
209func (d *DB) EnsureRepo(name string) error {
210	_, err := d.sql.Exec(`
211        INSERT INTO repos (name, created) VALUES (?, ?)
212        ON CONFLICT(name) DO NOTHING`, name, time.Now().Unix())
213	return err
214}
215
216// SetDescription updates what the UI can edit. Topics are a JSON array, not a
217// join table; nothing queries across them that a LIKE cannot answer.
218func (d *DB) SetDescription(name, description string, topics []string, homepage string) error {
219	if err := d.EnsureRepo(name); err != nil {
220		return err
221	}
222	blob, err := json.Marshal(topics)
223	if err != nil {
224		return err
225	}
226	_, err = d.sql.Exec(`
227        UPDATE repos SET description = ?, topics = ?, homepage = ? WHERE name = ?`,
228		description, string(blob), homepage, name)
229	return err
230}
231
232// SetHidden keeps a repository on disk but off the index. There is no delete here.
233func (d *DB) SetHidden(name string, hidden bool) error {
234	if err := d.EnsureRepo(name); err != nil {
235		return err
236	}
237	v := 0
238	if hidden {
239		v = 1
240	}
241	_, err := d.sql.Exec(`UPDATE repos SET hidden = ? WHERE name = ?`, v, name)
242	return err
243}
244
245// MarkMirror records that a repository is a mirror of an upstream URL.
246func (d *DB) MarkMirror(name, upstream string, archived bool) error {
247	if err := d.EnsureRepo(name); err != nil {
248		return err
249	}
250	a := 0
251	if archived {
252		a = 1
253	}
254	_, err := d.sql.Exec(`
255        UPDATE repos SET mirror = 1, upstream = ?, archived = ? WHERE name = ?`,
256		upstream, a, name)
257	return err
258}
259
260// RecordSync writes the outcome of one mirror fetch.
261func (d *DB) RecordSync(name string, err error) error {
262	msg := ""
263	if err != nil {
264		msg = err.Error()
265	}
266	_, dberr := d.sql.Exec(`
267        UPDATE repos SET last_sync = ?, last_sync_err = ? WHERE name = ?`,
268		time.Now().Unix(), msg, name)
269	return dberr
270}
271
272// MarkUpstreamGone marks a repository whose upstream is gone, so the index can
273// say this is the only copy left.
274func (d *DB) MarkUpstreamGone(name string, gone bool) error {
275	v := 0
276	if gone {
277		v = 1
278	}
279	_, err := d.sql.Exec(`UPDATE repos SET upstream_gone = ? WHERE name = ?`, v, name)
280	return err
281}
282
283// Token is one credential, without the secret.
284type Token struct {
285	ID       int64
286	Label    string
287	Prefix   string
288	Created  time.Time
289	LastUsed time.Time
290}
291
292// Argon2id parameters, modest because what is hashed is a 256 bit random value
293// rather than a human password. The hash is here so a leaked database is not a
294// credential, not to withstand a dictionary attack.
295const (
296	argonTime    = 1
297	argonMemory  = 64 * 1024
298	argonThreads = 4
299	argonKeyLen  = 32
300	saltLen      = 16
301	// 32 bytes is 43 base64url characters, so the 8 stored in clear give nothing away.
302	tokenBytes = 32
303)
304
305// CreateToken mints a push credential and returns it once; the database keeps
306// only a hash, so there is no way to read it back.
307func (d *DB) CreateToken(label string) (string, error) {
308	label = strings.TrimSpace(label)
309	if label == "" {
310		label = "unnamed"
311	}
312
313	raw := make([]byte, tokenBytes)
314	if _, err := rand.Read(raw); err != nil {
315		return "", fmt.Errorf("generate token: %w", err)
316	}
317	token := base64.RawURLEncoding.EncodeToString(raw)
318
319	salt := make([]byte, saltLen)
320	if _, err := rand.Read(salt); err != nil {
321		return "", fmt.Errorf("generate salt: %w", err)
322	}
323	hash := argon2.IDKey([]byte(token), salt, argonTime, argonMemory, argonThreads, argonKeyLen)
324
325	_, err := d.sql.Exec(`
326        INSERT INTO tokens (label, prefix, hash, salt, created)
327        VALUES (?, ?, ?, ?, ?)`,
328		label, token[:8], hash, salt, time.Now().Unix())
329	if err != nil {
330		return "", err
331	}
332	return token, nil
333}
334
335var errNoToken = errors.New("no such token")
336
337// VerifyToken checks a credential and returns its label. Every prefix candidate
338// is compared in constant time, so a colliding prefix gives nothing away by timing.
339func (d *DB) VerifyToken(token string) (string, error) {
340	if len(token) < 8 {
341		return "", errNoToken
342	}
343
344	rows, err := d.sql.Query(`
345        SELECT id, label, hash, salt FROM tokens
346        WHERE prefix = ? AND revoked = 0`, token[:8])
347	if err != nil {
348		return "", err
349	}
350	defer rows.Close()
351
352	for rows.Next() {
353		var (
354			id         int64
355			label      string
356			hash, salt []byte
357		)
358		if err := rows.Scan(&id, &label, &hash, &salt); err != nil {
359			return "", err
360		}
361		candidate := argon2.IDKey([]byte(token), salt,
362			argonTime, argonMemory, argonThreads, argonKeyLen)
363		if subtle.ConstantTimeCompare(candidate, hash) == 1 {
364			// Best effort: a push must not fail because this write did.
365			_, _ = d.sql.Exec(`UPDATE tokens SET last_used = ? WHERE id = ?`,
366				time.Now().Unix(), id)
367			return label, nil
368		}
369	}
370	return "", errNoToken
371}
372
373// Tokens lists the live credentials for the settings page.
374func (d *DB) Tokens() ([]Token, error) {
375	rows, err := d.sql.Query(`
376        SELECT id, label, prefix, created, last_used FROM tokens
377        WHERE revoked = 0 ORDER BY created DESC`)
378	if err != nil {
379		return nil, err
380	}
381	defer rows.Close()
382
383	var out []Token
384	for rows.Next() {
385		var (
386			t                 Token
387			created, lastUsed int64
388		)
389		if err := rows.Scan(&t.ID, &t.Label, &t.Prefix, &created, &lastUsed); err != nil {
390			return nil, err
391		}
392		t.Created = time.Unix(created, 0).UTC()
393		if lastUsed > 0 {
394			t.LastUsed = time.Unix(lastUsed, 0).UTC()
395		}
396		out = append(out, t)
397	}
398	return out, rows.Err()
399}
400
401// RevokeToken is one UPDATE, effective on the next request.
402func (d *DB) RevokeToken(id int64) error {
403	_, err := d.sql.Exec(`UPDATE tokens SET revoked = 1 WHERE id = ?`, id)
404	return err
405}
406
407// HasTokens reports whether any credential exists, so a fresh install can be told
408// to mint one.
409func (d *DB) HasTokens() bool {
410	var n int
411	if err := d.sql.QueryRow(`SELECT COUNT(*) FROM tokens WHERE revoked = 0`).Scan(&n); err != nil {
412		return false
413	}
414	return n > 0
415}
416
417// MirrorSource is one thing the mirror lane pulls from: a whole GitHub account,
418// or a single repository on one.
419type MirrorSource struct {
420	ID      int64
421	Kind    string // "account" or "repo"
422	Owner   string
423	Name    string // empty for an account
424	Created time.Time
425}
426
427// Label is "owner" for an account and "owner/name" for a single repository.
428func (m MirrorSource) Label() string {
429	if m.Kind == sourceRepo {
430		return m.Owner + "/" + m.Name
431	}
432	return m.Owner
433}
434
435func (m MirrorSource) URL() string { return "https://github.com/" + m.Label() }
436
437const (
438	sourceAccount = "account"
439	sourceRepo    = "repo"
440)
441
442// ghNamePart is GitHub's charset for a login or repository name. These values are
443// interpolated into an API URL, so they are checked at the form.
444var ghNamePart = regexp.MustCompile(`^[A-Za-z0-9][A-Za-z0-9._-]*$`)
445
446// ParseMirrorSource accepts "owner" or "owner/repo"; the slash is the whole grammar.
447func ParseMirrorSource(input string) (MirrorSource, error) {
448	in := strings.TrimSpace(input)
449	// A pasted GitHub URL is accepted rather than rejected.
450	in = strings.TrimSuffix(strings.TrimPrefix(strings.TrimPrefix(in,
451		"https://github.com/"), "github.com/"), ".git")
452	// Only a trailing slash is forgiven; a leading one means the owner is missing.
453	in = strings.TrimSuffix(in, "/")
454
455	if in == "" {
456		return MirrorSource{}, errors.New("enter an account or an owner/repository")
457	}
458	owner, name, hasSlash := strings.Cut(in, "/")
459	if !ghNamePart.MatchString(owner) || len(owner) > 100 {
460		return MirrorSource{}, fmt.Errorf("%q is not a GitHub account name", owner)
461	}
462	if !hasSlash {
463		return MirrorSource{Kind: sourceAccount, Owner: owner}, nil
464	}
465	if !ghNamePart.MatchString(name) || len(name) > 100 || strings.Contains(name, "/") {
466		return MirrorSource{}, fmt.Errorf("%q is not a repository name", name)
467	}
468	return MirrorSource{Kind: sourceRepo, Owner: owner, Name: name}, nil
469}
470
471// MirrorSources lists every configured source, accounts first.
472func (d *DB) MirrorSources() ([]MirrorSource, error) {
473	rows, err := d.sql.Query(`
474        SELECT id, kind, owner, name, created FROM mirror_sources
475        ORDER BY kind DESC, owner, name`)
476	if err != nil {
477		return nil, err
478	}
479	defer rows.Close()
480
481	var out []MirrorSource
482	for rows.Next() {
483		var m MirrorSource
484		var created int64
485		if err := rows.Scan(&m.ID, &m.Kind, &m.Owner, &m.Name, &created); err != nil {
486			return nil, err
487		}
488		m.Created = time.Unix(created, 0).UTC()
489		out = append(out, m)
490	}
491	return out, rows.Err()
492}
493
494// AddMirrorSource stores a source. Adding one twice succeeds and changes nothing.
495func (d *DB) AddMirrorSource(m MirrorSource) error {
496	_, err := d.sql.Exec(`
497        INSERT INTO mirror_sources (kind, owner, name, created) VALUES (?, ?, ?, ?)
498        ON CONFLICT(kind, owner, name) DO NOTHING`,
499		m.Kind, m.Owner, m.Name, time.Now().Unix())
500	return err
501}
502
503// DeleteMirrorSource stops syncing a source. The repositories it brought in stay
504// on disk; this site is a backup.
505func (d *DB) DeleteMirrorSource(id int64) error {
506	_, err := d.sql.Exec(`DELETE FROM mirror_sources WHERE id = ?`, id)
507	return err
508}
509
510// SeedMirrorSources adds owner as a source on first run. Guarded by a marker
511// rather than by an empty table, so deleting the last source keeps it deleted.
512func (d *DB) SeedMirrorSources(owner string) error {
513	var seeded string
514	err := d.sql.QueryRow(`SELECT value FROM settings WHERE key = 'mirror_sources_seeded'`).Scan(&seeded)
515	if err == nil {
516		return nil
517	}
518	if !errors.Is(err, sql.ErrNoRows) {
519		return err
520	}
521	if err := d.AddMirrorSource(MirrorSource{Kind: sourceAccount, Owner: owner}); err != nil {
522		return err
523	}
524	_, err = d.sql.Exec(
525		`INSERT INTO settings (key, value) VALUES ('mirror_sources_seeded', ?)`,
526		time.Now().UTC().Format(time.RFC3339))
527	return err
528}