Send PrAT and GrAT expiry emails to inherited members

Andrew Evansrequested to merge
463016-atevans-prat-expiry-inherited-notifications-implv2 into master

What does this MR do and why?

Send PrAT and GrAT expiry emails to inherited members

Sends notification emails about expiring project and group access tokens to inherited members of the project or group, in addition to direct owners and maintainers of the project or group.

Behind a feature flag initially while we test the setting for specific groups and projects. Also requires the group settings to enable sending to inherited members.

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

Part 4 of 4 MRs for this feature

  1. Instance-wide setting
  2. Group setting
  3. Convert to cascading namespace settings, default off
  4. This MR, with feature implementation and documentation

MR acceptance checklist

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

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

Database Query Changes

Original query on master

Details and visualization

SELECT "users"."id",
       "users"."email",
       "users"."encrypted_password",
       "users"."reset_password_token",
       "users"."reset_password_sent_at",
       "users"."remember_created_at",
       "users"."sign_in_count",
       "users"."current_sign_in_at",
       "users"."last_sign_in_at",
       "users"."current_sign_in_ip",
       "users"."last_sign_in_ip",
       "users"."created_at",
       "users"."updated_at",
       "users"."name",
       "users"."admin",
       "users"."projects_limit",
       "users"."failed_attempts",
       "users"."locked_at",
       "users"."username",
       "users"."can_create_group",
       "users"."can_create_team",
       "users"."state",
       "users"."color_scheme_id",
       "users"."password_expires_at",
       "users"."created_by_id",
       "users"."last_credential_check_at",
       "users"."avatar",
       "users"."confirmation_token",
       "users"."confirmed_at",
       "users"."confirmation_sent_at",
       "users"."unconfirmed_email",
       "users"."hide_no_ssh_key",
       "users"."admin_email_unsubscribed_at",
       "users"."notification_email",
       "users"."hide_no_password",
       "users"."password_automatically_set",
       "users"."encrypted_otp_secret",
       "users"."encrypted_otp_secret_iv",
       "users"."encrypted_otp_secret_salt",
       "users"."otp_required_for_login",
       "users"."otp_backup_codes",
       "users"."public_email",
       "users"."dashboard",
       "users"."project_view",
       "users"."consumed_timestep",
       "users"."layout",
       "users"."hide_project_limit",
       "users"."note",
       "users"."unlock_token",
       "users"."otp_grace_period_started_at",
       "users"."external",
       "users"."incoming_email_token",
       "users"."auditor",
       "users"."require_two_factor_authentication_from_group",
       "users"."two_factor_grace_period",
       "users"."last_activity_on",
       "users"."notified_of_own_activity",
       "users"."preferred_language",
       "users"."theme_id",
       "users"."accepted_term_id",
       "users"."feed_token",
       "users"."private_profile",
       "users"."roadmap_layout",
       "users"."include_private_contributions",
       "users"."commit_email",
       "users"."group_view",
       "users"."managing_group_id",
       "users"."first_name",
       "users"."last_name",
       "users"."static_object_token",
       "users"."role",
       "users"."user_type",
       "users"."static_object_token_encrypted",
       "users"."otp_secret_expires_at",
       "users"."onboarding_in_progress",
       "users"."color_mode_id"
FROM "users"
INNER JOIN "members" ON "users"."id" = "members"."user_id"
WHERE "members"."type" = 'GroupMember'
  AND "members"."source_id" = 33
  AND "members"."source_type" = 'Namespace'
  AND "members"."requested_at" IS NULL
  AND "members"."access_level" = 50
Group owners query

Details and visualization

/* GroupMembersFinder query */
SELECT "members"."id",
       "members"."access_level",
       "members"."source_id",
       "members"."source_type",
       "members"."user_id",
       "members"."notification_level",
       "members"."type",
       "members"."created_at",
       "members"."updated_at",
       "members"."created_by_id",
       "members"."invite_email",
       "members"."invite_token",
       "members"."invite_accepted_at",
       "members"."requested_at",
       "members"."expires_at",
       "members"."ldap",
       "members"."override",
       "members"."state",
       "members"."invite_email_success",
       "members"."member_namespace_id",
       "members"."member_role_id",
       "members"."expiry_notified_at",
       "members"."request_accepted_at"
FROM
  (SELECT DISTINCT ON (user_id,
                       invite_email) *
   FROM "members"
   WHERE "members"."type" = 'GroupMember'
     AND "members"."source_type" = 'Namespace'
     AND "members"."requested_at" IS NULL
     AND "members"."source_id" IN
       (SELECT "namespaces"."id"
        FROM (
                (SELECT "namespaces"."id",
                        "namespaces"."name",
                        "namespaces"."path",
                        "namespaces"."owner_id",
                        "namespaces"."created_at",
                        "namespaces"."updated_at",
                        "namespaces"."type",
                        "namespaces"."description",
                        "namespaces"."avatar",
                        "namespaces"."membership_lock",
                        "namespaces"."share_with_group_lock",
                        "namespaces"."visibility_level",
                        "namespaces"."request_access_enabled",
                        "namespaces"."ldap_sync_status",
                        "namespaces"."ldap_sync_error",
                        "namespaces"."ldap_sync_last_update_at",
                        "namespaces"."ldap_sync_last_successful_update_at",
                        "namespaces"."ldap_sync_last_sync_at",
                        "namespaces"."description_html",
                        "namespaces"."lfs_enabled",
                        "namespaces"."parent_id",
                        "namespaces"."shared_runners_minutes_limit",
                        "namespaces"."repository_size_limit",
                        "namespaces"."require_two_factor_authentication",
                        "namespaces"."two_factor_grace_period",
                        "namespaces"."cached_markdown_version",
                        "namespaces"."project_creation_level",
                        "namespaces"."runners_token",
                        "namespaces"."file_template_project_id",
                        "namespaces"."saml_discovery_token",
                        "namespaces"."runners_token_encrypted",
                        "namespaces"."custom_project_templates_group_id",
                        "namespaces"."auto_devops_enabled",
                        "namespaces"."extra_shared_runners_minutes_limit",
                        "namespaces"."last_ci_minutes_notification_at",
                        "namespaces"."last_ci_minutes_usage_notification_level",
                        "namespaces"."subgroup_creation_level",
                        "namespaces"."max_pages_size",
                        "namespaces"."max_artifacts_size",
                        "namespaces"."mentions_disabled",
                        "namespaces"."default_branch_protection",
                        "namespaces"."max_personal_access_token_lifetime",
                        "namespaces"."push_rule_id",
                        "namespaces"."shared_runners_enabled",
                        "namespaces"."allow_descendants_override_disabled_shared_runners",
                        "namespaces"."traversal_ids",
                        "namespaces"."organization_id"
                 FROM "namespaces"
                 WHERE "namespaces"."type" = 'Group'
                   AND "namespaces"."id" = 33)) namespaces
        WHERE "namespaces"."type" = 'Group')
   ORDER BY user_id,
            invite_email,
            CASE
                WHEN source_id = 33
                     AND source_type = 'Namespace' THEN access_level + 1
                ELSE access_level
            END DESC, member_role_id ASC, expires_at DESC, created_at ASC) members
LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
WHERE "members"."type" = 'GroupMember'
  AND (("members"."user_id" IS NULL
        AND "members"."invite_token" IS NOT NULL)
       OR "users"."state" = 'active')
  AND "members"."requested_at" IS NULL
  AND (members.access_level > 5)
  AND "members"."access_level" IN (50,
                                   60)
  AND "members"."invite_token" IS NULL
  AND (members.access_level > 5)
Project members query

Details and visualization

/* MembersFinder query */
SELECT "members"."id", "members"."access_level", "members"."source_id", "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at", "members"."request_accepted_at" FROM (SELECT DISTINCT ON (user_id, invite_email) member_union.id,COALESCE(project_authorizations.access_level, member_union.access_level) access_level,member_union.source_id,member_union.source_type,member_union.user_id,member_union.notification_level,member_union.type,member_union.created_at,member_union.updated_at,member_union.created_by_id,member_union.invite_email,member_union.invite_token,member_union.invite_accepted_at,member_union.requested_at,member_union.expires_at,member_union.ldap,member_union.override,member_union.state,member_union.invite_email_success,member_union.member_namespace_id,member_union.member_role_id,member_union.expiry_notified_at,member_union.request_accepted_at
FROM ((SELECT "members"."id", "members"."access_level", "members"."source_id", "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at", "members"."request_accepted_at" FROM (SELECT DISTINCT ON (user_id, invite_email) * FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" IN (SELECT "namespaces"."id" FROM ((SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids", "namespaces"."organization_id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 33)) namespaces WHERE "namespaces"."type" = 'Group') ORDER BY user_id, invite_email,
CASE WHEN source_id = 33 and source_type = 'Namespace'
THEN access_level + 1 ELSE access_level END DESC,
member_role_id ASC, expires_at DESC, created_at ASC
) members WHERE "members"."type" = 'GroupMember' AND "members"."invite_token" IS NULL AND (members.access_level > 5))
UNION ALL
(SELECT "members"."id", "members"."access_level", "members"."source_id", "members"."source_type", "members"."user_id", "members"."notification_level", "members"."type", "members"."created_at", "members"."updated_at", "members"."created_by_id", "members"."invite_email", "members"."invite_token", "members"."invite_accepted_at", "members"."requested_at", "members"."expires_at", "members"."ldap", "members"."override", "members"."state", "members"."invite_email_success", "members"."member_namespace_id", "members"."member_role_id", "members"."expiry_notified_at", "members"."request_accepted_at" FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."member_namespace_id" = 34 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL)) AS member_union
LEFT JOIN project_authorizations on project_authorizations.user_id = member_union.user_id
     AND
     project_authorizations.project_id = 7
ORDER BY user_id,
  invite_email,
  CASE
    WHEN type = 'ProjectMember' THEN 1
    WHEN type = 'GroupMember' THEN 2
    ELSE 3
  END
) AS members LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE (("members"."user_id" IS NULL AND "members"."invite_token" IS NOT NULL) OR "users"."state" = 'active') AND "members"."requested_at" IS NULL AND (members.access_level > 5) AND "members"."access_level" IN (50, 40)

How to set up and validate locally

  1. Enable the feature flag for the group or instance-wide: Feature.enable(:pat_expiry_inherited_members_notification, :instance)
  2. Check the instance and group settings to ensure they are not disabled from a previous MR
  3. Create or visit a group with an Ultimate license
  4. Create a sub-group: test-subgroup
  5. Create a project in the sub-group: test-project
  6. Add another member to the top-level group: alice
  7. Create a project access token on test-project expiring in 5 days
  8. Create a group access token on test-group expiring in 5 days
  9. Run the PersonalAccessTokens::ExpiringWorker either via the Rails console ( PersonalAccessTokens::ExpiringWorker.new.perform ) or via selecting Enqueue now in the Sidekiq page: https://gdk.test:3443/admin/sidekiq/cron/personal_access_tokens_expiring_worker
  10. Check letter-opener to ensure the emails are sent to user alice , who is not a direct member of test-subgroup or test-project

Validate settings:

Group setting:

  1. After creating the group hierarchy above, go to settings for test-subgroup : https://gdk.test:3443/groups/test-group/test-subgroup/-/edit
  2. Under Expiry notification emails about group and project access tokens within this group should be sent to: , select All direct and inherited members of the group or project
  3. Hit "Save changes"
  4. Create a new group access token expiring in 7 days or less
  5. Enqueue the cron job
  6. Check letter-opener to see that alice did not receive an email

Parent group setting:

  1. Change the setting back to All direct and inherited members of the group or project for test-subgroup
  2. Change the setting to Only direct members of the group or project for the parent group, test-group
  3. Create a new group access token expiring in 7 days or less
  4. Enqueue the cron job
  5. Check letter-opener to see that alice did not receive an email about the new token

Instance setting:

  1. Change the setting back to All direct and inherited members of the group or project for test-group
  2. Go to Admin ➡️ Settings ➡️ Preferences
  3. Expand the Email section
  4. Under Expiry notification emails about group and project access tokens should be sent to: , select Only direct members of the group or project
  5. Hit "Save changes"
  6. Create a new group access token on group test-subgroup
  7. Enqueue the cron job
  8. Check letter-opener to see that alice did not receive an email about the new token

Related to #463016 (closed)

Edited by Andrew Evans

Merge request reports