Prevent emails to user on expiry of impersonation token
What does this MR do?
As described in #216103 (closed), users with impersonation tokens created by administrators were being informed by email when these tokens expired.
This was due to the ExpiringWorker
running as a cron which would pick up every expiring personal access token (PAT) and inform the user of its upcoming expiry. It was not filtering out PATs where impersonation = true
.
This MR adds the required filtering to exclude impersonation tokens from being used in the query by ExpiringWorker
.
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. - [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Database
When adding the impersonation: false
filter, query time slowed significantly. @tancnle suggested an index which took 8.8 seconds to execute in #database-lab
but reduced the query time back to a speed similar to that before the change.
Query
SELECT
users.*
FROM
users
WHERE (EXISTS (
SELECT
1
FROM
personal_access_tokens
WHERE (personal_access_tokens.user_id = users.id)
AND (personal_access_tokens.impersonation = FALSE)
AND (revoked = FALSE
AND expire_notification_delivered = FALSE
AND expires_at >= CURRENT_DATE
AND expires_at <= '2020-06-02 07:55:43.250570')))
Before (#database-lab)
Time: 6.598 s
- planning: 0.846 ms
- execution: 6.597 s
- I/O read: 6.486 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 362 (~2.80 MiB) from the buffer pool
- reads: 11557 (~90.30 MiB) from the OS file cache, including disk I/O
- dirtied: 45 (~360.00 KiB)
- writes: 0
Index creation took 8.8 seconds (#database-lab)
CREATE INDEX index_expired_and_not_notified_personal_access_tokens ON public.personal_access_tokens USING btree (id, expires_at) WHERE ((impersonation = false) AND (revoked = false) AND (expire_notification_delivered = false));
After (#database-lab)
Time: 269.263 ms
- planning: 0.845 ms
- execution: 268.418 ms
- I/O read: 222.190 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 349 (~2.70 MiB) from the buffer pool
- reads: 9150 (~71.50 MiB) from the OS file cache, including disk I/O
- dirtied: 18 (~144.00 KiB)
- writes: 0
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec
- [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team
Related to #216103 (closed)