Re: Freeradius-Users Digest, Vol 144, Issue 6

4 Apr 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@lists.freeradius.org>
To: <freeradius-users@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@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@lists.freeradius.org
You can reach the person managing the list at
freeradius-users-owner@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@lists.freeradius.org>
Subject: "[sql] stop packet with zero session length" problem
Message-ID:
<VI1P18901MB0240723C6A133647731D6A85ED0B0@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@tomica.ru>
To: FreeRadius users mailing list
<freeradius-users@lists.freeradius.org>
Subject: perl and SQL question
Message-ID: <20170404151313.6f6ceb2c@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@quarantainenet.nl>
To: freeradius-users@lists.freeradius.org
Subject: Re: perl and SQL question
Message-ID: <381dda30-1f58-450b-f843-dd43cf9545e7@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...
-- 
Herwin Weststrate
------------------------------
Message: 4
Date: Tue, 4 Apr 2017 10:30:01 +0200
From: Pierre de Jong <pierredejong@gmail.com>
To: FreeRadius users mailing list
<freeradius-users@lists.freeradius.org>
Subject: FreeRadius 3.0.13 - Using SSID to check AD groups
Message-ID:
<CAHAvaJ5rgFj+UOnS+jms6MW=BSTW61DbS==bTH6dqDRMnHndbA@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@tomica.ru>
To: Herwin Weststrate <herwin@quarantainenet.nl>
Cc: FreeRadius users mailing list
<freeradius-users@lists.freeradius.org>
Subject: Re: perl and SQL question
Message-ID: <20170404154220.06d0db81@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@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...
-- 
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
************************************************

Re: Freeradius-Users Digest, Vol 144, Issue 6

dahili.network＠gmail.com