MyVideoGameList/myvideogamelist.com

Watch

Fork 0

Flag Suspicious Sign-Ups That Share IPs With Risky Accounts #49

New issue

Closed

opened 2024-01-14 14:46:14 -06:00 by jimmyb · 2 comments

jimmyb commented

2024-01-14 14:46:14 -06:00

Owner

Problem

The original idea was to notify Discord whenever a new registration used an IP address that had already been used by another account. That is too broad on its own: shared networks, mobile carriers, VPNs, schools, workplaces, and household devices can all create legitimate IP reuse.

Instead, treat reused registration IPs as a moderation signal only when the match is higher risk.

Proposed Behavior

When a new user registers, compare the sign-up IP against existing users with the same IP address, excluding the newly created account.

Create a moderation/security alert only if at least one matching account is suspicious, such as:

banned = Y
user_deleted = Y
deleted_at is not null
multiple existing accounts share the same IP within a short recent window, if that data is easy to query

Do not alert for every ordinary reused IP match.

Alert Contents

The alert should include the newly registered account:

Username
IP address
Created at

For each matching risky account, include:

Username
Banned
User deleted
Created at
Updated at
Last activity date
Deleted at

Implementation Notes

The users table already stores registration IPs in ip_address.
Registration is handled by App\Actions\Fortify\CreateNewUser.
Prefer a structured internal audit/security event if that pattern exists by implementation time.
Discord is acceptable only as the delivery channel for high-signal alerts, not as the entire audit trail.

Acceptance Criteria

New registrations do not trigger an alert when the IP only matches normal active accounts.
New registrations trigger an alert when the IP matches at least one banned, user-deleted, or soft-deleted account.
The alert includes the new account and risky matching account details listed above.
Tests cover both the no-alert ordinary IP reuse path and the high-signal alert path.

## Problem The original idea was to notify Discord whenever a new registration used an IP address that had already been used by another account. That is too broad on its own: shared networks, mobile carriers, VPNs, schools, workplaces, and household devices can all create legitimate IP reuse. Instead, treat reused registration IPs as a moderation signal only when the match is higher risk. ## Proposed Behavior When a new user registers, compare the sign-up IP against existing users with the same IP address, excluding the newly created account. Create a moderation/security alert only if at least one matching account is suspicious, such as: - banned = Y - user_deleted = Y - deleted_at is not null - multiple existing accounts share the same IP within a short recent window, if that data is easy to query Do not alert for every ordinary reused IP match. ## Alert Contents The alert should include the newly registered account: - Username - IP address - Created at For each matching risky account, include: - Username - Banned - User deleted - Created at - Updated at - Last activity date - Deleted at ## Implementation Notes - The users table already stores registration IPs in ip_address. - Registration is handled by App\\Actions\\Fortify\\CreateNewUser. - Prefer a structured internal audit/security event if that pattern exists by implementation time. - Discord is acceptable only as the delivery channel for high-signal alerts, not as the entire audit trail. ## Acceptance Criteria - [x] New registrations do not trigger an alert when the IP only matches normal active accounts. - [x] New registrations trigger an alert when the IP matches at least one banned, user-deleted, or soft-deleted account. - [x] The alert includes the new account and risky matching account details listed above. - [x] Tests cover both the no-alert ordinary IP reuse path and the high-signal alert path.

jimmyb added the

type: feature

component: backend

component: internal notifications

labels

2024-01-14 14:46:14 -06:00

Codex changed title from ~~Add Discord Notification For New Account Sign-Ups From Previously Used IP~~ to Flag Suspicious Sign-Ups That Share IPs With Risky Accounts

2026-06-01 23:28:16 -05:00

jimmyb added this to the New Features - Part 1 project

2026-06-03 23:54:20 -05:00

Codex added the

status

in progress

label

2026-10-15 08:24:30 -05:00

Codex self-assigned this

2026-10-15 08:24:30 -05:00

Codex referenced this issue from a pull request that will close it,

2026-10-15 08:54:00 -05:00

Flag suspicious registrations sharing risky account IPs #974

jimmyb closed this issue

2026-10-15 09:28:39 -05:00

Codex commented

2026-10-15 09:32:36 -05:00

Member

Implemented and merged in PR #974 (master merge revision 378a0344).

A registration alerts when its stored IP matches a banned account (including role ID 7), a user-deleted account, or a soft-deleted account. The new account is excluded from matching.
The agreed burst rule also alerts on the third registration from an IP when two existing accounts were created within the preceding seven days. Ordinary reuse below that threshold stays quiet.
Full account snapshots and trigger reasons are persisted in the internal security activity log as suspicious_registration; they are excluded from public activity feeds and follow the existing 365-day cleanup policy.
Optional Discord delivery uses DISCORD_WEBHOOK_URL, an asynchronous queue worker, bounded retries, and resumable paginated embeds. Embeds include admin edit links, Central-time dates with CDT/CST, and inline-code NULL for missing activity/deletion dates. Delivery failures preserve the registration and audit record.
The additive migration creates the (ip_address, created_at) lookup index. It must be applied with the deployment environment explicitly selected.

Validation: 48 focused tests passed, covering both acceptance paths, every risk flag, burst boundaries, IPv6, snapshots, rollback, delivery failures, embed limits, index rollback, and audit retention. Pint and PHPStan passed. Separate live mock notifications were accepted by Discord and visually reviewed during implementation. The opt-in live-delivery test stays skipped in normal automated runs.

All four acceptance criteria are complete. Dev and production deployment follows this closeout; deployment results will be recorded separately.

Implemented and merged in PR #974 (master merge revision `378a0344`). - A registration alerts when its stored IP matches a banned account (including role ID 7), a user-deleted account, or a soft-deleted account. The new account is excluded from matching. - The agreed burst rule also alerts on the third registration from an IP when two existing accounts were created within the preceding seven days. Ordinary reuse below that threshold stays quiet. - Full account snapshots and trigger reasons are persisted in the internal `security` activity log as `suspicious_registration`; they are excluded from public activity feeds and follow the existing 365-day cleanup policy. - Optional Discord delivery uses `DISCORD_WEBHOOK_URL`, an asynchronous queue worker, bounded retries, and resumable paginated embeds. Embeds include admin edit links, Central-time dates with CDT/CST, and inline-code `NULL` for missing activity/deletion dates. Delivery failures preserve the registration and audit record. - The additive migration creates the `(ip_address, created_at)` lookup index. It must be applied with the deployment environment explicitly selected. Validation: 48 focused tests passed, covering both acceptance paths, every risk flag, burst boundaries, IPv6, snapshots, rollback, delivery failures, embed limits, index rollback, and audit retention. Pint and PHPStan passed. Separate live mock notifications were accepted by Discord and visually reviewed during implementation. The opt-in live-delivery test stays skipped in normal automated runs. All four acceptance criteria are complete. Dev and production deployment follows this closeout; deployment results will be recorded separately.

Codex added

and removed

labels

2026-10-15 09:32:36 -05:00

Codex commented

2026-10-15 09:39:38 -05:00

Member

Deployment completed for dev and production at merged revision 378a03443d7261b5160d961eb0a6bd88b5f178f6.

Applied only 2026_10_15_120000_add_registration_ip_index_to_users_table to the explicitly verified mvgldev_dev and mvgl_prod databases. Both now have the (ip_address, created_at) lookup index.
Rebuilt configuration, route, and view caches. Existing DISCORD_WEBHOOK_URL values are configured and the security activity log is enabled without buffering.
Enabled persistent mvgl-queue.service user services for both deployment accounts, with user lingering enabled for boot/session-independent processing. Manage the worker as the respective account with systemctl --user status|restart mvgl-queue.service.
Verified real database-queued mock Discord deliveries in both environments. Temporary mock audit records were removed; no real accounts were created. Both queues report zero pending and zero failed jobs.
Dev and production homepage and registration endpoints returned HTTP 200. Verified all 420 staged runtime files on production and 413 on dev; seven pre-existing dev overlay files belonging to the pending-game workflow were intentionally preserved, along with its existing built assets.
Installed the committed Composer lock versions, including the separately merged Flysystem S3 adapter update to 3.35.3. Existing dev/production dependency-install modes were preserved.

Source and vendor rollback backups are available on the deployment host:

Dev: /home/mvgldev/deploy-backups/issue49-378a0344-6fd7mkew
Production: /home/mvgl/deploy-backups/issue49-378a0344-n115htmf

Each environment also has a deployment receipt under its releases/issue49-378a0344 directory. The primary local checkout is back on master, pulled to the merged revision, with no local changes.

Deployment completed for dev and production at merged revision `378a03443d7261b5160d961eb0a6bd88b5f178f6`. - Applied only `2026_10_15_120000_add_registration_ip_index_to_users_table` to the explicitly verified `mvgldev_dev` and `mvgl_prod` databases. Both now have the `(ip_address, created_at)` lookup index. - Rebuilt configuration, route, and view caches. Existing `DISCORD_WEBHOOK_URL` values are configured and the security activity log is enabled without buffering. - Enabled persistent `mvgl-queue.service` user services for both deployment accounts, with user lingering enabled for boot/session-independent processing. Manage the worker as the respective account with `systemctl --user status|restart mvgl-queue.service`. - Verified real database-queued mock Discord deliveries in both environments. Temporary mock audit records were removed; no real accounts were created. Both queues report zero pending and zero failed jobs. - Dev and production homepage and registration endpoints returned HTTP 200. Verified all 420 staged runtime files on production and 413 on dev; seven pre-existing dev overlay files belonging to the pending-game workflow were intentionally preserved, along with its existing built assets. - Installed the committed Composer lock versions, including the separately merged Flysystem S3 adapter update to 3.35.3. Existing dev/production dependency-install modes were preserved. Source and vendor rollback backups are available on the deployment host: - Dev: `/home/mvgldev/deploy-backups/issue49-378a0344-6fd7mkew` - Production: `/home/mvgl/deploy-backups/issue49-378a0344-n115htmf` Each environment also has a deployment receipt under its `releases/issue49-378a0344` directory. The primary local checkout is back on `master`, pulled to the merged revision, with no local changes.

Codex referenced this issue

2026-10-27 11:25:50 -05:00

Improve admin stats and restore Cloudflare visitor IPs #1000