internal/store/accountdelete.go
215 lines · 7436 bytes
1package store
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "time"
8)
9
10// ErrGhostNameTaken is returned when a real account holds the name the
11// ghost needs.
12var ErrGhostNameTaken = errors.New(`an account named "ghost" exists and is not the ghost; rename it before an account can be deleted`)
13
14// RequestAccountDeletion records a deletion request waiting on its mailed
15// link. A second request replaces the first.
16func (s *Store) RequestAccountDeletion(userID int64, tokenHash string, ttl time.Duration) error {
17 _, err := s.DB.Exec(`INSERT INTO account_deletions (user_id, token_hash, expires_at) VALUES (?, ?, ?)
18 ON CONFLICT (user_id) DO UPDATE SET token_hash = excluded.token_hash,
19 created_at = strftime('%Y-%m-%dT%H:%M:%fZ','now'), expires_at = excluded.expires_at`,
20 userID, tokenHash, fmtTime(time.Now().Add(ttl)))
21 return err
22}
23
24// AccountDeletionUser is the account an unexpired deletion link names.
25func (s *Store) AccountDeletionUser(tokenHash string) (User, error) {
26 var userID int64
27 err := s.DB.QueryRow("SELECT user_id FROM account_deletions WHERE token_hash = ? AND expires_at > ?",
28 tokenHash, fmtTime(time.Now())).Scan(&userID)
29 if errors.Is(err, sql.ErrNoRows) {
30 return User{}, ErrNotFound
31 }
32 if err != nil {
33 return User{}, err
34 }
35 return s.UserByID(userID)
36}
37
38// ConfirmAccountDeletion consumes the link and schedules the purge at
39// after: the account is disabled, its web sessions and login links end,
40// and its open connections close. API tokens stay, refused while the
41// account is disabled, so a cancelled deletion leaves them working.
42func (s *Store) ConfirmAccountDeletion(tokenHash string, after time.Time) (User, error) {
43 u, err := s.AccountDeletionUser(tokenHash)
44 if err != nil {
45 return User{}, err
46 }
47 // A suspension is an admin's decision; the link cannot turn it into
48 // a schedule its owner could then cancel by signing in.
49 if u.Disabled {
50 return User{}, ErrNotFound
51 }
52 tx, err := s.DB.Begin()
53 if err != nil {
54 return User{}, err
55 }
56 defer tx.Rollback()
57 if _, err := tx.Exec("DELETE FROM account_deletions WHERE user_id = ?", u.ID); err != nil {
58 return User{}, err
59 }
60 if _, err := tx.Exec("UPDATE users SET disabled = 1, delete_after = ? WHERE id = ? AND disabled = 0", fmtTime(after), u.ID); err != nil {
61 return User{}, err
62 }
63 for _, table := range []string{"web_sessions", "login_tokens"} {
64 if _, err := tx.Exec("DELETE FROM "+table+" WHERE user_id = ?", u.ID); err != nil {
65 return User{}, err
66 }
67 }
68 if err := tx.Commit(); err != nil {
69 return User{}, err
70 }
71 s.announce(Revoked{UserID: u.ID})
72 u.Disabled, u.DeleteAfter = true, fmtTime(after)
73 return u, nil
74}
75
76// Purging marks users.delete_after while the purge runs. It sorts after
77// every timestamp, so nothing cancels it, and DueDeletions returns it
78// again until the purge completes.
79const Purging = "purging"
80
81// ClaimDeletion marks a due account as being purged. It reports false
82// when a cancel or an admin got there first.
83func (s *Store) ClaimDeletion(userID int64, now time.Time) (bool, error) {
84 res, err := s.DB.Exec(`UPDATE users SET delete_after = ? WHERE id = ? AND disabled = 1
85 AND delete_after IS NOT NULL AND (delete_after <= ? OR delete_after = ?)`, Purging, userID, fmtTime(now), Purging)
86 if err != nil {
87 return false, err
88 }
89 n, _ := res.RowsAffected()
90 return n == 1, nil
91}
92
93// CancelAccountDeletion drops a waiting request and a scheduled purge.
94// It reports whether either existed.
95func (s *Store) CancelAccountDeletion(userID int64) (bool, error) {
96 res, err := s.DB.Exec("DELETE FROM account_deletions WHERE user_id = ?", userID)
97 if err != nil {
98 return false, err
99 }
100 requested, _ := res.RowsAffected()
101 res, err = s.DB.Exec("UPDATE users SET disabled = 0, delete_after = NULL WHERE id = ? AND delete_after IS NOT NULL AND delete_after != ?", userID, Purging)
102 if err != nil {
103 return false, err
104 }
105 scheduled, _ := res.RowsAffected()
106 return requested+scheduled > 0, nil
107}
108
109// DueDeletions lists the accounts whose scheduled purge time has passed.
110func (s *Store) DueDeletions(now time.Time) ([]User, error) {
111 rows, err := s.DB.Query("SELECT id FROM users WHERE delete_after IS NOT NULL AND (delete_after <= ? OR delete_after = ?)", fmtTime(now), Purging)
112 if err != nil {
113 return nil, err
114 }
115 ids, err := scanIDs(rows)
116 if err != nil {
117 return nil, err
118 }
119 var out []User
120 for _, id := range ids {
121 u, err := s.UserByID(id)
122 if err != nil {
123 return nil, err
124 }
125 out = append(out, u)
126 }
127 return out, nil
128}
129
130// SoleAdminOrgs names the organizations where the user is the only admin.
131func (s *Store) SoleAdminOrgs(userID int64) ([]string, error) {
132 rows, err := s.DB.Query(`SELECT o.name FROM orgs o JOIN org_members m ON m.org_id = o.id
133 WHERE m.user_id = ? AND m.role = 'admin'
134 AND NOT EXISTS (SELECT 1 FROM org_members x
135 WHERE x.org_id = o.id AND x.role = 'admin' AND x.user_id != m.user_id
136 AND x.user_id NOT IN (SELECT id FROM users WHERE ghost = 1))
137 ORDER BY o.name`, userID)
138 if err != nil {
139 return nil, err
140 }
141 defer rows.Close()
142 var names []string
143 for rows.Next() {
144 var n string
145 if err := rows.Scan(&n); err != nil {
146 return nil, err
147 }
148 names = append(names, n)
149 }
150 return names, rows.Err()
151}
152
153// OtherActiveAdmins counts instance admins other than the user who can
154// still act.
155func (s *Store) OtherActiveAdmins(userID int64) (int64, error) {
156 var n int64
157 err := s.DB.QueryRow(`SELECT COUNT(*) FROM users WHERE is_admin = 1 AND disabled = 0
158 AND pending = 0 AND id != ?`, userID).Scan(&n)
159 return n, err
160}
161
162// EnsureGhost returns the ghost account's id, creating it on first use.
163// It is disabled and holds no credentials, so nothing can act as it.
164func (s *Store) EnsureGhost() (int64, error) {
165 var id int64
166 err := s.DB.QueryRow("SELECT id FROM users WHERE ghost = 1").Scan(&id)
167 if err == nil {
168 return id, nil
169 }
170 if !errors.Is(err, sql.ErrNoRows) {
171 return 0, err
172 }
173 if _, err := s.UserByUsername("ghost"); err == nil {
174 return 0, ErrGhostNameTaken
175 }
176 res, err := s.DB.Exec(`INSERT INTO users (username, is_admin, disabled, ghost, description)
177 VALUES ('ghost', 0, 1, 1, 'This account stands in for deleted users.')`)
178 if err != nil {
179 return 0, err
180 }
181 return res.LastInsertId()
182}
183
184// ReassignToGhost moves what the user wrote on other owners' repositories
185// to the ghost: issues, merge requests, comments, diff comments and
186// reviews, the rows DeleteUser otherwise refuses over. Pending draft
187// comments are deleted and reviews made stale.
188func (s *Store) ReassignToGhost(userID, ghostID int64) error {
189 tx, err := s.DB.Begin()
190 if err != nil {
191 return err
192 }
193 defer tx.Rollback()
194 // Unsubmitted drafts go; reviews stay as text but no longer count
195 // toward a merge gate.
196 if _, err := tx.Exec("DELETE FROM mr_diff_comments WHERE author_id = ? AND pending = 1", userID); err != nil {
197 return err
198 }
199 if _, err := tx.Exec("UPDATE mr_reviews SET stale = 1 WHERE reviewer_id = ?", userID); err != nil {
200 return err
201 }
202 for _, col := range []struct{ table, column string }{
203 {"issues", "author_id"},
204 {"merge_requests", "author_id"},
205 {"issue_comments", "author_id"},
206 {"mr_comments", "author_id"},
207 {"mr_diff_comments", "author_id"},
208 {"mr_reviews", "reviewer_id"},
209 } {
210 if _, err := tx.Exec(fmt.Sprintf("UPDATE %s SET %s = ? WHERE %s = ?", col.table, col.column, col.column), ghostID, userID); err != nil {
211 return fmt.Errorf("reassigning %s: %w", col.table, err)
212 }
213 }
214 return tx.Commit()
215}