<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style>
</head>
<body class='hmmessage'>
<META name=Generator content="Microsoft SafeHTML">
<STYLE>
.ExternalClass .ecxhmmessage P
{padding:0px;}
.ExternalClass body.ecxhmmessage
{font-size:10pt;font-family:Tahoma;}
</STYLE>
<SPAN id=ecxresult_box lang=en><SPAN title="">Hello!<BR><BR></SPAN><SPAN title="">I use FreeRadius version 2.1.7 x64, Cent OS 5.5 and MySQL 5.1.47 x64 14:14 Distrib. </SPAN><SPAN title="">I'm having the following problem:<BR><BR></SPAN><SPAN title="">For no apparent reason some logins simply disappear from the 'radusergroup'.<BR><BR></SPAN><SPAN title="">The login remains in the 'radcheck' but simply disappears from 'radusergroup'.<BR><BR></SPAN><SPAN title="">The FreeRadius for some reason can modify the table 'radusergroup'? </SPAN><SPAN title="">As I understand the FreeRadius modifies the table 'radacct' but not 'radusergroup'. </SPAN><SPAN title="">Am I wrong?</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""></SPAN></SPAN> <BR><SPAN lang=en><SPAN title="">Could be a configuration problem or a bug?</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""></SPAN></SPAN> <BR><SPAN lang=en><SPAN title="">Thanks!! Fabricio</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""></SPAN></SPAN> <BR><SPAN lang=en><SPAN title="">Below is my file dialup.conf (I made some modifications. If another file is needed I can also send it!):</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""></SPAN></SPAN> <BR><SPAN lang=en><SPAN title="">##<BR>## dialup.conf -- MySQL configuration for default schema (schema.sql)<BR>##<BR>## $Id$</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> # Safe characters list for sql queries. Everything else is replaced<BR> # with their mime-encoded equivalents.<BR> # The default list should be ok<BR> #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> #######################################################################<BR> # Query config: Username<BR> #######################################################################<BR> # This is the username that will get substituted, escaped, and added<BR> # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below<BR> # everywhere a username substitution is needed so you you can be sure<BR> # the username passed from the client is escaped properly.<BR> #<BR> # Uncomment the next line, if you want the sql_user_name to mean:<BR> #<BR> # Use Stripped-User-Name, if it's there.<BR> # Else use User-Name, if it's there,<BR> # Else use hard-coded string "DEFAULT" as the user name.<BR> #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}"<BR> #<BR> sql_user_name = "%{User-Name}"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> #######################################################################<BR> # Default profile<BR> #######################################################################<BR> # This is the default profile. It is found in SQL by group membership.<BR> # That means that this profile must be a member of at least one group<BR> # which will contain the corresponding check and reply items.<BR> # This profile will be queried in the authorize section for every user.<BR> # The point is to assign all users a default profile without having to<BR> # manually add each one to a group that will contain the profile.<BR> # The SQL module will also honor the User-Profile attribute. This<BR> # attribute can be set anywhere in the authorize section (ie the users<BR> # file). It is found exactly as the default profile is found.<BR> # If it is set then it will *overwrite* the default profile setting.<BR> # The idea is to select profiles based on checks on the incoming packets,<BR> # not on user group membership. For example:<BR> # -- users file --<BR> # DEFAULT Service-Type == Outbound-User, User-Profile := "outbound"<BR> # DEFAULT Service-Type == Framed-User, User-Profile := "framed"<BR> #<BR> # By default the default_user_profile is not set<BR> #<BR> #default_user_profile = "DEFAULT"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> #######################################################################<BR> # NAS Query<BR> #######################################################################<BR> # This query retrieves the radius clients<BR> #<BR> # 0. Row ID (currently unused)<BR> # 1. Name (or IP address)<BR> # 2. Shortname<BR> # 3. Type<BR> # 4. Secret<BR> #######################################################################<BR> # Use these for case sensitive usernames.<BR># authorize_check_query = "SELECT id, username, attribute, value, op \<BR># FROM ${authcheck_table} \<BR># WHERE username = BINARY '%{SQL-User-Name}' \<BR># ORDER BY id"<BR># authorize_reply_query = "SELECT id, username, attribute, value, op \<BR># FROM ${authreply_table} \<BR># WHERE username = BINARY '%{SQL-User-Name}' \<BR># ORDER BY id"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> # The default queries are case insensitive. (for compatibility with<BR> # older versions of FreeRADIUS)<BR> #authorize_check_query = "SELECT aut.id, aut.username, aut.attribute, aut.value, aut.op \<BR> # FROM ${authcheck_table} aut \<BR> # WHERE aut.username = '%{SQL-User-Name}' \<BR> # and aut.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> # ORDER BY aut.id"<BR> ######################################## alterado por Erick - 09/08/2010 ########################################<BR> authorize_check_query = "(SELECT rc.id, rc.username, rc.attribute, rc.value, rc.op \<BR> FROM ${authcheck_table} rc \<BR> WHERE rc.username = '%{SQL-User-Name}' \<BR> and rc.idrevenda = \<BR> (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> ) UNION ( \<BR> SELECT rgc.id, rc.username, rgc.attribute, rgc.value, rgc.op \<BR> FROM ${authcheck_table} rc, ${groupcheck_table} rgc, ${usergroup_table} rug \<BR> WHERE rc.username = '%{SQL-User-Name}' \<BR> and rc.idrevenda = \<BR> (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> and rc.username = rug.username \<BR> and rgc.groupname = rug.groupname)"<BR> #################################################################################################################</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""><BR> authorize_reply_query = "SELECT rp.id, rp.username, rp.attribute, rp.value, rp.op \<BR> FROM ${authreply_table} rp \<BR> WHERE rp.username = '%{SQL-User-Name}' \<BR> and rp.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> ORDER BY rp.id"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> # Use these for case sensitive usernames.<BR># group_membership_query = "SELECT groupname \<BR># FROM ${usergroup_table} \<BR># WHERE username = BINARY '%{SQL-User-Name}' \<BR># ORDER BY priority"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> group_membership_query = "SELECT groupname \<BR> FROM ${usergroup_table} ug \<BR> WHERE ug.username = '%{SQL-User-Name}' \<BR> and ug.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> ORDER BY ug.priority"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> authorize_group_check_query = "SELECT gc.id, gc.groupname, gc.attribute, \<BR> gc.Value, gc.op \<BR> FROM ${groupcheck_table} gc \<BR> WHERE gc.groupname = '%{Sql-Group}' \<BR> and gc.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> ORDER BY gc.id"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""><BR> authorize_group_reply_query = "SELECT gr.id, gr.groupname, gr.attribute, \<BR> gr.value, gr.op \<BR> FROM ${groupreply_table} gr \<BR> WHERE gr.groupname = '%{Sql-Group}' \<BR> and gr.idrevenda = (SELECT distinct n.idrevenda FROM ${nas_table} n WHERE n.nasname = '%{NAS-IP-Address}' LIMIT 0,1) \<BR> ORDER BY gr.id"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> #######################################################################<BR> # Accounting Queries<BR> #######################################################################<BR> # accounting_onoff_query - query for Accounting On/Off packets<BR> # accounting_update_query - query for Accounting update packets<BR> # accounting_update_query_alt - query for Accounting update packets<BR> # (alternate in case first query fails)<BR> # accounting_start_query - query for Accounting start packets<BR> # accounting_start_query_alt - query for Accounting start packets<BR> # (alternate in case first query fails)<BR> # accounting_stop_query - query for Accounting stop packets<BR> # accounting_stop_query_alt - query for Accounting start packets<BR> # (alternate in case first query doesn't<BR> # affect any existing rows in the table)<BR> #######################################################################<BR> accounting_onoff_query = "\<BR> UPDATE ${acct_table1} \<BR> SET \<BR> acctstoptime = '%S', \<BR> acctsessiontime = unix_timestamp('%S') - \<BR> unix_timestamp(acctstarttime), \<BR> acctterminatecause = '%{Acct-Terminate-Cause}', \<BR> acctstopdelay = %{%{Acct-Delay-Time}:-0} \<BR> WHERE acctstoptime IS NULL \<BR> AND nasipaddress = '%{NAS-IP-Address}' \<BR> AND acctstarttime <= '%S'"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> accounting_update_query = " \<BR> UPDATE ${acct_table1} \<BR> SET \<BR> framedipaddress = '%{Framed-IP-Address}', \<BR> acctsessiontime = '%{Acct-Session-Time}', \<BR> acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Input-Octets}:-0}', \<BR> acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Output-Octets}:-0}' \<BR> WHERE acctsessionid = '%{Acct-Session-Id}' \<BR> AND username = '%{SQL-User-Name}' \<BR> AND nasipaddress = '%{NAS-IP-Address}'"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> accounting_update_query_alt = " \<BR> INSERT INTO ${acct_table1} \<BR> (acctsessionid, acctuniqueid, username, \<BR> realm, nasipaddress, nasportid, \<BR> nasporttype, acctstarttime, acctsessiontime, \<BR> acctauthentic, connectinfo_start, acctinputoctets, \<BR> acctoutputoctets, calledstationid, callingstationid, \<BR> servicetype, framedprotocol, framedipaddress, \<BR> acctstartdelay, xascendsessionsvrkey) \<BR> VALUES \<BR> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<BR> '%{SQL-User-Name}', \<BR> '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \<BR> '%{NAS-Port-Type}', \<BR> DATE_SUB('%S', \<BR> INTERVAL (%{%{Acct-Session-Time}:-0} + \<BR> %{%{Acct-Delay-Time}:-0}) SECOND), \<BR> '%{Acct-Session-Time}', \<BR> '%{Acct-Authentic}', '', \<BR> '%{%{Acct-Input-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Input-Octets}:-0}', \<BR> '%{%{Acct-Output-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Output-Octets}:-0}', \<BR> '%{Called-Station-Id}', '%{Calling-Station-Id}', \<BR> '%{Service-Type}', '%{Framed-Protocol}', \<BR> '%{Framed-IP-Address}', \<BR> '0', '%{X-Ascend-Session-Svr-Key}')"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> accounting_start_query = " \<BR> INSERT INTO ${acct_table1} \<BR> (acctsessionid, acctuniqueid, username, \<BR> realm, nasipaddress, nasportid, \<BR> nasporttype, acctstarttime, acctstoptime, \<BR> acctsessiontime, acctauthentic, connectinfo_start, \<BR> connectinfo_stop, acctinputoctets, acctoutputoctets, \<BR> calledstationid, callingstationid, acctterminatecause, \<BR> servicetype, framedprotocol, framedipaddress, \<BR> acctstartdelay, acctstopdelay, xascendsessionsvrkey) \<BR> VALUES \<BR> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<BR> '%{SQL-User-Name}', \<BR> '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \<BR> '%{NAS-Port-Type}', '%S', NULL, \<BR> '0', '%{Acct-Authentic}', '%{Connect-Info}', \<BR> '', '0', '0', \<BR> '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \<BR> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<BR> '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> accounting_start_query_alt = " \<BR> UPDATE ${acct_table1} SET \<BR> acctstarttime = '%S', \<BR> acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \<BR> connectinfo_start = '%{Connect-Info}' \<BR> WHERE acctsessionid = '%{Acct-Session-Id}' \<BR> AND username = '%{SQL-User-Name}' \<BR> AND nasipaddress = '%{NAS-IP-Address}'"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> accounting_stop_query = " \<BR> UPDATE ${acct_table2} SET \<BR> acctstoptime = '%S', \<BR> acctsessiontime = '%{Acct-Session-Time}', \<BR> acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Input-Octets}:-0}', \<BR> acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Output-Octets}:-0}', \<BR> acctterminatecause = '%{Acct-Terminate-Cause}', \<BR> acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \<BR> connectinfo_stop = '%{Connect-Info}' \<BR> WHERE acctsessionid = '%{Acct-Session-Id}' \<BR> AND username = '%{SQL-User-Name}' \<BR> AND nasipaddress = '%{NAS-IP-Address}'"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> accounting_stop_query_alt = " \<BR> INSERT INTO ${acct_table2} \<BR> (acctsessionid, acctuniqueid, username, \<BR> realm, nasipaddress, nasportid, \<BR> nasporttype, acctstarttime, acctstoptime, \<BR> acctsessiontime, acctauthentic, connectinfo_start, \<BR> connectinfo_stop, acctinputoctets, acctoutputoctets, \<BR> calledstationid, callingstationid, acctterminatecause, \<BR> servicetype, framedprotocol, framedipaddress, \<BR> acctstartdelay, acctstopdelay) \<BR> VALUES \<BR> ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \<BR> '%{SQL-User-Name}', \<BR> '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \<BR> '%{NAS-Port-Type}', \<BR> DATE_SUB('%S', \<BR> INTERVAL (%{%{Acct-Session-Time}:-0} + \<BR> %{%{Acct-Delay-Time}:-0}) SECOND), \<BR> '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \<BR> '%{Connect-Info}', \<BR> '%{%{Acct-Input-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Input-Octets}:-0}', \<BR> '%{%{Acct-Output-Gigawords}:-0}' << 32 | \<BR> '%{%{Acct-Output-Octets}:-0}', \<BR> '%{Called-Station-Id}', '%{Calling-Station-Id}', \<BR> '%{Acct-Terminate-Cause}', \<BR> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \<BR> '0', '%{%{Acct-Delay-Time}:-0}')"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> #######################################################################<BR> # Simultaneous Use Checking Queries<BR> #######################################################################<BR> # simul_count_query - query for the number of current connections<BR> # - If this is not defined, no simultaneouls use checking<BR> # - will be performed by this module instance<BR> # simul_verify_query - query to return details of current connections for verification<BR> # - Leave blank or commented out to disable verification step<BR> # - Note that the returned field order should not be changed.<BR> #######################################################################</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> # Uncomment simul_count_query to enable simultaneous use checking<BR> simul_count_query = "SELECT COUNT(*) \<BR> FROM ${acct_table1} \<BR> WHERE username = '%{SQL-User-Name}' \<BR> AND acctstoptime IS NULL"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> simul_verify_query = "SELECT radacctid, acctsessionid, username, \<BR> nasipaddress, nasportid, framedipaddress, \<BR> callingstationid, framedprotocol \<BR> FROM ${acct_table1} \<BR> WHERE username = '%{SQL-User-Name}' \<BR> AND acctstoptime IS NULL"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> #######################################################################<BR> # Authentication Logging Queries<BR> #######################################################################<BR> # postauth_query - Insert some info after authentication<BR> #######################################################################</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""> postauth_query = "INSERT INTO ${postauth_table} \<BR> (username, pass, reply, authdate) \<BR> VALUES ( \<BR> '%{User-Name}', \<BR> '%{%{User-Password}:-%{Chap-Password}}', \<BR> '%{reply:Packet-Type}', '%S')"</SPAN></SPAN><BR><SPAN lang=en><SPAN title=""><BR> <BR><BR><BR></SPAN></SPAN> </body>
</html>