internal/store/adminusers.go

  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// AdminMailAddresses returns where to reach the instance's admins: the
 90// verified primary address of every active admin who has activity mail
 91// on. An admin with no verified primary, or with mail off, is skipped
 92// rather than reported, the same rule ActivityMailAddress applies to
 93// anyone else (#234).
 94func (s *Store) AdminMailAddresses() ([]string, error) {
 95	rows, err := s.DB.Query(`SELECT e.address FROM users u
 96		JOIN emails e ON e.user_id = u.id AND e.is_primary = 1 AND e.verified_at IS NOT NULL
 97		WHERE u.is_admin = 1 AND u.pending = 0 AND u.disabled = 0 AND u.notify_mail != 0
 98		ORDER BY e.address`)
 99	if err != nil {
100		return nil, err
101	}
102	defer rows.Close()
103	var out []string
104	for rows.Next() {
105		var a string
106		if err := rows.Scan(&a); err != nil {
107			return nil, err
108		}
109		out = append(out, a)
110	}
111	return out, rows.Err()
112}
113
114// OwnedRepoCount counts repositories the user owns directly, not through
115// an org.
116func (s *Store) OwnedRepoCount(userID int64) (int64, error) {
117	var n int64
118	err := s.DB.QueryRow("SELECT COUNT(*) FROM repos WHERE owner_kind = 'user' AND owner_id = ?", userID).Scan(&n)
119	return n, err
120}
121
122// WebSessionCount counts the user's unexpired browser sessions.
123func (s *Store) WebSessionCount(userID int64) (int64, error) {
124	var n int64
125	err := s.DB.QueryRow("SELECT COUNT(*) FROM web_sessions WHERE user_id = ? AND expires_at > ?",
126		userID, fmtTime(time.Now())).Scan(&n)
127	return n, err
128}
129
130// ErrLastAdmin refuses the demotion that would leave the instance with no
131// admin at all.
132var ErrLastAdmin = errors.New("that is the only instance admin; promote someone else first")
133
134// SetUserAdmin grants or removes instance admin. Removing it from the last
135// admin is refused inside the same transaction that counts them.
136func (s *Store) SetUserAdmin(userID int64, admin bool) error {
137	tx, err := s.DB.Begin()
138	if err != nil {
139		return err
140	}
141	defer tx.Rollback()
142	if !admin {
143		var others int
144		if err := tx.QueryRow("SELECT COUNT(*) FROM users WHERE is_admin = 1 AND id != ?", userID).Scan(&others); err != nil {
145			return err
146		}
147		if others == 0 {
148			return ErrLastAdmin
149		}
150	}
151	res, err := tx.Exec("UPDATE users SET is_admin = ? WHERE id = ?", boolInt(admin), userID)
152	if err != nil {
153		return err
154	}
155	if n, _ := res.RowsAffected(); n == 0 {
156		return ErrNotFound
157	}
158	return tx.Commit()
159}
160
161// AdminRepo is one repository as the instance admin lists it. LastPush is
162// the newest push event, "" when nothing has been pushed.
163type AdminRepo struct {
164	Path       string // owner/name, the keyset cursor
165	OwnerName  string
166	Name       string
167	Visibility string
168	Archived   bool
169	CreatedAt  string
170	LastPush   string
171}
172
173// ListReposAdmin lists repositories across every owner, by path. owner and
174// visibility narrow the set when non-empty; after is the path keyset
175// cursor; limit 0 means no cap.
176func (s *Store) ListReposAdmin(owner, visibility string, limit int, after string) ([]AdminRepo, error) {
177	q := `SELECT COALESCE(u.username, o.name) || '/' || r.name, COALESCE(u.username, o.name), r.name,
178		r.visibility, r.settings_json, r.created_at,
179		COALESCE((SELECT MAX(created_at) FROM events WHERE repo_id = r.id AND kind = 'push'), '')
180		FROM repos r
181		LEFT JOIN users u ON r.owner_kind = 'user' AND u.id = r.owner_id
182		LEFT JOIN orgs o  ON r.owner_kind = 'org'  AND o.id = r.owner_id
183		WHERE COALESCE(u.username, o.name) || '/' || r.name > ?`
184	args := []any{after}
185	if owner != "" {
186		q += " AND COALESCE(u.username, o.name) = ?"
187		args = append(args, owner)
188	}
189	if visibility != "" {
190		q += " AND r.visibility = ?"
191		args = append(args, visibility)
192	}
193	q += " ORDER BY 1"
194	if limit > 0 {
195		q += " LIMIT ?"
196		args = append(args, limit)
197	}
198	rows, err := s.DB.Query(q, args...)
199	if err != nil {
200		return nil, err
201	}
202	defer rows.Close()
203	var out []AdminRepo
204	for rows.Next() {
205		var r AdminRepo
206		var settingsJSON string
207		if err := rows.Scan(&r.Path, &r.OwnerName, &r.Name, &r.Visibility, &settingsJSON, &r.CreatedAt, &r.LastPush); err != nil {
208			return nil, err
209		}
210		var st RepoSettings
211		if err := json.Unmarshal([]byte(settingsJSON), &st); err != nil {
212			return nil, fmt.Errorf("repo %s settings: %w", r.Path, err)
213		}
214		r.Archived = st.Archived
215		out = append(out, r)
216	}
217	return out, rows.Err()
218}