// Package store bewaart BlueGit in één SQLite-bestand op de datadisk. package store import ( "context" "crypto/rand" "crypto/sha256" "database/sql" "encoding/hex" "encoding/json" "errors" "fmt" "strings" "time" "gitlab.com/Timfa/bluegit/internal/i18n" "gitlab.com/Timfa/bluegit/internal/names" "gitlab.com/Timfa/bluegit/internal/secretbox" _ "modernc.org/sqlite" ) // Store is de metadata van deze installatie. type Store struct { db *sql.DB key []byte } // Open opent of maakt het databasebestand. key is 32 bytes voor geheimen. func Open(path string, key []byte) (*Store, error) { if len(key) != 32 { return nil, fmt.Errorf("geheime sleutel moet 32 bytes zijn") } dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)" db, err := sql.Open("sqlite", dsn) if err != nil { return nil, err } db.SetMaxOpenConns(8) s := &Store{db: db, key: key} if _, err := db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`); err != nil { _ = db.Close() return nil, err } if err := s.migrate(); err != nil { _ = db.Close() return nil, err } return s, nil } // Close sluit de database. func (s *Store) Close() error { return s.db.Close() } func (s *Store) migrate() error { if _, err := s.db.Exec(schema); err != nil { return err } alters := []string{ `ALTER TABLE users ADD COLUMN language TEXT NOT NULL DEFAULT 'en'`, `ALTER TABLE groups ADD COLUMN visibility TEXT NOT NULL DEFAULT 'private'`, `ALTER TABLE groups ADD COLUMN default_role TEXT NOT NULL DEFAULT 'developer'`, `ALTER TABLE groups ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`, `ALTER TABLE repos ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`, `ALTER TABLE webhooks ADD COLUMN method TEXT NOT NULL DEFAULT 'POST'`, `ALTER TABLE notes ADD COLUMN file_path TEXT NOT NULL DEFAULT ''`, `ALTER TABLE notes ADD COLUMN line_start INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE notes ADD COLUMN line_end INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE notes ADD COLUMN suggestion TEXT NOT NULL DEFAULT ''`, `ALTER TABLE notes ADD COLUMN base_text TEXT NOT NULL DEFAULT ''`, `ALTER TABLE pipelines ADD COLUMN source TEXT NOT NULL DEFAULT 'push'`, `ALTER TABLE pipelines ADD COLUMN actor TEXT NOT NULL DEFAULT ''`, `ALTER TABLE pipelines ADD COLUMN commit_title TEXT NOT NULL DEFAULT ''`, `ALTER TABLE pipelines ADD COLUMN started_at TEXT`, `ALTER TABLE jobs ADD COLUMN before_script TEXT NOT NULL DEFAULT ''`, `ALTER TABLE jobs ADD COLUMN after_script TEXT NOT NULL DEFAULT ''`, `ALTER TABLE jobs ADD COLUMN when_mode TEXT NOT NULL DEFAULT 'on_success'`, `ALTER TABLE jobs ADD COLUMN timeout_seconds INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE jobs ADD COLUMN tags_json TEXT NOT NULL DEFAULT '[]'`, `ALTER TABLE jobs ADD COLUMN cache_paths TEXT NOT NULL DEFAULT '[]'`, `ALTER TABLE jobs ADD COLUMN cache_key TEXT NOT NULL DEFAULT ''`, `ALTER TABLE jobs ADD COLUMN resource_group TEXT NOT NULL DEFAULT ''`, `ALTER TABLE jobs ADD COLUMN optional_json TEXT NOT NULL DEFAULT '[]'`, `ALTER TABLE jobs ADD COLUMN retry_max INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE jobs ADD COLUMN retry_when TEXT NOT NULL DEFAULT '[]'`, `ALTER TABLE jobs ADD COLUMN attempt INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE jobs ADD COLUMN services_json TEXT NOT NULL DEFAULT '[]'`, `ALTER TABLE jobs ADD COLUMN environment_name TEXT NOT NULL DEFAULT ''`, `ALTER TABLE jobs ADD COLUMN environment_url TEXT NOT NULL DEFAULT ''`, `ALTER TABLE jobs ADD COLUMN interruptible INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE pipelines ADD COLUMN mr_id INTEGER NOT NULL DEFAULT 0`, `ALTER TABLE groups ADD COLUMN owner_id INTEGER NOT NULL DEFAULT 0`, } for _, q := range alters { if _, err := s.db.Exec(q); err != nil && !strings.Contains(strings.ToLower(err.Error()), "duplicate column") { return err } } if _, err := s.db.Exec(`UPDATE groups SET owner_id = created_by WHERE owner_id = 0 AND created_by != 0`); err != nil { return err } return nil } const schema = ` CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, auth_id INTEGER NOT NULL UNIQUE, username TEXT NOT NULL UNIQUE, avatar_url TEXT NOT NULL DEFAULT '', auth_admin INTEGER NOT NULL DEFAULT 0, instance_admin INTEGER NOT NULL DEFAULT 0, language TEXT NOT NULL DEFAULT 'en', created_at TEXT NOT NULL, updated_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS sessions ( id TEXT PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, access_token TEXT NOT NULL, expires_at TEXT NOT NULL, csrf TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS access_tokens ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, name TEXT NOT NULL, token_hash TEXT NOT NULL UNIQUE, prefix TEXT NOT NULL, created_at TEXT NOT NULL, last_used_at TEXT, expires_at TEXT ); CREATE TABLE IF NOT EXISTS ssh_keys ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, name TEXT NOT NULL, public_key TEXT NOT NULL, fingerprint TEXT NOT NULL UNIQUE, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS groups ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, path TEXT NOT NULL UNIQUE, description TEXT NOT NULL DEFAULT '', visibility TEXT NOT NULL DEFAULT 'private', default_role TEXT NOT NULL DEFAULT 'developer', logo_mime TEXT NOT NULL DEFAULT '', created_by INTEGER NOT NULL, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS group_members ( group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, role TEXT NOT NULL, PRIMARY KEY (group_id, user_id) ); CREATE TABLE IF NOT EXISTS repos ( id INTEGER PRIMARY KEY, owner_kind TEXT NOT NULL, owner_id INTEGER NOT NULL, name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', default_branch TEXT NOT NULL, visibility TEXT NOT NULL DEFAULT 'private', logo_mime TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, UNIQUE(owner_kind, owner_id, name) ); CREATE TABLE IF NOT EXISTS repo_members ( repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, role TEXT NOT NULL, PRIMARY KEY (repo_id, user_id) ); CREATE TABLE IF NOT EXISTS pending_members ( id INTEGER PRIMARY KEY, scope_kind TEXT NOT NULL, scope_id INTEGER NOT NULL, username TEXT NOT NULL, role TEXT NOT NULL, UNIQUE(scope_kind, scope_id, username) ); CREATE TABLE IF NOT EXISTS protected_branches ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, pattern TEXT NOT NULL, UNIQUE(repo_id, pattern) ); CREATE TABLE IF NOT EXISTS protected_tags ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, pattern TEXT NOT NULL, UNIQUE(repo_id, pattern) ); CREATE TABLE IF NOT EXISTS secrets ( id INTEGER PRIMARY KEY, scope_kind TEXT NOT NULL, scope_id INTEGER NOT NULL, name TEXT NOT NULL, ciphertext BLOB NOT NULL, nonce BLOB NOT NULL, protected INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, UNIQUE(scope_kind, scope_id, name) ); CREATE TABLE IF NOT EXISTS webhooks ( id INTEGER PRIMARY KEY, scope_kind TEXT NOT NULL, scope_id INTEGER NOT NULL, name TEXT NOT NULL, preset TEXT NOT NULL, url_template TEXT NOT NULL, headers_json TEXT NOT NULL, body_template TEXT NOT NULL, events_json TEXT NOT NULL, branch_filter TEXT NOT NULL DEFAULT '', active INTEGER NOT NULL DEFAULT 1, method TEXT NOT NULL DEFAULT 'POST', created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS webhook_deliveries ( id INTEGER PRIMARY KEY, webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE, event_type TEXT NOT NULL, status_code INTEGER NOT NULL DEFAULT 0, error TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS merge_requests ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, number INTEGER NOT NULL, title TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', source_branch TEXT NOT NULL, target_branch TEXT NOT NULL, author_id INTEGER NOT NULL, status TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE(repo_id, number) ); CREATE TABLE IF NOT EXISTS pipelines ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, commit_sha TEXT NOT NULL, ref TEXT NOT NULL, status TEXT NOT NULL, created_at TEXT NOT NULL, finished_at TEXT, source TEXT NOT NULL DEFAULT 'push', actor TEXT NOT NULL DEFAULT '', commit_title TEXT NOT NULL DEFAULT '', started_at TEXT ); CREATE TABLE IF NOT EXISTS jobs ( id INTEGER PRIMARY KEY, pipeline_id INTEGER NOT NULL REFERENCES pipelines(id) ON DELETE CASCADE, repo_id INTEGER NOT NULL, name TEXT NOT NULL, stage TEXT NOT NULL, position INTEGER NOT NULL, status TEXT NOT NULL, image TEXT NOT NULL, script TEXT NOT NULL, needs_json TEXT NOT NULL, needs_set INTEGER NOT NULL, variables_json TEXT NOT NULL, artifact_paths TEXT NOT NULL, artifact_expire_seconds INTEGER NOT NULL DEFAULT 0, allow_failure INTEGER NOT NULL DEFAULT 0, log TEXT NOT NULL DEFAULT '', started_at TEXT, finished_at TEXT, before_script TEXT NOT NULL DEFAULT '', after_script TEXT NOT NULL DEFAULT '', when_mode TEXT NOT NULL DEFAULT 'on_success', timeout_seconds INTEGER NOT NULL DEFAULT 0, tags_json TEXT NOT NULL DEFAULT '[]', cache_paths TEXT NOT NULL DEFAULT '[]', cache_key TEXT NOT NULL DEFAULT '', resource_group TEXT NOT NULL DEFAULT '', optional_json TEXT NOT NULL DEFAULT '[]', retry_max INTEGER NOT NULL DEFAULT 0, retry_when TEXT NOT NULL DEFAULT '[]', attempt INTEGER NOT NULL DEFAULT 0, services_json TEXT NOT NULL DEFAULT '[]', environment_name TEXT NOT NULL DEFAULT '', environment_url TEXT NOT NULL DEFAULT '', interruptible INTEGER NOT NULL DEFAULT 0, UNIQUE(pipeline_id, name) ); CREATE TABLE IF NOT EXISTS artifacts ( id INTEGER PRIMARY KEY, job_id INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE, path TEXT NOT NULL, storage_path TEXT NOT NULL, size INTEGER NOT NULL, expires_at TEXT ); CREATE TABLE IF NOT EXISTS job_tokens ( job_id INTEGER PRIMARY KEY REFERENCES jobs(id) ON DELETE CASCADE, token_hash TEXT NOT NULL UNIQUE, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS releases ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, tag TEXT NOT NULL, commit_sha TEXT NOT NULL, name TEXT NOT NULL, body TEXT NOT NULL DEFAULT '', author_id INTEGER, job_id INTEGER, created_at TEXT NOT NULL, UNIQUE(repo_id, tag) ); CREATE TABLE IF NOT EXISTS release_assets ( id INTEGER PRIMARY KEY, release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE, name TEXT NOT NULL, storage_path TEXT NOT NULL, size INTEGER NOT NULL, UNIQUE(release_id, name) ); CREATE TABLE IF NOT EXISTS contributions ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, kind TEXT NOT NULL, ref TEXT NOT NULL, occurred_at TEXT NOT NULL, UNIQUE(repo_id, kind, ref) ); CREATE INDEX IF NOT EXISTS contributions_repo_time ON contributions(repo_id, occurred_at); CREATE INDEX IF NOT EXISTS contributions_user_time ON contributions(user_id, occurred_at); CREATE TABLE IF NOT EXISTS contribution_sync ( repo_id INTEGER PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE, synced_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS stars ( user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, created_at TEXT NOT NULL, PRIMARY KEY (user_id, repo_id) ); CREATE TABLE IF NOT EXISTS notes ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE, author_id INTEGER NOT NULL REFERENCES users(id), body TEXT NOT NULL, created_at TEXT NOT NULL, file_path TEXT NOT NULL DEFAULT '', line_start INTEGER NOT NULL DEFAULT 0, line_end INTEGER NOT NULL DEFAULT 0, suggestion TEXT NOT NULL DEFAULT '', base_text TEXT NOT NULL DEFAULT '' ); CREATE TABLE IF NOT EXISTS commit_pushers ( repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE, sha TEXT NOT NULL, user_id INTEGER NOT NULL REFERENCES users(id), author_name TEXT NOT NULL, author_email TEXT NOT NULL, created_at TEXT NOT NULL, PRIMARY KEY (repo_id, sha) ); CREATE TABLE IF NOT EXISTS approval_rules ( scope_kind TEXT NOT NULL, scope_id INTEGER NOT NULL, configured INTEGER NOT NULL DEFAULT 0, maintainer_required INTEGER NOT NULL DEFAULT 0, required_count INTEGER NOT NULL DEFAULT 1, developers_approve INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (scope_kind, scope_id) ); CREATE TABLE IF NOT EXISTS reviews ( id INTEGER PRIMARY KEY, repo_id INTEGER NOT NULL, mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE, user_id INTEGER NOT NULL, status TEXT NOT NULL, updated_at TEXT NOT NULL, UNIQUE(mr_id, user_id) ); ` func now() string { return time.Now().UTC().Format(time.RFC3339) } func parseTime(s string) time.Time { t, err := time.Parse(time.RFC3339, s) if err != nil { return time.Time{} } return t } // User is een ingelogd account van de auth-server. type User struct { ID int64 AuthID int64 Username string AvatarURL string AuthAdmin bool InstanceAdmin bool Language string CreatedAt time.Time } // UpsertUser maakt of ververst een account. De eerste gebruiker wordt // installatiebeheerder. Wachtende lidmaatschappen op deze naam worden gekoppeld. func (s *Store) UpsertUser(ctx context.Context, authID int64, username, avatar string, authAdmin bool) (User, error) { username = strings.ToLower(strings.TrimSpace(username)) if err := names.ValidUsername(username); err != nil { return User{}, err } tx, err := s.db.BeginTx(ctx, nil) if err != nil { return User{}, err } defer func() { _ = tx.Rollback() }() var count int if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count); err != nil { return User{}, err } ts := now() var existingID int64 err = tx.QueryRowContext(ctx, `SELECT id FROM users WHERE auth_id = ?`, authID).Scan(&existingID) if errors.Is(err, sql.ErrNoRows) { var nameTaken int if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ?`, username).Scan(&nameTaken); err != nil { return User{}, err } if nameTaken > 0 { return User{}, i18n.E("err.userTaken", username) } admin := 0 if count == 0 { admin = 1 } res, err := tx.ExecContext(ctx, `INSERT INTO users (auth_id, username, avatar_url, auth_admin, instance_admin, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`, authID, username, avatar, boolInt(authAdmin), admin, ts, ts) if err != nil { return User{}, err } existingID, _ = res.LastInsertId() } else if err != nil { return User{}, err } else { if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ? AND username = ?`, username, avatar, boolInt(authAdmin), ts, existingID, username); err != nil { return User{}, err } // Naamwijziging alleen als de nieuwe naam vrij is en gelijk blijft als hij botst. var current string if err := tx.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, existingID).Scan(¤t); err != nil { return User{}, err } if current != username { var taken int if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ? AND id <> ?`, username, existingID).Scan(&taken); err != nil { return User{}, err } var groupTaken int if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, username).Scan(&groupTaken); err != nil { return User{}, err } if taken == 0 && groupTaken == 0 { if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`, username, avatar, boolInt(authAdmin), ts, existingID); err != nil { return User{}, err } } else if _, err := tx.ExecContext(ctx, `UPDATE users SET avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`, avatar, boolInt(authAdmin), ts, existingID); err != nil { return User{}, err } } } if err := claimPending(ctx, tx, existingID, username); err != nil { return User{}, err } if err := tx.Commit(); err != nil { return User{}, err } return s.UserByID(ctx, existingID) } func claimPending(ctx context.Context, tx *sql.Tx, userID int64, username string) error { rows, err := tx.QueryContext(ctx, `SELECT id, scope_kind, scope_id, role FROM pending_members WHERE username = ?`, username) if err != nil { return err } defer rows.Close() type pend struct { id, scopeID int64 kind, role string } var all []pend for rows.Next() { var p pend if err := rows.Scan(&p.id, &p.kind, &p.scopeID, &p.role); err != nil { return err } all = append(all, p) } if err := rows.Err(); err != nil { return err } for _, p := range all { switch p.kind { case "group": if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil { return err } case "repo": if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO repo_members (repo_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil { return err } } if _, err := tx.ExecContext(ctx, `DELETE FROM pending_members WHERE id = ?`, p.id); err != nil { return err } } return nil } // UserByID leest één account. func (s *Store) UserByID(ctx context.Context, id int64) (User, error) { row := s.db.QueryRowContext(ctx, `SELECT id, auth_id, username, avatar_url, auth_admin, instance_admin, language, created_at FROM users WHERE id = ?`, id) return scanUser(row) } // UserByUsername leest op naam. func (s *Store) UserByUsername(ctx context.Context, username string) (User, error) { row := s.db.QueryRowContext(ctx, `SELECT id, auth_id, username, avatar_url, auth_admin, instance_admin, language, created_at FROM users WHERE username = ?`, strings.ToLower(username)) return scanUser(row) } func scanUser(row *sql.Row) (User, error) { var u User var admin, inst int var created string if err := row.Scan(&u.ID, &u.AuthID, &u.Username, &u.AvatarURL, &admin, &inst, &u.Language, &created); err != nil { if errors.Is(err, sql.ErrNoRows) { return User{}, errNotFound } return User{}, err } u.AuthAdmin = admin == 1 u.InstanceAdmin = inst == 1 if u.Language == "" { u.Language = "en" } u.CreatedAt = parseTime(created) return u, nil } // Session is een browsersessie. type Session struct { ID string UserID int64 AccessToken string ExpiresAt time.Time CSRF string User User } // CreateSession bewaart het bearer-token van de auth-server. func (s *Store) CreateSession(ctx context.Context, userID int64, accessToken string, expiresAt time.Time) (Session, error) { id, err := randomID() if err != nil { return Session{}, err } csrf, err := randomID() if err != nil { return Session{}, err } _, err = s.db.ExecContext(ctx, `INSERT INTO sessions (id, user_id, access_token, expires_at, csrf, created_at) VALUES (?, ?, ?, ?, ?, ?)`, id, userID, accessToken, expiresAt.UTC().Format(time.RFC3339), csrf, now()) if err != nil { return Session{}, err } return s.Session(ctx, id) } // Session leest een sessie met de gebruiker. func (s *Store) Session(ctx context.Context, id string) (Session, error) { var sess Session var exp string err := s.db.QueryRowContext(ctx, `SELECT id, user_id, access_token, expires_at, csrf FROM sessions WHERE id = ?`, id). Scan(&sess.ID, &sess.UserID, &sess.AccessToken, &exp, &sess.CSRF) if errors.Is(err, sql.ErrNoRows) { return Session{}, errNotFound } if err != nil { return Session{}, err } sess.ExpiresAt = parseTime(exp) u, err := s.UserByID(ctx, sess.UserID) if err != nil { return Session{}, err } sess.User = u return sess, nil } // UpdateSessionExpiry schuift de lokale kopie van de auth-sessie op. func (s *Store) UpdateSessionExpiry(ctx context.Context, id string, expiresAt time.Time) error { _, err := s.db.ExecContext(ctx, `UPDATE sessions SET expires_at = ? WHERE id = ?`, expiresAt.UTC().Format(time.RFC3339), id) return err } // DeleteSession verwijdert de lokale sessie. func (s *Store) DeleteSession(ctx context.Context, id string) error { _, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id = ?`, id) return err } // TokenInfo is een persoonlijk toegangstoken zonder het geheim. type TokenInfo struct { ID int64 Name string Prefix string CreatedAt time.Time LastUsedAt time.Time } // CreateToken maakt een bg_-token. De tekst wordt één keer teruggegeven. func (s *Store) CreateToken(ctx context.Context, userID int64, name string) (string, error) { name = strings.TrimSpace(name) if name == "" || len(name) > 80 { return "", i18n.E("err.tokenNeedsName") } plain, err := newToken("bg_") if err != nil { return "", err } _, err = s.db.ExecContext(ctx, `INSERT INTO access_tokens (user_id, name, token_hash, prefix, created_at) VALUES (?, ?, ?, ?, ?)`, userID, name, hashToken(plain), plain[:10], now()) if err != nil { return "", err } return plain, nil } // ListTokens toont de tokens van een gebruiker. func (s *Store) ListTokens(ctx context.Context, userID int64) ([]TokenInfo, error) { rows, err := s.db.QueryContext(ctx, `SELECT id, name, prefix, created_at, COALESCE(last_used_at, '') FROM access_tokens WHERE user_id = ? ORDER BY id DESC`, userID) if err != nil { return nil, err } defer rows.Close() var out []TokenInfo for rows.Next() { var t TokenInfo var created, used string if err := rows.Scan(&t.ID, &t.Name, &t.Prefix, &created, &used); err != nil { return nil, err } t.CreatedAt = parseTime(created) t.LastUsedAt = parseTime(used) out = append(out, t) } return out, rows.Err() } // DeleteToken verwijdert een token van deze gebruiker. func (s *Store) DeleteToken(ctx context.Context, userID, id int64) error { res, err := s.db.ExecContext(ctx, `DELETE FROM access_tokens WHERE id = ? AND user_id = ?`, id, userID) if err != nil { return err } n, _ := res.RowsAffected() if n == 0 { return errNotFound } return nil } // UserByToken zoekt de eigenaar van een persoonlijk token. func (s *Store) UserByToken(ctx context.Context, token string) (User, error) { if !strings.HasPrefix(token, "bg_") { return User{}, errNotFound } var userID, tokenID int64 err := s.db.QueryRowContext(ctx, `SELECT id, user_id FROM access_tokens WHERE token_hash = ?`, hashToken(token)).Scan(&tokenID, &userID) if errors.Is(err, sql.ErrNoRows) { return User{}, errNotFound } if err != nil { return User{}, err } _, _ = s.db.ExecContext(ctx, `UPDATE access_tokens SET last_used_at = ? WHERE id = ?`, now(), tokenID) return s.UserByID(ctx, userID) } // SSHKey is een publieke sleutel voor git-over-SSH. type SSHKey struct { ID int64 UserID int64 Name string PublicKey string Fingerprint string CreatedAt time.Time } // AddSSHKey bewaart één OpenSSH-regel. func (s *Store) AddSSHKey(ctx context.Context, userID int64, name, line, fingerprint string) error { name = strings.TrimSpace(name) if name == "" { name = "sleutel" } _, err := s.db.ExecContext(ctx, `INSERT INTO ssh_keys (user_id, name, public_key, fingerprint, created_at) VALUES (?, ?, ?, ?, ?)`, userID, name, strings.TrimSpace(line), fingerprint, now()) if err != nil && strings.Contains(err.Error(), "UNIQUE") { return i18n.E("err.sshDup") } return err } // ListSSHKeys toont de sleutels van een gebruiker. func (s *Store) ListSSHKeys(ctx context.Context, userID int64) ([]SSHKey, error) { rows, err := s.db.QueryContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE user_id = ? ORDER BY id`, userID) if err != nil { return nil, err } defer rows.Close() return scanKeys(rows) } // SSHKeyByFingerprint zoekt een sleutel voor de SSH-server. func (s *Store) SSHKeyByFingerprint(ctx context.Context, fingerprint string) (SSHKey, error) { row := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE fingerprint = ?`, fingerprint) var k SSHKey var created string if err := row.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil { if errors.Is(err, sql.ErrNoRows) { return SSHKey{}, errNotFound } return SSHKey{}, err } k.CreatedAt = parseTime(created) return k, nil } // DeleteSSHKey verwijdert een sleutel van deze gebruiker. func (s *Store) DeleteSSHKey(ctx context.Context, userID, id int64) error { res, err := s.db.ExecContext(ctx, `DELETE FROM ssh_keys WHERE id = ? AND user_id = ?`, id, userID) if err != nil { return err } n, _ := res.RowsAffected() if n == 0 { return errNotFound } return nil } func scanKeys(rows *sql.Rows) ([]SSHKey, error) { var out []SSHKey for rows.Next() { var k SSHKey var created string if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil { return nil, err } k.CreatedAt = parseTime(created) out = append(out, k) } return out, rows.Err() } func randomID() (string, error) { b := make([]byte, 32) if _, err := rand.Read(b); err != nil { return "", err } return hex.EncodeToString(b), nil } func newToken(prefix string) (string, error) { b := make([]byte, 32) if _, err := rand.Read(b); err != nil { return "", err } return prefix + hex.EncodeToString(b), nil } func hashToken(token string) string { sum := sha256.Sum256([]byte(token)) return hex.EncodeToString(sum[:]) } func boolInt(b bool) int { if b { return 1 } return 0 } var errNotFound = fmt.Errorf("niet gevonden") // ErrNotFound is het resultaat als een rij ontbreekt. func ErrNotFound() error { return errNotFound } // IsNotFound meldt of err een ontbrekende rij is. func IsNotFound(err error) bool { return errors.Is(err, errNotFound) } func mustJSON(v any) string { b, _ := json.Marshal(v) if b == nil { return "null" } return string(b) } // EncryptSecret versleutelt een geheim met de installatiesleutel. func (s *Store) encrypt(plain string) (nonce, ct []byte, err error) { return secretbox.Encrypt(s.key, plain) } func (s *Store) decrypt(nonce, ct []byte) (string, error) { return secretbox.Decrypt(s.key, nonce, ct) }