radius does not check table radgroupcheck
Hi! I'm having the following problem: Any item that I put in radgroupcheck is not being considered by the radius ... the radius only checks the table radcheck ... I'm using Radius 2 with mysql database. *nas *id: 200 idrevenda: 1 nasname: 192.168.0.10 shortname: rb45g type: other ports: 1812 secret: 123 *radcheck* idrevenda: 1 username: "john" attribute: "user-password" op: "==" value: "123456" *radgroupcheck* idrevenda: 1 groupname: "office" attribute: "Calling-Station-Id" op: "==" value: "00:00:00:00:00:00" *radusergroup* idrevenda: 1 username: "john" groupname: "office" priority: 1 Suppose that the John's MAC Address is 00:a2:bf:66:dd:ff ... the radius should not allow the access... # radtest john 123456 192.168.0.10 10 123 Sending Access-Request of id 218 to 192.168.0.10 port 1812 User-Name = "john" User-Password = "123456" NAS-IP-Address = 192.168.0.10 NAS-Port = 10 rad_recv: Access-Accept packet from host 192.168.0.10 port 1812, id=218, length=20 #radius -X ... rad_recv: Access-Request packet from host 192.168.0.10 port 44307, id=218, length=56 User-Name = "john" User-Password = "123456" NAS-IP-Address = 192.168.0.10 NAS-Port = 10 +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop ++[files] returns noop [sql] expand: %{User-Name} -> john [sql] sql_set_user escaped user --> 'john' rlm_sql (sql): Reserving sql socket id: 3 [sql] expand: SELECT aut.id, aut.username, aut.attribute, aut.value, aut.op FROM radcheck aut WHERE aut.username = '%{SQL-User-Name}' and aut.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY aut.id-> SELECT aut.id, aut.username, aut.attribute, aut.value, aut.op FROM radcheck aut WHERE aut.username = 'john' and aut.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '192.168.0.10' LIMIT 0,1) ORDER BY aut.id WARNING: Found User-Password == "...". WARNING: Are you sure you don't mean Cleartext-Password? WARNING: See "man rlm_pap" for more information. [sql] User found in radcheck table [sql] expand: SELECT rp.id, rp.username, rp.attribute, rp.value, rp.op FROM radreply rp WHERE rp.username = '%{SQL-User-Name}' and rp.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY rp.id -> SELECT rp.id, rp.username, rp.attribute, rp.value, rp.op FROM radreply rp WHERE rp.username = 'john' and rp.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '192.168.0.10' LIMIT 0,1) ORDER BY rp.id [sql] expand: SELECT ug.groupname FROM radusergroup ug WHERE ug.username = '%{SQL-User-Name}' and ug.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY ug.priority -> SELECT ug.groupname FROM radusergroup ug WHERE ug.username = 'john' and ug.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '192.168.0.10' LIMIT 0,1) ORDER BY ug.priority [sql] expand: SELECT gc.id, gc.groupname, gc.attribute, gc.Value, gc.op FROM radgroupcheck gc WHERE gc.groupname = '%{Sql-Group}' and gc.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY gc.id -> SELECT gc.id, gc.groupname, gc.attribute, gc.Value, gc.op FROM radgroupcheck gc WHERE gc.groupname = '27' and gc.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '192.168.0.10' LIMIT 0,1) ORDER BY gc.id rlm_sql (sql): Released sql socket id: 3 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! Replacing User-Password in config items with Cleartext-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! Please update your configuration so that the "known good" !!! !!! clear text password is in Cleartext-Password, and not in User-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! +- entering group PAP {...} [pap] login attempt with password "123456" [pap] Using clear text password "123456" [pap] User authenticated successfully ++[pap] returns ok expand: -> Login OK: [john/123456] (from client radtest port 10) +- entering group post-auth {...} [sql] expand: %{User-Name} -> john [sql] sql_set_user escaped user --> 'john' [sql] expand: %{User-Password} -> 123456 [sql] expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'john', '123456', 'Access-Accept', '2010-08-06 17:30:29') rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'john', '123456', 'Access-Accept', '2010-08-06 17:30:29') rlm_sql (sql): Reserving sql socket id: 2 rlm_sql (sql): Released sql socket id: 2 ++[sql] returns ok ++[exec] returns noop Sending Access-Accept of id 218 to 192.168.0.10 port 44307 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 218 with timestamp +6 Ready to process requests. *dialup.conf: *authorize_check_query = "SELECT aut.id, aut.username, aut.attribute, aut.value, aut.op \ FROM ${authcheck_table} aut \ WHERE aut.username = '%{SQL-User-Name}' \ and aut.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \ ORDER BY aut.id" authorize_reply_query = "SELECT rp.id, rp.username, rp.attribute, rp.value, rp.op \ FROM ${authreply_table} rp \ WHERE rp.username = '%{SQL-User-Name}' \ and rp.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \ ORDER BY rp.id" group_membership_query = "SELECT ug.groupname \ FROM ${usergroup_table} ug \ WHERE ug.username = '%{SQL-User-Name}' \ and ug.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \ ORDER BY ug.priority" authorize_group_check_query = "SELECT gc.id, gc.groupname, gc.attribute, \ gc.Value, gc.op \ FROM ${groupcheck_table} gc \ WHERE gc.groupname = '%{Sql-Group}' \ and gc.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \ ORDER BY gc.id" authorize_group_reply_query = "SELECT gr.id, gr.groupname, gr.attribute, \ gr.value, gr.op \ FROM ${groupreply_table} gr \ WHERE gr.groupname = '%{Sql-Group}' \ and gr.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \ ORDER BY gr.id"* *What can it be? Thank's you for help! -- Erick de A. Fabbio MSN/GTalk: erickfabbio@gmail.com Skype: erickfabbio
Hi everyone. I got freeradius to AAA the access to a Cisco Routers. If for some reason you disconnect because the router power off, an accounting finish packets is not sent to my radius, so in the DB looks like the user is still logged in.... an example: mysql> select radacctid,username,acctstarttime,acctstoptime from radacct where username = "jhon.doe" and acctstarttime > "2010-08-05 07:45:30"; +-----------+-----------------+----------------------+-------------------------+ | radacctid | username | acctstarttime | acctstoptime | +----------- +-----------------+---------------------+-------------------------+ | 93760 | jhon.doe | 2010-08-05 07:45:31 | NULL | | 93761 | jhon.doe | 2010-08-05 07:45:50 | NULL | | 93762 | jhon.doe | 2010-08-05 07:52:23 | NULL | What can I do to update this entries after a certain time has pass, or do something else so fix those entries? Thanks in advance.
On Aug 6, 2010, at 2:53 PM, Siryx XL wrote:
Hi everyone.
I got freeradius to AAA the access to a Cisco Routers.
If for some reason you disconnect because the router power off, an accounting finish packets is not sent to my radius, so in the DB looks like the user is still logged in....
an example:
mysql> select radacctid,username,acctstarttime,acctstoptime from radacct where username = "jhon.doe" and acctstarttime > "2010-08-05 07:45:30"; +-----------+-----------------+----------------------+-------------------------+ | radacctid | username | acctstarttime | acctstoptime | +----------- +-----------------+---------------------+-------------------------+ | 93760 | jhon.doe | 2010-08-05 07:45:31 | NULL | | 93761 | jhon.doe | 2010-08-05 07:45:50 | NULL | | 93762 | jhon.doe | 2010-08-05 07:52:23 | NULL |
What can I do to update this entries after a certain time has pass, or do something else so fix those entries?
You have three options, mail Cisco and ask why their products don't send Accounting-ON messages on startup (like pretty much every other well designed NAS does). Switch NAS vendor. Or write a script that polls the NAS that according to the accounting database have clients still present and close sessions in the database where they no longer exist on the NAS. -Arran
Thanks in advance. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Actually, option 4. If your NAS supports interim updates, you can add an additional field to the accounting database, and update the timestamp in this field every time an interim update packet is received. Then run a cron job to check for timestamps which are older than the interim update period and close those entries. You can do the updates with unlang and SQL xlat. -Arran On Aug 6, 2010, at 2:53 PM, Siryx XL wrote:
Hi everyone.
I got freeradius to AAA the access to a Cisco Routers.
If for some reason you disconnect because the router power off, an accounting finish packets is not sent to my radius, so in the DB looks like the user is still logged in....
an example:
mysql> select radacctid,username,acctstarttime,acctstoptime from radacct where username = "jhon.doe" and acctstarttime > "2010-08-05 07:45:30"; +-----------+-----------------+----------------------+-------------------------+ | radacctid | username | acctstarttime | acctstoptime | +----------- +-----------------+---------------------+-------------------------+ | 93760 | jhon.doe | 2010-08-05 07:45:31 | NULL | | 93761 | jhon.doe | 2010-08-05 07:45:50 | NULL | | 93762 | jhon.doe | 2010-08-05 07:52:23 | NULL |
What can I do to update this entries after a certain time has pass, or do something else so fix those entries?
Thanks in advance. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Or you can simple make small changes in SQL queries for accounting and you won't need to use unlang... Arran Cudbard-Bell wrote:
Actually, option 4. If your NAS supports interim updates, you can add an additional field to the accounting database, and update the timestamp in this field every time an interim update packet is received. Then run a cron job to check for timestamps which are older than the interim update period and close those entries.
You can do the updates with unlang and SQL xlat.
-Arran
On Aug 6, 2010, at 2:53 PM, Siryx XL wrote:
Hi everyone.
I got freeradius to AAA the access to a Cisco Routers.
If for some reason you disconnect because the router power off, an accounting finish packets is not sent to my radius, so in the DB looks like the user is still logged in....
an example:
mysql> select radacctid,username,acctstarttime,acctstoptime from radacct where username = "jhon.doe" and acctstarttime > "2010-08-05 07:45:30"; +-----------+-----------------+----------------------+-------------------------+ | radacctid | username | acctstarttime | acctstoptime | +----------- +-----------------+---------------------+-------------------------+ | 93760 | jhon.doe | 2010-08-05 07:45:31 | NULL | | 93761 | jhon.doe | 2010-08-05 07:45:50 | NULL | | 93762 | jhon.doe | 2010-08-05 07:52:23 | NULL |
What can I do to update this entries after a certain time has pass, or do something else so fix those entries?
Thanks in advance. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Aug 6, 2010, at 3:12 PM, Marinko Tarlac wrote:
Or you can simple make small changes in SQL queries for accounting and you won't need to use unlang...
I guess if you really just want to: "update this entries after a certain time has pass" Then that's easy enough with a simple update query, however it's generally a bad idea to just close out sessions because they've run on too long. Unless of course you don't care about the accuracy of your accounting database. -Arran
Arran Cudbard-Bell wrote:
Actually, option 4. If your NAS supports interim updates, you can add an additional field to the accounting database, and update the timestamp in this field every time an interim update packet is received. Then run a cron job to check for timestamps which are older than the interim update period and close those entries.
You can do the updates with unlang and SQL xlat.
-Arran
On Aug 6, 2010, at 2:53 PM, Siryx XL wrote:
Hi everyone.
I got freeradius to AAA the access to a Cisco Routers.
If for some reason you disconnect because the router power off, an accounting finish packets is not sent to my radius, so in the DB looks like the user is still logged in....
an example:
mysql> select radacctid,username,acctstarttime,acctstoptime from radacct where username = "jhon.doe" and acctstarttime > "2010-08-05 07:45:30"; +-----------+-----------------+----------------------+-------------------------+ | radacctid | username | acctstarttime | acctstoptime | +----------- +-----------------+---------------------+-------------------------+ | 93760 | jhon.doe | 2010-08-05 07:45:31 | NULL | | 93761 | jhon.doe | 2010-08-05 07:45:50 | NULL | | 93762 | jhon.doe | 2010-08-05 07:52:23 | NULL |
What can I do to update this entries after a certain time has pass, or do something else so fix those entries? Thanks in advance. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Subject: Re: Freeradius accounting issues From: a.cudbardb@googlemail.com Date: Fri, 6 Aug 2010 15:19:24 -0700 To: freeradius-users@lists.freeradius.org
On Aug 6, 2010, at 3:12 PM, Marinko Tarlac wrote:
Or you can simple make small changes in SQL queries for accounting and you won't need to use unlang...
What do you have exactly in mind?
I guess if you really just want to: "update this entries after a certain time has pass"
I have thinking in that these days that option too.
Then that's easy enough with a simple update query, however it's generally a bad idea to just close out sessions because they've run on too long. Unless of course you don't care about the accuracy of your accounting database.
-Arran
Arran Cudbard-Bell wrote:
Actually, option 4. If your NAS supports interim updates, you can add an additional field to the accounting database, and update the timestamp in this field every time an interim update packet is received. Then run a cron job to check for timestamps which are older than the interim update period and close those entries.
You can do the updates with unlang and SQL xlat.
-Arran
On Aug 6, 2010, at 2:53 PM, Siryx XL wrote:
Hi everyone.
I got freeradius to AAA the access to a Cisco Routers.
If for some reason you disconnect because the router power off, an accounting finish packets is not sent to my radius, so in the DB looks like the user is still logged in....
an example:
mysql> select radacctid,username,acctstarttime,acctstoptime from radacct where username = "jhon.doe" and acctstarttime > "2010-08-05 07:45:30"; +-----------+-----------------+----------------------+-------------------------+ | radacctid | username | acctstarttime | acctstoptime | +----------- +-----------------+---------------------+-------------------------+ | 93760 | jhon.doe | 2010-08-05 07:45:31 | NULL | | 93761 | jhon.doe | 2010-08-05 07:45:50 | NULL | | 93762 | jhon.doe | 2010-08-05 07:52:23 | NULL |
What can I do to update this entries after a certain time has pass, or do something else so fix those entries? Thanks in advance. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
"Erick de A. Fabbio" <erickfabbio@gmail.com> writes:
*radusergroup* idrevenda: 1 username: "john" groupname: "office" priority: 1
Nope, I don't think so.
[sql] expand: SELECT ug.groupname FROM radusergroup ug WHERE ug.username = '%{SQL-User-Name}' and ug.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY ug.priority -> SELECT ug.groupname FROM radusergroup ug WHERE ug.username = 'john' and ug.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '192.168.0.10' LIMIT 0,1) ORDER BY ug.priority [sql] expand: SELECT gc.id, gc.groupname, gc.attribute, gc.Value, gc.op FROM radgroupcheck gc WHERE gc.groupname = '%{Sql-Group}' and gc.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY gc.id -> SELECT gc.id, gc.groupname, gc.attribute, gc.Value, gc.op FROM radgroupcheck gc WHERE gc.groupname = '27' and gc.idrevenda =
This looks up groupname = '27', so I guess that's what you actually got in your radusergroup table. Bjørn
oh, sorry! my mystake! the correct groupname is 27... I've used "office" just for example...
*radusergroup* idrevenda: 1 username: "john" groupname: "27" priority: 1
just replace groupname "office" by "27". thanx! On Sat, Aug 7, 2010 at 6:51 AM, Bjørn Mork <bjorn@mork.no> wrote:
"Erick de A. Fabbio" <erickfabbio@gmail.com> writes:
*radusergroup* idrevenda: 1 username: "john" groupname: "office" priority: 1
Nope, I don't think so.
[sql] expand: SELECT ug.groupname FROM radusergroup ug WHERE ug.username = '%{SQL-User-Name}' and ug.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY ug.priority -> SELECT ug.groupname FROM radusergroup ug WHERE ug.username = 'john' and ug.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '192.168.0.10' LIMIT 0,1) ORDER BY ug.priority [sql] expand: SELECT gc.id, gc.groupname, gc.attribute, gc.Value, gc.op FROM radgroupcheck gc WHERE gc.groupname = '%{Sql-Group}' and gc.idrevenda = (SELECT distinct n.idrevenda FROM nas n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) ORDER BY gc.id -> SELECT gc.id, gc.groupname, gc.attribute, gc.Value, gc.op FROM radgroupcheck gc WHERE gc.groupname = '27' and gc.idrevenda =
This looks up groupname = '27', so I guess that's what you actually got in your radusergroup table.
Bjørn
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Erick de A. Fabbio MSN/GTalk: erickfabbio@gmail.com Skype: erickfabbio Cel: 19 - 9105 5028
participants (5)
-
Arran Cudbard-Bell -
Bjørn Mork -
Erick de A. Fabbio -
Marinko Tarlac -
Siryx XL