Alert security to repeated failed logins
Post to Slack when one user fails to log in five times in 15 minutes, from MariaDB, once per user per day, so security hears about it without new app code.
- MariaDB
- Slack
- Any REST API
- Security
- once per user per day
The trigger
One read-only SELECT against MariaDB. Rowfire polls it for new rows, and the rule fires once per user per day.
SELECT la.user_id,
u.email,
la.ip_address,
la.attempted_at AS failed_at,
COUNT(prev.id) AS failures_in_15_min
FROM login_attempts la
JOIN users u ON u.id = la.user_id
JOIN login_attempts prev
ON prev.user_id = la.user_id
AND prev.succeeded = 0
AND prev.attempted_at > la.attempted_at - INTERVAL 15 MINUTE
AND prev.attempted_at <= la.attempted_at
WHERE la.succeeded = 0
AND u.is_test = 0
GROUP BY la.id, la.user_id, u.email, la.ip_address, la.attempted_at
HAVING COUNT(prev.id) >= 5Your login endpoint already writes a row to login_attempts for every try,
with succeeded = 0 when the password is wrong. Five of those for one account
in a quarter of an hour is worth a look. But nothing in the app announces it,
and nobody reads that table until after something has gone wrong.
The query finds that moment. For each failed attempt, it counts the same
user’s failures in the 15 minutes up to it, and returns the attempt once that
count reaches five. The clock is failed_at, the time of that attempt. The key
is user_id, so the rule fires once per user per day, however many more
failures follow. Internal test accounts are filtered out, so a QA script that
retries a bad password doesn’t page anyone. Table and column names are a
sketch; adapt them to your schema.
Because the window is measured from each attempt rather than from now, a
backtest over past weeks shows how often this would have fired before anything
sends. On a large table, add AND la.attempted_at >= :since to the query: a
query that names :since gets the poll’s lower bound directly, so MariaDB can
use the index on attempted_at.
The Slack side
Bind the Slack send message action to #security:
:lock: {{ failures_in_15_min }} failed logins in 15 minutes for {{ email }} (user {{ user_id }}). Latest from {{ ip_address }} at {{ failed_at }}.
Your own API on the same trigger
If your app has an admin endpoint that forces a password reset, add it as an integration and bind it to a second rule on the same trigger:
POST /internal/users/{{ user_id }}/require-password-reset
Each rule keeps its own count, so the Slack post can be live while this rule runs in shadow, where every request is rendered and recorded but not sent. Rowfire still only reads the database; the change happens through your API.
Many accounts, one address
Credential stuffing looks different: one IP address trying many accounts. That
is a second trigger keyed on ip_address, counting distinct user_ids per
address instead, with its own rule. For another risk signal sent to a service,
see send flagged orders to a fraud service,
or call any REST API from a SQL query.
Try a trigger and a backtest on the live demo.
Questions
Is this fast enough to stop an attack?
No, and it isn't meant to be. Triggers are polled, within a minute by default. Rate limits and lockouts belong in the login request itself. This tells a person that one account is being hammered, so they can look.
What if the failures keep coming all day?
The key is user_id and the rule fires once per user per day, so the 6th, 20th and 200th failure that day post nothing new. If it is still happening tomorrow, it posts again.
Does Rowfire write to my MariaDB database?
No. Point it at a SELECT-only account. The session also runs SET SESSION TRANSACTION READ ONLY, and the query checker refuses anything but a single SELECT.
My login_attempts table stores local time. Does that matter?
Yes. The session is pinned to UTC and a DATETIME is read as UTC, so local times would land hours off. Convert in the query with CONVERT_TZ(attempted_at, 'Europe/Cairo', '+00:00'), using your own zone.