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
