rlm_sqlcounter doesn't increase counter

pamela pomary ppomary at gmail.com
Fri Feb 24 19:53:45 CET 2012


Hello Fellow Freeradius-Users,

I have freeradius-1.1.3 installed on Cent OS and I have Cisco C2960 switch
as my NAS.

I have a task to allocate browsing time 2hrs per day to students in our
computer labs.

Logs from  radius in debug mode, counter doesn't increase it always shows
(Check item - counter) is greater than zero;  check_item=120, counter=0.
Users are not signed out after Max-Daily-Session is reached.

I want to be able to disconnect a user logged in after they reach their
maximum daily session.

 Kindly find below config files which indicate maximum daily session of 120
seconds for testing purposes.

I have searched through Freeradius's mailing-List but have not found
solution yet. I will be grateful for any help.


Thank you.


###############################
config on Cisco switch C2960
#############################
aaa new-model
!
!
aaa group server radius dot1x
!
aaa authentication login default group radius local
aaa authentication dot1x default group radius
aaa authorization exec default group radius if-authenticated
aaa authorization network default group radius
aaa accounting send stop-record authentication failure
aaa accounting suppress null-username
aaa accounting session-duration ntp-adjusted
aaa accounting update newinfo periodic 1
aaa accounting exec default start-stop group radius
aaa accounting network default start-stop group radius
aaa accounting connection default start-stop group radius
aaa accounting resource default start-stop-failure group radius


interface FastEthernet0/9
 switchport access vlan 6
 switchport mode access
 authentication host-mode multi-auth
 authentication port-control auto
 authentication periodic
 authentication timer inactivity 5
 dot1x pae both
 dot1x max-req 3
 spanning-tree portfast

radius-server host 10.1.7.202 auth-port 1812 acct-port 1813 key  "key"



###############################
/etc/raddb/sqlcounter.conf
################################
   sqlcounter noresetcounter {
                counter-name = Max-All-Session-Time
                check-name = Max-All-Session
                sqlmod-inst = sql
                key = User-Name
                reset = never

                query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE
UserName='%{%k}'"

        }


sqlcounter dailycounter {
                 filename = ${raddbdir}/db.daily
                count-attribute = Acct-Session-Time
                driver = "rlm_sqlcounter"
                allowed-servicetype = Framed-User
                counter-name = Daily-Session-Time
                check-name = Max-Daily-Session
                sqlmod-inst = sqlcca3
                key = User-Name
                reset = daily


                query = "SELECT SUM(AcctSessionTime - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"

        }

sqlcounter monthlycounter {
                counter-name = Monthly-Session-Time
                check-name = Max-Monthly-Session
                sqlmod-inst = sqlcca3
                key = User-Name
                reset = monthly                query = "SELECT
SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0))
FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) +
AcctSessionTime > '%b'"

        }


######################################################################
/etc/raddb/radius.conf
######################################################################

modules {
$INCLUDE ${confdir}/sqlcounter.conf


instantiate {

        exec
        expr
        dailycounter
}





authorize {
         preprocess
        mschap
        suffix
        eap
        files
        sql
        dailycounter


}



authenticate {

        Auth-Type PAP {
                pap
        }


        Auth-Type CHAP {
                chap
        }


        Auth-Type MS-CHAP {
                mschap
        }

        unix


        eap
}




#######################################################################
/etc/raddb/users
#######################################################################


clare           Max-Daily-Session := 120, User-Password := "password"
                Auth-Type := Reject, Reply-Message = "Your time limit is
used",
                Simultaneous-Use := 1,




#########################################################################
Dubug output
#########################################################################
Going to the next request
Waking up in 6 seconds...
rad_recv: Access-Request packet from host 10.1.5.4:1645, id=220, length=244
        User-Name = "clare"
        Service-Type = Framed-User
        Framed-MTU = 1500
        Called-Station-Id = "EC-30-91-1D-69-89"
        Calling-Station-Id = "00-1E-33-D5-7A-68"
        EAP-Message =
0x0209002b19001703010020205ecf13b3dc88bd738478cf6a2111e5903a5089b64e282bc15b5c7c6c0e771c
        Message-Authenticator = 0x2cac65588536701091195c7b06db31a1
        Cisco-AVPair = "audit-session-id=0A01050400000672A669A7C1"
        NAS-Port-Type = Ethernet
        NAS-Port = 50009
        NAS-Port-Id = "FastEthernet0/9"
        State = 0xb014050291b7b551b59b2a5f168e4f7d
        NAS-IP-Address = 10.1.5.4
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 26
  modcall[authorize]: module "preprocess" returns ok for request 26
  modcall[authorize]: module "mschap" returns noop for request 26
    rlm_realm: No '@' in User-Name = "clare", looking up realm NULL
    rlm_realm: No such realm "NULL"
  modcall[authorize]: module "suffix" returns noop for request 26
  rlm_eap: EAP packet type response id 9 length 43
  rlm_eap: No EAP Start, assuming it's an on-going EAP conversation
  modcall[authorize]: module "eap" returns updated for request 26
    users: Matched entry clare at line 97
  modcall[authorize]: module "files" returns ok for request 26
radius_xlat:  'clare'
rlm_sql (sql): sql_set_user escaped user --> 'clare'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM
radcheck           WHERE Username = 'clare'           ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql (sql): User clare not found in radcheck
radius_xlat:  'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
FROM radgroupcheck,usergroup WHERE usergroup.Username = 'clare' AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
FROM radgroupreply,usergroup WHERE usergroup.Username = 'clare' AND
usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): User clare not found in radgroupcheck
rlm_sql (sql): Released sql socket id: 4
rlm_sql (sql): User not found
  modcall[authorize]: module "sql" returns notfound for request 26
rlm_sqlcounter: Entering module authorize code
sqlcounter_expand:  'SELECT SUM(AcctSessionTime - GREATEST((1330102800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE
UserName='%{User-Name}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime
> '1330102800''
radius_xlat:  'SELECT SUM(AcctSessionTime - GREATEST((1330102800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330102800''
sqlcounter_expand:  '%{sql:SELECT SUM(AcctSessionTime -
GREATEST((1330102800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct
WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime
> '1330102800'}'
radius_xlat: Running registered xlat function of module sql for string
'SELECT SUM(AcctSessionTime - GREATEST((1330102800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330102800''
rlm_sql (sql): - sql_xlat
radius_xlat:  'clare'
rlm_sql (sql): sql_set_user escaped user --> 'clare'
radius_xlat:  'SELECT SUM(AcctSessionTime - GREATEST((1330102800 -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330102800''
rlm_sql (sql): Reserving sql socket id: 3
rlm_sql (sql): row[0] returned NULL
rlm_sql (sql): Released sql socket id: 3
radius_xlat:  ''
rlm_sqlcounter: (Check item - counter) is greater than zero
rlm_sqlcounter: Authorized user clare, check_item=120, counter=0
rlm_sqlcounter: Sent Reply-Item for user clare, Type=Session-Timeout,
value=120
  modcall[authorize]: module "dailycounter" returns ok for request 26
modcall: leaving group authorize (returns updated) for request 26
  rad_check_password:  Found Auth-Type EAP
auth: type "EAP"
  Processing the authenticate section of radiusd.conf
modcall: entering group authenticate for request 26
  rlm_eap: Request found, released from the list
  rlm_eap: EAP/peap
  rlm_eap: processing type peap
  rlm_eap_peap: Authenticate
  rlm_eap_tls: processing TLS
  eaptls_verify returned 7
  rlm_eap_tls: Done initial handshake
  eaptls_process returned 7
  rlm_eap_peap: EAPTLS_OK
  rlm_eap_peap: Session established.  Decoding tunneled attributes.
  rlm_eap_peap: Received EAP-TLV response.
  rlm_eap_peap: Tunneled data is valid.
  rlm_eap_peap: Success
  rlm_eap: Freeing handler
  modcall[authenticate]: module "eap" returns ok for request 26
modcall: leaving group authenticate (returns ok) for request 26
radius_xlat:  'Your time limit is used'
Login OK: [clare] (from client C2960_NOC_LAN1 port 50009 cli
00-1E-33-D5-7A-68)
Sending Access-Accept of id 220 to 10.1.5.4 port 1645
        Reply-Message = "Your time limit is used"
        Session-Timeout = 120
        MS-MPPE-Recv-Key =
0x33430a677c0ab2f0331e95b8ad8a7c96de6ac7e8065f622a7883bb736882d0e0
        MS-MPPE-Send-Key =
0xed2c5c1c315e2387e636c3796035e004cb93d3d159a17c696a73bcd418ac483b
        EAP-Message = 0x03090004
        Message-Authenticator = 0x00000000000000000000000000000000
        User-Name = "clare"
Finished request 26
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20120224/8fe12e1f/attachment.html>


More information about the Freeradius-Users mailing list