Fix cross database join in fetch_policy_approvers_service
What does this MR do and why?
This fixes a cross database join between users
and members
by splitting the query up in two queries
Database
1st Query `container.authorizable_members_with_parents.pluck(:user_id)`
SELECT
"members"."user_id"
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"
FROM
"members"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" = 9970
)
UNION
(
WITH "group_group_links_cte" AS MATERIALIZED (
SELECT
"group_group_links".*
FROM
"group_group_links"
WHERE
"group_group_links"."shared_group_id" = 9970
)
SELECT
"members"."id",
LEAST(
"group_group_links"."group_access",
"members"."access_level"
) AS 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"
FROM
"members",
"group_group_links_cte" AS "group_group_links"
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" = "group_group_links"."shared_with_group_id"
AND "members"."source_type" = 'Namespace'
AND "members"."state" = 0
AND (members.access_level > 5)
)
) members
WHERE
"members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."user_id" IS NOT NULL
AND "members"."state" = 0
AND "members"."requested_at" IS NULL
AND (members.access_level > 5)
2nd Query `User.by_ids_or_usernames(user_ids, user_names).where(id: ...)`
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"
FROM
"users"
WHERE
(
"users"."username" = 'Andysoiron'
OR "users"."id" = 16943
)
AND "users"."id" = 16943
How to set up and validate locally
Copied from !113114 (merged)
- Create a new group
- Add members to the group and note their IDs
- Create a project within the group
- Add a scan result policy to the project that lists the user IDs as
user_approvers
, e.g.:
type: scan_result_policy
name: foobar
description: ''
enabled: true
rules:
- type: license_finding
branches: []
match_on_inclusion: true
license_types:
- MIT License
license_states:
- newly_detected
actions:
- type: require_approval
approvals_required: 1
user_approvers_ids:
- 16
- 14
- 15
- Navigate to the policy list, click on the newly created policy and verify the sidebar drawer correctly lists the users.
- Click "Edit policy" and verify the approver list correctly lists the users.
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 #417460 (closed)
Edited by Andy Schoenen