Set group_id and user_type for resource access tokens

What does this MR do and why?

Set group_id and user_type for resource access tokens

Sets group_id and user_type on PersonalAccessToken when created or rotated for a group or project access token. Also adds a migration to backfill these values for existing group and project access tokens.

This is needed for performance optimization on the Credentials Inventory for large groups with many personal access tokens. See #558112 (closed) and !201312 (merged) .

References

• Set group_id and user_type for human users' per... (#558105 - closed)
• Optimize retrieving personal and resource acces... (#558112 - closed)
• !201312 (merged)
• !201921 (merged)

Database

SQL query for batched background migration UPDATE

Largely cribbed from !201921 (merged) which already has database approval. Uses traversal_ids to ensure we get root namespaces for group_id .

Note: for the EXPLAIN, I substituted select id from personal_access_tokens limit 1000 for the sub-batch list of ids. If this is not okay because it might cause different query plans, please let me know and I can update the query here.

              UPDATE personal_access_tokens
              SET user_type=users.user_type, group_id=(CASE
                  WHEN root_namespace.type = 'Group' THEN root_namespace.id
                  ELSE personal_access_tokens.group_id
                END)
              FROM
                users
                LEFT JOIN user_details ON user_details.user_id=users.id
                LEFT JOIN namespaces bot_namespace ON bot_namespace.id=user_details.bot_namespace_id
                LEFT JOIN namespaces root_namespace ON root_namespace.id=bot_namespace.traversal_ids[1]
              WHERE
                personal_access_tokens.id IN (#{sub_batch.select(:id).limit(sub_batch_size).to_sql})
                AND personal_access_tokens.user_type IS NULL
                AND personal_access_tokens.user_id=users.id
                AND users.user_type = 6

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/43172/commands/131991

Screenshots or screen recordings

Before	After

How to set up and validate locally

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #558106 (closed)