chore: enforce unique linked_ids

Duplicate linked_ids make no sense. 2 users cannot share the same source user from a provider
coder · Emyrk · Apr 3, 2024 · Mar 28, 2024 · Mar 28, 2024 · Mar 29, 2024
commit 09aa12f8f2e1dac8289b5ed7417e23854e2675c4
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000205_unique_linked_id.down.sql b/coderd/database/migrations/000205_unique_linked_id.down.sql
@@ -0,0 +1 @@
+DROP INDEX idx_user_link_linked_id;
diff --git a/coderd/database/migrations/000205_unique_linked_id.up.sql b/coderd/database/migrations/000205_unique_linked_id.up.sql
@@ -0,0 +1,18 @@
+-- Remove the linked_id if two user_links share the same value.
+-- This will affect the user if they attempt to change their settings on
+-- the oauth/oidc provider. However, if two users exist with the same
+-- linked_value, there is no way to determine correctly which user should
+-- be updated. Since the linked_id is empty, this value will be linked
+-- by email.
+UPDATE ONLY user_links out
-UPDATE ONLY user_links out
+UPDATE ONLY user_links AS out
-UPDATE ONLY user_links out
+UPDATE ONLY user_links AS out
+SET
+	linked_id =
+		CASE WHEN (
+			  -- When the count of linked_id is greater than 1, set the linked_id to empty
+			  SELECT  COUNT(*)
+			  FROM    user_links inn
+			  WHERE   out.linked_id = inn.linked_id
+		  ) > 1 THEN '' ELSE out.linked_id END;
+
+-- Enforce unique linked_id constraint on non-empty linked_id
+CREATE UNIQUE INDEX idx_user_link_linked_id ON user_links USING btree (linked_id) WHERE (linked_id != '');
diff --git a/coderd/database/migrations/testdata/fixtures/000048_userdelete.up.sql b/coderd/database/migrations/testdata/fixtures/000048_userdelete.up.sql
@@ -17,3 +17,18 @@ INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token
 -- This has happened on a production database.
 INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token)
 VALUES('fc1511ef-4fcf-4a3b-98a1-8df64160e35a', 'oidc', 'foo', '');
+
+
+-- Lastly, make 2 other users who have the same user link.
+INSERT INTO public.users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)
+VALUES ('580ed397-727d-4aaf-950a-51f89f556c24', 'dup_link_a@coder.com', 'dupe_a', '\x', '2022-11-02 13:05:21.445455+02', '2022-11-02 13:05:21.445455+02', 'active', '{}', false) ON CONFLICT DO NOTHING;
+INSERT INTO public.organization_members VALUES ('580ed397-727d-4aaf-950a-51f89f556c24', 'bb640d07-ca8a-4869-b6bc-ae61ebb2fda1', '2022-11-02 13:05:21.447595+02', '2022-11-02 13:05:21.447595+02', '{}') ON CONFLICT DO NOTHING;
+INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token)
+VALUES('580ed397-727d-4aaf-950a-51f89f556c24', 'github', '500', '');
+
+
+INSERT INTO public.users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)
+VALUES ('c813366b-2fde-45ae-920c-101c3ad6a1e1', 'dup_link_b@coder.com', 'dupe_b', '\x', '2022-11-02 13:05:21.445455+02', '2022-11-02 13:05:21.445455+02', 'active', '{}', false) ON CONFLICT DO NOTHING;
+INSERT INTO public.organization_members VALUES ('c813366b-2fde-45ae-920c-101c3ad6a1e1', 'bb640d07-ca8a-4869-b6bc-ae61ebb2fda1', '2022-11-02 13:05:21.447595+02', '2022-11-02 13:05:21.447595+02', '{}') ON CONFLICT DO NOTHING;
+INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token)
+VALUES('c813366b-2fde-45ae-920c-101c3ad6a1e1', 'github', '500', '');
diff --git a/coderd/database/unique_constraint.go b/coderd/database/unique_constraint.go