January 2009 - Freeradius-Users - lists.freeradius.org

Optimum MYSQL settings
by Dave 13 Jan '09

13 Jan '09

Hi all. I promise this is my last question before I flip the switch on this new installation. I was using 1.1.7 and I had a number of problems with sqlippool handing out duplicate IPs, stop records not getting recorded, etc. Only under many requests (20-30) at one time. It was ok with small load. I have 2.1.3 ready to go but am not sure Im going to suffer from the same problems. Anyone have any suggestions on how to set up mysql to optimize its performance for sqlippool? It seemed that if two requests come in right at the right time, it would hand out the same IP to 2 different users before there was a chance it was recorded. Its a dual core 3.0ghz machine that also has mysql running for mail server authentication for about 220 users. This is my current config. # /etc/mysql/my.cnf: The global mysql configuration file. # $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $ # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /var/run/mysqld/mysqld.sock [mysql] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqladmin] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlcheck] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqldump] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlimport] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [mysqlshow] character-sets-dir=/usr/share/mysql/charsets default-character-set=utf8 [myisamchk] character-sets-dir=/usr/share/mysql/charsets [myisampack] character-sets-dir=/usr/share/mysql/charsets # use [safe_mysqld] with mysql-3 [mysqld_safe] err-log = /var/log/mysql/mysql.err # add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations [mysqld] max_connections = 500 character-set-server = utf8 default-character-set = utf8 user = mysql port = 3306 socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid log-error = /var/log/mysql/mysqld.err basedir = /usr datadir = /var/lib/mysql skip-locking key_buffer = 64M max_allowed_packet = 8M table_cache = 64 sort_buffer_size = 2M net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M language = /usr/share/mysql/english # old_passwords = 1 # security: # using "localhost" in connects uses sockets by default # skip-networking #bind-address = 127.0.0.1 log-bin server-id = 1 # point the following paths to different dedicated disks tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # you need the debug USE flag enabled to use the following directives, # if needed, uncomment them, start the server and issue # #tail -f /tmp/mysqld.sql /tmp/mysqld.trace # this will show you *exactly* what's happening in your server ;) #log = /tmp/mysqld.sql #gdb #debug = d:t:i:o,/tmp/mysqld.trace #one-thread # uncomment the following directives if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 10000 # the following is the InnoDB configuration # if you wish to disable innodb instead # uncomment just the next line #skip-innodb # # the rest of the innodb config follows: # don't eat too much memory, we're trying to be safe on 64Mb boxes # you might want to bump this up a bit on boxes with more RAM innodb_buffer_pool_size = 16M # this is the default, increase it if you have lots of tables innodb_additional_mem_pool_size = 2M # # i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-( # and upstream wants things to be under /var/lib/mysql/, so that's the route # we have to take for the moment #innodb_data_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ #innodb_log_group_home_dir = /var/lib/mysql/ # you may wish to change this size to be more suitable for your system # the max is there to avoid run-away growth on your machine innodb_data_file_path = ibdata1:10M:autoextend:max:128M # we keep this at around 25% of of innodb_buffer_pool_size # sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size) innodb_log_file_size = 5M # this is the default, increase it if you have very large transactions going on innodb_log_buffer_size = 8M # this is the default and won't hurt you # you shouldn't need to tweak it set-variable = innodb_log_files_in_group=2 # see the innodb config docs, the other options are not always safe innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] # uncomment the next directive if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Thanks for any suggestions!

2 1

eap/tls freeradius openssl
by Brian Ertel 13 Jan '09

13 Jan '09

Ok, I am ready to get flamed. I reinstalled the newest ver. of Freeradius and did not change anything. It started up in debug mode. I am trying to put together a system that will do eap/tls. Wireless client -> WAP -> Radius... I also just installed the newest version of openssl. Freeradius starts up, but I get the: Ignoring EAP-Type/tls because we do not have OpenSSL support. Ignoring EAP-Type/ttls because we do not have OpenSSL support. Ignoring EAP-Type/peap because we do not have OpenSSL support. output. I read on another thread about freeradius not being able to find the proper Openssl libs. I do not understand the process of making FR aware of OpenSSL and getting FR to "not Ingore" EAP-Type/tls... Thanks, Brian

5 31

Is possible create coding?
by Bil Dert 13 Jan '09

13 Jan '09

Hello list! I want to know if is possible create or modified coding in freeradius, for example, make a filter say if the user try connect with some program installed in your laptop dont let connect to net, or if user dont have the last xp pack installed dont let connect? thanks!!! _________________________________________________________________ Descubre todas las formas en que puedes estar en contacto con amigos y familiares. http://www.microsoft.com/latam/windows/windowslive/

3 2

Compile Error
by Dave 13 Jan '09

13 Jan '09

I cant win today. I cant compile the newest FR. GCC 3.4.6 glibc 2.6.1 gmake[6]: Leaving directory `/root/freeradius-server-2.1.3/src/modules/rlm_counter' Making all in rlm_dbm... gmake[6]: Entering directory `/root/freeradius-server-2.1.3/src/modules/rlm_dbm' /root/freeradius-server-2.1.3/libtool --mode=link gcc \ -o rlm_dbm_parser rlm_dbm_parser.lo /root/freeradius-server-2.1.3/src/lib/libfreeradius-radius.la -lndbm -lnsl -lresolv gcc -o .libs/rlm_dbm_parser .libs/rlm_dbm_parser.o /root/freeradius-server-2.1.3/src/lib/.libs/libfreeradius-radius.so -lndbm -lnsl -lresolv /root/freeradius-server-2.1.3/src/lib/.libs/libfreeradius-radius.so: undefined reference to `___tls_get_addr' collect2: ld returned 1 exit status gmake[6]: *** [rlm_dbm_parser] Error 1 gmake[6]: Leaving directory `/root/freeradius-server-2.1.3/src/modules/rlm_dbm' gmake[5]: *** [common] Error 2 gmake[5]: Leaving directory `/root/freeradius-server-2.1.3/src/modules' gmake[4]: *** [all] Error 2 gmake[4]: Leaving directory `/root/freeradius-server-2.1.3/src/modules' gmake[3]: *** [common] Error 2 gmake[3]: Leaving directory `/root/freeradius-server-2.1.3/src' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/root/freeradius-server-2.1.3/src' gmake[1]: *** [common] Error 2 gmake[1]: Leaving directory `/root/freeradius-server-2.1.3' make: *** [all] Error 2

1 2

MYSQL check_error: 1064 received
by Dave 13 Jan '09

13 Jan '09

I get this error on some of my queries but not from all users in the database, just some? I cant find much useful google information on it Here is a query that fails with that error: rad_recv: Access-Request packet from host 127.0.0.1 port 42830, id=69, length=57 User-Name = "hheeg" User-Password = "hheeg" NAS-IP-Address = 127.0.0.1 NAS-Port = 1348 +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop [suffix] No '@' in User-Name = "hheeg", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop [eap] No EAP-Message, not doing EAP ++[eap] returns noop ++[unix] returns notfound ++[files] returns noop [sql] expand: %{User-Name} -> hheeg [sql] sql_set_user escaped user --> 'hheeg' rlm_sql (sql): Reserving sql socket id: 4 [sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'hheeg' ORDER BY id [sql] User found in radcheck table [sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'hheeg' ORDER BY id [sql] expand: SELECT groupname FROM usergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority -> SELECT groupname FROM usergroup WHERE username = 'hheeg' ORDER BY priority [sql] 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 = 'wisp-dynamic-pool2' ORDER BY id [sql] User found in group wisp-dynamic-pool2 [sql] expand: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id -> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'wisp-dynamic-pool2' ORDER BY id rlm_sql (sql): Released sql socket id: 4 ++[sql] returns ok ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated Found Auth-Type = PAP +- entering group PAP {...} [pap] login attempt with password "hheeg" [pap] Using clear text password "hheeg" [pap] User authenticated successfully ++[pap] returns ok +- entering group post-auth {...} rlm_sql (sql): Reserving sql socket id: 3 [sqlippool] expand: %{User-Name} -> hheeg [sqlippool] sql_set_user escaped user --> 'hheeg' [sqlippool] expand: START TRANSACTION -> START TRANSACTION [sqlippool] expand: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}' -> UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '127.0.0.1' rlm_sql_mysql: MYSQL check_error: 1064 received sqlippool_command: database query error in: 'UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time IS NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '127.0.0.1'' [sqlippool] expand: SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < NOW() ORDER BY (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE -> SELECT framedipaddress FROM radippool WHERE pool_name = 'IP-Pool' AND expiry_time < NOW() ORDER BY (username <> 'hheeg'), (callingstationid <> ''), expiry_time LIMIT 1 FOR UPDATE [sqlippool] expand: UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = NOW() + INTERVAL 60 SECOND WHERE framedipaddress = '75.119.231.190' -> UPDATE radippool SET nasipaddress = '127.0.0.1', pool_key = '1348', callingstationid = '', username = 'hheeg', expiry_time = NOW() + INTERVAL 60 SECOND WHERE framedipaddress = '75.119.231.190' [sqlippool] Allocated IP 75.119.231.190 [bee7774b] [sqlippool] expand: COMMIT -> COMMIT rlm_sql (sql): Released sql socket id: 3 [sqlippool] expand: Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name}) -> Allocated IP: 75.119.231.190 from IP-Pool (did cli port 1348 user hheeg) Allocated IP: 75.119.231.190 from IP-Pool (did cli port 1348 user hheeg) ++[sqlippool] returns ok ++[exec] returns noop Sending Access-Accept of id 69 to 127.0.0.1 port 42830 Framed-IP-Address = 75.119.231.190 Finished request 7. Going to the next request Waking up in 2.2 seconds. Cleaning up request 6 ID 241 with timestamp +493 Waking up in 2.7 seconds. Cleaning up request 7 ID 69 with timestamp +496 Ready to process requests.

2 1

Just need a little help w/sqlippool
by Dave 13 Jan '09

13 Jan '09

Ive been fighting with upgrading my working 1.7 freeradius to 2.1.3 . Ive been using the sqlippool module all this time, and while I think I'm close I just cant figure this out. The database is the original database I used with 1.7 but the configs are all new from the ground up for 2.1.3. I think maybe it changed in the way it works. With 1.7 I was inserting a username and password into radcheck table; I was inserting the username into usergroup table with the groupname value of "wisp-dynamic-pool2" among one other group for my NAS; my radgroupcheck table: +----+--------------------+-----------+----+---------+ | id | GroupName | Attribute | op | Value | +----+--------------------+-----------+----+---------+ | 2 | wisp-dynamic-pool2 | Pool-Name | := | IP-Pool | +----+--------------------+-----------+----+---------+ part of my radippool table; +-----+-----------+-----------------+--------------+-----------------+------------------+---------------------+----------+----------+ | id | pool_name | framedipaddress | nasipaddress | calledstationid | callingstationid | expiry_time | username | pool_key | +-----+-----------+-----------------+--------------+-----------------+------------------+---------------------+----------+----------+ | 304 | IP-Pool | 75.119.xxx.xxx | | | | 0000-00-00 00:00:00 | | | | 305 | IP-Pool | 75.119.xxx.xxx | | | | 0000-00-00 00:00:00 | | | Part of my radcheck table; | 217 | preichenbach | Cleartext-Password | := | preichenbach | | 218 | dfast | Cleartext-Password | := | dfast | | 219 | jhoffman | Cleartext-Password | := | jhoffman | my radgroupreply table; +----+-----------+---------------------+----+-------------------------------------+------+ | id | GroupName | Attribute | op | Value | prio | +----+-----------+---------------------+----+-------------------------------------+------+ | 1 | wisp-1500 | Mikrotik-Rate-Limit | = | 384k/1500k 600k/4000k 128k/128k 8/8 | 0 | | 2 | wisp-1500 | Port-Limit | = | 1 | 0 | | 3 | wisp-2500 | Mikrotik-Rate-Limit | = | 384k/2500k 600k/4000k 128k/128k 8/8 | 0 | | 4 | wisp-2500 | Port-Limit | = | 1 | 0 | | 9 | wisp-256 | Port-Limit | = | 1 | 0 | | 8 | wisp-256 | Mikrotik-Rate-Limit | = | 64k/256k | 0 | +----+-----------+---------------------+----+-------------------------------------+------+ My usergroup table with a user for example: +----------+--------------------+----------+ | UserName | GroupName | priority | +----------+--------------------+----------+ | kcase | wisp-1500 | 0 | | kcase | wisp-dynamic-pool2 | 0 | +----------+--------------------+----------+ The above worked with 1.7 BUT this isnt working for 2.1.3; Do I have to insert into the radcheck table the username as well as the pool name into radcheck? +-----+----------+--------------------+----+---------+ | id | UserName | Attribute | op | Value | +-----+----------+--------------------+----+---------+ | 78 | kcase | Cleartext-Password | := | kcase | | 230 | kcase | Pool-Name | := | IP-Pool | +-----+----------+--------------------+----+---------+ The above seems to work, if I don't put the pool-name in radcheck; and I leave the user into the usergroup table, and put the user into group wisp-dynamic-pool2 where radgroupcheck has wisp-dynamic-pool2 setting Pool-Name := IP-Pool. I get no pool name defined error. The rest works as expected though using the usergroup table as it did before, (put my user into wisp-1500 group for example and radtest returns the correct attributes) Any suggestions would be great.!

2 2

Centralized authentication
by scouf scouf 13 Jan '09

13 Jan '09

Hello everybody, I would like to know if anybody had worked on centralizing authentication on FreeRadius and OpenLDAP for the following technologies: - Nokia IPSO - 3com switches - Cisco Pix I'm looking for any guides, howtos... Thanks

4 5

Some SQL radgroupcheck/reply troubles.
by Anton Borisov 13 Jan '09

13 Jan '09

Good day! I try to understand some of SQL selects in oracle.conf in Freeradius. I think, I have found incorrect sql selects in radgroupcheck/radgrpoupreply. Let me show it: First of all - typical install freeradius2.1.1 with oracle. I can see this selects in sql.conf for dialup.oracle.conf ########################### authorize_group_check_query = " \ SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op \ FROM ${groupcheck_table},${usergroup_table} \ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \ ORDER BY ${groupcheck_table}.id" authorize_group_reply_query = " \ SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op \ FROM ${groupreply_table},${usergroup_table} \ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \ ORDER BY ${groupreply_table}.id" ####################### user in sql: in radcheck USERNAME ATTRIBUTE OP VALUE c-user User-Password = c-password in radreply c-user Reply-Message += c-reply in usergroup USERNAME GROUPNAME PRIORITY c-user a-group 10 c-user b-group 5 in radgroupreply GROUPNAME ATTRIBUTE OP VALUE a-group Reply-Message += a-group b-group Reply-Message += b-group Ok, let try to make radclient requests for my c-user user: Here is debug about sql select in freeradius2.1.1 Tue Jan 13 13:03:07 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 1 Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:03:07 2009 : Info: Invalid operator for item User-Password: reverting to '==' Tue Jan 13 13:03:07 2009 : Info: [sqlauth] User found in radcheck table Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id Tue Jan 13 13:03:07 2009 : Info: [sqlauth] User found in group a-group ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Only one group here!!!!!!!!!!!!!! Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id Tue Jan 13 13:03:07 2009 : Debug: rlm_sql (sqlauth): Released sql socket id: 1 Tue Jan 13 13:03:07 2009 : Info: ++[sqlauth] returns ok And here is answer (radclient) rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=87, length=47 Reply-Message = "c-reply" Reply-Message = "b-group" Reply-Message = "a-group" Why? If we try to use SELECT from debug we can see that select pull user from a-group and b-group in ONLY the one request: Try to make sql select: >>>SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id; And we see: GROUPNAME ATTRIBUTE VALUE OP b-group Reply-Message b-group += a-group Reply-Message a-group += Next, if we add into a-group: Fall-Through = Yes we can see the same select several times: Tue Jan 13 14:29:11 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 1 Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 14:29:11 2009 : Info: Invalid operator for item User-Password: reverting to '==' Tue Jan 13 14:29:11 2009 : Info: [sqlauth] User found in radcheck table Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id Tue Jan 13 14:29:11 2009 : Info: [sqlauth] User found in group a-group Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id Tue Jan 13 14:29:11 2009 : Info: [sqlauth] User found in group b-group ^^^^^^^^^^^^^^^^^ Another select the SAME!! ^^^^^^^^^^^^^^^^^^^^^^^^^^ Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id Tue Jan 13 14:29:11 2009 : Debug: rlm_sql (sqlauth): Released sql socket id: 1 Tue Jan 13 14:29:11 2009 : Info: ++[sqlauth] returns ok Here this select in sql: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id GROUPNAME ATTRIBUTE VALUE OP a-group Fall-Through Yes = b-group Reply-Message b-group += a-group Reply-Message a-group += With or without Fall-Through we have the same result. I think this is wrond because: http://wiki.freeradius.org/index.php?title=Rlm_sql 1. Search the radcheck table for any check attributes specific to the user YES 2. If check attributes are found, and there's a match, pull the reply items from the radreply table for this user and add them to the reply YES 3. Group processing then begins if any of the following conditions are met: * The user IS NOT found in radcheck * The user IS found in radcheck, but the check items don't match * The user IS found in radcheck, the check items DO match AND Fall-Through is set in the radreply table * The user IS found in radcheck, the check items DO match AND the read_groups directive is set to 'yes' YES 4. If groups are to be processed for this user, the first thing that is done is the list of groups this user is a member of is pulled from the usergroup table ordered by the priority field. The priority field of the usergroup table allows us to control the order in which groups are processed, so that we can emulate the ordering in the users file. This can be important in many cases. YES 5. For each group this user is a member of, the corresponding check items are pulled from radgroupcheck table and compared with the request. If there is a match, the reply items for this group are pulled from the radgroupreply table and applied. NO 6. Processing continues to the next group IF: * There was not a match for the last group's check items OR * Fall-Through was set in the last group's reply items (The above is exactly the same as in the users file) NO 7. Finally, if the user has a User-Profile attribute set or the Default Profile option is set in the sql.conf, then steps 4-6 are repeated for the groups that the profile is a member of. NO Well, in debug we can see only one group-request with ALL groups parameters where user belong to and only ONE group-reply with ALL groups parameters where user belong to.. I.e one groupcheck/groupreply with all groups parameters... Now, let me try to correct SELECT in dialup.oracle.conf. I add only one: AND ${usergroup_table}.GroupName = '%{SQL-Group}', so, all group-select something like this: ########################### authorize_group_check_query = " \ SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op \ FROM ${groupcheck_table},${usergroup_table} \ WHERE (${usergroup_table}.Username = '%{SQL-User-Name}' \ AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \ AND ${usergroup_table}.GroupName = '%{SQL-Group}' \ ORDER BY ${usergroup_table}.PRIORITY" ########################### SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op \ FROM ${groupreply_table},${usergroup_table} \ WHERE (${usergroup_table}.Username = '%{SQL-User-Name}'\ AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \ AND ${usergroup_table}.GroupName = '%{SQL-Group}' \ ORDER BY ${usergroup_table}.PRIORITY" Here is debug with result in the same radius with the same request (without Fall-Through): Tue Jan 13 13:21:33 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 1 Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:21:33 2009 : Info: Invalid operator for item User-Password: reverting to '==' Tue Jan 13 13:21:33 2009 : Info: [sqlauth] User found in radcheck table Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' or usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = 'c-user' or usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:21:34 2009 : Info: [sqlauth] User found in group a-group ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Again Only one group here!!!!!!!!!!!!!! Tue Jan 13 13:21:34 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' OR usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:21:34 2009 : Debug: rlm_sql (sqlauth): Released sql socket id: 1 Tue Jan 13 13:21:34 2009 : Info: ++[sqlauth] returns ok Answer: rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=116, length=38 Reply-Message = "c-reply" Reply-Message = "a-group" We can see user found in only one group (And only one select). But - we did not use Fall-Through','=','Yes. (See 6. Processing continues to the next group IF: in wiki Freeradius sql). And we can see only ONE group in select from radgroupreply: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY; GROUPNAME ATTRIBUTE VALUE OP a-group Reply-Message a-group += Only one group. Well, just add Fall-Through: radgroupreply GROUPNAME ATTRIBUTE OP VALUE a-group Reply-Message += a-group b-group Reply-Message += b-group a-group Fall-Through = Yes Here is result: ue Jan 13 13:28:04 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 0 Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:28:04 2009 : Info: Invalid operator for item User-Password: reverting to '==' Tue Jan 13 13:28:04 2009 : Info: [sqlauth] User found in radcheck table Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' or usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = 'c-user' or usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:28:04 2009 : Info: [sqlauth] User found in group a-group ^^^^^^^^^^^^^^^^ We can see a-group check ^^^^^^^^^^^^^^^^^^^^^^^^^ Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' OR usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' or usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = 'c-user' or usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = 'b-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:28:04 2009 : Info: [sqlauth] User found in group b-group ^^^^^^^^^^^^^^^^^^^^^^ And we can see b-group check^^^^^^^^^^^^^^^^^^^^ Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' OR usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'b-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:28:04 2009 : Debug: rlm_sql (sqlauth): Released sql socket id: 0 Tue Jan 13 13:28:04 2009 : Info: ++[sqlauth] returns ok And correct result: rad_recv: Access-Accept packet from host 127.0.0.01 port 1812, id=133, length=47 Reply-Message = "c-reply" Reply-Message = "a-group" Reply-Message = "b-group" All in all: We have TWO selects about TWO groups and we use Fall-Through for check all groups. All are working! First select in debug: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY GROUPNAME ATTRIBUTE VALUE OP a-group Fall-Through Yes = a-group Reply-Message a-group += Second select: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'b-group' ORDER BY usergroup.PRIORITY GROUPNAME ATTRIBUTE VALUE OP b-group Reply-Message b-group += What do you think? -- Yours faithfully, Anton Borisov.

1 0

unsubscribe
by freeradius-users 13 Jan '09

13 Jan '09

unsubscribe CompuLab - Consult Robert Schuster Am Karmelkloster 16 53229 Bonn mailto: robert.schuster(a)compulab-consult.de Tel. +49 228 97604-0 Fax. +49 228 97604-25 mobil +49 175 1606254

1 0

Multiple output pairs in rlm_exec - How to format the response pai string?
by Hanno Schupp 12 Jan '09

12 Jan '09

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); ?>

1 0