SQL Counter access period reply CoA on Update Interim
Hi, I setup up a sql_counter fine… after access period is reached and active connections is reset user cannot login again. The issue is to ‘kill’ that connection just in the moment access period limit is reached. Can anybody explain me how to do that? Kind Regards JL
On Jun 9, 2015, at 2:48 PM, Jose Luis Espinoza <info@ispcube.com> wrote:
Hi, I setup up a sql_counter fine… after access period is reached and active connections is reset user cannot login again.
The issue is to ‘kill’ that connection just in the moment access period limit is reached.
The sqlcounter module sends back a Session-Timeout attribute. The NAS is supposed to disconnect the users session after this time.
Can anybody explain me how to do that?
CoA might work. But if the NAS doesn't support Session-Timeout, it probably won't support CoA. Alan DeKok.
Thanks for the fast answer.! Using Mikrotik as NAS. My main goal is to get users disconnected after some condition during acct update process. In radcheck table I have ZmeG0 Access-Period := 120 then in NAS I setup acct interim period in 1 minute. Here you can see debug result when starting radiusd -X Module: Linked to module rlm_sqlcounter Module: Instantiating module "accessperiod" from file /etc/raddb/sql/mysql/counter.conf sqlcounter accessperiod { counter-name = "Max-Access-Period-Time" check-name = "Access-Period" reply-name = "Session-Timeout" key = "User-Name" sqlmod-inst = "sql" query = "SELECT (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(AcctStartTime)) FROM radacct WHERE UserName = '%{%k}' ORDER BY AcctStartTime LIMIT 1" reset = "never" safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } rlm_sqlcounter: Reply attribute Session-Timeout is number 27 rlm_sqlcounter: Counter attribute Max-Access-Period-Time is number 11273 rlm_sqlcounter: Check attribute Access-Period is number 11274 rlm_sqlcounter: Current Time: 1433881568 [2015-06-09 22:26:08], Next reset 0 [2015-06-09 22:00:00] rlm_sqlcounter: Current Time: 1433881568 [2015-06-09 22:26:08], Prev reset 0 [2015-06-09 22:00:00] Module: Checking preacct {...} for more modules to load Then when NAS is doing Acct Update rad_recv: Accounting-Request packet from host 190.97.25.169 port 36897, id=101, length=181 Acct-Status-Type = Interim-Update NAS-Port-Type = Wireless-802.11 Calling-Station-Id = "2C:F0:EE:21:DE:06" Called-Station-Id = "hotspot1" NAS-Port-Id = "wlan1" User-Name = "ZmeG0" NAS-Port = 2148532243 Acct-Session-Id = "80100013" Framed-IP-Address = 10.5.200.2 Mikrotik-Host-IP = 10.5.200.2 Event-Timestamp = "Jan 2 1970 05:36:56 CET" Acct-Input-Octets = 617577 Acct-Output-Octets = 938225 Acct-Input-Gigawords = 0 Acct-Output-Gigawords = 0 Acct-Input-Packets = 4789 Acct-Output-Packets = 4075 Acct-Session-Time = 240 NAS-Identifier = "ISPCubeLAB" Acct-Delay-Time = 0 NAS-IP-Address = 192.168.100.230 # Executing section preacct from file /etc/raddb/sites-enabled/default +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] Hashing 'NAS-Port = 2148532243,Client-IP-Address = 190.97.25.169,NAS-IP-Address = 192.168.100.230,Acct-Session-Id = "80100013",User-Name = "ZmeG0"' [acct_unique] Acct-Unique-Session-ID = "99c7e36288faa86b". ++[acct_unique] returns ok [suffix] No '@' in User-Name = "ZmeG0", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop ++[files] returns noop # Executing section accounting from file /etc/raddb/sites-enabled/default +- entering group accounting {...} [detail] expand: %{Packet-Src-IP-Address} -> 190.97.25.169 [detail] expand: /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /var/log/radius/radacct/190.97.25.169/detail-20150609 [detail] /var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radius/radacct/190.97.25.169/detail-20150609 [detail] expand: %t -> Tue Jun 9 22:28:30 2015 ++[detail] returns ok ++[unix] returns noop [radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> ZmeG0 ++[radutmp] returns ok [sql] expand: %{User-Name} -> ZmeG0 [sql] sql_set_user escaped user --> 'ZmeG0' [sql] expand: %{Acct-Input-Gigawords} -> 0 [sql] expand: %{Acct-Input-Octets} -> 617577 [sql] expand: %{Acct-Output-Gigawords} -> 0 [sql] expand: %{Acct-Output-Octets} -> 938225 [sql] expand: CALL acct_update( '%S', '%{Acct-Session-Time}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', 'Acct-Update', '%{Acct-Session-Id}', '%{SQL-User-Name}', '%{NAS-IP-Address}', '%{Acct-Unique-Session-Id}', '%{Realm}', '%{NAS-Port}', '%{NAS-Port-Type}', '%{Acct-Authentic}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}') -> CALL acct_update( '2015-06-09 22:28:30', '240', '0' << 32 | '617577', '0' << 32 | '938225', 'Acct-Update', '80100013', 'ZmeG0', '192.168.100.230', '99c7e36288faa86b', '', '2148532243', 'Wireless-802.11' rlm_sql (sql): Reserving sql socket id: 2 rlm_sql (sql): Released sql socket id: 2 ++[sql] returns ok ++[exec] returns noop [attr_filter.accounting_response] expand: %{User-Name} -> ZmeG0 attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 101 to 190.97.25.169 port 36897 Finished request 1. Cleaning up request 1 ID 101 with timestamp +83 Going to the next request Ready to process requests. I need to mention that I have modified accounting_update_query and accounting_stop_query accounting_update_query = " \ CALL acct_update( \ '%S', \ '%{Acct-Session-Time}', \ '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \ '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \ 'Acct-Update', \ '%{Acct-Session-Id}', \ '%{SQL-User-Name}', \ '%{NAS-IP-Address}', \ '%{Acct-Unique-Session-Id}', \ '%{Realm}', \ '%{NAS-Port}', \ '%{NAS-Port-Type}', \ '%{Acct-Authentic}', \ '%{Called-Station-Id}', \ '%{Calling-Station-Id}', \ '%{Service-Type}', \ '%{Framed-Protocol}', \ '%{Framed-IP-Address}')" accounting_stop_query = " \ CALL acct_stop( \ '%S', \ '%{Acct-Session-Time}', \ '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \ '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \ '%{Acct-Terminate-Cause}', \ '%{%{Acct-Delay-Time}:-0}', \ '%{Connect-Info}', \ '%{Acct-Session-Id}', \ '%{SQL-User-Name}', \ '%{NAS-IP-Address}')" UPDATE SQL PROCEDURE BEGIN DECLARE Prev_Acct_Input_Octets BIGINT(20); DECLARE Prev_Acct_Output_Octets BIGINT(20); DECLARE Prev_Acct_Session_Time INT(12); SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime) INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets, Prev_Acct_Session_Time FROM radacct WHERE AcctSessionId = Acct_Session_Id AND UserName = SQL_User_Name AND NASIPAddress = NAS_IP_Address AND ( AcctStopTime > 0); IF (Prev_Acct_Session_Time IS NULL) THEN SET Prev_Acct_Session_Time = 0; SET Prev_Acct_Input_Octets = 0; SET Prev_Acct_Output_Octets = 0; END IF; UPDATE radacct SET AcctStopTime = S, AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time), AcctInputOctets = (Acct_Input_Octets - Prev_Acct_Input_Octets), AcctOutputOctets = (Acct_Output_Octets - Prev_Acct_Output_Octets), AcctTerminateCause = Acct_Terminate_Cause WHERE AcctSessionId = Acct_Session_Id AND UserName = SQL_User_Name AND NASIPAddress = NAS_IP_Address AND (AcctStopTime IS NULL OR AcctStopTime = 0); INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) VALUES (Acct_Session_Id, Acct_Unique_Session_Id, SQL_User_Name, Realm, NAS_IP_Address, NAS_Port, NAS_Port_Type, S, '0', '0', Acct_Authentic, '0', '0', Called_Station_Id, Calling_Station_Id, '', Service_Type, Framed_Protocol, Framed_IP_Address, '0', '0'); END STOP SQL PROCEDURE BEGIN DECLARE Prev_Acct_Input_Octets BIGINT(20); DECLARE Prev_Acct_Output_Octets BIGINT(20); DECLARE Prev_Acct_Session_Time INT(12); SELECT SUM(AcctInputOctets), SUM(AcctOutputOctets), SUM(AcctSessionTime) INTO Prev_Acct_Input_Octets, Prev_Acct_Output_Octets, Prev_Acct_Session_Time FROM radacct WHERE AcctSessionId = Acct_Session_Id AND UserName = SQL_User_Name AND NASIPAddress = NAS_IP_Address AND ( AcctStopTime > 0); IF (Prev_Acct_Session_Time IS NULL) THEN SET Prev_Acct_Session_Time = 0; SET Prev_Acct_Input_Octets = 0; SET Prev_Acct_Output_Octets = 0; END IF; UPDATE radacct SET AcctStopTime = S, AcctSessionTime = (Acct_Session_Time - Prev_Acct_Session_Time), AcctInputOctets = (Acct_Input_Octets - Prev_Acct_Input_Octets), AcctOutputOctets = (Acct_Output_Octets - Prev_Acct_Output_Octets), AcctTerminateCause = Acct_Terminate_Cause, AcctStopDelay = Acct_Delay_Time, ConnectInfo_stop = Connect_Info WHERE AcctSessionId = Acct_Session_Id AND UserName = SQL_User_Name AND NASIPAddress = NAS_IP_Address AND (AcctStopTime IS NULL OR AcctStopTime=0); END
El 9/6/2015, a las 16:53, Alan DeKok <aland@deployingradius.com> escribió:
On Jun 9, 2015, at 2:48 PM, Jose Luis Espinoza <info@ispcube.com> wrote:
Hi, I setup up a sql_counter fine… after access period is reached and active connections is reset user cannot login again.
The issue is to ‘kill’ that connection just in the moment access period limit is reached.
The sqlcounter module sends back a Session-Timeout attribute. The NAS is supposed to disconnect the users session after this time.
Can anybody explain me how to do that?
CoA might work. But if the NAS doesn't support Session-Timeout, it probably won't support CoA.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jun 9, 2015, at 4:45 PM, Jose Luis Espinoza <info@ispcube.com> wrote:
Using Mikrotik as NAS. My main goal is to get users disconnected after some condition during acct update process.
That's not what you said in your first message.
In radcheck table I have
ZmeG0 Access-Period := 120
With the sqlcounter module, that will send Session-Timeout of 120. Which is very, very, low. The NAS is free to ignore such a low value. It should be 5 minutes or more. And, the Session-Timeout will be sent during the initial Access-Request. It will have NO EFFECT on later Accounting-Request packets.
then in NAS I setup acct interim period in 1 minute.
That is probably too often. But if you want a high-load server, that's up to you.
Then when NAS is doing Acct Update
Which doesn't send a Session-Timeout attribute back. What happens during an Access-Request packet?
I need to mention that I have modified accounting_update_query and accounting_stop_query
Uh... if you're going to use non-standard schema && queries, the standard sqlcounter module is likely to not work. If you want to send a CoA packet when the server receives an Accounting-Request packet, see raddb/sites-available/originate-coa. This is extensively documented. Alan DeKok.
Thanks Again Alan for your time. Problem is solved.. Adding Session-Timeout on radreply works! JL
El 9/6/2015, a las 17:50, Alan DeKok <aland@deployingradius.com> escribió:
On Jun 9, 2015, at 4:45 PM, Jose Luis Espinoza <info@ispcube.com> wrote:
Using Mikrotik as NAS. My main goal is to get users disconnected after some condition during acct update process.
That's not what you said in your first message.
In radcheck table I have
ZmeG0 Access-Period := 120
With the sqlcounter module, that will send Session-Timeout of 120. Which is very, very, low. The NAS is free to ignore such a low value.
It should be 5 minutes or more.
And, the Session-Timeout will be sent during the initial Access-Request. It will have NO EFFECT on later Accounting-Request packets.
then in NAS I setup acct interim period in 1 minute.
That is probably too often. But if you want a high-load server, that's up to you.
Then when NAS is doing Acct Update
Which doesn't send a Session-Timeout attribute back.
What happens during an Access-Request packet?
I need to mention that I have modified accounting_update_query and accounting_stop_query
Uh... if you're going to use non-standard schema && queries, the standard sqlcounter module is likely to not work.
If you want to send a CoA packet when the server receives an Accounting-Request packet, see raddb/sites-available/originate-coa. This is extensively documented.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (2)
-
Alan DeKok -
Jose Luis Espinoza