Skip to content

Commit a3187dc

Browse files
EmyrkEmyrk
authored
chore: enforce unique linked_ids (coder#12815)
* chore: enforce unique linked_ids Duplicate linked_ids make no sense. 2 users cannot share the same source user from a provider
1 parent 65f8d18 commit a3187dc

File tree

5 files changed

+40
-0
lines changed

5 files changed

+40
-0
lines changed

‎coderd/database/dump.sql

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

‎coderd/database/migrations/000205_unique_linked_id.down.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
DROP INDEX user_links_linked_id_login_type_idx;

‎coderd/database/migrations/000205_unique_linked_id.up.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
-- Remove the linked_id if two user_links share the same value.
2+
-- This will affect the user if they attempt to change their settings on
3+
-- the oauth/oidc provider. However, if two users exist with the same
4+
-- linked_value, there is no way to determine correctly which user should
5+
-- be updated. Since the linked_id is empty, this value will be linked
6+
-- by email.
7+
UPDATE ONLY user_links AS out
8+
SET
9+
linked_id =
10+
CASE WHEN (
11+
-- When the count of linked_id is greater than 1, set the linked_id to empty
12+
SELECT
13+
COUNT(*)
14+
FROM
15+
user_links inn
16+
WHERE
17+
out.linked_id = inn.linked_id AND out.login_type = inn.login_type
18+
) > 1 THEN '' ELSE out.linked_id END;
19+
20+
-- Enforce unique linked_id constraint on non-empty linked_id
21+
CREATE UNIQUE INDEX user_links_linked_id_login_type_idx ON user_links USING btree (linked_id, login_type) WHERE (linked_id != '');

‎coderd/database/migrations/testdata/fixtures/000048_userdelete.up.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,3 +17,18 @@ INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token
1717
-- This has happened on a production database.
1818
INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token)
1919
VALUES('fc1511ef-4fcf-4a3b-98a1-8df64160e35a', 'oidc', 'foo', '');
20+
21+
22+
-- Lastly, make 2 other users who have the same user link.
23+
INSERT INTO public.users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)
24+
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;
25+
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;
26+
INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token)
27+
VALUES('580ed397-727d-4aaf-950a-51f89f556c24', 'github', '500', '');
28+
29+
30+
INSERT INTO public.users(id, email, username, hashed_password, created_at, updated_at, status, rbac_roles, deleted)
31+
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;
32+
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;
33+
INSERT INTO public.user_links(user_id, login_type, linked_id, oauth_access_token)
34+
VALUES('c813366b-2fde-45ae-920c-101c3ad6a1e1', 'github', '500', '');

‎coderd/database/unique_constraint.go

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

0 commit comments

Comments
 (0)