freeradius et rlm_sqlcounter
Cette note s’adresse aux utilisateurs de freeradius utilisant rlm_sqlcounter, et rajoute une précision concernant son utilisation.
Ce module est présent et activé par défaut dans freeradius depuis la version 1.1.0. Il permet de définir une durée maximale de connexion pour un utilisateur donné, en se basant sur les valeurs stockées dans la table radacct (Accounting) pour chaque session.
On trouvera le fichier de configuration du module dans /etc/freeradius/sql/mysql/counter.conf
Il est possible de définir une période de rafraîchissement pour laquelle la durée autorisée est réinitialisée à intervalle régulier. On pourra par exemple accorder x minutes par jour ou par mois en utilisant les scénarios prédéfinis daily et monthly de counter.conf, ou en écrivant son propre sqlcounter.
On trouve aussi dans counter.conf, le sqlcounter noresetcounter, permettant de définir une durée maximale unique pour l’utilisateur qu’il pourra utiliser sur une ou plusieurs sessions:
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}'" }
Le fichier de conf est assez bien commenté et on trouvera une doc complète de mise en place sur http://wiki.freeradius.org/Rlm_sqlcounter
Une fois activée, à chaque Access-Request, freeradius va consulter le module rlm_sqlcounter qui va calculer le temps de connexion passé en additionnant les AcctSessionTime des sessions précédentes, et le comparer avec la valeur de l”attribut Max-All-Session (placé dans radcheck ou radgroupcheck).
Si Max-All-Session > SUM(AcctSessionTime) alors freeradius va renvoyer un Access-Accept avec un attribut Session-Timeout = Max-All-Session – SUM(AcctSessionTime), sinon il envoie un Access-Reject.
Exemple avec un utilisateur min5 (avec un Max-All-Session de 5min, ayant déjà consommé 124s de connexion) envoyant un Access-Request (logs crachés par un freeradius lancé avec l’option -X):
[...] rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}'' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> min5 [noresetcounter] sql_set_user escaped user --> 'min5' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' rlm_sql (sql): Reserving sql socket id: 1 [noresetcounter] sql_xlat finished rlm_sql (sql): Released sql socket id: 1 [noresetcounter] expand: %{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5'} -> 124 rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user min5, check_item=300, counter=124 rlm_sqlcounter: Sent Reply-Item for user min5, Type=Session-Timeout, value=176 ++[noresetcounter] returns ok Sending Access-Accept of id 85 to 78.114.63.174 port 53158 Session-Timeout == 176 [...]
Si Max-All-Session < SUM(AcctSessionTime) alors on aura:
[...] rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}'' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> min5 [noresetcounter] sql_set_user escaped user --> 'min5' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' rlm_sql (sql): Reserving sql socket id: 1 [noresetcounter] sql_xlat finished rlm_sql (sql): Released sql socket id: 1 [noresetcounter] expand: %{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5'} -> 301 rlm_sqlcounter: (Check item - counter) is less than zero rlm_sqlcounter: Rejected user min5, check_item=300, counter=301 ++[noresetcounter] returns reject Sending Access-Reject of id 124 to 78.114.63.174 port 51651 Reply-Message = "Your maximum never usage time has been reached" [...]
Néanmoins, que se passe t’il lors de la première connexion de l’utilisateur, alors qu’il n’y a aucune durée de connexion loggée dans la table radacct ?
[...] rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}'' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{User-Name}' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5'}' [noresetcounter] sql_xlat [noresetcounter] expand: %{User-Name} -> min5 [noresetcounter] sql_set_user escaped user --> 'min5' [noresetcounter] expand: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' -> SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5' rlm_sql (sql): Reserving sql socket id: 1 [noresetcounter] row[0] returned NULL rlm_sql (sql): Released sql socket id: 1 [noresetcounter] expand: %{sql:SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='min5'} -> rlm_sqlcounter: No integer found in string "" ++[noresetcounter] returns noop Sending Access-Accept of id 81 to 78.114.63.174 port 57171 [...]
Rlm_sqlcounter n’est pas capable d’effectuer sa requête puisqu’il n’y a pas de session antérieure concernant le nouvel utilisateur. Il est échoue et freeradius renvoie un Access-Accept, sans Session-Timeout, donc sans aucune restriction concernant la durée de connexion !
Il faut préalablement définir pour l’utilisateur (table radreply) ou un groupe d’utilisateur (table radgroupreply) un Session-Timeout correspondant au Max-All-Session, afin qu’il soit utilisé lors de la première connexion. Pour les futures connexions, le module Rlm_sqlcounter génèrera un Session-Timeout que freeradius utilisera à la place de la valeur configurée par défaut.

Bonjour, j’ai ajouté ce module mais tjrs il n’est pas pris en considération.
Voici mon fichier de config :
prefix = /usr
exec_prefix = ${prefix}
sysconfdir = /etc
localstatedir = /var
sbindir = ${exec_prefix}/sbin
logdir = ${localstatedir}/log/freeradius
raddbdir = ${sysconfdir}/freeradius
radacctdir = ${logdir}/radacct
confdir = ${raddbdir}
run_dir = ${localstatedir}/run/freeradius
log_file = ${logdir}/radius.log
log_destination = files
libdir = ${exec_prefix}/lib/freeradius
pidfile = ${run_dir}/freeradius.pid
max_request_time = 30
delete_blocked_requests = no
cleanup_delay = 5
max_requests = 1024
listen {
ipaddr = *
port = 0
type = auth
}
listen {
ipaddr = *
port = 0
type = acct
}
hostname_lookups = no
allow_core_dumps = no
regular_expressions = yes
extended_expressions = yes
log {
syslog_facility = daemon
}
log_stripped_names = no
log_auth = no
log_auth_badpass = no
log_auth_goodpass = no
checkrad = ${sbindir}/checkrad
security {
max_attributes = 200
reject_delay = 1
status_server = no
}
proxy_requests = no
$INCLUDE ${confdir}/clients.conf
snmp = no
thread pool {
start_servers = 5
max_servers = 32
min_spare_servers = 3
max_spare_servers = 10
max_requests_per_server = 0
}
modules {
pap {
auto_header = no
}
chap {
authtype = CHAP
}
pam {
pam_auth = radiusd
}
unix {
radwtmp = ${logdir}/radwtmp
}
$INCLUDE ${confdir}/eap.conf
mschap {
}
ldap ldap {
server = “ldap-neel.grenoble.cnrs.fr”
identity = “cn=radius,dc=grenoble,dc=cnrs,dc=fr”
password = admin-pwd
basedn = “dc=grenoble,dc=cnrs,dc=fr”
filter = “(|(|(uid=%{Stripped-User-Name:-%{User-Name}})(mail=%{Stripped-User-Name:-%{User-Name}}))(mail=%{Stripped-User-Name:-%{User-Name}}@grenoble.cnrs.fr))”
base_filter = “(objectclass=radiusprofile)”
ldap_connections_number = 5
timeout = 4
timelimit = 3
net_timeout = 1
tls {
start_tls = no
}
dictionary_mapping = ${raddbdir}/ldap.attrmap
auto_header = yes
groupname_attribute = radiusGroupName
#groupmembership_filter = “(|(&(uid=%{Stripped-User-Name:-%{User-Name}}))(&(aliasMail=%{Stripped-User-Name:-%{User-Name}})))(objectclass=radiusProfile)”
groupmembership_filter = “(|(|(uid=%{Stripped-User-Name:-%{User-Name}})(mail=%{Stripped-User-Name:-%{User-Name}}))(mail=%{Stripped-User-Name:-%{User-Name}}@grenoble.cnrs.fr))”
groupmembership_attribute = radiusGroupName
}
realm IPASS {
format = prefix
delimiter = “/”
}
realm suffix {
format = suffix
delimiter = “@”
}
realm realmpercent {
format = suffix
delimiter = “%”
}
realm ntdomain {
format = prefix
delimiter = “\\”
}
checkval {
item-name = Calling-Station-Id
check-name = Calling-Station-Id
data-type = string
}
attr_rewrite addtunneltype {
attribute = Tunnel-Type
searchin = proxy_reply
searchfor = “[+ ]”
replacewith = “VLAN”
new_attribute = yes
}
attr_rewrite addtunnelmediumtype {
attribute = Tunnel-Medium-Type
searchin = proxy_reply
searchfor = “[+ ]”
replacewith = “IEEE-802″
new_attribute = yes
}
attr_rewrite addvlanmcbt {
attribute = Tunnel-Private-Group-ID
searchin = proxy_reply
searchfor = “[+ ]”
replacewith = “244″
new_attribute = yes
}
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
header = “%t”
}
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 attr_filter.post-proxy {
attrsfile = ${confdir}/attrs
}
attr_filter attr_filter.pre-proxy {
attrsfile = ${confdir}/attrs.pre-proxy
}
sqlcounter noresetcounter {
counter-name = Max-All-Session-Time
check-name = “Max-All-Session”
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = never
query = “SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName=’%{%k}’”
}
sqlcounter hourlycounter {
counter-name = Hourly-Session-Time
check-name = Max-Hourly-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = hourly
query = “SELECT SUM(AcctSessionTime – \
GREATEST((%b – UNIX_TIMESTAMP(AcctStartTime)), 0)) \
FROM radacct WHERE UserName=’%{%k}’ AND \
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > ‘%b’”
}
sqlcounter dailycounter {
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
sqlmod-inst = sql
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
reply-name = Session-Timeout
sqlmod-inst = sql
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’”
}
sqlcounter yearlycounter {
counter-name = Yearly-Session-Time
check-name = Max-Yearly-Session
reply-name = Session-Timeout
sqlmod-inst = sql
key = User-Name
reset = 12m
query = “SELECT SUM(AcctSessionTime – \
GREATEST((%b – UNIX_TIMESTAMP(AcctStartTime)), 0)) \
FROM radacct WHERE UserName=’%{%k}’ AND \
UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > ‘%b’”
}
always fail {
rcode = fail
}
always reject {
rcode = reject
}
always ok {
rcode = ok
simulcount = 0
mpp = no
}
expr {
}
digest {
}
expiration {
reply-message = “Password Has Expired\r\n”
}
logintime {
reply-message = “You are calling outside your allowed timespan\r\n”
minimum-timeout = 60
}
exec {
wait = yes
input_pairs = request
shell_escape = yes
output = none
}
exec echo {
wait = yes
program = “/bin/echo %{User-Name}”
input_pairs = request
output_pairs = reply
shell_escape = yes
}
logintime {
}
}
instantiate {
exec
expr
}
authorize {
preprocess
sql
pap
chap
mschap
unix
suffix
eap
files
Autz-Type LDAP {
ldap
}
expiration
logintime
noresetcounter
hourlycounter
dailycounter
monthlycounter
yearlycounter
}
authenticate {
Auth-Type PAP {
pap
}
Auth-Type CHAP {
chap
}
eap
}
preacct {
preprocess
acct_unique
suffix
files
}
accounting {
detail
unix
radutmp
sql
}
session {
radutmp
sql
}
post-auth {
}
pre-proxy {
files
}
post-proxy {
Post-Proxy-Type post.proxy.mcbt {
addtunneltype
addtunnelmediumtype
addvlanmcbt
}
eap
}
Est vous pouvez m’aider ??