Skip to content

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

Availability and Testing

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)

Edited by Mayra Cabrera

Merge request reports

Loading