internal/store/adminusers.go
193 lines · 5782 bytes
1package store
2
3import (
4 "database/sql"
5 "encoding/json"
6 "errors"
7 "fmt"
8 "time"
9)
10
11// AdminUser is one account as the instance admin sees it. LastSeen is the
12// most recent authentication by any of the account's SSH keys or API
13// tokens, "" when there has been none.
14type AdminUser struct {
15 Username string
16 IsAdmin bool
17 Pending bool
18 Disabled bool
19 CreatedAt string
20 LastSeen string
21}
22
23const adminUserSelect = `SELECT u.username, u.is_admin, u.pending, u.disabled, u.created_at,
24 COALESCE((SELECT MAX(t) FROM (
25 SELECT last_used_at t FROM ssh_keys WHERE user_id = u.id
26 UNION ALL SELECT last_used_at FROM api_tokens WHERE user_id = u.id)), '')
27 FROM users u`
28
29func scanAdminUser(row interface{ Scan(...any) error }) (AdminUser, error) {
30 var u AdminUser
31 var admin, pending, disabled int
32 err := row.Scan(&u.Username, &admin, &pending, &disabled, &u.CreatedAt, &u.LastSeen)
33 u.IsAdmin = admin != 0
34 u.Pending = pending != 0
35 u.Disabled = disabled != 0
36 return u, err
37}
38
39// ListUsers returns accounts by username. state narrows the set: "" for
40// every account, active (neither pending nor disabled), pending, disabled,
41// or admin. after is the keyset cursor: usernames strictly greater than
42// it, "" from the start. limit 0 means no cap.
43func (s *Store) ListUsers(state string, limit int, after string) ([]AdminUser, error) {
44 where := "WHERE u.username > ?"
45 switch state {
46 case "":
47 case "active":
48 where += " AND u.pending = 0 AND u.disabled = 0"
49 case "pending":
50 where += " AND u.pending = 1"
51 case "disabled":
52 where += " AND u.disabled = 1"
53 case "admin":
54 where += " AND u.is_admin = 1"
55 default:
56 return nil, fmt.Errorf("unknown state %q", state)
57 }
58 q := adminUserSelect + " " + where + " ORDER BY u.username"
59 args := []any{after}
60 if limit > 0 {
61 q += " LIMIT ?"
62 args = append(args, limit)
63 }
64 rows, err := s.DB.Query(q, args...)
65 if err != nil {
66 return nil, err
67 }
68 defer rows.Close()
69 var out []AdminUser
70 for rows.Next() {
71 u, err := scanAdminUser(rows)
72 if err != nil {
73 return nil, err
74 }
75 out = append(out, u)
76 }
77 return out, rows.Err()
78}
79
80// AdminUserByName is the ListUsers row for one account.
81func (s *Store) AdminUserByName(name string) (AdminUser, error) {
82 u, err := scanAdminUser(s.DB.QueryRow(adminUserSelect+" WHERE u.username = ?", name))
83 if errors.Is(err, sql.ErrNoRows) {
84 return u, ErrNotFound
85 }
86 return u, err
87}
88
89// OwnedRepoCount counts repositories the user owns directly, not through
90// an org.
91func (s *Store) OwnedRepoCount(userID int64) (int64, error) {
92 var n int64
93 err := s.DB.QueryRow("SELECT COUNT(*) FROM repos WHERE owner_kind = 'user' AND owner_id = ?", userID).Scan(&n)
94 return n, err
95}
96
97// WebSessionCount counts the user's unexpired browser sessions.
98func (s *Store) WebSessionCount(userID int64) (int64, error) {
99 var n int64
100 err := s.DB.QueryRow("SELECT COUNT(*) FROM web_sessions WHERE user_id = ? AND expires_at > ?",
101 userID, fmtTime(time.Now())).Scan(&n)
102 return n, err
103}
104
105// ErrLastAdmin refuses the demotion that would leave the instance with no
106// admin at all.
107var ErrLastAdmin = errors.New("that is the only instance admin; promote someone else first")
108
109// SetUserAdmin grants or removes instance admin. Removing it from the last
110// admin is refused inside the same transaction that counts them.
111func (s *Store) SetUserAdmin(userID int64, admin bool) error {
112 tx, err := s.DB.Begin()
113 if err != nil {
114 return err
115 }
116 defer tx.Rollback()
117 if !admin {
118 var others int
119 if err := tx.QueryRow("SELECT COUNT(*) FROM users WHERE is_admin = 1 AND id != ?", userID).Scan(&others); err != nil {
120 return err
121 }
122 if others == 0 {
123 return ErrLastAdmin
124 }
125 }
126 res, err := tx.Exec("UPDATE users SET is_admin = ? WHERE id = ?", boolInt(admin), userID)
127 if err != nil {
128 return err
129 }
130 if n, _ := res.RowsAffected(); n == 0 {
131 return ErrNotFound
132 }
133 return tx.Commit()
134}
135
136// AdminRepo is one repository as the instance admin lists it. LastPush is
137// the newest push event, "" when nothing has been pushed.
138type AdminRepo struct {
139 Path string // owner/name, the keyset cursor
140 OwnerName string
141 Name string
142 Visibility string
143 Archived bool
144 CreatedAt string
145 LastPush string
146}
147
148// ListReposAdmin lists repositories across every owner, by path. owner and
149// visibility narrow the set when non-empty; after is the path keyset
150// cursor; limit 0 means no cap.
151func (s *Store) ListReposAdmin(owner, visibility string, limit int, after string) ([]AdminRepo, error) {
152 q := `SELECT COALESCE(u.username, o.name) || '/' || r.name, COALESCE(u.username, o.name), r.name,
153 r.visibility, r.settings_json, r.created_at,
154 COALESCE((SELECT MAX(created_at) FROM events WHERE repo_id = r.id AND kind = 'push'), '')
155 FROM repos r
156 LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
157 LEFT JOIN orgs o ON r.owner_kind = 'org' AND o.id = r.owner_id
158 WHERE COALESCE(u.username, o.name) || '/' || r.name > ?`
159 args := []any{after}
160 if owner != "" {
161 q += " AND COALESCE(u.username, o.name) = ?"
162 args = append(args, owner)
163 }
164 if visibility != "" {
165 q += " AND r.visibility = ?"
166 args = append(args, visibility)
167 }
168 q += " ORDER BY 1"
169 if limit > 0 {
170 q += " LIMIT ?"
171 args = append(args, limit)
172 }
173 rows, err := s.DB.Query(q, args...)
174 if err != nil {
175 return nil, err
176 }
177 defer rows.Close()
178 var out []AdminRepo
179 for rows.Next() {
180 var r AdminRepo
181 var settingsJSON string
182 if err := rows.Scan(&r.Path, &r.OwnerName, &r.Name, &r.Visibility, &settingsJSON, &r.CreatedAt, &r.LastPush); err != nil {
183 return nil, err
184 }
185 var st RepoSettings
186 if err := json.Unmarshal([]byte(settingsJSON), &st); err != nil {
187 return nil, fmt.Errorf("repo %s settings: %w", r.Path, err)
188 }
189 r.Archived = st.Archived
190 out = append(out, r)
191 }
192 return out, rows.Err()
193}