usergroup problems with separate auth and accounting databases
I have to mysql configurations for one for my authentication request and one for the accounting data. When it make a groupcheck query it is always using the module for the accounting server is there anyway to make this function correctly and have it use the configuration for the authentication database. Any Ideas of why this is happening Here some output while doing a request sql1 is the authentication DB and sql2 is the accounting rad_recv: Access-Request packet from host 127.0.0.1 port 2701, id=94, length=61 User-Name = "user@domain.net" CHAP-Password = 0x00000000000000000000000 +- entering group authorize {...} ++[preprocess] returns ok [chap] Setting 'Auth-Type := CHAP' ++[chap] returns ok ++[mschap] returns noop [suffix] Looking up realm "domain.net" for User-Name = "user@domain.net" [suffix] No such realm "vortexmail.com" ++[suffix] returns noop [sql1] expand: %{User-Name} -> user@domain.net [sql1] sql_set_user escaped user --> 'user@domain.net' rlm_sql (sql1): Reserving sql socket id: 4 [sql1] expand: SELECT .......................................... rlm_sql_mysql: query: SELECT ................................... [sql1] User found in radcheck table [sql1] expand: SELECT .......................................... rlm_sql_mysql: query: SELECT ................................... [sql1] expand: SELECT ........................................... rlm_sql_mysql: query: SELECT.................................... [sql1] expand: SELECT ........................................... rlm_sql_mysql: query: SELECT .................................. [sql1] sql_groupcmp [sql1] expand: %{User-Name} -> user@domain.net [sql1] sql_set_user escaped user --> 'user@domain.net' rlm_sql (sql2): Reserving sql socket id: 4 rlm_sql (sql2): Released sql socket id: 4 [sql1] sql_groupcmp finished: User is NOT a member of group active Invalid operator for item Sql-Group: reverting to '==' rlm_sql (sql1): Released sql socket id: 4 ++[sql1] returns ok [files] sql_groupcmp [files] expand: %{User-Name} -> user@domain.net [files] sql_set_user escaped user --> 'user@domain.net' rlm_sql (sql2): Reserving sql socket id: 3 rlm_sql (sql2): Released sql socket id: 3 [files] sql_groupcmp finished: User is NOT a member of group active [files] sql_groupcmp [files] expand: %{User-Name} -> user@domain.net [files] sql_set_user escaped user --> 'user@domain.net' rlm_sql (sql2): Reserving sql socket id: 2 rlm_sql (sql2): Released sql socket id: 2
Trey Scarborough wrote:
I have to mysql configurations for one for my authentication request and one for the accounting data. When it make a groupcheck query it is always using the module for the accounting server is there anyway to make this function correctly and have it use the configuration for the authentication database.
read doc/rlm_sql, or the "rlm_sql" page on the Wiki. This is documented. Alan DeKok.
Yes I am aware of how it is Documented I followed the documentation but still is not functioning correctly. I have a configuration that is similar to as follows sql sql1 { configuration for authentication database no accounting queries configured } sql sql2 { configuration for accounting database no authentication queries configured } #dose not work uses accounting sql2 for usergroup query authorize { sql1 files } accounting { detial sql } #configuration of groups works fine but I lose accounting sql uthorize { sql1 files } accounting { detial } Alan DeKok wrote:
Trey Scarborough wrote:
I have to mysql configurations for one for my authentication request and one for the accounting data. When it make a groupcheck query it is always using the module for the accounting server is there anyway to make this function correctly and have it use the configuration for the authentication database.
read doc/rlm_sql, or the "rlm_sql" page on the Wiki. This is documented.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Trey Scarborough wrote:
Yes I am aware of how it is Documented I followed the documentation but still is not functioning correctly.
I have a configuration that is similar to as follows
<sigh> Similar is not the same. Perhaps you could explain in *detail* what you are trying to do with SQL groups. Use examples from your cvonfiguration, not invented ones. Alan DeKok.
Alan DeKok wrote:
Trey Scarborough wrote:
Yes I am aware of how it is Documented I followed the documentation but still is not functioning correctly.
I have a configuration that is similar to as follows
<sigh> Similar is not the same.
Perhaps you could explain in *detail* what you are trying to do with SQL groups. Use examples from your cvonfiguration, not invented ones.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
All I am trying to do is run the radius auth querys on a database on one machine and the accounting on another in another database. The problem I am seeing is that when the additional sql configuration is put in for the accounting database it begins to use that configuration for the group_membership_query which is not in the accounting database and fails. If I remove the sql-auth from the accounting configuration it runs fine using the rad-auth sql configuration. Here is the exerts from my configuration. I am trying to set some radreply items with sql and some by the users file by group. This works fine until I try to seperate the databases. authorize { preprocess chap mschap suffix sql-auth files } accounting { detail radutmp sql-acct #works when this line is commented out } #sql.conf file sql sql-auth { driver = "rlm_sql_mysql" server = "localhost" login = "radius" password = "radpass" radius_db = "radius" postauth_table = "radpostauth" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" usergroup_table = "usergroup" nas_table = "nas" deletestalesessions = no sqltrace = no sqltracefile = ${logdir}/sqltrace.sql num_sql_socks = 5 connect_failure_retry_delay = 60 sql_user_name = "%{User-Name}" authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'" # # Set to 'yes' to read radius clients from the database ('nas' table) readclients = yes } sql sql-acct { driver = "rlm_sql_mysql" server = "192.168.5.84" login = "radius" password = "radpass" radius_db = "radius-acct" acct_table1 = "radacct" acct_table2 = "radacct" accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S', AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}' WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'" accounting_update_query = "UPDATE ${acct_table1} \ SET FramedIPAddress = '%{Framed-IP-Address}', \ AcctSessionTime = '%{Acct-Session-Time}', \ AcctInputOctets = '%{Acct-Input-Octets}', \ AcctOutputOctets = '%{Acct-Output-Octets}' \ WHERE AcctSessionId = '%{Acct-Session-Id}' \ AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress= '%{NAS-IP-Address}'" accounting_update_query_alt = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0')" accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')" accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}'" accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}'" accounting_stop_query_alt = "INSERT into ${acct_table2} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND), '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time}')" }
Trey Scarborough wrote:
All I am trying to do is run the radius auth querys on a database on one machine and the accounting on another in another database. The problem I am seeing is that when the additional sql configuration is put in for the accounting database it begins to use that configuration for the group_membership_query
Uh... no. Nothing in the SQL accounting configuration uses the group membership query. See the source code.
which is not in the accounting database and fails. If I remove the sql-auth from the accounting configuration it runs fine using the rad-auth sql configuration. Here is the exerts from my configuration. I am trying to set some radreply items with sql and some by the users file by group. This works fine until I try to seperate the databases.
Let me guess... you have policies for accounting which use "SQL-Group"? Alan DeKok.
Alan DeKok wrote:
Trey Scarborough wrote:
All I am trying to do is run the radius auth querys on a database on one machine and the accounting on another in another database. The problem I am seeing is that when the additional sql configuration is put in for the accounting database it begins to use that configuration for the group_membership_query
Uh... no. Nothing in the SQL accounting configuration uses the group membership query. See the source code.
Exactly my problem and why I don't understand why it breaks the authorization radius reply attributes.
which is not in the accounting database and fails. If I remove the sql-auth from the accounting configuration it runs fine using the rad-auth sql configuration. Here is the exerts from my configuration. I am trying to set some radreply items with sql and some by the users file by group. This works fine until I try to seperate the databases.
Let me guess... you have policies for accounting which use "SQL-Group"?
No It breaks the Authentication when I add the Accounting configuration
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Here is another more specific output from a debug It runs like this without the accounting configuration [sql-auth] sql_groupcmp [sql-auth] expand: %{User-Name} -> test@testdomain.net [sql-auth] sql_set_user escaped user --> 'test@testdomain.net' rlm_sql (sql-auth): Reserving sql socket id: 3 rlm_sql_mysql: query: SELECT GroupName FROM usergroup WHERE UserName='test@testdomain.net' [sql-auth] sql_groupcmp finished: User is a member of group active rlm_sql (sql-auth): Released sql socket id: 3 Runs like this when I add the rad-acct to accounting. It appears to be using the sql-acct for the sql_groupcmp for some reason. [sql-auth] sql_groupcmp [sql-auth] expand: %{User-Name} -> test@testdomain.net [sql-auth] sql_set_user escaped user --> 'test@testdomain.net' rlm_sql (sql-acct): Reserving sql socket id: 4 rlm_sql (sql-acct): Released sql socket id: 4 [sql-auth] sql_groupcmp finished: User is NOT a member of group active Any ideas as to why It would do this?
Trey Scarborough wrote:
Alan DeKok wrote: ...
Let me guess... you have policies for accounting which use "SQL-Group"?
No It breaks the Authentication when I add the Accounting configuration
Fine. You have *authentication* policies which use "SQL-Group". That's the issue. When there is *one* SQL module, the SQL-Group attribute refers only to it. When there are *two* SQL modules... which one does it refer to? That's the problem you're running into. The simple solution here is to use the "instantiate" section of radiusd.conf. List "sql-acct" first, and "sql-auth" section. That way, the SQL-Group comparison will use the "sql-auth" module, and not the "sql-acct" module. Alan DeKok.
Alan DeKok wrote:
The simple solution here is to use the "instantiate" section of radiusd.conf. List "sql-acct" first, and "sql-auth" section. That way, the SQL-Group comparison will use the "sql-auth" module, and not the "sql-acct" module.
Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thanks that fixed the problem I would have thought it would have been the other way sql_auth before sql-acct.
participants (2)
-
Alan DeKok -
Trey Scarborough