Bjørn Mork <bjorn@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