bug: OPA/RBAC authorization performance degrades significantly when user belongs to many organizations

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

Reported by customer on ticket 4943.

After upgrading from Coder 2.24.x to 2.28.8, a customer is experiencing significant UI latency (7+ seconds) when loading the Workspaces page. The issue is specific to users who belong to many organizations (108 in this case).

Key observations from the customer:

• Database migrations completed successfully (schema version 393)
• Database latency appears normal according to their monitoring
• The /api/v2/organizations endpoint takes ~3 seconds (verified via curl)
• CPU profiling (pprof) shows high CPU usage in OPA (Open Policy Agent) functions:
• ast.(*GenericVisitor).Walk - 12.53% flat, 52.88% cumulative
• topdown.(*saveSet).containsrec.func1 - 2.27% flat, 23.37% cumulative
• topdown.(*saveSet).saveRequired.func1 - 2.03% flat, 49.93% cumulative
• Various ast.WalkTerms and evaluation functions
• CPU throttling was observed
• When logged in as a user assigned to only a single organization, there are no latency issues

We have been able to reproduce this issue in house:

1. Create a Coder deployment
2. Create 100+ organizations
3. Add a user to all organizations
4. Log in as that user
5. Navigate to the Workspaces page (the user should have atleast 3-4 workspaces although the latency is noticeable with just 1 workspace as well)
6. Observe slow load times (6-7+ seconds)

Workaround:

The customer is cleaning up unused organizations to mitigate the issue, but this is not a sustainable long-term solution for deployments that legitimately need many organizations as is the case with this customer as well.

Relevant Log Output

Please access the images via ticket 4943.

Expected Behavior

Coder should not induce latency if a user is part of a lot of organizations.

Steps to Reproduce

1. Create a Coder deployment
2. Create 100+ organizations
3. Add a user to all organizations
4. Log in as that user
5. Navigate to the Workspaces page (the user should have atleast 3-4 workspaces although the latency is noticeable with just 1 workspace as well)
6. Observe slow load times (6-7+ seconds)

Environment

• Coder version: 2.28.8 (upgrade from 2.24.x)
• Number of organizations: 108
• Database: Migrations successful (schema v393)

Additional Context

No response