rlm_sql and read_groups

11 Jul 2011

      We're trying to get FreeRADIUS to get at the user info in our Oracle DB, and it does not appear to be respecting the read_groups = yes setting in sql.conf.

Forex:
[sql] WARNING: Deprecated conditional expansion ":-".  See "man unlang" for details
[sql] 	... expanding second conditional
[sql] WARNING: Deprecated conditional expansion ":-".  See "man unlang" for details
[sql] 	expand: %{User-Name:-DEFAULT} -> dawson@vt.edu
[sql] 	expand: %{Stripped-User-Name:-%{User-Name:-DEFAULT}} -> dawson@vt.edu
[sql] sql_set_user escaped user --> 'dawson@vt.edu'
rlm_sql (sql): Reserving sql socket id: 4
[sql] 	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 = 'dawson@vt.edu' ORDER BY id
SELECT id, username, Attribute, Value, op FROM radcheck WHERE Username = 'dawson@vt.edu' ORDER BY id
[sql] User found in radcheck table
rlm_sql (sql): Released sql socket id: 4
++[sql] returns ok

That's all well and good, and I understand that this is normal behavior if you don't set read_groups to yes, but that's decidedly unhelpful behavior when you also want to confirm group memberships.

Here's the related output from when FR loads the sql module:

 Module: Linked to module rlm_sql
 Module: Instantiating module "sql" from file /usr/local/freeradius-2.1.11/etc/raddb/sql.conf
  sql {
	driver = "rlm_sql_oracle"
	server = "<redacted>"
	port = "<redacted>"
	login = "<redacted>"
	password = "<redacted>"
	radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<redacted>)(PORT=<redacted>))(CONNECT_DATA=(SID=<redacted>)))"
	read_groups = yes
	sqltrace = yes
	sqltracefile = "/usr/local/freeradius-2.1.11/var/log/radius/sqltrace.sql"
	readclients = no
	deletestalesessions = yes
	num_sql_socks = 5
	lifetime = 0
	max_queries = 0
	sql_user_name = "%{Stripped-User-Name:-%{User-Name:-DEFAULT}}"
	default_user_profile = ""
	nas_query = "SELECT id,nasname,shortname,type,secret FROM nas"
	authorize_check_query = "SELECT id, username, Attribute, Value, op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id"
	authorize_group_check_query = "SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  FROM radgroupcheck,radusergroup WHERE radusergroup.Username = '%{SQL-User-Name}' AND radusergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id"
	authorize_group_reply_query = ""
	accounting_onoff_query = ""
	accounting_update_query = ""
	accounting_update_query_alt = ""
	accounting_start_query = ""
	accounting_start_query_alt = ""
	accounting_stop_query = ""
	accounting_stop_query_alt = ""
	connect_failure_retry_delay = 60
	simul_count_query = ""
	simul_verify_query = ""
	postauth_query = ""
	safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
  }

Did I miss something, or is this misbehaving?

Thanks much,
- Jacob M. Dawson

rlm_sql and read_groups

Jacob Dawson