Files
coder/coderd/database/queries/organizationmembers.sql
Michael Suchacz 2c432a12a3 feat: add organization skill sharing ACL routes (#30611)
## 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.
2026-10-13 16:43:15 +00:00

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);