Skip to content

Commit 5949406

Browse files
jaaydenhjaaydenh
committed
feat: add trigger to check for resources on soft delete
1 parent 083bf7f commit 5949406

File tree

4 files changed

+81
-1
lines changed

4 files changed

+81
-1
lines changed

‎coderd/database/dump.sql

Lines changed: 36 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/migrations/000296_organization_soft_delete.down.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,3 +8,6 @@ CREATE UNIQUE INDEX IF NOT EXISTS idx_organization_name_lower ON organizations U
88

99
ALTER TABLE ONLY organizations
1010
ADD CONSTRAINT organizations_name UNIQUE (name);
11+
12+
DROP TRIGGER IF EXISTS protect_provisioned_organizations ON organizations;
13+
DROP FUNCTION IF EXISTS protect_provisioned_organizations;

‎coderd/database/migrations/000296_organization_soft_delete.up.sql

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,3 +10,44 @@ CREATE UNIQUE INDEX IF NOT EXISTS idx_organization_name_lower ON organizations U
1010

1111
ALTER TABLE ONLY organizations
1212
DROP CONSTRAINT IF EXISTS organizations_name;
13+
14+
CREATE FUNCTION protect_provisioned_organizations()
15+
RETURNS TRIGGER AS
16+
$$
17+
DECLARE
18+
workspace_count int;
19+
template_count int;
20+
BEGIN
21+
workspace_count := (
22+
SELECT count(*) as count FROM workspaces
23+
WHERE
24+
workspaces.organization_id = OLD.id
25+
AND workspaces.deleted = false
26+
);
27+
28+
template_count := (
29+
SELECT count(*) as count FROM templates
30+
WHERE
31+
templates.organization_id = OLD.id
32+
AND templates.deleted = false
33+
);
34+
35+
-- Fail the deletion if one of the following:
36+
-- * the organization has 1 or more workspaces
37+
-- * the organization has 1 or more templates
38+
IF (workspace_count + template_count) > 0 THEN
39+
RAISE EXCEPTION 'cannot delete organization: organization has % workspaces and % templates that must be deleted first', workspace_count, template_count;
40+
END IF;
41+
42+
-- add more cases to fail a delete
43+
44+
RETURN OLD;
45+
END;
46+
$$ LANGUAGE plpgsql;
47+
48+
-- Trigger to protect organizations from being soft deleted with existing resources
49+
CREATE TRIGGER protect_provisioned_organizations
50+
BEFORE UPDATE ON organizations
51+
FOR EACH ROW
52+
WHEN (NEW.deleted = true AND OLD.deleted = false)
53+
EXECUTE FUNCTION protect_provisioned_organizations();

‎docs/admin/security/audit-logs.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -23,7 +23,7 @@ We track the following resources:
2323
| NotificationsSettings<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>id</td><td>false</td></tr><tr><td>notifier_paused</td><td>true</td></tr></tbody></table> |
2424
| OAuth2ProviderApp<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>callback_url</td><td>true</td></tr><tr><td>created_at</td><td>false</td></tr><tr><td>icon</td><td>true</td></tr><tr><td>id</td><td>false</td></tr><tr><td>name</td><td>true</td></tr><tr><td>updated_at</td><td>false</td></tr></tbody></table> |
2525
| OAuth2ProviderAppSecret<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>app_id</td><td>false</td></tr><tr><td>created_at</td><td>false</td></tr><tr><td>display_secret</td><td>false</td></tr><tr><td>hashed_secret</td><td>false</td></tr><tr><td>id</td><td>false</td></tr><tr><td>last_used_at</td><td>false</td></tr><tr><td>secret_prefix</td><td>false</td></tr></tbody></table> |
26-
| Organization<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>created_at</td><td>false</td></tr><tr><td>deleted</td><td>false</td></tr><tr><td>description</td><td>true</td></tr><tr><td>display_name</td><td>true</td></tr><tr><td>icon</td><td>true</td></tr><tr><td>id</td><td>false</td></tr><tr><td>is_default</td><td>true</td></tr><tr><td>name</td><td>true</td></tr><tr><td>updated_at</td><td>true</td></tr></tbody></table> |
26+
| Organization<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>created_at</td><td>false</td></tr><tr><td>deleted</td><td>true</td></tr><tr><td>description</td><td>true</td></tr><tr><td>display_name</td><td>true</td></tr><tr><td>icon</td><td>true</td></tr><tr><td>id</td><td>false</td></tr><tr><td>is_default</td><td>true</td></tr><tr><td>name</td><td>true</td></tr><tr><td>updated_at</td><td>true</td></tr></tbody></table> |
2727
| OrganizationSyncSettings<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>assign_default</td><td>true</td></tr><tr><td>field</td><td>true</td></tr><tr><td>mapping</td><td>true</td></tr></tbody></table> |
2828
| RoleSyncSettings<br><i></i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>field</td><td>true</td></tr><tr><td>mapping</td><td>true</td></tr></tbody></table> |
2929
| Template<br><i>write, delete</i> | <table><thead><tr><th>Field</th><th>Tracked</th></tr></thead><tbody> | <tr><td>active_version_id</td><td>true</td></tr><tr><td>activity_bump</td><td>true</td></tr><tr><td>allow_user_autostart</td><td>true</td></tr><tr><td>allow_user_autostop</td><td>true</td></tr><tr><td>allow_user_cancel_workspace_jobs</td><td>true</td></tr><tr><td>autostart_block_days_of_week</td><td>true</td></tr><tr><td>autostop_requirement_days_of_week</td><td>true</td></tr><tr><td>autostop_requirement_weeks</td><td>true</td></tr><tr><td>created_at</td><td>false</td></tr><tr><td>created_by</td><td>true</td></tr><tr><td>created_by_avatar_url</td><td>false</td></tr><tr><td>created_by_username</td><td>false</td></tr><tr><td>default_ttl</td><td>true</td></tr><tr><td>deleted</td><td>false</td></tr><tr><td>deprecated</td><td>true</td></tr><tr><td>description</td><td>true</td></tr><tr><td>display_name</td><td>true</td></tr><tr><td>failure_ttl</td><td>true</td></tr><tr><td>group_acl</td><td>true</td></tr><tr><td>icon</td><td>true</td></tr><tr><td>id</td><td>true</td></tr><tr><td>max_port_sharing_level</td><td>true</td></tr><tr><td>name</td><td>true</td></tr><tr><td>organization_display_name</td><td>false</td></tr><tr><td>organization_icon</td><td>false</td></tr><tr><td>organization_id</td><td>false</td></tr><tr><td>organization_name</td><td>false</td></tr><tr><td>provisioner</td><td>true</td></tr><tr><td>require_active_version</td><td>true</td></tr><tr><td>time_til_dormant</td><td>true</td></tr><tr><td>time_til_dormant_autodelete</td><td>true</td></tr><tr><td>updated_at</td><td>false</td></tr><tr><td>user_acl</td><td>true</td></tr></tbody></table> |

0 commit comments

Comments
 (0)