Hello Everyone, On Freeradius Version 2.0.0, I have an issue with Simultaneous-Use. Multiple logins are being caught, but the issue seems to be that the checks seem to be catching much more than they should be. In particular, I was looking at this user: mysql> select * from radgroupcheck; +----+-----------+------------------+----+-------+ | id | GroupName | Attribute | op | Value | +----+-----------+------------------+----+-------+ | 1 | dynamic | Auth-Type | == | Local | | 2 | static | Auth-Type | == | Local | | 8 | static | Simultaneous-Use | := | 1 | | 7 | dynamic | Simultaneous-Use | := | 1 | +----+-----------+------------------+----+-------+ 4 rows in set (0.00 sec) mysql> select * from radusergroup where username='Kat'; +----------+-----------+----------+ | UserName | GroupName | priority | +----------+-----------+----------+ | Kat | static | 1 | +----------+-----------+----------+ 1 row in set (0.00 sec) mysql> select * from radcheck where username='Kat'; +------+----------+------------+----+----------------------+ | id | UserName | Attribute | op | Value | +------+----------+------------+----+----------------------+ | 4946 | Kat | Password | == |testing123 | | 4947 | Kat | expiration | := | 15 Feb 2008 15:01:32 | +------+----------+------------+----+----------------------+ 2 rows in set (0.00 sec) I took a look at the SQL Queries used by freeradius to check the logins and decided to run them manually on my sql database: mysql> SELECT COUNT(*) FROM radacct WHERE username = 'Kat' AND acctstoptime = 0; +----------+ | COUNT(*) | +----------+ | 16 | +----------+ 1 row in set (0.00 sec) But if I run: mysql> select * from radacct where username ='Kat' \G; *************************** 26. row *************************** radacctid: 1789 acctsessionid: 00:0E:0C:2D:18:56:12008736891000 acctuniqueid: 9d581ecac1736df4 username: kat groupname: realm: nasipaddress: ###.###.###.### nasportid: nasporttype: Wireless-802.11 acctstarttime: 2008-01-20 23:22:02 acctstoptime: 2008-01-20 23:22:02 acctsessiontime: 1322 acctauthentic: RADIUS connectinfo_start: connectinfo_stop: acctinputoctets: 425841 acctoutputoctets: 170623 calledstationid: 00:0E:0C:2D:18:56 callingstationid: 00:13:e8:6a:e1:cf acctterminatecause: Admin-Reset servicetype: framedprotocol: framedipaddress: 10.51.2.56 acctstartdelay: 0 acctstopdelay: 0 xascendsessionsvrkey: NULL 26 rows in set (0.00 sec) You can see from the last row returned that they are indeed logged out. But attempts to login are futile as radpostauth shows: +------+------+--------+---------------+--------------------------+ | id | user | pass | reply | date | +------+------+--------+---------------+--------------------------+ | 97264 | Kat | testing123 | Access-Reject | 2008-01-21 09:40:50 | +------+------+--------+---------------+--------------------------+ The problem is, if you try to log this person in the server saying that they are already logged in, because the simul_count_query is picking up *ALL* rows which have accountstoptime = 0. This is an problem because all the accounting records are kept in the same place as the start and stop records, which means that there will always be records with an acctstoptime=0 in that table. Am I doing anything wrong here? Any tips on how to fix this problem would be much appreciated. Thanks in Advance! Regards, Dryw Paulic
On Monday 21 January 2008 14:19:06 Dryw Paulic wrote:
mysql> select * from radgroupcheck; +----+-----------+------------------+----+-------+ | id | GroupName | Attribute | op | Value | +----+-----------+------------------+----+-------+ | 1 | dynamic | Auth-Type | == | Local | | 2 | static | Auth-Type | == | Local |
Don't do this. The operator is incorrect as is nearly every use of Auth-Type.
mysql> SELECT COUNT(*) FROM radacct WHERE username = 'Kat' AND acctstoptime = 0; ... mysql> select * from radacct where username ='Kat' \G;
What is shown when you use the full where clause from the previous command? What version of MySQL are you using? I just tried this with 5.0.48 and 'datefield = 0' does not match on datetime fields. If you're using the V2.0.0 schema, that SQL query should be changed to 'acctstoptime IS NULL'. Try this from your SQL command line and see if it gives the desired results for both connected and disconnected users. Kevin Bonner
Hi Kevin, To answer your questions: Auth-Type: Ok. Mysql Version: 5.0.22 Freeradius_Schema: V1.5 (It is what came with FreeRADIUS Version 2.0.0-pre2) Original Query: select * from radacct where username ='absolute' \G; *************************** 2. row *************************** radacctid: 81 acctsessionid: 00:0E:0C:2D:18:56:12009942091051 acctuniqueid: 5e5fbec08c10c41f username: absolute groupname: realm: nasipaddress: ###.###.###.### nasportid: nasporttype: Wireless-802.11 acctstarttime: 2008-01-22 08:50:32 acctstoptime: 0000-00-00 00:00:00 acctsessiontime: 0 acctauthentic: Local connectinfo_start: connectinfo_stop: acctinputoctets: 0 acctoutputoctets: 0 calledstationid: 00:0E:0C:2D:18:56 callingstationid: 00:1e:4c:13:c1:37 acctterminatecause: servicetype: framedprotocol: framedipaddress: 10.51.1.63 acctstartdelay: 0 acctstopdelay: 0 xascendsessionsvrkey: *************************** 3. row *************************** radacctid: 85 acctsessionid: 00:0E:0C:2D:18:56:12009942091051 acctuniqueid: 5e5fbec08c10c41f username: absolute groupname: realm: nasipaddress: ###.###.###.### nasportid: nasporttype: Wireless-802.11 acctstarttime: 2008-01-22 08:50:31 acctstoptime: 2008-01-22 08:51:06 acctsessiontime: 35 acctauthentic: RADIUS connectinfo_start: connectinfo_stop: acctinputoctets: 273675 acctoutputoctets: 128263 calledstationid: 00:0E:0C:2D:18:56 callingstationid: 00:1e:4c:13:c1:37 acctterminatecause: Admin-Reset servicetype: framedprotocol: framedipaddress: 10.51.1.63 acctstartdelay: 0 acctstopdelay: 0 xascendsessionsvrkey: NULL 3 row in set (0.00 sec) Full Where Clause: select * from radacct where username ='absolute' AND acctstoptime=0 \G; *************************** 1. row *************************** radacctid: 81 acctsessionid: 00:0E:0C:2D:18:56:12009942091051 acctuniqueid: 5e5fbec08c10c41f username: absolute groupname: realm: nasipaddress: ###.###.###.### nasportid: nasporttype: Wireless-802.11 acctstarttime: 2008-01-22 08:50:32 acctstoptime: 0000-00-00 00:00:00 acctsessiontime: 0 acctauthentic: Local connectinfo_start: connectinfo_stop: acctinputoctets: 0 acctoutputoctets: 0 calledstationid: 00:0E:0C:2D:18:56 callingstationid: 00:1e:4c:13:c1:37 acctterminatecause: servicetype: framedprotocol: framedipaddress: 10.51.1.63 acctstartdelay: 0 acctstopdelay: 0 xascendsessionsvrkey: 1 row in set (0.00 sec) mysql> select count(*) from radacct where username ='absolute' AND acctstoptime=0; +----------+ | count(*) | +----------+ | 1 | +----------+ If I look at my /var/log/radius I can see: Tue Jan 22 08:51:06 2008 User-Name = "absolute" Acct-Status-Type = Stop Acct-Session-Id = "00:0E:0C:2D:18:56:120099420910511" Acct-Authentic = RADIUS Acct-Session-Time = 35 NAS-Identifier = "#######" NAS-IP-Address = ###.###.###.### Acct-Input-Octets = 273675 Acct-Output-Octets = 128263 Acct-Input-Packets = 961 Acct-Output-Packets = 1002 Calling-Station-Id = "00:1e:4c:13:c1:37" Called-Station-Id = "00:0E:0C:2D:18:56" Framed-IP-Address = ###.###.###.### NAS-Port-Type = Wireless-802.11 Class = 0x20 Acct-Terminate-Cause = Admin-Reset Acct-Unique-Session-Id = "5e5fbec08c10c41f" Timestamp = 1201020666 Request-Authenticator = Verified So, the session has been terminated as you can see by the last record in the first query, but the query with the full where clause picks up on the old accounting record. Using acctstoptime IS NULL has the same result. 'datefield = 0' seems to match on datetime fields on my version of Mysql. (In schema 1.5 acctstoptime datetime NOT NULL default '0000-00-00 00:00:00'). From what I can see start, interim and stop records are being recorded just fine. The issue is that the sql queries are matching old accounting records. Any idea on how I can fix this issue? If there is something I'm not understanding, please let me know. At this point I think my next step might be to update to the new version of freeradius, with the full 2.0 schema. Any other suggestions? Thanks very much for the reply, Dryw Paulic -----Original Message----- From: freeradius-users-bounces+dpaulic=tranzeo.com@lists.freeradius.org [mailto:freeradius-users-bounces+dpaulic=tranzeo.com@lists.freeradius.or g] On Behalf Of Kevin Bonner Sent: Monday, January 21, 2008 4:10 PM To: FreeRadius users mailing list Subject: Re: FreeRadius V2.0.0 Simultaneous-Use Problems On Monday 21 January 2008 14:19:06 Dryw Paulic wrote:
mysql> select * from radgroupcheck; +----+-----------+------------------+----+-------+ | id | GroupName | Attribute | op | Value | +----+-----------+------------------+----+-------+ | 1 | dynamic | Auth-Type | == | Local | | 2 | static | Auth-Type | == | Local |
Don't do this. The operator is incorrect as is nearly every use of Auth-Type.
mysql> SELECT COUNT(*) FROM radacct WHERE username = 'Kat' AND acctstoptime = 0; ... mysql> select * from radacct where username ='Kat' \G;
What is shown when you use the full where clause from the previous command? What version of MySQL are you using? I just tried this with 5.0.48 and 'datefield = 0' does not match on datetime fields. If you're using the V2.0.0 schema, that SQL query should be changed to 'acctstoptime IS NULL'. Try this from your SQL command line and see if it gives the desired results for both connected and disconnected users. Kevin Bonner
From what I can see start, interim and stop records are being recorded just fine. The issue is that the sql queries are matching old accounting records. Any idea on how I can fix this issue? If there is something I'm not understanding, please let me know.
At this point I think my next step might be to update to the new version of freeradius, with the full 2.0 schema. Any other suggestions?
Thanks very much for the reply,
Dryw Paulic
Not really. You have 3 start records for that session recorded with different session start times. But theere is no delay (acctstartdelay) in them. Do a debug of accounting after login and see what's going on: is your database too slow, so new packets arrive before "old" ones are inserted into the database and is there Acct-Delay-Time in second and third packet. You should be able to correct both things on the NAS side of things by adjusting the time it waits for acconting response packets. Ivan Kalik Kalik Informatika ISP
I took a look at the SQL Queries used by freeradius to check the logins and decided to run them manually on my sql database:
mysql> SELECT COUNT(*) FROM radacct WHERE username = 'Kat' AND acctstoptime = 0; +----------+ | COUNT(*) | +----------+ | 16 | +----------+ 1 row in set (0.00 sec)
So, you have 16 open sessions for that user. Why? Do they have some sentimental value to you? If not, just delete them. They most likely occured when your radius server was down while users logged off.
The problem is, if you try to log this person in the server saying that they are already logged in, because the simul_count_query is picking up *ALL* rows which have accountstoptime = 0. This is an problem because all the accounting records are kept in the same place as the start and stop records, which means that there will always be records with an acctstoptime=0 in that table. Am I doing anything wrong here? Any tips on how to fix this problem would be much appreciated.
It looks to me that you are switching off your radius server and not clearing up the mess in accounting records that happens because of that. If you are not doing that but Stop packets are being lost because of poor and unreliable communication between radius server and your NAS, you need to fix that. Ivan Kalik Kalik Informatika ISP
participants (3)
-
Dryw Paulic -
Kevin Bonner -
tnt＠kalik.co.yu