How to convert a users file into a rlm_sql one?

[Hugh Messenger]

"Pshem Kowalczyk" <pshem.k at 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