Some SQL radgroupcheck/reply troubles.

Anton Borisov Tue, 13 Jan 2009 03:49:09 -0800

Good day!

I try to understand some of SQL selects in oracle.conf in Freeradius.
I think, I have found incorrect sql selects in radgroupcheck/radgrpoupreply.


Let me show it:
First of all - typical install freeradius2.1.1 with oracle.
I can see this selects in sql.conf for dialup.oracle.conf

###########################
authorize_group_check_query = " \

SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op \

FROM ${groupcheck_table},${usergroup_table} \
WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
    AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
ORDER BY ${groupcheck_table}.id"


authorize_group_reply_query = " \

SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op \

FROM ${groupreply_table},${usergroup_table} \
WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
    AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
ORDER BY ${groupreply_table}.id"


#######################
user in sql:
in radcheck
USERNAME    ATTRIBUTE    OP VALUE
c-user    User-Password    =    c-password
in radreply
c-user    Reply-Message += c-reply

in usergroup
USERNAME    GROUPNAME    PRIORITY
c-user        a-group        10
c-user        b-group         5

in radgroupreply
GROUPNAME  ATTRIBUTE        OP VALUE
a-group    Reply-Message   += a-group
b-group    Reply-Message   += b-group


Ok, let try to make radclient requests for my c-user user:
Here is debug about sql select in freeradius2.1.1

Tue Jan 13 13:03:07 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 1 Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:03:07 2009 : Info: Invalid operator for item User-Password: reverting to '=='

Tue Jan 13 13:03:07 2009 : Info: [sqlauth] User found in radcheck table

Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id

Tue Jan 13 13:03:07 2009 : Info: [sqlauth] User found in group a-group

^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Only one group here!!!!!!!!!!!!!!

Tue Jan 13 13:03:07 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id Tue Jan 13 13:03:07 2009 : Debug: rlm_sql (sqlauth): Released sql socket id: 1

Tue Jan 13 13:03:07 2009 : Info: ++[sqlauth] returns ok



And here is answer (radclient)

rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=87, length=47

    Reply-Message = "c-reply"
    Reply-Message = "b-group"
    Reply-Message = "a-group"

Why?

If we try to use SELECT from debug we can see that select pull user from a-group and b-group in ONLY the one request:


Try to make sql select:

>>>SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id;


And we see:
GROUPNAME  ATTRIBUTE        VALUE     OP
b-group    Reply-Message    b-group     +=
a-group    Reply-Message    a-group     +=

Next, if we add into a-group: Fall-Through       =  Yes
we can see the same select several times:

Tue Jan 13 14:29:11 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 1 Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 14:29:11 2009 : Info: Invalid operator for item User-Password: reverting to '=='

Tue Jan 13 14:29:11 2009 : Info: [sqlauth] User found in radcheck table

Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id

Tue Jan 13 14:29:11 2009 : Info: [sqlauth] User found in group a-group

Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id Tue Jan 13 14:29:11 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id

Tue Jan 13 14:29:11 2009 : Info: [sqlauth] User found in group b-group

^^^^^^^^^^^^^^^^^ Another select the SAME!! ^^^^^^^^^^^^^^^^^^^^^^^^^^

Tue Jan 13 14:29:11 2009 : Info: ++[sqlauth] returns ok


Here this select in sql:

SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'c-user' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id


GROUPNAME  ATTRIBUTE       VALUE    OP
a-group    Fall-Through    Yes                  =
b-group    Reply-Message        b-group         +=
a-group    Reply-Message        a-group         +=

With or without Fall-Through we have the same result.





I think this is wrond because:
http://wiki.freeradius.org/index.php?title=Rlm_sql

1. Search the radcheck table for any check attributes specific to the user
YES

2. If check attributes are found, and there's a match, pull the reply items from the radreply table for this user and add them to the reply

YES

3. Group processing then begins if any of the following conditions are met:
          * The user IS NOT found in radcheck
          * The user IS found in radcheck, but the check items don't match

* The user IS found in radcheck, the check items DO match AND Fall-Through is set in the radreply table * The user IS found in radcheck, the check items DO match AND the read_groups directive is set to 'yes'

YES

4. If groups are to be processed for this user, the first thing that is done is the list of groups this user is a member of is pulled from the usergroup table ordered by the priority field. The priority field of the usergroup table allows us to control the order in which groups are processed, so that we can emulate the ordering in the users file. This can be important in many cases.

YES

5. For each group this user is a member of, the corresponding check items are pulled from radgroupcheck table and compared with the request. If there is a match, the reply items for this group are pulled from the radgroupreply table and applied.

NO

6. Processing continues to the next group IF:
          * There was not a match for the last group's check items OR

* Fall-Through was set in the last group's reply items (The above is exactly the same as in the users file)

NO

7. Finally, if the user has a User-Profile attribute set or the Default Profile option is set in the sql.conf, then steps 4-6 are repeated for the groups that the profile is a member of.

NO

Well, in debug we can see only one group-request with ALL groups parameters where user belong to and only ONE group-reply with ALL groups parameters where user belong to.. I.e one groupcheck/groupreply with all groups parameters...


Now, let me try to correct SELECT in dialup.oracle.conf. I add only one:

AND ${usergroup_table}.GroupName = '%{SQL-Group}', so, all group-select something like this:


###########################
authorize_group_check_query = " \

SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op \

FROM ${groupcheck_table},${usergroup_table} \
WHERE (${usergroup_table}.Username = '%{SQL-User-Name}' \
        AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
        AND ${usergroup_table}.GroupName = '%{SQL-Group}' \
ORDER BY ${usergroup_table}.PRIORITY"

###########################

SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op \

FROM ${groupreply_table},${usergroup_table} \
WHERE (${usergroup_table}.Username = '%{SQL-User-Name}'\
    AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
    AND ${usergroup_table}.GroupName = '%{SQL-Group}' \
ORDER BY ${usergroup_table}.PRIORITY"

Here is debug with result in the same radius with the same request (without Fall-Through):

Tue Jan 13 13:21:33 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 1 Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:21:33 2009 : Info: Invalid operator for item User-Password: reverting to '=='

Tue Jan 13 13:21:33 2009 : Info: [sqlauth] User found in radcheck table

Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 13:21:33 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' or usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = 'c-user' or usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY

Tue Jan 13 13:21:34 2009 : Info: [sqlauth] User found in group a-group

^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Again Only one group here!!!!!!!!!!!!!!

Tue Jan 13 13:21:34 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' OR usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:21:34 2009 : Debug: rlm_sql (sqlauth): Released sql socket id: 1

Tue Jan 13 13:21:34 2009 : Info: ++[sqlauth] returns ok


Answer:

rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=116, length=38

    Reply-Message = "c-reply"
    Reply-Message = "a-group"

We can see user found in only one group (And only one select). But - we did not use Fall-Through','=','Yes. (See 6. Processing continues to the next group IF: in wiki Freeradius sql). And we can see only ONE group in select from radgroupreply:

SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY;


GROUPNAME  ATTRIBUTE      VALUE     OP
a-group    Reply-Message   a-group +=

Only one group.


Well, just add Fall-Through:
radgroupreply
GROUPNAME  ATTRIBUTE        OP VALUE
a-group    Reply-Message       += a-group
b-group    Reply-Message       += b-group
a-group    Fall-Through       =  Yes


Here is result:

ue Jan 13 13:28:04 2009 : Debug: rlm_sql (sqlauth): Reserving sql socket id: 0 Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:28:04 2009 : Info: Invalid operator for item User-Password: reverting to '=='

Tue Jan 13 13:28:04 2009 : Info: [sqlauth] User found in radcheck table

Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'c-user' ORDER BY id Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT GroupName FROM usergroup WHERE UserName='%{SQL-User-Name}' order by priority DESC -> SELECT GroupName FROM usergroup WHERE UserName='c-user' order by priority DESC Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' or usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = 'c-user' or usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY

Tue Jan 13 13:28:04 2009 : Info: [sqlauth] User found in group a-group

^^^^^^^^^^^^^^^^ We can see a-group check ^^^^^^^^^^^^^^^^^^^^^^^^^

Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' OR usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE (usergroup.Username = 'c-user' OR usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupreply.GroupName AND usergroup.GroupName = 'a-group' ORDER BY usergroup.PRIORITY Tue Jan 13 13:28:04 2009 : Info: [sqlauth] expand: SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = '%{SQL-User-Name}' or usergroup.CLID = '%{Calling-Station-Id}') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = '%{SQL-Group}' ORDER BY usergroup.PRIORITY -> SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE (usergroup.Username = 'c-user' or usergroup.CLID = '250097000222612') AND usergroup.GroupName = radgroupcheck.GroupName AND usergroup.GroupName = 'b-group' ORDER BY usergroup.PRIORITY

Tue Jan 13 13:28:04 2009 : Info: [sqlauth] User found in group b-group

^^^^^^^^^^^^^^^^^^^^^^ And we can see b-group check^^^^^^^^^^^^^^^^^^^^

Tue Jan 13 13:28:04 2009 : Info: ++[sqlauth] returns ok


And correct result:

rad_recv: Access-Accept packet from host 127.0.0.01 port 1812, id=133, length=47

    Reply-Message = "c-reply"
    Reply-Message = "a-group"
    Reply-Message = "b-group"


All in all:

We have TWO selects about TWO groups and we use Fall-Through for check all groups. All are working!


First select in debug:


GROUPNAME  ATTRIBUTE        VALUE     OP
a-group    Fall-Through       Yes         =
a-group    Reply-Message    a-group     +=

Second select:


GROUPNAME  ATTRIBUTE      VALUE      OP
b-group    Reply-Message   b-group +=



What do you think?


--
Yours faithfully,
Anton Borisov.

smime.p7s
Description: S/MIME Cryptographic Signature

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

Some SQL radgroupcheck/reply troubles.

Reply via email to