Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfb3cf7b

Browse files
chore(coderd/database): create db trigger to enforce agent name uniqueness
This PR creates a new database trigger to ensure an inserted workspaceagent has a unique name within the scope of its workspace.
1 parent71a647b commitfb3cf7b

File tree

4 files changed

+262
-0
lines changed

4 files changed

+262
-0
lines changed

‎coderd/database/dump.sql

Lines changed: 42 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.
Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
DROPTRIGGER IF EXISTS workspace_agent_name_unique_triggerON workspace_agents;
2+
DROPFUNCTION IF EXISTS check_workspace_agent_name_unique();
Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
CREATE OR REPLACEFUNCTIONcheck_workspace_agent_name_unique()
2+
RETURNS TRIGGERAS $$
3+
DECLARE
4+
workspace_id_var uuid;
5+
existing_countinteger;
6+
BEGIN
7+
-- Get the workspace_id for this agent by following the relationship chain:
8+
-- workspace_agents -> workspace_resources -> provisioner_jobs -> workspace_builds -> workspaces
9+
SELECTwb.workspace_id INTO workspace_id_var
10+
FROM workspace_resources wr
11+
JOIN provisioner_jobs pjONwr.job_id=pj.id
12+
JOIN workspace_builds wbONpj.id=wb.job_id
13+
WHEREwr.id=NEW.resource_id;
14+
15+
-- If we couldn't find a workspace_id, allow the insert (might be a template import or other edge case)
16+
IF workspace_id_var ISNULL THEN
17+
RETURN NEW;
18+
END IF;
19+
20+
-- Check if there's already an agent with this name in this workspace
21+
SELECTCOUNT(*) INTO existing_count
22+
FROM workspace_agents wa
23+
JOIN workspace_resources wrONwa.resource_id=wr.id
24+
JOIN provisioner_jobs pjONwr.job_id=pj.id
25+
JOIN workspace_builds wbONpj.id=wb.job_id
26+
WHEREwb.workspace_id= workspace_id_var
27+
ANDwa.name=NEW.name
28+
ANDwa.id!=NEW.id;-- Exclude the current agent (for updates)
29+
30+
-- If there's already an agent with this name, raise an error
31+
IF existing_count>0 THEN
32+
RAISE EXCEPTION'workspace agent name "%" already exists in this workspace',NEW.name
33+
USING ERRCODE='unique_violation';
34+
END IF;
35+
36+
RETURN NEW;
37+
END;
38+
$$ LANGUAGE plpgsql;
39+
40+
CREATETRIGGERworkspace_agent_name_unique_trigger
41+
BEFORE INSERTORUPDATE OF name, resource_idON workspace_agents
42+
FOR EACH ROW
43+
EXECUTE FUNCTION check_workspace_agent_name_unique();

‎coderd/database/querier_test.go

Lines changed: 175 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,12 +4,15 @@ import (
44
"context"
55
"database/sql"
66
"encoding/json"
7+
"errors"
78
"fmt"
89
"sort"
10+
"sync/atomic"
911
"testing"
1012
"time"
1113

1214
"github.com/google/uuid"
15+
"github.com/lib/pq"
1316
"github.com/prometheus/client_golang/prometheus"
1417
"github.com/stretchr/testify/assert"
1518
"github.com/stretchr/testify/require"
@@ -4705,6 +4708,178 @@ func TestGetPresetsAtFailureLimit(t *testing.T) {
47054708
})
47064709
}
47074710

4711+
funcTestWorkspaceAgentNameUniqueTrigger(t*testing.T) {
4712+
t.Parallel()
4713+
4714+
varbuilds atomic.Int32
4715+
4716+
if!dbtestutil.WillUsePostgres() {
4717+
t.Skip("This test makes use of a database trigger not implemented in dbmem")
4718+
}
4719+
4720+
createWorkspaceWithAgent:=func(t*testing.T,db database.Store,org database.Organization,agentNamestring) (database.WorkspaceTable, database.TemplateVersion, database.WorkspaceAgent) {
4721+
t.Helper()
4722+
4723+
user:=dbgen.User(t,db, database.User{})
4724+
template:=dbgen.Template(t,db, database.Template{
4725+
OrganizationID:org.ID,
4726+
CreatedBy:user.ID,
4727+
})
4728+
templateVersion:=dbgen.TemplateVersion(t,db, database.TemplateVersion{
4729+
TemplateID: uuid.NullUUID{Valid:true,UUID:template.ID},
4730+
OrganizationID:org.ID,
4731+
CreatedBy:user.ID,
4732+
})
4733+
workspace:=dbgen.Workspace(t,db, database.WorkspaceTable{
4734+
OrganizationID:org.ID,
4735+
TemplateID:template.ID,
4736+
OwnerID:user.ID,
4737+
})
4738+
job:=dbgen.ProvisionerJob(t,db,nil, database.ProvisionerJob{
4739+
Type:database.ProvisionerJobTypeWorkspaceBuild,
4740+
OrganizationID:org.ID,
4741+
})
4742+
build:=dbgen.WorkspaceBuild(t,db, database.WorkspaceBuild{
4743+
BuildNumber:builds.Add(1),
4744+
JobID:job.ID,
4745+
WorkspaceID:workspace.ID,
4746+
TemplateVersionID:templateVersion.ID,
4747+
})
4748+
resource:=dbgen.WorkspaceResource(t,db, database.WorkspaceResource{
4749+
JobID:build.JobID,
4750+
})
4751+
agent:=dbgen.WorkspaceAgent(t,db, database.WorkspaceAgent{
4752+
ResourceID:resource.ID,
4753+
Name:agentName,
4754+
})
4755+
4756+
returnworkspace,templateVersion,agent
4757+
}
4758+
4759+
t.Run("DuplicateNamesInSameWorkspace",func(t*testing.T) {
4760+
t.Parallel()
4761+
4762+
db,_:=dbtestutil.NewDB(t)
4763+
org:=dbgen.Organization(t,db, database.Organization{})
4764+
ctx:=testutil.Context(t,testutil.WaitShort)
4765+
4766+
// Given: A workspace with an agent
4767+
workspace1,templateVersion1,agent1:=createWorkspaceWithAgent(t,db,org,"duplicate-agent")
4768+
require.Equal(t,"duplicate-agent",agent1.Name)
4769+
4770+
// When: Another agent is created for that workspace with the same name.
4771+
job2:=dbgen.ProvisionerJob(t,db,nil, database.ProvisionerJob{
4772+
Type:database.ProvisionerJobTypeWorkspaceBuild,
4773+
OrganizationID:org.ID,
4774+
})
4775+
build2:=dbgen.WorkspaceBuild(t,db, database.WorkspaceBuild{
4776+
BuildNumber:builds.Add(1),
4777+
JobID:job2.ID,
4778+
WorkspaceID:workspace1.ID,
4779+
TemplateVersionID:templateVersion1.ID,
4780+
})
4781+
resource2:=dbgen.WorkspaceResource(t,db, database.WorkspaceResource{
4782+
JobID:build2.JobID,
4783+
})
4784+
_,err:=db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
4785+
ID:uuid.New(),
4786+
CreatedAt:time.Now(),
4787+
UpdatedAt:time.Now(),
4788+
Name:"duplicate-agent",// Same name as agent1
4789+
ResourceID:resource2.ID,
4790+
AuthToken:uuid.New(),
4791+
Architecture:"amd64",
4792+
OperatingSystem:"linux",
4793+
APIKeyScope:database.AgentKeyScopeEnumAll,
4794+
})
4795+
4796+
// Then: We expect it to fail.
4797+
require.Error(t,err)
4798+
varpqErr*pq.Error
4799+
require.True(t,errors.As(err,&pqErr))
4800+
require.Equal(t,pq.ErrorCode("23505"),pqErr.Code)// unique_violation
4801+
require.Contains(t,pqErr.Message,`workspace agent name "duplicate-agent" already exists in this workspace`)
4802+
})
4803+
4804+
t.Run("SameNamesInDifferentWorkspaces",func(t*testing.T) {
4805+
t.Parallel()
4806+
4807+
agentName:="same-name-different-workspace"
4808+
4809+
db,_:=dbtestutil.NewDB(t)
4810+
org:=dbgen.Organization(t,db, database.Organization{})
4811+
4812+
// Given: A workspace with an agent
4813+
_,_,agent1:=createWorkspaceWithAgent(t,db,org,agentName)
4814+
require.Equal(t,agentName,agent1.Name)
4815+
4816+
// When: A second workspace is created with an agent having the same name
4817+
_,_,agent2:=createWorkspaceWithAgent(t,db,org,agentName)
4818+
require.Equal(t,agentName,agent2.Name)
4819+
4820+
// Then: We expect there to be different agents with the same name.
4821+
require.NotEqual(t,agent1.ID,agent2.ID)
4822+
require.Equal(t,agent1.Name,agent2.Name)
4823+
})
4824+
4825+
t.Run("NullWorkspaceID",func(t*testing.T) {
4826+
t.Parallel()
4827+
4828+
db,_:=dbtestutil.NewDB(t)
4829+
org:=dbgen.Organization(t,db, database.Organization{})
4830+
ctx:=testutil.Context(t,testutil.WaitShort)
4831+
4832+
// Given: A resource that does not belong to a workspace build (simulating template import)
4833+
orphanJob:=dbgen.ProvisionerJob(t,db,nil, database.ProvisionerJob{
4834+
Type:database.ProvisionerJobTypeTemplateVersionImport,
4835+
OrganizationID:org.ID,
4836+
})
4837+
orphanResource:=dbgen.WorkspaceResource(t,db, database.WorkspaceResource{
4838+
JobID:orphanJob.ID,
4839+
})
4840+
4841+
// And this resource has a workspace agent.
4842+
agent1,err:=db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
4843+
ID:uuid.New(),
4844+
CreatedAt:time.Now(),
4845+
UpdatedAt:time.Now(),
4846+
Name:"orphan-agent",
4847+
ResourceID:orphanResource.ID,
4848+
AuthToken:uuid.New(),
4849+
Architecture:"amd64",
4850+
OperatingSystem:"linux",
4851+
APIKeyScope:database.AgentKeyScopeEnumAll,
4852+
})
4853+
require.NoError(t,err)
4854+
require.Equal(t,"orphan-agent",agent1.Name)
4855+
4856+
// When: We created another resource that does not belong to a workspace build.
4857+
orphanJob2:=dbgen.ProvisionerJob(t,db,nil, database.ProvisionerJob{
4858+
Type:database.ProvisionerJobTypeTemplateVersionImport,
4859+
OrganizationID:org.ID,
4860+
})
4861+
orphanResource2:=dbgen.WorkspaceResource(t,db, database.WorkspaceResource{
4862+
JobID:orphanJob2.ID,
4863+
})
4864+
4865+
// Then: We expect to be able to create an agent in this new resource that has the same name.
4866+
agent2,err:=db.InsertWorkspaceAgent(ctx, database.InsertWorkspaceAgentParams{
4867+
ID:uuid.New(),
4868+
CreatedAt:time.Now(),
4869+
UpdatedAt:time.Now(),
4870+
Name:"orphan-agent",// Same name as agent1
4871+
ResourceID:orphanResource2.ID,
4872+
AuthToken:uuid.New(),
4873+
Architecture:"amd64",
4874+
OperatingSystem:"linux",
4875+
APIKeyScope:database.AgentKeyScopeEnumAll,
4876+
})
4877+
require.NoError(t,err)
4878+
require.Equal(t,"orphan-agent",agent2.Name)
4879+
require.NotEqual(t,agent1.ID,agent2.ID)
4880+
})
4881+
}
4882+
47084883
funcrequireUsersMatch(t testing.TB,expected []database.User,found []database.GetUsersRow,msgstring) {
47094884
t.Helper()
47104885
require.ElementsMatch(t,expected,database.ConvertUserRows(found),msg)

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp