Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite4624b5

Browse files
committed
fix: add postgres triggers to keep user_links clear of deleted users
1 parent24e767f commite4624b5

File tree

3 files changed

+119
-2
lines changed

3 files changed

+119
-2
lines changed

‎coderd/database/dump.sql

Lines changed: 27 additions & 2 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
DROPTRIGGER IF EXISTS trigger_update_usersON users;
2+
DROPFUNCTION IF EXISTS delete_deleted_user_resources;
3+
4+
DROPTRIGGER IF EXISTS trigger_upsert_user_linksON user_links;
5+
DROPFUNCTION IF EXISTS insert_user_links_fail_if_user_deleted;
6+
7+
-- Restore the previous trigger
8+
CREATEFUNCTIONdelete_deleted_user_api_keys() RETURNS trigger
9+
LANGUAGE plpgsql
10+
AS $$
11+
DECLARE
12+
BEGIN
13+
IF (NEW.deleted) THEN
14+
DELETEFROM api_keys
15+
WHERE user_id=OLD.id;
16+
END IF;
17+
RETURN NEW;
18+
END;
19+
$$;
20+
21+
22+
CREATETRIGGERtrigger_update_users
23+
AFTER INSERTORUPDATEON users
24+
FOR EACH ROW
25+
WHEN (NEW.deleted= true)
26+
EXECUTE PROCEDURE delete_deleted_user_api_keys();
Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
-- We need to delete all existing user_links for soft-deleted users
2+
DELETEFROM
3+
user_links
4+
WHERE
5+
user_id
6+
IN (
7+
SELECT idFROM usersWHERE deleted
8+
);
9+
10+
-- Drop the old trigger
11+
DROPTRIGGER trigger_update_usersON users;
12+
-- Drop the old function
13+
DROPFUNCTION delete_deleted_user_api_keys;
14+
15+
-- When we soft-delete a user, we also want to delete their API key.
16+
-- The previous function deleted all api keys. This extends that with user_links.
17+
CREATEFUNCTIONdelete_deleted_user_resources() RETURNS trigger
18+
LANGUAGE plpgsql
19+
AS $$
20+
DECLARE
21+
BEGIN
22+
IF (NEW.deleted) THEN
23+
-- Remove their api_keys
24+
DELETEFROM api_keys
25+
WHERE user_id=OLD.id;
26+
27+
-- Remove their user_links
28+
-- Their login_type is preserved in the users table.
29+
-- Matching this user back to the link can still be done by their
30+
-- email if the account is undeleted. Although that is not a guarantee.
31+
DELETEFROM user_links
32+
WHERE user_id=OLD.id;
33+
END IF;
34+
RETURN NEW;
35+
END;
36+
$$;
37+
38+
39+
-- Update it to the new trigger
40+
CREATETRIGGERtrigger_update_users
41+
AFTER INSERTORUPDATEON users
42+
FOR EACH ROW
43+
WHEN (NEW.deleted= true)
44+
EXECUTE PROCEDURE delete_deleted_user_resources();
45+
46+
47+
-- Prevent adding new user_links for soft-deleted users
48+
CREATEFUNCTIONinsert_user_links_fail_if_user_deleted() RETURNS trigger
49+
LANGUAGE plpgsql
50+
AS $$
51+
52+
DECLARE
53+
BEGIN
54+
IF (NEW.user_idIS NOT NULL) THEN
55+
IF (SELECT deletedFROM usersWHERE id=NEW.user_idLIMIT1) THEN
56+
RAISE EXCEPTION'Cannot create user_link for deleted user';
57+
END IF;
58+
END IF;
59+
RETURN NEW;
60+
END;
61+
$$;
62+
63+
CREATETRIGGERtrigger_upsert_user_links
64+
BEFORE INSERTORUPDATEON user_links
65+
FOR EACH ROW
66+
EXECUTE PROCEDURE insert_user_links_fail_if_user_deleted();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp