rlm_sql and read_groups
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
On Tue, Jul 12, 2011 at 2:59 PM, Phil Mayers <p.mayers@imperial.ac.uk> wrote:
On 07/11/2011 10:59 PM, Jacob Dawson wrote:
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.
Are you setting "Fall-Through = Yes" in radreply?
Shouldn't setting "read_groups='yes'" be enough? The documentation says so, and it works for mysql. -- Fajar
On 07/12/2011 09:21 AM, Fajar A. Nugraha wrote:
On Tue, Jul 12, 2011 at 2:59 PM, Phil Mayers<p.mayers@imperial.ac.uk> wrote:
On 07/11/2011 10:59 PM, Jacob Dawson wrote:
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.
Are you setting "Fall-Through = Yes" in radreply?
Shouldn't setting "read_groups='yes'" be enough? The documentation says so, and it works for mysql.
AFAICT the source code checks for and honours Fall-Through in the radreply/radgroupreply variables, and assumes "no" if it's absent. I don't have time to give the code a re-read/test right now.
I'll have the time to test it today, but according to this comment in sql.conf, I shouldn't have to set that, and I'd prefer not to have to set it on every user in production. # If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table read_groups = yes - Jacob On 12 Jul 2011, at 04:45, Phil Mayers wrote:
On 07/12/2011 09:21 AM, Fajar A. Nugraha wrote:
On Tue, Jul 12, 2011 at 2:59 PM, Phil Mayers<p.mayers@imperial.ac.uk> wrote:
On 07/11/2011 10:59 PM, Jacob Dawson wrote:
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.
Are you setting "Fall-Through = Yes" in radreply?
Shouldn't setting "read_groups='yes'" be enough? The documentation says so, and it works for mysql.
AFAICT the source code checks for and honours Fall-Through in the radreply/radgroupreply variables, and assumes "no" if it's absent.
I don't have time to give the code a re-read/test right now. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 12/07/11 13:34, Jacob Dawson wrote:
I'll have the time to test it today, but according to this comment in sql.conf, I shouldn't have to set that, and I'd prefer not to have to set it on every user in production.
# If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table read_groups = yes
Oops, yes, quite right. Upon closer inspection, fallthrough is only inspected if "read_groups == no", and defaults to "on". I think your problem is that "group_membership_query" is unset, looking at your original email and the debug output, hence no groups are found.
That doesn't make a lot of sense from my quick skim of the config and the code, as I don't see anywhere that group_membership_query is referenced, but it definitely triggered FR to respect the read_groups setting. - Jacob On 12 Jul 2011, at 08:59, Phil Mayers wrote:
On 12/07/11 13:34, Jacob Dawson wrote:
I'll have the time to test it today, but according to this comment in sql.conf, I shouldn't have to set that, and I'd prefer not to have to set it on every user in production.
# If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table read_groups = yes
Oops, yes, quite right. Upon closer inspection, fallthrough is only inspected if "read_groups == no", and defaults to "on".
I think your problem is that "group_membership_query" is unset, looking at your original email and the debug output, hence no groups are found. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On 12/07/11 14:29, Jacob Dawson wrote:
That doesn't make a lot of sense from my quick skim of the config and the code, as I don't see anywhere that group_membership_query is
group_membership_query is defined in the default configs; or is that not what you mean? The call graph is as follows: rlm_sql_authorize rlm_sql_process_group sql_get_grouplist ...and in sql_get_grouplist we have: if (!inst->config->groupmemb_query || (inst->config->groupmemb_query[0] == 0)) return 0;
referenced, but it definitely triggered FR to respect the read_groups setting.
So it's working now?
Didn't look for abbreviated forms of the name, so I didn't see that. And I'd say that 'working' is too strong a term, but I haven't determined what's causing the latest failure, as yet. At least it's querying the groups tables, so we're on to new errors, and those are like progress. - Jacob On 12 Jul 2011, at 09:42, Phil Mayers wrote:
On 12/07/11 14:29, Jacob Dawson wrote:
That doesn't make a lot of sense from my quick skim of the config and the code, as I don't see anywhere that group_membership_query is
group_membership_query is defined in the default configs; or is that not what you mean?
The call graph is as follows:
rlm_sql_authorize rlm_sql_process_group sql_get_grouplist
...and in sql_get_grouplist we have:
if (!inst->config->groupmemb_query || (inst->config->groupmemb_query[0] == 0)) return 0;
referenced, but it definitely triggered FR to respect the read_groups setting.
So it's working now? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Found the source of my problem, thanks to your pointer. While it doesn't seem to matter if FreeRADIUS gets any results from the authorize_group_reply_query, sql.conf requires that it be configured and run in order to be happy. Since we have, in this case, no reply attributes to set (those are only a sometimes thing for us, and not happening the current batch of work we're doing), it returns nothing when it runs the query, but continues happily and authorizes the user. Also, if there's interest, I can submit the oracle-ized version of the schema that we created. The one included in the source users non-oracle variable types and a few incorrect restrictions (Several items are set unique when, logically, they should not be). Thanks much, - Jacob On 12 Jul 2011, at 09:49, Jacob Dawson wrote:
Didn't look for abbreviated forms of the name, so I didn't see that.
And I'd say that 'working' is too strong a term, but I haven't determined what's causing the latest failure, as yet. At least it's querying the groups tables, so we're on to new errors, and those are like progress.
- Jacob
On 12 Jul 2011, at 09:42, Phil Mayers wrote:
On 12/07/11 14:29, Jacob Dawson wrote:
That doesn't make a lot of sense from my quick skim of the config and the code, as I don't see anywhere that group_membership_query is
group_membership_query is defined in the default configs; or is that not what you mean?
The call graph is as follows:
rlm_sql_authorize rlm_sql_process_group sql_get_grouplist
...and in sql_get_grouplist we have:
if (!inst->config->groupmemb_query || (inst->config->groupmemb_query[0] == 0)) return 0;
referenced, but it definitely triggered FR to respect the read_groups setting.
So it's working now? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Also, if there's interest, I can submit the oracle-ized version of the schema that we created. The one included in the source users non-oracle variable types and a few incorrect restrictions (Several items are set unique when, logically, they should not be). Thanks much,
Sure, could you put a patch together and send a pull request via GitHub. If you can't figure out git/github I can put some instructions together on the wiki. -Arran Arran Cudbard-Bell a.cudbardb@freeradius.org RADIUS - Half the complexity of Diameter
I spent about an hour on it, and while I seem to be pretty close, instructions on the wiki would be handy, particularly as it relates to generating the patch and the best way to submit it. - Jacob On 12 Jul 2011, at 10:17, Arran Cudbard-Bell wrote:
Also, if there's interest, I can submit the oracle-ized version of the schema that we created. The one included in the source users non-oracle variable types and a few incorrect restrictions (Several items are set unique when, logically, they should not be). Thanks much,
Sure, could you put a patch together and send a pull request via GitHub. If you can't figure out git/github I can put some instructions together on the wiki.
-Arran
Arran Cudbard-Bell a.cudbardb@freeradius.org
RADIUS - Half the complexity of Diameter
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 12, 2011, at 7:33 PM, Jacob Dawson wrote:
I spent about an hour on it, and while I seem to be pretty close, instructions on the wiki would be handy, particularly as it relates to generating the patch and the best way to submit it.
Ok, its up here http://wiki.freeradius.org/github, let me know if you run into issues. -Arran Arran Cudbard-Bell a.cudbardb@freeradius.org RADIUS - Half the complexity of Diameter
Bug 166 has a patch for this. Noticed it didn't seem to be failing if the user was found, but wasn't in any groups, even though I instructed it to check for groups. That's incorrect behavior in my case (plenty of users who were authorized at one time, but are no longer) and it seems to stem from a gap in the logic, where it found the user, set found=1, then didn't have a case to handle rows == 0, and so would move through the function, reach the end with found=1, and succeed, when it should have failed. Bug 167 has two patches for this, really jus the result of testing it once and then applying the change in a couple other places, so two commits were involved. The change DOES seem to provide the desired behavior if the user exists but isn't in a valid group. - Jacob On 12 Jul 2011, at 10:17, Arran Cudbard-Bell wrote:
Also, if there's interest, I can submit the oracle-ized version of the schema that we created. The one included in the source users non-oracle variable types and a few incorrect restrictions (Several items are set unique when, logically, they should not be). Thanks much,
Sure, could you put a patch together and send a pull request via GitHub. If you can't figure out git/github I can put some instructions together on the wiki.
-Arran
Arran Cudbard-Bell a.cudbardb@freeradius.org
RADIUS - Half the complexity of Diameter
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
Arran Cudbard-Bell -
Fajar A. Nugraha -
Jacob Dawson -
Phil Mayers