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