Freeradius-Users Digest, Vol 144, Issue 6

dahili.network at gmail.com dahili.network at gmail.com
Tue Apr 4 12:42:34 CEST 2017


radacct table related problems exists on every version of freeradius
i made custom session script to solve this problem i use that since two 
years
i also used custom addons

radacc watch
own dyn dns mechanism to use dynamic ip's

also NAS side scripts helps to solve this kind of problems

Oguz Ersoz
Dahili Network






----- Original Message ----- 
From: <freeradius-users-request at lists.freeradius.org>
To: <freeradius-users at lists.freeradius.org>
Sent: Tuesday, April 04, 2017 10:42 AM
Subject: Freeradius-Users Digest, Vol 144, Issue 6


> Send Freeradius-Users mailing list submissions to
> freeradius-users at lists.freeradius.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://lists.freeradius.org/mailman/listinfo/freeradius-users
> or, via email, send a message with subject or body 'help' to
> freeradius-users-request at lists.freeradius.org
>
> You can reach the person managing the list at
> freeradius-users-owner at lists.freeradius.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Freeradius-Users digest..."
>
>
> Today's Topics:
>
>   1. "[sql] stop packet with zero session length" problem
>      (Selahattin Cilek)
>   2. perl and SQL question (Anton)
>   3. Re: perl and SQL question (Herwin Weststrate)
>   4. FreeRadius 3.0.13 - Using SSID to check AD groups (Pierre de Jong)
>   5. Re: perl and SQL question (Anton)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Tue, 4 Apr 2017 05:25:11 +0000
> From: Selahattin Cilek <>
> To: FreeRadius users mailing list
> <freeradius-users at lists.freeradius.org>
> Subject: "[sql] stop packet with zero session length" problem
> Message-ID:
> <VI1P18901MB0240723C6A133647731D6A85ED0B0 at VI1P18901MB0240.EURP189.PROD.OUTLOOK.COM>
>
> Content-Type: text/plain; charset="utf-8"
>
> I have been trying to get the Unifi AP line of products to work with 
> FreeRADIUS (v 2.2.9) seamlessly for quite some time now; but with little 
> success, I am afraid to say. It turned out that the portion of the 
> firmware that deals with RADIUS accounting is a complete mess. I have sent 
> them many e-mails with detailed information for the many bugs I have 
> discovered. They say they will fix them all and I am still waiting.
>
> Since I cannot tell the site owners to dump all their Ubiquiti hardware 
> and buy new equipment, my only option is to make the most of FreeRADIUS 
> configuration and MySQL programming to compensate for the firmware bugs in 
> the UAP. However, there is one bug for which I have not been able to find 
> a workaround.
>
> Sometimes, and for some reason that is unknown to me, when a user connects 
> to a UAP NAS, he is immediately kicked out. We can see that in the 
> accounting details log of the NAS:
>
> Tue Apr  4 08:02:36 2017
>    Acct-Session-Id = "58D911BB-00001F5D"
>    Acct-Status-Type = Start
>    Acct-Authentic = RADIUS
>    User-Name = "99481225842"
>    NAS-Identifier = "44d9e77a2de5"
>    NAS-Port = 0
>    Called-Station-Id = "46-D9-E7-7B-2D-E5:YEMEKHANE"
>    Calling-Station-Id = "28-ED-6A-30-55-5A"
>    NAS-Port-Type = Wireless-802.11
>    Connect-Info = "CONNECT 0Mbps 802.11b"
>    Class = 0x3939343831323235383432
>    NAS-IP-Address = 192.168.0.31
>    FreeRADIUS-Acct-Session-Start-Time = "Apr  4 2017 08:02:36 MSK"
>    Timestamp = 1491282156
>
> Tue Apr  4 08:02:36 2017
>    Acct-Session-Id = "58D911BB-00001F5D"
>    Acct-Status-Type = Stop
>    Acct-Authentic = RADIUS
>    User-Name = "99481225842"
>    NAS-Identifier = "44d9e77a2de5"
>    NAS-Port = 0
>    Called-Station-Id = "46-D9-E7-7B-2D-E5:YEMEKHANE"
>    Calling-Station-Id = "28-ED-6A-30-55-5A"
>    NAS-Port-Type = Wireless-802.11
>    Connect-Info = "CONNECT 0Mbps 802.11b"
>    Class = 0x3939343831323235383432
>    Acct-Session-Time = 0
>    Acct-Input-Packets = 11
>    Acct-Output-Packets = 12
>    Acct-Input-Octets = 1289
>    Acct-Output-Octets = 3415
>    Event-Timestamp = "Apr  4 2017 08:02:35 MSK"
>    Acct-Terminate-Cause = User-Request
>    NAS-IP-Address = 192.168.0.31
>    FreeRADIUS-Acct-Session-Start-Time = "Apr  4 2017 08:02:36 MSK"
>    Timestamp = 1491282156
>
>
> I believe this is ridiculous. How on earth could someone be connected to a 
> WLAN for 0 seconds, right? We can observe this bug in FreeRADIUS log 
> messages too:
>
> Apr 4 08:02:36     radiusd     96187     [sql] stop packet with zero 
> session length. [user '99481225842', nas '192.168.0.31']
>
> So what is the problem? Well, the problem is that when FreeRADIUS receives 
> the first packet, it runs a custom MySQL stored procedure I have written 
> myself, which enters a record into the radacct table. But because it 
> refuses to accept the second packet, I end up with a suspended session. 
> And because users allowed to connect one device only to the network 
> (Simultaneous-Use := 1), they will have to wait for 10 minutes for the 
> suspended session to drop, thanks to a custom bash script.
>
> My question is:
> How can I make the sql module to accept accounting packets with zero 
> session length?
>
> PS: Upgrading to FreeRADIUS 3.x is not an option.
>
>
> Thanks in advance.
>
> Selahattin CILEK
>
>
>
>
>
>
>
>
>
>
> [https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif]<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> 
> Virus-free. 
> www.avast.com<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
>
> ------------------------------
>
> Message: 2
> Date: Tue, 4 Apr 2017 15:13:13 +0700
> From: Anton <zav at tomica.ru>
> To: FreeRadius users mailing list
> <freeradius-users at lists.freeradius.org>
> Subject: perl and SQL question
> Message-ID: <20170404151313.6f6ceb2c at zav-wrk>
> Content-Type: text/plain; charset=US-ASCII
>
> Hi.
>
> I have next code in perl module to communicate with postgresql:
>
> ...
> our $pgclientsn = "DBI:Pg:dbname=mydb;host=10.14.2.66;port=5432;";
> ...
> sub CLONE {
>  ...
>  # remote psql
>  $pgclientdbh = DBI->connect($pgclientsn, 'username', 
> 'password',{AutoCommit => 1,pg_server_prepare => 1});
>
>  # accoutning stop insert (?)
>  $astopins = $pgclientdbh->prepare("INSERT INTO telephonecallacct 
> (username,acctstarttime,acctstoptime,acctsessiontime,callingstationid,calledstationid,calldirection) 
> VALUES (?,TO_TIMESTAMP(?),TO_TIMESTAMP(?),?,?,?,?)");
>  ...
>  # check service status for tel.number -- check number status
>  $numstatcheck = $pgclientdbh->prepare("SELECT s.enabled FROM 
> telephony_numbers AS tn, service AS s WHERE number = ? AND tn.sid = 
> s.id");
>  ...
> }
>
> today postgres was unaccessible for 10 minits (some pg processes were core 
> dumped). freeradius started responding "Invalid user" and stayed in
> this state until the freeradius restart.
>
> How should I modify perl code to restart sql connection automatically or 
> periodically or something else to prevent sql connection problems ?
> If this possible at all ?
>
>
>
> ------------------------------
>
> Message: 3
> Date: Tue, 4 Apr 2017 10:23:55 +0200
> From: Herwin Weststrate <herwin at quarantainenet.nl>
> To: freeradius-users at lists.freeradius.org
> Subject: Re: perl and SQL question
> Message-ID: <381dda30-1f58-450b-f843-dd43cf9545e7 at quarantainenet.nl>
> Content-Type: text/plain; charset=utf-8
>
> On 04-04-17 10:13, Anton wrote:
>> Hi.
>>
>> I have next code in perl module to communicate with postgresql:
>>
>> ...
>> our $pgclientsn = "DBI:Pg:dbname=mydb;host=10.14.2.66;port=5432;";
>> ...
>> sub CLONE {
>>   ...
>>   # remote psql
>>   $pgclientdbh = DBI->connect($pgclientsn, 'username', 
>> 'password',{AutoCommit => 1,pg_server_prepare => 1});
>>
>>   # accoutning stop insert (?)
>>   $astopins = $pgclientdbh->prepare("INSERT INTO telephonecallacct 
>> (username,acctstarttime,acctstoptime,acctsessiontime,callingstationid,calledstationid,calldirection) 
>> VALUES (?,TO_TIMESTAMP(?),TO_TIMESTAMP(?),?,?,?,?)");
>>   ...
>>   # check service status for tel.number -- check number status
>>   $numstatcheck = $pgclientdbh->prepare("SELECT s.enabled FROM 
>> telephony_numbers AS tn, service AS s WHERE number = ? AND tn.sid = 
>> s.id");
>>   ...
>> }
>>
>> today postgres was unaccessible for 10 minits (some pg processes were 
>> core dumped). freeradius started responding "Invalid user" and stayed in
>> this state until the freeradius restart.
>>
>> How should I modify perl code to restart sql connection automatically or 
>> periodically or something else to prevent sql connection problems ?
>> If this possible at all ?
>
> You probably shouldn't. The CLONE sub is executed every time a thread is
> spawned [1], so the code snippet you posted is probably wrong (it might
> be cropped too heavy, but it looks as if the database inserts are
> performed inside the CLONE sub).
>
> You *might* be able to move $pgclientdbh to an "our" variable in the
> outer scope of the file and do something like "reconnect() unless
> $pgclientdbh->ping" I have no idea if it works or not.
>
> But looking at your code once again: I don't see anything here that
> can't be done by the sql module. And even if it has, you can still make
> it more robust by moving the select and insert statements to specific
> sql-modules and storing results in temporary variables. This removes the
> database handling from the perl script.
>
>
> [1]
> http://perldoc.perl.org/perlguts.html#Should-I-do-anything-special-if-I-call-perl-from-multiple-threads?
>
> -- 
> Herwin Weststrate
>
>
> ------------------------------
>
> Message: 4
> Date: Tue, 4 Apr 2017 10:30:01 +0200
> From: Pierre de Jong <pierredejong at gmail.com>
> To: FreeRadius users mailing list
> <freeradius-users at lists.freeradius.org>
> Subject: FreeRadius 3.0.13 - Using SSID to check AD groups
> Message-ID:
> <CAHAvaJ5rgFj+UOnS+jms6MW=BSTW61DbS==bTH6dqDRMnHndbA at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Hi,
>
> I am currently trying to set up a single FreeRadius 3.0.13 server to 
> handle
> diffenrents SSID on a network.
> Users should be allowed to use certain SSID IF they are in certain AD 
> Group.
>
> We (my collegue and myself) have been to work things out, BUT we would 
> like
> to optimise.
>
> Here is what has been done:
>
> In the DEFAULT site:
>
> Authenticate -->  With the MSCHAP via NTLM_Auth
>
> Authorize ---> we have configured the LDAP module to be able to query the 
> AD
>
>
> We could see that is was working, in the "radiusd -X" output.
>
> Then we implemented this, under....
>
> But the first "if" does not work, although it should be ENOUGH and really
> enought dynamic.... if the SSID and AD Group are created the right way.
>
> We encountered the following problem:
>
> UNABLE to use "%{Called-Station-SSID}"  (nor %Called-Station-SSID)
>
> But  %{User-Name} works great.
>
>
> - rewrite_called_station_id   --> has been called in "authorized" that
> should "instantiate" the Called-Station-SSID
>
> post-auth {
>                if (LDAP-Group == "grp-ssid-%{Called-Station-SSID}") {
>                        update reply {
>                                Reply-Message = "Hello %{User-Name}: accès
> permis sur %{Called-Station-SSID} a"
>                        }
>                        ok
>                        return
>                }
>                if (LDAP-Group == "grp-ssid-GRP-Radius" &&
> Called-Station-SSID == "GRP-Radius") {
>                        update reply {
>                                Reply-Message = "Hello %{User-Name}: accès
> permis sur %{Called-Station-SSID} b"
>                        }
>                        ok
>                        return
>                }
>                if (LDAP-Group == "grp-ssid-GRP-Radius2" &&
> Called-Station-SSID == "GRP-Radius2") {
>                        update reply {
>                                Reply-Message = "Hello %{User-Name}: accès
> permis sur %{Called-Station-SSID} "
>                        }
>                        ok
>                        return
>                } else {
>                        update reply {
>                                Reply-Message = "Hello %{User-Name}: Vous
> n'avez pas accès"
>                        }
>                        reject
>                }
> ####
>
>
> - Is there any ways to use that "CALLED-STATION-SSID"? anywhere.... or,
> where is it "usable".
> - Is that kind of "mechanisme" the good way to go ?
> - Is there a way to initialise a "global" variable, so that we can use it
> "anywhere" ? (UNLANG)
>
> Thanks in advance
>
> Pierre de Jong
> -.-.-.-.-.-.-.-.-.-.-
>
>
> ------------------------------
>
> Message: 5
> Date: Tue, 4 Apr 2017 15:42:20 +0700
> From: Anton <zav at tomica.ru>
> To: Herwin Weststrate <herwin at quarantainenet.nl>
> Cc: FreeRadius users mailing list
> <freeradius-users at lists.freeradius.org>
> Subject: Re: perl and SQL question
> Message-ID: <20170404154220.06d0db81 at zav-wrk>
> Content-Type: text/plain; charset=UTF-8
>
> Herwin, I have "->prepare" in CLONE, so INSERTs and SELECTs are not 
> executed in CLONE functions. They are only prepared
> on sql-server for next execution in more effective way. There is only one 
> place I know for "sql prepare" -- CLONE function.
>
> Thanks for You reply. Spawned theads did not help me for some unknown 
> reason.
>
>
> On Tue, 4 Apr 2017 10:23:55 +0200
> Herwin Weststrate <herwin at quarantainenet.nl> wrote:
>
>> On 04-04-17 10:13, Anton wrote:
>> > Hi.
>> >
>> > I have next code in perl module to communicate with postgresql:
>> >
>> > ...
>> > our $pgclientsn = "DBI:Pg:dbname=mydb;host=10.14.2.66;port=5432;";
>> > ...
>> > sub CLONE {
>> >   ...
>> >   # remote psql
>> >   $pgclientdbh = DBI->connect($pgclientsn, 'username', 
>> > 'password',{AutoCommit => 1,pg_server_prepare => 1});
>> >
>> >   # accoutning stop insert (?)
>> >   $astopins = $pgclientdbh->prepare("INSERT INTO telephonecallacct
>> > (username,acctstarttime,acctstoptime,acctsessiontime,callingstationid,calledstationid,calldirection) 
>> > VALUES
>> > (?,TO_TIMESTAMP(?),TO_TIMESTAMP(?),?,?,?,?)"); ... # check service 
>> > status for tel.number -- check number status
>> >   $numstatcheck = $pgclientdbh->prepare("SELECT s.enabled FROM 
>> > telephony_numbers AS tn, service AS s WHERE number = ? AND tn.sid = 
>> > s.id");
>> >   ...
>> > }
>> >
>> > today postgres was unaccessible for 10 minits (some pg processes were 
>> > core dumped). freeradius started responding "Invalid user" and stayed 
>> > in
>> > this state until the freeradius restart.
>> >
>> > How should I modify perl code to restart sql connection automatically 
>> > or periodically or something else to prevent sql connection problems ?
>> > If this possible at all ?
>>
>> You probably shouldn't. The CLONE sub is executed every time a thread is
>> spawned [1], so the code snippet you posted is probably wrong (it might
>> be cropped too heavy, but it looks as if the database inserts are
>> performed inside the CLONE sub).
>>
>> You *might* be able to move $pgclientdbh to an "our" variable in the
>> outer scope of the file and do something like "reconnect() unless
>> $pgclientdbh->ping" I have no idea if it works or not.
>>
>> But looking at your code once again: I don't see anything here that
>> can't be done by the sql module. And even if it has, you can still make
>> it more robust by moving the select and insert statements to specific
>> sql-modules and storing results in temporary variables. This removes the
>> database handling from the perl script.
>>
>>
>> [1]
>> http://perldoc.perl.org/perlguts.html#Should-I-do-anything-special-if-I-call-perl-from-multiple-threads?
>>
>
>
>
>
> -- 
> Anton,
> инженер отдела управления сетью связи,
> ООО "ИКА" (Томика) 634050 г. Томск
> пр. Ленина 55, оф. 101
> Тел: 701-855
>
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> -
> List info/subscribe/unsubscribe? See 
> http://www.freeradius.org/list/users.html
>
> ------------------------------
>
> End of Freeradius-Users Digest, Vol 144, Issue 6
> ************************************************
> 



More information about the Freeradius-Users mailing list