Hello List, I have a question regarding the ability of rlm_sql setting of the Autz-Type attribute. I am attempting to assign/add to the Autz-Type attribute for processing of sqlcounter instances based on the groups the user belongs to. User bob@testing belongs to a group DSL-LOCAL for local only DSL service. In the authorize section after the sql statement I have a sqlcounter called "MonthlyOctetsLocal" that need to be executed if the user belongs to the DSL-LOCAL group. # # Look in an SQL database. The schema of the database # is meant to mirror the "users" file. # # See "Authorization Queries" in sql.conf sql Autz-Type DSL-LOCAL { MonthlyOctetsLocal } debian:/etc/freeradius# freeradius -X Starting - reading configuration files ... reread_config: reading radiusd.conf <cut> Module: Instantiated sql (sql) Module: Loaded SQL Counter sqlcounter: counter-name = "Monthly-Session-Octets-Local" sqlcounter: check-name = "Max-Monthly-Octets-Local" sqlcounter: reply-name = "(null)" sqlcounter: key = "User-Name" sqlcounter: sqlmod-inst = "sql" sqlcounter: query = "SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='%{%k}' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" sqlcounter: reset = "monthly" sqlcounter: safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" <cut> Ready to process requests. rad_recv: Access-Request packet from host 127.0.0.1:32768, id=178, length=62 User-Name = "bob@testing" User-Password = "hello" Access-Type = "DSL" Processing the authorize section of radiusd.conf modcall: entering group authorize for request 0 modcall[authorize]: module "preprocess" returns ok for request 0 modcall[authorize]: module "chap" returns noop for request 0 modcall[authorize]: module "mschap" returns noop for request 0 rlm_realm: Looking up realm "testing" for User-Name = "bob@testing" rlm_realm: No such realm "testing" modcall[authorize]: module "suffix" returns noop for request 0 rlm_eap: No EAP-Message, not doing EAP modcall[authorize]: module "eap" returns noop for request 0 modcall[authorize]: module "files" returns notfound for request 0 radius_xlat: 'bob@testing' rlm_sql (sql): sql_set_user escaped user --> 'bob@testing' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'bob@testing' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 4 radius_xlat: 'SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'bob@testing' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'bob@testing' ORDER BY id' radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'bob@testing' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id' rlm_sql (sql): Released sql socket id: 4 modcall[authorize]: module "sql" returns ok for request 0 modcall[authorize]: module "pap" returns updated for request 0 modcall: leaving group authorize (returns updated) for request 0 rad_check_password: Found Auth-Type pap auth: type "PAP" Processing the authenticate section of radiusd.conf modcall: entering group PAP for request 0 rlm_pap: login attempt with password hello rlm_pap: Using clear text password "hello". rlm_pap: User authenticated successfully modcall[authenticate]: module "pap" returns ok for request 0 modcall: leaving group PAP (returns ok) for request 0 Sending Access-Accept of id 178 to 127.0.0.1 port 32768 Finished request 0 Going to the next request --- Walking the entire request list --- Waking up in 6 seconds... --- Walking the entire request list --- Umm, nothing was executed within the Autz-Type section at all. So to test if the rlm_sql module is actually attempting to set the Autz-Type attribute I did this in the authorize section. # Look in an SQL database. The schema of the database # is meant to mirror the "users" file. # # See "Authorization Queries" in sql.conf sql # Autz-Type DSL-LOCAL { # MonthlyOctetsLocal # } MonthlyOctetsLocal Started the server again in debug mode: <cut> Module: Instantiated sql (sql) Module: Loaded SQL Counter sqlcounter: counter-name = "Monthly-Session-Octets-Local" sqlcounter: check-name = "Max-Monthly-Octets-Local" sqlcounter: reply-name = "(null)" sqlcounter: key = "User-Name" sqlcounter: sqlmod-inst = "sql" sqlcounter: query = "SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='%{%k}' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" <cut> Ready to process requests. rad_recv: Access-Request packet from host 127.0.0.1:32768, id=183, length=62 User-Name = "bob@testing" User-Password = "hello" Access-Type = "DSL" Processing the authorize section of radiusd.conf modcall: entering group authorize for request 0 modcall[authorize]: module "preprocess" returns ok for request 0 modcall[authorize]: module "chap" returns noop for request 0 modcall[authorize]: module "mschap" returns noop for request 0 rlm_realm: Looking up realm "testing" for User-Name = "bob@testing" rlm_realm: No such realm "testing" modcall[authorize]: module "suffix" returns noop for request 0 rlm_eap: No EAP-Message, not doing EAP modcall[authorize]: module "eap" returns noop for request 0 modcall[authorize]: module "files" returns notfound for request 0 radius_xlat: 'bob@testing' rlm_sql (sql): sql_set_user escaped user --> 'bob@testing' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'bob@testing' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 4 radius_xlat: 'SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'bob@testing' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radreply WHERE Username = 'bob@testing' ORDER BY id' radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'bob@testing' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id' *rlm_sql: Failed to create the pair: Unknown value DSL-LOCAL for attribute Autz-Type* rlm_sql (sql): Error getting data from database rlm_sql (sql): Released sql socket id: 4 modcall[authorize]: module "sql" returns ok for request 0 rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='%{User-Name}' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1199138400'' radius_xlat: 'SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='bob@testing' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1199138400'' sqlcounter_expand: '%{sql:SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='bob@testing' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1199138400'}' radius_xlat: Running registered xlat function of module sql for string 'SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='bob@testing' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1199138400'' rlm_sql (sql): - sql_xlat radius_xlat: 'bob@testing' rlm_sql (sql): sql_set_user escaped user --> 'bob@testing' radius_xlat: 'SELECT SUM(AcctInputOctets) + SUM(AcctOutputOctets) ???FROM radacct WHERE UserName='bob@testing' ???AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1199138400'' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): - sql_xlat finished rlm_sql (sql): Released sql socket id: 3 radius_xlat: '1073741824' rlm_sqlcounter: (Check item - counter) is less than zero rlm_sqlcounter: Rejected user bob@testing, check_item=-1, counter=1073741824 modcall[authorize]: module "MonthlyOctetsLocal" returns reject for request 0 modcall: leaving group authorize (returns reject) for request 0 Delaying request 0 for 1 seconds Finished request 0 Going to the next request --- Walking the entire request list --- Waking up in 1 seconds... --- Walking the entire request list --- Waking up in 1 seconds... --- Walking the entire request list --- Sending Access-Reject of id 183 to 127.0.0.1 port 32768 Reply-Message = "Your maximum monthly usage time has been reached" Waking up in 4 seconds... "rlm_sql: Failed to create the pair: Unknown value DSL-LOCAL for attribute Autz-Type" line in the above output indicates that the rlm_sql module is setting the attribute - or here attempting to do so. Anyway, why did the previous attempt not try and run the sqlcounter module instance? Any advise/help on this subject will be much appreciated. -- Kind Regards Etienne Pretorius