Trouble configuring SQL data store for users (second attempt)
Please forgive the duplicate post. I posted this a few days back and didn't see any response; thought I would give it just one more try. Thanks for any response. --Bill I have started to experiment with using mysql as the datastore for users and clients instead of the default file method for my relatively small installation. Right now my work is on a test system and all is working well, with one exception: a user that is a member of two or more groups. Based on all I have read, this last thing should be very basic. If I put the user in only groupA (in the usergroup table), the test works great. If I put user1 in only groupB, the test works great. When I put user1 in both groupA and groupB in the usergroup table it will only work against the first record of the two, the second record always returns a failure. I am sure this is probably something really stupid, but I just cannot see it. Any help would be appreciated. I have attatched table dumps, sample commands, and a debug trace. I hope it is helpful Thanks, --Bill FreeRadius version 1.0.1 MySQL version 4.1.20 vm # /usr/bin/radtest -d /etc/raddb kparr@cisi password \ localhost:1645 10 naspass will sucseed, while vm # /usr/bin/radtest -d /etc/raddb kparr@syst password \ localhost:1645 10 naspass fails, but should sucseed The following is a test data set to validate a variety of cases that we need to support in our environment. select * from radcheck into outfile '/tmp/f1'; -------------------------------------------------------- id username attribute op value -- -------- --------- -- ----- 1 bill Password == userpass 5 guest01 Auth-Type := Local 6 guest01 Password == password select * from radreply into outfile '/tmp/f4'; -------------------------------------------------------- id username attribute op value -- -------- --------- -- ----- 7 guest01 Class := OU=Wireless; 8 guest01 Fall-Through := No select * from radgroupcheck into outfile '/tmp/f2'; -------------------------------------------------------- id groupname attribute op value -- -------- --------- -- ----- 6 LocalUnix Auth-Type == System 7 LocalUnix Realm == Test 9 LdapCiscoAdm Password == password 10 LdapCiscoAdm Auth-Type == Local 11 LdapCiscoAdm Realm == cisi 12 LdapHpReho Realm == syst 13 LdapHpReho Auth-Type == Local 14 LdapHpReho Password == password 15 Rejected Auth-Type := Reject select * from radgroupreply into outfile '/tmp/f3'; -------------------------------------------------------- id groupname attribute op value -- -------- --------- -- ----- 8 LocalUnix Service-Type = Login 0 9 LdapCiscoAdm Cisco-AVPair = shell:priv-lvl=15 0 10 LdapCiscoAdm Class := OU=cis; 0 11 LdapCiscoAdm Fall-Through := Yes 0 12 LdapCiscoAdm Service-Type = 6 0 13 LdapHpReho Class := OU=Proj; 0 14 LdapHpReho Fall-Through := Yes 0 15 Rejected Fall-Through := No 0 17 Rejected Reply-Message := Account is locked out. 0 select * from usergroup into outfile '/tmp/f5'; -------------------------------------------------------- id username groupname -- -------- --------- 9 root LocalUnix 10 kparr LdapCiscoAdm 11 kchow LdapHpReho 12 jpage Rejected 13 kparr LdapHpReho 14 bshaver LdapCiscoAdm -------------------------------------------------------- vm # radiusd -x Starting - reading configuration files ... Module: Loaded exec Module: Instantiated exec (exec) Module: Loaded expr Module: Instantiated expr (expr) Module: Loaded PAP Module: Instantiated pap (pap) Module: Loaded CHAP Module: Instantiated chap (chap) Module: Loaded MS-CHAP Module: Instantiated mschap (mschap) Module: Loaded System Module: Instantiated unix (unix) Module: Loaded eap rlm_eap: Loaded and initialized type md5 rlm_eap: Loaded and initialized type leap rlm_eap: Loaded and initialized type gtc rlm_eap: Loaded and initialized type mschapv2 Module: Instantiated eap (eap) Module: Loaded preprocess Module: Instantiated preprocess (preprocess) Module: Loaded realm Module: Instantiated realm (suffix) Module: Loaded files Module: Instantiated files (files) Module: Loaded SQL rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked rlm_sql (sql): Attempting to connect to root@localhost:/radius rlm_sql (sql): starting 0 rlm_sql (sql): Attempting to connect rlm_sql_mysql #0 rlm_sql_mysql: Starting connect to MySQL server for #0 rlm_sql (sql): Connected new DB handle, #0 rlm_sql (sql): starting 1 rlm_sql (sql): Attempting to connect rlm_sql_mysql #1 rlm_sql_mysql: Starting connect to MySQL server for #1 rlm_sql (sql): Connected new DB handle, #1 rlm_sql (sql): starting 2 rlm_sql (sql): Attempting to connect rlm_sql_mysql #2 rlm_sql_mysql: Starting connect to MySQL server for #2 rlm_sql (sql): Connected new DB handle, #2 rlm_sql (sql): starting 3 rlm_sql (sql): Attempting to connect rlm_sql_mysql #3 rlm_sql_mysql: Starting connect to MySQL server for #3 rlm_sql (sql): Connected new DB handle, #3 rlm_sql (sql): starting 4 rlm_sql (sql): Attempting to connect rlm_sql_mysql #4 rlm_sql_mysql: Starting connect to MySQL server for #4 rlm_sql (sql): Connected new DB handle, #4 Module: Instantiated sql (sql) Module: Loaded Acct-Unique-Session-Id Module: Instantiated acct_unique (acct_unique) Module: Loaded detail Module: Instantiated detail (detail) Module: Loaded radutmp Module: Instantiated radutmp (radutmp) Initializing the thread pool... Listening on authentication *:1645 Listening on accounting *:1646 Listening on proxy *:1647 Ready to process requests. rad_recv: Access-Request packet from host 127.0.0.1:32773, id=23, length=62 User-Name = "kparr@cisi" User-Password = "password" NAS-IP-Address = 255.255.255.255 NAS-Port = 10 rlm_sql (sql): Reserving sql socket id: 4 rlm_sql (sql): User kparr not found in radcheck rlm_sql (sql): Released sql socket id: 4 Sending Access-Accept of id 23 to 127.0.0.1:32773 Cisco-AVPair = "shell:priv-lvl=15" Class := 0x4f553d6369733b Service-Type = Administrative-User rad_recv: Access-Request packet from host 127.0.0.1:32773, id=27, length=62 User-Name = "kparr@syst" User-Password = "password" NAS-IP-Address = 255.255.255.255 NAS-Port = 10 rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): User kparr not found in radcheck rlm_sql (sql): No matching entry in the database for request from user [kparr] rlm_sql (sql): Released sql socket id: 3 rad_recv: Access-Request packet from host 127.0.0.1:32773, id=27, length=62 Sending Access-Reject of id 27 to 127.0.0.1:32773
On Mon, 2007-09-17 at 20:30 -0700, Bill Shaver wrote:
Please forgive the duplicate post. I posted this a few days back and didn't see any response; thought I would give it just one more try. Thanks for any response. --Bill
I have started to experiment with using mysql as the datastore for users and clients instead of the default file method for my relatively small installation. Right now my work is on a test system and all is working well, with one exception: a user that is a member of two or more groups. Based on all I have read, this last thing should be very basic.
If I put the user in only groupA (in the usergroup table), the test works great. If I put user1 in only groupB, the test works great. When I put user1 in both groupA and groupB in the usergroup table it will only work against the first record of the two, the second record always returns a failure.
Versions of FreeRadius prior to 2.0pre/CVS handle multiple groups a little bit oddly. The SQL code merges the check items for the user (from radcheck) and all their groups (from radgroupcheck) into one check list, compares them all, then if they all match, merges the reply items for the user (from radreply) and all their groups (from radgroupreply) into one set of reply items, which are added to the reply. So having a user in two groups with conflicting check or reply items doesn't work. This is annoying, since it's usually what you want to do! In FreeRadius 2.x the SQL groups work as expected. The user check/reply items are processed, then the groups are processed in order of precedence, processing stopping the first time Fall-Through==no (the default). The easiest way to get around this is in 1.x to modify the group membership SQL query to include more than just the username as the "key". I like to refer to this as "netgroup-like". For example, you might set: group_membership_query = " select groupname from netgroup where \ username='%{SQL-User-Name}' and \ realm='%{Realm}'" In SQL: create table netgroup (username varchar(253), realm varchar(253), groupname varchar(253), primary key (username,realm)); insert into netgroup values ('kpass','cisi','CiscoAdm'); insert into netgroup values ('kpass','syst','LdapHpReho'); This is a very simple example, and can in fact be accomplished without changing anything; just change the SQL-User-Name to be User-Name as opposed to Stripped-User-Name and put the user@realm in the user column. However, there are endless variations on this scheme, including putting things such as: * Service-Type * Calling-Station-Id * Called-Station-Id * Huntgroup-Name * etc. ...in the "key" to the group table.
I am sure this is probably something really stupid, but I just cannot see it. Any help would be appreciated.
I have attatched table dumps, sample commands, and a debug trace. I hope it is helpful
Thanks, --Bill
FreeRadius version 1.0.1
This is ancient. Upgrade to 1.1.7
MySQL version 4.1.20
vm # /usr/bin/radtest -d /etc/raddb kparr@cisi password \ localhost:1645 10 naspass will sucseed, while vm # /usr/bin/radtest -d /etc/raddb kparr@syst password \ localhost:1645 10 naspass fails, but should sucseed
The following is a test data set to validate a variety of cases that we need to support in our environment.
select * from radcheck into outfile '/tmp/f1'; -------------------------------------------------------- id username attribute op value -- -------- --------- -- ----- 1 bill Password == userpass 5 guest01 Auth-Type := Local
Once you've upgraded to 1.1.7, you can stop setting Auth-Type to Local, and stop using password comparison. Instead, do: Cleartext-Password := value ...and ensure the PAP module is at the bottom of the authorize section. Then it'll a) magically work and b) be the recommended way of doing it.
6 guest01 Password == password
select * from radreply into outfile '/tmp/f4'; -------------------------------------------------------- id username attribute op value -- -------- --------- -- ----- 7 guest01 Class := OU=Wireless; 8 guest01 Fall-Through := No
As above, Fall-Through is irrelevant on SQL groups in FreeRadius 1.x.
select * from radgroupcheck into outfile '/tmp/f2'; -------------------------------------------------------- id groupname attribute op value -- -------- --------- -- ----- 6 LocalUnix Auth-Type == System 7 LocalUnix Realm == Test 9 LdapCiscoAdm Password == password 10 LdapCiscoAdm Auth-Type == Local 11 LdapCiscoAdm Realm == cisi 12 LdapHpReho Realm == syst 13 LdapHpReho Auth-Type == Local 14 LdapHpReho Password == password 15 Rejected Auth-Type := Reject
select * from radgroupreply into outfile '/tmp/f3'; -------------------------------------------------------- id groupname attribute op value -- -------- --------- -- ----- 8 LocalUnix Service-Type = Login 0 9 LdapCiscoAdm Cisco-AVPair = shell:priv-lvl=15 0 10 LdapCiscoAdm Class := OU=cis; 0 11 LdapCiscoAdm Fall-Through := Yes 0 12 LdapCiscoAdm Service-Type = 6 0 13 LdapHpReho Class := OU=Proj; 0 14 LdapHpReho Fall-Through := Yes 0 15 Rejected Fall-Through := No 0 17 Rejected Reply-Message := Account is locked out. 0
select * from usergroup into outfile '/tmp/f5'; -------------------------------------------------------- id username groupname -- -------- --------- 9 root LocalUnix 10 kparr LdapCiscoAdm 11 kchow LdapHpReho 12 jpage Rejected 13 kparr LdapHpReho 14 bshaver LdapCiscoAdm
-------------------------------------------------------- vm # radiusd -x
The generally recommended command to show output for is "radiusd -X" (upper case). HTH
participants (2)
-
Bill Shaver -
Phil Mayers