I’m currently working on the user sign up/sign in/recover password functionality for Feature Upvote. I’m in danger of over-engineering it. I log every signin failure and success in the application log. I’m recording in the database User table the date/time of the last successful signin and last failed signin, and last modified date.

Should I be recording all sign ins (or recent sign ins) in the database?

Should I be recording IP address and user agent of each sign in?

My product is not dealing with financial data or anything else that is critical. I want to do things well, but I also don’t want to go overboard.

You can do that, obviously.
But some users/companies prefer emails like: S.Smith@example.com.
You can then show the original (upcase) email to the user after loging in , for example in the top right corner of your admin panel. It’s aesthetics / UX mainly.

Now the approach I take is just enough logging to spot if there is a general issue in an area.
So if I want to see if users are logging in OK, I might just log the failures (or enough info so that I know the failure %).

So… I cast a very wide net with very big holes, looking only for the biggest fish to fry.

Why not save the email as entered but use case insensitive sql for queries. Two versions is redundant. Separate table for audit is the right way to go. Depending on your needs you can even remove old entries to keep it small if needed.

Thanks for the alert. We’re currently setting up a proper production system. It seems that we’re briefly responding to https only…and even then you’ll get an AWS “Welcome” page… Should be better tomorrow.

I ask as in addition to ip address throttling and random delay an app I’ve been involved with before kept a count of # failed logins against a username (your [user] table) - when > X could block temporarily or big delay, when > Y then could block for manual review. Reset on successful login.

(Just doing IP throttling won’t pick up a distributed attack focused against an individual - guess that would be very unlikely for your app but for the work involved for # failed logins might be worth it anyway).

I believe blocking the user after failed logins is suitable for internal networks, where the security team then can proceed and find an app or person who does that - and eliminate it (cause, not person; tho who knows, these are the hard times).

In internet the find and eliminate part is simply not working, so the blocking backfires.

For internet I’d rather temporarily (for 5 minutes) block the IP from which we get a few failed attempts. That would make most script attacks on a user much harder to implement, while at the same time would be largely invisible to real users.