BlueGit

timfa bluegit

Files

bluegit / internal / store / store.go

store.go

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