# SQL Editor and SQL Users Placement-Driven RBAC Design

## Scope and evidence

The placement replay reports 98 unexpected SQL Editor 403 responses across 17 endpoints and 20 unexpected SQL Users 403 responses across four endpoints. The affected service plans are Starter and Essential. The expected roles are:

- SQL Editor dataapps endpoints: `cluster:admin` (reported as instance-admin).
- SQL Editor Data Service create endpoints: `project:dev`.
- SQL Users read: `project:dev` and `project:readonly`.
- SQL Users write: `project:dev`.

The implementation is limited to the documented role contracts. `cluster:admin` is treated as the instance administrator for every SQL Editor cluster route that exists today, but those routes are enumerated explicitly so a future route is denied until it is classified. `cluster:viewer` is granted only the TiDB-scoped `sqleditor/get_api` privilege so it can read cluster status and other GET endpoints; the POST/PUT/DELETE privileges are withheld because the current HTTP-method privileges combine operations with different capabilities and the replay does not list viewer write failures.

## Root causes

1. `dataapps/api/api.go` maps every SQL Editor request to a project-scoped resource. A TiDB-scoped `cluster:admin` role therefore cannot match it.
2. `dataapps/api/middlewares.go` requires a project-visible role before RBAC runs. Account's project visibility query intentionally returns org-owner/project-role projects, not projects inferred from a pure cluster role.
3. The cluster status endpoint carries one or more cluster IDs in `cluster_ids`, not in the path, so the generic path mapper cannot authorize its actual TiDB scopes.
4. The `/users` endpoints are per-user Chat2Query preferences keyed by authenticated email. They neither read nor mutate cluster data, despite being inventoried under SQL Editor.
5. Account's `account-central` role bindings only grant SQL User `list` to `project:dev` and `project:readonly`; the replay invokes `query`, `create`, `delete`, and `update`.
6. `dataservices/central` omits its existing `post_api` privilege from `project:dev`.

## Chosen architecture

### SQL Editor request authorization

Replace the overlapping wildcard map with non-overlapping mappings:

- Every currently registered route containing `/clusters/{cluster_id}/...` is listed explicitly and maps to `orgs/{org_id}/projects/{project_id}/tidbs/{cluster_id}/sqleditor/{method}_api`. Unknown or newly added cluster routes do not inherit permission automatically.
- Project-only SQL Editor routes retain the existing project-scoped resources.
- Personal `/users` GET and PUT routes use a method- and Gin-route-template-specific release after `CheckUser`. The SDK path whitelist cannot represent two methods for one path because it stores entries by path, so the release is kept in the dataapps wrapper instead. Authentication and organization membership still run first; the handlers can only read or update the authenticated user's own row.
- `/clusters/status` uses a dedicated middleware branch. It validates every `cluster_ids` value and asks Account for the TiDB-scoped SQL Editor GET permission for every unique requested cluster. One denied cluster rejects the whole request with 403; malformed input returns 400.

Remove the project-visibility precheck from `CheckUser`. Account RBAC becomes the single authorization authority after authentication and organization membership. Its scope-cascade check validates the concrete org/project/TiDB relationship for scoped resources.

### Account RBAC configuration

Add four TiDB-scoped SQL Editor privileges: GET, POST, PUT, and DELETE. Bind them to the existing org/project roles that already receive the corresponding project-scoped privileges, preserving current behavior. Bind all four to `cluster:admin`. Bind only GET to `cluster:viewer` so the role can read cluster status and other TiDB-scoped SQL Editor GET endpoints; this over-grants read access to schema and audit-log GET endpoints, accepted as a tradeoff for covering the benign `/clusters/status` endpoint without introducing a dedicated privilege.

For legacy SQL Users resources, add:

- `project:dev`: `query`, `create`, `delete`, `update` (it already has `list`).
- `project:readonly`: `query` (it already has `list`).

For SQL Editor's two Data Service create operations, bind the existing `dataservices/central/post_api` privilege to `project:dev`. Data Service currently exposes a service-wide method privilege rather than endpoint-level privileges, so this binding also covers its other POST routes. This is an explicit compatibility decision; endpoint-level Data Service privileges require a coordinated change in that service and are outside these two repositories.

## Data flow

For a cluster path, dataapps authenticates the user, verifies organization membership, derives a concrete TiDB-scoped resource from the URL, and calls Account. Account validates resource hierarchy, finds role bindings at org/project/TiDB scope, and evaluates the configured privilege.

For cluster status, dataapps performs the same Account permission call once per requested cluster before invoking the handler. For personal preferences, dataapps stops after authentication and organization membership because the repository key is the authenticated email rather than a requested user or cluster.

## Error handling and security boundaries

- Missing or invalid `cluster_ids` returns HTTP 400 before the handler.
- More than 100 raw `cluster_ids` values returns HTTP 400. Duplicate IDs count toward that input limit but are checked only once, in first-seen order.
- The complete Account permission batch has a five-second request-scoped deadline.
- Account RPC failures and denied permissions return HTTP 403, matching the existing RBAC middleware behavior.
- Every cluster in a batch must pass; authorizing only the first cluster is forbidden.
- Cluster-scoped resources include org, project, and TiDB IDs so Account can reject cross-project or cross-org combinations.
- No generic child-role-to-parent-resource rule is added to Account.
- No SQL Editor whitelist is added beyond the authenticated user's personal preferences.

## Testing and verification

Use TDD in both repositories:

1. Add dataapps unit tests that first fail for cluster resource generation, explicit current-route coverage, future-route fail-closed behavior, non-overlapping path mapping, personal preference release, all-cluster status checks, invalid/duplicate/excess cluster IDs, batch timeout, denied permissions, and Account RPC errors.
2. Add an account configuration regression test that first fails for every required privilege and role binding.
3. Make the minimal implementation/configuration changes.
4. Run targeted tests, Account's `rbac_management.py check-config`, formatting/static checks, and the broadest repository test suites that the existing baseline permits.
5. Independently review the combined diff for privilege escalation, route coverage, and consistency with the replay contract.

## Out of scope

- Redesigning SQL Editor permissions into capability-level resources.
- Granting SQL Editor write access (POST/PUT/DELETE) to `cluster:viewer`.
- Fixing unrelated pre-existing test, Swagger, generated-code, or Docker-dependent failures.
- Deploying RBAC configuration or replaying production/staging traffic.