Re: RADIUS + MySQL + decisionmaking?

27 Sep 2006

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

Re: RADIUS + MySQL + decisionmaking?

Guy Fraser