Check for banned user normalized email reuse as part of model validation
This is an enhancement for https://gitlab.com/gitlab-org/modelops/anti-abuse/team-tasks/-/issues/815
What does this MR do and why?
Instead of triggering the check for banned user normalized email reuse in a before_action
hook as previously implemented (see !161122 (merged)), trigger it as part of the User
model validation step and only when there are no other email validation errors already present. This prevents the additional DB query for the check which results to a lower priority action prompt for the user compared to the earlier validation errors (e.g. email is not unique).
Database changes
Users::BannedUser.by_detumbled_email
scope
Change: added .where.not({ emails: { confirmed_at: nil } })
condition.
Raw SQL
SELECT
"banned_users".*
FROM
"banned_users"
INNER JOIN "emails" ON "emails"."user_id" = "banned_users"."user_id"
WHERE
"emails"."detumbled_email" = 'users_email@example.com'
AND "emails"."confirmed_at" IS NOT NULL
Explain
https://console.postgres.ai/shared/9605bc5e-4874-4c24-9d64-81ed194baf06
Nested Loop (cost=0.98..7.02 rows=1 width=24) (actual time=26.658..26.665 rows=1 loops=1)
Buffers: shared hit=5 read=7
I/O Timings: read=25.573 write=0.000
-> Index Scan using index_emails_on_detumbled_email on public.emails (cost=0.56..3.58 rows=1 width=4) (actual time=21.637..21.642 rows=1 loops=1)
Index Cond: (emails.detumbled_email = 'users_email@example.com'::text)
Filter: (emails.confirmed_at IS NOT NULL)
Rows Removed by Filter: 0
Buffers: shared read=5
I/O Timings: read=21.228 write=0.000
-> Index Scan using banned_users_pkey on public.banned_users (cost=0.42..3.44 rows=1 width=24) (actual time=5.008..5.008 rows=1 loops=1)
Index Cond: (banned_users.user_id = emails.user_id)
Buffers: shared hit=5 read=2
I/O Timings: read=4.345 write=0.000
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
Before | After |
---|---|
How to set up and validate locally
- Register a new user. Take note of the email you used
- In Rails console, ban the newly created user and enable the feature flag
> rails c > User.last.ban! > Feature.enable(:block_banned_user_normalized_email_reuse)
- Go to the signup page again and try to register a new user with a tumbled version of the banned user's email. For example, if you used
my_user@ex.com
in step 1, you can usemy_user+letmereuse@ex.com
- Verify that the registration is blocked and the
Email not allowed. ...
error is displayed