BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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