Hi,<br><br>I am using FreeRADIUS 2.1.10 to write accounting data to a postgres database. The usernames are email addresses and therefore might contain characters that are not included in the default safe-characters list (for example, '+'). I am also writing user-agents, which contain other characters not in the default list.<br>
<br>I have modified the safe-characters list in the DB conf for that server and the logs suggest that this change has been picked-up, but when I fire some test packets at the server with radclient I find that the additional characters are still being escaped. I have also tried making the same change in sql/postgres/dialup.conf, but this does not work either.<div>
<br></div><div>Excerpts from config and logs are below.</div><div><br></div><div>Where am I going wrong with this?</div><div><br></div><div>Thanks,</div><div>Jack P.</div><div><br></div><div><font face="courier new, monospace">DB conf</font></div>
<div><font face="courier new, monospace">==========</font></div><div><font face="courier new, monospace"><div>database = "postgresql"</div><div>driver = "rlm_sql_postgresql"</div><div>server = "..."</div>
<div>login = "..."</div><div>password = "..."</div><div>radius_db = "..."</div><div>sqltrace = no</div><div>num_sql_socks = 5</div><div>connect_failure_retry_delay = 60</div><div>lifetime = 0</div>
<div>max_queries = 0</div><div>safe-characters = "+();@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"</div><div><br></div><div><div>SQL module definition</div><div style="font-family:arial">
<font face="courier new, monospace">=====================</font></div></div><div><font face="courier new, monospace"><div>sql ucaaa_accounting_sql {</div><div> $INCLUDE db_connection.ini</div><div> </div><div> sql_user_name = "%{%{Stripped-User-Name}:-%{User-Name}}"</div>
<div><br></div><div> accounting_start_query = "SELECT * FROM radius_accounting_start ( \</div><div> '%{Tmp-String-0}', \</div><div> '%{SQL-User-Name}', \</div><div> '%{Framed-IP-Address}', \</div>
<div> '%{NAS-IP-Address}', \</div><div> '%{Tmp-String-1}', \</div><div> '%{NAS-Identifier}', \</div><div> '%{Called-Station-Id}', \</div><div> '%{Connect-Info}', \</div>
<div> '%{Calling-Station-Id}', \</div><div> '%{%{Acct-Delay-Time}:-0}', \</div><div> '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \</div><div> '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \</div>
<div> '%{%{Acct-Input-Packets}:-0}', \</div><div> '%{%{Acct-Output-Packets}:-0}', \</div><div> '%{%{Acct-Session-Time}:-0}', \</div><div> '%{Acct-Session-Id}', \</div><div>
'%{Venue-Id}')"</div><div>...</div><div>}</div><div><br></div><div>Log output</div><div>==========</div><div>...</div><div>...</div><div><div>server ucaaa { # from file /etc/raddb/sites-enabled/ucaaa</div>
<div> modules {</div><div> Module: Checking authenticate {...} for more modules to load</div><div> Module: Checking authorize {...} for more modules to load</div><div> Module: Instantiating module "ucaaa" from file /etc/raddb/modules/ucaaa</div>
<div> python ucaaa {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>mod_instantiate = "ucaaa"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>func_instantiate = "instantiate"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>mod_authorize = "ucaaa"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>func_authorize = "authorize"</div><div> }</div>
<div>Loading UCAAA</div><div>Readying configuration</div><div>UCAAA ready</div><div> Module: Checking accounting {...} for more modules to load</div><div> Module: Instantiating module "ucaaa_accounting_sql" from file /etc/raddb/modules/ucaaa_accounting_sql</div>
<div> sql ucaaa_accounting_sql {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>driver = "rlm_sql_postgresql"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>server = "..."</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>port = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>login = "..."</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>password = "...."</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>radius_db = "..."</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>read_groups = yes</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>sqltrace = no</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>sqltracefile = "/var/log/radius/ucaaa.sql"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>readclients = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>deletestalesessions = yes</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>num_sql_socks = 5</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>lifetime = 0</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>max_queries = 0</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>sql_user_name = "%{%{Stripped-User-Name}:-%{User-Name}}"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>default_user_profile = ""</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>nas_query = "SELECT id,nasname,shortname,type,secret FROM nas"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_check_query = ""</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_group_check_query = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_group_reply_query = ""</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_onoff_query = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_update_query = "SELECT * FROM radius_accounting_update ( '%{Acct-Session-Id}', '%{NAS-Identifier}', '%{%{Acct-Delay-Time}:-0}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{Acct-Input-Packets}', '%{Acct-Output-Packets}', '%{Acct-Session-Time}')"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_update_query_alt = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_start_query = "SELECT * FROM radius_accounting_start ( '%{Tmp-String-0}', '%{SQL-User-Name}', '%{Framed-IP-Address}', '%{NAS-IP-Address}', '%{Tmp-String-1}', '%{NAS-Identifier}', '%{Called-Station-Id}', '%{Connect-Info}', '%{Calling-Station-Id}', '%{%{Acct-Delay-Time}:-0}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{%{Acct-Input-Packets}:-0}', '%{%{Acct-Output-Packets}:-0}', '%{%{Acct-Session-Time}:-0}', '%{Acct-Session-Id}', '%{Venue-Id}')"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_start_query_alt = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_stop_query = "SELECT * FROM radius_accounting_stop ( '%{Acct-Session-Id}', '%{NAS-Identifier}', '%{%{Acct-Delay-Time}:-0}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{Acct-Input-Packets}', '%{Acct-Output-Packets}', '%{Acct-Session-Time}', '%{Tmp-String-2}')"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_stop_query_alt = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>connect_failure_retry_delay = 60</div><div>
<span class="Apple-tab-span" style="white-space:pre"> </span>simul_count_query = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>simul_verify_query = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>postauth_query = ""</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>safe-characters = "+();@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"</div><div> }</div><div>rlm_sql Creating new attribute ucaaa_accounting_sql-SQL-Group</div>
<div>rlm_sql (ucaaa_accounting_sql): Driver rlm_sql_postgresql (module rlm_sql_postgresql) loaded and linked</div><div>rlm_sql (ucaaa_accounting_sql): Attempting to connect to xx@xxx.xxx.xxx.xxx:/xx</div><div>rlm_sql (ucaaa_accounting_sql): starting 0</div>
<div>rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #0</div><div>rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #0</div><div>rlm_sql (ucaaa_accounting_sql): starting 1</div><div>rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #1</div>
<div>rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #1</div><div>rlm_sql (ucaaa_accounting_sql): starting 2</div><div>rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #2</div><div>rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #2</div>
<div>rlm_sql (ucaaa_accounting_sql): starting 3</div><div>rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #3</div><div>rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #3</div><div>rlm_sql (ucaaa_accounting_sql): starting 4</div>
<div>rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #4</div><div>rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #4</div><div> } # modules</div><div>} # server</div></div><div>...</div><div>
...</div><div><div>Listening on authentication address * port 1812</div><div>Listening on accounting address * port 1813</div><div>Listening on command file /var/run/radiusd/radiusd.sock</div><div>Listening on proxy address * port 1814</div>
<div>Ready to process requests.</div><div>rad_recv: Accounting-Request packet from host 127.0.0.1 port 56133, id=151, length=320</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Acct-Status-Type = Start</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-IP-Address = 10.5.10.68</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Framed-IP-Address = 127.0.0.1</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Acct-Session-Id = "114954251"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-Port-Type = Ethernet</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-Port-Id = "BNG-A 1/1/1:40"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>User-Name = "REALM/<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a>"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>Venue-Id = 2003174</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>WISPr-Billing-Class-Of-Service = "pt=FREE, nai=226645"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>Calling-Station-Id = "f8:1e:df:dd:02:d4"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-Identifier = "2005264-Park Hotel"</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>Called-Station-Id = "00-15-C5-E7-B5-E4"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Connect-Info = "Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; sv-SE; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12"</div>
<div># Executing section preacct from file /etc/raddb/sites-enabled/default</div><div>+- entering group preacct {...}</div><div>++[preprocess] returns ok</div><div>[acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent</div>
<div>[acct_unique] Hashing ',Client-IP-Address = 127.0.0.1,NAS-IP-Address = 10.5.10.68,Acct-Session-Id = "114954251",User-Name = "REALM/<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a>"'</div>
<div>[acct_unique] Acct-Unique-Session-ID = "00f4dfc71e21d2a8".</div><div>++[acct_unique] returns ok</div><div>[IPASS] Looking up realm "REALM" for User-Name = "REALM/<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a>"</div>
<div>[IPASS] Found realm "REALM"</div><div>[IPASS] Adding Stripped-User-Name = "<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a>"</div><div>[IPASS] Adding Realm = "REALM"</div>
<div>[IPASS] Proxying request from user <a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a> to realm REALM</div><div>[IPASS] Preparing to proxy accounting request to realm "REALM" </div><div>
++[IPASS] returns updated</div><div>[suffix] Request already proxied. Ignoring.</div><div>++[suffix] returns ok</div><div>++[files] returns noop</div><div># Executing section accounting from file /etc/raddb/sites-enabled/default</div>
<div>+- entering group accounting {...}</div><div>[detail] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/radius/radacct/<a href="http://127.0.0.1/detail-20120530">127.0.0.1/detail-20120530</a></div>
<div>[detail] /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/radius/radacct/<a href="http://127.0.0.1/detail-20120530">127.0.0.1/detail-20120530</a></div><div>[detail] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %t -> Wed May 30 04:08:40 2012</div>
<div>++[detail] returns ok</div><div>++[unix] returns noop</div><div>[radutmp] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: /var/log/radius/radutmp -> /var/log/radius/radutmp</div><div>[radutmp] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{User-Name} -> REALM/<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a></div>
<div> rlm_radutmp: No NAS-Port seen. Cannot do anything.</div><div> rlm_radumtp: WARNING: checkrad will probably not work!</div><div>++[radutmp] returns noop</div><div>[attr_filter.accounting_response] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{User-Name} -> REALM/<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a></div>
<div> attr_filter: Matched entry DEFAULT at line 12</div><div>++[attr_filter.accounting_response] returns updated</div><div> WARNING: Empty pre-proxy section. Using default return values.</div><div>>>> Sending proxied request internally to virtual server.</div>
<div>server ucaaa {</div><div> WARNING: Empty preacct section. Using default return values.</div><div># Executing section accounting from file /etc/raddb/sites-enabled/ucaaa</div><div>+- entering group accounting {...}</div>
<div>++? if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i)</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{WISPr-Billing-Class-Of-Service} -> pt=FREE, nai=226645</div>
<div>? Evaluating ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) -> TRUE</div><div>++? if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) -> TRUE</div><div>++- entering if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) {...}</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{1} -> 226645</div><div>+++[request] returns noop</div><div>++- if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) returns noop</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{NAS-Port-Type} -> Ethernet</div><div>++- entering switch %{NAS-Port-Type} {...}</div><div>+++- entering case Ethernet {...}</div><div>++++[request] returns noop</div>
<div>+++- case Ethernet returns noop</div><div>++- switch %{NAS-Port-Type} returns noop</div><div>++? if (request:Acct-Status-Type == 'Stop')</div><div>? Evaluating (request:Acct-Status-Type == 'Stop') -> FALSE</div>
<div>++? if (request:Acct-Status-Type == 'Stop') -> FALSE</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Stripped-User-Name} -> <a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a></div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{%{Stripped-User-Name}:-%{User-Name}} -> <a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a></div>
<div>[ucaaa_accounting_sql] sql_set_user escaped user --> '<a href="mailto:test.user%2B012@company.com">test.user+012@company.com</a>'</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Delay-Time} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Input-Gigawords} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Input-Octets} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Output-Gigawords} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Output-Octets} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Input-Packets} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Output-Packets} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{Acct-Session-Time} -> </div>
<div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>... expanding second conditional</div><div>[ucaaa_accounting_sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: SELECT * FROM pams.radius_accounting_start ( '%{Tmp-String-0}', '%{SQL-User-Name}', '%{Framed-IP-Address}', '%{NAS-IP-Address}', '%{Tmp-String-1}', '%{NAS-Identifier}', '%{Called-Station-Id}', '%{Connect-Info}', '%{Calling-Station-Id}', '%{%{Acct-Delay-Time}:-0}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{%{Acct-Input-Packets}:-0}', '%{%{Acct-Output-Packets}:-0}', '%{%{Acct-Session-Time}:-0}', '%{Acct-Session-Id}', '%{Venue-Id}') -> SELECT * FROM pams.radius_accounting_start ( '226645', 'test.user=<a href="mailto:2B012@company.com">2B012@company.com</a>', '127.0.0.1', '10.5.10.68', '15', '2005264-Park Hotel', '00-15-C5-E7-B5-E4', 'Mozilla/5.0 =28Macintosh=3B U=3B Intel Mac OS X 10.6=3B sv-SE=3B rv:1.9.2.12=29 Gecko/20101026 Firefox/3.6.12',</div>
<div>rlm_sql (ucaaa_accounting_sql): Reserving sql socket id: 4</div><div>rlm_sql_postgresql: Status: PGRES_TUPLES_OK</div><div>rlm_sql_postgresql: query affected rows = 1 , fields = 1</div><div>rlm_sql (ucaaa_accounting_sql): Released sql socket id: 4</div>
<div>++[ucaaa_accounting_sql] returns ok</div><div>} # server ucaaa</div><div>Going to the next request</div><div><<< Received proxied response code 5 from internal virtual server.</div><div># Executing section post-proxy from file /etc/raddb/sites-enabled/default</div>
<div>+- entering group post-proxy {...}</div><div>[eap] No pre-existing handler found</div><div>++[eap] returns noop</div><div>Sending Accounting-Response of id 151 to 127.0.0.1 port 56133</div><div>Finished request 0.</div>
<div>Cleaning up request 0 ID 151 with timestamp +10</div><div>Going to the next request</div><div>Ready to process requests.</div></div><div><br></div></font></div></font></div>