fix: add postgres triggers to keep user_links clear of deleted users

Emyrk · Emyrk · commit c42625941ac4 · 2024-02-13T13:38:13.000-06:00
diff --git a/coderd/database/dump.sql b/coderd/database/dump.sql
diff --git a/coderd/database/migrations/000190_trigger_delete_user_user_link.down.sql b/coderd/database/migrations/000190_trigger_delete_user_user_link.down.sql
@@ -0,0 +1,26 @@
+DROP TRIGGER IF EXISTS trigger_update_users ON users;
+DROP FUNCTION IF EXISTS delete_deleted_user_resources;
+
+DROP TRIGGER IF EXISTS trigger_upsert_user_links ON user_links;
+DROP FUNCTION IF EXISTS insert_user_links_fail_if_user_deleted;
+
+-- Restore the previous trigger
+CREATE FUNCTION delete_deleted_user_api_keys() RETURNS trigger
+	LANGUAGE plpgsql
+AS $$
+DECLARE
+BEGIN
+	IF (NEW.deleted) THEN
+		DELETE FROM api_keys
+		WHERE user_id = OLD.id;
+	END IF;
+	RETURN NEW;
+END;
+$$;
+
+
+CREATE TRIGGER trigger_update_users
+	AFTER INSERT OR UPDATE ON users
+	FOR EACH ROW
+	WHEN (NEW.deleted = true)
+EXECUTE PROCEDURE delete_deleted_user_api_keys();
diff --git a/coderd/database/migrations/000190_trigger_delete_user_user_link.up.sql b/coderd/database/migrations/000190_trigger_delete_user_user_link.up.sql
@@ -0,0 +1,66 @@
+-- We need to delete all existing user_links for soft-deleted users
+DELETE FROM
+	user_links
+WHERE
+	user_id
+		IN (
+		SELECT id FROM users WHERE deleted
+	);
+
+-- Drop the old trigger
+DROP TRIGGER trigger_update_users ON users;
+-- Drop the old function
+DROP FUNCTION delete_deleted_user_api_keys;
+
+-- When we soft-delete a user, we also want to delete their API key.
+-- The previous function deleted all api keys. This extends that with user_links.
+CREATE FUNCTION delete_deleted_user_resources() RETURNS trigger
+	LANGUAGE plpgsql
+AS $$
+DECLARE
+BEGIN
+	IF (NEW.deleted) THEN
+		-- Remove their api_keys
+		DELETE FROM api_keys
+		WHERE user_id = OLD.id;
+
+		-- Remove their user_links
+		-- Their login_type is preserved in the users table.
+		-- Matching this user back to the link can still be done by their
+		-- email if the account is undeleted. Although that is not a guarantee.
+		DELETE FROM user_links
+		WHERE user_id = OLD.id;
+	END IF;
+	RETURN NEW;
+END;
+$$;
+
+
+-- Update it to the new trigger
+CREATE TRIGGER trigger_update_users
+	AFTER INSERT OR UPDATE ON users
+	FOR EACH ROW
+	WHEN (NEW.deleted = true)
+EXECUTE PROCEDURE delete_deleted_user_resources();
+
+
+-- Prevent adding new user_links for soft-deleted users
+CREATE FUNCTION insert_user_links_fail_if_user_deleted() RETURNS trigger
+	LANGUAGE plpgsql
+AS $$
+
+DECLARE
+BEGIN
+	IF (NEW.user_id IS NOT NULL) THEN
+		IF (SELECT deleted FROM users WHERE id = NEW.user_id LIMIT 1) THEN
+			RAISE EXCEPTION 'Cannot create user_link for deleted user';
+		END IF;
+	END IF;
+	RETURN NEW;
+END;
+$$;
+
+CREATE TRIGGER trigger_upsert_user_links
+	BEFORE INSERT OR UPDATE ON user_links
+	FOR EACH ROW
+EXECUTE PROCEDURE insert_user_links_fail_if_user_deleted();
