Remove non-human created tokens from PAT list
What does this MR do and why?
- Removes personal access tokens that belong to non-human users from the credentials inventory.
- When a project access token is created, it actually creates a new user object, as well as a personal access token for that user object.
- This caused those access tokens to appear in the credentials inventory which meant that they could be revoked without the associated user object being destroyed too.
- It was also confusing as these tokens, whilst being saved as personal access tokens in the database, are not reffered to as personal access tokens in our documentation but instead as "project access tokens".
Database Review
- Adds a new parameter to the PAT finder. Generates a new query:
SELECT "personal_access_tokens"."id" AS t0_r0,
"personal_access_tokens"."user_id" AS t0_r1,
"personal_access_tokens"."name" AS t0_r2,
"personal_access_tokens"."revoked" AS t0_r3,
"personal_access_tokens"."expires_at" AS t0_r4,
"personal_access_tokens"."created_at" AS t0_r5,
"personal_access_tokens"."updated_at" AS t0_r6,
"personal_access_tokens"."scopes" AS t0_r7,
"personal_access_tokens"."impersonation" AS t0_r8,
"personal_access_tokens"."token_digest" AS t0_r9,
"personal_access_tokens"."expire_notification_delivered" AS t0_r10,
"personal_access_tokens"."last_used_at" AS t0_r11,
"personal_access_tokens"."after_expiry_notification_delivered" AS t0_r12,
"user"."id" AS t1_r0,
"user"."email" AS t1_r1,
"user"."encrypted_password" AS t1_r2,
"user"."reset_password_token" AS t1_r3,
"user"."reset_password_sent_at" AS t1_r4,
"user"."remember_created_at" AS t1_r5,
"user"."sign_in_count" AS t1_r6,
"user"."current_sign_in_at" AS t1_r7,
"user"."last_sign_in_at" AS t1_r8,
"user"."current_sign_in_ip" AS t1_r9,
"user"."last_sign_in_ip" AS t1_r10,
"user"."created_at" AS t1_r11,
"user"."updated_at" AS t1_r12,
"user"."name" AS t1_r13,
"user"."admin" AS t1_r14,
"user"."projects_limit" AS t1_r15,
"user"."skype" AS t1_r16,
"user"."linkedin" AS t1_r17,
"user"."twitter" AS t1_r18,
"user"."failed_attempts" AS t1_r19,
"user"."locked_at" AS t1_r20,
"user"."username" AS t1_r21,
"user"."can_create_group" AS t1_r22,
"user"."can_create_team" AS t1_r23,
"user"."state" AS t1_r24,
"user"."color_scheme_id" AS t1_r25,
"user"."password_expires_at" AS t1_r26,
"user"."created_by_id" AS t1_r27,
"user"."last_credential_check_at" AS t1_r28,
"user"."avatar" AS t1_r29,
"user"."confirmation_token" AS t1_r30,
"user"."confirmed_at" AS t1_r31,
"user"."confirmation_sent_at" AS t1_r32,
"user"."unconfirmed_email" AS t1_r33,
"user"."hide_no_ssh_key" AS t1_r34,
"user"."website_url" AS t1_r35,
"user"."admin_email_unsubscribed_at" AS t1_r36,
"user"."notification_email" AS t1_r37,
"user"."hide_no_password" AS t1_r38,
"user"."password_automatically_set" AS t1_r39,
"user"."location" AS t1_r40,
"user"."encrypted_otp_secret" AS t1_r41,
"user"."encrypted_otp_secret_iv" AS t1_r42,
"user"."encrypted_otp_secret_salt" AS t1_r43,
"user"."otp_required_for_login" AS t1_r44,
"user"."otp_backup_codes" AS t1_r45,
"user"."public_email" AS t1_r46,
"user"."dashboard" AS t1_r47,
"user"."project_view" AS t1_r48,
"user"."consumed_timestep" AS t1_r49,
"user"."layout" AS t1_r50,
"user"."hide_project_limit" AS t1_r51,
"user"."note" AS t1_r52,
"user"."unlock_token" AS t1_r53,
"user"."otp_grace_period_started_at" AS t1_r54,
"user"."external" AS t1_r55,
"user"."incoming_email_token" AS t1_r56,
"user"."organization" AS t1_r57,
"user"."auditor" AS t1_r58,
"user"."require_two_factor_authentication_from_group" AS t1_r59,
"user"."two_factor_grace_period" AS t1_r60,
"user"."last_activity_on" AS t1_r61,
"user"."notified_of_own_activity" AS t1_r62,
"user"."preferred_language" AS t1_r63,
"user"."email_opted_in" AS t1_r64,
"user"."email_opted_in_ip" AS t1_r65,
"user"."email_opted_in_source_id" AS t1_r66,
"user"."email_opted_in_at" AS t1_r67,
"user"."theme_id" AS t1_r68,
"user"."accepted_term_id" AS t1_r69,
"user"."feed_token" AS t1_r70,
"user"."private_profile" AS t1_r71,
"user"."roadmap_layout" AS t1_r72,
"user"."include_private_contributions" AS t1_r73,
"user"."commit_email" AS t1_r74,
"user"."group_view" AS t1_r75,
"user"."managing_group_id" AS t1_r76,
"user"."first_name" AS t1_r77,
"user"."last_name" AS t1_r78,
"user"."static_object_token" AS t1_r79,
"user"."role" AS t1_r80,
"user"."user_type" AS t1_r81,
"user"."static_object_token_encrypted" AS t1_r82
FROM "personal_access_tokens"
LEFT OUTER JOIN "users" "user"
ON "user"."id" = "personal_access_tokens"."user_id"
WHERE "personal_access_tokens"."impersonation" = false
AND "user"."user_type" IS NULL
ORDER BY "personal_access_tokens"."id" DESC
LIMIT 21 offset 0 Query Plan
Limit (cost=0.86..17.43 rows=21 width=1544) (actual time=6.027..70.703 rows=21 loops=1)
Buffers: shared hit=39 read=84 dirtied=35
I/O Timings: read=68.284 write=0.000
-> Nested Loop Left Join (cost=0.86..3277732.24 rows=4154500 width=1544) (actual time=6.025..70.688 rows=21 loops=1)
Filter: ("user".user_type IS NULL)
Rows Removed by Filter: 1
Buffers: shared hit=39 read=84 dirtied=35
I/O Timings: read=68.284 write=0.000
-> Index Scan using personal_access_tokens_pkey on public.personal_access_tokens (cost=0.43..287527.88 rows=4169217 width=131) (actual time=3.082..26.087 rows=22 loops=1)
Filter: (NOT personal_access_tokens.impersonation)
Rows Removed by Filter: 0
Buffers: shared hit=3 read=32 dirtied=29
I/O Timings: read=24.398 write=0.000
-> Index Scan using users_pkey on public.users user (cost=0.43..0.71 rows=1 width=1413) (actual time=2.021..2.021 rows=1 loops=22)
Index Cond: ("user".id = personal_access_tokens.user_id)
Buffers: shared hit=36 read=52 dirtied=6
I/O Timings: read=43.886 write=0.000Timings
Time: 77.602 ms
- planning: 6.744 ms
- execution: 70.858 ms
- I/O read: 68.284 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 39 (~312.00 KiB) from the buffer pool
- reads: 84 (~672.00 KiB) from the OS file cache, including disk I/O
- dirtied: 35 (~280.00 KiB)
- writes: 0 How to set up and validate locally
- Create a bunch of project access tokens.
- Create a bunch of personal access tokens.
- In the admin credentials inventory, check that the correct tokens appear under the correct tab section.
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #351759 (closed) Closes #353442 (closed)
Edited by Max Woolf