RADIUS + MySQL + decisionmaking?

[Guy Fraser]

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 ---