Support for MySQL Stored Procedures in FreeRADIUS 2.0?
Will there be support for MySQL Stored Procedures in 2.0? FreeRADIUS 2.0.0-pre1 does not yet support SP in MySQL. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
That is great news! Alan DeKok wrote: >Thomas Martens wrote: >> I added your hack to my version too. >> I also don't get any errors till now. It seems to work with SP, and >> also normal SQL-querys. > > Sounds good to me. > >> Here is the diff...so please, a FR developer take a look at it;) > > Nicolas is looking into it. It should be in 1.1.7 && in 2.0. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
Did some further research on the MySQL - FR Stored Procedure (SP) problem.
When calling the SP, MySQL always returns two results. One is the actual
result and
the other is the number of affected rows, which is different to a normal
e.g. SELECT query.
SP:
mysql> call CheckIt('myString');
++
| result |
++
| 10 | (result is correct)
++
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec) <-- Result plus the number of affected
rows!
Normal Query:
mysql> select 25 AS result;
++
| result |
++
| 25 |
++
1 row in set (0.00 sec) <--- Normal query with one result
MYSQL 5.0 Ref manual
If you write C programs that use the CALL SQL statement to execute stored
procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS
flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS
when you call mysql_real_connect(). This is because each such stored
procedure produces multiple results: the result sets returned by statements
executed within the procedure, as well as a result to indicate the call
status. To process the result of a CALL statement, use a loop that calls
mysql_next_result() to determine whether there are more results.
The following procedure outlines a suggested strategy for handling multiple
statements:
1. Pass CLIENT_MULTI_STATEMENTS to mysql_real_connect(), to fully enable
multiple-statement execution and multiple-result processing.
2. After calling mysql_query() or mysql_real_query() and verifying that it
succeeds, enter a loop within which you process statement results.
3. For each iteration of the loop, handle the current statement result,
retrieving either a result set or an affected-rows count. If an error
occurs, exit the loop.
4. At the end of the loop, call mysql_next_result() to check whether
another result exists and initiate retrieval for it if so. If no more
results are available, exit the loop.
--
Just for a test, I added a very quick and dirty 'mysql_next_result' into the
sql_free_result function of
"sql_mysql.c" in row 292 of FR 1.1.6, the same location Thomas used the
.
if (sqlsocket->row == NULL) {
return sql_check_error(mysql_errno(mysql_sock->sock));
}
mysql_next_result(mysql_sock->sock); /* eat the number of affected
rows result */
return 0;
}
.
As a result I do not get the 2014 error anymore and everything seems to be
working fine.
Since I do not really know the implications of just adding this command,
maybe one of the experts
could help out here.
In an ealier posting 3 days ago I said that the problem is not really stored
procedure related ...
but it is! Once the SP is called at least once other queries will have
errors too.
Gunther
FR 1.1.6 - MySQL 5.0.41 - CentOS 4.4
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
I added this patch to sql_mysql.c and started testing.
1. When I do not call any SPs, it works fine
2. When I call a SP for the first time, it works fine
3. When I call a SP for the second and more time, the 2014 error shows again
It seems that these errors happen whenever any SP is called a second time,
and there are still some results not freed.
Gunther
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
g] On Behalf Of cky
Sent: Tuesday, 08 May 2007 10:18 AM
To: freeradius-users@lists.freeradius.org
Subject: Re: FR with MySQL - Stored Procedures
so...after a while I found a workaround for the SP problem..well, is quick
and dirty and probably not the best solution.
I've added to the function "sql_fetch_row" in sql_mysql.c a simple
"sql_free_result(sqlsocket, config);"...see the diff below.
Now my Stored Procedure works correct and as expected (my SP is at the end
of this message).
Perhaps someone can verify this, and perhaps this can added to the
freeradius sourcecode from a developer (well, perhaps not so dirty ;) )
--- sql_mysql.c_org 2007-05-08 15:55:47.0 +0200
+++ sql_mysql.c 2007-05-08 15:57:35.0 +0200
@@ -50,6 +50,8 @@
SQL_ROW row;
} rlm_sql_mysql_sock;
+static int sql_free_result(SQLSOCK*, SQL_CONFIG*);
+
/*
*
* Function: sql_create_socket
@@ -82,7 +84,7 @@
config->sql_db,
atoi(config->sql_port),
NULL,
- CLIENT_FOUND_ROWS))) {
+
CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) {
radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to
MySQL server [EMAIL PROTECTED]:%s", config->sql_login, config->sql_server,
config->sql_db);
radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'",
mysql_error(&mysql_sock->conn));
mysql_sock->sock = NULL; @@ -289,6 +291,9 @@
if (sqlsocket->row == NULL) {
return sql_check_error(mysql_errno(mysql_sock->sock));
}
+
+ sql_free_result(sqlsocket, config);
+
return 0;
}
My SP:
CREATE PROCEDURE ssg_auth (IN `CallingStationID` VARCHAR(14),IN `ClientIP`
VARCHAR(15))
SQL SECURITY INVOKER
BEGIN
DECLARE rows INT (1);
DECLARE v_id BIGINT (20);
DECLARE v_UserName VARCHAR(10);
DECLARE v_Attribute,v_Value VARCHAR (30); DECLARE v_op VARCHAR (4);
DECLARE v_IP VARCHAR (15); DECLARE user CURSOR FOR SELECT
`id`,`UserName`,`Attribute`,`Value`,`op`,`IP` FROM `ssg_check` WHERE
`Calling-Station-Id` = `CallingStationID`; SELECT COUNT(*) INTO rows FROM
`ssg_check` WHERE `Calling-Station-Id` = `CallingStationID`;
IF rows = 0 THEN
INSERT INTO `ssg_check` (`Calling-Station-Id`,`IP`) VALUES
(`CallingStationID`,`ClientIP`);
SELECT '0','Guest','Auth-Type','REJECT',':=';
END IF;
IF rows = 1 THEN
OPEN user;
FETCH user INTO v_id,v_UserName,v_Attribute,v_Value,v_op,v_IP;
CLOSE user;
IF v_Value = 'REJECT' and v_ip != `ClientIP` THEN
UPDATE `ssg_check` SET `IP` = `ClientIP` WHERE `Calling-Station-Id` =
`CallingStationID`;
END IF;
SELECT v_id,v_UserName,v_Attribute,v_Value,v_op;
END IF;
END; //
Thomas
--
View this message in context:
http://www.nabble.com/FR-with-MySQL---Stored-Procedures-tf3701829.html#a1037
6727
Sent from the FreeRadius - User mailing list archive at Nabble.com.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FR with MySQL - Stored Procedures
Alan DeKok wrote >> I forgot to mention that the problem I reported previously is actually >> not 'Stored Procedure' related, but related to the MySQL client_flag >> 'CLIENT_MULTI_STATEMENTS'. This flag is required to support Stored >> Procedures and is causing the problem (at least with my operating >> system (CentOS 4.4)). So no need to create any stored procedures ... >> just compile FR with CLIENT_MULTI_STATEMENTS in the >> rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c module. > > OK. > >> Looking forward using Stored Procedures (no more 253 byte limit for my >> SQL statements!!!). > > Huh? From the changelog in 1.1.5: > > * Increase buffer size for dynamic expansion, which allows > longer SQL qeuries. (close: #405) > > 1.1.5 and 1.1.6 allow SQL queries up to 4k in length. The only limit of 253 bytes is the data that has to go into a RADIUS packet. > > Alan DeKok. Thanks Alan! OK, seems I am not really up-to-date ;-) Good to know that that limit has been lifted. When things work ... you hardly like to touch them ;-) I thought this was a RADIUS definition issue and cannot be changed (rfc) as I learned some time ago ... Anyhow, the stored procedures is the way I like to proceed and it would be great if I can use them within FreeRadius like other people apparently do. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: FR with MySQL - Stored Procedures
I forgot to mention that the problem I reported previously is actually not 'Stored Procedure' related, but related to the MySQL client_flag 'CLIENT_MULTI_STATEMENTS'. This flag is required to support Stored Procedures and is causing the problem (at least with my operating system (CentOS 4.4)). So no need to create any stored procedures ... just compile FR with CLIENT_MULTI_STATEMENTS in the rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c module. Looking forward using Stored Procedures (no more 253 byte limit for my SQL statements!!!). Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
FR with MySQL - Stored Procedures
Despite that several people reported that their FreeRadius 1.x installation
is working fine with MySQL Stored Procedures, I run into quite some
problems.
Here my environment:
- CentOS 4.4 on Xen Server 3.1
- FreeRadius 1.1.6
- MySQL 5.0.37 Community Edition with INNODB Tables
I used a very simple stored procedure to track down the problem:
---
DELIMITER //
DROP PROCEDURE IF EXISTS CheckIt //
CREATE PROCEDURE CheckIt ()
BEGIN
SELECT 12345;
END//
---
This routine will always return the value 12345.
Anyhow, when I called this procedure from FreeRadius I always go an error:
"PROCEDURE myDB.CheckIt can't return a result set in the given context"
-
Sun May 6 07:23:10 2007 : Debug: rlm_sql_mysql: query: CALL CheckIt()
Sun May 6 07:23:10 2007 : Debug: rlm_sql_mysql: MYSQL check_error: 1312
received
Sun May 6 07:23:10 2007 : Error: rlm_sql (sql): database query error, CALL
CheckIt(): PROCEDURE myDB.CheckIt can't return a result set in the given
context
-
I then tried to call the same function via a PHP script (w/o FreeRadius
involved) and run into the same problem.
Then I found the information that it is required for MySQL Stored Procedures
to function
the client_flag 'CLIENT_MULTI_STATEMENTS' (refer to mysql.h) has to be added
to the mysql_real_connect call.
After adding it to the call within PHP all worked fine.
Then I added the flag to
freeradius-1.1.6/src/modules/rlm_sql/drivers/rlm_sql_mysql/sql_mysql.c :
-
if (!(mysql_sock->sock = mysql_real_connect(&(mysql_sock->conn),
config->sql_server,
config->sql_login,
config->sql_password,
config->sql_db,
atoi(config->sql_port),
NULL,
CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) {
-
./configure; make; make install
Note: CLIENT_MULTI_STATEMENTS automatically also sets CLIENT_MULTI_RESULTS
within MySQL
Started FreeRadius and procedure calls were accepted and results were
correct.
-
Sun May 6 21:29:08 2007 : Debug: rlm_sql_mysql: query: CALL CheckIt()
Sun May 6 21:29:08 2007 : Debug: rlm_sql (sql): - sql_xlat finished
Sun May 6 21:29:08 2007 : Debug: rlm_sql (sql): Released sql socket id: 0
Sun May 6 21:29:08 2007 : Debug: radius_xlat: '12345'
-
A user can now login and things seem to work fine. But then consecutive
MySQL queries started
showing new error results:
-
Sun May 6 21:41:42 2007 : Debug: rlm_sql_mysql: MYSQL check_error: 2014
received
Sun May 6 21:41:42 2007 : Error: rlm_sql (sql): database query error,
: Commands out of sync; you can't run this command
now
-
MySQL seems to track the state of each call and when the order of this state
is incorrect,
MySQL responds with 'CR_COMMANDS_OUT_OF_SYNC' = 'Commands out of sync; you
can't run this command now'.
>From what I found on the net ... "When the result of a statement isn't freed
MySQL gives an error when
trying to process a new query"
Could it be that there is somewhere a 'mysql_free_result' missing?
This is what the MySQL documentation is saying:
B.1.2.13. Commands out of sync
If you get Commands out of sync; you can't run this command now in your
client code, you are calling client functions in the wrong order.
This can happen, for example, if you are using mysql_use_result() and try to
execute a new query before you have called mysql_free_result(). It can also
happen if you try to execute two queries that return data without calling
mysql_use_result() or mysql_store_result() in between.
2.4.16. Upgrading MySQL
If, after an upgrade, you experience problems with recompiled client
programs, such as Commands out of sync or unexpected core dumps, you
probably have used old header or library files when compiling your programs.
In this case, you should check the date for your mysql.h file and
libmysqlclient.a library to verify that they are from the new MySQL
distribution. If not, recompile your programs with the new headers and
libraries.
... Did that ...
When I leave some time between a login/logout/login it works ... Looks like
the MySQL status information
times out after a short while.
Any hints on getting this up and running without changing O/S ? Thanks!
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Parse error for entry DEFAULT entry in users file when Auth-Type := SQL
> Alan DeKok wrote > > I was trying to upgrade from version 1.1.0 to 1.1.3 but > when starting > > FreeRadius I received the following error: > > "Error: /usr/local/etc/raddb/users[216]: Parse error > (check) for entry > > DEFAULT: Unknown value SQL for attribute Auth-Type" > > That's because the SQL module doesn't do authentication. > > > I use FR with MySQL and the ./raddb/users file contains the default > > attributes. > > E.g. > > DEFAULT Simultaneous-Use := 1, Auth-Type := sql > > Why are you forcing Auth-Type to SQL? > > > Idle-Timeout = 3600, > > Acct-Interim-Interval = 180 > > > > This worked fine until FR 1.1.1 and stopped working with 1.1.2. > > No, it did NOT work in 1.1.1. It was IGNORED in 1.1.1, due > to a bug in the server. Yes, that is what I thought. I changed my users file to: DEFAULT Simultaneous-Use := 1 Idle-Timeout = 3600, Acct-Interim-Interval = 180 and things are working fine. > > In a posting from Oct 2001 I saw the following: > >> If you want to do SQL authentication, you should use > 'Auth-Type := SQL' > >> in the 'users' file. > > If that was a post from me, it's a typo. It's wrong. > > Alan DeKok. > -- What I found in the 'ancient' times: http://lists.freeradius.org/mailman/htdig/freeradius-users/2001-October/0025 18.html My initial reason for the upgrade from 1.1.0 was a problem with my backup RADIUS server. The idle RADIUS daemon simply died for unknown reasons. It looked to me that RADIUS did a reload of the configuration files and got killed. This happend in in normal and debug mode. With 1.1.3 I did not yet experience that problem. Thanks for your response Alan! Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Parse error for entry DEFAULT entry in users file when Auth-Type := SQL
I was trying to upgrade from version 1.1.0 to 1.1.3 but when starting
FreeRadius I received the
following error:
"Error: /usr/local/etc/raddb/users[216]: Parse error (check) for entry
DEFAULT: Unknown value SQL for attribute Auth-Type"
I use FR with MySQL and the ./raddb/users file contains the default
attributes.
E.g.
DEFAULT Simultaneous-Use := 1, Auth-Type := sql
Idle-Timeout = 3600,
Acct-Interim-Interval = 180
This worked fine until FR 1.1.1 and stopped working with 1.1.2.
I found that one change in the ./src/lib/valuepair.c file caused this
problem:
At the end of the pairread function: (1265-1271)
/*
* If we didn't make a pair, return an error.
*/
if (!vp) {
*eol = T_INVALID;
return NULL;
}
It seems that the pairmake function does not return a valid pair when the
pair is 'Auth-Type := SQL'.
I am not sure if I use the users file in connection with my MySQL setup
correctly, or
if my users file entry is simply not correct?
Is 'Auth-Type := SQL' in the users file still allowed? It doesn't work
anymore since 1.1.2.
In a posting from Oct 2001 I saw the following:
> If you want to do SQL authentication, you should use 'Auth-Type := SQL'
>in the 'users' file.
>
> If you want to authenticate agains /etc/passwd, THEN you use
>'Auth-Type := System'.
>
> Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: acct_unique module - Dynamic Client-IP-Address causes duplicate session entries in accounting table
Alan DeKok wrote: > "Gunther" <[EMAIL PROTECTED]> wrote: > > But when the connection to the ISP drops for a moment, a new > > Client-IP-Address will be used and the Acct-Unique-Session-Id will > > change in the middle of a session. > > You're saying a users session stays up even if the NAS goes > down? I haven't heard that before. No, the NAS does not go down, just either the router to the ISP or the ISP, causing a new IP address in a ISP DHCP environment > > > Any recommendations on this? > > It looks like it would work, but why is the > Client-IP-Address changing? Yes, for the last 12 hours it works well. The Client-IP-Address changes when the connection to the ISP is down for whatever reason. Here two packets were the ISP connection was down for 30 minutes (a moment), while the NAS was up and running, keeping the session alive. With the use of Calling-Station-Id instead of Client-IP-Address for acct_unique, the existing accounting table entry will be used instead of adding another entry for the same session (Acct-Unique-Session-Id remains the same!): Sat Nov 18 12:18:10 2006 Acct-Status-Type = Interim-Update User-Name = "MyUsername" Calling-Station-Id = "00-0D-88-00-xx-B9" Called-Station-Id = "00-16-B6-1C-xx-32" NAS-Port-Type = Wireless-802.11 NAS-Port = 8 NAS-Port-Id = "0008" NAS-IP-Address = 0.0.0.0 NAS-Identifier = "00-16-B6-1C-xx-34" Framed-IP-Address = 192.168.182.5 Acct-Session-Id = "455e3b1a0008" Acct-Input-Octets = 213280 Acct-Output-Octets = 213280 Acct-Input-Gigawords = 0 Acct-Output-Gigawords = 0 Acct-Input-Packets = 2666 Acct-Output-Packets = 2666 Acct-Session-Time = 48859 Client-IP-Address = xx.yy.80.129 Acct-Unique-Session-Id = "36857b8cc9e1608f" Timestamp = 1163852290 Sat Nov 18 12:48:40 2006 Acct-Status-Type = Interim-Update User-Name = "MyUsername" Calling-Station-Id = "00-0D-88-00-xx-B9" Called-Station-Id = "00-16-B6-1C-xx-32" NAS-Port-Type = Wireless-802.11 NAS-Port = 8 NAS-Port-Id = "0008" NAS-IP-Address = 0.0.0.0 NAS-Identifier = "00-16-B6-1C-xx-34" Framed-IP-Address = 192.168.182.5 Acct-Session-Id = "455e3b1a0008" Acct-Input-Octets = 221200 Acct-Output-Octets = 221200 Acct-Input-Gigawords = 0 Acct-Output-Gigawords = 0 Acct-Input-Packets = 2765 Acct-Output-Packets = 2765 Acct-Session-Time = 50689 Client-IP-Address = xx.yy.22.61 <-- New IP Address Acct-Unique-Session-Id = "36857b8cc9e1608f" <-- Same unique Id Timestamp = 1163854120 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
acct_unique module - Dynamic Client-IP-Address causes duplicate session entries in accounting table
My set up: multiple servers with FreeRadius 1.1.0, MySQL 5.0.27 with
Master-Master replication
After some trouble with duplicate entries in the radius accounting table due
to
non-unique Acct-Session-Id's, I switched to using the acct_unique module.
acct_unique {
key = "User-Name, Acct-Session-Id, NAS-IP-Address,
Client-IP-Address, NAS-Port"
}
This works fine as long as the Client-IP-Address remains a constant during a
session.
But when the connection to the ISP drops for a moment, a new
Client-IP-Address will be used and the
Acct-Unique-Session-Id will change in the middle of a session. This causes a
new session to be inserted
into the accounting table. The new session simply continues using all the
values of the
previous session as the NAS is not aware of the Client-IP-Address change.
This will cause that the time of the first session and the second session
are added, even it
is the same session. This is pretty bad for prepaid tickets as their life is
shortend.
I like to change the acct_unique set up and exchange the Client-IP-Address
with another more
steady parameter like NAS-Identifier or Called-Station-Id or
Calling-Station-Id or Framed-IP-Address.
I am going to use the following:
acct_unique {
key = "User-Name, Acct-Session-Id, NAS-IP-Address,
Calling-Station-Id, NAS-Port"
}
The Calling-Station-Id (end-user MAC address) would not change during a
session and
is not really user selectable.
While the Called-Station-Id and NAS-Identifier could occur multiple times as
this might be
a configurable NAS parameter.
Any recommendations on this?
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
How to rewrite the sql_user_name
I am trying to rewrite the username (sql_user_name) to always add the realm
to the username. I am getting different 'User-Name' and 'Realm' from
different NAS units.
My db storage format is: [EMAIL PROTECTED]
One unit is sending it in this way already ([EMAIL PROTECTED]), the other unit
from a
different vendor can send the domain in the 'Realm' part:
User-Name = user
Realm = domain
How can I rewrite the User-Name that I always get it in my preferred db
format?
I tried:
sql_user_name = "%{%{Realm:[EMAIL PROTECTED]:-%{User-Name}}"
But it seems that the conditional sh syntax :+ does not work, only :-
And I do not exactly know if this nested approach could work anyhow.
Any hints?
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Auth-Type = System not working
Have you tried: Auth-Type := System > -Original Message- > From: Andreas Lund > Sent: Friday, April 07, 2006 9:37 AM > Subject: Auth-Type = System not working > > Hello, > > What kind of magic tricks are needed to get Auth-Type = > System to work? > > Running FreeRADIUS Version 1.0.4 on SuSE 10 with MD5 and > shadow passwords, using the following 'users' file: > > DEFAULT Auth-Type = System >Service-Type = Framed-User, >Framed-Protocol = PPP, >Port-Limit = 2, >Framed-IP-Address = 255.255.255.254, >Framed-MTU = 1500 > > > Now, I restart radiusd and test it: > > # echo "User-Name = ###, User-Password = ###" | radclient -x > localhost auth ### Sending Access-Request of id 151 to 127.0.0.1:1645 >User-Name = "###" >User-Password = "###" > rad_recv: Access-Reject packet from host 127.0.0.1:1645, > id=151, length=20 > > > I get this in my radius log: > Fri Apr 7 14:39:01 2006 : Auth: rlm_unix: [###]: invalid password > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Multiple Accounting in Radacct
Hi, from what I see in the log files ... A start packet from the NAS has reached the FreeRadius server, but the ACK packet has not reached the NAS, therefore the NAS is sending the packet again, and the ACK packet again did not reach ... a few times. The last packet reached the NAS and accounting starts. But since there are now 3 identical accounting records in the db (with identical Unique IDs), all db records are updated at the same time. To avoid this I made the UniqueSessionID column in the radacct table UNIQUE within MySQL. This avoids the creation of a duplicate records in the db. On Mar 28, 2006, at 3:09 AM, zack musa wrote: Hi all. Thanks for your reply gunther. Can you explain a bit of what might happen when radius start duplicating/recording/receiving the same user information 3 times in a row and some cases might be more, maximum i got is 7 times in mysql database. what brings u to that solution? Or is there anyone else who got similar problems before? user get connection but recorded until 7 times within 40 seconds! Is this come from NAS or radius server conifguration or MySQL? Thanks to all. Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Multiple Accounting in Radacct
Hi, I posted the same problem just a few days ago 21-Mar-2006 .. (Subject: Duplicate Accounting Start Packets) I added my solution, but did not get any feedback if this would be ok. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of zack musaSent: Monday, March 27, 2006 12:50 AMTo: freeradius-users@lists.freeradius.orgSubject: Multiple Accounting in Radacct Hi all. My radius server records to mysql database for every single user session for 3 times in three rows. similar records for 1 single user but the different is in mil.sec. What configuration in freeradius might done this? Is this because the NAS or th radius server?Thanks for any help. Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Duplicate Accounting Start Packets
Due to some network problems today, my FreeRadius 1.1.0 server and NAS (wrt54g+Chilli) had problems with duplicate accounting packets. The NAS sent a Start packet, the reply from the radius server did not reach and the NAS sent it again. I got up to 3 identical rows in the radacct table. To avoid this I changed the 'AcctUniqueId' column to UNIQUE and it seems to work fine. I just get an error message in my debug log and instead of an INSERT an UPDATE is done. Tue Mar 21 04:36:46 2006 : Debug: rlm_sql_mysql: query: INSERT into Tue Mar 21 04:36:46 2006 : Debug: rlm_sql_mysql: MYSQL check_error: 1062 received Tue Mar 21 04:36:46 2006 : Error: rlm_sql (sql): Couldn't insert SQL accounting START record - Duplicate entry 'af40ee210a7c0400' for key 2 Tue Mar 21 04:36:46 2006 : Debug: radius_xlat: 'UPDATE ... That is exactly what I need. If the Start packet is able to write 3 identical rows, all Interim-Updates are written to the 3 rows. That means my prepaid cards are running off the time three times faster. I also tried ... INSERT (.) ON DUPLICATE KEY UPDATE , but it seems not to be necessary. Are there any implications setting the 'AcctUniqueId' column to UNIQUE? Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: primary backup configuration
I setup MySQL one-way replication between the two MySQL servers. That means both DB's are always in sync, with the 2nd (and 3rd...) DB updated from the master. As soon as you add another row into the db only in the secondary db, the replication will stop updating the secondary db. I have two RADIUS Servers (with MySQL on the same server) at different locations, but only allow access (via firewall) to the primary one. If the primary server is down, I would have to open my firewall on the secondary server and the connected devices would use the up-to-date db. When the primary server is back online, the firewall must immediately disable any radius access. You can then setup the secondary DB as the master and primary as the slave, synchonizing all radius entries, switch radius back to the first one and set the replication back to the original state. Most of this could probably fit into a few shell scripts doing the job more automatically. I know that my failover is not automatically, but I have not really found a better way yet with MySQL. Maybe Postgresql has better features. The MySQL replication is so far (over 4 month) very reliable. If both of your MySQL servers are on the same LAN you could also set up 'MySQL Cluster', which would make failovers automatically and there would be no manual intervention required. But apparently you cannot do that with servers connected over the Internet as there is quite a network load. Gunther > -Original Message- > From: Maqbool Hashim > Sent: Friday, March 17, 2006 1:38 PM > I have two radius servers one primary and one backup one, on > different ip addresses. They both have a mysql backend which > runs on the same physical machine. I need the sql database > and radius configuration files to be synchronised > periodically (probably every 24hours). I guess this is a > common setup, so I'd appreciate some ideas as to the best way > to achieve this? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: incorrect radacct AcctSessionTime
Thanks for the hint ... !! The details file shows: Thu Mar 16 02:41:46 2006 Acct-Status-Type = Stop User-Name = "[EMAIL PROTECTED]" Calling-Station-Id = "00-0D-88-00-aa-aa" Called-Station-Id = "00-14-BF-20-ff-dd" NAS-Port-Type = Wireless-802.11 NAS-Port = 2 NAS-Port-Id = "0002" NAS-IP-Address = 0.0.0.0 ... (how come it is always 0.0.0.0?) NAS-Identifier = "myIdent" Framed-IP-Address = 192.168.182.251 Acct-Session-Id = "00150002" Acct-Input-Octets = 130 Acct-Output-Octets = 48 Acct-Input-Gigawords = 0 Acct-Output-Gigawords = 0 Acct-Input-Packets = 1 Acct-Output-Packets = 1 Acct-Session-Time = 1142462484 ... that looks like a timestamp !!! Acct-Terminate-Cause = Session-Timeout Client-IP-Address = My-IP Acct-Unique-Session-Id = "dd506e4d4bda70aa" Timestamp = 1142476906 Means it is from the Chillispot wrt54g nas! Not a FR problem at all! Gunther > -Original Message- >Peter Nixon wrote: > Sent: Thursday, March 16, 2006 3:11 AM > > On Tue 14 Mar 2006 08:51, Gunther wrote: > > I presume that the NAS (wrt54g with Chillispot) is sending the > > incorrect information ... > > Is this correct? > > Please check your detail files (If you have them enabled) to > confirm if the NAS is sending you this data or not. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: incorrect radacct AcctSessionTime
> Guy Fraser wrote > Sent: Wednesday, March 15, 2006 11:24 AM > > On Tue, 2006-14-03 at 15:16 -0500, Alan DeKok wrote: > > "Gunther" <[EMAIL PROTECTED]> wrote: > > > From time to time I see entries in the radacct AcctSessionTime > > > column with over 1 billion seconds, despite that the > StopTime minus > > > StartTime is less than 5 seconds. > > > With FR 1.0.5 it was a few times 2147483647: > > > > 2147483647 is 2^31-1. It looks like a signed/unsigned problem to > > me. > > I used to see Acct-Session-Time responses from USR Hyper > Cards like that every so often, the NAS was at fault and > required a reboot. > > We would then calculate the time : > Example, > Acct-Session-Time = (Acct-Stop-Time - Acct-Stop-Delay) - > (Acct-Start-Time + Acct-Start-Delay) > > We did it that way to give the customer the benefit of any > error possibly incurred by delays. How you do this in real > life will depend on what kind of DB you use to store the > accounting data. > I am using MySQL and I simply exclude these entries as the amount of data is usually below 200 bytes. The problem seem to come from Chillispot in connection with mac authentication (macallowed). We put the number of macallowed users down, using UAM instead and it seems to work. Had no entries of that kind for over 24 hours. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: incorrect radacct AcctSessionTime
> -Original Message- > Alan DeKok wrote: > Sent: Tuesday, March 14, 2006 4:17 PM > To: FreeRadius users mailing list > Subject: Re: incorrect radacct AcctSessionTime > > > From time to time I see entries in the radacct > AcctSessionTime column > > with over 1 billion seconds, despite that the StopTime > minus StartTime > > is less than 5 seconds. > > > I presume that the NAS (wrt54g with Chillispot) is sending the > > incorrect information ... > > Is this correct? > > Maybe. See the SQL queries. If the NAS is sending > Acct-Session-Time, that goes into the column. If it doesn't > send Acct-Session-Time, then the session time is calculated > based on the local system time, and other info. Yes, it is in the SQL queries and as far as I understand Chillispot is sending the Acct-Session-Time. It only seems to happen with MAC authenticated addresses (macallowed). Looks still like a Chillispot problem. But it could help to actually subtract AcctStopTime - AcctStartTime to get the AcctSessionTime. > > It looks to me like the clocks on your NAS and the RADIUS > server may be quite a ways off from each other. Not sure if I can change that at all. The wrt54G & Chillispot box does not always set the time correct. If there is no Internet connection at boottime to synchronize with a time server, it is using some very old time. But that is not the case here as the unit was on the 'right local time', while the server runs on GMT. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
incorrect radacct AcctSessionTime
Just one observation ... >From time to time I see entries in the radacct AcctSessionTime column with over 1 billion seconds, despite that the StopTime minus StartTime is less than 5 seconds. With FR 1.0.5 it was a few times 2147483647: AcctStartTime AcctStopTimeAcctSessionTime inOctets outOctets 2005-10-06 03:34:30 2005-10-06 03:34:34 2147483647 1069 4541 2005-11-09 00:13:39 2005-11-09 00:13:40 2147483647 128 124 2005-12-01 03:13:55 2005-12-01 03:13:58 2147483647 0 40 Now with 1.1.0 it is around 1142280970: 2006-03-14 00:16:29 2006-03-14 00:16:32 1142280970 100 152 2006-03-14 00:16:29 2006-03-14 00:16:32 1142280970 0 0 2006-03-14 00:16:29 2006-03-14 00:16:32 1142280969 0 0 2006-03-14 00:16:30 2006-03-14 00:16:32 1142280969 0 0 2006-03-14 03:26:54 2006-03-14 03:26:56 1142292393 0 0 While the number of transferred octes is usually less than 200 bytes. There are less than 15 users at the same time (GMT time) using the server. There is some heavy download activity before ... I presume that the NAS (wrt54g with Chillispot) is sending the incorrect information ... Is this correct? Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: freeradius newbie
Forgot one more thing:
You have to create the MySQL tables with the
db_mysql.sql file e.g. FC4:
/usr/share/doc/freeradius-1.0.4/db_mysql.sql
Or download the source code, install it
(./freeradius-1.1.0/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql).
Use phpMyAdmin and this file to populate the
tables.
From: GuntherSent: Friday, March
10, 2006 1:25 AMTo: 'FreeRadius users mailing
list'Subject: RE: freeradius newbie
With FC4 you can either use the existing RPM (yum
install ) or the new 1.1.0 FreeRadius source code and compile
it.
It actually compiles without any problems on FC4. The
installation path (make install) is a bit different from the RPM (/etc/raddb)
path as the binaries/libraries (I
guess) are installed in different directories (/usr/local/etc/raddb), but that
can be switch with a
./configure option
First install MySQL, FreeRadius, phpMyAdmin
...
You then edit the corresponding radiusd.conf file and
modify it for sql support.
The following examples might not be complete, but they
are direct cut and pastes from my files and it works fine.
radiusd.conf:
$INCLUDE ${confdir}/sql.confauthorize
{
...
# Look
in an SQL database. The schema of the
database # is meant to
mirror the "users" file.
# # See "Authorization
Queries" in sql.conf
sql}
accounting {
... #
Log traffic to an SQL database.
# # See "Accounting
queries" in sql.conf
sql}
session {
#
radutmp
# # See "Simultaneous Use
Checking Querie" in sql.conf
sql}
post-auth {
# After
authenticating the user, do another SQL
qeury.
# # See "Authentication
Logging Queries" in sql.conf
sql}
sql.conf:
server =
"localhost" login =
"root" password =
"rootpass"
To manage the MySQL DB you would use phpMyAdmin (www.phpmyadmin.net)
You can then
start FreeRadius with 'service radiusd
start'.
To get detailed
debug information you can edit the /etc/init.d/radiusd file and increase the
debug level:
daemon $RADIUSD -y - -d
$CONFIGD
The - is
the debug level. Debug information is written to STDout and you can redirect
it into a file
with e.g.
'service radiusd start >>
radDebug.log'
Hope this helps!
Gunther
From: Atkins, Dwane PSent:
Friday, March 10, 2006 12:41 AMTo:
freeradius-users@lists.freeradius.orgSubject: freeradius
newbie
Thank you for this mailing list. I am trying
to install Freeradius on a FC4 device. I am also trying to use the
MySql database function. I am having problems getting a good
install. So, as usual, I have a few questions for you
all.
Is there a good document that I can follow that
will give me step-by-step instructions to install freeradius on Fedora
4? I would even do FreeBSD if need be but I am more familiar with
the FC 4 processes.
Is there a GUI front-end that will allow us to
https into the FreeRadius Server and make changes to the mysql
database?
Where would I find the debug documents that I read
about when I execute the radtest executable?
Thanks for the help.
Dwane
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: freeradius newbie
With FC4 you can either use the existing RPM (yum
install ) or the new 1.1.0 FreeRadius source code and compile
it.
It actually compiles without any problems on FC4. The
installation path (make install) is a bit different from the RPM (/etc/raddb)
path as the binaries/libraries (I guess)
are installed in different directories (/usr/local/etc/raddb), but that can be
switch with a
./configure option
First install MySQL, FreeRadius, phpMyAdmin
...
You then edit the corresponding radiusd.conf file and
modify it for sql support.
The following examples might not be complete, but they are
direct cut and pastes from my files and it works fine.
radiusd.conf:
$INCLUDE ${confdir}/sql.confauthorize
{
...
# Look in
an SQL database. The schema of the
database # is meant to
mirror the "users" file.
# # See "Authorization
Queries" in sql.conf
sql}
accounting {
... #
Log traffic to an SQL database.
# # See "Accounting queries"
in sql.conf
sql}
session {
#
radutmp
# # See "Simultaneous Use
Checking Querie" in sql.conf
sql}
post-auth {
# After
authenticating the user, do another SQL
qeury.
# # See "Authentication
Logging Queries" in sql.conf
sql}
sql.conf:
server =
"localhost" login =
"root" password =
"rootpass"
To
manage the MySQL DB you would use phpMyAdmin (www.phpmyadmin.net)
You can then
start FreeRadius with 'service radiusd start'.
To get detailed
debug information you can edit the /etc/init.d/radiusd file and increase the
debug level:
daemon $RADIUSD -y - -d
$CONFIGD
The - is the
debug level. Debug information is written to STDout and you can redirect it into
a file
with e.g.
'service radiusd start >> radDebug.log'
Hope this helps!
Gunther
From: Atkins, Dwane PSent: Friday,
March 10, 2006 12:41 AMTo:
freeradius-users@lists.freeradius.orgSubject: freeradius
newbie
Thank you for this mailing list. I am trying to
install Freeradius on a FC4 device. I am also trying to use the MySql
database function. I am having problems getting a good install.
So, as usual, I have a few questions for you all.
Is there a good document that I can follow that will
give me step-by-step instructions to install freeradius on Fedora 4? I
would even do FreeBSD if need be but I am more familiar with the FC 4
processes.
Is there a GUI front-end that will allow us to https
into the FreeRadius Server and make changes to the mysql
database?
Where would I find the debug documents that I read
about when I execute the radtest executable?
Thanks for the help.
Dwane
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Long mysql queries in sqlcounter get truncated.
Had the same trouble with long SQL queries. It seems the RADIUS standard only allows up to 253 characters or so. I ended up modifying my table column names to a shorter name. Not nice, but it works. I also 'merged' some tables information to shorten the queries. Initially I changed the max string length within the code, but the main disadvantage is that every upgrade you have to do the same. Now I use 100% unmodified FreeRadius code and the upgrade to 1.1.0 went without any problems. Gunther > -Original Message- > From: > [EMAIL PROTECTED] > ius.org > [mailto:[EMAIL PROTECTED] > .freeradius.org] On Behalf Of Luis Zarrabeitia > Sent: Wednesday, March 01, 2006 9:52 PM > To: freeradius-users@lists.freeradius.org > Subject: Long mysql queries in sqlcounter get truncated. > > > Hi. I'm trying to configure a freeradius 1.1.0 [Debian > testing] server with mysql backend, and I'm having trouble > with the "sql counter" module. When trying to customize the > query to obtain the remaining time (the default query does > not reflect my enviroment), I find mysql syntax errors. > Apparently, it is not processing the full query. > > Output of "freeradius -X". Note that I changed the actual > query for brevity, as anything [valid] that I put in there > seem to get me the same result. > > --- > rlm_sql_mysql: query: SELECT > AND UNIX_TIMESTAMP(AcctStartT > rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql > (sql): database query error, SELECT AND > UNIX_TIMESTAMP(AcctStartT: You have an error in your SQL > syntax; check the manual that corresponds to your MySQL > server version for the right syntax to use near '' at line 1 > rlm_sql (sql): Released sql socket id: 2 > radius_xlat: 'ime)+AcctSessionTime>'1138766400'' > - > > (the original query on my radiusd.conf file is: > SELECT AND > UNIX_TIMESTAMP(AcctStartTime)+AcctSessionTime>'%b') > > Notice how the first line in the log is truncated to the "T" > of "AcctStartT", that is, the first 251 characters of the > query after applying the parameter expansion (%b, %k). (the > length is always 251, regardless of the query - if I strip > spaces from it I can fit more, but it is still not enough). > Also notice how the end of the query is continued on the next > "radius_xlat:" line. > > The query works perfectly with the command line mysql client > (after %b and %k expansions). > > Glancing at the source I see a line "#define MAX_QUERY_LEN > 1024" in rlm_sqlcounter.c, far greater than 251, wich makes > me doubt that the problem lies in that size, and seems to > confirm my suspicion that it might be a configuration > parameter that I'm missing somewhere. > > Can you suggest either a solution or a workaround? (other > than storing the query in a mysql5.0 'stored procedure', > because I do not wish to add Debian Unstable to my production servers) > > Regards, > > Zarrabeitia > - > List info/subscribe/unsubscribe? See > http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: CentOS
I am using CentOS 4 with FreeRadius 1.0.5 and I just upgraded to 1.1.0 and it works fine so far. I did not use RPM's, but instead did a fresh compile. Pretty straight forward and no errors. Gunther -Original Message- From: Italo Morellato Sent: Friday, February 24, 2006 6:22 AM To: freeradius-users@lists.freeradius.org Subject: CentOS Freeradius 1.1.0 RPM for CentOS 4.2 (smeserver) is possible? Thanks in advance. Italo Morellato - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: NAS table
Yes, it is working fine, at least in freeradius 1.0.5. Read my comments here: http://lists.freeradius.org/mailman/htdig/freeradius-users/2005-October/047765.html Unfortunately every change in the nas_table requires a restart of the freeradius server. Would be nice to have something like a reload or so, or even an auto reload after the radius server did an insert or update. Gunther From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Santiago Balaguer GarcíaSent: Monday, January 23, 2006 4:47 AMTo: freeradius-users@lists.freeradius.orgSubject: NAS table Hi people, I am using freeradius as authentication service for two years. I use freeradius 1.0.4 in a Debian servers. My quiestion is I use clients.conf file for mu nas clients, however I read in the freeradius doc that this file can be supported in an database ( it is very useful for me because I have an administration web for control my radius accounts). I detect that I put 'readclients=yes ' in my postgres.conf file perhaps it works, but it is not works. So, What do I have to write in order to have all nas information in my database? Thanks, Santiago Éxitos, grandes clásicos y novedades. Un millón de canciones en MSN Music. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply message from the sqlcounter module
Just added the sqlcounter to my FreeRadius configuration.
Stumbled over the "Your maximum never usage time has been reached" reply
message
for my noresetcounter (refer to doc/rlm_sqlcounter).
Changed it to: "Your maximum access time has been reached" for the 'never'
case.
Here my Q&D solution:
Change in freeradius-1.0.5/src/modules/rlm_sqlcounter/rlm_sqlcounter.c
diff rlm_sqlcounter.c rlm_sqlcounter.c.ORIG
668,672c668
< if (strcmp(data->reset, "never") == 0) {
< snprintf(msg, sizeof(msg), "Your maximum access time
has been reached");
< } else {
< snprintf(msg, sizeof(msg), "Your maximum %s usage
time has been reached", data->reset);
< }
---
> snprintf(msg, sizeof(msg), "Your maximum %s usage time has
been reached", data->reset);
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Authenticate users for a hotel through webpage?
mfred wrote: > maybe someone can point me to the right direction as it seems I use > wrong keywords for my search in the list and on google. > I am trying to use freeradius with wlan to authenticate hotel guests > that need i-net access. > > They would prefer that the customer could visit a webpage entering the > username / password combination they got at hotel-reception. Would > this be a authentication methode which freeradius can handle or do I > have to use other authentication modules for such a purpose? > > The most important thing is: Easy to use for the hotel staff at > registering / deleting guests and easy to use for the guests at login. > > TIA and br, > > mfred Hi, we build a complete solution for Internet access for hotel guests, marinas etc. using FreeRadius. With a web-based control panel, different staff permission levels, custom defined access packages etc. No server at the hotel required, we operate them centrally on the Internet ... www.pointHotspot.com Individual hotel login page pops up when guest/user goes to any website, they type in username and password and can then use the Internet as long as they are allowed. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: NAS list and dynamic IP
Alan DeKok wrote: >> How can I use a unique secret for each NAS connected to the same ISP? > > You can't. There's no real way to tell them apart. > > Alan DeKok. Thanks Alan! I presume it is a radius protocol issue. Maybe good for a future enhancement with some form of additional ID coming from the NAS and not just using the IP address. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
NAS list and dynamic IP
I am running FR 1.0.5 using MySQL including the nas list in a table. So far I have to use the same secret for a number of different NAS units which are connected to the Internet via a dynamic IP. My FR server is on the Internet and has to accept connections from the various NAS units, which can be connected with a variety of different IPs. One ISP has several IP ranges and all of these addresses have to be recognised. So far I am using a masked IP address as nas identifier (e.g. 123.123.0.0/16). How can I use a unique secret for each NAS connected to the same ISP? This is the fixed position format of the nas list in sql (rlm_sql.c): /* * Format: * Row1 Row2Row3Row4Row5Row6Row7Row8 * * id nasname shortname typeports secret community description * */ >From what I understand and experienced, the nasname must be either a DNS resolveable name or an IP address or IP range with netmask. Since my nas clients are on dynamic IP addresses and the nas list will only be read during the radius startup, I am using IP addresses with netmasks. Startup: Sun Dec 12 13:03:44 2005 : Debug: rlm_sql_mysql: query: SELECT * FROM phs_nas Sun Dec 12 13:03:44 2005 : Debug: rlm_sql (sql): Read entry nasname=123.123.0.0/16,shortname=MyShortName,secret=mySecretHere Sun Dec 12 13:03:44 2005 : Debug: rlm_sql (sql): Adding client 123.123.0.0/16 (MyShortName) to clients list ... read nas next entry Is there another way of handling dynamic NAS IP addresses and unique nas secrets? Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Wiki is now live
Alan DeKok wrote: > > http://wiki.freeradius.org/ > > Please feel free to add documentation, configuration examples, etc. > Right now it's pretty minimal and free-form. > > Thanks to Peter Nixon for setting it up and hosting it. > Cool !! Applied already some minor modification(s). - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: tool for testing machine authentication
Johan Ramm-Ericson wrote: >Hi, >having just recently succesfully setup freeradius and being somewhat >frustrated with the documentation, I >felt there may be someway I could contribute to improve it. A while back there was a thread on the mailinglist to the effect of setting up a Wiki. Has this seen any progression? If not, I'll be glad to put >in some effort to get this done. >Also, I'm willing to pitch in on writing the documentation, however my freeradius experience is so recent >that I'd probably only be able to do any good with well-defined tasks... I set up an empty wiki a few weeks ago with the intention to start a FreeRadius wiki. http://s92562228.onlinehome.us/wiki ... it is still empty ... Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: tool for testing machine authentication
Robin Mordasiewicz wrote: >i just did a google on NTRadTest, but found nothing. Where can I download NTRadTest Try NTRadPing at: http://www.mastersoft-group.com/download/ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FW: Attribute Value length using SQL
Alan DeKok wrote:
>"Gunther" <[EMAIL PROTECTED]> wrote:
>> My remaining trouble was the conditional expression
>> %{expr: SQL1}:-%{expr:SQL2}.
>
> I don't think that's the correct syntax.
Yes, I realised that ... I skipped this approach and I now use
post-auth sql module for updating my initial login time in radreply.
But even my current SQL query in radgroupreply was still 40 bytes over the
'edge' of 253.
As mentioned, when I change MAX_STRING_LEN to a higher value and modify a
few (2) static
'256' values, it is working fine in my test environment.
My next approach .. I am cutting down on sql column name lengths to fit it
into the 253 limit.
Not nice, but it works. ... e.g. (uuname=user_username)
Here is my hopefully final sql statement for Session-Timeout in
radroupreply:
(for 30 minute access from 1st login)
`%{expr:%{sql:SELECT
IF(NOW()>usttm,IF(ulsttm=0,1800,IF(TIME_TO_SEC(TIMEDIFF(DATE_ADD(ulsttm,INTE
RVAL 30 MINUTE),NOW()))>0,TIME_TO_SEC(TIMEDIFF(DATE_ADD(ulsttm,INTERVAL 30
MINUTE),NOW())),1)),1) as result FROM phs_user WHERE uuname='%{User-Name}'}`
When user logs in first time, the full 30min (1800sec) are assigned.
The next log in will find a timestamp in ulsttm from the first login and
calculates the
remaining time from that timestamp. If it is expired, it returns 1 second
(because when I assign 0,
unlimited access time is the result).
I added another column in radreply (I call the table phs_user) named ulstrt
(or user_loginstart).
user_loginstart is getting a timestamp from post-auth when a user initially
logs in.
My post-auth query in sql.conf is:
postauth_query = "UPDATE ${authcheck_table} SET ulsttm = NOW() WHERE
uuname='%{User-Name}' AND ulsttm=0"
Why all this stuff ... I like to start the timer for a 30min, 60min etc.
login from the time
they log in and not from the time I assign the user start time.
... 2+ days of FR research ... and it works ;-) with an unmodified
FreeRadius 1.0.5
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
FW: Attribute Value length using SQL
Alan DeKok wrote:
>"Gunther" <[EMAIL PROTECTED]> wrote:
>> Thanks! Yes, I see it in rfc2138 (0-253 octets) ... But isn't the
>> feature I am talking about more part of the expression module and
>> enhanced functionality of FreeRadius?
>
> Yes, but the data structures used by the code are still based on
> RADIUS ones.
>
> If you want to use longer strings, rlm_policy in the CVS snapshot may
help.
>
> Alan DeKok.
Will try that one!
I was actually successful with changing MAX_STRING_LEN and changing one
fixed [256] value in main/xlat.c (char attrname[256];). The long SQL strings
were recognised and functioning.
My remaining trouble was the conditional expression
%{expr: SQL1}:-%{expr:SQL2}. Whenever SQL1 does not return anything,
SQL2 will not be executed ... I guess I am not using the right syntax
or I am running into a limit ...
I presume that I have to use a complete CVS snapshot in order to use
rlm_policy?
Thanks,
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Attribute Value length using SQL
>I actually changed the MAX_STRING_LEN size from 254 to 762 in include/libradius.h and it seems to work. No, it doesn't work ... - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Attribute Value length using SQL
Alan DeKok wrote:
>"Gunther" <[EMAIL PROTECTED]> wrote:
>> I was trying to change the AV lenght from 253 to 1024, but radius is
>> simply cutting off after the limit, resulting in an error in the log
>> file (MYSQL check_error: 1054).
>
> RADIUS has a maximum attribute length of 253. You CAN NOT change it.
>
> Alan DeKok.
Thanks! Yes, I see it in rfc2138 (0-253 octets) ... But isn't the feature
I am talking about more part of the expression module and enhanced
functionality of FreeRadius?
I was reading in the Changelog:
* Preliminary 'expression' module, to allow you to do cool things
like:Session-Timeout = `%{expr:3600 - %{sql:SELECT ...}}`
I saw that and started using it ... more and more.
The result of the SQL statement is not supposed to be longer than 253, but
the
actual statement could be much longer.
I actually changed the MAX_STRING_LEN size from 254 to 762 in
include/libradius.h
and it seems to work. Have to do more testing. But I am not really fond of
the
fact that I change the max string length for everything in FreeRadius. I
would prefer
to have just some extended space for sql statements for users who are using
that
feature. But if you tell me that is not the way to go ... I would need to
look
for another way. These SQL statements are just very convenient.
Thanks!
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Attribute Value length using SQL
I am running FR 1.0.5 with MySQL 4.1.12. All data is stored in MySQL.
My radgroupreply attribute values are SQL statements. It works very well.
But it seems I have reached the limit for the Value column.
My SQL output is setting the Session-Timeout.
The length of Attribute Value can be found in the
src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql
CREATE TABLE radgroupreply (
id int(11) unsigned NOT NULL auto_increment,
GroupName varchar(64) NOT NULL default '',
Attribute varchar(32) NOT NULL default '',
op char(2) NOT NULL DEFAULT '=',
Value varchar(253) NOT NULL default '', <==
prio int unsigned NOT NULL default '0',
PRIMARY KEY (id),
KEY GroupName (GroupName(32))
) ;
I am trying to use a combination of the conditional syntax (e.g.
%{Foo:-%{Bar}}) in
connection with SQL statements. One sql statement is doing that and if false
another sql
statement is doing something else.
e.g
`%{expr: %{sql:SELECT IF(NOW() > user_starttime,
IF(DATE_ADD(radacct_starttime, INTERVAL 30 MINUTE) > NOW(), ... and so on
I was trying to change the AV lenght from 253 to 1024, but radius is simply
cutting off after
the limit, resulting in an error in the log file (MYSQL check_error: 1054).
Log:
radius_xlat: Running registered xlat function of module expr for string
'%{sql:SELECT IF(NOW()>user_starttime,IF(DATE_ADD(radacct_starttime,INTERVAL
30 MINUTE)>NOW(),TIME_TO_SEC(TIMEDIFF(DATE_ADD(radacct_starttime, INTERVAL
30 MINUTE),NOW())),1),1) FROM phs_user,phs_radacct WHERE
user_username='%{User-Name}' AND radac'
radius_xlat: Running registered xlat function of module sql for string
'SELECT IF(NOW()>user_starttime,IF(DATE_ADD(radacct_starttime,INTERVAL 30
MINUTE)>NOW(),TIME_TO_SEC(TIMEDIFF(DATE_ADD(radacct_starttime, INTERVAL 30
MINUTE),NOW())),1),1) FROM phs_user,phs_radacct WHERE
user_username='%{User-Name}' AND radac'
rlm_sql (sql): - sql_xlat
radius_xlat: '[EMAIL PROTECTED]'
rlm_sql (sql): sql_set_user escaped user --> '[EMAIL PROTECTED]'
radius_xlat: 'SELECT
IF(NOW()>user_starttime,IF(DATE_ADD(radacct_starttime,INTERVAL 30
MINUTE)>NOW(),TIME_TO_SEC(TIMEDIFF(DATE_ADD(radacct_starttime, INTERVAL 30
MINUTE),NOW())),1),1) FROM phs_user,phs_radacct WHERE
user_username='[EMAIL PROTECTED]' AND radac'
radius_xlat: '/usr/local/var/log/radius/sqltrace.sql'
rlm_sql (sql): Reserving sql socket id: 2
rlm_sql_mysql: query: SELECT
IF(NOW()>user_starttime,IF(DATE_ADD(radacct_starttime,INTERVAL 30
MINUTE)>NOW(),TIME_TO_SEC(TIMEDIFF(DATE_ADD(radacct_starttime, INTERVAL 30
MINUTE),NOW())),1),1) FROM phs_user,phs_radacct WHERE
user_username='[EMAIL PROTECTED]' AND radac
rlm_sql_mysql: MYSQL check_error: 1054 received
I then tried to shorten tablenames etc., but even with that I still have
about 500+ bytes left.
Is there any reason the limit must be at 253?
Can the limit be changed ... in an easy way?
My complete Sessiont-Timeout Value:
`%{expr: %{sql:SELECT IF(NOW() > user_starttime,
IF(DATE_ADD(radacct_starttime, INTERVAL 30 MINUTE) > NOW(),
TIME_TO_SEC(TIMEDIFF(DATE_ADD(radacct_starttime, INTERVAL 30 MINUTE),
NOW())), 1), 1) FROM phs_user, phs_radacct WHERE
user_username='%{User-Name}' AND radacct_username=user_username AND
radacct_starttime >= user_starttime} :-%{expr: %{sql:SELECT IF(NOW() >
user_starttime, IF(DATE_ADD(user_starttime, INTERVAL 24 HOUR) > NOW(),
TIME_TO_SEC(TIMEDIFF(DATE_ADD(NOW(), INTERVAL 30 MINUTE), NOW())), 1), 1)
FROM phs_user WHERE user_username='%{User-Name}'}}`
Thanks!
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Timezone support
I'm using FreeRadius 1.0.5 with MySQL 4.1.10. I am looking for a solution for timezone support over several timezones. There are probably several ways of doing this: 1. Set server with FreeRadius to the particular fixed timezone e.g. GMT 2. Set MySQL to a fixed timezone (my.cnf timezone= ...) 3. others ... Where are timezones used: - System - MySQL - PHP Application - FreeRadius I do not like to change the server timezone as other applications are using the same server. Same thing for setting MySQL to a fixed timezone, which is not my normal timezone, as other applications are using other DBs on the MySQL server. I thought I can use the MySQL session specific timezone settings, but I do not see anyhwere a place where I can set that in FreeRadius. I can set everything in PHP, but when it comes to radacct updates from a NAS ... I only have the MySQL specific timezone. It looks like that FreeRadius is always using the System specific time and does not care about the MySQL timezone settings. My goal: I like to have all radacct date&time in e.g. GMT and then convert in my application to the NAS specific timezone. Right now I can only achieve this by setting the MySQL timezone to GMT and ALL DB's are then in that timezone. Or I run a separate instance of MySQL with different port to do this? Any thoughts? Is there maybe a variable in FreeRadius telling to store time information in another timezone? Do I have to switch from MySQL tp Postgresql ... there are probably many people using FreeRadius with NAS' spreaded over several timezones. Thx! - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Simultaneous-Use check MySQL
On Oct 14, 2005, at 2:35 PM, Alan DeKok wrote: My doubt/question is: checkrad is always used to Simultaneous-Use checking? Is it not possible to use radacct table instead of SNMP polling? Yes. Set the "nastype" to "other". And when the system misses an accounting stop packet, the user won't be able to log in again. Alan DeKok. I use a timestamp column in my radacct table instead of checkrad and SNMP. So far it works fine. If last packet older than e.g. 15 min (3-5.. times Interval-time) ... the session is most probably lost. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: client configuration via postgres in version 1.0.1
Yes, it is possible, at least in 1.0.5. My nas list is in MySQL. The DB
table columns are in a fixed sequence and referred by
position and not by name. The nas identifier must be either a DNS
resolvable name or IP address or
IP Address/netmask, e.g. 200.100.100.0/255.255.255.0.
I only have 'client 127.0.0.1' in my clients.conf file.
Unfortunately a change in the SQL nas table will only be active after
restarting freeradius and is not dynamically
after adding/updating the nas table.
You can assign the name of the NAS table in sql.conf (if you chose not
to use the default name).
e.g. nas_table = "my_nas" and you have to set:
# Set to 'yes' to read radius clients from the database ('nas' table)
readclients = yes
Debug output:
Debug: rlm_sql (sql): - generate_sql_clients
Debug: rlm_sql (sql): Query: SELECT * FROM nas
Debug: rlm_sql (sql): Reserving sql socket id: 4
Debug: rlm_sql_mysql: query: SELECT * FROM nas
Debug: rlm_sql (sql): Read entry
nasname=my-resolvable-name-or-IP,shortname=moin,secret=whatever
Debug: rlm_sql (sql): Adding client 192.168.0.244 (moin) to clients list
Gunther
On Oct 13, 2005, at 2:13 PM, Joel Bjerk wrote:
Is it possible to use the nas table for client information instead of
clients.conf? I tried uncommenting readclients=yes in sql.conf but the
server read the clients from clients.conf.
Thanks,
Joel
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Online Status with FR & SQL + Simultaneous-Use
For those interested, here the solution I am using now:
I am using FR 1.0.5 with MySQL 4.1.10a, PHP and no flat files, except for
the DEFAULT values
and the standard configuration files radiusd.conf, sql.conf (my NAS list is
also
in a SQL table). I also changed most of the SQL column names, by adding a
prefix.
For the examples I changed it back to the original names.
How to get user online status:
==
1. Add the TIMESTAMP column to the radacct table
`radacct_mdate` timestamp NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP
2. To get the online status set up a query something like this (using PHP):
define('RAD_ONLINE_TIMEOUT', 15); // 15 min since last timestamp from NAS,
assuming connection dead
$query = "SELECT COUNT(*) FROM radacct WHERE UserName = ' . $username . '
AND AcctStopTime=0 AND DATE_SUB(NOW(),INTERVAL ' . RAD_ONLINE_TIMEOUT .'
MINUTE) <= radacct_mdate ORDER BY AcctStartTime DESC LIMIT 1)";
My actual query is a bit more complicated as I verify against a user table
if they are
actually allowed to be online ( Starttime < ActualTime < Stoptime)
How to get Simultaneous-Use with FR & MySQL working:
1. I setup my defaults in the raddb/users file (at the very end and nothing
else beside localhost)
DEFAULT Simultaneous-Use := 1, Auth-Type := SQL (this seems to be the
only way)
Idle-Timeout = 3600,
Acct-Interim-Interval = 180
Note: You can override the default Simultaneous-Use attribute for a user or
a group by setting it
in radcheck or radgroupcheck table (groupname Simultaneous-Use := 3).
2. In raddb/radiusd.conf
Instead of using radutmp, I am using SQL:
accounting {
.
.
sql
}
session {
# See "Simultaneous Use Checking Querie" in sql.conf
sql
}
3. In raddb/sql.conf
simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE
UserName='%{SQL-User-Name}' AND AcctStopTime=0 AND CallingStationId <>
'%{Calling-Station-Id}' AND DATE_SUB(NOW(),INTERVAL 15 MINUTE) <=
radacct_mdate"
simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol
FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0
AND CallingStationId <> '%{Calling-Station-Id}' AND DATE_SUB(NOW(),INTERVAL
15 MINUTE) <= radacct_mdate"
Not sure that this is all 100% like the developers intended it to be, but it
works.
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Online Status with FR & SQL
Alan DeKok wrote: >"Gunther" wrote: >> how do I know that the AcctStopTime=0 is not a 'leftover' ... >> Would it make sense to add a TIMESTAMP to the radacct table to record >> the last update? > > Sure. If the NAS is supposed to send accounting updates every 15 minutes, and it's been an hour since the last >one, you can guess that the user isn't logged in any more. > >> And would the use of TIMESTAMP for the radacct table produce some form >> of performance degrade? > > I doubt it. > > Alan DeKok. Thanks! It works very well! Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Online Status with FR & SQL
I am trying to minimize the failure rate for detecting a user online status using FR105 & MySQL411. Sometimes the AcctStopTime in the radacct table remains 0 even the user is not anymore online for whatever reason (reboot, connection lost or ...). I can therefore not just check if the AcctStopTime for a particular user is 0. Since there is no record when the NAS unit sent the last update to FR and it is not recorded in the radacct table ... how do I know that the AcctStopTime=0 is not a 'leftover' ... Would it make sense to add a TIMESTAMP to the radacct table to record the last update? And would the use of TIMESTAMP for the radacct table produce some form of performance degrade? A TIMESTAMP would allow me to see if the row was updated within Idle-Timeout. Any hints from experience? Thanks, Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Which Operating System is best for freeRADIUS
Nicolas Baradakis wrote: >I was talking about the user point of view: the users are assured that FreeRADIUS is regulary tested under Debian, >and the Debian package is up-to-date. Well, I tried CentOS (Redhat EL4) on a VPS server and with a few problems (missing libraries, rpm's) I got FR105 compiled. FR is up and running and now I have to find a way to pass the firewall ... Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Online Status & SQL
Hi, What is the second easiest way to know who is online beside radwho when using FR & MySQL. The radacct table seems to be the only place. Will it be sufficient just testing if AcctStopTime is 0? I saw some 'old' sessions in the table, which have AcctStopTime = 0 and no terminate cause. These 'zombies' would have to be cleaned by a watchdog ... (Note: Zombies were left under FR 1.0.4, now I'm on 1.0.5 and no zombies so far). Am I on the right track here? Thanks! - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Which Operating System is best for freeRADIUS
Nicolas Baradakis wrote: >I'd suggest Debian, because several members of the project are developping or testing FreeRADIUS under Debian. >Moreover the Debian package is directly maintained by one the developpers who regularly adds the major bugfixes >into the Debian package between two releases of FreeRADIUS. Thanks! Not too familiar with Debian, but I don't think it is a different world to all the other Linux distributions. I thought FreeBSD might be a candidate since it is more focusing on networking and services. I run several web hosting packages with FreeBSD, Fedora FC4, Redhat 9, SuSE ... I was actually more looking from the user point of view and not the developers. (sorry for that ;-) Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Which Operating System is best for freeRADIUS
Building my FR server, I have the choice of a number of operating system for my FreeRADIUS server. Anybody with a suggestion which operating system is best suited for FR? I like to run FR on a VPS (virtual private server) using one of the following OS: - FreeBSD 4.9 (jail) - FreeBSD 5.2 (jail) - Fedora 2 (virtuozza) - Redhat AS3 (virtuozza) - Redhat 9.0 (virtuozza) - CentOS 4.0 (virtuozza) Thanks! Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: expr in SQL not working
Thanks Alan and Nicolas, Compiled again 1.0.5 for Fedora FC4 ... and so far it is working. I can confirm that the sql+expr problem is fixed with 1.0.5! Thanks a lot! And now the next step on my todo list ... Gunther Nicolas Baradakis wrote: >Alan DeKok wrote: > >> "Gunther" <[EMAIL PROTECTED]> wrote: >> > Tue Oct 4 00:53:46 2005 : Debug: rlm_sql_mysql: query: SELECT >> > phs_radgroupreply.radgroupreply_id,phs_radgroupreply.radgroupreply_g >> > roupname >> > ,phs_radgroupreply.radgroupreply_attribute,phs_radgroupreply.radgrou >> > preply_v alue,phs_radgroupreply.radgroupreply_op FROM >> > phs_radgroupreply,phs_usergroup WHERE >> > phs_usergroup.usergroup_username = 'gunther' AND >> > phs_usergroup.usergroup_groupname = >> > phs_radgroupreply.radgroupreply_groupname ORDER BY >> > phs_radgroupreply.radgroupreply_id >> > Tue Oct 4 00:53:46 2005 : Debug: radius_xlat: '=' >> >> That last line doesn't look right. > >Yes, it looks like bug #242. (and #245) >http://bugs.freeradius.org/show_bug.cgi?id=242 >http://bugs.freeradius.org/show_bug.cgi?id=245 > >The problem should be fixed in 1.0.5. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: expr in SQL not working
Hi Alan,
the following is a packet which is not recognizeing the expr value in
rad_reply or rad_groupreply:
radreply:
5 gunther Session-Timeout = `%{expr: 3600 -
400}`
Should be 3200 seconds ... but results into 0 seconds.
--
rad_recv: Access-Request packet from host 192.168.0.254:2055, id=0,
length=227
User-Name = "gunther"
CHAP-Challenge = 0x26222c6c476b3be21958dc1ddc0ad3db
CHAP-Password = 0x00a4b8f5b9ba96eb52251722039e1091e2
NAS-IP-Address = 0.0.0.0
Service-Type = Login-User
Framed-IP-Address = 192.168.182.4
Calling-Station-Id = "00-0D-93-88-5C-B9"
Called-Station-Id = "00-13-10-94-A9-14"
NAS-Identifier = "[EMAIL PROTECTED]"
Acct-Session-Id = "4341d27f"
NAS-Port-Type = Wireless-802.11
NAS-Port = 0
Message-Authenticator = 0x94c26b45aefa054e6ab79d4d6bc26562
WISPr-Logoff-URL = "http://192.168.182.1:3990/logoff";
Tue Oct 4 00:53:46 2005 : Debug: Processing the authorize section of
radiusd.conf
Tue Oct 4 00:53:46 2005 : Debug: modcall: entering group authorize for
request 1
Tue Oct 4 00:53:46 2005 : Debug: modsingle[authorize]: calling preprocess
(rlm_preprocess) for request 1
Tue Oct 4 00:53:46 2005 : Debug: modsingle[authorize]: returned from
preprocess (rlm_preprocess) for request 1
Tue Oct 4 00:53:46 2005 : Debug: modcall[authorize]: module "preprocess"
returns ok for request 1
Tue Oct 4 00:53:46 2005 : Debug: modsingle[authorize]: calling suffix
(rlm_realm) for request 1
Tue Oct 4 00:53:46 2005 : Debug: rlm_realm: No '@' in User-Name =
"gunther", looking up realm NULL
Tue Oct 4 00:53:46 2005 : Debug: rlm_realm: No such realm "NULL"
Tue Oct 4 00:53:46 2005 : Debug: modsingle[authorize]: returned from
suffix (rlm_realm) for request 1
Tue Oct 4 00:53:46 2005 : Debug: modcall[authorize]: module "suffix"
returns noop for request 1
Tue Oct 4 00:53:46 2005 : Debug: modsingle[authorize]: calling sql
(rlm_sql) for request 1
Tue Oct 4 00:53:46 2005 : Debug: radius_xlat: 'gunther'
Tue Oct 4 00:53:46 2005 : Debug: rlm_sql (sql): sql_set_user escaped user
--> 'gunther'
Tue Oct 4 00:53:46 2005 : Debug: radius_xlat: 'SELECT
user_id,user_username,user_attribute,user_value,user_op FROM phs_user WHERE
user_username = 'gunther' ORDER BY user_id'
Tue Oct 4 00:53:46 2005 : Debug: rlm_sql (sql): Reserving sql socket id: 2
Tue Oct 4 00:53:46 2005 : Debug: rlm_sql_mysql: query: SELECT
user_id,user_username,user_attribute,user_value,user_op FROM phs_user WHERE
user_username = 'gunther' ORDER BY user_id
Tue Oct 4 00:53:46 2005 : Debug: radius_xlat: 'SELECT
phs_radgroupcheck.radgroupcheck_id,phs_radgroupcheck.radgroupcheck_groupname
,phs_radgroupcheck.radgroupcheck_attribute,phs_radgroupcheck.radgroupcheck_v
alue,phs_radgroupcheck.radgroupcheck_op FROM
phs_radgroupcheck,phs_usergroup WHERE phs_usergroup.usergroup_username =
'gunther' AND phs_usergroup.usergroup_groupname =
phs_radgroupcheck.radgroupcheck_groupname ORDER BY
phs_radgroupcheck.radgroupcheck_id'
Tue Oct 4 00:53:46 2005 : Debug: rlm_sql_mysql: query: SELECT
phs_radgroupcheck.radgroupcheck_id,phs_radgroupcheck.radgroupcheck_groupname
,phs_radgroupcheck.radgroupcheck_attribute,phs_radgroupcheck.radgroupcheck_v
alue,phs_radgroupcheck.radgroupcheck_op FROM
phs_radgroupcheck,phs_usergroup WHERE phs_usergroup.usergroup_username =
'gunther' AND phs_usergroup.usergroup_groupname =
phs_radgroupcheck.radgroupcheck_groupname ORDER BY
phs_radgroupcheck.radgroupcheck_id
Tue Oct 4 00:53:46 2005 : Debug: radius_xlat: 'SELECT
radreply_id,radreply_username,radreply_attribute,radreply_value,radreply_op
FROM phs_radreply WHERE radreply_username = 'gunther' ORDER BY radreply_id'
Tue Oct 4 00:53:46 2005 : Debug: rlm_sql_mysql: query: SELECT
radreply_id,radreply_username,radreply_attribute,radreply_value,radreply_op
FROM phs_radreply WHERE radreply_username = 'gunther' ORDER BY radreply_id
Tue Oct 4 00:53:46 2005 : Debug: radius_xlat: 'SELECT
phs_radgroupreply.radgroupreply_id,phs_radgroupreply.radgroupreply_groupname
,phs_radgroupreply.radgroupreply_attribute,phs_radgroupreply.radgroupreply_v
alue,phs_radgroupreply.radgroupreply_op FROM
phs_radgroupreply,phs_usergroup WHERE phs_usergroup.usergroup_username =
'gunther' AND phs_usergroup.usergroup_groupname =
phs_radgroupreply.radgroupreply_groupname ORDER BY
phs_radgroupreply.radgroupreply_id'
Tue Oct 4 00:53:46 2005 : Debug: rlm_sql_mysql: query: SELECT
phs_radgroupreply.radgroupreply_id,phs_radgroupreply.radgroupreply_groupname
,phs_radgroupreply.radgroupreply_attribute,phs_radgroupreply.radgroupreply_v
alue,phs_radgroupreply
RE: expr in SQL not working
Gunther wrote:
>I then compiled and installed 1.0.5 ... with the same result.
One correction: Yes, I installed 1.0.5, but I did not update the
startup script and therefore I was still using 1.0.4.
When I tried to use 1.0.5, the first request caused a segmentation fault.
Probably has to be configured with other compiler parameters under Fedora
FC4.
Anyhow, if I can get within the rad_reply table e.g. `%{expr:10+100)`
working
to set my Session-Timeout ... I would be quite happy.
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
expr in SQL no working
I am running Fedora FC4 with FR 1.0.4 (yum updated) with MySQL 4.1.12.
I am trying to use in rad_reply table for Session-Timeout with the following
value:
`%{expr: %{sql:SELECT IF(NOW() <= user_stoptime,
(TIME_TO_SEC(TIMEDIFF(user_stoptime, IF(NOW() >= user_starttime, now(),
DATE_SUB(user_stoptime, INTERVAL 1 SECOND), 1) FROM phs_user WHERE
user_username='%{User-Name}'}}`
This doesn't work as the Session-Timeout value is not assigned.
The sql statement alone works fine and returns the correct value.
I then tried to get a simple expression up and running in rad_reply:
AttrOp Value
Session-Timeout = `%{expr:3600 - 300}`
No success.
In debug mode I can see that the expr module is loaded:
Module: Loaded expr
Module: Instantiated expr (expr)
I then compiled and installed 1.0.5 ... with the same result.
Then I created a user in the ./raddb/users file and assigned the
Session-Timeout with above SQL statement
and it works fine.
What is wrong getting the Session-Timeout from the rad_reply table?
When I assign a fixed value, e.g. 3500 in the sql row, it works fine.
Anything with MySQL collation maybe? I changed it to different languages ...
no success.
This is the debug output when I use the users file, when using sql, I do not
get this.
Debug: rlm_sql (sql): - sql_xlat
Debug: radius_xlat: 'gunther'
Debug: rlm_sql (sql): sql_set_user escaped user --> 'gunther'
Debug: radius_xlat: 'SELECT IF(NOW() <= user_stoptime,
TIME_TO_SEC(TIMEDIFF(user_stoptime, IF(NOW() >= user_starttime, now(),
DATE_SUB(user_stoptime, INTERVAL 1 SECOND, 1) FROM phs_user WHERE
user_username='gunther4''
Debug: radius_xlat: '/var/log/radius/sqltrace.sql'
Debug: rlm_sql (sql): Reserving sql socket id: 2
Debug: rlm_sql_mysql: query: SELECT IF(NOW() <= user_stoptime,
TIME_TO_SEC(TIMEDIFF(user_stoptime, IF(NOW() >= user_starttime, now(),
DATE_SUB(user_stoptime, INTERVAL 1 SECOND, 1) FROM phs_user WHERE
user_username='gunther4'
Debug: rlm_sql (sql): - sql_xlat finished
Debug: rlm_sql (sql): Released sql socket id: 2
Debug: radius_xlat: ' 1'
Debug: radius_xlat: '1'
When I use the sql table, sql_xlat is not in the debug output.
Gunther
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Freeradius - Where to start and where to get the right answer
Alan DeKok wrote: >I'll see if I can get one set up. Vladimir Vuksan wrote: >I recommend DokuWiki Wikipedia and many others are using MediaWiki.org. I did a quick test installation at http://s92562228.onlinehome.us/wiki If you need any assistance ... and no, I have not used any other RADIUS server before. Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Freeradius - Where to start and where to get the right answer
Nicolas Baradakis wrote: >You're right, the documentation isn't up-to-date. As you said, this is free software, >and any patch against the documentation will be greatly appreciated. > >Volunteers can submit their patches here: >http://bugs.freeradius.org/ Thanks for the hint! Didn't know that changes for the documentation would have to be filed as bugs. I could imaging that a wiki site, updated from quite a bunch of people, could solve a lot of problems. But I am glad that there is the search feature (ht://Dig) for this mailing list. ... and now back to my original problem ...dig, dig, dig ;-) Gunther - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Freeradius - Where to start and where to get the righ answer
After using FR 1.0.5 for testing for a while with a FR+MySQL configuration, I found out the hard way that documentation is not a pet project of FR. The only up-to-date documentation I found so far is reading the source code for hours, days, weeks ... There are lots of features I could not find anywhere in any kind of documentation, but in the source code, e.g. positional parameters for the SQL nas table. Or you can use the IP or domain name within the nas table for the 'nasname'. What happens to dynamic DNS addresses? etc Anyhow, it is a nice piece of software, working most of the times very well! Additional guidance, a wiki or ... could be extremely helpful for the project ... maybe it even exists somewhere. I found my way around, but I am kind of stuck when it comes to the accounting records. Sometimes I simply do not get a stoptime and therefore the session is supposed to be still online. I can then take maybe the idle time, add it to the last startime and see if it makes 'sense'. I had 4+ records with empty starttime for a nas. All I like to do for instance ... - User got 24h (or other time period) allocated - When session ends an updated record for the next session time could be generated - When user logs in again, the remaining session time will be granted But what happens when radacct does not get the stoptime for the last session? Anyhow, is there anything, anywhere (up-to-date) where I can find further information? Going through ever source file is quite a time consuming act. Anyhow, Open Source is best ... I can apply changes without requesting a license change ;-) And where can you directly communicate with the developers ... Thanks! G. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
