coder
diff --git a/‎coderd/database/check_constraint.go‎
Lines changed: 1 addition & 0 deletions b/‎coderd/database/check_constraint.go‎
Lines changed: 1 addition & 0 deletions
diff --git a/‎coderd/database/dump.sql‎
Lines changed: 3 additions & 2 deletions b/‎coderd/database/dump.sql‎
Lines changed: 3 additions & 2 deletions
diff --git a/‎coderd/database/migrations/000404_allow_same_role_name_in_different_orgs.down.sql‎
Lines changed: 6 additions & 0 deletions b/‎coderd/database/migrations/000404_allow_same_role_name_in_different_orgs.down.sql‎
Lines changed: 6 additions & 0 deletions
diff --git a/‎coderd/database/migrations/000404_allow_same_role_name_in_different_orgs.up.sql‎
Lines changed: 28 additions & 0 deletions b/‎coderd/database/migrations/000404_allow_same_role_name_in_different_orgs.up.sql‎
Lines changed: 28 additions & 0 deletions
diff --git a/‎coderd/database/unique_constraint.go‎
Lines changed: 1 addition & 1 deletion b/‎coderd/database/unique_constraint.go‎
Lines changed: 1 addition & 1 deletion
@@ -0,0 +1,6 @@
+-- Restore the original unique constraint (name only, no organization_id).
+DROP INDEX IF EXISTS idx_custom_roles_name_lower_organization_id;
+
+ALTER TABLE custom_roles DROP CONSTRAINT IF EXISTS organization_id_not_zero;
+
+CREATE UNIQUE INDEX idx_custom_roles_name_lower ON custom_roles USING btree (LOWER(name));
@@ -0,0 +1,28 @@
+-- Fix the unique index in `custom_roles` to allow the same role name
+-- in different organizations. The original index only covered name,
+-- but names don't have to be unique across different organizations.
+--
+-- Note: after fixing it, we end up with an almost-replica of the
+-- existing `custom_roles_unique_key` constraint. That's unfortunate,
+-- but since we can't define a constraint on an expression (e.g. lower()),
+-- we'll have to keep both of them.
+DROP INDEX IF EXISTS idx_custom_roles_name_lower;
+
+-- Use `COALESCE` to handle `NULL` organization_id. Site-wide custom
+-- roles are currently not used, but that can change in the future and
+-- this will become necessary. And there are no performance implications.
+--
+-- Note: Using `NULLS NOT DISTINCT` instead of `COALESCE` here would
+-- limit us to PG15+.
+
+-- Paranoia check.
+UPDATE custom_roles SET organization_id = NULL WHERE organization_id = '00000000-0000-0000-0000-000000000000';
+
+ALTER TABLE custom_roles
+    ADD CONSTRAINT organization_id_not_zero
+    CHECK (organization_id <> '00000000-0000-0000-0000-000000000000'::uuid);
+
+CREATE UNIQUE INDEX idx_custom_roles_name_lower_organization_id ON custom_roles USING btree (
+    LOWER(name),
+    COALESCE(organization_id, '00000000-0000-0000-0000-000000000000'::uuid)
+);