BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

feat: nieuw-formulier, groepsoverzicht, logo's en talen
timfa project_87923614_bot_3aff574f6f98414b945a8799374f9092 · pushed by timfa · 3 weeks ago
Failed d1203d3b History
store.go 22.1 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);
300`
301
302func now() string { return time.Now().UTC().Format(time.RFC3339) }
303
304func parseTime(s string) time.Time {
305 t, err := time.Parse(time.RFC3339, s)
306 if err != nil {
307 return time.Time{}
308 }
309 return t
310}
311
312// User is een ingelogd account van de auth-server.
313type User struct {
314 ID int64
315 AuthID int64
316 Username string
317 AvatarURL string
318 AuthAdmin bool
319 InstanceAdmin bool
320 Language string
321 CreatedAt time.Time
322}
323
324// UpsertUser maakt of ververst een account. De eerste gebruiker wordt
325// installatiebeheerder. Wachtende lidmaatschappen op deze naam worden gekoppeld.
326func (s *Store) UpsertUser(ctx context.Context, authID int64, username, avatar string, authAdmin bool) (User, error) {
327 username = strings.ToLower(strings.TrimSpace(username))
328 if err := names.ValidUsername(username); err != nil {
329 return User{}, err
330 }
331 tx, err := s.db.BeginTx(ctx, nil)
332 if err != nil {
333 return User{}, err
334 }
335 defer func() { _ = tx.Rollback() }()
336
337 var count int
338 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users`).Scan(&count); err != nil {
339 return User{}, err
340 }
341 ts := now()
342 var existingID int64
343 err = tx.QueryRowContext(ctx, `SELECT id FROM users WHERE auth_id = ?`, authID).Scan(&existingID)
344 if errors.Is(err, sql.ErrNoRows) {
345 var nameTaken int
346 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ?`, username).Scan(&nameTaken); err != nil {
347 return User{}, err
348 }
349 if nameTaken > 0 {
350 return User{}, i18n.E("err.userTaken", username)
351 }
352 admin := 0
353 if count == 0 {
354 admin = 1
355 }
356 res, err := tx.ExecContext(ctx, `INSERT INTO users (auth_id, username, avatar_url, auth_admin, instance_admin, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
357 authID, username, avatar, boolInt(authAdmin), admin, ts, ts)
358 if err != nil {
359 return User{}, err
360 }
361 existingID, _ = res.LastInsertId()
362 } else if err != nil {
363 return User{}, err
364 } else {
365 if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ? AND username = ?`,
366 username, avatar, boolInt(authAdmin), ts, existingID, username); err != nil {
367 return User{}, err
368 }
369 // Naamwijziging alleen als de nieuwe naam vrij is en gelijk blijft als hij botst.
370 var current string
371 if err := tx.QueryRowContext(ctx, `SELECT username FROM users WHERE id = ?`, existingID).Scan(&current); err != nil {
372 return User{}, err
373 }
374 if current != username {
375 var taken int
376 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM users WHERE username = ? AND id <> ?`, username, existingID).Scan(&taken); err != nil {
377 return User{}, err
378 }
379 var groupTaken int
380 if err := tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM groups WHERE path = ?`, username).Scan(&groupTaken); err != nil {
381 return User{}, err
382 }
383 if taken == 0 && groupTaken == 0 {
384 if _, err := tx.ExecContext(ctx, `UPDATE users SET username = ?, avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
385 username, avatar, boolInt(authAdmin), ts, existingID); err != nil {
386 return User{}, err
387 }
388 } else if _, err := tx.ExecContext(ctx, `UPDATE users SET avatar_url = ?, auth_admin = ?, updated_at = ? WHERE id = ?`,
389 avatar, boolInt(authAdmin), ts, existingID); err != nil {
390 return User{}, err
391 }
392 }
393 }
394 if err := claimPending(ctx, tx, existingID, username); err != nil {
395 return User{}, err
396 }
397 if err := tx.Commit(); err != nil {
398 return User{}, err
399 }
400 return s.UserByID(ctx, existingID)
401}
402
403func claimPending(ctx context.Context, tx *sql.Tx, userID int64, username string) error {
404 rows, err := tx.QueryContext(ctx, `SELECT id, scope_kind, scope_id, role FROM pending_members WHERE username = ?`, username)
405 if err != nil {
406 return err
407 }
408 defer rows.Close()
409 type pend struct {
410 id, scopeID int64
411 kind, role string
412 }
413 var all []pend
414 for rows.Next() {
415 var p pend
416 if err := rows.Scan(&p.id, &p.kind, &p.scopeID, &p.role); err != nil {
417 return err
418 }
419 all = append(all, p)
420 }
421 if err := rows.Err(); err != nil {
422 return err
423 }
424 for _, p := range all {
425 switch p.kind {
426 case "group":
427 if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
428 return err
429 }
430 case "repo":
431 if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO repo_members (repo_id, user_id, role) VALUES (?, ?, ?)`, p.scopeID, userID, p.role); err != nil {
432 return err
433 }
434 }
435 if _, err := tx.ExecContext(ctx, `DELETE FROM pending_members WHERE id = ?`, p.id); err != nil {
436 return err
437 }
438 }
439 return nil
440}
441
442// UserByID leest één account.
443func (s *Store) UserByID(ctx context.Context, id int64) (User, error) {
444 row := s.db.QueryRowContext(ctx, `SELECT id, auth_id, username, avatar_url, auth_admin, instance_admin, language, created_at FROM users WHERE id = ?`, id)
445 return scanUser(row)
446}
447
448// UserByUsername leest op naam.
449func (s *Store) UserByUsername(ctx context.Context, username string) (User, error) {
450 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))
451 return scanUser(row)
452}
453
454func scanUser(row *sql.Row) (User, error) {
455 var u User
456 var admin, inst int
457 var created string
458 if err := row.Scan(&u.ID, &u.AuthID, &u.Username, &u.AvatarURL, &admin, &inst, &u.Language, &created); err != nil {
459 if errors.Is(err, sql.ErrNoRows) {
460 return User{}, errNotFound
461 }
462 return User{}, err
463 }
464 u.AuthAdmin = admin == 1
465 u.InstanceAdmin = inst == 1
466 if u.Language == "" {
467 u.Language = "en"
468 }
469 u.CreatedAt = parseTime(created)
470 return u, nil
471}
472
473// Session is een browsersessie.
474type Session struct {
475 ID string
476 UserID int64
477 AccessToken string
478 ExpiresAt time.Time
479 CSRF string
480 User User
481}
482
483// CreateSession bewaart het bearer-token van de auth-server.
484func (s *Store) CreateSession(ctx context.Context, userID int64, accessToken string, expiresAt time.Time) (Session, error) {
485 id, err := randomID()
486 if err != nil {
487 return Session{}, err
488 }
489 csrf, err := randomID()
490 if err != nil {
491 return Session{}, err
492 }
493 _, err = s.db.ExecContext(ctx, `INSERT INTO sessions (id, user_id, access_token, expires_at, csrf, created_at) VALUES (?, ?, ?, ?, ?, ?)`,
494 id, userID, accessToken, expiresAt.UTC().Format(time.RFC3339), csrf, now())
495 if err != nil {
496 return Session{}, err
497 }
498 return s.Session(ctx, id)
499}
500
501// Session leest een sessie met de gebruiker.
502func (s *Store) Session(ctx context.Context, id string) (Session, error) {
503 var sess Session
504 var exp string
505 err := s.db.QueryRowContext(ctx, `SELECT id, user_id, access_token, expires_at, csrf FROM sessions WHERE id = ?`, id).
506 Scan(&sess.ID, &sess.UserID, &sess.AccessToken, &exp, &sess.CSRF)
507 if errors.Is(err, sql.ErrNoRows) {
508 return Session{}, errNotFound
509 }
510 if err != nil {
511 return Session{}, err
512 }
513 sess.ExpiresAt = parseTime(exp)
514 u, err := s.UserByID(ctx, sess.UserID)
515 if err != nil {
516 return Session{}, err
517 }
518 sess.User = u
519 return sess, nil
520}
521
522// UpdateSessionExpiry schuift de lokale kopie van de auth-sessie op.
523func (s *Store) UpdateSessionExpiry(ctx context.Context, id string, expiresAt time.Time) error {
524 _, err := s.db.ExecContext(ctx, `UPDATE sessions SET expires_at = ? WHERE id = ?`, expiresAt.UTC().Format(time.RFC3339), id)
525 return err
526}
527
528// DeleteSession verwijdert de lokale sessie.
529func (s *Store) DeleteSession(ctx context.Context, id string) error {
530 _, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id = ?`, id)
531 return err
532}
533
534// TokenInfo is een persoonlijk toegangstoken zonder het geheim.
535type TokenInfo struct {
536 ID int64
537 Name string
538 Prefix string
539 CreatedAt time.Time
540 LastUsedAt time.Time
541}
542
543// CreateToken maakt een bg_-token. De tekst wordt één keer teruggegeven.
544func (s *Store) CreateToken(ctx context.Context, userID int64, name string) (string, error) {
545 name = strings.TrimSpace(name)
546 if name == "" || len(name) > 80 {
547 return "", i18n.E("err.tokenNeedsName")
548 }
549 plain, err := newToken("bg_")
550 if err != nil {
551 return "", err
552 }
553 _, err = s.db.ExecContext(ctx, `INSERT INTO access_tokens (user_id, name, token_hash, prefix, created_at) VALUES (?, ?, ?, ?, ?)`,
554 userID, name, hashToken(plain), plain[:10], now())
555 if err != nil {
556 return "", err
557 }
558 return plain, nil
559}
560
561// ListTokens toont de tokens van een gebruiker.
562func (s *Store) ListTokens(ctx context.Context, userID int64) ([]TokenInfo, error) {
563 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)
564 if err != nil {
565 return nil, err
566 }
567 defer rows.Close()
568 var out []TokenInfo
569 for rows.Next() {
570 var t TokenInfo
571 var created, used string
572 if err := rows.Scan(&t.ID, &t.Name, &t.Prefix, &created, &used); err != nil {
573 return nil, err
574 }
575 t.CreatedAt = parseTime(created)
576 t.LastUsedAt = parseTime(used)
577 out = append(out, t)
578 }
579 return out, rows.Err()
580}
581
582// DeleteToken verwijdert een token van deze gebruiker.
583func (s *Store) DeleteToken(ctx context.Context, userID, id int64) error {
584 res, err := s.db.ExecContext(ctx, `DELETE FROM access_tokens WHERE id = ? AND user_id = ?`, id, userID)
585 if err != nil {
586 return err
587 }
588 n, _ := res.RowsAffected()
589 if n == 0 {
590 return errNotFound
591 }
592 return nil
593}
594
595// UserByToken zoekt de eigenaar van een persoonlijk token.
596func (s *Store) UserByToken(ctx context.Context, token string) (User, error) {
597 if !strings.HasPrefix(token, "bg_") {
598 return User{}, errNotFound
599 }
600 var userID, tokenID int64
601 err := s.db.QueryRowContext(ctx, `SELECT id, user_id FROM access_tokens WHERE token_hash = ?`, hashToken(token)).Scan(&tokenID, &userID)
602 if errors.Is(err, sql.ErrNoRows) {
603 return User{}, errNotFound
604 }
605 if err != nil {
606 return User{}, err
607 }
608 _, _ = s.db.ExecContext(ctx, `UPDATE access_tokens SET last_used_at = ? WHERE id = ?`, now(), tokenID)
609 return s.UserByID(ctx, userID)
610}
611
612// SSHKey is een publieke sleutel voor git-over-SSH.
613type SSHKey struct {
614 ID int64
615 UserID int64
616 Name string
617 PublicKey string
618 Fingerprint string
619 CreatedAt time.Time
620}
621
622// AddSSHKey bewaart één OpenSSH-regel.
623func (s *Store) AddSSHKey(ctx context.Context, userID int64, name, line, fingerprint string) error {
624 name = strings.TrimSpace(name)
625 if name == "" {
626 name = "sleutel"
627 }
628 _, err := s.db.ExecContext(ctx, `INSERT INTO ssh_keys (user_id, name, public_key, fingerprint, created_at) VALUES (?, ?, ?, ?, ?)`,
629 userID, name, strings.TrimSpace(line), fingerprint, now())
630 if err != nil && strings.Contains(err.Error(), "UNIQUE") {
631 return i18n.E("err.sshDup")
632 }
633 return err
634}
635
636// ListSSHKeys toont de sleutels van een gebruiker.
637func (s *Store) ListSSHKeys(ctx context.Context, userID int64) ([]SSHKey, error) {
638 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)
639 if err != nil {
640 return nil, err
641 }
642 defer rows.Close()
643 return scanKeys(rows)
644}
645
646// SSHKeyByFingerprint zoekt een sleutel voor de SSH-server.
647func (s *Store) SSHKeyByFingerprint(ctx context.Context, fingerprint string) (SSHKey, error) {
648 row := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE fingerprint = ?`, fingerprint)
649 var k SSHKey
650 var created string
651 if err := row.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
652 if errors.Is(err, sql.ErrNoRows) {
653 return SSHKey{}, errNotFound
654 }
655 return SSHKey{}, err
656 }
657 k.CreatedAt = parseTime(created)
658 return k, nil
659}
660
661// DeleteSSHKey verwijdert een sleutel van deze gebruiker.
662func (s *Store) DeleteSSHKey(ctx context.Context, userID, id int64) error {
663 res, err := s.db.ExecContext(ctx, `DELETE FROM ssh_keys WHERE id = ? AND user_id = ?`, id, userID)
664 if err != nil {
665 return err
666 }
667 n, _ := res.RowsAffected()
668 if n == 0 {
669 return errNotFound
670 }
671 return nil
672}
673
674func scanKeys(rows *sql.Rows) ([]SSHKey, error) {
675 var out []SSHKey
676 for rows.Next() {
677 var k SSHKey
678 var created string
679 if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
680 return nil, err
681 }
682 k.CreatedAt = parseTime(created)
683 out = append(out, k)
684 }
685 return out, rows.Err()
686}
687
688func randomID() (string, error) {
689 b := make([]byte, 32)
690 if _, err := rand.Read(b); err != nil {
691 return "", err
692 }
693 return hex.EncodeToString(b), nil
694}
695
696func newToken(prefix string) (string, error) {
697 b := make([]byte, 32)
698 if _, err := rand.Read(b); err != nil {
699 return "", err
700 }
701 return prefix + hex.EncodeToString(b), nil
702}
703
704func hashToken(token string) string {
705 sum := sha256.Sum256([]byte(token))
706 return hex.EncodeToString(sum[:])
707}
708
709func boolInt(b bool) int {
710 if b {
711 return 1
712 }
713 return 0
714}
715
716var errNotFound = fmt.Errorf("niet gevonden")
717
718// ErrNotFound is het resultaat als een rij ontbreekt.
719func ErrNotFound() error { return errNotFound }
720
721// IsNotFound meldt of err een ontbrekende rij is.
722func IsNotFound(err error) bool { return errors.Is(err, errNotFound) }
723
724func mustJSON(v any) string {
725 b, _ := json.Marshal(v)
726 if b == nil {
727 return "null"
728 }
729 return string(b)
730}
731
732// EncryptSecret versleutelt een geheim met de installatiesleutel.
733func (s *Store) encrypt(plain string) (nonce, ct []byte, err error) {
734 return secretbox.Encrypt(s.key, plain)
735}
736
737func (s *Store) decrypt(nonce, ct []byte) (string, error) {
738 return secretbox.Decrypt(s.key, nonce, ct)
739}