BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

fix: compacte token- en SSH-knoppen op /settings
timfa project_87923614_bot_3aff574f6f98414b945a8799374f9092 · pushed by timfa · 3 weeks ago
25146e27 History
store.go 27.1 KiB Download Raw
1// Package store bewaart BlueGit in één SQLite-bestand op de datadisk.
2package store
3
4import (
5 "context"
6 "crypto/rand"
7 "crypto/sha256"
8 "database/sql"
9 "encoding/hex"
10 "encoding/json"
11 "errors"
12 "fmt"
13 "strings"
14 "time"
15
16 "gitlab.com/Timfa/bluegit/internal/i18n"
17 "gitlab.com/Timfa/bluegit/internal/names"
18 "gitlab.com/Timfa/bluegit/internal/secretbox"
19
20 _ "modernc.org/sqlite"
21)
22
23// Store is de metadata van deze installatie.
24type Store struct {
25 db *sql.DB
26 key []byte
27}
28
29// Open opent of maakt het databasebestand. key is 32 bytes voor geheimen.
30func Open(path string, key []byte) (*Store, error) {
31 if len(key) != 32 {
32 return nil, fmt.Errorf("geheime sleutel moet 32 bytes zijn")
33 }
34 dsn := "file:" + path + "?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)"
35 db, err := sql.Open("sqlite", dsn)
36 if err != nil {
37 return nil, err
38 }
39 db.SetMaxOpenConns(8)
40 s := &Store{db: db, key: key}
41 if _, err := db.Exec(`PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`); err != nil {
42 _ = db.Close()
43 return nil, err
44 }
45 if err := s.migrate(); err != nil {
46 _ = db.Close()
47 return nil, err
48 }
49 return s, nil
50}
51
52// Close sluit de database.
53func (s *Store) Close() error { return s.db.Close() }
54
55func (s *Store) migrate() error {
56 if _, err := s.db.Exec(schema); err != nil {
57 return err
58 }
59 alters := []string{
60 `ALTER TABLE users ADD COLUMN language TEXT NOT NULL DEFAULT 'en'`,
61 `ALTER TABLE groups ADD COLUMN visibility TEXT NOT NULL DEFAULT 'private'`,
62 `ALTER TABLE groups ADD COLUMN default_role TEXT NOT NULL DEFAULT 'developer'`,
63 `ALTER TABLE groups ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
64 `ALTER TABLE repos ADD COLUMN logo_mime TEXT NOT NULL DEFAULT ''`,
65 `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 ExpiresAt time.Time
648}
649
650// CreateToken maakt een bg_-token. De tekst wordt één keer teruggegeven.
651func (s *Store) CreateToken(ctx context.Context, userID int64, name string) (string, error) {
652 name = strings.TrimSpace(name)
653 if name == "" || len(name) > 80 {
654 return "", i18n.E("err.tokenNeedsName")
655 }
656 plain, err := newToken("bg_")
657 if err != nil {
658 return "", err
659 }
660 _, err = s.db.ExecContext(ctx, `INSERT INTO access_tokens (user_id, name, token_hash, prefix, created_at) VALUES (?, ?, ?, ?, ?)`,
661 userID, name, hashToken(plain), plain[:10], now())
662 if err != nil {
663 return "", err
664 }
665 return plain, nil
666}
667
668// ListTokens toont de tokens van een gebruiker.
669func (s *Store) ListTokens(ctx context.Context, userID int64) ([]TokenInfo, error) {
670 rows, err := s.db.QueryContext(ctx, `SELECT id, name, prefix, created_at, COALESCE(last_used_at, ''), COALESCE(expires_at, '') FROM access_tokens WHERE user_id = ? ORDER BY id DESC`, userID)
671 if err != nil {
672 return nil, err
673 }
674 defer rows.Close()
675 var out []TokenInfo
676 for rows.Next() {
677 var t TokenInfo
678 var created, used, expires string
679 if err := rows.Scan(&t.ID, &t.Name, &t.Prefix, &created, &used, &expires); err != nil {
680 return nil, err
681 }
682 t.CreatedAt = parseTime(created)
683 t.LastUsedAt = parseTime(used)
684 t.ExpiresAt = parseTime(expires)
685 out = append(out, t)
686 }
687 return out, rows.Err()
688}
689
690// DeleteToken verwijdert een token van deze gebruiker.
691func (s *Store) DeleteToken(ctx context.Context, userID, id int64) error {
692 res, err := s.db.ExecContext(ctx, `DELETE FROM access_tokens WHERE id = ? AND user_id = ?`, id, userID)
693 if err != nil {
694 return err
695 }
696 n, _ := res.RowsAffected()
697 if n == 0 {
698 return errNotFound
699 }
700 return nil
701}
702
703// UserByToken zoekt de eigenaar van een persoonlijk token.
704func (s *Store) UserByToken(ctx context.Context, token string) (User, error) {
705 if !strings.HasPrefix(token, "bg_") {
706 return User{}, errNotFound
707 }
708 var userID, tokenID int64
709 err := s.db.QueryRowContext(ctx, `SELECT id, user_id FROM access_tokens WHERE token_hash = ?`, hashToken(token)).Scan(&tokenID, &userID)
710 if errors.Is(err, sql.ErrNoRows) {
711 return User{}, errNotFound
712 }
713 if err != nil {
714 return User{}, err
715 }
716 _, _ = s.db.ExecContext(ctx, `UPDATE access_tokens SET last_used_at = ? WHERE id = ?`, now(), tokenID)
717 return s.UserByID(ctx, userID)
718}
719
720// SSHKey is een publieke sleutel voor git-over-SSH.
721type SSHKey struct {
722 ID int64
723 UserID int64
724 Name string
725 PublicKey string
726 Fingerprint string
727 CreatedAt time.Time
728}
729
730// AddSSHKey bewaart één OpenSSH-regel.
731func (s *Store) AddSSHKey(ctx context.Context, userID int64, name, line, fingerprint string) error {
732 name = strings.TrimSpace(name)
733 if name == "" {
734 name = "sleutel"
735 }
736 _, err := s.db.ExecContext(ctx, `INSERT INTO ssh_keys (user_id, name, public_key, fingerprint, created_at) VALUES (?, ?, ?, ?, ?)`,
737 userID, name, strings.TrimSpace(line), fingerprint, now())
738 if err != nil && strings.Contains(err.Error(), "UNIQUE") {
739 return i18n.E("err.sshDup")
740 }
741 return err
742}
743
744// ListSSHKeys toont de sleutels van een gebruiker.
745func (s *Store) ListSSHKeys(ctx context.Context, userID int64) ([]SSHKey, error) {
746 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)
747 if err != nil {
748 return nil, err
749 }
750 defer rows.Close()
751 return scanKeys(rows)
752}
753
754// SSHKeyByFingerprint zoekt een sleutel voor de SSH-server.
755func (s *Store) SSHKeyByFingerprint(ctx context.Context, fingerprint string) (SSHKey, error) {
756 row := s.db.QueryRowContext(ctx, `SELECT id, user_id, name, public_key, fingerprint, created_at FROM ssh_keys WHERE fingerprint = ?`, fingerprint)
757 var k SSHKey
758 var created string
759 if err := row.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
760 if errors.Is(err, sql.ErrNoRows) {
761 return SSHKey{}, errNotFound
762 }
763 return SSHKey{}, err
764 }
765 k.CreatedAt = parseTime(created)
766 return k, nil
767}
768
769// DeleteSSHKey verwijdert een sleutel van deze gebruiker.
770func (s *Store) DeleteSSHKey(ctx context.Context, userID, id int64) error {
771 res, err := s.db.ExecContext(ctx, `DELETE FROM ssh_keys WHERE id = ? AND user_id = ?`, id, userID)
772 if err != nil {
773 return err
774 }
775 n, _ := res.RowsAffected()
776 if n == 0 {
777 return errNotFound
778 }
779 return nil
780}
781
782func scanKeys(rows *sql.Rows) ([]SSHKey, error) {
783 var out []SSHKey
784 for rows.Next() {
785 var k SSHKey
786 var created string
787 if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.PublicKey, &k.Fingerprint, &created); err != nil {
788 return nil, err
789 }
790 k.CreatedAt = parseTime(created)
791 out = append(out, k)
792 }
793 return out, rows.Err()
794}
795
796func randomID() (string, error) {
797 b := make([]byte, 32)
798 if _, err := rand.Read(b); err != nil {
799 return "", err
800 }
801 return hex.EncodeToString(b), nil
802}
803
804func newToken(prefix string) (string, error) {
805 b := make([]byte, 32)
806 if _, err := rand.Read(b); err != nil {
807 return "", err
808 }
809 return prefix + hex.EncodeToString(b), nil
810}
811
812func hashToken(token string) string {
813 sum := sha256.Sum256([]byte(token))
814 return hex.EncodeToString(sum[:])
815}
816
817func boolInt(b bool) int {
818 if b {
819 return 1
820 }
821 return 0
822}
823
824var errNotFound = fmt.Errorf("niet gevonden")
825
826// ErrNotFound is het resultaat als een rij ontbreekt.
827func ErrNotFound() error { return errNotFound }
828
829// IsNotFound meldt of err een ontbrekende rij is.
830func IsNotFound(err error) bool { return errors.Is(err, errNotFound) }
831
832func mustJSON(v any) string {
833 b, _ := json.Marshal(v)
834 if b == nil {
835 return "null"
836 }
837 return string(b)
838}
839
840// EncryptSecret versleutelt een geheim met de installatiesleutel.
841func (s *Store) encrypt(plain string) (nonce, ct []byte, err error) {
842 return secretbox.Encrypt(s.key, plain)
843}
844
845func (s *Store) decrypt(nonce, ct []byte) (string, error) {
846 return secretbox.Decrypt(s.key, nonce, ct)
847}