Trouble configuring SQL data store for users (second attempt)

[Phil Mayers]

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 at 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 at cisi  password \
> 	localhost:1645 10 naspass
> will sucseed, while
> vm # /usr/bin/radtest -d /etc/raddb kparr at 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