"Pshem Kowalczyk" <pshem.k@gmail.com> said

I would prefer to avoid user files all together. Currently we have over 100k customers (heaps of them have 'user-specific' setup, not just static ips). Customers change connection properties through a web-based interface and we need to speed up the propagation of those changes (currently we re-create the files every 5 minutes). I'll try to remove the unwanted ones with rlm_attr.

I'm in the process of building a very similar setup in freeradius. Like you, my aim is to have everything in SQL, with the absolute minimum of file based config required. I have backend provisioning which updates my SQL tables in real time as connection details are changed. Note that right now, only the 'wireless' part of the following is being used live. My dialups are currently still handled thru another RADIUS server (Funk/Juniper) while I finish testing and migration to freeradius. But the following tests out just fine with a Cisco 5200 NAS for dialup testing, as well as the live wireless stuff (coming in through a clutch of Mikrotiks). Also note that I'm doing this on 2.0.0pre, but with some small exceptions, the config should be the same for 1.1.x. This setup assumes that wireless and dialup users can be distinguished by which NAS they are connecting through. If you use the same NAS's for dialup and your broadband, this won't work. I have two huntgroups: wireless NAS-IP-Address == x.x.x.1 wireless NAS-IP-Address == x.x.x.2 dialup NAS-IP-Address == y.y.y.1 dialup NAS-IP-Address == y.y.y.2 So far, this is the only file based config data I'll need to change on an ongoing basis, as NAS IP's are added and removed. All other file configs are one-off changes. In the users file, I reference those huntgroups: DEFAULT Huntgroup-Name == 'wireless', Auth-Type := pam Fall-Through = 1 DEFAULT Huntgroup-Name == 'dialup', Auth-Type := pam Fall-Through = 1 NOTE that I'm using PAM for authentication, you won't want to set Auth-Type. Conventional wisdom probably says that I shouldn't set it either, but it works for me, where I only do PAM auth, period. The important thing here seems to be that you need to reference the Huntgroup-Name in the users file for it to get recognized later on. I then have the usual usergroup (called radusergroup in 2.0.0) definitions in the db: mysql> select * from radusergroup; +------------+-----------+----------+ | UserName | GroupName | priority | +------------+-----------+----------+ | radiustest | wireless | 1 | | radiustest | dialup | 1 | NOTE that in my setup, users can belong to both groups, and 'priority' has no significance. Allowing users to be in both groups does present problems with per-user reply attributes, see comments further on. In radgroupcheck, I have this: mysql> select * from radgroupcheck; +----+-----------+----------------+----+----------+ | id | GroupName | Attribute | op | Value | +----+-----------+----------------+----+----------+ | 5 | wireless | Huntgroup-Name | == | wireless | | 6 | dialup | Huntgroup-Name | == | dialup | And in radgroupreply, I have this: mysql> select * from radgroupreply; +----+-----------+-----------------+----+-------+ | id | GroupName | Attribute | op | Value | +----+-----------+-----------------+----+-------+ | 1 | wireless | Session-Timeout | = | 86400 | [all my other wireless defaults] | 12 | dialup | Session-Timeout | = | 3600 | [all my other dialup defaults] NOTE ... in order for the SQL group stuff to kick in, you have to do one of two things, as per the sql.conf file: ## If set to 'yes' (default) we read the group tables ## If set to 'no' the user MUST have Fall-Through = Yes in the radreply table # read_groups = yes Personally I leave this defaulting to 'no', and set Fall-Through in the per-user radreply table. The radcheck table looks like this: mysql> select * from radcheck; +----+------------+------------------+----+---------+ | id | UserName | Attribute | op | Value | +----+------------+------------------+----+---------+ | 1 | radiustest | Pam-Auth | := | radiusd | | 3 | radiustest | Simultaneous-Use | := | 1 | [other checklist attributes] Again, the above is specific to my setup using PAM to authenticate (via Winbind to a Windows AD). You'll probably have your Password attribute here instead of Pam-Auth, judging by the users file samples you provided. Also note that if you want to do 'simultaneous use' checking, you'll need to uncomment the simul_count_query in the main rlm_sql query file for your db type (in my case, mysql-dialup.conf). You'll also need to decide on simul_verify_query, which if defined will cause rlm_sql to try and use 'checkrad' to talk to the NAS to verify any existing sessions for a user rlm_sql thinks has already reached the max. Which means making sure your 'naspasswd' config is set up correctly, and NAS type is correct in your clients.conf (or nas db table, depending on your sql.conf 'nasclients' setting). I'm still testing simultaneous use in my setup, and can't guarantee it works right now. The radreply table looks like this: mysql> select * from radreply; +----+------------+---------------------+----+-----------+ | id | UserName | Attribute | op | Value | +----+------------+---------------------+----+-----------+ | 1 | radiustest | Mikrotik-Rate-Limit | = | 512k/512k | [other per user reply attributes] | 4 | radiustest | Fall-Through | = | 1 | Note the Fall-Through attribute. This is what tells rlm_sql to check the group tables, if you haven't set "read_groups=yes" in sql.conf. The only gotcha with the above setup is that if a user is in both groups, it will return all the 'radreply' attributes regardless of which Huntgroup they connect through. So even if 'radiustest' is connecting through a dialup NAS, the accept response will include the wireless specific Mikrotik-Rate-Limit attribute. So far this doesn't seem to be a problem - the NAS's I use seem to ignore nonsensical return attributes. This isn't a huge problem, and if you don't allow multiple account types per username, it won't affect you. But I'm looking for a way of suppressing inappropriate responses. I'm also using sqlippool for wireless users doing PPPOE. From your example it looks like you are probably using an external DHCP server for IP's on your ADSL, so I won't detail my sqlippool config. Let me know if you'd like to have that info. My main config looks like this (minus a few site specific sqlippool directives using the new 'unlang'). The only item of note is that I'm rejecting logins which don't get handled by rlm_sql. Without that 'notfound = reject' my setup will authenticate anyone with a Windows AD login, regardless of the rlm_sql config. This way, if they aren't in the rlm_sql users/groups, they can't login. authorize { preprocess chap mschap files sql { notfound = reject noop = reject } pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } pam } preacct { preprocess acct_unique } accounting { detail sqlippool sql attr_filter.accounting_response } session { sql } post-auth { sql Post-Auth-Type REJECT { attr_filter.access_reject } } pre-proxy { } post-proxy { } And that's about it. OK, so it's not exactly the setup you need, but very close.

kind regards pshem

-- hugh