Make group credentials inv work with resource access tokens
What does this MR do and why?
Related to #297441 (closed)
Fixes what should be the last remaining item in order to [Feature flag] Rollout of `group_credentials_in... (#485659)
This change adds both group and project bots to the credentials inventory users query so that group and project access tokens can be filtering alongside PATs and SSH Keys.
Database
Queries are largely similar to existing ones we have for group members. This just replicates them for project members. If there's a better way to accomplish this, I'm open to suggestions.
Query 1
ProjectMember
.with_source_id(all_projects)
.active_without_invites_and_requests
Database lab query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31679/commands/98175
SELECT
members.*
FROM
members
LEFT JOIN users ON users.id = members.user_id
WHERE
members.type = 'ProjectMember' AND
members.source_type = 'Project' AND
members.source_id IN (
SELECT
projects.id
FROM
projects
WHERE
projects.namespace_id IN (
SELECT
namespaces.traversal_ids[array_length(
namespaces.traversal_ids,
1
)] AS id
FROM
namespaces
WHERE
namespaces.type = 'Group' AND
traversal_ids @> '{9970}'
)
) AND
users.state = 'active' AND
members.state = 0 AND
members.requested_at IS NULL AND
members.invite_token IS NULL AND
members.access_level > 5;
-- Formatted by Pg::SQL::PrettyPrinter
Query 2
User
.where(id: descendant_project_members.select(:user_id))
.reorder(nil)
Database lab query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31679/commands/98174
SELECT
users.*
FROM
users
WHERE
users.id IN (
SELECT
members.user_id
FROM
members
LEFT JOIN users ON users.id = members.user_id
WHERE
members.type = 'ProjectMember' AND
members.source_type = 'Project' AND
members.source_id IN (
SELECT
projects.id
FROM
projects
WHERE
projects.namespace_id IN (
SELECT
namespaces.traversal_ids[array_length(
namespaces.traversal_ids,
1
)] AS id
FROM
namespaces
WHERE
namespaces.type = 'Group' AND
traversal_ids @> '{9970}'
)
) AND
users.state = 'active' AND
members.state = 0 AND
members.requested_at IS NULL AND
members.invite_token IS NULL AND
members.access_level > 5
);
-- Formatted by Pg::SQL::PrettyPrinter
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 |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
-
Simulate SaaS, including setting a premium or ultimate plan for a test group.
-
Enable the feature
Feature.enable(:group_credentials_inventory)
-
Create one or more access tokens in a group, subgroup or project of the hierarchy.
-
Navigate to the group. Find the credentials inventory - Secure > Credentials and choose the 'Project and group access tokens' tab.
-
You will now see the project and group access tokens for the group hierarchy. They can be revoked if not already revoked or expired.