mirror of
https://github.com/coder/coder.git
synced 2025-07-03 16:13:58 +00:00
4c33846f6d
Pre-requisite for https://github.com/coder/coder/pull/16891 Closes https://github.com/coder/internal/issues/515 This PR introduces a new concept of a "system" user. Our data model requires that all workspaces have an owner (a `users` relation), and prebuilds is a feature that will spin up workspaces to be claimed later by actual users - and thus needs to own the workspaces in the interim. Naturally, introducing a change like this touches a few aspects around the codebase and we've taken the approach _default hidden_ here; in other words, queries for users will by default _exclude_ all system users, but there is a flag to ensure they can be displayed. This keeps the changeset relatively small. This user has minimal permissions (it's equivalent to a `member` since it has no roles). It will be associated with the default org in the initial migration, and thereafter we'll need to somehow ensure its membership aligns with templates (which are org-scoped) for which it'll need to provision prebuilds; that's a solution we'll have in a subsequent PR. --------- Signed-off-by: Danny Kopping <dannykopping@gmail.com> Co-authored-by: Sas Swart <sas.swart.cdk@gmail.com>
100 lines
2.1 KiB
SQL
100 lines
2.1 KiB
SQL
-- name: GetGroupMembers :many
|
|
SELECT * FROM group_members_expanded
|
|
WHERE CASE
|
|
WHEN @include_system::bool THEN TRUE
|
|
ELSE
|
|
user_is_system = false
|
|
END;
|
|
|
|
-- name: GetGroupMembersByGroupID :many
|
|
SELECT *
|
|
FROM group_members_expanded
|
|
WHERE group_id = @group_id
|
|
-- Filter by system type
|
|
AND CASE
|
|
WHEN @include_system::bool THEN TRUE
|
|
ELSE
|
|
user_is_system = false
|
|
END;
|
|
|
|
-- name: GetGroupMembersCountByGroupID :one
|
|
-- Returns the total count of members in a group. Shows the total
|
|
-- count even if the caller does not have read access to ResourceGroupMember.
|
|
-- They only need ResourceGroup read access.
|
|
SELECT COUNT(*)
|
|
FROM group_members_expanded
|
|
WHERE group_id = @group_id
|
|
-- Filter by system type
|
|
AND CASE
|
|
WHEN @include_system::bool THEN TRUE
|
|
ELSE
|
|
user_is_system = false
|
|
END;
|
|
|
|
-- InsertUserGroupsByName adds a user to all provided groups, if they exist.
|
|
-- name: InsertUserGroupsByName :exec
|
|
WITH groups AS (
|
|
SELECT
|
|
id
|
|
FROM
|
|
groups
|
|
WHERE
|
|
groups.organization_id = @organization_id AND
|
|
groups.name = ANY(@group_names :: text [])
|
|
)
|
|
INSERT INTO
|
|
group_members (user_id, group_id)
|
|
SELECT
|
|
@user_id,
|
|
groups.id
|
|
FROM
|
|
groups;
|
|
|
|
-- InsertUserGroupsByID adds a user to all provided groups, if they exist.
|
|
-- name: InsertUserGroupsByID :many
|
|
WITH groups AS (
|
|
SELECT
|
|
id
|
|
FROM
|
|
groups
|
|
WHERE
|
|
groups.id = ANY(@group_ids :: uuid [])
|
|
)
|
|
INSERT INTO
|
|
group_members (user_id, group_id)
|
|
SELECT
|
|
@user_id,
|
|
groups.id
|
|
FROM
|
|
groups
|
|
-- If there is a conflict, the user is already a member
|
|
ON CONFLICT DO NOTHING
|
|
RETURNING group_id;
|
|
|
|
-- name: RemoveUserFromAllGroups :exec
|
|
DELETE FROM
|
|
group_members
|
|
WHERE
|
|
user_id = @user_id;
|
|
|
|
-- name: RemoveUserFromGroups :many
|
|
DELETE FROM
|
|
group_members
|
|
WHERE
|
|
user_id = @user_id AND
|
|
group_id = ANY(@group_ids :: uuid [])
|
|
RETURNING group_id;
|
|
|
|
-- name: InsertGroupMember :exec
|
|
INSERT INTO
|
|
group_members (user_id, group_id)
|
|
VALUES
|
|
($1, $2);
|
|
|
|
-- name: DeleteGroupMemberFromGroup :exec
|
|
DELETE FROM
|
|
group_members
|
|
WHERE
|
|
user_id = $1 AND
|
|
group_id = $2;
|