BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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