Touble configuring SQL data store for users

Bill Shaver bshaver at cis.ctc.edu
Sat Sep 15 05:34:40 CEST 2007


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 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
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 at 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 at 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 at 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





More information about the Freeradius-Users mailing list