Fixed Duration Weekly, Monthly and Daily Accounts

classic Classic list List threaded Threaded
19 messages Options
| Threaded
Open this post in threaded view
|

Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
Looking for some advise regarding accounts with a preset amount of time. I have freeradius setup and working with the sqlcounter module. Am just a little lost.

How do I create a group which provides access for a preset amount of time, for instance one day, week or month. I've tried by using expiration but don't think thats right. Will the noreset parameter do this?

In our current system (not freeradius), we'd set this up as a continuous account which starts the counter the first time the users logs on.

It's only the daily, weekly, monthly plans that work like this. I feel if we set a monthly rule to allow 43200 minutes, the user will get a lot of time online!
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Fajar A. Nugraha-2
On Sun, Oct 30, 2011 at 7:39 PM, JennyBlunt <[hidden email]> wrote:
> How do I create a group which provides access for a preset amount of time,
> for instance one day, week or month. I've tried by using expiration but
> don't think thats right. Will the noreset parameter do this?

>
> In our current system (not freeradius), we'd set this up as a continuous
> account which starts the counter the first time the users logs on.

I actually suggest you use rlm_sqlcounter instead, which (for me) is
easier to understand and maintain. Use the example noresetcounter from
http://wiki.freeradius.org/Rlm_sqlcounter

You can see exactly how the module counts whatever-it-uses (in the
example it's SUM(AcctSessionTime)), and you can run the query manually
for debugging purposes.

--
Fajar
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Suman Dash
Expiration is actually the validity of the account. It does not calculates the amount of time used. If you are looking to limit the session time i.e 100 hrs Monthly or anything , you need rlm_sqlcounter with attributes like Max-Session-Time with a value.

In conjunction with Expiration, you can use Max-Session-Time to create packages such as 1 Month 100 Hrs Browsing etc .

Feel to send any other query if you are stuck .

Regards
Suman

On Sun, Oct 30, 2011 at 6:28 PM, Fajar A. Nugraha <[hidden email]> wrote:
On Sun, Oct 30, 2011 at 7:39 PM, JennyBlunt <[hidden email]> wrote:
> How do I create a group which provides access for a preset amount of time,
> for instance one day, week or month. I've tried by using expiration but
> don't think thats right. Will the noreset parameter do this?

>
> In our current system (not freeradius), we'd set this up as a continuous
> account which starts the counter the first time the users logs on.

I actually suggest you use rlm_sqlcounter instead, which (for me) is
easier to understand and maintain. Use the example noresetcounter from
http://wiki.freeradius.org/Rlm_sqlcounter

You can see exactly how the module counts whatever-it-uses (in the
example it's SUM(AcctSessionTime)), and you can run the query manually
for debugging purposes.

--
Fajar
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
In reply to this post by Fajar A. Nugraha-2
That's the one we're using. What I don't understand is that if we set up a group with max-all-session = 43200 the user would get in total 43200 minutes. When, in reality, we're trying to give them a continuous 43200 minutes from first login.
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Suman Dash
Re-Correct - Max-All-Session = 43200 ( It's in Seconds , Not in Minutes )

Second, It is not under our control to decide how much time the user users. We can assign a continuous usage of XYZ minutes / Hours but what if the user disconnects early ?

What happens when the user logins again ? Will you reject the user or accept the user ?

Anyway, to answer your questions, Session-Timeout of 43200 is what needed to give a continuous Session session of 43200. But again when the user disconnects and re-logins , he/she will again get 43200 of time during re-login.

Regards
Suman

On Sun, Oct 30, 2011 at 6:50 PM, JennyBlunt <[hidden email]> wrote:
That's the one we're using. What I don't understand is that if we set up a
group with max-all-session = 43200 the user would get in total 43200
minutes. When, in reality, we're trying to give them a continuous 43200
minutes from first login.


--
View this message in context: http://freeradius.1045715.n5.nabble.com/Fixed-Duration-Weekly-Monthly-and-Daily-Accounts-tp4950022p4950078.html
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
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Alan DeKok-2
In reply to this post by JennyBlunt
JennyBlunt wrote:
> That's the one we're using. What I don't understand is that if we set up a
> group with max-all-session = 43200 the user would get in total 43200
> minutes. When, in reality, we're trying to give them a continuous 43200
> minutes from first login.

  Then set the expiration date when they first log in.

  Remember: FreeRADIUS authenticates people.  It isn't a DB.

  If you want to have it remember something, you need to store that
information in a DB.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Suman Dash
Hi Alan,

I think the poster is asking for a continuous amount of time after login. Setting Expiration will give a max valid period in which the user can login but under no circumstances it can provide a continuous period of usage.

Poster is also not clear what will happen if the user disconnects and connects again So i think the question asked here is incomplete.

Regards
Suman

On Sun, Oct 30, 2011 at 7:07 PM, Alan DeKok <[hidden email]> wrote:
JennyBlunt wrote:
> That's the one we're using. What I don't understand is that if we set up a
> group with max-all-session = 43200 the user would get in total 43200
> minutes. When, in reality, we're trying to give them a continuous 43200
> minutes from first login.

 Then set the expiration date when they first log in.

 Remember: FreeRADIUS authenticates people.  It isn't a DB.

 If you want to have it remember something, you need to store that
information in a DB.

 Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
In reply to this post by Alan DeKok-2
that's what I was trying to get to - am happy to set an expiration upon first login - just wanted to see if I could achieve the desired with the built in functions etc.

If expiration date is the way forward, presumably I need to write something in which will set the session-timeout based on end time and start time?

J
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Alan DeKok-2
JennyBlunt wrote:
> that's what I was trying to get to - am happy to set an expiration upon first
> login - just wanted to see if I could achieve the desired with the built in
> functions etc.

  Yes... your question was also ambiguous.

  Do you want a TOTAL of X hours after first login, summed only over
times they're logged in?

  Or do you want them to be able to login for only X hours after first
login, based on wall-clock time?  so if they log out for 2 hours, they
"lose" those two hours?

> If expiration date is the way forward, presumably I need to write something
> in which will set the session-timeout based on end time and start time?

  The server does that automatically when you set Expiration.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Fajar A. Nugraha-2
In reply to this post by JennyBlunt
On Sun, Oct 30, 2011 at 9:59 PM, JennyBlunt <[hidden email]> wrote:
> that's what I was trying to get to - am happy to set an expiration upon first
> login - just wanted to see if I could achieve the desired with the built in
> functions etc.

You really need to make sure you know EXACTLY what you want, and tell
what that is in a clear manner, so they can help you better.

Anyway, if you just want to set expiration after a user logs in, try
using unlang in post-auth section. See "man unlang" for details.
Something like

post-auth {
...
  update control {
    Tmp-String-0 := "%{sql: SELECT ID FROM radcheck WHERE
username='%{User-Name}' AND attribute='Expiration'}"
  }

  if (! "%{control.Tmp-String-0}") {
    update control {
      Tmp-String-0 := "%{sql: INSERT INTO radcheck
(username,atrribute,op,value) values (...)}; SELECT NOW()"
    }
  }
...
}


The above snippet is just an example from the top of my head and
incomplete. Basically after a succesful login, you check whether an
entry for Expiration already exists in radcheck, and insert it if it
isn't already there. The last "SELECT NOW()" is so that the query
returns a value so FR won't complain.

--
Fajar
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
Sorry if I wasn't clear. I want to be able to generate a monthly voucher - 30 days access from the exact time of first login. A continuous clock ticking from the start time.

I'm going to look at Fajar's suggestion now...

| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Suman Dash
You have the exact solution given by Fajar. See his SQL query and modify as required.

Regards
Suman

On Sun, Oct 30, 2011 at 10:26 PM, JennyBlunt <[hidden email]> wrote:
Sorry if I wasn't clear. I want to be able to generate a monthly voucher - 30
days access from the exact time of first login. A continuous clock ticking
from the start time.

I'm going to look at Fajar's suggestion now...



--
View this message in context: http://freeradius.1045715.n5.nabble.com/Fixed-Duration-Weekly-Monthly-and-Daily-Accounts-tp4950022p4950392.html
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
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
In reply to this post by Fajar A. Nugraha-2
I'm looking at your query and am a bit lost as to what I should use for the attributes.

Say I wanted 24 hour voucher - expires exactly 24 hours after first login. I need to give the user some attribute in the db of 86400 seconds - for example I'll call this Access-Duration

So how do I insert the sql query?

Your query looks like this:

post-auth {
...
  update control {
    Tmp-String-0 := "%{sql: SELECT ID FROM radcheck WHERE
username='%{User-Name}' AND attribute='Expiration'}"
  }

  if (! "%{control.Tmp-String-0}") {
    update control {
      Tmp-String-0 := "%{sql: INSERT INTO radcheck
(username,atrribute,op,value) values (...)}; SELECT NOW()"
    }
  }
...
}

What should the values be and how do I call the Access-Duration value?? Do I need another statement near TmpWould it be something like this:

"%{sql: INSERT INTO radcheck
(username,atrribute,op,value) values ('%{User-Name}', 'expiration', ':=', 'UNIX_TIMESTAMP+ %{control.Tmp-String-0}'}; SELECT NOW()"

Thanks for your help
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Fajar A. Nugraha-2
On Sat, Nov 12, 2011 at 2:43 AM, JennyBlunt <[hidden email]> wrote:
> I'm looking at your query and am a bit lost as to what I should use for the
> attributes.
>
> Say I wanted 24 hour voucher - expires exactly 24 hours after first login. I
> need to give the user some attribute in the db of 86400 seconds - for
> example I'll call this Access-Duration

You can't. It's not a valid attribute.
You use existing Expiration instead, where

Expiration = now() + whatever-value-you-want-for-the-account-to-be-valid

There are of course additional details, like what the format of
Expiration attribute should be (e.g. using 2011-11-11 won't work),
and how to do the actual addition (simply using "+" in mysql won't
work).

Hint: try http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
. Should be easy enough, but might take several hours of trying to get
the correct syntax right. If you're still confused, hire an
experienced mysql developer to help you out.

--
Fajar
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Marinko Tarlać
For example, Mikrotik understands this syntax

id | UserName | Attribute | Value | Op
183 | someuser| Expiration | October 04 2011 00:00:00 | ==

To convert 2011-10-04 into October 04 2011 00:00:00 you should use the
next SQL syntax

SELECT DATE_FORMAT( `date` , '%M %d %Y %H:%i:%s' ) AS date FROM users
WHERE ....

Or check the next example

SELECT DATE_FORMAT('2011-10-04','%M %d %Y %H:%i:%s') AS date,
DATE_FORMAT((NOW()+ INTERVAL 1 day),'%M %d %Y %H:%i:%s') AS date2....

gives

date     date2
October 04 2011 00:00:00     November 12 2011 23:52:57

(right now is November 11 2011 23:52:57)


On 11/11/2011 10:48 PM, Fajar A. Nugraha wrote:

> On Sat, Nov 12, 2011 at 2:43 AM, JennyBlunt<[hidden email]>  wrote:
>> I'm looking at your query and am a bit lost as to what I should use for the
>> attributes.
>>
>> Say I wanted 24 hour voucher - expires exactly 24 hours after first login. I
>> need to give the user some attribute in the db of 86400 seconds - for
>> example I'll call this Access-Duration
> You can't. It's not a valid attribute.
> You use existing Expiration instead, where
>
> Expiration = now() + whatever-value-you-want-for-the-account-to-be-valid
>
> There are of course additional details, like what the format of
> Expiration attribute should be (e.g. using 2011-11-11 won't work),
> and how to do the actual addition (simply using "+" in mysql won't
> work).
>
> Hint: try http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
> . Should be easy enough, but might take several hours of trying to get
> the correct syntax right. If you're still confused, hire an
> experienced mysql developer to help you out.
>

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
Thanks for the help, that's very useful. I can get the time in there now but have a question about getting our access-period out.

In post-auth, I've tried this query to get the access-period out:

if (! "%{control.Tmp-String-0}") {
    update control {
      Tmp-String-2 := "%{sql: SELECT value FROM radcheck WHERE username='%{User-Name}' and attribute = 'Access-Period'}"
      Tmp-String-3 := "%{sql: INSERT INTO radcheck (username,attribute,op,value) values ('%{User-Name}', 'Expiration', ':=', '%{Tmp-String-2}')}; SELECT NOW()"
    }
  }

In freeradius, Tmp-String-2 has no value, whereas if I run the query in mysql, I get a value of 86400 (I know the above doesn't sum anything).

How can I get and use the value?
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

Fajar A. Nugraha-2
On Sat, Nov 12, 2011 at 7:49 PM, JennyBlunt <[hidden email]> wrote:

> Thanks for the help, that's very useful. I can get the time in there now but
> have a question about getting our access-period out.
>
> In post-auth, I've tried this query to get the access-period out:
>
> if (! "%{control.Tmp-String-0}") {
>    update control {
>      Tmp-String-2 := "%{sql: SELECT value FROM radcheck WHERE
> username='%{User-Name}' and attribute = 'Access-Period'}"
>      Tmp-String-3 := "%{sql: INSERT INTO radcheck
> (username,attribute,op,value) values ('%{User-Name}', 'Expiration', ':=',
> '%{Tmp-String-2}')}; SELECT NOW()"
>    }
>  }
>
> In freeradius, Tmp-String-2 has no value, whereas if I run the query in
> mysql, I get a value of 86400 (I know the above doesn't sum anything).
>
> How can I get and use the value?

I don't think Access-Period is a valid attribute (are you using your
own dictionary?)

Anyway, if you think freeradius is not passing the correct query, try
activating mysql's general query log:
http://dev.mysql.com/doc/refman/5.5/en/query-log.html . It should
print out exactly what queries are recevied by mysql, and you can run
those queries manually. Pretty handy for debug cases of mismatched
quotes or brackets.

--
Fajar

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
Access-Period's defined by ourselves indeed.

I'm using the query log already and the value output is blank. I have tested the query with a hardcoded input such as '123' and it inputs correctly.

INSERT INTO radcheck (username,attribute,op,value) values ('hcTTm', 'Expiration', ':=', ''))

I'm a bit confused as the query works in sql
| Threaded
Open this post in threaded view
|

Re: Fixed Duration Weekly, Monthly and Daily Accounts

JennyBlunt
Got it sorted by moving the query out in to a separate update request section and calling without control.XXX