BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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