RADIUS + MySQL + decisionmaking?

[Jan Mulders]

Thanks for your help. I ended up pulling the accounting counter part
out completely and did it via a cronjob that changed the user's group
- and have implemented the detail log as suggested.

Thank you,

Jan

On 27/09/06, Guy Fraser <guy at incentre.net> wrote:
> On Wed, 2006-09-27 at 02:47 +0100, Jan Mulders wrote:
> > Hello,
> >
> > I am trying to set up some decision-making logic into FreeRADIUS, to
> > assign users a different speed of service depending on how much
> > bandwidth they've used since their billing started.
> >
> > I want to issue 512k speed to users in group A, who have used less
> > than 20GB of bandwidth (monthlybytecounter is working fine at the
> > moment and totals this up nicely). However, if they've used more than
> > 20GB, I want to issue 256k speed to users.
> >
> > For group B, I want users to get 10Mbps as long as they've used less
> > than 50GB of bandwidth, and 1Mbps if they're over.
> >
> > I want to assign the values for speed to some vendor-specific
> > variable, let's say Max-User-Speed.
> >
>
> Hi
>
> I am replying because I haven't seen any other replies.
>
> The Attribute you use will depend on the "NAS" equipment
> you are using. Check the documentation and dictionaries
> for your radius client.
>
> > I am using MySQL for this. Here is a snippet from my database:
> >
> > radcheck table:
> >
> > username, attribute, op, value
> > testuser1, Password, ==, testing
> >
> > usergroup table:
> >
> > username, groupname
> > testuser1, groupa
> >
> >
> > Here is a snippet from my radiusd.conf file:
> >
> > instantiate {
> >                 monthlybytecounter
> > }
> >
> > authorize {
> >                 preprocess
> >                 sql
> > }
> >
> > authenticate {
> >                 pap
> > }
> >
> > preacct {
> >                 preprocess
> > }
> >
> > accounting {
> >                 #acct_unique
> >                 #detail
> >                 sql
> >                 radutmp # ?
> > }
> >
> >
> > session {
> >                 radutmp # ?
> >                 sql
> >
> > }
> >
> > My question is... how do I implement this? Can anyone write down a few
> > examples of how I'd go about making these rules?
> >
> > Would I perhaps be better off making a cronjob or something that
> > changes the user's group to one of the following? groupA_belowcap,
> > groupA_overcap, groupB_belowcap, groupB_overcap?
> >
> I do not usually work with MySQL but you are on the right track
> using a counter but you didn't say if it was an sql_counter, which
> is what I would use. I would also drop the "radutmp" bits, and do
> everything from SQL. One other note, I usually keep the "detail"
> bits, for archival purposes in case of a dispute.
>
> As for examples, this is as close as I can give you with the bits
> you want :
> --- snip ---
> modules {
>         detail acct_log {
>                 detailfile = ${radacctdir}/%Y/%m/detail-%Y%m%d
>                 detailperm = 0640
>                 dirperm = 0750
>         }
>         sqlcounter dailycounter {
>                 counter-name = Daily-Session-Time
>                 check-name = Max-Daily-Session
>                 sqlmod-inst = sql
>                 key = User-Name
>                 reset = daily
>                 query = "SELECT SUM(AcctSessionTime - \
>                  GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
>                  FROM radacct WHERE UserName='%{%k}' AND \
>                  UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
>         }
>         sqlcounter monthlycounter {
>                 counter-name = Monthly-Session-Time
>                 check-name = Max-Monthly-Session
>                 sqlmod-inst = sql
>                 key = User-Name
>                 reset = monthly
>                 query = "SELECT SUM(AcctSessionTime - \
>                  GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) \
>                  FROM radacct WHERE UserName='%{%k}' AND \
>                  UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
>         }
> }
> instantiate {
>         dailycounter
>         monthlycounter
> }
> authorize {
>         sql
>         dailycounter
>         monthlycounter
> }
> accounting {
>         acct_log
>         sql
> }
> session {
>         sql
> }
> --- snip ---
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>