Query to denormalize user membership access levels

User group membership access levels are stored in a normalized form in the members table.

An example scenario where we have groups A B C D E F G. Carla has two direct memberships, and Alex has one.

graph TD
  A --> B
  A --> C
  B --> D
  B --> E
  C --> F
  C --> G

  caral1[Carla]-. reporter .-A
  Carla-. developer .-E
  Alex-. developer .-D

The members table records look like so:

User	Group	Access Level
Alex	D	Developer
Carla	A	Reporter
Carla	B
Carla	C
Carla	D
Carla	E	Developer
Carla	F
Carla	G

However, due to inheritance, Carla and Alex also have membership in descendant groups which do not contain membership records. We want to query the access level for all rows to generate a table like so:

User	Group	Access Level
Alex	D	Developer
Carla	A	Reporter
Carla	B	Reporter
Carla	C	Reporter
Carla	D	Reporter
Carla	E	Developer
Carla	F	Reporter
Carla	G	Reporter

Availability and Testing

• Consider using a feature flag with this change, which will allow us to roll out and monitor any potential issues on staging and production.
  • This would also trigger e2e:package-and-test with ee:instance-parallel and ee:instance-parallel-ff-inverse jobs on the MR for functional regression tests.
  • If a feature flag cannot be used, and changes don't trigger a review app pipeline, please trigger e2e:package-and-test job manually

Feel free to reach out to:

• @vburton for any questions on functional testing, QA failures, etc.
• The Quality Enablement team for running any performance tests needed