Re: Problem with rml_sqlcounter with GigaByte datavolume
Hi Yves, thanks for your response. I understand the difference between the storing gigawords in the database and the sqlcounter response. I do not speak C, but have no doubt that what you are saying is correct. I just find it hard to believe that FreeRadius has such a basic limitation, which it appears can be easily overcome (if you can do so with a PERL script using rlm_perl, it must be possible to do it with C in rlm_sqlcounter, right), but so far no one has bothered to address. While this might have been OK in former times, when data traffic was expensive and limited, in a broadband scenario multi GigaByte data allowances over 4GB per month are very common, and I am gobsmacked that FreeRadius cannot handle what seems a very basic requirement. So accepting the point that this is a limitation of FreeRadius in its current version, I reword my question to Alan: Are there any plans to address this shortcoming of FreeRadius in the near future? Failing that, are there any rlm_perl scripts out there on the wiki or in the wider user community, that can handle gigawords on the radcheck values that actual usage is checked against? My language is PHP and I know from experience that while PHP code can be embedded into FreeRadius, it is probably the least performing option. So I would love to avoid to roll my own in PHP, if at all possible. YvesDM wrote: You confuse gigawords storage in the database coming from acct updates/stop packets of the nas with the reply from sqlcounter. FR is capable of saving gigawords in the database when a nas is sending them, that's not the problem. But, the sqlcounter's code was never changed to reply gigawords to the nas. Check the C code and you will see. Kind regards Y. On Mon, Jun 6, 2011 at 1:24 PM, Hanno Schupp wrote: > Thank you for this reply. > > I thought the limitation might come from the wrapping around 4.3 GB due to > the limitations of a 32bit system with 2147483648 being the highest signed > and 4294967296 being the highest unsigned number. 1705032704 is then exactly > the difference to 6GB, after the system wrapped at 4.29GB. I requite the > log: > > > > Sat Jun 4 23:10:21 2011 : Debug: rlm_sqlcounter: Rejected user lapzel14, > check_item=1705032704, counter=2147513300 > > > > Exactly the 1705032704 one would expect based on highest 32bit unsigned > integer. > > > > Now here is my problem: Why does it wrap at 32Bit, if the system is a x64 > server? Does not make a lot of sense to me. > > > > Also, the FAQ is containing instructions how to deal with gigawords in > terms of the sql statements that handle the calculation of the counter > value. And as this is implemented, the counter value is not the problem here > – it is the check_item value that as I understand is based on my > configuration, taken straight out of the radcheck table. > > > > I am sorry, but this sounds like a limitation/bug of the standard system, > that could be overcome. After all, if it can be resolved with custom perl > code as I understand you suggest, why should the standard system not be able > to handle data limits larger than 4.29GB out of the box? > > Or am I missing something? > > > > Alan, can you enlighten us on this issue? > > > > Regards > > > > Hanno > > > > > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Problem with rml_sqlcounter with GigaByte datavolume
Thank you for this reply. I thought the limitation might come from the wrapping around 4.3 GB due to the limitations of a 32bit system with 2147483648 being the highest signed and 4294967296 being the highest unsigned number. 1705032704 is then exactly the difference to 6GB, after the system wrapped at 4.29GB. I requite the log: Sat Jun 4 23:10:21 2011 : Debug: rlm_sqlcounter: Rejected user lapzel14, check_item=1705032704, counter=2147513300 Exactly the 1705032704 one would expect based on highest 32bit unsigned integer. Now here is my problem: Why does it wrap at 32Bit, if the system is a x64 server? Does not make a lot of sense to me. Also, the FAQ is containing instructions how to deal with gigawords in terms of the sql statements that handel the calculation of the counter value. And as this is implemented, the counter value is not the problem here - it is the check_item value that as I understand is based on my configuration, taken straight out of the radcheck table. I am sorry, but this sounds like a limitation/bug of the standard system, that could be overcome. After all, if it can be resolved with custom perl code as I understand you suggest, why should the standard system not be able to handle data limits larger than 4.29GB out of the box? Or am I missing something? Alan, can you enlighten us on this issue? Regards Hanno From: YvesDM [mailto:ydm...@gmail.com] Sent: Monday, 6 June 2011 5:42 a.m. To: FreeRadius users mailing list Subject: Re: Problem with rml_sqlcounter with GigaByte datavolume On Sun, Jun 5, 2011 at 1:22 AM, Hanno Schupp wrote: Dear All, can I ask for some pointers please. in my FreeRADIUS Version 2.1.8, for host x86_64-pc-linux-gnu (Ubuntu LTS 10.04) installation I have followed the Gigabyte instructions on the FreeRADIUS wiki's FAQ http://wiki.freeradius.org/FAQ#Why+do+Acct-Input-Octets+and+Acct-Output-Octe ts+wrap+at+4+GB%3F. The Usage is calculated correctly, but the check_item value is not what I expect to see (1.7 GB as opposed th 6GB set in radcheck). I understand who the system determines the counter value and it is correctly calculated, but where does the check_item vlaue of 1.7GB come from? I have no idea to be truthful. Sqlcounter also wraps at 4GB in its reply. Your "6GB" is actually 5722.045 MB, then wraps at 4GB so 1,7GB left and this is replied ;-) As far as I know there's no integrated solution to this unless you change the source code. Most people solve this by using rlm_perl if I'm not mistaking. Make your perl calculate and reply gigawords + remaining bytes when values are >4GB Ps Make sure your coova-chilli is equal or >1.0.13, else it won't understand gigawords replies Kind regards, Y. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Problem with rml_sqlcounter with GigaByte datavolume
Dear All,
can I ask for some pointers please. in my FreeRADIUS Version 2.1.8, for host
x86_64-pc-linux-gnu (Ubuntu LTS 10.04) installation I have followed the
Gigabyte instructions on the FreeRADIUS wiki's FAQ
http://wiki.freeradius.org/FAQ#Why+do+Acct-Input-Octets+and+Acct-Output-Octets+wrap+at+4+GB%3F.
The Usage is calculated correctly, but the check_item value is not what I
expect to see (1.7 GB as opposed th 6GB set in radcheck). I understand who
the system determines the counter value and it is correctly calculated, but
where does the check_item vlaue of 1.7GB come from? I have no idea to be
truthful.
Any pointers welcome.
Thanks in advance.
Here the data/logs:
Here the relevant debug log:
Sat Jun 4 23:10:21 2011 : Debug: rlm_sqlcounter: Entering module authorize
code
Sat Jun 4 23:10:21 2011 : Debug: sqlcounter_expand: 'SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='%{User-Name}''
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] expand: SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='%{User-Name}' -> SELECT IFNULL(SUM(AcctInputOctets) +
SUM(AcctOutputOctets),0) FROM radacct WHERE UserName='lapzel14'
Sat Jun 4 23:10:21 2011 : Debug: sqlcounter_expand: '%{sql03:SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='lapzel14'}'
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] sql_xlat
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] expand: %{User-Name}
-> lapzel14
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] sql_set_user escaped user
--> 'lapzel14'
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] expand: SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='lapzel14' -> SELECT IFNULL(SUM(AcctInputOctets) +
SUM(AcctOutputOctets),0) FROM radacct WHERE UserName='lapzel14'
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] expand:
/var/log/freeradius/sqltrace.sql -> /var/log/freeradius/sqltrace.sql
Sat Jun 4 23:10:21 2011 : Debug: rlm_sql (sql03): Reserving sql socket id:
3
Sat Jun 4 23:10:21 2011 : Debug: rlm_sql_mysql: query: SELECT
IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM radacct WHERE
UserName='lapzel14'
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] sql_xlat finished
Sat Jun 4 23:10:21 2011 : Debug: rlm_sql (sql03): Released sql socket id: 3
Sat Jun 4 23:10:21 2011 : Info: [noresettotal] expand:
%{sql03:SELECT IFNULL(SUM(AcctInputOctets) + SUM(AcctOutputOctets),0) FROM
radacct WHERE UserName='lapzel14'} -> 2147513300
Sat Jun 4 23:10:21 2011 : Debug: rlm_sqlcounter: (Check item - counter) is
less than zero
Sat Jun 4 23:10:21 2011 : Debug: rlm_sqlcounter: Rejected user lapzel14,
check_item=1705032704, counter=2147513300
Sat Jun 4 23:10:21 2011 : Info: ++[noresettotal] returns reject
Definition of the noresettotal module in counter.conf:
sqlcounter noresettotal {
counter-name = Max-All-Total-Octets
check-name = Max-All-Total
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql03
key = User-Name
reset = never
query = "SELECT IFNULL(SUM(AcctInputOctets) +
SUM(AcctOutputOctets),0) FROM radacct WHERE UserName='%{%k}'"
}
and here the radcheck values for user 'lapzel14':
[image: Full
Texts]
id
UserName
Attribute
op
Value
Double-up of radacct entries anmd radacct entries with UserName empty
Dear All, I am running freeradius 2.10 with mysql for some time now, currently on Ubuntu 9.10, NASes are various router models running Openwrt and Coova-Chilli. I am having trouble with my radacct table. Which creates some 80k entries per annum. For about 1% of users it contains doubled up entries (entries with same AcctSessionId and same AcctUniqueId) and also lots of entries with the Username being empty (as opposed to either filled or Null). Example extract from radacct: RadAcctId AcctSessionId AcctUniqueIdUserNameRealm NASIPAddressNASPortId NASPortType AcctStartTime AcctStopTime AcctSessionTime AcctAuthentic ConnectInfo_start ConnectInfo_stop AcctInputOctets AcctOutputOctetsCalledStationId CallingStationId AcctTerminateCause ServiceType FramedProtocol FramedIPAddress AcctStartDelay AcctStopDelay xascendsessionsvrkey 202678 4c2c5c720002b50051a7f2d0e210jasdoxur7 192.168.182.1 2 Wireless-802.11 2010-07-01 21:14:47 2010-07-02 01:12:1814251 8100124 28550967 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Lost-Carrier 192.168.182.25 0 0 203366 4c2ce6de0001584e51b205634c34jasdoxur7 192.168.182.1 1 Wireless-802.11 2010-07-02 07:10:24 2010-07-02 10:38:3512491 8620576 48189131 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Lost-Carrier 192.168.182.2 0 0 203370 4c2ce6de0001584e51b205634c34jasdoxur7 192.168.182.1 1 Wireless-802.11 2010-07-02 07:10:24 2010-07-02 10:38:3512491 8620576 48189131 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Lost-Carrier 192.168.182.2 0 0 203374 4c2ce6de0001584e51b205634c34jasdoxur7 192.168.182.1 1 Wireless-802.11 2010-07-02 07:10:24 2010-07-02 10:38:3512491 8620576 48189131 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Lost-Carrier 192.168.182.2 0 0 203378 4c2ce6de0001584e51b205634c34jasdoxur7 192.168.182.1 1 Wireless-802.11 2010-07-02 07:10:24 2010-07-02 10:38:3512491 8620576 48189131 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Lost-Carrier 192.168.182.2 0 0 204458 4c2dd9a90006e3dbcb6e1e8d44dcjasdoxur7 192.168.182.1 6 Wireless-802.11 2010-07-03 00:26:33 2010-07-03 01:28:1637026564061 23169066 00-15-6D-DA-E0-59 00-13-02-89-7D-8E User-Request 192.168.182.28 0 0 204462 4c2dd9a90006e3dbcb6e1e8d44dcjasdoxur7 192.168.182.1 6 Wireless-802.11 2010-07-03 00:26:33 2010-07-03 01:28:1637026564061 23169066 00-15-6D-DA-E0-59 00-13-02-89-7D-8E User-Request 192.168.182.28 0 0 204466 4c2dd9a90006e3dbcb6e1e8d44dcjasdoxur7 192.168.182.1 6 Wireless-802.11 2010-07-03 00:26:33 2010-07-03 01:28:1637026564061 23169066 00-15-6D-DA-E0-59 00-13-02-89-7D-8E User-Request 192.168.182.28 0 0 204470 4c2dd9a90006e3dbcb6e1e8d44dcjasdoxur7 192.168.182.1 6 Wireless-802.11 2010-07-03 00:26:33 2010-07-03 01:28:1637026564061 23169066 00-15-6D-DA-E0-59 00-13-02-89-7D-8E User-Request 192.168.182.28 0 0 204474 4c2dd9a90006e3dbcb6e1e8d44dcjasdoxur7 192.168.182.1 6 Wireless-802.11 2010-07-03 00:26:33 2010-07-03 01:28:1637026564061 23169066 00-15-6D-DA-E0-59 00-13-02-89-7D-8E User-Request 192.168.182.28 0 0 204478 4c2dd9a90006e3dbcb6e1e8d44dcjasdoxur7 192.168.182.1 6 Wireless-802.11 2010-07-03 00:26:34 2010-07-03 01:28:1637026564061 23169066 00-15-6D-DA-E0-59 00-13-02-89-7D-8E User-Request 192.168.182.28 0 0 205110 4c2e6be700021bbe9ab0e9e6321bjasdoxur7 192.168.182.1 2 Wireless-802.11 2010-07-03 10:54:33 2010-07-03 11:14:451212585059 2206797 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Lost-Carrier 192.168.182.6 0 0 205950 4c2f20140007390a572eaaf851c9jasdoxur7 192.168.182.1 7 Wireless-802.11 2010-07-03 23:37:55 2010-07-04 00:13:4821534465894 50532205 00-15-6D-DA-E0-59 00-13-02-89-7D-8E Session-Timeout 192.168.182.23 Here an extract of the sqltrace.log, which shows how the UserName is empty: UPDATE radacct SET framedipaddress = '192.168.182.2', ac
RE: Freeradius-Users Digest, Vol 61, Issue 112
The reset put it back to 9.10.1 which might explain the lack of vpn connection. Would have been good to check the version after resetting and if in doubt reflashing with a newer version. -Original Message- From: freeradius-users-bounces+hanno.schupp=gmail@lists.freeradius.org [mailto:freeradius-users-bounces+hanno.schupp=gmail@lists.freeradius.org ] On Behalf Of freeradius-users-requ...@lists.freeradius.org Sent: Wednesday, 26 May 2010 9:17 p.m. To: freeradius-users@lists.freeradius.org Subject: Freeradius-Users Digest, Vol 61, Issue 112 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-requ...@lists.freeradius.org You can reach the person managing the list at freeradius-users-ow...@lists.freeradius.org When replying, please edit your Subject line so it is more specific than "Re: Contents of Freeradius-Users digest..." - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Huntgroups issue - every user is accepted
-Original Message- From: Alan DeKok [mailto:al...@deployingradius.com] Sent: Monday, 19 January 2009 10:29 p.m. To: FreeRadius users mailing list Subject: Re: Huntgroups issue - every user is accepted Hanno Schupp wrote: > > I am trying to implement huntgroups via MySQL according to > > http://wiki.freeradius.org/SQL_Huntgroup_HOWTO On difference is the > > assignment of huntgroups not according to NAS-IP, but to > > Called-Station-Id. The goal is to suppress roaming between hotspot > > routers, between groups of hotspots. > > > > For that purpose I have inserted the code ... > > In lieu of the module ‘preprocess’ into group ‘authorize’, as advised in > > the HOWTO. > You also seen to be over-riding that in the SQL tables: > > `radgroupcheck` > > `id`, `GroupName`, `Attribute`, `op`, `Value` > > 1, 'TestGroup', 'Huntgroup-Name', ':=', 'Test' > This sets the Huntgroup-Name to "Test". You are right, I checked the tutorial again, and the suggested operator in there is indeed == So now the entry reads: `radgroupcheck` `id`, `GroupName`, `Attribute`, `op`, `Value` 1, 'TestGroup', 'Huntgroup-Name', '==', 'Test' Unfortunately it does not make any difference. > > One would expect the user to be rejected if the user tries to log in to > > the router with the Called-Station-Id '00-1D-7E-E7-96-9F’, However, the > > user is authenticated and not rejected. > You did not configure the server to reject the user if he logs in with > that Called-Station-Id. You configured the server to put him in a > huntgroup if he logs in with that Called-Station-Id. > Did you configure the server to reject users in the "Test-Rejec" > huntgroup? It looks like you didn't. I do not want the user to be rejected per se. I only want the user to be rejected if her own huntgroup as stored in radgroupcheck is different from the huntgroup of the Called-Station-Id in the radhuntgroup table. The goal is to prevent a user to login to a hotspot router, that does not belong to the huntgroup the user belongs to. I am sorry if I have left out any other configuration, but again, according to the howto in the freeradius wiki, what I have configured is all that is necessary. Or are you saying the instructions on http://wiki.freeradius.org/SQL_Huntgroup_HOWTO are incorrect? > > One thing I don’t get is, why is the rlm_sql_mysql module finding the > > Hungroup-Name ‘Test-Rejec’ correctly, but module ‘request’ returns not > > found? > There are explanations for that... Great. Can you please point out where, as neither rlm_sql not /etc/freeradius/sql/mysql/dialup.conf says anything about returned status. > > The user is found in radgroupchek for the correct usergroup > > ‘TestGroup’. As the values in radgroupcheck and radgroupreplycheck do > > not match, the user should be rejected, but the user is accepted. > No. If the values in radgroupcheck do not match it means they do not match. As per above, the howto on the freeradius wiki suggests something very different. If it is incorrect, that how to should be pulled. > You have *other* configurations that let the server authenticate the > request. You did *not* configure the server to reject the request if > it's in the "Test-Rejec" huntgroup. Sure I do, but the wiki documentation suggests that the request would be rejected by the system on reading the radgroupcheck table and realising it has a different huntgroup table than the assigned to the NAS. So let me ask another way, if the documentation is indeed incorrect, how do I reject a request, where Huntgroup of user and NAS do not match? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Huntgroups issue - every user is accepted
-Original Message- From: t...@kalik.net [mailto:t...@kalik.net] Sent: Monday, 19 January 2009 10:52 p.m. To: FreeRadius users mailing list Subject: Re: Huntgroups issue - every user is accepted > >The goal is to suppress roaming between hotspot routers, between groups of > >hotspots. > > > > > >`radhuntgroup` > > > >`id`, `groupname`, `calledstationid` > > > >1, 'Test-Rejec', '00-1D-7E-E7-96-9F' > > > > > > > >`usergroup` > > > >`UserName`, `GroupName`, `priority` > > > >'yubvef13', 'TestGroup', 1 > > > > > This is OK. > > > >`radgroupcheck` > > > >`id`, `GroupName`, `Attribute`, `op`, `Value` > > > >1, 'TestGroup', 'Huntgroup-Name', ':=', 'Test' > > > This doesn't check anything. It sets huntgroup to Test. > As I understand it you want to reject huntgroups that are not Test. So > make such a policy: > Huntgroup-Name != "Test", Auth-Type := Reject Thanks for your response. It overlapped time wise with one from Alan. However, the issue remains: I do not want the user to be rejected per se. I only want the user to be rejected if her own huntgroup as stored in radgroupcheck is different from the huntgroup of the Called-Station-Id in the radhuntgroup table. The goal is to prevent a user to login to a hotspot router, that does not belong to the huntgroup the user belongs to. I am sorry if I have left out any other configuration, but again, according to the howto in the freeradius wiki, what I have configured is all that is necessary. But the wiki seems to be incorrect, so what do I need to configure to have a request rejected, where a user's huntgroup and an NAS huntgroup do not match? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Huntgroups issue - every user is accepted
Dear All,
I am trying to implement huntgroups via MySQL according to
http://wiki.freeradius.org/SQL_Huntgroup_HOWTO On difference is the
assignment of huntgroups not according to NAS-IP, but to Called-Station-Id.
The goal is to suppress roaming between hotspot routers, between groups of
hotspots.
For that purpose I have inserted the code
...
update request {
Huntgroup-Name := "%{sql02:select groupname from
radhuntgroup where calledstationid = '%{Called-Station-Id}'}"
}
...
In lieu of the module 'preprocess' into group 'authorize', as advised in the
HOWTO.
I have maintained the following entries in SQL tables:
`radhuntgroup`
`id`, `groupname`, `calledstationid`
1, 'Test-Rejec', '00-1D-7E-E7-96-9F'
`usergroup`
`UserName`, `GroupName`, `priority`
'yubvef13', 'TestGroup', 1
`radgroupcheck`
`id`, `GroupName`, `Attribute`, `op`, `Value`
1, 'TestGroup', 'Huntgroup-Name', ':=', 'Test'
One would expect the user to be rejected if the user tries to log in to the
router with the Called-Station-Id '00-1D-7E-E7-96-9F', However, the user is
authenticated and not rejected.
Here the relevant parts of the debug:
...
Mon Jan 19 20:57:03 2009 : Info: sql_xlat
Mon Jan 19 20:57:03 2009 : Debug: expand: %{User-Name} -> yubvef13
Mon Jan 19 20:57:03 2009 : Info: sql_set_user escaped user --> 'yubvef13'
Mon Jan 19 20:57:03 2009 : Debug: expand: select groupname from
radhuntgroup where calledstationid = '%{Called-Station-Id}' -> select
groupname from radhuntgroup where calledstationid = '00-1D-7E-E7-96-9F'
Mon Jan 19 20:57:03 2009 : Debug: expand:
/var/log/freeradius/sqltrace.sql -> /var/log/freeradius/sqltrace.sql
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql (sql02): Reserving sql socket id:
3
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql_mysql: query: select groupname
from radhuntgroup where calledstationid = '00-1D-7E-E7-96-9F'
Mon Jan 19 20:57:03 2009 : Info: sql_xlat finished
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql (sql02): Released sql socket id: 3
Mon Jan 19 20:57:03 2009 : Debug: expand: %{sql02:select groupname
from radhuntgroup where calledstationid = '%{Called-Station-Id}'} ->
Test-Rejec
Mon Jan 19 20:57:03 2009 : Info: ++[request] returns notfound
Mon Jan 19 20:57:03 2009 : Info: ++[chap] returns noop
Mon Jan 19 20:57:03 2009 : Info: ++[mschap] returns noop
Mon Jan 19 20:57:03 2009 : Info: [suffix] No '@' in User-Name = "yubvef13",
looking up realm NULL
Mon Jan 19 20:57:03 2009 : Info: [suffix] No such realm "NULL"
Mon Jan 19 20:57:03 2009 : Info: ++[suffix] returns noop
Mon Jan 19 20:57:03 2009 : Info: [eap] No EAP-Message, not doing EAP
Mon Jan 19 20:57:03 2009 : Info: ++[eap] returns noop
Mon Jan 19 20:57:03 2009 : Info: ++- entering redundant-load-balance group
sql0203 {...}
Mon Jan 19 20:57:03 2009 : Debug: expand: %{User-Name} -> yubvef13
Mon Jan 19 20:57:03 2009 : Info: [sql02] sql_set_user escaped user -->
'yubvef13'
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql (sql02): Reserving sql socket id:
2
Mon Jan 19 20:57:03 2009 : Debug: expand: SELECT id, username,
attribute, value, op FROM radcheck WHERE username =
BINARY '%{SQL-User-Name}' ORDER BY id -> SELECT id, username,
attribute, value, op FROM radcheck WHERE username =
BINARY 'yubvef13' ORDER BY id
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql_mysql: query: SELECT id,
username, attribute, value, op FROM radcheck WHERE
username = BINARY 'yubvef13' ORDER BY id
Mon Jan 19 20:57:03 2009 : Info: [sql02] User found in radcheck table
Mon Jan 19 20:57:03 2009 : Debug: expand: SELECT id, username,
attribute, value, op FROM radreply WHERE username =
BINARY '%{SQL-User-Name}' ORDER BY id -> SELECT id, username,
attribute, value, op FROM radreply WHERE username =
BINARY 'yubvef13' ORDER BY id
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql_mysql: query: SELECT id,
username, attribute, value, op FROM radreply WHERE
username = BINARY 'yubvef13' ORDER BY id
Mon Jan 19 20:57:03 2009 : Debug: expand: SELECT groupname
FROM usergroup WHERE username = BINARY '%{SQL-User-Name}'
ORDER BY priority -> SELECT groupname FROM usergroup
WHERE username = BINARY 'yubvef13' ORDER BY priority
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql_mysql: query: SELECT groupname
FROM usergroup WHERE username = BINARY 'yubvef13' ORDER
BY priority
Mon Jan 19 20:57:03 2009 : Debug: expand: SELECT id, groupname,
attribute, Value, op FROM radgroupcheck WHERE
groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname,
attribute, Value, op FROM radgroupcheck WHERE
groupname = 'TestGroup' ORDER BY id
Mon Jan 19 20:57:03 2009 : Debug: rlm_sql_m
Multiple output pairs in rlm_exec - How to format the response pai string?
I try to determine some data externally through an PHP programme with the
'exec' module.
Everything is fine as long as I do only return one value from PHP, the
return attribute is duly accepted and processed.
As soon as I return more than one the exec module gets confused, as
everything is returned in one long string (see debug output). There is no
documentation in the echo or exec module as far as I can see, so I tried
spaces, \r\n, and commas ',' as delimiters. Nothing worked.
Is this a limitation of the exec module, that it only accepts one reply
value back? The name of the parameter 'output_pairs' seems to suggest
otherwise.
Can anyone advise on the proper delimitation of multiple return values?
BTW, thanks everyone for all their help so far.
Debug output:
...
Tue Jan 13 13:38:40 2009 : Debug: expand: %{User-Name} -> yubvef24
Tue Jan 13 13:38:40 2009 : Debug: Exec-Program output:
WISPr-Bandwidth-Max-Down := 256000 Reply-Message := "Connection speed has
been reduced" WISPr-Bandwidth-Max-Up := 32000 Reply-Message :=
"Connection speed has been reduced"
Tue Jan 13 13:38:40 2009 : Debug: Exec-Program-Wait: plaintext:
WISPr-Bandwidth-Max-Down := 256000 Reply-Message := "Connection speed has
been reduced" WISPr-Bandwidth-Max-Up := 32000 Reply-Message :=
"Connection speed has been reduced"
Tue Jan 13 13:38:40 2009 : Debug: Exec-Program: returned: 0
Tue Jan 13 13:38:40 2009 : Info: +++[maxspeedrestrict] returns ok
...
PHP code creating the response:
...
$colname_Recordset1 = "-1";
if (isset($_ENV['USER_NAME'])) {
$colname_Recordset1 = substr($_ENV['USER_NAME'], 1,
strlen($_ENV['USER_NAME']) - 2);
}
$retval = 2;
mysql_select_db($database_chilli, $chilli);
$query_Recordset1 = sprintf("SELECT Value, op FROM radcheck WHERE UserName =
%s AND Attribute = 'Download-Speed-Restricted'",
GetSQLValueString($colname_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $chilli) or die(mysql_error);
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
$query_Recordset2 = sprintf("SELECT Value, op FROM radcheck WHERE UserName =
%s AND Attribute = 'Upload-Speed-Restricted'",
GetSQLValueString($colname_Recordset1, "text"));
$Recordset2 = mysql_query($query_Recordset2, $chilli) or die(mysql_error);
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);
if ($totalRows_Recordset1 == '1') {
echo 'WISPr-Bandwidth-Max-Down '.$row_Recordset1['op'].'
'.$row_Recordset1['Value']." \r\n";
echo 'Reply-Message := "Connection speed has been
reduced"'."\r\n";
$retval = 0;
}
if ($totalRows_Recordset2 == '1') {
echo 'WISPr-Bandwidth-Max-Up '.$row_Recordset2['op'].'
'.$row_Recordset2['Value']." \r\n";
echo 'Reply-Message := "Connection speed has been
reduced"'."\r\n";
$retval = 0;
}
if ($retval == '2') {
echo 'Reply-Message := "Time or Data allowance has been
exhausted"';
}
exit ($retval);
?>
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Freeradius-Users Digest, Vol 45, Issue 31
Hi Ivan, thanks for your response. Unfortunately your response cut over with my response to a previous mail list contributor. I had made some progress with unlang and described my progress in there. I have run against a wall though, when I discovered that I cannot perform any unlang actions after the rlm_sql_counter module issued a reject. Freeradius seems to stop any processing of further modules within the authorisation section, once a reject occurs within rlm_sqlcounter. I had attached debugging and code extracts in the other response. I won't tire you with repetition. Unless I can get freeradius to process the steps in authorize AFTER a reject is issued by rlm_sqlcounter, I cannot see how the suggested solutions for issues 1 and 3 can work. Any idea how to resolve the issue described above? Thanks for the suggestion for issue 2. Cheers Hanno Subject: Re: SQLCounter configuration >1) After the time or data volume for a period (say a month) is reached >the user is automatically disconnected - as expected. Rather them not >allowing to log in , I would like freeradius to return some other attributes >(WISPr-Bandwidth-Max-Up and WISPr-Bandwidth-Max-Down work fine with >ChilliSpot) to be sent to the router for the user, so that a connection is >still possible, but at a much lower maximum speed. Can that be done, and if >so, can someone give me a pointer. I was thinking maybe using unlang, but I >may be barking up the wrong tree. > Create a sql group called slow that enforces those limits. Add the user to that group (with low priority) with unlang (on authentication) or perl sript (on accounting stop packet). Important - remove slow group entries when counter resets. >2) The reset = monthly method seem to the first of the current >month. That is one way of doing it, but I would like the reset to occur at >the same date in the month every month. So if someone signs up, say on the >12th of a month, the reset will occur every month on the 12th. Can that be >done with freeradius? > Yes. Queries are configurable. Don't use %b. Calculate start the way you see fit. >3) Currently I receive something like Reply-Message = "Your maximum >never usage time has been reached" when the counter reaches its limit - >even when what was counted as not time but data volume. Can Reply-Message >for sqlcounter be configured as in the expiration module? > No. But you can sort out the reply with unlang. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Freeradius-Users Digest, Vol 45, Issue 31
>
> > Dear All,
> >
> >
> >
> > I have a few questions regarding the rlm_sqlcounter module and its
> > configuration. I have a freeradius 2.1.0 communicating with a router
> running
> > chillispot 1.1 I set up counters for time, data volume, and with
> different
> > reset periods. All works fine and as expected.
> >
> >
> >
> > Here my questions:
> >
> >
> >
> > 1) After the time or data volume for a period (say a month) is
> reached
> > the user is automatically disconnected – as expected. Rather them not
> > allowing to log in , I would like freeradius to return some other
> attributes
> > (WISPr-Bandwidth-Max-Up and WISPr-Bandwidth-Max-Down work fine with
> > ChilliSpot) to be sent to the router for the user, so that a connection
> is
> > still possible, but at a much lower maximum speed. Can that be done, and
> if
> > so, can someone give me a pointer. I was thinking maybe using unlang, but
> I
> > may be barking up the wrong tree.
> >
>
> You might be better off making changes to your provisioning system
> assuming you are using a database backend although
> you might be able to get away with unlang.
I know 'I might be able to get away with unlang'. I was hoping for a useful
pointer as to how.
I have followed this up further and have a new roadblock. I have put this
code into my 'authorize' section:
...
monthlytotal
if (reject) {
update control {
Auth-Type := "Accept"
}
update reply {
WISPr-Bandwidth-Max-Down := 256000
WISPr-Bandwidth-Max-Up := 32000
}
}
monthlyupload
monthlydownload
logintime
...
Module monthlytotal is behaving as expected (definition below), throwing a
reject, when the so far in the month used Octets exceed the set limit for
the user.
However, what appears to happen when looking through the debug (below),
freeradius appears to stop processing any further modules in the authorise
section. Is that what is expected? If so, can this behaviour be changed
through configuration, or does this require a whole new module?
...
sqlcounter monthlytotal {
counter-name = Max-Monthly-Total-Octets
check-name = Max-Monthly-Total
reply-name = ChilliSpot-Max-Total-Octets
sqlmod-inst = sql02
key = User-Name
reset = monthly
query = "SELECT IFNULL(SUM(AcctInputOctets - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0))+ SUM(AcctOutputOctets -GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)),0) FROM radacct WHERE UserName='%{%k}'
AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
}
...
...
Mon Jan 12 11:49:28 2009 : Debug: rlm_sqlcounter: (Check item - counter) is
less than zero
Mon Jan 12 11:49:28 2009 : Debug: rlm_sqlcounter: Rejected user yubvef24,
check_item=50, counter=535178
Mon Jan 12 11:49:28 2009 : Info: ++[monthlytotal] returns reject
Mon Jan 12 11:49:28 2009 : Info: Using Post-Auth-Type Reject
Mon Jan 12 11:49:28 2009 : Info: +- entering group REJECT {...}
Mon Jan 12 11:49:28 2009 : Debug: expand: %{User-Name} -> yubvef24
Mon Jan 12 11:49:28 2009 : Debug: attr_filter: Matched entry DEFAULT at
line 11
Mon Jan 12 11:49:28 2009 : Info: ++[attr_filter.access_reject] returns
updated
Mon Jan 12 11:49:28 2009 : Info: Delaying reject of request 0 for 1 seconds
...
>
>
> >
> >
> > 2) The reset = monthly method seem to the first of the current
> > month. That is one way of doing it, but I would like the reset to occur
> at
> > the same date in the month every month. So if someone signs up, say on
> the
> > 12th of a month, the reset will occur every month on the 12th. Can that
> be
> > done with freeradius?
> >
> >
>
> rlm_sqlcounter only support calendar month or day or week.
I am surprised to hear that, as this seems to be the standard way any ISP I
came accross in this part of the world seems to define a 'month', including
large internationals. If the final answer remains 'no, this cannot be done',
who do I need to contact to discuss sponsoring the development of such a new
feature within the module?
>
>
> >
> > 3) Currently I receive something like Reply-Message = "Your
> maximum
> > never usage time has been reached" when the counter reaches its limit –
> > even when what was counted as not time but data volume. Can Reply-Message
> > for sqlcounter be configured as in the expiration module?
> >
> >
>
> rlm_sqlcounter does not have provision for cusom reply messages. There
> was a patch floating around to send custom reply messages from
> rlm_sqlcounter. Probably you should look in mailing list archives.
>
I have done that, thanks. Neither search for 'rlm_sqlcounter' nor
'Reply-Message' yielded anything useful.
>
> >
> > 4) One surprise I have had: I don't quite understand how the system
> > calculates the day for the beginning of the res
SQLCounter configuration
Dear All, I have a few questions regarding the rlm_sqlcounter module and its configuration. I have a freeradius 2.1.0 communicating with a router running chillispot 1.1 I set up counters for time, data volume, and with different reset periods. All works fine and as expected. Here my questions: 1) After the time or data volume for a period (say a month) is reached the user is automatically disconnected - as expected. Rather them not allowing to log in , I would like freeradius to return some other attributes (WISPr-Bandwidth-Max-Up and WISPr-Bandwidth-Max-Down work fine with ChilliSpot) to be sent to the router for the user, so that a connection is still possible, but at a much lower maximum speed. Can that be done, and if so, can someone give me a pointer. I was thinking maybe using unlang, but I may be barking up the wrong tree. 2) The reset = monthly method seem to the first of the current month. That is one way of doing it, but I would like the reset to occur at the same date in the month every month. So if someone signs up, say on the 12th of a month, the reset will occur every month on the 12th. Can that be done with freeradius? 3) Currently I receive something like Reply-Message = "Your maximum never usage time has been reached" when the counter reaches its limit - even when what was counted as not time but data volume. Can Reply-Message for sqlcounter be configured as in the expiration module? 4) One surprise I have had: I don't quite understand how the system calculates the day for the beginning of the reset = weekly. I appreciate that calculating the day of the week is not easy as for example first of the current month or beginning of the day, so I was not surprised to not have a Monday, but I was surprised to see the SQL query run with 'Fri Jan 09 2009 00:00:00 GMT+1300 (New Zealand Daylight Time)' as beginning of the week, when running the query on a Saturday. BTW, SQL time is configured correctly, and the UNIX_TIMESTAMP is giving a correct timestamp for local time. What am I missing? (Note: When setting reset = 7d, I get something more explainable like 'Sat Jan 10 2009 00:00:00 GMT+1300 (New Zealand Daylight Time)', which is the beginning of the today's day. Should reset = weekly and reset =7d yield the same result? If not, what is weekly vs 7d, and why does the week not start on a Monday? Any hints welcome - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Error in SQL statement expansion? And how to correct it?
Dear All,
I found the following in my freeradius 2.1.0 debug on Ubuntu 8.10 for my
simultaneous use check:
...
Sat Jan 10 10:21:47 2009 : Debug: expand: SELECT COUNT(*)
FROM radacct WHERE username =
'%{SQL-User-Name}' AND acctstoptime IS NULL
AND callingstationid <> '{%Calling-Station-Id}' -> SELECT COUNT(*)
FROM radacct WHERE username = 'yubvef23'
AND acctstoptime IS NULL AND callingstationid
<> '{anyonealling-Station-Id}'
...
It appears that after expansion of {%Calling-Station-Id} the looked for
value is {anyonealling-Station-Id}. The {} suggest this si a value that
should have been expanded/replaced further. The spelling -
{anyonealling-Station-Id} as opposed to {anyonecalling-Station-Id} as one
might expect - suggested the expansion/replacement fails due to a syntax
error. I have looked in the sql.conf and includes where the SQL statements
are defined. The SQL statement looks OK but I cannot find any definition of
or reference to {anyonealling-Station-Id}.
I would like to confirm firstly that this is indeed a syntax error and
secondly, where the value of {anyonealling-Station-Id} may be defined, so I
can fix this.
Thanks
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
EAP-TLS without client authentication - Another possible approach to achieve this?
Hi, Another approach you may wish to try is using the hotspot firmware on the hotspot router to help you in conjunction with the freeradius server. Coovachilli seems to have a solution, based on freeradius, jradius and coovachilli (maintained branch of chillispot, as I understand). Have a look here http://coova.org/wiki/index.php/CoovaChilli/WithWPACaptivePortal whether that is what you are looking for. Cheers Hanno Schupp -Original Message- From: Alan DeKok [mailto:al...@deployingradius.com] Sent: Friday, 9 January 2009 10:00 a.m. To: FreeRadius users mailing list Subject: Re: EAP-TLS without client authentication Christopher Byrd wrote: > What I am looking for a way to replace open, clear text WiFi at public > hotspots (and possibly newly installed home WiFi routers) with > something more secure. This is network layer security. > That's where WPA-Enterprise comes in, with it's > support for 802.1x and EAP. Yes. EAP provides keying material. > In "A secure Wireless LAN hotspot for anonymous users" > (http://blogs.zdnet.com/Ou/?p=587), George Ou proposed that public > access wireless operators could use WPA-Enterprise with > PEAPv0/MSCHAPv2 and a well known username and password combination > such as guest/guest. Because PEAP uses TLS, the keying material is > sent securely from the RADIUS server to the client, even if the client > side authentication is well known. That's marketing nonsense. Don't believe random garbage you read on industry web sites. > The two downsides of this approach is similar to PSKs, in that you > have to have a mechanism to communicate the configuration information, > and the configuration is burdensome on the user. I have proposed this > solution to hotspot operators whom, after testing, have rejected it as > too difficult for the user. I spend a fair amount of time working with WiFi operators && telecommunications companies. None of them will do EAP unless it's easy for their end users. > When thinking about George's proposed solution, I considered that > WPA-Enterprise would be useful for these hotspots if we could use a > EAP method that authenticates the identity of the server and provides > for secure transfer of the keying material to the client without > requiring the client to authenticate itself. RFC 5216 "The EAP-TLS > Authentication Protocol" (http://www.ietf.org/rfc/rfc5216.txt) has > clarified that it is not mandatory that the EAP server require peer > authentication: Ignore George's blog. It's marketing material. It has little to no relevance for operators running real networks. > "The certificate_request message is included when the server desires > the peer to authenticate itself via public key. While the EAP server > SHOULD require peer authentication, this is not mandatory, since there > are circumstances in which peer authentication will not be needed > (e.g., emergency services, as described in [UNAUTH]), or where the > peer will authenticate via some other means." Emergency services are being removed from many network access standards. That use is being abused, and isn't useful. > It seems that because the RFC does not require EAP-TLS to authenticate > the client, It does. The only time the client isn't being authenticated is via mechanisms that aren't implement, aren't supported, and are being deprecated. > it could provide that mechanism if there were a RADIUS > server that supported EAP-TLS without client authentication. > Obviously FreeRADIUS seems ideal for this purpose because of it's > GPLv2 license, community support, and wide acceptance. Operators will not agree to this. > Of course this will also rely on the popular 802.1x supplicants > supporting the same. I plan on testing the client supplicant piece > after setting up a server that can handle EAP-TLS without client > certificate authentication.Hopefully it could be configured > natively to do so, or with code changes if necessary. Supplicants do not currently support this. I know all of the major supplicant vendors, and I doubt very much this functionality will go in. Perhaps you could describe what the *problem* is in more detail. Talking about specific solutions often results in the discussion getting bogged down in details about why it won't work that way. As background, many operators are looking into moving to 802.1x everywhere. If it's supported on their home networks, then their users will have it configured, and it will (usually) work on visited networks, too. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
