- Freeradius-Users - lists.freeradius.org

maintenance of radacct table
by bbnlradius 06 Mar '17

06 Mar '17

Hi, We are running a free radius version 2.2.0. It is talking to a mariadb database having 32 gb ram with 24 core cpu. From many days we are seeing major performance issue on the radius database. Seeing radacct table getting bombarded with high hits of update statement. The radacct table has been daily partitioned and has around 6 months of data. The total rows is Data_length: 17732239360 Index_length: 27330428928 Data_free: 1644167168 Auto_increment: 131859985 I need to drop the old data by dropping the partitions, but we are seeing that the gb limit goes hay wire, when we do this. Even when the table was not partitioned we saw the same issue. Can somebody please do help me out as to how to update the right data when the radacct table data is dropped. I saw a script from my colleague who had a maintenance script for radacct, it is using rm_radacct table too, but i see no rows in this table. Is this what supposed to be? Please do help me out as to how to do maintenance of the radacct table. thanks in advance Arun

4 7

redis vs traditional sql
by Peter Lambrechtsen 06 Mar '17

06 Mar '17

I'm looking to store delta usage information on a temporary basis so it can be exported out as a CSV style and fed off to an upstream billing system. Using redis seem to meed my needs of high performance and light, I especially like the expire feature so I don't need to worry about cleaning up stale sessions as they will just fall off at the end of the day. The main use case is just allowing FR to calculate the delta from the last time period, using the Acct-Session-Id as the primary key. Store the current timestamp, bytes up, bytes down against the Session ID. Then when the interim turns up calculate the difference and spit that out to a linelog then update the record in redis with the new timestamp + new byte counts and reset the expiry. Then send DMs to the subscribers if I get a timestamp that's wildly outside the expected period, with a tollerace since then if there was a FR outage / patching for a period of time the accounting events turning up wouldn't cause a mass-DM of the end-users. This means much of the pain with managing a large dynamic DB goes away as the redis db is self cleaning. Or is it just easier to stick with the traditional mysql/postgre model and not get too clever log everything and then run cron jobs afterwards to do the typical cleanup: DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 HOUR) I also love the raw transactional throughput of redis which means I don't need to have as sigificant db running this log.

2 1

any maintenance script for taking care of radacct table
by bbnlradius 05 Mar '17

05 Mar '17

Hi, Does anybody have any maintenance script for purging the old data of radacct table. Cause, if I purge the old data, then the customers gb limit is going hay wire. Hence wanted to know if there is a method to purge this table Please do let me know thanks in advance Arun

1 0

3.0.12: Failed registering counter attribute ChilliSpot-Max-Output-Octets: Attribute 'ChilliSpot-Max-Output-Octets' already exists
by David Pires 03 Mar '17

03 Mar '17

Hi, I am in the process of upgrading my radius servers from v2.1.12 to v3.0.12 on Ubuntu xenial 64bit. I have successfully got sql and sqlcounter mods working, (I have tested dailycounter/Max-Daily-Session) however when trying to add a counter to sqlcounter, I have run into the following error: = Failed registering counter attribute ChilliSpot-Max-Output-Octets: Attribute 'ChilliSpot-Max-Output-Octets' already exists = When first adding the attribute I ran into this integer error: = /etc/freeradius/mods-enabled/sqlcounter[101]: Counter attribute ChilliSpot-Max-Output-Octets MUST be integer64 = I rectified this by editing the chillispot dictionary and changing integer to integer64. I also manually changed sqlcounter dialect sections to reference mysql directly, as I was getting an error about referencing modules that haven't loaded yet, which was answered previously on this list. Apart from adding sql to authorize {} in the default and inner-tunnel sites as per the FAQ, my configuration is default. Log: = FreeRADIUS Version 3.0.12 Copyright (C) 1999-2016 The FreeRADIUS server project and contributors There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE You may redistribute copies of FreeRADIUS under the terms of the GNU General Public License For more information about these matters, see the file named COPYRIGHT Starting - reading configuration files ... including dictionary file /usr/share/freeradius/dictionary including dictionary file /usr/share/freeradius/dictionary.dhcp including dictionary file /usr/share/freeradius/dictionary.vqp including dictionary file /etc/freeradius/dictionary including configuration file /etc/freeradius/radiusd.conf including configuration file /etc/freeradius/proxy.conf including configuration file /etc/freeradius/clients.conf including files in directory /etc/freeradius/mods-enabled/ including configuration file /etc/freeradius/mods-enabled/dynamic_clients including configuration file /etc/freeradius/mods-enabled/mschap including configuration file /etc/freeradius/mods-enabled/eap including configuration file /etc/freeradius/mods-enabled/detail including configuration file /etc/freeradius/mods-enabled/utf8 including configuration file /etc/freeradius/mods-enabled/sqlcounter including configuration file /etc/freeradius/mods-config/sql/counter/mysql/dailycounter.conf including configuration file /etc/freeradius/mods-config/sql/counter/mysql/monthlycounter.conf including configuration file /etc/freeradius/mods-config/sql/counter/mysql/noresetcounter.conf including configuration file /etc/freeradius/mods-config/sql/counter/mysql/expire_on_login.conf including configuration file /etc/freeradius/mods-enabled/radutmp including configuration file /etc/freeradius/mods-enabled/sql including configuration file /etc/freeradius/mods-config/sql/main/mysql/queries.conf including configuration file /etc/freeradius/mods-enabled/files including configuration file /etc/freeradius/mods-enabled/preprocess including configuration file /etc/freeradius/mods-enabled/expr including configuration file /etc/freeradius/mods-enabled/unpack including configuration file /etc/freeradius/mods-enabled/replicate including configuration file /etc/freeradius/mods-enabled/digest including configuration file /etc/freeradius/mods-enabled/logintime including configuration file /etc/freeradius/mods-enabled/chap including configuration file /etc/freeradius/mods-enabled/soh including configuration file /etc/freeradius/mods-enabled/sradutmp including configuration file /etc/freeradius/mods-enabled/realm including configuration file /etc/freeradius/mods-enabled/expiration including configuration file /etc/freeradius/mods-enabled/always including configuration file /etc/freeradius/mods-enabled/date including configuration file /etc/freeradius/mods-enabled/exec including configuration file /etc/freeradius/mods-enabled/cache_eap including configuration file /etc/freeradius/mods-enabled/attr_filter including configuration file /etc/freeradius/mods-enabled/passwd including configuration file /etc/freeradius/mods-enabled/echo including configuration file /etc/freeradius/mods-enabled/linelog including configuration file /etc/freeradius/mods-enabled/ntlm_auth including configuration file /etc/freeradius/mods-enabled/pap including configuration file /etc/freeradius/mods-enabled/detail.log including configuration file /etc/freeradius/mods-enabled/unix including files in directory /etc/freeradius/policy.d/ including configuration file /etc/freeradius/policy.d/operator-name including configuration file /etc/freeradius/policy.d/eap including configuration file /etc/freeradius/policy.d/dhcp including configuration file /etc/freeradius/policy.d/cui including configuration file /etc/freeradius/policy.d/moonshot-targeted-ids including configuration file /etc/freeradius/policy.d/filter including configuration file /etc/freeradius/policy.d/abfab-tr including configuration file /etc/freeradius/policy.d/canonicalization including configuration file /etc/freeradius/policy.d/debug including configuration file /etc/freeradius/policy.d/control including configuration file /etc/freeradius/policy.d/accounting including files in directory /etc/freeradius/sites-enabled/ including configuration file /etc/freeradius/sites-enabled/default including configuration file /etc/freeradius/sites-enabled/inner-tunnel main { security { user = "freerad" group = "freerad" allow_core_dumps = no } name = "freeradius" prefix = "/usr" localstatedir = "/var" logdir = "/var/log/freeradius" run_dir = "/var/run/freeradius" } main { name = "freeradius" prefix = "/usr" localstatedir = "/var" sbindir = "/usr/sbin" logdir = "/var/log/freeradius" run_dir = "/var/run/freeradius" libdir = "/usr/lib/freeradius" radacctdir = "/var/log/freeradius/radacct" hostname_lookups = no max_request_time = 30 cleanup_delay = 5 max_requests = 16384 pidfile = "/var/run/freeradius/freeradius.pid" checkrad = "/usr/sbin/checkrad" debug_level = 0 proxy_requests = yes log { stripped_names = no auth = no auth_badpass = no auth_goodpass = no colourise = yes msg_denied = "You are already logged in - access denied" } resources { } security { max_attributes = 200 reject_delay = 1.000000 status_server = yes } } radiusd: #### Loading Realms and Home Servers #### proxy server { retry_delay = 5 retry_count = 3 default_fallback = no dead_time = 120 wake_all_if_all_dead = no } home_server localhost { ipaddr = 127.0.0.1 port = 1812 type = "auth" secret = <<< secret >>> response_window = 20.000000 response_timeouts = 1 max_outstanding = 65536 zombie_period = 40 status_check = "status-server" ping_interval = 30 check_interval = 30 check_timeout = 4 num_answers_to_alive = 3 revive_interval = 120 limit { max_connections = 16 max_requests = 0 lifetime = 0 idle_timeout = 0 } coa { irt = 2 mrt = 16 mrc = 5 mrd = 30 } } home_server_pool my_auth_failover { type = fail-over home_server = localhost } realm example.com { auth_pool = my_auth_failover } realm LOCAL { } radiusd: #### Loading Clients #### client localhost { ipaddr = 127.0.0.1 require_message_authenticator = no secret = <<< secret >>> nas_type = "other" proto = "*" limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } client localhost_ipv6 { ipv6addr = ::1 require_message_authenticator = no secret = <<< secret >>> limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } client 0.0.0.0/0 { ipaddr = 0.0.0.0 require_message_authenticator = no secret = <<< secret >>> shortname = "test_name" limit { max_connections = 16 lifetime = 0 idle_timeout = 30 } } Debugger not attached # Creating Auth-Type = mschap # Creating Auth-Type = digest # Creating Auth-Type = eap # Creating Auth-Type = PAP # Creating Auth-Type = CHAP # Creating Auth-Type = MS-CHAP radiusd: #### Instantiating modules #### modules { # Loaded module rlm_dynamic_clients # Loading module "dynamic_clients" from file /etc/freeradius/mods-enabled/dynamic_clients # Loaded module rlm_mschap # Loading module "mschap" from file /etc/freeradius/mods-enabled/mschap mschap { use_mppe = yes require_encryption = no require_strong = no with_ntdomain_hack = yes passchange { } allow_retry = yes } # Loaded module rlm_eap # Loading module "eap" from file /etc/freeradius/mods-enabled/eap eap { default_eap_type = "md5" timer_expire = 60 ignore_unknown_eap_types = no cisco_accounting_username_bug = no max_sessions = 16384 } # Loaded module rlm_detail # Loading module "detail" from file /etc/freeradius/mods-enabled/detail detail { filename = "/var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d" header = "%t" permissions = 384 locking = no escape_filenames = no log_packet_header = no } # Loaded module rlm_utf8 # Loading module "utf8" from file /etc/freeradius/mods-enabled/utf8 # Loaded module rlm_sqlcounter # Loading module "dailycounter" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter dailycounter { sql_module_instance = "sql" key = "User-Name" query = "SELECT SUM(acctsessiontime - GREATEST((%%b - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username = '%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%%b'" reset = "daily" counter_name = "Daily-Session-Time" check_name = "Max-Daily-Session" reply_name = "Session-Timeout" } # Loading module "monthlycounter" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter monthlycounter { sql_module_instance = "sql" key = "User-Name" query = "SELECT SUM(acctsessiontime - GREATEST((%%b - UNIX_TIMESTAMP(acctstarttime)), 0)) FROM radacct WHERE username='%{User-Name}' AND UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%%b'" reset = "monthly" counter_name = "Monthly-Session-Time" check_name = "Max-Monthly-Session" reply_name = "Session-Timeout" } # Loading module "noresetcounter" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter noresetcounter { sql_module_instance = "sql" key = "User-Name" query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{User-Name}'" reset = "never" counter_name = "Max-All-Session-Time" check_name = "Max-All-Session" reply_name = "Session-Timeout" } # Loading module "expire_on_login" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter expire_on_login { sql_module_instance = "sql" key = "User-Name" query = "SELECT IFNULL( MAX(TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime))),0) FROM radacct WHERE UserName='%{User-Name}' ORDER BY acctstarttime LIMIT 1;" reset = "never" counter_name = "Expire-After-Initial-Login" check_name = "Expire-After" reply_name = "Session-Timeout" } # Loading module "chillispot_download_limit" from file /etc/freeradius/mods-enabled/sqlcounter sqlcounter chillispot_download_limit { sql_module_instance = "sql" key = "User-Name" query = "SELECT SUM(AcctOutputOctets) FROM radacct WHERE UserName='%{%k}'" reset = "daily" counter_name = "ChilliSpot-Max-Output-Octets" check_name = "ChilliSpot-Max-Output-Octets" reply_name = "ChilliSpot-Max-Output-Octets" } /etc/freeradius/mods-enabled/sqlcounter[101]: Failed registering counter attribute ChilliSpot-Max-Output-Octets: Attribute 'ChilliSpot-Max-Output-Octets' already exists. /etc/freeradius/mods-enabled/sqlcounter[101]: Instantiation failed for module "chillispot_download_limit" = The counter that I have added to sqlcounter: = sqlcounter chillispot_download_limit { sql_module_instance = sql dialect = "mysql" counter_name = ChilliSpot-Max-Output-Octets check_name = ChilliSpot-Max-Output-Octets reply_name = ChilliSpot-Max-Output-Octets key = User-Name reset = daily $INCLUDE ${modconfdir}/sql/counter/${dialect}/${.:instance}.conf } = The query file: = query = "SELECT SUM(AcctInputOctets) FROM radacct WHERE UserName='%{${key}}'" = Is there something critical configuration-wise that I have missed between 2.x.x and 3.x.x? David

2 1

TruBrain user activation
by Team at TruBrain 03 Mar '17

03 Mar '17

Hi FreeRadius users mailing list, A new TruBrain account has been created for you. Click the url below to activate your account and select a password! https://trubrain.freshdesk.com/register/TBf64nacozMxHd7NG9K If the above URL does not work try copying and pasting it into your browser. If you continue to have problems, please feel free to contact us. Regards, TruBrain

1 0

Help with redundant autz
by Paolo Cecchini 03 Mar '17

03 Mar '17

Hello everybody. First at all a few words about me, I’m from Italy and I work at my city, Urbino, University. Our AAA is freeradius based. Student credential’s authoritative source is an Oracle database located outside of the local net, because the application is outsourced. Credentials went synced hourly to our internal LDAP. We experienced sporadic black out with that database and in effort to mitigate the issue we’re planning to switch the first source to LDAP. Something like this. Users …. DEFAULT Realm == "stud",Autz-Type := stud, Auth-Type := PAP Sites-available/default … Autz-Type stud { group { ldap_stud { ok= return fail= 1 notfound = 2 default = return } sql_stud } } Now, scenarios. a)The user is found in ldap, ldap_stud module returns ok, the password is checked via pap and matches, access-accept. The sql module is skipped, as intended. b)The user is NOT found in ldap, the module returns notfound, proceed to sql_stud module (the same if ldap crashes, via fail) c)The user IS FOUND in ldap but the user made a password change and ldap is not synced. The ldap_stud module returns ok, but now the pap will reject the authentication. Btw the sql_stud module is skipped, of course, because the previous returned ok. I tried my best to solve the issue but that’s apparently far beyond my skill J May add that the reverse approach, sql first then ldap, will work in abstract Autz-Type stud { group { sql_stud { ok= return fail= 1 notfound = 2 default = return } ldap_stud } } But when the sql is unattainable the radius will time-out a lot. So in the end thanks a lot for any hint. Greetings. Paolo Cecchini

2 2

Re: sending Filter-Id to another accounting server - need help
by Eby Mani 02 Mar '17

02 Mar '17

Hi Alan, I have 3 NAS, one for wireless/wired access, one firewall for internet access and another firewall for restricted network access. Both firewalls are in radius accounting listen mode and have built in log and reporting functions. Internet firewall is configured to provide radius single sign-on based on user group which is passed via "Class" attribute. This works fine. The other firewall is configured to provide radius single sign-on based on user group and "magic key" which is passed via "Filter-Id" attribute. Any user without the "magic key" is not allowed to the network. This is not working as Filter-Id is not sent by radius server. I can tell radius server is not sending Filter-Id to the NAS mentioned in copy-acct-to-home-server, but it is sending Filter-Id back to the wireless/wired NAS. radius debug says, # Executing section accounting from file /etc/freeradius/sites-enabled/copy-acct-to-home-server +- entering group accounting {...} ++[ok] returns ok } # server copy-acct-to-home-server WARNING: Empty pre-proxy section. Using default return values. The question is how to tell radius server to include Filter-Id values to restricted network firewall ?. I assume adding Filter-Id to the "attrs" file will work without any additional configuration, but i don't want to do that unless that is the only solution. Thanks, Eby -------------------------------------------- On Wed, 1/3/17, Alan DeKok <aland(a)deployingradius.com> wrote: Subject: Re: sending Filter-Id to another accounting server - need help To: "Eby Mani" <eby_km(a)yahoo.com>, "FreeRadius users mailing list" <freeradius-users(a)lists.freeradius.org> Date: Wednesday, 1 March, 2017, 7:22 PM On Mar 1, 2017, at 2:11 PM, Eby Mani via Freeradius-Users <freeradius-users(a)lists.freeradius.org> wrote: > > How do i configure freeradius to send user based Filter-Id to an accounting server?. Each user will have different Filter-Id value, users without any Filter-Id string will be denied access on the another NAS(listen only). > > I have the following in post-auth section of sites-enabled/default > > update reply { > Filter-Id = "%{sql:SELECT `value` FROM `radreply` WHERE > `username`='%{User-Name}';} > } That updates the Access-Accept. > Freeradius is sending Filter-Id back to the user connected NAS, but not to the accounting server(another NAS, listen only). I have no idea what that means. > To which section should i add the update reply{} section in copy-acct-to-home-server file ? or is there a special command to include Filter-Id ?. I have no idea what that means, either. Please explain what you want to do. This time, using more detail. What packet is the server receiving? What has to be updated? What packet is the server sending? Your question assumes that we already know what you're doing. We don't. You need to explain it. Alan DeKok.

2 1

Re: freeradius 3.1 sqlcounter[41]: Attribute Daily-Session-Time already defined with different flags
by mpajarin plexus 02 Mar '17

02 Mar '17

> > Hi again and thanks for he answer. > > The reason im using 3.1 is a bug, if dont remember bad, you ( Alan ) fixed > on this version. > It´s about {Sql-group} can´t be called beyond a sql instance on > authorize { } , instead of {Sql-User-name}, it can be called on, for > example post-auth secction > > Im searching the github link about the bug for more references > > Thanks > > 2017-03-01 22:45 GMT+01:00 Alan DeKok <aland(a)deployingradius.com>: > >> >> > On Mar 1, 2017, at 3:34 PM, mpajarin plexus <mpajarin.plexus(a)gmail.com> >> wrote: >> > >> > Im running freeradius 3.1.0 version, on Centos and on debug -X get >> an >> > error like this: >> >> 3.1 isn't an officially supported release. Please use v3.0. >> >> Alan DeKok. >> >> >> - >> List info/subscribe/unsubscribe? See http://www.freeradius.org/list >> /users.html > > >

2 5

Re: Accounting on 2 tables
by Ahmed Munir 02 Mar '17

02 Mar '17

Thanks. BTW, is there a way to distinguish instances in Radius server for accounting i.e. for asterisk use instance1 and opensips use instance2? Currently using NAS table for radius client setup. id nasname shortname type ports secret server community description 1 10.0.0.0/8 Company other 5060 hatuiks \N \N RADIUS Client 2 10.3.120.100 TestSIP04 other 5060 hatuiks04 \N \N RADIUS Client Please advise and share any supported document related to above case as I'm newbie in Freeradius. Date: Fri, 24 Feb 2017 09:54:53 -0500 > From: Alan DeKok <aland(a)deployingradius.com> > To: FreeRadius users mailing list > <freeradius-users(a)lists.freeradius.org> > Subject: Re: Accounting on 2 tables > Message-ID: <3653CA7E-31CF-4044-98AD-68E8DC0205CF(a)deployingradius.com> > Content-Type: text/plain; charset=us-ascii > > On Feb 24, 2017, at 9:44 AM, Ahmed Munir <ahmedmunir007(a)gmail.com> wrote: > > > > I would like to know is it possible to setup more than 1 tables for > > accounting? As by default radacct table is used. > > Yes. > > You will need to set up two instances of the SQL module, and then edit > the queries as necessary. > > > The reason I'm asking as need to setup accounting on different tables for > > Asterisk and OpenSIPs. > > > > Please advise and share the supported document. > > I would suggest taking the time to look at the wiki. This is documented > (see rlm_sql in the wiki). All it takes is for you to look. > > Alan DeKok. > -- Regards, Ahmed Munir Chohan

2 1

RADZAP - Empty Location
by Aurélio de Souza Ribeiro Neto 02 Mar '17

02 Mar '17

Dear Alan or Everyone, I had a problem in my checkrad, and some users are in radwho with an "Empty Location", like this example: Login Name What TTY When From Location eudantasal eudantasalves PPP >999 Tue 12:53 172.20.43.2 eudantasal eudantasalves PPP >999 Tue 13:16 172.20.43.2 187.120.XXX.XXX eudantasal eudantasalves PPP >999 Wed 13:30 172.20.43.2 187.120.XXX.XXX So, if I try to radzap "eudantasalves" I got an error: radclient:: failed to get value radclient: Nothing to send. Running "radwho -r -i" for this user, I have: eudantasalves,8180329f,PPP,S15741799,Tue 12:53,172.20.43.2, eudantasalves,81805b36,PPP,S15752644,Tue 13:16,172.20.43.2,187.120.XXX.XXX eudantasalves,81807c32,PPP,S15761221,Wed 13:35,172.20.43.2,187.120.XXX.XXX How can I zap this user? Thanks Aurélio

2 3