rlm_sql: usergroup lookup if User-Profile is defined

Bjørn Mork bjorn at mork.no
Sun Oct 25 11:01:15 CET 2009


Bjørn Mork <bjorn at mork.no> writes:

> I am wondering if I'm the only one who finds the following default
> behaviour a bit confusing:  Given a user defined like this:
>
>  user1     Cleartext-Password := "foo", User-Profile := "profile1"
>
> I would expect "profile1" to always be looked up in the the usergroup
> table for this user.  However, this won't happen if "user1" is defined
> in that table without Fall-Through.  rlm_sql will lookup "user1" first
> and only lookup "profile1" if either "user1" is not found or
> Fall-Through is set vy the "user1" groups.


Some more information about what I'm trying to achieve.  Maybe I'm doing
something very awkward and strange, and really should go another route.
Any hints are appreciated.


I have 2.6 million user accounts:

mysql> select count(distinct(username)) from radcheck;
+---------------------------+
| count(distinct(username)) |
+---------------------------+
|                   2627686 | 
+---------------------------+
1 row in set (7.41 sec)


Nearly all of these set User-Profile:

mysql> select count(*) from radcheck where attribute = 'User-Profile';
+----------+
| count(*) |
+----------+
|  2627522 | 
+----------+
1 row in set (2.19 sec)


The profiles represent a small number of common check and reply items
for one account class.  There are only(?) 83 such distinct account types
at the moment:


mysql> select count(distinct(username)) from radusergroup;
+---------------------------+
| count(distinct(username)) |
+---------------------------+
|                        83 | 
+---------------------------+
1 row in set (0.01 sec)



Most of the profiles have more than one entry in the radusergroup, to do
prioritized lookups like

user1    NAS-Port-Type == xDSL 
         attribute1 = foo

user1    NAS-Port-Type == Ethernet
         attribute1 = bar



So the total number of entries in radusergroup is higher than the number
of profiles, giving an average of 4.7 group check lists per profile:


mysql> select count(*) from radusergroup;
+----------+
| count(*) |
+----------+
|      387 | 
+----------+
1 row in set (0.00 sec)




Now, I do realize that the original design is based on an assumption
that every user will have an individual entry in radusergroup, mapping
to every group check list for that user.  I am trying to avoid that
because: 

  - I don't need it:  There are only 83 distinct profiles, not 2.6 million
  - mapping a user to a profile instead of a group list virtualizes the
    knowlegde of the actual profile contents, thereby avoiding the need
    for every script creating user account to do this mapping (there is
    more than 1 such script...)
  - the 2.6 million users would expand to approx. 12.3 million rows in
    the usergroup tables, assuming an even distibution among the
    profiles (real numbers are probably worse, as the most common
    profiles also tend to be the most complex ones).  The alternative is
    2.6 million rows in the radcheck table, saving ~10 million rows...
  - not adding users to radusergroup reduces the number of tables a
    useradd script need to touch from 3 to 2.  Remember again that each
    such table will be shared among several writers, and therefore need
    an "per row ownership" policy

But to be able to use the radusergroup as I want, I have one
requirement:

  - "eviluser" should not gain access to anything by using "profile" as
    username, even if "profile" sets a password (some profiles might be
    meant for devices with a preprogrammed common password, where the
    individiual user check list is doing the actual authentication based
    on e.g. Calling-Station-Id)

 and also some wishes:

  - "profile1" should be both a valid username and profile name, where
    the user very well could be mapped to "profile2"
  - looking up the username in the radusergroup table is pointless, so
    it should be avoided
  - in particular, looking up a username not found in radcheck or which
    failed the radcheck items should be avoided.  It is guaranteed to be
    pointless if the requirement above is fulfilled.



I think I can meet my requirement without any code changes by adding a
check item like this to every group referenced by "profilename":

  User-Name != "profilename"

(maybe think a bit about case sensitivity here - doing case sensitive
lookups in the radusergroup table would solve that)

But AFAICS, my wishlist items would need a code change.  My suggestion
would be something like this, of course defaulting to the existing
behaviour (concept for discussion only - not even build tested): 

diff --git a/raddb/sql.conf b/raddb/sql.conf
index 690c3a2..631e7b5 100644
--- a/raddb/sql.conf
+++ b/raddb/sql.conf
@@ -66,6 +66,10 @@ sql {
 	# If set to 'no' the user MUST have Fall-Through = Yes in the radreply table
 	# read_groups = yes
 
+	# If set to 'yes' then only the User-Profile is looked up in the usergroup table
+	# If set to 'no' (default) then we lookup the username first
+	# user_profile_only = no
+
 	# Remove stale session if checkrad does not see a double login
 	deletestalesessions = yes
 
diff --git a/src/modules/rlm_sql/rlm_sql.c b/src/modules/rlm_sql/rlm_sql.c
index 5940700..f6dcfbc 100644
--- a/src/modules/rlm_sql/rlm_sql.c
+++ b/src/modules/rlm_sql/rlm_sql.c
@@ -51,6 +51,8 @@ static const CONF_PARSER module_config[] = {
 	 offsetof(SQL_CONFIG,sql_db), NULL, "radius"},
 	{"read_groups", PW_TYPE_BOOLEAN,
 	 offsetof(SQL_CONFIG,read_groups), NULL, "yes"},
+	{"user_profile_only", PW_TYPE_BOOLEAN,
+	 offsetof(SQL_CONFIG,user_profile_only), NULL, "no"},
 	{"sqltrace", PW_TYPE_BOOLEAN,
 	 offsetof(SQL_CONFIG,sqltrace), NULL, "no"},
 	{"sqltracefile", PW_TYPE_STRING_PTR,
@@ -1064,7 +1066,7 @@ static int rlm_sql_authorize(void *instance, REQUEST * request)
 	 *	however, *is* found, Fall-Through must be set in order to process
 	 *	the groups as well
 	 */
-	if (dofallthrough) {
+	if (!inst->config->user_profile_only && dofallthrough) {
 		rows = rlm_sql_process_groups(inst, request, sqlsocket, &dofallthrough);
 		if (rows < 0) {
 			radlog_request(L_ERR, 0, request, "Error processing groups; rejecting user");




Does something like this stand a chance to be accepted?  In that case, I
am tempted to use it.



Bjørn




More information about the Freeradius-Users mailing list