Phil, I configured my freeradius server as in your example above ( It looks like case letters doesn't make any difference in MySQL statements)<br><br>User is successfully authenticated because of radcheck table. Maybe I need to reinstall freeradius server , because a month ago there was "Dialup Admin" installed too. Radcheck sql statements runs from "dialup.conf" file.<br>
<br><b>This is my table:</b><br><br>CREATE TABLE `Resv` (<br> `Username` varchar(20) NOT NULL,<br> `Start-Time` int(11) NOT NULL,<br> `End-Time` int(11) NOT NULL<br>) ENGINE=MyISAM DEFAULT CHARSET=latin1;<br><br><br><b>This is my output:</b><br>
<br># Executing section authorize from file /etc/freeradius/sites-enabled/default<br>+- entering group authorize {...}<br>sql_xlat<br> expand: %{User-Name} -> ieva<br>sql_set_user escaped user --> 'ieva'<br>
expand: select Start_time from Resv where Username='%{User-Name}' -> select Start_time from Resv where Username='ieva'<br>rlm_sql (sql): Reserving sql socket id: 3<br>rlm_sql_mysql: MYSQL check_error: 1054 received<br>
rlm_sql (sql): database query error, select Start_time from Resv where Username='ieva': Unknown column 'Start_time' in 'field list'<br>rlm_sql (sql): Released sql socket id: 3<br> expand: %{sql:select Start_time from Resv where Username='%{User-Name}'} -> <br>
sql_xlat<br> expand: %{User-Name} -> ieva<br>sql_set_user escaped user --> 'ieva'<br> expand: select End_time from Resv where Username='%{User-Name}' -> select End_time from Resv where Username='ieva'<br>
rlm_sql (sql): Reserving sql socket id: 2<br>rlm_sql_mysql: MYSQL check_error: 1054 received<br>rlm_sql (sql): database query error, select End_time from Resv where Username='ieva': Unknown column 'End_time' in 'field list'<br>
rlm_sql (sql): Released sql socket id: 2<br> expand: %{sql:select End_time from Resv where Username='%{User-Name}'} -> <br> expand: %l -> 1337002345<br>++[request] returns notfound<br>++? if (Resv-Cur-Time < Resv-Start-Time)<br>
Failed parsing "Resv-Start-Time": Unknown value Resv-Start-Time for attribute Resv-Cur-Time<br>++? if (Resv-Cur-Time > Resv-End-Time)<br>Failed parsing "Resv-End-Time": Unknown value Resv-End-Time for attribute Resv-Cur-Time<br>
expand: %{Resv-End-Time} - %{Resv-Cur-Time} -> 0 - 1337002345<br> expand: %{expr:%{Resv-End-Time} - %{Resv-Cur-Time}} -> -1337002345<br>++[reply] returns notfound<br>++[preprocess] returns ok<br>[auth_log] expand: /var/log/freeradius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /var/log/freeradius/radacct/<a href="http://172.16.83.51/auth-detail-20120514">172.16.83.51/auth-detail-20120514</a><br>
[auth_log] /var/log/freeradius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/freeradius/radacct/<a href="http://172.16.83.51/auth-detail-20120514">172.16.83.51/auth-detail-20120514</a><br>[auth_log] expand: %t -> Mon May 14 16:32:25 2012<br>
++[auth_log] returns ok<br>[sql] expand: %{User-Name} -> ieva<br>[sql] sql_set_user escaped user --> 'ieva'<br>rlm_sql (sql): Reserving sql socket id: 1<br>[sql] expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'ieva' ORDER BY id<br>
[sql] User found in radcheck table<br>[sql] expand: SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id -> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'ieva' ORDER BY id<br>
rlm_sql (sql): Released sql socket id: 1<br>++[sql] returns ok<br>++[expiration] returns noop<br>++[logintime] returns noop<br>rlm_checkval: Item Name: Calling-Station-Id, Value: 10.0.0.1<br>rlm_checkval: Could not find attribute named Calling-Station-Id in check pairs<br>
++[checkval] returns notfound<br>++[pap] returns updated<br>Found Auth-Type = PAP<br># Executing group from file /etc/freeradius/sites-enabled/default<br>+- entering group PAP {...}<br>[pap] login attempt with password "ieva"<br>
[pap] Using clear text password "ieva"<br>[pap] User authenticated successfully<br>++[pap] returns ok<br># Executing section post-auth from file /etc/freeradius/sites-enabled/default<br>+- entering group post-auth {...}<br>
[sql] expand: %{User-Name} -> ieva<br>[sql] sql_set_user escaped user --> 'ieva'<br>[sql] expand: %{User-Password} -> ieva<br>[sql] 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 ( 'ieva', 'ieva', 'Access-Accept', '2012-05-14 16:32:25')<br>
rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'ieva', 'ieva', 'Access-Accept', '2012-05-14 16:32:25')<br>
rlm_sql (sql): Reserving sql socket id: 0<br>rlm_sql (sql): Released sql socket id: 0<br>++[sql] returns ok<br>[sql_log] Processing sql_log_postauth<br>[sql_log] expand: %{User-Name} -> ieva<br>[sql_log] expand: %{%{User-Name}:-DEFAULT} -> ieva<br>
[sql_log] sql_set_user escaped user --> 'ieva'<br>[sql_log] WARNING: Deprecated conditional expansion ":-". See "man unlang" for details<br>[sql_log] 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 ('ieva', 'ieva', 'Access-Accept', '2012-05-14 16:32:25');<br>
[sql_log] expand: /var/log/freeradius/radacct/sql-relay -> /var/log/freeradius/radacct/sql-relay<br>++[sql_log] returns ok<br>++[exec] returns noop<br><br><br><br><div class="gmail_quote">On Mon, May 14, 2012 at 3:39 PM, Efx Efx <span dir="ltr"><<a href="mailto:ster.efx@gmail.com" target="_blank">ster.efx@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Thank you Phil!<br><br>I will try!<div class="HOEnZb"><div class="h5"><br><br><br><div class="gmail_quote">On Mon, May 14, 2012 at 3:25 PM, Phil Mayers <span dir="ltr"><<a href="mailto:p.mayers@imperial.ac.uk" target="_blank">p.mayers@imperial.ac.uk</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>On 14/05/12 12:09, jomajo wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hello Phil. Ofcourse it is not! I don't know (other people) but if they know<br>
any helpful information related with this, please let me know.<br>
</blockquote>
<br></div>
Ah, sorry, I'm confused - you're the same person!<div><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Can you share more information about Matthew lab ? How he's reserving a time<br>
slot and authenticating users with freeradius?<br>
<br>
This information would be really helpful, because I'm tying to achieve this<br>
too<br>
</blockquote>
<br></div>
So, in brief, you want something like this:<br>
<br>
Let's say you have an SQL table:<br>
<br>
username string, start_time integer, end_time integer<br>
<br>
...and the start/end times are unix seconds-since-epoch. You could implement this as follows.<br>
<br>
First, create three local attributes in raddb/dictionary:<br>
<br>
ATTRIBUTE Resv-Start-Time 3010 integer<br>
ATTRIBUTE Resv-End-Time 3011 integer<br>
ATTRIBUTE Resv-Cur-Time 3012 integer<br>
<br>
Second, write an "unlang" policy in your virtual server like so:<br>
<br>
authorize {<br>
...<br>
update request {<br>
Resv-Start-Time := "%{sql:select start_time from resv where username='%{User-Name}'}"<br>
Resv-End-Time := "%{sql:select end_time from resv where username='%{User-Name}'}"<br>
Resv-Cur-Time := "%l"<br>
}<br>
<br>
if (Resv-Cur-Time < Resv-Start-Time) {<br>
reject<br>
update reply {<br>
Reply-Message := "your slot has not yet started"<br>
}<br>
}<br>
<br>
if (Resv-Cur-Time > Resv-End-Time) {<br>
reject<br>
update reply {<br>
Reply-Message := "your slot has finished"<br>
}<br>
}<br>
<br>
# you probably want to set the Session-Timeout so they get kicked off<br>
update reply {<br>
Session-Timeout := "%{expr:%{Resv-End-Time} - %{Resv-Cur-Time}}"<br>
}<br>
<br>
...<br>
}<br>
<br>
Hopefully it's clear what this does, and how it works.<div><div><br>
-<br>
List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/users.html" target="_blank">http://www.freeradius.org/<u></u>list/users.html</a><br>
</div></div></blockquote></div><br>
</div></div></blockquote></div><br>