alter query

johnstcn · johnstcn · commit 8e2430d76510 · 2023-08-18T16:26:29.000Z
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/workspaceagents.sql b/coderd/database/queries/workspaceagents.sql
@@ -199,31 +199,10 @@ SELECT
 	users.username AS owner_name,
 	users.status AS owner_status,
 	array_cat(
-		-- All users are members
 		array_append(users.rbac_roles, 'member'),
-		(
-			SELECT
-				array_agg(org_roles)
-			FROM
-				organization_members,
-				-- All org_members get the org-member role for their orgs
-				unnest(
-					array_append(roles, 'organization-member:' || organization_members.organization_id::text)
-					) AS org_roles
-			WHERE
-					user_id = users.id
-		)
-		) :: text[] AS owner_roles,
-	(
-		SELECT
-			array_agg(
-				group_members.group_id :: text
-				)
-		FROM
-			group_members
-		WHERE
-				user_id = users.id
-	) :: text[] AS owner_groups
+		array_append(ARRAY[]::text[], 'organization-member:' || organization_members.organization_id::text)
+		)::text[] as owner_roles,
+	array_agg(COALESCE(group_members.group_id::text, ''))::text[] AS owner_groups
 FROM users
 		 INNER JOIN
 	 workspaces
@@ -241,6 +220,18 @@ FROM users
 	 workspace_agents
 	 ON
 			 workspace_agents.resource_id = workspace_resources.id
+		 INNER JOIN -- every user is a member of some org
+	organization_members
+					ON
+							organization_members.user_id = users.id
+		 LEFT JOIN -- as they may not be a member of any groups
+	group_members
+				   ON
+						   group_members.user_id = users.id
 WHERE
 		workspace_agents.auth_token = @auth_token
+  AND
+		users.status = 'active' -- workspaces that belong to inactive users should not be
+GROUP BY
+	workspace_agents.id, workspaces.id, users.id, organization_members.organization_id
 LIMIT 1;
diff --git a/coderd/httpmw/workspaceagent_test.go b/coderd/httpmw/workspaceagent_test.go
@@ -67,7 +67,13 @@ func TestWorkspaceAgent(t *testing.T) {
 func setup(t testing.TB, db database.Store, authToken uuid.UUID, mw func(http.Handler) http.Handler) (*http.Request, http.Handler) {
 	t.Helper()
 	org := dbgen.Organization(t, db, database.Organization{})
-	user := dbgen.User(t, db, database.User{})
+	user := dbgen.User(t, db, database.User{
+		Status: database.UserStatusActive,
+	})
+	_ = dbgen.OrganizationMember(t, db, database.OrganizationMember{
+		UserID:         user.ID,
+		OrganizationID: org.ID,
+	})
 	templateVersion := dbgen.TemplateVersion(t, db, database.TemplateVersion{
 		OrganizationID: org.ID,
 		CreatedBy:      user.ID,
