Resolve: "PAT expiry policy for GMA groups"

What does this MR do?

Relates to #118893 (closed)

We have already implemented PAT expiry policy (!17344 (merged)) at the instance level.

But, right now this policy also works also for PATs of users in a group managed account.

Since we are planning on building PAT expiry setting specifically for groups that support managed accounts with #118893 (closed), instance level PAT expiry settings should not affect any users in a group managed account and so as first step towards implementing #118893 (closed), this MR is aimed at making this change.

Note: I am not making any changes to the "notify about PAT about to expire" implemented in !19296 (merged), because it is not concerned with the instance level PAT expiry setting (it just sends email 7 days before a PAT expiry, and it can be re-used as it is even for group managed users)

Next step: Allow GMA groups to specify their own PAT expiry rule: !27055 (merged)

Query plans

User.not_managed.with_invalid_expires_at_tokens(expiration_date)

Raw SQL"

 SELECT "users".* FROM "users" WHERE "users"."managing_group_id" IS NULL AND "users"."id" IN 
(SELECT "personal_access_tokens"."user_id" FROM ((SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."revoked" = FALSE AND "personal_access_tokens"."expires_at" IS NULL LIMIT 1000)
UNION
(SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."revoked" = FALSE AND (expires_at > '2020-03-07 07:15:16.477987') LIMIT 1000)) 
personal_access_tokens)

Plan:

Nested Loop  (cost=1804.12..2695.69 rows=2636552 width=1678) (actual time=10.886..24.210 rows=1656 loops=1)
   Buffers: shared hit=9505
   ->  HashAggregate  (cost=1803.69..1805.69 rows=200 width=4) (actual time=10.856..11.478 rows=1656 loops=1)
         Group Key: personal_access_tokens.user_id
         Buffers: shared hit=2868
         ->  HashAggregate  (cost=1758.69..1778.69 rows=2000 width=127) (actual time=9.283..10.012 rows=2000 loops=1)
               Group Key: personal_access_tokens.id, personal_access_tokens.user_id, personal_access_tokens.name, personal_access_tokens.revoked, personal_access_tokens.expires_at, personal_access_tokens.created_at, personal_access_tokens.updated_at, personal_access_tokens.scopes, personal_access_tokens.impersonation, personal_access_tokens.token_digest, personal_access_tokens.expire_notification_delivered
               Buffers: shared hit=2868
               ->  Append  (cost=0.43..1703.69 rows=2000 width=127) (actual time=0.029..7.091 rows=2000 loops=1)
                     Buffers: shared hit=2868
                     ->  Limit  (cost=0.43..117.49 rows=1000 width=109) (actual time=0.029..1.964 rows=1000 loops=1)
                           Buffers: shared hit=1072
                           ->  Index Scan using index_pat_on_user_id_and_expires_at on public.personal_access_tokens  (cost=0.43..223763.24 rows=1911560 width=109) (actual time=0.028..1.813 rows=1000 loops=1)
                                 Index Cond: (personal_access_tokens.expires_at IS NULL)
                                 Filter: (NOT personal_access_tokens.revoked)
                                 Rows Removed by Filter: 128
                                 Buffers: shared hit=1072
                     ->  Limit  (cost=0.43..1566.20 rows=1000 width=109) (actual time=0.009..4.867 rows=1000 loops=1)
                           Buffers: shared hit=1796
                           ->  Index Scan using index_pat_on_user_id_and_expires_at on public.personal_access_tokens personal_access_tokens_1  (cost=0.43..181817.33 rows=116120 width=109) (actual time=0.009..4.746 rows=1000 loops=1)
                                 Index Cond: (personal_access_tokens_1.expires_at > '2020-03-07'::date)
                                 Filter: (NOT personal_access_tokens_1.revoked)
                                 Rows Removed by Filter: 417
                                 Buffers: shared hit=1796
   ->  Index Scan using users_pkey on public.users  (cost=0.43..4.44 rows=1 width=1678) (actual time=0.006..0.007 rows=1 loops=1656)
         Index Cond: (users.id = personal_access_tokens.user_id)
         Filter: (users.managing_group_id IS NULL)
         Rows Removed by Filter: 0
         Buffers: shared hit=6637

Screenshots

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

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