# SQL Editor and SQL Users RBAC Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan.

**Goal:** Eliminate the placement replay's unexpected SQL Editor and SQL Users 403 responses without broadening access beyond the documented roles.

**Architecture:** Dataapps will authorize cluster routes against concrete TiDB-scoped SQL Editor resources and will explicitly authorize every cluster in the status batch. Account's declarative RBAC configuration will bind those resources, the missing SQL Users operations, and the existing Data Service POST privilege to the intended roles. Authentication and organization membership remain in dataapps, while Account is the sole scoped authorization authority.

**Tech Stack:** Go 1.20, Gin, gRPC/protobuf, gomock, JSON RBAC configuration, Python configuration validator.

---

### Task 1: Lock the Account RBAC contract with failing tests

**Files:**
- Create: `/Users/clark/coding/account/tools/rbac-management/rbac_config_test.go`
- Read: `/Users/clark/coding/account/tools/rbac-management/ALL_PRIVILEGE.json`
- Read: `/Users/clark/coding/account/tools/rbac-management/ALL_ROLEBINDING.json`

**Step 1: Add JSON test helpers**

Create table-driven helpers that load the two configuration files into `map[string]map[string]...` values and report a focused failure when a service, privilege, role, or binding is absent.

**Step 2: Add the missing-contract assertions**

Assert all of the following:

- `sqleditor` declares TiDB-scoped GET, POST, PUT, and DELETE privileges.
- `sqleditor` binds all four to `org:owner`, `project:owner`, `project:dev`, `project:readonly`, and `cluster:admin`.
- `sqleditor` binds only the TiDB-scoped GET privilege to `cluster:viewer`.
- `account-central` binds SQL User create/delete/query/update to `project:dev` and query to `project:readonly`.
- `dataservices/central` binds its existing `post_api` privilege to `project:dev`.

**Step 3: Prove the tests fail for the intended omissions**

Run: `go test ./tools/rbac-management -run TestPlacementDrivenRBACBindings -count=1`

Expected: FAIL with missing TiDB-scoped SQL Editor privileges/bindings and missing role bindings for SQL Users/Data Service.

### Task 2: Make the Account RBAC contract pass

**Files:**
- Modify: `/Users/clark/coding/account/tools/rbac-management/ALL_PRIVILEGE.json`
- Modify: `/Users/clark/coding/account/tools/rbac-management/ALL_ROLEBINDING.json`
- Test: `/Users/clark/coding/account/tools/rbac-management/rbac_config_test.go`

**Step 1: Add precise SQL Editor privileges**

Under `sqleditor`, add:

- `orgs/{org_id}/projects/{project_id}/tidbs/{cluster_id}/sqleditor/get_api`
- `orgs/{org_id}/projects/{project_id}/tidbs/{cluster_id}/sqleditor/post_api`
- `orgs/{org_id}/projects/{project_id}/tidbs/{cluster_id}/sqleditor/put_api`
- `orgs/{org_id}/projects/{project_id}/tidbs/{cluster_id}/sqleditor/delete_api`

Their URL patterns must match only cluster-path SQL Editor routes and must capture org, project, and cluster IDs in that order.

**Step 2: Add only the documented role bindings**

Bind those four resources to the four existing org/project roles and `cluster:admin`; bind only `get_api` to `cluster:viewer`. Add the SQL Users and Data Service bindings asserted in Task 1.

**Step 3: Validate JSON and run the focused test**

Run:

```bash
python3 -m json.tool tools/rbac-management/ALL_PRIVILEGE.json >/dev/null
python3 -m json.tool tools/rbac-management/ALL_ROLEBINDING.json >/dev/null
go test ./tools/rbac-management -run TestPlacementDrivenRBACBindings -count=1
python3 tools/rbac-management/rbac_management.py check-config
```

Expected: all commands succeed.

### Task 3: Lock dataapps route-to-resource behavior with failing tests

**Files:**
- Create: `/Users/clark/coding/dataapps/api/api_test.go`
- Modify: `/Users/clark/coding/dataapps/api/api.go`

**Step 1: Extract testable RBAC configuration builders**

Introduce functions that return the SQL Editor/EDA path map and whitelist while preserving the current middleware installation behavior. Do not yet add the new mappings.

**Step 2: Add table-driven routing contract tests**

Cover every HTTP method used by cluster SQL Editor routes and assert that it maps to a resource containing `tidbs/{cluster_id}`. Assert the project-only settings, bot, permission-entry, and streaming routes retain project-scoped resources. Assert GET/PUT personal `/users` and the cluster-status path are whitelisted, but unrelated methods/routes are not.

**Step 3: Prove the routing contract fails**

Run: `go test ./api -run 'TestSQL(EditorRBACMappings|EditorRBACWhitelist)' -count=1`

Expected: FAIL because the existing wildcard resources are project-scoped and users/status are not explicitly handled.

### Task 4: Implement dataapps TiDB-scoped and batch authorization

**Files:**
- Modify: `/Users/clark/coding/dataapps/api/api.go`
- Modify: `/Users/clark/coding/dataapps/api/middlewares.go`
- Modify: `/Users/clark/coding/dataapps/api/api_test.go`

**Step 1: Replace overlapping SQL Editor wildcard mappings**

Enumerate every currently registered cluster-path GET/POST/PUT/DELETE route and map it to the new TiDB-scoped resources. Do not use a cluster wildcard: a future route must fail closed until classified. Map the four project-only route families explicitly to their existing project-scoped resources. Keep EDA mappings unchanged. Exclude cluster status and personal users from the generic matcher, and release them only after adding their dedicated handling.

**Step 2: Add failing cluster-status middleware tests**

Using Gin `httptest` and the generated Account mock client, assert:

- every valid `cluster_ids` value causes a permission call with the concrete TiDB-scoped GET resource;
- one denied cluster returns 403 and prevents the handler;
- an Account RPC error returns 403 and prevents the handler;
- missing, zero, or malformed IDs return 400 and make no permission call.

Run: `go test ./api -run TestCheckSQLListClusterStatusPermission -count=1`

Expected: FAIL because the dedicated middleware does not yet exist.

**Step 3: Implement the dedicated cluster-status middleware**

Recognize only `GET /api/v1/dataapps/sqleditor/orgs/:org_id/projects/:project_id/clusters/status`. Parse the comma-separated `cluster_ids` query, reject empty/invalid/zero IDs and more than 100 raw values, deduplicate in first-seen order, fetch the authenticated Account user from Gin context, and call `HasPermissionForUser` once per unique cluster using `orgs/{org}/projects/{project}/tidbs/{cluster}/sqleditor/get_api`. Apply a five-second deadline to the whole permission batch and require every response to allow access. Preserve the existing RBAC behavior of returning 403 for denial or RPC failure.

**Step 4: Remove the conflicting project-visibility gate**

In `authorization`, retain token authentication, org membership validation, and numeric org/project parsing, but remove `ListProjectsByOrgsInUser` as an authorization prerequisite. The concrete Account RBAC call now verifies project and TiDB scope.

**Step 5: Run and format the focused suite**

Run:

```bash
gofmt -w api/api.go api/middlewares.go api/api_test.go
go test ./api -count=1
go vet ./api
```

Expected: all commands succeed.

### Task 5: Verify repository-wide behavior

**Files:**
- Verify all modified files in both repositories.

**Step 1: Inspect diffs for unintended grants**

Run `git diff --check` and inspect the role-binding diff. Confirm `cluster:viewer` only receives the TiDB-scoped `sqleditor/get_api` privilege (not POST/PUT/DELETE), no generic Account scope-cascade change, and no broad SQL Editor whitelist.

**Step 2: Run dataapps verification**

Run:

```bash
go test ./api -count=1
go build ./cmd/...
go vet ./api/...
go test ./...
```

Record any repository-baseline failure separately from failures caused by this change.

**Step 3: Run account verification**

Run:

```bash
go test ./tools/rbac-management -count=1
python3 tools/rbac-management/rbac_management.py check-config
make check
```

Record environment-dependent or generated-code baseline failures separately.

### Task 6: Independent review and handoff

**Files:**
- Review all changed files in `/Users/clark/coding/dataapps` and `/Users/clark/coding/account`.

**Step 1: Request an independent code review**

Ask a fresh reviewer to compare the combined diff with the Feishu placement matrix and the design document, focusing on route gaps, privilege escalation, multi-cluster authorization, malformed input, and regression risk.

**Step 2: Resolve valid findings and rerun affected checks**

Apply fixes through the same failing-test-first process, then rerun targeted tests and `git diff --check`.

**Step 3: Report without committing or deploying**

Summarize changed behavior, exact verification evidence, and any pre-existing failures. Leave both repositories on `fix/sql-editor-sql-users-rbac`; do not commit, push, deploy, or replay placement traffic without explicit user authorization.