## Stack Part of [CODAGT-1341](https://linear.app/codercom/issue/CODAGT-1341/support-organization-level-skills-in-coder-agents). This stack adds organization skills to Coder Agents: admins publish a skill once per organization, share it with groups or members, and chats in that organization load it next to personal and workspace skills. It replaces the 21-PR draft stack #30584 to #30604 with five PRs, bottom to top: 1. #30609 data model: one migration, queries, RBAC and dbauthz 2. #30610 organization skill API routes and the scope-neutral SDK 3. **#30611** sharing ACL routes (this PR) 4. #30612 settings UI: shared skills table, admin page, member section 5. #30613 chats: chatd loading, `/` menu, docs ## What changes This PR adds the routes that let admins choose who can read an organization skill. It reuses the MCP server ACL machinery rather than adding a second implementation. Routes under `/api/experimental/organizations/{organization}/skills/{skillName}`, all behind `httpmw.ExtractOrganizationSkillParam` with `share`, so a caller without share gets 404: - `GET /acl` returns the resolved user and group grants. - `GET /acl/available` returns the organization's users and groups that can be granted, with the same `q`, `after_id`, `limit` and `offset` paging as the MCP endpoint. - `PATCH /acl` takes a sparse `UpdateOrganizationSkillACLRequest`: only the listed principals change, `read` grants and `""` removes. It returns 204 and writes a `write` audit entry with the old and new ACL. **Concurrency (please review closely):** the PATCH transaction first locks the granted users' `organization_members` rows `FOR KEY SHARE` (new query `LockOrganizationMembersByUserIDsForShare`, run as system), then locks the skill row `FOR UPDATE`, then validates the update on the transaction handle. Member removal takes the same locks in the same order (its membership row, then the skill rows through the #30609 trigger), so the two can't deadlock, and a removal can't miss a grant written by a concurrent PATCH. A skill deleted between the middleware read and the row lock stays a 404. Grants are users and groups, the same as MCP server configs (Mike's decision, for parity). Only the `read` role exists; any other role is a validation error, as are users or groups outside the organization. Supporting changes: - `coderd/mcp_acl.go`: the MCP ACL handlers' resolution, candidate listing and validation move into generic helpers (`writeOrganizationACLAvailable`, `validateOrganizationACLUpdate`, `resolveACLUsers`, `resolveACLGroups`) shared by both resources; `acl.Validate` accepts a named `acl.Role` constraint that now includes `codersdk.OrganizationSkillRole`. - `codersdk/skills.go`: `OrganizationSkillRole`, `OrganizationSkillACL` with its user and group entries, `UpdateOrganizationSkillACLRequest`, and client methods. - New sqlc query `UpdateOrganizationSkillACLByID` with its dbauthz wrapper (requires `share`). ## Commits The first commit reproduces the reviewed code from the old stack; each later commit is a labeled trim or fix. - `b1c85560b69` feat: add organization skill sharing ACL routes. Reproduction: every file this commit changes equals the reviewed old `acl-routes` head `80c1c3594e6`; the rest of the tree differs only by #30609's migration merge and main. - `b5d2ee265c8` test(coderd): merge duplicate organization skill ACL route tests (+49/-111). The removed cases repeated `TestMCPServerConfigACL`, `TestMCPServerConfigACLAvailable` or `TestRolePermissions`; with the MCP tests included, the covered block set of `organizationskills_acl.go`, `mcp_acl.go` and `rbac/acl/updatevalidator.go` is identical before and after. Kept: `TestOrganizationSkillACL` (`OrgAdminManagesACL`, `ReadersGetNotFound`, `SparseUpdateChangesReaders` with the audit diff, `RejectsUnsupportedRole`, `ScopedAPIKeys`), `TestOrganizationSkillACLConcurrentDelete`, and the lock order tests `TestOrganizationSkillACLMemberRemoval` (`RemovalBeforeGrant`, `RemovalAfterGrant`). `OrgAdminManagesACL` now also checks that users from another organization are not candidates (mutation checked). Sharing the param extractor with MCP was skipped: it would save about 10 lines of security-sensitive code. - `e09c4dba66b` Merge the updated org-api layer into sharing. Brings in #30610's `ad0a7a37504`; the merge has no changes of its own. - `1d3817dc639` and `c8c88f84d18` merge the updated #30610 layer, including main; no changes of their own. - `c4d22c47990` merges the updated #30610 layer (main at `e7b76872098`); no changes of its own. ## Size Production +431/-102, tests +377/-0, generated +918/-0; total +1726/-102. This is above the 500-line reviewer-friendly limit on purpose: Mike asked for 4 to 5 PRs instead of the 21 small ones this stack replaces. ## Replaces #30591 (ACL queries), #30592 (ACL routes). ## Validation - `go build` and `go vet` passed; `make gen` exited 0 with no diff. - `go test` passed for `coderd` (skill, MCP and ACL tests), `coderd/database`, `dbauthz`, `migrations`, `gentest`, `coderd/rbac/...`, `coderd/httpmw`, `coderd/x/skills/...`, `coderd/x/chatd/...`, and the audit and skill tests in `enterprise/audit` and `enterprise/coderd`. - The covered block comparison above, before and after the test merge. - On `c4d22c47990`: the same checks as #30610's head, plus `go test ./coderd/ -run 'Skill|MCP|ACL'`, pass. - Remote UAT round 7 through Coder Agents passed on the whole stack; see #30613's Validation for scope and findings. > Xum opened this PR on behalf of @ibetitsmike.
235 lines
6.7 KiB
SQL
235 lines
6.7 KiB
SQL
-- name: OrganizationMembers :many
|
|
-- Arguments are optional with uuid.Nil to ignore.
|
|
-- - Use just 'organization_id' to get all members of an org
|
|
-- - Use just 'user_id' to get all orgs a user is a member of
|
|
-- - Use both to get a specific org member row
|
|
SELECT
|
|
sqlc.embed(organization_members),
|
|
users.username, users.avatar_url, users.name, users.email, users.rbac_roles as "global_roles",
|
|
users.last_seen_at, users.status, users.login_type, users.is_service_account,
|
|
users.created_at as user_created_at, users.updated_at as user_updated_at
|
|
FROM
|
|
organization_members
|
|
INNER JOIN
|
|
users ON organization_members.user_id = users.id AND users.deleted = false
|
|
WHERE
|
|
-- Filter by organization id
|
|
CASE
|
|
WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
organization_id = @organization_id
|
|
ELSE true
|
|
END
|
|
-- Filter by user id
|
|
AND CASE
|
|
WHEN @user_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
user_id = @user_id
|
|
ELSE true
|
|
END
|
|
-- Filter by system type
|
|
AND CASE
|
|
WHEN @include_system::bool THEN TRUE
|
|
ELSE
|
|
is_system = false
|
|
END
|
|
-- Filter by github user ID. Note that this requires a join on the users table.
|
|
AND CASE
|
|
WHEN @github_user_id :: bigint != 0 THEN
|
|
users.github_com_user_id = @github_user_id
|
|
ELSE true
|
|
END;
|
|
|
|
-- name: InsertOrganizationMember :one
|
|
INSERT INTO
|
|
organization_members (
|
|
organization_id,
|
|
user_id,
|
|
created_at,
|
|
updated_at,
|
|
roles
|
|
)
|
|
VALUES
|
|
($1, $2, $3, $4, $5) RETURNING *;
|
|
|
|
-- name: DeleteOrganizationMember :exec
|
|
DELETE
|
|
FROM
|
|
organization_members
|
|
WHERE
|
|
organization_id = @organization_id AND
|
|
user_id = @user_id
|
|
;
|
|
|
|
|
|
-- name: GetOrganizationIDsByMemberIDs :many
|
|
SELECT
|
|
user_id, array_agg(organization_id) :: uuid [ ] AS "organization_IDs"
|
|
FROM
|
|
organization_members
|
|
WHERE
|
|
user_id = ANY(@ids :: uuid [ ])
|
|
GROUP BY
|
|
user_id;
|
|
|
|
-- name: LockOrganizationMembersByUserIDsForShare :many
|
|
-- Locks the listed users' membership rows with FOR KEY SHARE for the rest of
|
|
-- the current transaction. FOR KEY SHARE conflicts with DELETE, so a member
|
|
-- cannot be removed until the transaction ends, and a removal committed
|
|
-- earlier is observed as a missing row.
|
|
SELECT
|
|
user_id
|
|
FROM
|
|
organization_members
|
|
WHERE
|
|
organization_id = @organization_id
|
|
AND user_id = ANY(@user_ids :: uuid [ ])
|
|
ORDER BY
|
|
user_id
|
|
FOR KEY SHARE;
|
|
|
|
-- name: UpdateMemberRoles :one
|
|
UPDATE
|
|
organization_members
|
|
SET
|
|
-- Remove all duplicates from the roles.
|
|
roles = ARRAY(SELECT DISTINCT UNNEST(@granted_roles :: text[]))
|
|
WHERE
|
|
user_id = @user_id
|
|
AND organization_id = @org_id
|
|
RETURNING *;
|
|
|
|
-- name: PaginatedOrganizationMembers :many
|
|
SELECT
|
|
sqlc.embed(organization_members),
|
|
users.username, users.avatar_url, users.name, users.email, users.rbac_roles as "global_roles",
|
|
users.last_seen_at, users.status, users.login_type, users.is_service_account,
|
|
users.created_at as user_created_at, users.updated_at as user_updated_at,
|
|
COUNT(*) OVER() AS count
|
|
FROM
|
|
organization_members
|
|
INNER JOIN
|
|
users ON organization_members.user_id = users.id AND users.deleted = false
|
|
WHERE
|
|
CASE
|
|
-- This allows using the last element on a page as effectively a cursor.
|
|
-- This is an important option for scripts that need to paginate without
|
|
-- duplicating or missing data.
|
|
WHEN @after_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN (
|
|
-- The pagination cursor is the last ID of the previous page.
|
|
-- The query is ordered by the username field, so select all
|
|
-- rows after the cursor.
|
|
(LOWER(users.username)) > (
|
|
SELECT
|
|
LOWER(users.username)
|
|
FROM
|
|
organization_members
|
|
INNER JOIN
|
|
users ON organization_members.user_id = users.id
|
|
WHERE
|
|
organization_members.user_id = @after_id
|
|
)
|
|
)
|
|
ELSE true
|
|
END
|
|
-- Start filters
|
|
-- Filter by organization id
|
|
AND CASE
|
|
WHEN @organization_id :: uuid != '00000000-0000-0000-0000-000000000000'::uuid THEN
|
|
organization_id = @organization_id
|
|
ELSE true
|
|
END
|
|
-- Filter by email, username, or name (display name)
|
|
AND CASE
|
|
WHEN @search :: text != '' THEN (
|
|
users.email ILIKE concat('%', @search, '%')
|
|
OR users.username ILIKE concat('%', @search, '%')
|
|
OR users.name ILIKE concat('%', @search, '%')
|
|
)
|
|
ELSE true
|
|
END
|
|
-- Filter by name (display name)
|
|
AND CASE
|
|
WHEN @name :: text != '' THEN
|
|
users.name ILIKE concat('%', @name, '%')
|
|
ELSE true
|
|
END
|
|
-- Filter by exact username
|
|
AND CASE
|
|
WHEN @exact_username :: text != '' THEN
|
|
lower(users.username) = lower(@exact_username)
|
|
ELSE true
|
|
END
|
|
-- Filter by exact email
|
|
AND CASE
|
|
WHEN @exact_email :: text != '' THEN
|
|
lower(users.email) = lower(@exact_email)
|
|
ELSE true
|
|
END
|
|
-- Filter by status
|
|
AND CASE
|
|
-- @status needs to be a text because it can be empty, If it was
|
|
-- user_status enum, it would not.
|
|
WHEN cardinality(@status :: user_status[]) > 0 THEN
|
|
users.status = ANY(@status :: user_status[])
|
|
ELSE true
|
|
END
|
|
-- Filter by global rbac_roles
|
|
AND CASE
|
|
-- @rbac_role allows filtering by rbac roles. If 'member' is included, show everyone, as
|
|
-- everyone is a member.
|
|
WHEN cardinality(@rbac_role :: text[]) > 0 AND 'member' != ANY(@rbac_role :: text[]) THEN
|
|
users.rbac_roles && @rbac_role :: text[]
|
|
ELSE true
|
|
END
|
|
-- Filter by last_seen
|
|
AND CASE
|
|
WHEN @last_seen_before :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
|
|
users.last_seen_at <= @last_seen_before
|
|
ELSE true
|
|
END
|
|
AND CASE
|
|
WHEN @last_seen_after :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
|
|
users.last_seen_at >= @last_seen_after
|
|
ELSE true
|
|
END
|
|
-- Filter by created_at (user creation date, not date added to org)
|
|
AND CASE
|
|
WHEN @created_before :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
|
|
users.created_at <= @created_before
|
|
ELSE true
|
|
END
|
|
AND CASE
|
|
WHEN @created_after :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
|
|
users.created_at >= @created_after
|
|
ELSE true
|
|
END
|
|
-- Filter by system type
|
|
AND CASE
|
|
WHEN @include_system::bool THEN TRUE
|
|
ELSE users.is_system = false
|
|
END
|
|
-- Filter by github.com user ID
|
|
AND CASE
|
|
WHEN @github_com_user_id :: bigint != 0 THEN
|
|
users.github_com_user_id = @github_com_user_id
|
|
ELSE true
|
|
END
|
|
-- Filter by login_type
|
|
AND CASE
|
|
WHEN cardinality(@login_type :: login_type[]) > 0 THEN
|
|
users.login_type = ANY(@login_type :: login_type[])
|
|
ELSE true
|
|
END
|
|
-- Filter by service account.
|
|
AND CASE
|
|
WHEN sqlc.narg('is_service_account') :: boolean IS NOT NULL THEN
|
|
users.is_service_account = sqlc.narg('is_service_account') :: boolean
|
|
ELSE true
|
|
END
|
|
-- End of filters
|
|
ORDER BY
|
|
-- Deterministic and consistent ordering of all users. This is to ensure consistent pagination.
|
|
LOWER(users.username) ASC OFFSET @offset_opt
|
|
LIMIT
|
|
-- A null limit means "no limit", so 0 means return all
|
|
NULLIF(@limit_opt :: int, 0);
|