BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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