Best way to deny users not matching any groups in the SQL DB

[Sylvain Munaut]

Hi Alan,


First, thanks for your reply.


>> What's the recommended way to achieve that result ?
>
>   Create an SQL table with 2 columns, user and SSID.  Then, write a SELECT statement which looks up user and SSID.  If it returns anything, the user is allowed to use that SSID.  If it returns nothing, the user isn't allowed to use that SSID.
>
>         if (! "%{sql:SELECT user FROM my_table WHERE user=%{User-Name} AND SSID=%{Called-Station-ID}}") {
>                 reject
>         }
>         # else it's OK
>
>   You'll have to double-check the SQL select statement, of course.  But that's the general idea.

Well that's just for the Wifi SSID, but I have a bunch of other
services too. All those checks are done in the rachgroupcheck and I'd
rather avoid having to duplicate all of them in the config files as
well.

Is there a way to recover the group that was matched ?


The current options I'm using is to :

 - Have all users be a member of the "deny" group that sets Auth-Type
:= None in the radgroupcheck table.  And have this group with a low
prio so that it's found last.
 - This covers the case of existing users that try to access a
non-authorized service, but not the case of a user that's not in the
DB at all.
 - For auth modes that need a password, ClearText-Password won't be
set is user doesn't exist, so auth will fail.
 - For EAP-TLS, the user could have a valid cert but not be in the DB
... so I added something like this in the check-eap-tls virtual server
:

        if (!(ok || updated)) {
                # Not found in the DB, deny
                update config {
                        &Auth-Type := Reject
                }
                return
        }

   This tests the return value of the sql module and if nothing was
found, it rejects the user.

But I find this a bit of a mess because :
 - If I forget to put the user in the "deny" group, he'll have access
to everything suddenly.
 - The EAP-TLS checks is based on the return value of the 'sql' module
and I'm not sure if that's guaranteed / specified.
 - Since I pretty much "invented" that and not followed an official
example, I'm not entirely sure I didn't break the security and that
there is no way to bypass those checks.

As improvements I was thinking either :

 - Change the group membership query to always return the 'deny'
group, no matter the username.
 - Or set a default_user_profile that would include 'deny', but then
in each group reply I have to add a 'Fall-Through; No' to prevent it
going to the 'profiles' processing after the group processing if a
group was matched.
 - Or maybe just never put the users into groups directly, but just
set a User-Profile for them, then maps the profiles to the group
(which might actually map reality more accurately since users belong
to a business unit and it's that business units that determines what
service they can access).

Since this seems like such a common usecase, I thought there would be
a "canonical / proven / recommended" way to  handle this.


Cheers

   Sylvain