BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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