Method ListWorkspacesByEmail

backend/store/workspace.go:280–350

ListWorkspacesByEmail finds all workspaces where the given email is a member in the workspace IAM policy bindings, either directly or via a group. Returns workspaces sorted by name.

(ctx context.Context, find *FindWorkspaceMessage)

// in the workspace IAM policy bindings, either directly or via a group.
// Returns workspaces sorted by name.
func (s *Store) ListWorkspacesByEmail(ctx context.Context, find *FindWorkspaceMessage) ([]*WorkspaceMessage, error) {
	memberName := common.FormatUserEmail(find.Email)

	// Check direct membership OR group membership:
	// 1. Direct: member = 'users/{email}'
	// 2. Group: member = 'groups/{groupEmail}' and user_group with that email
	//    contains 'users/{email}' in its payload.members[].member
	// 3. AllUsers: member = 'allUsers' (self-hosted only)
	memberFilter := qb.Q().Space("member = ?", memberName)
	memberFilter.Or(`member LIKE 'groups/%' AND EXISTS (
		SELECT 1
		FROM user_group ug,
		     jsonb_array_elements(ug.payload->'members') AS gm
		WHERE ug.workspace = w.resource_id
		  AND ('groups/' || ug.email = member OR 'groups/' || ug.id = member)
		  AND gm->>'member' = ?
	)`, memberName)
	if find.IncludeAllUser {
		memberFilter.Or("member = ?", common.AllUsers)
	}

	q := qb.Q().Space(`
		SELECT DISTINCT w.resource_id, w.payload, w.created_at
		FROM workspace w
		JOIN policy p ON p.workspace = w.resource_id
		WHERE p.resource_type = ?
		  AND p.type = ?
		  AND w.deleted = FALSE
		  AND EXISTS (
			SELECT 1
			FROM jsonb_array_elements(p.payload->'bindings') AS binding,
			     jsonb_array_elements_text(binding->'members') AS member
			WHERE ?
		  )
	`, storepb.Policy_WORKSPACE.String(), storepb.Policy_IAM.String(), memberFilter)
	if v := find.WorkspaceID; v != nil {
		q.And("w.resource_id = ?", *v)
	}
	q.Space("ORDER BY w.created_at")

	query, args, err := q.ToSQL()
	if err != nil {
		return nil, errors.Wrapf(err, "failed to build sql")
	}

	rows, err := s.GetDB().QueryContext(ctx, query, args...)
	if err != nil {
		return nil, errors.Wrap(err, "failed to find workspaces by member")
	}
	defer rows.Close()

	var workspaces []*WorkspaceMessage
	for rows.Next() {
		var ws WorkspaceMessage
		var payloadBytes []byte
		var createdAt any
		if err := rows.Scan(&ws.ResourceID, &payloadBytes, &createdAt); err != nil {
			return nil, errors.Wrap(err, "failed to scan workspace")