Delete users unconfirmed secondary emails after 3 days
What does this MR do and why?
Related to #367823 (closed)
Any user could try to add any email to their GitLab account unless the email is already used by another user. When email is added to the GitLab account even as unverified secondary email, GitLab reserves the email. Malicious users could exploit that behavior and reserve emails they do not own. Currently, The Support resolves such issues manually by removing those emails. This MR should improve Support Efficiency by automatically deleting users unconfirmed secondary emails that are created more than 3 days ago.
This MR adds cron job that is performed every hour(0 * * * *
). That cron job deletes users unconfirmed secondary emails that are created more than 3 days ago.
DB migration
bin/rails db:migrate RAILS_ENV=test
main: == [advisory_lock_connection] object_id: 116280, pg_backend_pid: 186821
main: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: migrating =====
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0050s
main: -- index_exists?(:emails, :created_at, {:where=>"confirmed_at IS NULL", :name=>"index_emails_on_created_at_where_confirmed_at_is_null", :algorithm=>:concurrently})
main: -> 0.0036s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0005s
main: -- add_index(:emails, :created_at, {:where=>"confirmed_at IS NULL", :name=>"index_emails_on_created_at_where_confirmed_at_is_null", :algorithm=>:concurrently})
main: -> 0.0019s
main: -- execute("RESET statement_timeout")
main: -> 0.0005s
main: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: migrated (0.0267s)
main: == [advisory_lock_connection] object_id: 116280, pg_backend_pid: 186821
ci: == [advisory_lock_connection] object_id: 116500, pg_backend_pid: 186823
ci: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: migrating =====
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0007s
ci: -- index_exists?(:emails, :created_at, {:where=>"confirmed_at IS NULL", :name=>"index_emails_on_created_at_where_confirmed_at_is_null", :algorithm=>:concurrently})
ci: -> 0.0034s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0006s
ci: -- add_index(:emails, :created_at, {:where=>"confirmed_at IS NULL", :name=>"index_emails_on_created_at_where_confirmed_at_is_null", :algorithm=>:concurrently})
ci: -> 0.0015s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0006s
ci: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: migrated (0.0238s)
ci: == [advisory_lock_connection] object_id: 116500, pg_backend_pid: 186823
bin/rails db:rollback:main RAILS_ENV=test
main: == [advisory_lock_connection] object_id: 115920, pg_backend_pid: 186039
main: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: reverting =====
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0042s
main: -- indexes(:emails)
main: -> 0.0040s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0005s
main: -- remove_index(:emails, {:algorithm=>:concurrently, :name=>"index_emails_on_created_at_where_confirmed_at_is_null"})
main: -> 0.0013s
main: -- execute("RESET statement_timeout")
main: -> 0.0006s
main: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: reverted (0.0260s)
main: == [advisory_lock_connection] object_id: 115920, pg_backend_pid: 186039
bin/rails db:rollback:ci RAILS_ENV=test
ci: == [advisory_lock_connection] object_id: 115920, pg_backend_pid: 186384
ci: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: reverting =====
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0039s
ci: -- indexes(:emails)
ci: -> 0.0032s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0005s
ci: -- remove_index(:emails, {:algorithm=>:concurrently, :name=>"index_emails_on_created_at_where_confirmed_at_is_null"})
ci: -> 0.0011s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0004s
ci: == 20240501103038 IndexEmailsOnCreatedAtWhereConfirmedAtIsNull: reverted (0.0271s)
ci: == [advisory_lock_connection] object_id: 115920, pg_backend_pid: 186384
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Email preview:
- https://gdk.test:3443/rails/mailers/devise_mailer/confirmation_instructions_for_secondary_email.html?locale=en
- https://gdk.test:3443/rails/mailers/devise_mailer/confirmation_instructions_for_secondary_email.txt?locale=en
How to set up and validate locally
- Sign in to a user account.
- Add secondary email to the user account, but do not verify the email address.
- Confirm that email was send to that address with sentence
Confirm this email address within 3 days, otherwise the email address is removed.
, as on the screenshot. - In rails console update the email's
created_at
attribute to3.days.ago
Email.find_by(email: 'SECONDARY_EMAIL').update!(created_at: 3.days.ago)
- Wait for next execution of
Users::UnconfirmedSecondaryEmailsDeletionCronWorker
by the cron. (It is executed every hour). - Confirm that the email address was removed
Email.where(email: 'SECONDARY_EMAIL').exists?