Hello, I want to setup sqlcounter to prevent users to login once they have reached traffic quota. So i added a new specific sqlcounter in raddbb_dir/sql/mysql/counter.conf, and the name of that counter in the authrorize section. All is as follow: #####counter.conf##### sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserNmae='%{%k}'" } #####virtual_server##### authorize { ... bytesQuota } #####users file##### scott Mana-Traffic = 101, Cleartext-Password := "tiger" whenever i launch freeradius -X I get the folloawing error: /etc/freeradius/users[205]: Parse error (check) for entry scott: Invalid octet string "101" for attribute name "Max-Traffic" Line 205 is the line where user scott is defined in users files. I have tryed setting up my own dictionnary (which i think shouldn't be needed) with the Max-Traffic attribute defined as interger VENDOR ME ATTRIBUTE Max-Traffic 1 integer But that doesn't help. I have read all over the web that sqlcounter with mysql are considered as stable enough for production , so am quite surprised of this issue... Any idea?
Alexandre Chapellon wrote:
whenever i launch freeradius -X I get the folloawing error: /etc/freeradius/users[205]: Parse error (check) for entry scott: Invalid octet string "101" for attribute name "Max-Traffic"
The modules are initialized in *order*. The sqlcounter module creates the attributes on the fly. But... it can't do this if it hasn't been run yet.
Line 205 is the line where user scott is defined in users files. I have tryed setting up my own dictionnary (which i think shouldn't be needed) with the Max-Traffic attribute defined as interger
VENDOR ME ATTRIBUTE Max-Traffic 1 integer
That isn't the correct dictionary file format, but it's not relevant, either.
But that doesn't help. I have read all over the web that sqlcounter with mysql are considered as stable enough for production , so am quite surprised of this issue... Any idea?
List "sqlcounter" in the "instantiate" section. It will be initialized before the "users" file is read. Alan DeKok.
You're right, adding the name of my sqlcounter in the instantiate section lake it works. thx :) Alan DeKok a écrit :
Alexandre Chapellon wrote:
whenever i launch freeradius -X I get the folloawing error: /etc/freeradius/users[205]: Parse error (check) for entry scott: Invalid octet string "101" for attribute name "Max-Traffic"
The modules are initialized in *order*. The sqlcounter module creates the attributes on the fly. But... it can't do this if it hasn't been run yet.
Line 205 is the line where user scott is defined in users files. I have tryed setting up my own dictionnary (which i think shouldn't be needed) with the Max-Traffic attribute defined as interger
VENDOR ME ATTRIBUTE Max-Traffic 1 integer
That isn't the correct dictionary file format, but it's not relevant, either.
But that doesn't help. I have read all over the web that sqlcounter with mysql are considered as stable enough for production , so am quite surprised of this issue... Any idea?
List "sqlcounter" in the "instantiate" section. It will be initialized before the "users" file is read.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I want to se sqlcounters to count bytes transferred from clients so that i manage quota. Aiming this i configured the following counter: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Session-Traffic-Limit sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" } My first problem is that the Session-Traffic-Limit (from the redback dictionnary) is not returned. I can't see it neither in the output of radtest nor with radsniff. Yet, looking at the output of radiusd -X i can see it's correctly understood by freeradius: rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user scott, check_item=12000, counter=10891 rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12792 ++[bytesQuota] returns ok Does anyone has a clue? Alexandre Chapellon a écrit :
You're right, adding the name of my sqlcounter in the instantiate section lake it works. thx :)
Alan DeKok a écrit :
Alexandre Chapellon wrote:
whenever i launch freeradius -X I get the folloawing error: /etc/freeradius/users[205]: Parse error (check) for entry scott: Invalid octet string "101" for attribute name "Max-Traffic"
The modules are initialized in *order*. The sqlcounter module creates the attributes on the fly. But... it can't do this if it hasn't been run yet.
Line 205 is the line where user scott is defined in users files. I have tryed setting up my own dictionnary (which i think shouldn't be needed) with the Max-Traffic attribute defined as interger
VENDOR ME ATTRIBUTE Max-Traffic 1 integer
That isn't the correct dictionary file format, but it's not relevant, either.
But that doesn't help. I have read all over the web that sqlcounter with mysql are considered as stable enough for production , so am quite surprised of this issue... Any idea?
List "sqlcounter" in the "instantiate" section. It will be initialized before the "users" file is read.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12792 Which part dont you understand? Sqlcounter returned it. How does the log part of the RADIUS Packet looks like? It should contain the Session-Traffic-Limit if it can be found in the dictionary, right? E:S From: freeradius-users-bounces+edvin.seferovic=kolp.at@lists.freeradius.org [mailto:freeradius-users-bounces+edvin.seferovic=kolp.at@lists.freeradius.or g] On Behalf Of Alexandre Chapellon Sent: Samstag, 06. September 2008 01:53 To: FreeRadius users mailing list Subject: sqlcounters for traffic I want to se sqlcounters to count bytes transferred from clients so that i manage quota. Aiming this i configured the following counter: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Session-Traffic-Limit sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" } My first problem is that the Session-Traffic-Limit (from the redback dictionnary) is not returned. I can't see it neither in the output of radtest nor with radsniff. Yet, looking at the output of radiusd -X i can see it's correctly understood by freeradius: rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user scott, check_item=12000, counter=10891 rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12792 ++[bytesQuota] returns ok Does anyone has a clue? Alexandre Chapellon a écrit : You're right, adding the name of my sqlcounter in the instantiate section lake it works. thx :) Alan DeKok a écrit : Alexandre Chapellon wrote: whenever i launch freeradius -X I get the folloawing error: /etc/freeradius/users[205]: Parse error (check) for entry scott: Invalid octet string "101" for attribute name "Max-Traffic" The modules are initialized in *order*. The sqlcounter module creates the attributes on the fly. But... it can't do this if it hasn't been run yet. Line 205 is the line where user scott is defined in users files. I have tryed setting up my own dictionnary (which i think shouldn't be needed) with the Max-Traffic attribute defined as interger VENDOR ME ATTRIBUTE Max-Traffic 1 integer That isn't the correct dictionary file format, but it's not relevant, either. But that doesn't help. I have read all over the web that sqlcounter with mysql are considered as stable enough for production , so am quite surprised of this issue... Any idea? List "sqlcounter" in the "instantiate" section. It will be initialized before the "users" file is read. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html _____ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
My first problem is that the Session-Traffic-Limit (from the redback dictionnary) is not returned. I can't see it neither in the output of radtest nor with radsniff. Yet, looking at the output of radiusd -X i can see it's correctly understood by freeradius:
rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user scott, check_item=12000, counter=10891 rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12792 ++[bytesQuota] returns ok
It looks like it made it to the reply list.
Does anyone has a clue?
Not without the rest of the debug. Ivan Kalik Kalik Informatika ISP
tnt@kalik.net a écrit :
My first problem is that the Session-Traffic-Limit (from the redback dictionnary) is not returned. I can't see it neither in the output of radtest nor with radsniff. Yet, looking at the output of radiusd -X i can see it's correctly understood by freeradius:
rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user scott, check_item=12000, counter=10891 rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12792 ++[bytesQuota] returns ok
It looks like it made it to the reply list.
Yep, but when sniffing network, it appears it's not.
Does anyone has a clue?
Not without the rest of the debug.
Here is the full debug outputed during the auth query/reply rad_recv: Access-Request packet from host 127.0.0.1 port 37792, id=201, length=57 User-Name = "scott" User-Password = "tiger" NAS-IP-Address = 192.168.20.145 NAS-Port = 451 +- entering group authorize ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop rlm_realm: No '@' in User-Name = "scott", looking up realm NULL rlm_realm: No such realm "NULL" ++[suffix] returns noop rlm_eap: No EAP-Message, not doing EAP ++[eap] returns noop ++[unix] returns notfound users: Matched entry scott at line 205 ++[files] returns ok ++[expiration] returns noop ++[logintime] returns noop ++[pap] returns updated rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{User-Name}'' expand: SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{User-Name}' -> SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='scott' sqlcounter_expand: '%{mysqldb:SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='scott'}' rlm_sql (mysqldb): - sql_xlat expand: %{User-Name} -> scott rlm_sql (mysqldb): sql_set_user escaped user --> 'scott' expand: SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='scott' -> SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='scott' expand: /var/log/freeradius/sqltrace.sql -> /var/log/freeradius/sqltrace.sql rlm_sql (mysqldb): Reserving sql socket id: 4 rlm_sql_mysql: query: SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='scott' rlm_sql (mysqldb): - sql_xlat finished rlm_sql (mysqldb): Released sql socket id: 4 expand: %{mysqldb:SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='scott'} -> 10891 rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user scott, check_item=12000, counter=10891 rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12694 ++[bytesQuota] returns ok rad_check_password: Found Auth-Type auth: type "PAP" +- entering group PAP rlm_pap: login attempt with password "tiger" rlm_pap: Using clear text password "tiger" rlm_pap: User authenticated successfully ++[pap] returns ok +- entering group session expand: %{User-Name} -> scott rlm_sql (mysqldb): sql_set_user escaped user --> 'scott' expand: SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL -> SELECT COUNT(*) FROM radacct WHERE username = 'scott' AND acctstoptime IS NULL rlm_sql (mysqldb): Reserving sql socket id: 3 rlm_sql_mysql: query: SELECT COUNT(*) FROM radacct WHERE username = 'scott' AND acctstoptime IS NULL rlm_sql (mysqldb): Released sql socket id: 3 ++[mysqldb] returns ok +- entering group post-auth expand: %{NAS-IP-Address} %{NAS-Port} -> 192.168.20.145 451 rlm_ippool: MD5 on 'key' directive maps to: 20879599c5e6463384e80698249eaa87 rlm_ippool: Searching for an entry for key: '20879599c5e6463384e80698249eaa87' rlm_ippool: Found a stale entry for ip: 10.0.116.61 rlm_ippool: num: 0 rlm_ippool: Allocating ip to key: '20879599c5e6463384e80698249eaa87' rlm_ippool: num: 1 rlm_ippool: Allocated ip 10.0.129.45 to client key: 20879599c5e6463384e80698249eaa87 ++[test_pool] returns ok rlm_sql (mysqldb): Processing sql_postauth expand: %{User-Name} -> scott rlm_sql (mysqldb): sql_set_user escaped user --> 'scott' expand: %{User-Password} -> tiger expand: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'scott', 'tiger', 'Access-Accept', '2008-09-05 14:48:26') expand: /var/log/freeradius/sqltrace.sql -> /var/log/freeradius/sqltrace.sql rlm_sql (mysqldb) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'scott', 'tiger', 'Access-Accept', '2008-09-05 14:48:26') rlm_sql (mysqldb): Reserving sql socket id: 2 rlm_sql_mysql: query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'scott', 'tiger', 'Access-Accept', '2008-09-05 14:48:26') rlm_sql (mysqldb): Released sql socket id: 2 ++[mysqldb] returns ok ++[exec] returns noop Sending Access-Accept of id 201 to 127.0.0.1 port 37792 Session-Traffic-Limit = "" Framed-IP-Address = 10.0.129.45 Framed-IP-Netmask = 255.255.255.255 Finished request 0.
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Alexandre Chapellon wrote:
Here is the full debug outputed during the auth query/reply ... rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12694 ... Sending Access-Accept of id 201 to 127.0.0.1 port 37792 Session-Traffic-Limit = ""
That's the problem. Looking at dictionary.redback, Session-Traffic-Limit is a string. It's not an integer counter. If you do really want to use Session-Traffic-Limit, you will have to change sqlcounter to use a *different* attribute in the reply, such as Tmp-Integer-0, which is a server-side attribute. Then use "unlang" in post-auth to copy it to Session-Traffic-Limit: update reply { Session-Traffic-Limit = "%{reply:Tmp-Integer-0}" } That should work. Alan DeKok.
Good it's sent in the reply to the nas! Thx But the sqlcounter i setup was supposed to reset every hours , but apparently doesn't... Where can i take a look to find out why? Is it supposed to update the database to reset counters (which seems a bad solution to me) or does freeradius maintain separate counters elsewhere, using accounting database to feed them? Alan DeKok a écrit :
Alexandre Chapellon wrote:
Here is the full debug outputed during the auth query/reply
...
rlm_sqlcounter: Sent Reply-Item for user scott, Type=Session-Traffic-Limit, value=12694
...
Sending Access-Accept of id 201 to 127.0.0.1 port 37792 Session-Traffic-Limit = ""
That's the problem. Looking at dictionary.redback, Session-Traffic-Limit is a string. It's not an integer counter.
If you do really want to use Session-Traffic-Limit, you will have to change sqlcounter to use a *different* attribute in the reply, such as Tmp-Integer-0, which is a server-side attribute. Then use "unlang" in post-auth to copy it to Session-Traffic-Limit:
update reply { Session-Traffic-Limit = "%{reply:Tmp-Integer-0}" }
That should work.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Good it's sent in the reply to the nas! Thx But the sqlcounter i setup was supposed to reset every hours , but apparently doesn't... Where can i take a look to find out why?
Check the sql query definition and value of reset in counter.conf. Ivan Kalik Kalik Informatika ISP
tnt@kalik.net a écrit :
Good it's sent in the reply to the nas! Thx But the sqlcounter i setup was supposed to reset every hours , but apparently doesn't... Where can i take a look to find out why?
Check the sql query definition and value of reset in counter.conf.
here is the counter definition: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Tmp-Integer-0 sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" } sounds good to me... what could be the reasn for noreset?
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
here is the counter definition: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Tmp-Integer-0 sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" }
sounds good to me... what could be the reasn for noreset?
query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'"
You need to add to WHERE one of the statements using %b. Ivan Kalik Kalik Informatika ISP
tnt@kalik.net a écrit :
here is the counter definition: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Tmp-Integer-0 sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" }
sounds good to me... what could be the reasn for noreset?
query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'"
You need to add to WHERE one of the statements using %b.
Ouch i didn't saw that in the example queries... spank my ...!
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
so what's the use of the reset parameter if th sql query is managing it all by its own? Alexandre Chapellon a écrit :
tnt@kalik.net a écrit :
here is the counter definition: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Tmp-Integer-0 sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" }
sounds good to me... what could be the reasn for noreset?
query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'"
You need to add to WHERE one of the statements using %b.
Ouch i didn't saw that in the example queries... spank my ...!
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
reset parameter controls value of %b. Ivan Kalik Kalik Informatika ISP Dana 9/9/2008, "Alexandre Chapellon" <alexandre.chapellon@mana.pf> piše:
so what's the use of the reset parameter if th sql query is managing it all by its own?
Alexandre Chapellon a écrit :
tnt@kalik.net a écrit :
here is the counter definition: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Tmp-Integer-0 sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" }
sounds good to me... what could be the reasn for noreset?
query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'"
You need to add to WHERE one of the statements using %b.
Ouch i didn't saw that in the example queries... spank my ...!
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/usershtml
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Looking at the source of rlm_sqlcounter i saw that when a users tries to conect at a time close to the next reset time, the value of the check-item for the next cycle is added to the reply item. I'd like to avoid this behaviour for **some** of my users. Indeed I want to use counters to count traffic and **not** time while rlm_sqlcounter decide it's closed to reset time when check-item - counter (in my case: bytes) is less than the number of second untill reset time. Can i disable this behaviour? how? tnt@kalik.net a écrit :
reset parameter controls value of %b.
Ivan Kalik Kalik Informatika ISP
Dana 9/9/2008, "Alexandre Chapellon" <alexandre.chapellon@mana.pf> piše:
so what's the use of the reset parameter if th sql query is managing it all by its own?
Alexandre Chapellon a écrit :
tnt@kalik.net a écrit :
here is the counter definition: sqlcounter bytesQuota { counter-name = traffic_quota check-name = Max-Traffic reply-name = Tmp-Integer-0 sqlmod-inst = mysqldb key = User-Name reset = hourly query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'" }
sounds good to me... what could be the reasn for noreset?
query = "SELECT SUM(acctinputoctets + acctoutputoctets) FROM radacct WHERE UserName='%{%k}'"
You need to add to WHERE one of the statements using %b.
Ouch i didn't saw that in the example queries... spank my ...!
Ivan Kalik Kalik Informatika ISP
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/usershtml
------------------------------------------------------------------------
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Alan DeKok -
Alexandre Chapellon -
Edvin Seferovic -
tnt＠kalik.net