I would appreciate some advice.
How can I configure FreeRADIUS so that when a PPPoE user's account expires, their profile changes?
Currently, when the account expires, the PPPoE user cannot connect to the MikroTik router.
I would like them to still be able to connect, but using a custom profile.
Common approach for this in FreeRADIUS with MikroTik:
Don't reject expired users; reauthorize them into a restricted profile
In your authorize section (usually inside sql or a custom module), check the expiration date before deciding Auth-Type. Instead of rejecting on expiry, let the user authenticate normally but attach different reply attributes.
Steps:
In your SQL query (or unlang), check expiration against NOW(). If expired, don't send Reject. Instead, continue to Auth-Type PAP/CHAP as normal.
In post-auth, conditionally set reply attributes based on expiry status:
if (&SQL-User-Group == "expired") {
update reply {
Mikrotik-Group = "expired-profile"
Framed-Pool = "expired-pool"
}
}
else {
update reply {
Mikrotik-Group = "%{SQL-User-Group}"
Framed-Pool = "%{SQL-User-Pool}"
}
}
On the MikroTik side, create a PPP profile named to match (e.g. expired-profile) with:
Low rate limit (or zero, if you want a walled garden only)
A separate IP pool routed only to a redirect/payment page
Optional local hotspot walled garden pointing to your billing portal
Make sure your radgroupreply (or equivalent SQL table) has a row mapping the expired group to those Mikrotik-Group/Framed-Pool values, so FreeRADIUS returns them automatically instead of you hardcoding logic per user.
Key point: The account should still authenticate successfully in FreeRADIUS (no Access-Reject). The profile change lives entirely in which reply attributes get attached post-auth. RouterOS reads Mikrotik-Group and applies the matching local PPP profile, where the actual restriction (rate limit, address pool, parent queue, etc.) occurs.
This avoids user-side reconnection failures and lets you push suspended users into a "please renew" walled garden state instead of a hard block.
Hallo @zilleali Thank you, Sir.
Should I delete expired data from the radcheck table?
If I do delete it, how can I log the user session details when the connection is terminated due to expiration?
No, don't delete the expired user's row from radcheck. If you delete it, FreeRADIUS won't find the user at all on next connection attempt, and you'll get a straight reject instead of the controlled "expired profile" reauth we set up.
Keep radcheck intact. Just flip the group/status field so authorize logic routes them to the expired profile.
On session logging: this is completely separate from radcheck. Session details (start time, stop time, bytes in/out, terminate cause) come from RADIUS Accounting, not Authentication. As long as your acct module is active and writing to radacct, every session gets logged there regardless of what's in radcheck.
So even if a session gets terminated because the account expired mid session, you'll still see it in radacct with Acct-Terminate-Cause showing something like Session-Timeout or Admin-Reset, depending on how MikroTik sends the disconnect.
Deleting from radcheck only kills future authentication, not historical accounting data. Keep both tables independent and you're fine.
That is the default content for that module, nothing wrong with it. In FreeRADIUS 3 the expiration module is mostly a legacy stub now, it does not need any manual configuration for what you are trying to do.
The logic we discussed does not go inside mods-enabled/expiration. It lives in your sql module authorize query and the post-auth section instead. So you can leave this file untouched.
What matters is:
Your SQL authorize query pulls a status or group field alongside the normal radcheck attributes
Your unlang in post-auth (usually in sites-enabled/default) checks that field and sets Mikrotik-Group accordingly
radcheck stays intact for the user regardless of expired status
If you are using rlm_sql with the standard schema, check your queries.conf authorize query, that is where the expired vs active branching should happen, not in this module file.
I need to create new code for the SQL-User-Group in queries.conf, right?
May I know the sample code, sir?
queries.confInstead of pulling a raw SQL-User-Group attribute directly, the cleanest method is to have the authorize query set control:Group (or Fall-Through into a check) based on the expiration date comparison done in SQL itself. That way radgroupreply handles the actual attribute mapping and you never touch queries.conf again when adding new groups.
In queries.conf, find authorize_group_check_query and authorize_group_reply_query. Before that, you need the query that assigns which group a user belongs to. This is usually not a static usergroup table entry; it should be dynamic based on expiry.
Replace your group_membership_query with something like this:
sql
group_membership_query = "\
SELECT groupname \
FROM ( \
SELECT 'expired-profile' AS groupname \
WHERE EXISTS ( \
SELECT 1 FROM radcheck \
WHERE username = '%{SQL-User-Name}' \
AND attribute = 'Expiration' \
AND TO_TIMESTAMP(value, 'DD Mon YYYY HH24:MI:SS') < NOW() \
) \
UNION \
SELECT 'active-profile' AS groupname \
WHERE NOT EXISTS ( \
SELECT 1 FROM radcheck \
WHERE username = '%{SQL-User-Name}' \
AND attribute = 'Expiration' \
AND TO_TIMESTAMP(value, 'DD Mon YYYY HH24:MI:SS') < NOW() \
) \
) AS grp"
Adjust the date format string to match however you store Expiration in radcheck. If you store expiry in a separate subscribers table instead of radcheck, point the subquery there and join on username.
radgroupreply with the two groupssql
INSERT INTO radgroupreply (groupname, attribute, op, value) VALUES
('active-profile', 'Mikrotik-Group', ':=', 'default'),
('active-profile', 'Framed-Pool', ':=', 'main-pool'),
('expired-profile', 'Mikrotik-Group', ':=', 'expired'),
('expired-profile', 'Framed-Pool', ':=', 'expired-pool');
sql moduleIn mods-enabled/sql, confirm these are uncommented:
read_groups = yes
And in sites-enabled/default, inside authorize, make sure sql is called before expiration and that group reply is not being skipped.
SQL-User-Group variablePulling SQL-User-Group as a loose attribute means you would need unlang conditionals in post-auth referencing it directly, and every new profile means editing sites-enabled/default. With group-based branching, adding a third tier (grace period, suspended, etc.) is just one more INSERT into radgroupreply, no config file changes.
If your Expiration values are not stored in radcheck and instead live in a custom billing table, tell me that table's schema and I will adjust the subquery to join against it directly instead of assuming radcheck.
This is the only table I have,
MariaDB [radius]> SELECT * FROM radcheck where username = 'HORAS';
+--------+----------+--------------------+----+----------------------+
| id | username | attribute | op | value |
+--------+----------+--------------------+----+----------------------+
| 156872 | HORAS | NAS-IP-Address | == | 192.168.45.25 |
| 156874 | HORAS | Cleartext-Password | := | 1234567899 |
| 156875 | HORAS | Expiration | := | 22 Jul 2030 19:40:00 |
+--------+----------+--------------------+----+----------------------+
4 rows in set (0.002 sec)
but I am running two services:
hotspot and PPPoE,
the hotspot system works normally using the expiration module in the radcheck table,
however, with PPP, I am facing an issue changing the RADIUS user profile to maintain the connection to the MikroTik using a custom profile. sir
Since you're on MariaDB, use STR_TO_DATE instead of TO_TIMESTAMP. Your date format 22 Jul 2030 19:40:00 maps to %d %b %Y %H:%i:%s.
Update group_membership_query in queries.conf:
sql
group_membership_query = "\
SELECT groupname FROM ( \
SELECT 'expired-profile' AS groupname \
WHERE EXISTS ( \
SELECT 1 FROM radcheck \
WHERE username = '%{SQL-User-Name}' \
AND attribute = 'Expiration' \
AND STR_TO_DATE(value, '%d %b %Y %H:%i:%s') < NOW() \
) \
UNION \
SELECT 'active-profile' AS groupname \
WHERE NOT EXISTS ( \
SELECT 1 FROM radcheck \
WHERE username = '%{SQL-User-Name}' \
AND attribute = 'Expiration' \
AND STR_TO_DATE(value, '%d %b %Y %H:%i:%s') < NOW() \
) \
) AS grp"
No separate billing table needed; this reads straight from your existing radcheck.
On why PPPoE behaves differently from hotspot: the expiration module's default action on an expired user is Auth-Type = Reject. Hotspot handles that gracefully because RouterOS shows a walled garden or login page regardless. PPPoE has no equivalent fallback; a reject just drops the PPP negotiation, so the user cannot connect at all.
The group query above bypasses that entirely. It never lets expiration issue the reject; it routes the user into expired-profile before that check matters, and radgroupreply attaches Mikrotik-Group = expired so RouterOS applies your restricted PPP profile instead of blocking the connection.
Make sure in sites-enabled/default, your authorize section calls sql (which resolves group membership) before expiration runs, otherwise expiration will still reject first.