|
| 1 | +DROP TRIGGER IF EXISTS protect_deleting_organizations ON organizations; |
| 2 | + |
| 3 | +-- Replace the function with the new implementation |
| 4 | +CREATE OR REPLACE FUNCTION protect_deleting_organizations() |
| 5 | + RETURNS TRIGGER AS |
| 6 | +$$ |
| 7 | +DECLARE |
| 8 | + workspace_count int; |
| 9 | + template_count int; |
| 10 | + group_count int; |
| 11 | + member_count int; |
| 12 | + provisioner_keys_count int; |
| 13 | +BEGIN |
| 14 | + workspace_count := ( |
| 15 | + SELECT count(*) as count FROM workspaces |
| 16 | + WHERE |
| 17 | + workspaces.organization_id = OLD.id |
| 18 | + AND workspaces.deleted = false |
| 19 | + ); |
| 20 | + |
| 21 | + template_count := ( |
| 22 | + SELECT count(*) as count FROM templates |
| 23 | + WHERE |
| 24 | + templates.organization_id = OLD.id |
| 25 | + AND templates.deleted = false |
| 26 | + ); |
| 27 | + |
| 28 | + group_count := ( |
| 29 | + SELECT count(*) as count FROM groups |
| 30 | + WHERE |
| 31 | + groups.organization_id = OLD.id |
| 32 | + ); |
| 33 | + |
| 34 | + member_count := ( |
| 35 | + SELECT count(*) as count FROM organization_members |
| 36 | + WHERE |
| 37 | + organization_members.organization_id = OLD.id |
| 38 | + ); |
| 39 | + |
| 40 | + provisioner_keys_count := ( |
| 41 | + Select count(*) as count FROM provisioner_keys |
| 42 | + WHERE |
| 43 | + provisioner_keys.organization_id = OLD.id |
| 44 | + ); |
| 45 | + |
| 46 | + -- Fail the deletion if one of the following: |
| 47 | + -- * the organization has 1 or more workspaces |
| 48 | + -- * the organization has 1 or more templates |
| 49 | + -- * the organization has 1 or more groups other than "Everyone" group |
| 50 | + -- * the organization has 1 or more members other than the organization owner |
| 51 | + -- * the organization has 1 or more provisioner keys |
| 52 | + |
| 53 | + -- Only create error message for resources that actually exist |
| 54 | + IF (workspace_count + template_count + provisioner_keys_count) > 0 THEN |
| 55 | + DECLARE |
| 56 | + error_message text := 'cannot delete organization: organization has '; |
| 57 | + error_parts text[] := '{}'; |
| 58 | + BEGIN |
| 59 | + IF workspace_count > 0 THEN |
| 60 | + error_parts := array_append(error_parts, workspace_count || ' workspaces'); |
| 61 | + END IF; |
| 62 | + |
| 63 | + IF template_count > 0 THEN |
| 64 | + error_parts := array_append(error_parts, template_count || ' templates'); |
| 65 | + END IF; |
| 66 | + |
| 67 | + IF provisioner_keys_count > 0 THEN |
| 68 | + error_parts := array_append(error_parts, provisioner_keys_count || ' provisioner keys'); |
| 69 | + END IF; |
| 70 | + |
| 71 | + error_message := error_message || array_to_string(error_parts, ', ') || ' that must be deleted first'; |
| 72 | + RAISE EXCEPTION '%', error_message; |
| 73 | + END; |
| 74 | + END IF; |
| 75 | + |
| 76 | + IF (group_count) > 1 THEN |
| 77 | + RAISE EXCEPTION 'cannot delete organization: organization has % groups that must be deleted first', group_count - 1; |
| 78 | + END IF; |
| 79 | + |
| 80 | + -- Allow 1 member to exist, because you cannot remove yourself. You can |
| 81 | + -- remove everyone else. Ideally, we only omit the member that matches |
| 82 | + -- the user_id of the caller, however in a trigger, the caller is unknown. |
| 83 | + IF (member_count) > 1 THEN |
| 84 | + RAISE EXCEPTION 'cannot delete organization: organization has % members that must be deleted first', member_count - 1; |
| 85 | + END IF; |
| 86 | + |
| 87 | + RETURN NEW; |
| 88 | +END; |
| 89 | +$$ LANGUAGE plpgsql; |
| 90 | + |
| 91 | +-- Trigger to protect organizations from being soft deleted with existing resources |
| 92 | +CREATE TRIGGER protect_deleting_organizations |
| 93 | + BEFORE UPDATE ON organizations |
| 94 | + FOR EACH ROW |
| 95 | + WHEN (NEW.deleted = true AND OLD.deleted = false) |
| 96 | + EXECUTE FUNCTION protect_deleting_organizations(); |
0 commit comments