Comment 84 for bug 1918145

Reviewed: https://review.opendev.org/c/openstack/neutron/+/885173
Committed: https://opendev.org/openstack/neutron/commit/d3b403bfee17267dfa36184d6be27ba973f068ee
Submitter: "Zuul (22348)"
Branch: stable/zed

commit d3b403bfee17267dfa36184d6be27ba973f068ee
Author: Rodolfo Alonso Hernandez <email address hidden>
Date: Sun May 28 17:28:03 2023 +0200

    Change RBAC relationship loading method to "joined"

    This patch changes all RBAC relationship method to "joined". This change
    enforces that the RBAC associated registers are loaded along with the
    parent resource. The rationale of this change is to be able to control
    the SQL query executed; the subquery cannot be directly managed by
    Neutron.

    It is very usual to create the RBAC rules from one single project that
    is usually the adminitrator project. That means all RBAC rules will
    belong to it. Before this change, the SQL subquery performed to
    retrieve the RBAC entries was this (from a network query):

      SELECT networks.id AS networks_id
      FROM networks LEFT OUTER JOIN networkrbacs ON networks.id =
      networkrbacs.object_id
      WHERE networks.project_id = 'bd133e2c499c4bf8aeb16206e31c3c20'
        OR networkrbacs.action = 'access_as_external'
        AND networkrbacs.target_project = 'bd133e2c499c4bf8aeb16206e31c3c20'
        OR networkrbacs.target_project = '*'
        OR networks.project_id = 'bd133e2c499c4bf8aeb16206e31c3c20'
        OR networkrbacs.action IN ('access_as_shared', 'access_as_readonly')
        AND (networkrbacs.target_project = 'bd133e2c499c4bf8aeb16206e31c3c20'
        OR networkrbacs.target_project = '*');

    This SQL result has a very low cardinality; that means there are many
    duplicated registers. For example, with 10 external network, 1000
    projects and 2500 RBAC rules, this query returns 1.4 million rows.
    Instead if a "GROUP BY resource_id" (in this case network_id) clause is
    added, the number of rows is reduced to 10 (considering this project
    has a RBAC per network).

    In order to introduce this "GROUP BY" clause, this patch is changing
    the loading method. The clause is added in a neutron-lib patch [1].

    This change by itself does not improve the query performance. The
    neutron-lib patch is needed too. Although this patch does not modify
    que SQL query results, the tests added will prove that the neutron-lib
    patch does not introduce any regression.

    [1]https://review.opendev.org/c/openstack/neutron-lib/+/884878

    Closes-Bug: #1918145
    Change-Id: Ic6001bd5a57493b8befdf81a41eb0bd1c8022df3
    (cherry picked from commit e9da29d16c474822c015996cf34e40005419146a)