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