BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

feat: koppel commits aan wie ze pushte
timfa project_87923614_bot_3aff574f6f98414b945a8799374f9092 · pushed by timfa · 3 weeks ago
Failed 6c3f521b History
store.go 22.4 KiB Download Raw
1// Package store bewaart BlueGit in één SQLite-bestand op de datadisk.
2package store
3
4import (
5 "context"
6 "crypto/rand"
7 "crypto/sha256"
8 "database/sql"
9 "encoding/hex"
10 "encoding/json"
11 "errors"
12 "fmt"
13 "strings"
14 "time"
15
16 "gitlab.com/Timfa/bluegit/internal/i18n"
17 "gitlab.com/Timfa/bluegit/internal/names"
18 "gitlab.com/Timfa/bluegit/internal/secretbox"
19
20 _ "modernc.org/sqlite"
21)
22
23// Store is de metadata van deze installatie.
24type Store struct {
25 db *sql.DB
26 key []byte
27}
28
29// Open opent of maakt het databasebestand. key is 32 bytes voor geheimen.
30func Open(path string, key []byte) (*Store, error) {
31 if len(key) != 32 {
32 return nil, fmt.Errorf("geheime sleutel moet 32 bytes zijn")
33 }
34 dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)"
35 db, err := sql.Open("sqlite", dsn)
36 if err != nil {
37 return nil, err
38 }
39 db.SetMaxOpenConns(8)
40 s := &Store{db: db, key: key}
41 if _, err := db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`); err != nil {
42 _ = db.Close()
43 return nil, err
44 }
45 if err := s.migrate(); err != nil {
46 _ = db.Close()
47 return nil, err
48 }
49 return s, nil
50}
51
52// Close sluit de database.
53func (s *Store) Close() error { return s.db.Close() }
54
55func (s *Store) migrate() error {
56 if _, err := s.db.Exec(schema); err != nil {
57 return err
58 }
59 alters := []string{
60 `ALTER TABLE users ADD COLUMN language TEXT NOT NULL DEFAULT 'en'`,
61 `ALTER TABLE groups ADD COLUMN visibility TEXT NOT NULL DEFAULT 'private'`,
62 `ALTER TABLE groups ADD COLUMN default_role TEXT NOT NULL DEFAULT 'developer'`,
63 `ALTER TABLE groups ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
64 `ALTER TABLE repos ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
65 }
66 for _, q := range alters {
67 if _, err := s.db.Exec(q); err != nil && !strings.Contains(strings.ToLower(err.Error()), "duplicate column") {
68 return err
69 }
70 }
71 return nil
72}
73
74const schema = `
75CREATE TABLE IF NOT EXISTS users (
76 id INTEGER PRIMARY KEY,
77 auth_id INTEGER NOT NULL UNIQUE,
78 username TEXT NOT NULL UNIQUE,
79 avatar_url TEXT NOT NULL DEFAULT '',
80 auth_admin INTEGER NOT NULL DEFAULT 0,
81 instance_admin INTEGER NOT NULL DEFAULT 0,
82 language TEXT NOT NULL DEFAULT 'en',
83 created_at TEXT NOT NULL,
84 updated_at TEXT NOT NULL
85);
86CREATE TABLE IF NOT EXISTS sessions (
87 id TEXT PRIMARY KEY,
88 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
89 access_token TEXT NOT NULL,
90 expires_at TEXT NOT NULL,
91 csrf TEXT NOT NULL,
92 created_at TEXT NOT NULL
93);
94CREATE TABLE IF NOT EXISTS access_tokens (
95 id INTEGER PRIMARY KEY,
96 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
97 name TEXT NOT NULL,
98 token_hash TEXT NOT NULL UNIQUE,
99 prefix TEXT NOT NULL,
100 created_at TEXT NOT NULL,
101 last_used_at TEXT,
102 expires_at TEXT
103);
104CREATE TABLE IF NOT EXISTS ssh_keys (
105 id INTEGER PRIMARY KEY,
106 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
107 name TEXT NOT NULL,
108 public_key TEXT NOT NULL,
109 fingerprint TEXT NOT NULL UNIQUE,
110 created_at TEXT NOT NULL
111);
112CREATE TABLE IF NOT EXISTS groups (
113 id INTEGER PRIMARY KEY,
114 name TEXT NOT NULL,
115 path TEXT NOT NULL UNIQUE,
116 description TEXT NOT NULL DEFAULT '',
117 visibility TEXT NOT NULL DEFAULT 'private',
118 default_role TEXT NOT NULL DEFAULT 'developer',
119 logo_mime TEXT NOT NULL DEFAULT '',
120 created_by INTEGER NOT NULL,
121 created_at TEXT NOT NULL
122);
123CREATE TABLE IF NOT EXISTS group_members (
124 group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
125 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
126 role TEXT NOT NULL,
127 PRIMARY KEY (group_id, user_id)
128);
129CREATE TABLE IF NOT EXISTS repos (
130 id INTEGER PRIMARY KEY,
131 owner_kind TEXT NOT NULL,
132 owner_id INTEGER NOT NULL,
133 name TEXT NOT NULL,
134 description TEXT NOT NULL DEFAULT '',
135 default_branch TEXT NOT NULL,
136 visibility TEXT NOT NULL DEFAULT 'private',
137 logo_mime TEXT NOT NULL DEFAULT '',
138 created_at TEXT NOT NULL,
139 UNIQUE(owner_kind, owner_id, name)
140);
141CREATE TABLE IF NOT EXISTS repo_members (
142 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
143 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
144 role TEXT NOT NULL,
145 PRIMARY KEY (repo_id, user_id)
146);
147CREATE TABLE IF NOT EXISTS pending_members (
148 id INTEGER PRIMARY KEY,
149 scope_kind TEXT NOT NULL,
150 scope_id INTEGER NOT NULL,
151 username TEXT NOT NULL,
152 role TEXT NOT NULL,
153 UNIQUE(scope_kind, scope_id, username)
154);
155CREATE TABLE IF NOT EXISTS protected_branches (
156 id INTEGER PRIMARY KEY,
157 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
158 pattern TEXT NOT NULL,
159 UNIQUE(repo_id, pattern)
160);
161CREATE TABLE IF NOT EXISTS secrets (
162 id INTEGER PRIMARY KEY,
163 scope_kind TEXT NOT NULL,
164 scope_id INTEGER NOT NULL,
165 name TEXT NOT NULL,
166 ciphertext BLOB NOT NULL,
167 nonce BLOB NOT NULL,
168 protected INTEGER NOT NULL DEFAULT 0,
169 created_at TEXT NOT NULL,
170 UNIQUE(scope_kind, scope_id, name)
171);
172CREATE TABLE IF NOT EXISTS webhooks (
173 id INTEGER PRIMARY KEY,
174 scope_kind TEXT NOT NULL,
175 scope_id INTEGER NOT NULL,
176 name TEXT NOT NULL,
177 preset TEXT NOT NULL,
178 url_template TEXT NOT NULL,
179 headers_json TEXT NOT NULL,
180 body_template TEXT NOT NULL,
181 events_json TEXT NOT NULL,
182 branch_filter TEXT NOT NULL DEFAULT '',
183 active INTEGER NOT NULL DEFAULT 1,
184 created_at TEXT NOT NULL
185);
186CREATE TABLE IF NOT EXISTS webhook_deliveries (
187 id INTEGER PRIMARY KEY,
188 webhook_id INTEGER NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
189 event_type TEXT NOT NULL,
190 status_code INTEGER NOT NULL DEFAULT 0,
191 error TEXT NOT NULL DEFAULT '',
192 created_at TEXT NOT NULL
193);
194CREATE TABLE IF NOT EXISTS merge_requests (
195 id INTEGER PRIMARY KEY,
196 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
197 number INTEGER NOT NULL,
198 title TEXT NOT NULL,
199 description TEXT NOT NULL DEFAULT '',
200 source_branch TEXT NOT NULL,
201 target_branch TEXT NOT NULL,
202 author_id INTEGER NOT NULL,
203 status TEXT NOT NULL,
204 created_at TEXT NOT NULL,
205 updated_at TEXT NOT NULL,
206 UNIQUE(repo_id, number)
207);
208CREATE TABLE IF NOT EXISTS pipelines (
209 id INTEGER PRIMARY KEY,
210 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
211 commit_sha TEXT NOT NULL,
212 ref TEXT NOT NULL,
213 status TEXT NOT NULL,
214 created_at TEXT NOT NULL,
215 finished_at TEXT
216);
217CREATE TABLE IF NOT EXISTS jobs (
218 id INTEGER PRIMARY KEY,
219 pipeline_id INTEGER NOT NULL REFERENCES pipelines(id) ON DELETE CASCADE,
220 repo_id INTEGER NOT NULL,
221 name TEXT NOT NULL,
222 stage TEXT NOT NULL,
223 position INTEGER NOT NULL,
224 status TEXT NOT NULL,
225 image TEXT NOT NULL,
226 script TEXT NOT NULL,
227 needs_json TEXT NOT NULL,
228 needs_set INTEGER NOT NULL,
229 variables_json TEXT NOT NULL,
230 artifact_paths TEXT NOT NULL,
231 artifact_expire_seconds INTEGER NOT NULL DEFAULT 0,
232 allow_failure INTEGER NOT NULL DEFAULT 0,
233 log TEXT NOT NULL DEFAULT '',
234 started_at TEXT,
235 finished_at TEXT,
236 UNIQUE(pipeline_id, name)
237);
238CREATE TABLE IF NOT EXISTS artifacts (
239 id INTEGER PRIMARY KEY,
240 job_id INTEGER NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
241 path TEXT NOT NULL,
242 storage_path TEXT NOT NULL,
243 size INTEGER NOT NULL,
244 expires_at TEXT
245);
246CREATE TABLE IF NOT EXISTS job_tokens (
247 job_id INTEGER PRIMARY KEY REFERENCES jobs(id) ON DELETE CASCADE,
248 token_hash TEXT NOT NULL UNIQUE,
249 created_at TEXT NOT NULL
250);
251CREATE TABLE IF NOT EXISTS releases (
252 id INTEGER PRIMARY KEY,
253 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
254 tag TEXT NOT NULL,
255 commit_sha TEXT NOT NULL,
256 name TEXT NOT NULL,
257 body TEXT NOT NULL DEFAULT '',
258 author_id INTEGER,
259 job_id INTEGER,
260 created_at TEXT NOT NULL,
261 UNIQUE(repo_id, tag)
262);
263CREATE TABLE IF NOT EXISTS release_assets (
264 id INTEGER PRIMARY KEY,
265 release_id INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
266 name TEXT NOT NULL,
267 storage_path TEXT NOT NULL,
268 size INTEGER NOT NULL,
269 UNIQUE(release_id, name)
270);
271CREATE TABLE IF NOT EXISTS contributions (
272 id INTEGER PRIMARY KEY,
273 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
274 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
275 kind TEXT NOT NULL,
276 ref TEXT NOT NULL,
277 occurred_at TEXT NOT NULL,
278 UNIQUE(repo_id, kind, ref)
279);
280CREATE INDEX IF NOT EXISTS contributions_repo_time ON contributions(repo_id, occurred_at);
281CREATE INDEX IF NOT EXISTS contributions_user_time ON contributions(user_id, occurred_at);
282CREATE TABLE IF NOT EXISTS contribution_sync (
283 repo_id INTEGER PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
284 synced_at TEXT NOT NULL
285);
286CREATE TABLE IF NOT EXISTS stars (
287 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
288 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
289 created_at TEXT NOT NULL,
290 PRIMARY KEY (user_id, repo_id)
291);
292CREATE TABLE IF NOT EXISTS notes (
293 id INTEGER PRIMARY KEY,
294 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
295 mr_id INTEGER NOT NULL REFERENCES merge_requests(id) ON DELETE CASCADE,
296 author_id INTEGER NOT NULL REFERENCES users(id),
297 body TEXT NOT NULL,
298 created_at TEXT NOT NULL
299);
300CREATE TABLE IF NOT EXISTS commit_pushers (
301 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
302 sha TEXT NOT NULL,
303 user_id INTEGER NOT NULL REFERENCES users(id),
304 author_name TEXT NOT NULL,
305 author_email TEXT NOT NULL,
306 created_at TEXT NOT NULL,
307 PRIMARY KEY (repo_id, sha)
308);
309`
310
311func now() string { return time.Now().UTC().Format(time.RFC3339) }
312
313func parseTime(s string) time.Time {
314 t, err := time.Parse(time.RFC3339, s)
315 if err != nil {
316 return time.Time{}
317 }
318 return t
319}
320
321// User is een ingelogd account van de auth-server.
322type User struct {
323 ID int64
324 AuthID int64
325 Username string
326 AvatarURL string
327 AuthAdmin bool
328 InstanceAdmin bool
329 Language string
330 CreatedAt time.Time
331}
332
333// UpsertUser maakt of ververst een account. De eerste gebruiker wordt
334// installatiebeheerder. Wachtende lidmaatschappen op deze naam worden gekoppeld.
335func (s *Store) UpsertUser(ctx context.Context, authID int64, username, avatar string, authAdmin bool) (User, error) {
336 username = strings.ToLower(strings.TrimSpace(username))
337 if err := names.ValidUsername(username); err != nil {
338 return User{}, err
339 }
340 tx, err := s.db.BeginTx(ctx, nil)
341 if err != nil {
342 return User{}, err
343 }
344 defer func() { _ = tx.Rollback() }()
345
346 var count int
347 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count); err != nil {
348 return User{}, err
349 }
350 ts := now()
351 var existingID int64
352 err = tx.QueryRowContext(ctx, `SELECT id FROM users WHERE auth_id = ?`, authID).Scan(&existingID)
353 if errors.Is(err, sql.ErrNoRows) {
354 var nameTaken int
355 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ?`, username).Scan(&nameTaken); err != nil {
356 return User{}, err
357 }
358 if nameTaken > 0 {
359 return User{}, i18n.E("err.userTaken", username)
360 }
361 admin := 0
362 if count == 0 {
363 admin = 1
364 }
365 res, err := tx.ExecContext(ctx, `INSERT INTO users (auth_id, username, avatar_url, auth_admin, instance_admin, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
366 authID, username, avatar, boolInt(authAdmin), admin, ts, ts)
367 if err != nil {
368 return User{}, err
369 }
370 existingID, _ = res.LastInsertId()
371 } else if err != nil {
372 return User{}, err
373 } else {
374 if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ? AND username = ?`,
375 username, avatar, boolInt(authAdmin), ts, existingID, username); err != nil {
376 return User{}, err
377 }
378 // Naamwijziging alleen als de nieuwe naam vrij is en gelijk blijft als hij botst.
379 var current string
380 if err := tx.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, existingID).Scan(&current); err != nil {
381 return User{}, err
382 }
383 if current != username {
384 var taken int
385 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ? AND id <> ?`, username, existingID).Scan(&taken); err != nil {
386 return User{}, err
387 }
388 var groupTaken int
389 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, username).Scan(&groupTaken); err != nil {
390 return User{}, err
391 }
392 if taken == 0 && groupTaken == 0 {
393 if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
394 username, avatar, boolInt(authAdmin), ts, existingID); err != nil {
395 return User{}, err
396 }
397 } else if _, err := tx.ExecContext(ctx, `UPDATE users SET avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
398 avatar, boolInt(authAdmin), ts, existingID); err != nil {
399 return User{}, err
400 }
401 }
402 }
403 if err := claimPending(ctx, tx, existingID, username); err != nil {
404 return User{}, err
405 }
406 if err := tx.Commit(); err != nil {
407 return User{}, err
408 }
409 return s.UserByID(ctx, existingID)
410}
411
412func claimPending(ctx context.Context, tx *sql.Tx, userID int64, username string) error {
413 rows, err := tx.QueryContext(ctx, `SELECT id, scope_kind, scope_id, role FROM pending_members WHERE username = ?`, username)
414 if err != nil {
415 return err
416 }
417 defer rows.Close()
418 type pend struct {
419 id, scopeID int64
420 kind, role string
421 }
422 var all []pend
423 for rows.Next() {
424 var p pend
425 if err := rows.Scan(&p.id, &p.kind, &p.scopeID, &p.role); err != nil {
426 return err
427 }
428 all = append(all, p)
429 }
430 if err := rows.Err(); err != nil {
431 return err
432 }
433 for _, p := range all {
434 switch p.kind {
435 case "group":
436 if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
437 return err
438 }
439 case "repo":
440 if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO repo_members (repo_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
441 return err
442 }
443 }
444 if _, err := tx.ExecContext(ctx, `DELETE FROM pending_members WHERE id = ?`, p.id); err != nil {
445 return err
446 }
447 }
448 return nil
449}
450
451// UserByID leest één account.
452func (s *Store) UserByID(ctx context.Context, id int64) (User, error) {
453 row := s.db.QueryRowContext(ctx, `SELECT id, auth_id, username, avatar_url, auth_admin, instance_admin, language, created_at FROM users WHERE id = ?`, id)
454 return scanUser(row)
455}
456
457// UserByUsername leest op naam.
458func (s *Store) UserByUsername(ctx context.Context, username string) (User, error) {
459 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))
460 return scanUser(row)
461}
462
463func scanUser(row *sql.Row) (User, error) {
464 var u User
465 var admin, inst int
466 var created string
467 if err := row.Scan(&u.ID, &u.AuthID, &u.Username, &u.AvatarURL, &admin, &inst, &u.Language, &created); err != nil {
468 if errors.Is(err, sql.ErrNoRows) {
469 return User{}, errNotFound
470 }
471 return User{}, err
472 }
473 u.AuthAdmin = admin == 1
474 u.InstanceAdmin = inst == 1
475 if u.Language == "" {
476 u.Language = "en"
477 }
478 u.CreatedAt = parseTime(created)
479 return u, nil
480}
481
482// Session is een browsersessie.
483type Session struct {
484 ID string
485 UserID int64
486 AccessToken string
487 ExpiresAt time.Time
488 CSRF string
489 User User
490}
491
492// CreateSession bewaart het bearer-token van de auth-server.
493func (s *Store) CreateSession(ctx context.Context, userID int64, accessToken string, expiresAt time.Time) (Session, error) {
494 id, err := randomID()
495 if err != nil {
496 return Session{}, err
497 }
498 csrf, err := randomID()
499 if err != nil {
500 return Session{}, err
501 }
502 _, err = s.db.ExecContext(ctx, `INSERT INTO sessions (id, user_id, access_token, expires_at, csrf, created_at) VALUES (?, ?, ?, ?, ?, ?)`,
503 id, userID, accessToken, expiresAt.UTC().Format(time.RFC3339), csrf, now())
504 if err != nil {
505 return Session{}, err
506 }
507 return s.Session(ctx, id)
508}
509
510// Session leest een sessie met de gebruiker.
511func (s *Store) Session(ctx context.Context, id string) (Session, error) {
512 var sess Session
513 var exp string
514 err := s.db.QueryRowContext(ctx, `SELECT id, user_id, access_token, expires_at, csrf FROM sessions WHERE id = ?`, id).
515 Scan(&sess.ID, &sess.UserID, &sess.AccessToken, &exp, &sess.CSRF)
516 if errors.Is(err, sql.ErrNoRows) {
517 return Session{}, errNotFound
518 }
519 if err != nil {
520 return Session{}, err
521 }
522 sess.ExpiresAt = parseTime(exp)
523 u, err := s.UserByID(ctx, sess.UserID)
524 if err != nil {
525 return Session{}, err
526 }
527 sess.User = u
528 return sess, nil
529}
530
531// UpdateSessionExpiry schuift de lokale kopie van de auth-sessie op.
532func (s *Store) UpdateSessionExpiry(ctx context.Context, id string, expiresAt time.Time) error {
533 _, err := s.db.ExecContext(ctx, `UPDATE sessions SET expires_at = ? WHERE id = ?`, expiresAt.UTC().Format(time.RFC3339), id)
534 return err
535}
536
537// DeleteSession verwijdert de lokale sessie.
538func (s *Store) DeleteSession(ctx context.Context, id string) error {
539 _, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id = ?`, id)
540 return err
541}
542
543// TokenInfo is een persoonlijk toegangstoken zonder het geheim.
544type TokenInfo struct {
545 ID int64
546 Name string
547 Prefix string
548 CreatedAt time.Time
549 LastUsedAt time.Time
550}
551
552// CreateToken maakt een bg_-token. De tekst wordt één keer teruggegeven.
553func (s *Store) CreateToken(ctx context.Context, userID int64, name string) (string, error) {
554 name = strings.TrimSpace(name)
555 if name == "" || len(name) > 80 {
556 return "", i18n.E("err.tokenNeedsName")
557 }
558 plain, err := newToken("bg_")
559 if err != nil {
560 return "", err
561 }
562 _, err = s.db.ExecContext(ctx, `INSERT INTO access_tokens (user_id, name, token_hash, prefix, created_at) VALUES (?, ?, ?, ?, ?)`,
563 userID, name, hashToken(plain), plain[:10], now())
564 if err != nil {
565 return "", err
566 }
567 return plain, nil
568}
569
570// ListTokens toont de tokens van een gebruiker.
571func (s *Store) ListTokens(ctx context.Context, userID int64) ([]TokenInfo, error) {
572 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)
573 if err != nil {
574 return nil, err
575 }
576 defer rows.Close()
577 var out []TokenInfo
578 for rows.Next() {
579 var t TokenInfo
580 var created, used string
581 if err := rows.Scan(&t.ID, &t.Name, &t.Prefix, &created, &used); err != nil {
582 return nil, err
583 }
584 t.CreatedAt = parseTime(created)
585 t.LastUsedAt = parseTime(used)
586 out = append(out, t)
587 }
588 return out, rows.Err()
589}
590
591// DeleteToken verwijdert een token van deze gebruiker.
592func (s *Store) DeleteToken(ctx context.Context, userID, id int64) error {
593 res, err := s.db.ExecContext(ctx, `DELETE FROM access_tokens WHERE id = ? AND user_id = ?`, id, userID)
594 if err != nil {
595 return err
596 }
597 n, _ := res.RowsAffected()
598 if n == 0 {
599 return errNotFound
600 }
601 return nil
602}
603
604// UserByToken zoekt de eigenaar van een persoonlijk token.
605func (s *Store) UserByToken(ctx context.Context, token string) (User, error) {
606 if !strings.HasPrefix(token, "bg_") {
607 return User{}, errNotFound
608 }
609 var userID, tokenID int64
610 err := s.db.QueryRowContext(ctx, `SELECT id, user_id FROM access_tokens WHERE token_hash = ?`, hashToken(token)).Scan(&tokenID, &userID)
611 if errors.Is(err, sql.ErrNoRows) {
612 return User{}, errNotFound
613 }
614 if err != nil {
615 return User{}, err
616 }
617 _, _ = s.db.ExecContext(ctx, `UPDATE access_tokens SET last_used_at = ? WHERE id = ?`, now(), tokenID)
618 return s.UserByID(ctx, userID)
619}
620
621// SSHKey is een publieke sleutel voor git-over-SSH.
622type SSHKey struct {
623 ID int64
624 UserID int64
625 Name string
626 PublicKey string
627 Fingerprint string
628 CreatedAt time.Time
629}
630
631// AddSSHKey bewaart één OpenSSH-regel.
632func (s *Store) AddSSHKey(ctx context.Context, userID int64, name, line, fingerprint string) error {
633 name = strings.TrimSpace(name)
634 if name == "" {
635 name = "sleutel"
636 }
637 _, err := s.db.ExecContext(ctx, `INSERT INTO ssh_keys (user_id, name, public_key, fingerprint, created_at) VALUES (?, ?, ?, ?, ?)`,
638 userID, name, strings.TrimSpace(line), fingerprint, now())
639 if err != nil && strings.Contains(err.Error(), "UNIQUE") {
640 return i18n.E("err.sshDup")
641 }
642 return err
643}
644
645// ListSSHKeys toont de sleutels van een gebruiker.
646func (s *Store) ListSSHKeys(ctx context.Context, userID int64) ([]SSHKey, error) {
647 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)
648 if err != nil {
649 return nil, err
650 }
651 defer rows.Close()
652 return scanKeys(rows)
653}
654
655// SSHKeyByFingerprint zoekt een sleutel voor de SSH-server.
656func (s *Store) SSHKeyByFingerprint(ctx context.Context, fingerprint string) (SSHKey, error) {
657 row := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE fingerprint = ?`, fingerprint)
658 var k SSHKey
659 var created string
660 if err := row.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
661 if errors.Is(err, sql.ErrNoRows) {
662 return SSHKey{}, errNotFound
663 }
664 return SSHKey{}, err
665 }
666 k.CreatedAt = parseTime(created)
667 return k, nil
668}
669
670// DeleteSSHKey verwijdert een sleutel van deze gebruiker.
671func (s *Store) DeleteSSHKey(ctx context.Context, userID, id int64) error {
672 res, err := s.db.ExecContext(ctx, `DELETE FROM ssh_keys WHERE id = ? AND user_id = ?`, id, userID)
673 if err != nil {
674 return err
675 }
676 n, _ := res.RowsAffected()
677 if n == 0 {
678 return errNotFound
679 }
680 return nil
681}
682
683func scanKeys(rows *sql.Rows) ([]SSHKey, error) {
684 var out []SSHKey
685 for rows.Next() {
686 var k SSHKey
687 var created string
688 if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
689 return nil, err
690 }
691 k.CreatedAt = parseTime(created)
692 out = append(out, k)
693 }
694 return out, rows.Err()
695}
696
697func randomID() (string, error) {
698 b := make([]byte, 32)
699 if _, err := rand.Read(b); err != nil {
700 return "", err
701 }
702 return hex.EncodeToString(b), nil
703}
704
705func newToken(prefix string) (string, error) {
706 b := make([]byte, 32)
707 if _, err := rand.Read(b); err != nil {
708 return "", err
709 }
710 return prefix + hex.EncodeToString(b), nil
711}
712
713func hashToken(token string) string {
714 sum := sha256.Sum256([]byte(token))
715 return hex.EncodeToString(sum[:])
716}
717
718func boolInt(b bool) int {
719 if b {
720 return 1
721 }
722 return 0
723}
724
725var errNotFound = fmt.Errorf("niet gevonden")
726
727// ErrNotFound is het resultaat als een rij ontbreekt.
728func ErrNotFound() error { return errNotFound }
729
730// IsNotFound meldt of err een ontbrekende rij is.
731func IsNotFound(err error) bool { return errors.Is(err, errNotFound) }
732
733func mustJSON(v any) string {
734 b, _ := json.Marshal(v)
735 if b == nil {
736 return "null"
737 }
738 return string(b)
739}
740
741// EncryptSecret versleutelt een geheim met de installatiesleutel.
742func (s *Store) encrypt(plain string) (nonce, ct []byte, err error) {
743 return secretbox.Encrypt(s.key, plain)
744}
745
746func (s *Store) decrypt(nonce, ct []byte) (string, error) {
747 return secretbox.Decrypt(s.key, nonce, ct)
748}