FREERADIUS WITH MIKROTIK – Part #11 – Prepaid Hourly Accounts along-with the mighty COA !

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …

Disclaimer: This is not a copy pasted post.  It’s purely personnel R&D work. I cannot guarantee if it will work at your end or not, but still you can have some fair idea ! I also assume you must have some fair knowledge on how freeradius & its modules works all together.!

---

Task:

Allow Prepaid hourly accounts as per following requirements …

#1

We want to allow `One hour access` to users, like coupons etc. and the Counting time should start after first login. Account will expire either 1 hour of its consumption OR if the user login first time but logouts before one hour limit reaches & date changes , then the account should expire !

Example we can provide a promo offer in a shopping mall that every customer who purchase 1000 Rs worth of goods, he can avail one hour of internet. If he consume 1 hour he should get disconnected, or if he consumes just 30 minutes, and left, the account will expire right after date changes to avoid re login next day.

#2

Once the limit is over, the FR should send disconnect message to Mikrotik so that user should disconnect on the FLY. weeeeeeeeeeeeeeee ….. 😉

---

~~ Hour account (for a single day) ~~

!

1# User Entry in radcheck table

First let add one Max-All-Session entry in the FR table.

mysql> select * from radcheck;
+----+----------+----------------------+----+----------------------+
| id | username | attribute | op | value |
+----+----------+----------------------+----+----------------------+
| 1 | zaib | Cleartext-Password | := | zaib |
| 2 | zaib | Max-All-Session | := | 10 |
| 2 | zaib | Access-Period     | := | 20 |
+----+----------+----------------------+----+----------------------+
2 rows in set (0.00 sec)

NOTE: In above , we are using 10 seconds max allowed time for a user , and if the user logout before time reaches, BUT the date changes , account will still expire because 20 seconds limit in access-period! It’s an example only ! Adjust according to your need. in Lab we have to perform fast in order to save time !

---

2# Create Module for Max-All-Session & Access-Period

We will create a module for session time  that will actually check user total used time from the radacct time.

[Note: We can also modify to check users table where we can make our custom column and let it update by radacct TRIGGER.]

nano /etc/freeradius/sites-enabled/default

& paste following

sqlcounter timelimit {
counter-name = Max-All-Session-Time
check-name = Max-All-Session
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT SUM(AcctSessionTime) FROM radacct where UserName='%{%k}'"
}

Now create another module named accessperiod

& paste following (to check account expiry after first login, its in seconds)

sqlcounter accessperiod {
counter-name = Max-Access-Period-Time
check-name = Access-Period
sqlmod-inst = sql
key = User-Name
reset = never
query = "SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(AcctStartTime) FROM radacct WHERE UserName = '%{%k}' LIMIT 1"
}

Save & Exit.

3# Add above modules and SQL XLT entry in DEFAULT file

Edit DEFAULT file

nano /etc/freeradius/sites-enabled/default

& under authorize section , add the module name we made above.

timelimit
acessperiod

Now Move on to ACCOUNTING part and use following (its a SQL XLT commands to check if user have uptime_limit defined in the USERS table, if YES then simply compare the users usage from the radacct table with the uptime_limit defined in the users table.

If the condition meets, it should send POD to the NAS. This section is required only if you want to disconnect the user on the FLY.

accounting {
detail
unix
sql
exec
# Our Check entry Starts here -  CHECK Time limit from USERS table - ZaiB
# CHECK OVER TIME USER - zaiB
update control {
# Check if user have UPTIME limit defined or if its NULL
Tmp-Integer-0 := "%{sql:SELECT uptime_limit FROM users WHERE username='%{User-Name}'}"
# Get total time used from RADACCT table for this particular user
Tmp-Integer-1 := "%{sql:SELECT acctsessiontime AS Total FROM radacct where acctstarttime >= CURDATE() AND radacct.username='%{User-Name}'}"
Tmp-String-2 := "%{sql: SELECT value FROM users WHERE attribute='uptime_limit' AND username='%{User-Name}'}"
}
# If uptime limit is NULL means not deifned, then donot proceed further, Just ignore and move forward
if ("%{control:Tmp-Integer-0}" > 0){
# If uptime limit is defined in users table, then compare uptime with limit
if ("%{control:Tmp-Integer-1}" > "%{control:Tmp-String-2}"){
# If limit exceeds, send Disconnection packet, Simple is this, 🙂 zaib
update disconnect {
Framed-IP-Address = "%{Framed-IP-Address}"
User-Name = "%{request:User-Name}"
}
}
}
}

---

4# COA Section, to Disconnect on the FLY

First add COA section in the clients.conf so that we can provide FR the authority to send POD packet of disconnection to our beloved Mikrotik Router.

nano /etc/freeradius/clinets.conf

Paste following at the end of the file. Make sure to change the IP / Secret & port.

# COA Section / zaib , what a headache it was to enable COA, I am Quite a DUFFER for Sure : D
home_server example-coa {
type = coa
# Mikrotik IP Address
ipaddr = 101.11.11.253
# Radius INCOMING PORT, pay attention to this to make sure it matches with MT port
port = 1700
# Make sure that secret matches with MT as well
secret = testing123
# Now some Junk that I myself doesn't understand as well, but I just know that adding below is required to make COA works 😀
coa {
irt = 2
mrt = 16
mrc = 5
mrd = 30
}
}

 

---

5# Final Test

Now connect your windows workstation with the defined userid / password.

For test purposes I set accounting interim update to 10 seconds so that testing can be done quickly. Make a note of it ….

Now as soon as Mikrotik sends accounting request to FR, FR will do the matching case (that we defined) & if found condition to be TRUE, it will simply send DISCONNECT packet to the NAS.

Mikrotik LOG

RADIUS DEBUG

++update control {
sql_xlat
expand: %{User-Name} -> zaib
sql_set_user escaped user --> 'zaib'
expand: SELECT acctsessiontime AS Total FROM radacct where acctstarttime >= CURDATE() AND radacct.username='%{User-Name}' -> SELECT acctsessiontime AS Total FROM radacct where acctstarttime >= CURDATE() AND radacct.username='zaib'
rlm_sql (sql): Reserving sql socket id: 20
sql_xlat finished

rlm_sql (sql): Released sql socket id: 20
expand: %{sql:SELECT acctsessiontime AS Total FROM radacct where acctstarttime >= CURDATE() AND radacct.username='%{User-Name}'} -> 10
sql_xlat
expand: %{User-Name} -> zaib
sql_set_user escaped user --> 'zaib'
expand: SELECT value FROM users WHERE attribute='uptime_limit' AND username='%{User-Name}' -> SELECT value FROM users WHERE attribute='uptime_limit' AND username='zaib'

rlm_sql (sql): Reserving sql socket id: 19
rlm_sql_mysql: MYSQL check_error: 1054 received

rlm_sql (sql): database query error, SELECT value FROM users WHERE attribute='uptime_limit' AND username='zaib': Unknown column 'value' in 'field list'

rlm_sql (sql): Released sql socket id: 19
expand: %{sql: SELECT value FROM users WHERE attribute='uptime_limit' AND username='%{User-Name}'} ->

++} # update control = noop

++? if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}")
expand: %{control:Tmp-Integer-0} -> 10
expand: %{control:Tmp-String-1} ->
? Evaluating ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}") -> TRUE

++? if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}") -> TRUE
++if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}") {
+++update disconnect {
expand: %{Framed-IP-Address} -> 1.1.1.255
expand: %{request:User-Name} -> zaib
+++} # update disconnect = noop
++} # if ("%{control:Tmp-Integer-0}" > "%{control:Tmp-String-1}") = noop
+} # group accounting = ok
Sending Accounting-Response of id 219 to 101.11.11.253 port 35756
WARNING: Empty pre-proxy section. Using default return values.
Sending Disconnect-Request of id 152 to 101.11.11.253 port 3799
Framed-IP-Address = 1.1.1.255
User-Name = "zaib"
Finished request 3.
Cleaning up request 3 ID 219 with timestamp +29
Going to the next request
rad_recv: Disconnect-ACK packet from host 101.11.11.253 port 3799, id=135, length=31
NAS-Identifier = "Win"
NAS-IP-Address = 101.11.11.253
# Executing section post-proxy from file /etc/freeradius/sites-enabled/default
+group post-proxy {
[eap] No pre-existing handler found
++[eap] = noop
+} # group post-proxy = noop
Finished request 2.
Going to the next request
Waking up in 2.3 seconds.
rad_recv: Disconnect-NAK packet from host 101.11.11.253 port 3799, id=152, length=37
Error-Cause = Session-Context-Not-Found
NAS-Identifier = "Win"
NAS-IP-Address = 101.11.11.253
# Executing section post-proxy from file /etc/freeradius/sites-enabled/default
+group post-proxy {
[eap] No pre-existing handler found
++[eap] = noop
+} # group post-proxy = noop
Finished request 3.
Going to the next request
Ready to process requests.

& on the next LOGIN attempt, we will observe following …

Reply-Message = "Your maximum never usage time has been reached"

off-course you can customized this message as well….

---

Regard’s
Syed Jahanzaib