Re: sqlcounter monthly counter impementation problem

7 May 2007

      Dear all

                  Here I am shareing my Knowledge. for freeradius users. i have done freeradius-1.1.4 with mysql with cisco VPDN configuration as well as i have configuraed per user base bandwidth configuration and simultanious user login configuration i have sharing my configuration for my freeradius users

I have cisco router with this configuration

aaa new-model
!
!
aaa group server radius testing123
 server-private  71.5.250.243 auth-port 1812 acct-port 1813 key tulipconnect
 ip radius source-interface FastEthernet0/1
 deadtime 0
!
aaa authentication login default local group radius group testing123
aaa authentication ppp default group testing123 local 
aaa authorization exec default local group radius group testing123
aaa authorization network default group testing123 local
aaa accounting update periodic  1
aaa accounting exec default start-stop group testing123
aaa accounting network default start-stop group testing123
aaa accounting connection default start-stop group testing123
!

_________________________________________________________ 

My all user databases in mysql and simultanius login also in mysql 

mysql tables :-

mysql> select * from radcheck;
+----+----------+---------------+----+-------+
| id | UserName | Attribute     | op | Value | 
+----+----------+---------------+----+-------+
|  1 | satish   | User-Password | := | tulip |
|  2 | priya    | User-Password | := | tulip |
+----+----------+---------------+----+-------+
2 rows in set (0.00  sec)

mysql> select * from radgroupcheck;;
+----+-----------+------------------+----+-------+
| id | GroupName | Attribute        | op | Value  |
+----+-----------+------------------+----+-------+
|  1 | 64KB      | Simultaneous-Use | := | 1     |
|  4 | 128KB     | Simultaneous-Use | := | 1     |
+----+-----------+------------------+----+-------+
 2 rows in set (0.00 sec)

mysql> select * from radgroupreply;;
+----+-----------+-----------------+----+--------------------------------------------------------------------------------------------------------+------+ 
| id | GroupName | Attribute       | op | Value                                                                                                  | prio |
+----+-----------+-----------------+----+--------------------------------------------------------------------------------------------------------+------+ 
|  1 | 64KB      | Framed-Protocol | =  | PPP                                                                                                    |    0 |
|  2 | 64KB      | Framed-MTU      | =  | 1400                                                                                                   |    0 |
|  3 | 64KB      | Service-Type    | =  |  Framed-User                                                                                            |    0 |
|  4 | 128KB     | Framed-Protocol | =  | PPP                                                                                                    |    0 |
|  5 | 128KB     | Framed-MTU      | =  | 1450                                                                                                   |    0 |
|  6 | 128KB     | Service-Type    | =  |  Framed-User                                                                                            |    0 |
|  7 | 128KB     | Cisco-Avpair    | =  | lcp:interface-config#1=rate-limit output 128000 10000 10000 conform-action continue exceed-action drop |    0 | 
+----+-----------+-----------------+----+--------------------------------------------------------------------------------------------------------+------+
7 rows in set (0.00  sec)

mysql> select * from usergroup;
+----+----------+-----------+
| id | UserName | GroupName |
+----+----------+-----------+
|  1 | satish   | 64KB      |
|  3 | priya    | 128KB     |
 +----+----------+-----------+
2 rows in set (0.00 sec)

________________________________________________________

Simultanious Login configuration ( edit this file /etc/raddb/sql.conf )

 ####################################################################### 
        # Simultaneous Use Checking Queries
        #######################################################################
        # simul_count_query     - query for the number of current connections
        #                       - If this is not defined, no simultaneouls use checking
        #                       - will be performed by this module instance
        # simul_verify_query    - query to return details of current connections for verification
        #                       - Leave blank or commented out to disable verification step
        #                       - Note that the returned field order should not be changed.
        #######################################################################

        # Uncomment simul_count_query to enable simultaneous use checking 
         simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
        simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0"

____________________________________________________________

My Sqlcounter.conf file for time limit for user and u cat read more about in freeradius tarball doc directory there is some more help regarding sqlcounter.conf

edit  file   /etc/raddb/sqlcounter.conf

suse:/etc/raddb # cat 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 {
            driver = "rlm_sqlcounter"
            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/raddbd/client.conf

My client.conf  u have to change NAS type when u use Simultanious use with Mysql databases so take care of this configuration 

In my care i have useing other caz my cisco not support it so if u would use NAS type other it will work fine ....enjoy

client  127.0.0.1 {
        secret          =  testing123
        shortname       = localhost
}
client 71.5.250.199 {
        secret          = tulipconnect 
        shortname       = test
        nastype         = other  <----------  ( care full about it if u want to simultanous user tih mysql ) 
}

_________________________________________________________

/etc/raddb/radius.conf

My main radius.conf file 

prefix = /usr
exec_prefix = ${prefix}
sysconfdir = /etc
localstatedir = /var 
sbindir = ${exec_prefix}/sbin
logdir = ${localstatedir}/log/radius
raddbdir = ${sysconfdir}/raddb
radacctdir = ${logdir}/radacct
confdir = ${raddbdir}
run_dir =  ${localstatedir}/run/radiusd
log_file = ${logdir}/radius.log
libdir = /usr/lib/freeradius
pidfile = ${run_dir}/radiusd.pid
user = radiusd
group = radiusd
max_request_time = 30
delete_blocked_requests = no 
cleanup_delay = 5
max_requests = 1024
bind_address = *
port = 0
hostname_lookups = no
allow_core_dumps = no
regular_expressions     = yes
extended_expressions    = yes
log_stripped_names = no
 log_auth = yes
log_auth_badpass = yes
log_auth_goodpass = no
usercollide = no
lower_user = no
lower_pass = no
nospace_user = no
nospace_pass = no
checkrad = ${sbindir}/checkrad
security {
        max_attributes = 200 
        reject_delay = 1
        status_server = no
}
proxy_requests  = yes
$INCLUDE  ${confdir}/proxy.conf
$INCLUDE   ${confdir}/clients.conf
snmp    = no
$INCLUDE  ${confdir}/snmp.conf
thread pool {
        start_servers = 5
        max_servers = 32
        min_spare_servers = 3
        max_spare_servers = 10
        max_requests_per_server = 0 
}
modules {
        $INCLUDE ${confdir}/sqlcounter.conf

        pap {
                encryption_scheme = crypt
        }
        chap {
                authtype = CHAP
         }
        pam {
                pam_auth = radiusd
        }
        unix {
                cache = no
                cache_reload = 600
                radwtmp = ${logdir}/radwtmp
        }
 $INCLUDE ${confdir}/eap.conf
        mschap {
                authtype = MS-CHAP

        }
        ldap  {
                server = "ldap.your.domain"
                basedn = "o=My Org,c=UA"
                filter = "(uid=%{Stripped-User-Name:-%{User-Name}})"
                start_tls = no
                access_attr = "dialupAccess"
                dictionary_mapping = ${raddbdir}/ldap.attrmap
                ldap_connections_number = 5
                 edir_account_policy_check=no
                timeout = 4
                timelimit = 3
                net_timeout = 1
        }
        realm IPASS {
                format = prefix
                delimiter = "/" 
                ignore_default = no
                ignore_null = no
         }
        realm suffix {
                format = suffix
                delimiter = "@"
                ignore_default = no
                ignore_null = no
        }
        realm realmpercent { 
                format = suffix
                delimiter = "%"
                ignore_default =  no
                ignore_null = no
        }
        realm ntdomain {
                format = prefix
                delimiter = "\\"
                ignore_default = no
                ignore_null = no 
        }
        checkval {
                item-name =  Calling-Station-Id
                check-name = Calling-Station-Id
                data-type = string
        }

        preprocess {
                huntgroups = ${confdir}/huntgroups
                hints = ${confdir}/hints 
                with_ascend_hack = no
                ascend_channels_per_line = 23
                with_ntdomain_hack =  no
                with_specialix_jetstream_hack = no
                with_cisco_vsa_hack = no
        }
        files {
                usersfile = ${confdir}/users
                acctusersfile = ${confdir}/acct_users 
                preproxy_usersfile = ${confdir}/preproxy_users
                compat = no
        }
        detail  {
                detailfile = ${radacctdir}/%{Client-IP-Address}/detail-%Y%m%d
                detailperm = 0600
        }
        acct_unique {
                key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port"
        }
        $INCLUDE  ${confdir}/sql.conf

        radutmp {
                filename = ${logdir}/radutmp
                username =  %{User-Name}
                case_sensitive = yes
                check_with_nas = yes
                perm = 0600
                callerid = "yes"
        }
        radutmp sradutmp {
                filename = ${logdir}/sradutmp 
                perm = 0644
                callerid = "no"
         }
        attr_filter {
                attrsfile = ${confdir}/attrs
        }
        counter daily {
                filename = ${raddbdir}/db.daily
                key = User-Name
                count-attribute = Acct-Session-Time 
                reset = daily
                counter-name =  Daily-Session-Time
                check-name = Max-Daily-Session
                allowed-servicetype = Framed-User
                cache-size = 5000
        }
        always fail {
                rcode = fail 
        }
        always reject {
                rcode = reject
        }
        always ok  {
                rcode = ok
                simulcount = 0
                mpp = no
        }
        expr {
        }
        digest {
        }
        exec {
                wait = yes
                 input_pairs = request
        }
        exec echo  {
                wait = yes
                program = "/bin/echo %{User-Name}"
                input_pairs = request
                output_pairs = reply
        }
        ippool main_pool { 
                range-start = 192.168.1.1
                range-stop =  192.168.3.254
                netmask =  255.255.255.0
                cache-size = 800
                session-db = ${raddbdir}/db.ippool
                ip-index = ${raddbdir}/db.ipindex 
                override = no
                maximum-timeout = 0
        }
}
instantiate {
        exec
        expr
}
authorize {
        preprocess

        chap
         mschap
        suffix
        sql
        noresetcounter
        dailycounter
        monthlycounter
        daily
}
authenticate {
        Auth-Type PAP {
                pap
        }
         Auth-Type CHAP {
                chap
        }
        Auth-Type MS-CHAP {
                 mschap
        }
}
preacct {
        preprocess
        acct_unique
        suffix
        files
}
accounting {
        detail
        daily
        unix
        sql
        radutmp 
}
session {
        sql
}
post-auth {
}
pre-proxy {
}
post-proxy {
        eap
}

_________________________________________________________

I will charge for this document and help ....................Kidding...........><))));> 

contect me if u get more help  regarding freeradius 

Name :- Satish Patel
Company:- Tulip It Services ( Data Center ) ( Delhi )
Email :-  linuxtrap@yahoo.co.in
Mobile : - +91-9818875535

Cory Robson <cory@cmi.net.au> wrote: 
I have the following configuration in my radius.conf file. The counter does
function as such and if the user has utilized the allotted time it will not
allow them to connect.

However I'm looking to see how to also apply it to the session limit.
(IE adjust the session time. If user has a max session defined as 4 hrs but
only has 2 hrs left of the monthly limit then adjust this to have them
dropped automatically once this has been reached)

sqlcounter monthlycounter {
  counter-name = Monthly-Session-Time
  check-name = Max-Monthly-Session
              sqlmod-inst = sql
  key = User-Name
  reset = monthly

  # This query properly handles calls that span from the
  # previous reset period into the current period but
  # involves more work for the SQL server than those
  # below
  # The same notes above about the differences between mysql
  # versus postgres queries apply here.
  query = "SELECT SUM(AcctSessionTime - \
                 GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
                 FROM radacct WHERE UserName='%{%k}' AND \
                 UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"

How would I implement this to enforce the session time limits?

Cory

- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

$ cat ~/satish/url.txt

System administrator ( Data Center )

please visit this site

http://linux.tulipit.com   

---------------------------------
 Heres a new way to find what you're looking for - Yahoo! Answers

Re: sqlcounter monthly counter impementation problem

satish patel