BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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