1. Rowfire
  2. Use cases

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) >= 5

Your 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.