User cannot list groups despite having access to subgroups

[arvindh123]

We have a group hierarchy like:

g1
└── g2
    └── g3
        └── g4
            └── g5
                └── g6

A role named subgroup_reader was created in g3 with the action subgroup_read.
user3 was added as a member of this role.

Expected behavior

When user3 calls the list groups endpoint (e.g., GET /groups), it should return subgroups g4, g5, and g6, as user3 has access via the subgroup_reader role in g3.

Actual behavior

The list groups endpoint returns no results for user3.
However, if directly querying access (e.g., GET /groups/g4), user3 is allowed to view g4, g5, and g6.

To Reproduce

1. Create groups: g1 → g6 as a parent-child chain.
2. In g3, create role subgroup_reader.
3. Assign action subgroup_read to that role.
4. Add user3 to the role in g3.
5. Call GET /groups as user3.

**Solution: **
In All list query
we need to replace direct_groups_with_subgroup with below and additoal CTE is added in below query direct_leaf_groups_with_subgroup to remove parent nodes

    direct_groups_with_subgroup AS (
        SELECT
            g.*,
            gr.entity_id AS entity_id,
            grm.member_id AS member_id,
            gr.id AS role_id,
            gr."name" AS role_name,
            array_agg(DISTINCT all_actions."action") AS actions,
            g.path AS g_path,
            nlevel(g.path) AS g_nlevel
        FROM
            groups_role_members grm
        JOIN
            groups_role_actions gra ON gra.role_id = grm.role_id
        JOIN
            groups_roles gr ON gr.id = grm.role_id
        JOIN
            "groups" g ON g.id = gr.entity_id
        JOIN
            groups_role_actions all_actions ON all_actions.role_id = grm.role_id
        WHERE
            grm.member_id = '%s'
            AND g.domain_id ='%s'
            AND gra."action" LIKE 'subgroup_%'
        GROUP BY
            gr.entity_id, grm.member_id, gr.id, gr."name", g."path", g.id
    ),
    direct_leaf_groups_with_subgroup  AS (
        SELECT dgws.*
        FROM direct_groups_with_subgroup dgws
        WHERE NOT EXISTS (
            SELECT 1
            FROM direct_groups_with_subgroup dgws2
            WHERE
                dgws2.path @> dgws.path 
                AND dgws2.id != dgws.id  
        )
    ),

This bug was introduced as a side effect of PR #2976, which was intended to fix issue #2865.

[arvindh123]

Hello @felixgateru
Could please test the below base query
It should not bring back the issue #2976 and please test does this solves the issue

func (repo groupRepository) userGroupsBaseQuery(domainID, userID string) string {
	return fmt.Sprintf(`
WITH direct_groups AS (
SELECT
	g.*,
	gr.entity_id AS entity_id,
	grm.member_id AS member_id,
	gr.id AS role_id,
	gr."name" AS role_name,
	array_agg(gra."action") AS actions
FROM
	groups_role_members grm
JOIN
	groups_role_actions gra ON gra.role_id = grm.role_id
JOIN
	groups_roles gr ON gr.id = grm.role_id
JOIN
	"groups" g ON g.id = gr.entity_id
WHERE
	grm.member_id = '%s'
	AND g.domain_id = '%s'
GROUP BY
	gr.entity_id, grm.member_id, gr.id, gr."name", g."path", g.id
),
direct_groups_with_subgroup AS (
	SELECT
		g.*,
		gr.entity_id AS entity_id,
		grm.member_id AS member_id,
		gr.id AS role_id,
		gr."name" AS role_name,
		array_agg(DISTINCT all_actions."action") AS actions
	FROM
		groups_role_members grm
	JOIN
		groups_role_actions gra ON gra.role_id = grm.role_id
	JOIN
		groups_roles gr ON gr.id = grm.role_id
	JOIN
		"groups" g ON g.id = gr.entity_id
	JOIN
		groups_role_actions all_actions ON all_actions.role_id = grm.role_id
	WHERE
		grm.member_id = '%s'
		AND g.domain_id = '%s'
		AND gra."action" LIKE 'subgroup_%%'
	GROUP BY
		gr.entity_id, grm.member_id, gr.id, gr."name", g."path", g.id
),
direct_leaf_groups_with_subgroup  AS (
	SELECT dgws.*
	FROM direct_groups_with_subgroup dgws
	WHERE NOT EXISTS (
		SELECT 1
		FROM direct_groups_with_subgroup dgws2
		WHERE
			dgws2.path @> dgws.path
			AND dgws2.id != dgws.id
	)
),
indirect_child_groups AS (
	SELECT
		DISTINCT indirect_child_groups.id as child_id,
		indirect_child_groups.*,
		dlgws.id as access_provider_id,
		dlgws.role_id as access_provider_role_id,
		dlgws.role_name as access_provider_role_name,
		dlgws.actions as access_provider_role_actions
	FROM
		direct_leaf_groups_with_subgroup dlgws
	JOIN
		groups indirect_child_groups ON indirect_child_groups.path <@ dlgws.path  -- Finds all children of entity_id based on ltree path
	WHERE
		indirect_child_groups.domain_id = '%s'
		AND
		NOT EXISTS (  -- Ensures that the indirect_child_groups.id is not already in the direct_groups_with_subgroup table
			SELECT 1
			FROM direct_groups_with_subgroup dlgws
			WHERE dlgws.id = indirect_child_groups.id
		)
),
direct_indirect_groups as (
	SELECT
		id,
		parent_id,
		domain_id,
		"name",
		tags,
		description,
		metadata,
		created_at,
		updated_at,
		updated_by,
		status,
		"path",
		role_id,
		role_name,
		actions,
		'direct' AS access_type,
		'' AS access_provider_id,
		'' AS access_provider_role_id,
		'' AS access_provider_role_name,
		array[]::::text[] AS access_provider_role_actions
	FROM
		direct_groups
	UNION
	SELECT
		id,
		parent_id,
		domain_id,
		"name",
		tags,
		description,
		metadata,
		created_at,
		updated_at,
		updated_by,
		status,
		"path",
		'' AS role_id,
		'' AS role_name,
		array[]::::text[] AS actions,
		'indirect' AS access_type,
		access_provider_id,
		access_provider_role_id,
		access_provider_role_name,
		access_provider_role_actions
	FROM
		indirect_child_groups
),
final_groups AS (
	SELECT
		dig.id,
		dig.parent_id,
		dig.domain_id,
		dig."name",
		dig.tags,
		dig.description,
		dig.metadata,
		dig.created_at,
		dig.updated_at,
		dig.updated_by,
		dig.status,
		dig."path",
		dig.role_id,
		dig.role_name,
		dig.actions,
		dig.access_type,
		dig.access_provider_id,
		dig.access_provider_role_id,
		dig.access_provider_role_name,
		dig.access_provider_role_actions
	FROM
		direct_indirect_groups as dig
	UNION
	SELECT
		dg.id,
		dg.parent_id,
		dg.domain_id,
		dg."name",
		dg.tags,
		dg.description,
		dg.metadata,
		dg.created_at,
		dg.updated_at,
		dg.updated_by,
		dg.status,
		dg."path",
		'' AS role_id,
		'' AS role_name,
		array[]::::text[] AS actions,
		'domain' AS access_type,
		d.id AS access_provider_id,
		dr.id AS access_provider_role_id,
		dr."name" AS access_provider_role_name,
		array_agg(dra."action") as actions
	FROM
		domains_role_members drm
	JOIN
		domains_role_actions dra ON dra.role_id = drm.role_id
	JOIN
		domains_roles dr ON dr.id = drm.role_id
	JOIN
		domains d ON d.id = dr.entity_id
	JOIN
		"groups" dg ON dg.domain_id = d.id
	WHERE
		drm.member_id = '%s' -- user_id
	 	AND d.id = '%s' -- domain_id
	 	AND dra."action" LIKE 'group_%%'
	 	AND NOT EXISTS (  -- Ensures that the direct and indirect groups are not in included.
			SELECT 1 FROM direct_indirect_groups dig
			WHERE dig.id = dg.id
		)
	GROUP BY
		dg.id, d.id, dr.id
)
		`, userID, domainID, userID, domainID, domainID, userID, domainID)
}