SQL Groups and Autz-Type

16 Jan 2008

      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