sqlcounter monthly counter impementation problem
satish patel
linuxtrap at yahoo.co.in
Mon May 7 08:10:21 CEST 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 at yahoo.co.in
Mobile : - +91-9818875535
Cory Robson <cory at 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
---------------------------------
Here’s a new way to find what you're looking for - Yahoo! Answers
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070507/ec0f191a/attachment.html>
More information about the Freeradius-Users
mailing list