- 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
🏁 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)
- Never fight FreeRADIUS design
- Max-All-Session ≠ quota enforcement
- Expiration ≠ time tracking
- unlang math is fragile — avoid it
- sqlcounter exists for a reason
- Attributes are tested with &
- Dictionary collisions break sqlcounter
- Diagnostic messages must be explicit
- Order of checks matters
- 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
2️⃣ 1-Day Card
3️⃣ 1-Week Card
4️⃣ 1-Month Card (30 Days)
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:
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
sqlcountermodule - 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
Syed Jahanzaib - سید جہانزیب - Personal Blog to Share Knowledge ! 