Syed Jahanzaib – سید جہانزیب – Personal Blog to Share Knowledge !

February 9, 2026

Implementing Daily Quota-Based Speed Throttling in FreeRADIUS 3.2.x (Production-Grade, Without sqlcounter)

Filed under: freeradius, Mikrotik Related — Tags: , , , , , , — Syed Jahanzaib / Pinochio~:) @ 10:21 AM

  • Author: Syed Jahanzaib ~A Humble Human being! nothing else 😊
  • Platform: aacable.wordpress.com
  • Category: Corporate Offices / DHCP-DNS Engineering
  • Audience: Systems Administrators, IT Support, NOC Teams, Network Architects

⚠️ Disclaimer & Note on Writing Style

Every network environment is unique. A solution that works effectively in one infrastructure may require modification in another. Readers are strongly encouraged to understand the underlying concepts and adapt the guidance according to their own architecture, operational policies, and risk tolerance.

Blind copy-paste implementation without proper validation, testing, and change management is never recommended — especially in production environments. Always ensure proper backups and risk assessment before applying any configuration.

The content shared here is based on hands-on experience from real-world deployments, ISP environments, lab testing, and continuous learning. While I strive for technical accuracy, no technical implementation is entirely free from the possibility of error. Constructive discussion and alternative approaches are always welcome.

Due to professional commitments, it is not always feasible to publish highly detailed or multi-part write-ups. The technical logic and implementation details are written based on my own practical experience. AI tools such as ChatGPT are used only to refine grammar, structure, and presentation — not to generate the core technical concepts.

This blog is not intended for client acquisition or follower growth. It exists solely to share practical knowledge and real-world experience with the community.

Thank you for your understanding and continued support.

Implementing Daily Quota‑Based Speed Throttling in FreeRADIUS 3.2.x

Production‑Grade Design (Without sqlcounter)

Overview

In many ISP and enterprise environments, not all subscribers should be treated equally. A common requirement is:

  • Allow full speed (e.g., 10 Mbps) up to a daily quota (e.g., 100 GB/day)
  • Automatically downgrade speed (e.g., to 5 Mbps) once the quota is exceeded
  • Restore full speed automatically the next day

This article documents a real, production‑tested implementation using:

  • FreeRADIUS (tested on 3.2.7)
  • MySQL backend
  • MikroTik NAS with CoA support
  • Cron‑based enforcement
  • Explicit SQL logic (no magic counters)
  • .my.cnf security

⚠️ Important: This design intentionally does NOT use sqlcounter. The reasoning is explained later.
Also we can adapt this logic for Huawei / Cisco & other NASES too… 😉

What This Design Supports

  • Multiple service profiles (e.g., 5M, 10M, 20M)
  • Per‑service daily quota limits
  • Automatic speed downgrade after quota exhaustion
  • Real‑time enforcement for already‑connected users via CoA (We send only explicit bandwidth-update CoA attributes, not disconnect requests.)
  • Automatic speed restoration on the next day
  • No infinite CoA loops (Avoid sending CoA too frequently because the NAS might be overloaded otherwise
  • Safe to run every few minutes in production

This approach scales cleanly and is widely used in real ISP deployments.

High‑Level Architecture

Responsibilities are cleanly separated:

Component Responsibility
FreeRADIUS Authentication & policy decision
MySQL Usage data, services, enforcement state
Cron script Quota detection & CoA triggering
CoA Force re‑authorization / speed change
State table Prevent repeated enforcement

Why this matters

  • Easier debugging
  • Predictable behavior
  • Safe upgrades
  • No hidden logic in authentication path

Design Principles (Read This First)

Before touching configuration, understand these realities:

  • Authorization happens once (at login)
  • Quota is crossed later (during accounting)
  • FreeRADIUS does not automatically re‑authorize sessions
  • CoA is mandatory for real‑time speed changes
  • Repeated CoA without state tracking causes loops

To solve this correctly, we introduce a quota state table.

Database Tables Used

Table Purpose 
radcheck
 
Authentication only
 
radacct
 
Usage accounting
 
service_profiles
 
Speed + quota rules
 
user_services
 
User → service mapping
 
user_quota_state
 
Enforcement memory (critical)

This is exactly how large ISP RADIUS systems are structured.

Mental Model (Clean Separation)

Large systems scale by separating responsibility — not by collapsing everything into one table.

For this design we use three custom tables.

1️⃣ service_profiles — Rules Table

Purpose

Defines how a service behaves.

Controls

  • Normal speed (e.g., 10M/10M)
  • Throttled speed (e.g., 5M/5M)
  • Daily quota (GB)
  • Whether quota enforcement is enabled

One‑Line Summary

service_profiles defines HOW a service behaves.

Table Creation

CREATE TABLE service_profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
normal_rate VARCHAR(50),
throttled_rate VARCHAR(50),
daily_quota_gb INT,
quota_enabled TINYINT(1)
);

Example Entry

INSERT INTO service_profiles
(name, normal_rate, throttled_rate, daily_quota_gb, quota_enabled)
VALUES
('10mb', '10M/10M', '5M/5M', 100, 1);

2️⃣ user_services — User → Service Mapping

Purpose

Maps which user is on which service plan.

One‑Line Summary

user_services defines WHICH rules apply to WHICH user.

Table Creation

CREATE TABLE user_services (
username VARCHAR(64) PRIMARY KEY,
service_name VARCHAR(50)
);

This keeps radcheck clean and avoids overloading authentication tables.

3️⃣ user_quota_state — Enforcement Memory (Critical)

Purpose

Remembers who has already been throttled today.

It prevents infinite CoA loops.

One‑Line Summary

user_quota_state answers one question: “Has quota enforcement already been applied for this user today?”

Table Creation

CREATE TABLE user_quota_state (
username VARCHAR(64) NOT NULL,
service_name VARCHAR(50) NOT NULL,
quota_date DATE NOT NULL,
is_throttled TINYINT(1) DEFAULT 0,
PRIMARY KEY (username, quota_date)
);

What This Table IS

  • A daily flag
  • A loop‑prevention mechanism
  • A CoA control switch

What This Table IS NOT

  • ❌ Billing record
  • ❌ Usage log
  • ❌ Authorization source
  • ❌ Permanent status

It does not control access. It only prevents repeated enforcement.

Lifecycle of a User

  1. Login → Normal speed
  2. Quota exceeded → Script detects breach
  3. CoA sent → Speed reduced
  4. State recorded → No further CoA today
  5. New day → State cleared → Full speed restored

Authorization Logic (Speed Assignment)

During authorization, FreeRADIUS decides which speed profile applies.

Logic

On login (or re-auth):

  • If user is not throttled today → Normal speed
  • If user is throttled today → Throttled speed

🧠 One Sentence Mental Model

Authorization decides speed, accounting measures usage, CoA enforces change, one flag prevents repetition.

1️⃣ Is putting this logic in sites-enabled/default efficient?

Short answer

Yes, if done correctly — and no, it will NOT overload authorization in your use-case.

Why this is safe

In FreeRADIUS, authorize {} is executed anyway for every Access-Request.
You are not adding a new phase, you are only adding conditional logic.

The real cost is:

  • SQL queries
  • String expansion
  • Unlang branching

These are cheap compared to:

  • TLS
  • PAP/CHAP/MSCHAP
  • SQL auth itself
  • Accounting writes

What would be inefficient (and what you should avoid)

❌ Multiple SQL queries per request
❌ Repeating the same SQL lookup again and again
❌ Logic applied to every NAS when not required

✅ Production-safe optimization pattern (Recommended)

  1. Move logic into a policy
  2. Cache service data in control
  3.  Proper way to detect NAS type and send correct attributes (MikroTik vs others)
  4. Best Practice: Use nas table (already supported by FreeRADIUS)
  5. Use NAS-Type in unlang

FreeRADIUS authorize {} Configuration

Edit:

nano /etc/freeradius/sites-enabled/default

authorize {
preprocess
filter_username
suffix
# 1) Load credentials
sql
# 2) Determine user's service
update control {
Service-Name := "%{sql:SELECT service_name FROM user_services WHERE username='%{User-Name}' LIMIT 1}"
}
# 3) Quota‑based speed decision
if ("%{sql:SELECT 1 FROM user_quota_state WHERE username='%{User-Name}' AND quota_date=CURDATE() LIMIT 1}" == "1") {
update reply {
Mikrotik-Rate-Limit := "%{sql:SELECT throttled_rate FROM service_profiles WHERE name='%{control:Service-Name}'}"
}
}
else {
update reply {
Mikrotik-Rate-Limit := "%{sql:SELECT normal_rate FROM service_profiles WHERE name='%{control:Service-Name}'}"
}
}
pap
chap
mschap
digest
eap {
ok = return
}
expiration
logintime
}

Result

  • Normal login → Normal speed
  • Over quota → Throttled speed
  • CoA re‑auth → Correct speed
  • Next day → Full speed restored

Real‑Time Enforcement Using CoA (qc.sh)

Mental Model

SQL detects quota breach → CoA enforces change → State table prevents repetition
Authorization alone is not enough because users may stay connected for days.

Selection Criteria (Critical)
The script must select only:

  • Active sessions
  • Services with quota_enabled = 1
  • Users who exceeded daily quota
  • Users not already throttled today

Production‑Ready qc.sh (quota check every 5 minutes)

#!/bin/bash
while read user nas ip secret; do
echo "Applying quota throttle for user=$user on NAS=$nas"
echo "User-Name=$user, Framed-IP-Address=$ip" | \
radclient -x "$nas" coa "$secret"
mysql <<EOF
INSERT IGNORE INTO user_quota_state (username, quota_date)
VALUES ('$user', CURDATE());
EOF
done < <(
mysql -N <<EOF
SELECT
r.username,
r.nasipaddress,
r.framedipaddress,
n.secret
FROM radacct r
JOIN user_services u ON r.username = u.username
JOIN service_profiles s ON u.service_name = s.name
JOIN nas n ON n.nasname = r.nasipaddress
WHERE r.acctstoptime IS NULL
AND s.quota_enabled = 1
AND NOT EXISTS (
SELECT 1
FROM user_quota_state q
WHERE q.username = r.username
AND q.quota_date = CURDATE()
)
GROUP BY r.username, r.nasipaddress, r.framedipaddress, n.secret
HAVING
SUM(r.acctinputoctets + r.acctoutputoctets)
> (MAX(s.daily_quota_gb) * 1024 * 1024 * 1024);
EOF
)

Safe to run every 5–10 minutes. Cron it to run every 5 minutes.

Why This Does NOT Loop

Once a user is throttled:

  • is_throttled = 1 is recorded
  • Script excludes that user
  • No repeated CoA packets
  • No NAS overload

This is idempotent enforcement, which is mandatory in ISP systems.

Daily Quota Reset & restore already connected users to there original speed – Script

Restoring Original Speed for Already Connected Users at Midnight

✅ The correct restore logic (mirror of qc.sh)

The restore script must:

  • Target only users who were throttled
  • Target only active sessions
  • Send CoA only
  • NOT touch accounting
  • NOT disconnect

That means:

  • Same CoA
  • Different condition

The Problem

At midnight, we reset daily quota (qc_reset.sh) state by running:

DELETE FROM user_quota_state
WHERE quota_date < CURDATE();

This correctly unmarks users as “quota exceeded”.

However:

❗ Users who are already connected and throttled will remain throttled
until their session is re-authorized.

Because:

  • FreeRADIUS only applies speed on authentication / re-auth
  • Clearing the table alone does not push a change to active sessions

So we need a way to force already connected users to re-apply policy.

Change bandwidth of ALREADY CONNECTED users using CoA, both when quota is exceeded and when quota is reset

The Correct Solution (Industry Practice)

✔ Use CoA (Change of Authorization) at midnight

to force policy re-evaluation for affected users.

This is exactly how ISPs do it.

Design Principle (Important)

We do NOT want to:

  • Disconnect users
  • Restart NAS
  • Restart FreeRADIUS
  • Touch radacct

Example of qc_restore.sh

#!/bin/bash
#set -x
while read user nas rate secret; do
echo "Restoring user=$user to $rate"
# I have disabled this for test, you may enable it , syedjahanzaib
# echo "User-Name=$user, Mikrotik-Rate-Limit=$rate" | \
# radclient -q -c 1 "$nas:3799" coa "$secret"
done < <(
mysql radius -N <<EOF
SELECT
r.username,
r.nasipaddress,
s.normal_rate,
n.secret
FROM user_quota_state q
JOIN radacct r ON r.username = q.username
JOIN user_services u ON u.username = r.username
JOIN service_profiles s ON s.name = u.service_name
JOIN nas n ON n.nasname = r.nasipaddress
WHERE q.quota_date < CURDATE()
AND r.acctstoptime IS NULL;
EOF
)

NOTE:
WHERE q.quota_date < CURDATE()
This is intentional: only past-quota users restored after the new day begins

Why You MUST NOT Rely Only on FreeRADIUS Logic??

You might ask:
“Why not let FreeRADIUS re-evaluate on every packet?”
Because:

  • Authorization happens once
  • Accounting happens later
  • Usage thresholds are crossed after auth

➡ CoA + state table is the only correct solution

qc_reset.sh

DELETE FROM user_quota_state WHERE quota_date < CURDATE();

Cron Job (Run this script every night at 00:05 (12:05 am)

At midnight:

  1. quota_reset.sh deletes state
  2. qc_restore.sh sends CoA
  3. NAS re-asks RADIUS
  4. RADIUS sees NO quota state
  5. RADIUS replies with normal_rate
  6. NAS updates bandwidth live

✔ Same session
✔ Same IP
✔ Same user
✔ Only bandwidth changes

🕛 Correct cron order

Script Purpose Frequency
qc.sh Detect quota exceeded & throttle Every 5–10 min
quota_reset.sh Reset daily enforcement state Once per day
qc_restore.sh Restore normal speed via CoA Once per day
*/5 * * * * /temp/qc.sh
5 0 * * * /temp//quota_reset.sh
10 0 * * * /temp/qc_restore.sh

🧠 Final Mental Model (This is the key)

qc.sh → INSERT state + CoA → throttle bandwidth
quota_reset.sh → DELETE state
qc_restore.sh → CoA only → restore bandwidth

CoA is used in BOTH directions
Throttle AND restore.

🟢 There is NO DISCONNECT Anywhere

If you wanted to disconnect, you would see:

Disconnect-Request

You are not doing that.

So your system is:

  • Correct
  • Clean
  • ISP-grade
  • Non-disruptive

Final sanity checklist

Item Status
Throttle works ✅
Restore logic correct ✅
No disconnects ✅
Explicit bandwidth CoA ✅
Daily lifecycle clean ✅
Script behavior predictable ✅

One-line takeaway (this explains everything)

Restore script does nothing during the same day — and that is exactly correct.

You’ve reached the correct end-state.

Data Consistency Lesson (Important)

Problem: Enforcement query returned no rows even though users were active.
Root Cause:

  • nasipaddress did not match nas.nasname

Fix:

  • Ensure nasname contains the NAS IP address, not only a hostname

This alignment is critical when dynamically pulling NAS secrets.

Why We Did NOT Use sqlcounter

This was a deliberate engineering decision.
Problems with sqlcounter

  • Runs inside authentication path
  • Executes on every login / re‑auth
  • Awkward speed downgrade logic
  • No native state memory
  • Fragile across upgrades

Correct Reality

  • Authorization happens once
  • Usage thresholds are crossed later
  • CoA + state tracking is mandatory

🔹 Simulate Quota Usage (Insert Fake Active Session)

When testing quota logic, you may not have a real online user yet.
In that case, insert a fake but valid active session into radacct.

✅ Example: Insert Active Session for User zaib

INSERT INTO radacct (
acctsessionid,
username,
nasipaddress,
framedipaddress,
acctstarttime,
acctstoptime,
acctinputoctets,
acctoutputoctets
) VALUES (
'SIM-SESSION-001',
'zaib',
'192.168.1.1',
'10.10.10.100',
NOW(),
NULL,
120 * 1024 * 1024 * 1024,
0
);

Why these fields matter

Column Reason
acctsessionid Must be unique
username Must match user_services
nasipaddress Must match nas.nasname
framedipaddress Used for session identification
acctstarttime Marks session start
acctstoptime = NULL Marks session as active
acctinputoctets Simulates data usage
acctoutputoctets Optional (can be 0)

🔹 Verify Inserted Session

SELECT username, nasipaddress, framedipaddress, acctinputoctets, acctstoptime
FROM radacct
WHERE username = 'zaib'
AND acctstoptime IS NULL;

Expected result:

zaib | 192.168.1.1 | 10.10.10.100 | 128849018880 | NULL

(≈120 GB)

🔹 Now Run QC (Quota and Throttle Mode Check)

qc.sh

Expected behavior

  • Script detects quota exceeded
  • Sends bandwidth-only CoA
  • Inserts entry into user_quota_state
  • User remains connected
  • Speed changes to throttled_rate

⚠️ Important Cleanup After Testing

After simulation, remove the fake session to avoid confusion:

DELETE FROM radacct
WHERE acctsessionid = 'SIM-SESSION-001';

🔹 One-Line Reminder

radacct must contain an active (acctstoptime = NULL) session for quota logic to trigger.

 

Final Architecture Summary

Component Role
radacct Usage accounting
service_profiles Speed & quota rules
user_services User mapping
user_quota_state Enforcement memory
qc.sh Detection & CoA
quota_reset.sh Daily reset
FreeRADIUS Policy decision

Securing MySQL Credentials (.my.cnf)

⚠️ Use a .my.cnf file in production (don’t hardcode passwords). this is a security + ops best practice, so let’s do it correctly and simply.

Location

/root/.my.cnf

Content

[client]
user=radius
password=YOUR_DB_PASSWORD
host=localhost
database=radius

🔒 VERY IMPORTANT: Set strict permissions

MySQL will ignore the file if permissions are weak.

chmod 600 /root/.my.cnf
chown root:root /root/.my.cnf

Verify:

ls -l /root/.my.cnf

Expected:

-rw------- 1 root root ... /root/.my.cnf

Result

❌ No passwords in scripts ❌ No exposure via ps or history

✅ How this changes your scripts (Clean & Safe)

❌ Old (bad practice)

mysql radius -u radius -pPASSWORD -e "DELETE FROM user_quota_state ..."

✅ New (correct)

mysql -e "DELETE FROM user_quota_state WHERE quota_date < CURDATE();"
  • No username
  • No password
  • No exposure in ps, history, or logs

🧠 How MySQL finds .my.cnf (important to know)

When you run mysql, it checks in this order:

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. ~/.my.cnf ← this one
  4. Command-line options

So /root/.my.cnf is automatically used.

Conclusion

By avoiding sqlcounter and using explicit SQL + CoA + state tracking, we achieve:

  • Reliable daily quota enforcement
  • Correct speed throttling
  • Clean FreeRADIUS configuration
  • Predictable behavior
  • Production‑grade stability

This reflects how real ISP systems are built, not lab examples.

Performance Tuning & Scaling Tips (Production ISPs)

This solution is already efficient by design, but the following optimizations ensure stable performance at scale (thousands to tens of thousands of users).

1️⃣ Indexing: The Single Biggest Performance Win

Your scripts heavily query radacct and user_quota_state.
Without proper indexes, MySQL will scan entire tables.

✅ Required Indexes

-- radacct: active session lookup
CREATE INDEX idx_radacct_active
ON radacct (username, acctstoptime);
CREATE INDEX idx_radacct_nas
ON radacct (nasipaddress);
-- radacct: usage calculation
CREATE INDEX idx_radacct_usage
ON radacct (username, acctstarttime);
-- user_quota_state: daily state check
CREATE UNIQUE INDEX idx_quota_user_day
ON user_quota_state (username, quota_date);
-- user_services: user lookup
CREATE INDEX idx_user_services_user
ON user_services (username);
-- service_profiles: service lookup
CREATE INDEX idx_service_profiles_name
ON service_profiles (name);

📌 These indexes reduce query time from seconds to milliseconds.

2️⃣ Limit radacct Growth (Very Important)

radacct grows fast in ISP environments.

Best Practices

  • Enable Interim-Update (5–10 minutes)
  • Periodically archive old records:
DELETE FROM radacct
WHERE acctstarttime < NOW() - INTERVAL 90 DAY;

Or move to an archive table.

3️⃣ Cron Frequency: Don’t Overdo It

Recommended schedule

Script Frequency
qc.sh  Every 5–10 minutes
quota_reset.sh Once per day
quota_restore.sh  Once per day

Running throttle every minute does not improve accuracy, only DB load.

5️⃣ Reduce CoA Traffic (Selective Targeting)

Your design sends CoA only when needed:

✔ User exceeded quota
✔ User not already throttled
✔ User still online

This is far better than sending CoA to all users.

6️⃣ Avoid sqlcounter for High-Scale Quota Logic

Why your design scales better:

Aspect sqlcounter This Design
Runs on auth Yes No
DB queries Per login Scheduled
CoA support Limited Native
Loop protection No Yes
Debuggability Hard Easy

For large ISPs, offloading quota logic from auth path is critical.

7️⃣ MySQL Tuning (Basic but Important)

Minimum recommended settings for radius DB:

[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
max_connections = 300</div>

Adjust according to RAM and load.

8️⃣ Use .my.cnf (Already Done)

Avoid command-line passwords.

Benefits:

  • No leaks in ps
  • Cleaner scripts
  • Safer cron jobs

9️⃣ Logging Without Killing Performance

Avoid excessive debug logging.

Recommended approach

echo "$(date) THROTTLE user=$user rate=$rate" >> /var/log/quota_coa.log
  • Log actions, not every query
  • Rotate logs weekly

🔟 Test at Scale (Before Production)

Before enabling in production:

  • Simulate 100–500 fake users
  • Run throttle mode
  • Observe:
    • MySQL CPU
    • Disk I/O
    • CoA response time

If MySQL is slow → indexes are missing.

Final Performance Rule (Remember This)

Quota logic must run OUTSIDE the authentication path.

Your design already follows this rule — which is why it scales.

One-Line Takeaway

Indexes + selective CoA + cron-based logic = ISP-grade performance.

Syed Jahanzaib

 

February 7, 2026

Building a Production-Grade Prepaid Time System in FreeRADIUS 3.x (Lessons Learned the Hard Way)

Filed under: freeradius, Linux Related — Tags: , , , , , , , , — Syed Jahanzaib / Pinochio~:) @ 6:54 PM

 

  • Author: Syed Jahanzaib ~A Humble Human being! nothing else 😊
  • Platform: aacable.wordpress.com
  • Category: Corporate Offices / DHCP-DNS Engineering
  • Audience: Systems Administrators, IT Support, NOC Teams, Network Architects

⚠️ Disclaimer & Note on Writing Style

Every network environment is unique. A solution that works effectively in one infrastructure may require modification in another. Readers are strongly encouraged to understand the underlying concepts and adapt the guidance according to their own architecture, operational policies, and risk tolerance.

Blind copy-paste implementation without proper validation, testing, and change management is never recommended , especially in production environments. Always ensure proper backups and risk assessment before applying any configuration.

The content shared here is based on hands-on experience from real-world deployments, ISP environments, lab testing, and continuous learning. While I strive for technical accuracy, no technical implementation is entirely free from the possibility of error. Constructive discussion and alternative approaches are always welcome.

Due to professional commitments, it is not always feasible to publish highly detailed or multi-part write-ups. The technical logic and implementation details are written based on my own practical experience. AI tools such as ChatGPT are used only to refine grammar, structure, and presentation — not to generate the core technical concepts.

This blog is not intended for client acquisition or follower growth. It exists solely to share practical knowledge and real-world experience with the community.

Thank you for your understanding and continued support.

Building a Production-Grade Prepaid Time System in FreeRADIUS 3.2.x

(Lessons Learned by zaib , the Hard Way)

Introduction

Implementing prepaid time-based accounts in FreeRADIUS sounds simple at first:

“Just create a 1-hour card and expire it when time is used.”

In reality, if you want a correct, scalable, and production-safe solution, you will quickly discover that:

  • Max-All-Session does not reject logins
  • Expiration does not behave the way most people assume
  • unlang math is fragile and error-prone
  • FreeRADIUS has very strict parsing and execution rules
  • The correct solution is not obvious unless you understand internals

This article documents a real-world journey of building a prepaid voucher system in FreeRADIUS 3.x, including every pitfall, diagnostic message, and final correct design.

If you are planning:

  • 1-hour / 1-day / 1-week / 1-month cards
  • Countdown from first login
  • Pause/resume usage
  • Hard expiry safety
  • Clean rejection messages < This is where i stucked for hours

👉 This article will save you days of frustration.

Design Requirements

We wanted the system to behave as follows:

  • Prepaid cards with fixed time (e.g. 1 hour = 3600 seconds)
  • Time countdown starts only after first login
  • Time pauses when the user disconnects
  • Once time is fully consumed → login must be rejected
  • Optional hard expiry (e.g. 1 year safety)
  • Clear diagnostic messages:
    • Time quota exhausted
    • Account expired
    • Invalid username or password
  • Must scale (no heavy SQL in unlang)

What Does NOT Work (Common Mistakes)

Before the correct solution, let’s clear misconceptions.

❌ Max-All-Session alone is NOT enough

Max-All-Session:

  • Limits session duration
  • Does not reject authentication
  • Only influences Session-Timeout

So even if a user has used all time, authentication can still succeed.

❌ Using Expiration for time quota

Expiration:

  • Is date-based only
  • Does not support HH:MM:SS
  • Is parsed by rlm_expiration
  • Midnight expiry is by design

It is not a time quota mechanism.

❌ Doing math in unlang

Examples like:

if (SUM(radacct) >= Max-All-Session)

lead to:

  • parser errors
  • performance issues
  • unreadable configs
  • upgrade nightmares

This approach is not production-safe.

The Correct Tool: rlm_sqlcounter

FreeRADIUS already ships with a module designed exactly for this problem:

rlm_sqlcounter

It:

  • Tracks usage via accounting
  • Compares usage against a limit
  • Rejects authentication automatically
  • Scales cleanly
  • Avoids unlang math entirely

This is how ISPs and hotspot providers do it.

Final Architecture (Correct & Supported)

Components

Component Purpose
radacct Stores used time
sqlcounter Enforces quota
Expiration Safety expiry
Post-Auth-Type REJECT Diagnostic messages

Step 1 – Prepaid Attribute in radcheck

Example: 1-hour card
(Expiration 1 year (for safe side so that these accounts may not remain live for ever)

INSERT INTO radcheck (username, attribute, op, value)
VALUES
('card1001', 'Cleartext-Password', ':=', 'card1001'),
('card1001', 'Prepaid-Time-Limit', ':=', '3600'),
('card1001', 'Expiration', ':=', '31 Jan 2027');

⚠️ Important

  • Do NOT define Prepaid-Time-Limit in any dictionary
  • sqlcounter registers it dynamically as integer64

Step 2 – sqlcounter Module (The Core)

Create the module:

/etc/freeradius/mods-available/sqlcounter_prepaid

sqlcounter prepaid_time {
sql_module_instance = sql
key = User-Name
counter_name = Prepaid-Time-Limit
check_name = Prepaid-Time-Limit
reply_name = Session-Timeout
reset = never
query = "SELECT COALESCE(SUM(acctsessiontime),0)
FROM radacct
WHERE username='%{User-Name}'"
}

Enable it:

ln -s /etc/freeradius/mods-available/sqlcounter_prepaid \
/etc/freeradius/mods-enabled/sqlcounter_prepaid

Step 3 – Call sqlcounter in Authorization

In/etc/freeradius/sites-enabled/default

authorize {
sql
expiration
prepaid_time ## This it the one , zaib
logintime
filter_username
preprocess
chap
mschap
digest
suffix
eap {
ok = return
}
files
pap
}

What happens now:

  • If used time < limit → Access-Accept
  • If used time ≥ limit → Access-Reject
  • No unlang math
  • No ambiguity

Step 4 – Correct Diagnostic Messages (This Is Where Most People Fail)

FreeRADIUS does not automatically tell users why they were rejected.
We must explicitly add logic in Post-Auth-Type REJECT.

⚠️ Important unlang rules

  • Attributes must be tested with &
  • if (control:Attribute) is invalid
  • Never compare Auth-Type to Reject
  • Order of conditions matters

Final, Correct Post-Auth-Type REJECT Block

post-auth {
Post-Auth-Type REJECT {
#
# 1) Prepaid quota exhausted (sqlcounter)
#
if (&control:Prepaid-Time-Limit) {
update reply {
Reply-Message := "Time quota exhausted"
}
}
#
# 2) Date-based expiration
#
elsif (&control:Expiration) {
update reply {
Reply-Message := "Account expired"
}
}
#
# 3) All other failures
#
else {
update reply {
Reply-Message := "Invalid username or password"
}
}
sql
attr_filter.access_reject
}
}

This produces clean, deterministic results.

FREERADIUS server reload

Note: After any change in the Freeradius CONFIG files, ensure to reload or restart freeradius service by

  • service freeradius reload 

Also its better to check freeradius config syntax before reload/restarting  by issuing below cmd

  • freeradius -XC

Final Behaviour (Verified with radclient)

Using RADCLIENT,

echo “User-Name=card1001,User-Password=card1001” | radclient -x localhost:1812 auth testing123

  • Quota exhausted

Access-Reject

Reply-Message = “Time quota exhausted”

  • Date expired

Access-Reject

Reply-Message = “Account expired”

  • Wrong credentials

Access-Reject

Reply-Message = “Invalid username or password”

Optional: Show Used vs Allocated time (SQL)

Here is the final, clean, production-safe SQL query to show remaining prepaid time in a user-friendly HH:MM:SS format, based on everything we finalized.

This query is read-only, audit-safe, and does not interfere with sqlcounter enforcement.

SELECT
rc.username,
SEC_TO_TIME(MAX(CAST(rc.value AS UNSIGNED))) AS allocated_time,
SEC_TO_TIME(IFNULL(SUM(ra.acctsessiontime),0)) AS used_time,
SEC_TO_TIME(
GREATEST(
0,
MAX(CAST(rc.value AS UNSIGNED)) - IFNULL(SUM(ra.acctsessiontime),0)
)
) AS remaining_time
FROM radcheck rc
LEFT JOIN radacct ra
ON rc.username = ra.username
WHERE rc.username = 'card1001'
AND rc.attribute = 'Prepaid-Time-Limit'
GROUP BY rc.username;

🧪 Expected Output

+———-+—————+———–+—————-+
| username | allocated_time| used_time | remaining_time |
+———-+—————+———–+—————-+
| card1001 | 01:00:00 | 01:00:00 | 00:00:00 |
+———-+—————+———–+—————-+

🏁 Final Confirmation

This query is now:

✔ MySQL-8 compliant
✔ ONLY_FULL_GROUP_BY safe
✔ Accurate
✔ Read-only
✔ Audit-friendly

You’re done — this is the final form of the remaining-time query.

Key Lessons Learned  the hard way !

by zaib,THE HARDWAY
(Read This Twice)

  1. Never fight FreeRADIUS design
  2. Max-All-Session ≠ quota enforcement
  3. Expiration ≠ time tracking
  4. unlang math is fragile — avoid it
  5. sqlcounter exists for a reason
  6. Attributes are tested with &
  7. Dictionary collisions break sqlcounter
  8. Diagnostic messages must be explicit
  9. Order of checks matters
  10. Debug (freeradius -X) is your best friend

Creating 1-Hour / 1-Day / 1-Week / 1-Month Prepaid Cards

Once the prepaid time system is implemented using rlm_sqlcounter, creating different card durations becomes purely a data task.
No configuration changes are required.

The only thing that changes is the time value (in seconds) stored in radcheck.

Always add Expiration (like 1 year or few months) (For safe side so that these accounts may not remain live for ever)

Time Conversion Reference

Plan Duration Seconds
1 Hour 1 × 60 × 60 3600
1 Day 24 × 60 × 60 86400
1 Week 7 × 24 × 60 × 60 604800
1 Month (30 days) 30 × 24 × 60 × 60 2592000

ℹ️ Note

  • A “month” is intentionally treated as 30 days for consistency and predictability.
  • Calendar months vary in length and should not be used for prepaid time accounting.

Example: Creating Prepaid Cards

1️⃣ 1-Hour Card

INSERT INTO radcheck (username, attribute, op, value)
VALUES
(‘card1001’, ‘Cleartext-Password’, ‘:=’, ‘card1001’),
(‘card1001’, ‘Prepaid-Time-Limit’, ‘:=’, ‘3600’),
(‘card1001’, ‘Expiration’, ‘:=’, ’31 Jan 2027′);

2️⃣ 1-Day Card

INSERT INTO radcheck (username, attribute, op, value)
VALUES
(‘card2001’, ‘Cleartext-Password’, ‘:=’, ‘card2001’),
(‘card2001’, ‘Prepaid-Time-Limit’, ‘:=’, ‘86400’),
(‘card2001’, ‘Expiration’, ‘:=’, ’31 Jan 2027′);

3️⃣ 1-Week Card

INSERT INTO radcheck (username, attribute, op, value)
VALUES
(‘card3001’, ‘Cleartext-Password’, ‘:=’, ‘card3001’),
(‘card3001’, ‘Prepaid-Time-Limit’, ‘:=’, ‘604800’),
(‘card3001’, ‘Expiration’, ‘:=’, ’31 Jan 2027′);

4️⃣ 1-Month Card (30 Days)

INSERT INTO radcheck (username, attribute, op, value)
VALUES
(‘card4001’, ‘Cleartext-Password’, ‘:=’, ‘card4001’),
(‘card4001’, ‘Prepaid-Time-Limit’, ‘:=’, ‘2592000’),
(‘card4001’, ‘Expiration’, ‘:=’, ’31 Jan 2027′);

Why This Design Works Perfectly

✔ Countdown starts from first login
✔ Time pauses when the user disconnects
✔ Time resumes on next login
✔ Authentication is rejected immediately when quota is exhausted
✔ Expiration provides a hard safety cutoff
✔ No unlang math
✔ No schema changes
✔ Scales cleanly

All cards — hourly, daily, weekly, monthly — are handled by the same logic.

Operational Tip (Recommended)

For large deployments:

  • Generate cards in batches
  • Store card type in an external inventory table
  • Keep FreeRADIUS focused only on authentication & accounting

Example naming convention:

H-XXXX → Hourly cards
D-XXXX → Daily cards
W-XXXX → Weekly cards
M-XXXX → Monthly cards

Final Note

At this point, your FreeRADIUS setup supports:

  • Prepaid vouchers
  • Flexible durations
  • Clean enforcement
  • Clear diagnostics
  • Enterprise-grade behavior

No further complexity is required.

Final Thoughts

This setup is now:

  • ✔ Production-grade
  • ✔ Scalable
  • ✔ Upgrade-safe
  • ✔ ISP-style architecture
  • ✔ Fully tested with radclient

If you’re building prepaid vouchers, Wi-Fi cards, or temporary access accounts in FreeRADIUS — this is (one of) the correct way to do it.

Audit Summary (By Syed Jahanzaib)

SOP – Prepaid Time-Based Authentication (Audit-Friendly Flow)

This section documents the operational and control flow of the prepaid authentication system implemented using FreeRADIUS.
It is written for audits, compliance reviews, and operational SOPs.

SOP 1 – Prepaid Card Lifecycle

Objective:
Describe how prepaid cards are created, used, and retired.

Process Flow:

  • IT Administrator generates prepaid cards
  • Each card is stored in the FreeRADIUS database (radcheck)
  • Card record includes:
    • Username
    • Password
    • Prepaid time limit (in seconds)
    • Hard expiration date (safety control)
  • Card remains unused until first successful login
  • Time consumption starts only after authentication
  • Card becomes unusable when:
    • Prepaid time is exhausted OR
    • Expiration date is reached

Audit Controls:

  • Centralized credential storage
  • No manual intervention during usage
  • Automatic enforcement

SOP 2 – Authentication & Authorization Flow

Objective:
Explain how a login request is processed.

Process Flow:

  • User attempts login via NAS / captive portal
  • Access-Request is sent to FreeRADIUS
  • FreeRADIUS performs:
    • Username validation
    • Password verification
  • Expiration date is evaluated
  • Prepaid time quota is evaluated using accounting data
  • One of the following outcomes occurs:
    • Access-Accept (quota available)
    • Access-Reject (quota exhausted or expired)

Audit Controls:

  • Deterministic decision path
  • No ambiguity in enforcement
  • Fully automated

SOP 3 – Prepaid Time Enforcement Logic

Objective:
Describe how time usage is calculated and enforced.

Process Flow:

  • Allocated time is stored per user in radcheck
  • Actual usage is accumulated in radacct
  • Each login triggers:
    • Retrieval of total used session time
    • Comparison with allocated prepaid time
  • Enforcement is performed by the sqlcounter module
  • Authentication is rejected immediately when usage reaches or exceeds allocation

Audit Controls:

  • Time cannot exceed allocation
  • Enforcement occurs before session establishment
  • No reliance on client-side timers

SOP 4 – Accounting & Usage Tracking

Objective:
Demonstrate how usage is logged and auditable.

Process Flow:

  • Each user session generates accounting records
  • Accounting data includes:
    • Session start time
    • Session stop time
    • Total session duration
  • Usage accumulates across multiple sessions
  • Historical usage remains available for reporting and audits

Audit Controls:

  • Complete usage history
  • Non-repudiation
  • Supports forensic analysis

SOP 5 – Rejection Reason Handling (User Messaging)

Objective:
Ensure consistent and non-revealing rejection messages.

Process Flow (Priority Order):

  • If prepaid time quota is exhausted:
    • User receives message: “Time quota exhausted”
  • If hard expiration date is reached:
    • User receives message: “Account expired”
  • For all other failures:
    • User receives message: “Invalid username or password”

Audit Controls:

(zaib: although i would prefer not to categorize and clearly show each error, but still let us pass through)

  • No sensitive information leakage
  • Consistent messaging
  • Clear categorization of failure reasons

SOP 6 – Exception & Error Handling

Objective:
Describe system behavior in abnormal scenarios.

Handled Scenarios:

  • Incorrect credentials:
    • Authentication rejected
    • Event logged
  • Fully used prepaid card:
    • Authentication rejected
    • Time quota message returned
  • Expired card:
    • Authentication rejected
    • Expiration message returned
  • Database unavailable:
    • Authentication fails safely
    • No partial access granted

Audit Controls:

  • Fail-secure design
  • No bypass conditions
  • Logged outcomes

SOP 7 – Roles & Responsibilities (RACI Summary)

Role Responsibility
IT Administrator Card creation and policy configuration
FreeRADIUS Server Authentication, quota enforcement, accounting
NAS / Controller Session connectivity
End User Consumption of prepaid access
Auditor Review of logs, controls, and compliance

SOP 8 – Audit & Compliance Summary

Control Summary:

  • Prepaid access is enforced automatically
  • Time consumption is accurately tracked
  • Authentication is denied once limits are reached
  • All decisions are logged centrally
  • No manual override exists at user level

Audit Statement:

The prepaid authentication system enforces access using centrally managed credentials and accounting-based quota validation. Time usage is cumulative, automatically enforced, and fully auditable without manual intervention.

Final Note for Auditors

This design ensures:

  • Predictable enforcement
  • Strong access control
  • Minimal operational risk
  • Clear audit trail
  • Compliance with standard IT control frameworks