Hi, 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.
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. Excerpts from config and logs are below. Where am I going wrong with this? Thanks, Jack P. DB conf ========== database = "postgresql" driver = "rlm_sql_postgresql" server = "..." login = "..." password = "..." radius_db = "..." sqltrace = no num_sql_socks = 5 connect_failure_retry_delay = 60 lifetime = 0 max_queries = 0 safe-characters = "+();@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" SQL module definition ===================== sql ucaaa_accounting_sql { $INCLUDE db_connection.ini sql_user_name = "%{%{Stripped-User-Name}:-%{User-Name}}" 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}')" ... } Log output ========== ... ... server ucaaa { # from file /etc/raddb/sites-enabled/ucaaa modules { Module: Checking authenticate {...} for more modules to load Module: Checking authorize {...} for more modules to load Module: Instantiating module "ucaaa" from file /etc/raddb/modules/ucaaa python ucaaa { mod_instantiate = "ucaaa" func_instantiate = "instantiate" mod_authorize = "ucaaa" func_authorize = "authorize" } Loading UCAAA Readying configuration UCAAA ready Module: Checking accounting {...} for more modules to load Module: Instantiating module "ucaaa_accounting_sql" from file /etc/raddb/modules/ucaaa_accounting_sql sql ucaaa_accounting_sql { driver = "rlm_sql_postgresql" server = "..." port = "" login = "..." password = "...." radius_db = "..." read_groups = yes sqltrace = no sqltracefile = "/var/log/radius/ucaaa.sql" readclients = no deletestalesessions = yes num_sql_socks = 5 lifetime = 0 max_queries = 0 sql_user_name = "%{%{Stripped-User-Name}:-%{User-Name}}" default_user_profile = "" nas_query = "SELECT id,nasname,shortname,type,secret FROM nas" authorize_check_query = "" authorize_group_check_query = "" authorize_group_reply_query = "" accounting_onoff_query = "" 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}')" accounting_update_query_alt = "" 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}')" accounting_start_query_alt = "" 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}')" accounting_stop_query_alt = "" connect_failure_retry_delay = 60 simul_count_query = "" simul_verify_query = "" postauth_query = "" safe-characters = "+();@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" } rlm_sql Creating new attribute ucaaa_accounting_sql-SQL-Group rlm_sql (ucaaa_accounting_sql): Driver rlm_sql_postgresql (module rlm_sql_postgresql) loaded and linked rlm_sql (ucaaa_accounting_sql): Attempting to connect to x...@xxx.xxx.xxx.xxx :/xx rlm_sql (ucaaa_accounting_sql): starting 0 rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #0 rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #0 rlm_sql (ucaaa_accounting_sql): starting 1 rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #1 rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #1 rlm_sql (ucaaa_accounting_sql): starting 2 rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #2 rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #2 rlm_sql (ucaaa_accounting_sql): starting 3 rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #3 rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #3 rlm_sql (ucaaa_accounting_sql): starting 4 rlm_sql (ucaaa_accounting_sql): Attempting to connect rlm_sql_postgresql #4 rlm_sql (ucaaa_accounting_sql): Connected new DB handle, #4 } # modules } # server ... ... Listening on authentication address * port 1812 Listening on accounting address * port 1813 Listening on command file /var/run/radiusd/radiusd.sock Listening on proxy address * port 1814 Ready to process requests. rad_recv: Accounting-Request packet from host 127.0.0.1 port 56133, id=151, length=320 Acct-Status-Type = Start NAS-IP-Address = 10.5.10.68 Framed-IP-Address = 127.0.0.1 Acct-Session-Id = "114954251" NAS-Port-Type = Ethernet NAS-Port-Id = "BNG-A 1/1/1:40" User-Name = "REALM/test.user+...@company.com" Venue-Id = 2003174 WISPr-Billing-Class-Of-Service = "pt=FREE, nai=226645" Calling-Station-Id = "f8:1e:df:dd:02:d4" NAS-Identifier = "2005264-Park Hotel" Called-Station-Id = "00-15-C5-E7-B5-E4" 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" # Executing section preacct from file /etc/raddb/sites-enabled/default +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent [acct_unique] Hashing ',Client-IP-Address = 127.0.0.1,NAS-IP-Address = 10.5.10.68,Acct-Session-Id = "114954251",User-Name = "REALM/ test.user+...@company.com"' [acct_unique] Acct-Unique-Session-ID = "00f4dfc71e21d2a8". ++[acct_unique] returns ok [IPASS] Looking up realm "REALM" for User-Name = "REALM/ test.user+...@company.com" [IPASS] Found realm "REALM" [IPASS] Adding Stripped-User-Name = "test.user+...@company.com" [IPASS] Adding Realm = "REALM" [IPASS] Proxying request from user test.user+...@company.com to realm REALM [IPASS] Preparing to proxy accounting request to realm "REALM" ++[IPASS] returns updated [suffix] Request already proxied. Ignoring. ++[suffix] returns ok ++[files] returns noop # Executing section accounting from file /etc/raddb/sites-enabled/default +- entering group accounting {...} [detail] expand: /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/radius/radacct/127.0.0.1/detail-20120530 [detail] /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/radius/radacct/127.0.0.1/detail-20120530 [detail] expand: %t -> Wed May 30 04:08:40 2012 ++[detail] returns ok ++[unix] returns noop [radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> REALM/test.user+...@company.com rlm_radutmp: No NAS-Port seen. Cannot do anything. rlm_radumtp: WARNING: checkrad will probably not work! ++[radutmp] returns noop [attr_filter.accounting_response] expand: %{User-Name} -> REALM/ test.user+...@company.com attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated WARNING: Empty pre-proxy section. Using default return values. >>> Sending proxied request internally to virtual server. server ucaaa { WARNING: Empty preacct section. Using default return values. # Executing section accounting from file /etc/raddb/sites-enabled/ucaaa +- entering group accounting {...} ++? if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) expand: %{WISPr-Billing-Class-Of-Service} -> pt=FREE, nai=226645 ? Evaluating ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) -> TRUE ++? if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) -> TRUE ++- entering if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) {...} expand: %{1} -> 226645 +++[request] returns noop ++- if ("%{WISPr-Billing-Class-Of-Service}" =~ /nai=([0-9]*)/i) returns noop expand: %{NAS-Port-Type} -> Ethernet ++- entering switch %{NAS-Port-Type} {...} +++- entering case Ethernet {...} ++++[request] returns noop +++- case Ethernet returns noop ++- switch %{NAS-Port-Type} returns noop ++? if (request:Acct-Status-Type == 'Stop') ? Evaluating (request:Acct-Status-Type == 'Stop') -> FALSE ++? if (request:Acct-Status-Type == 'Stop') -> FALSE [ucaaa_accounting_sql] expand: %{Stripped-User-Name} -> test.user+...@company.com [ucaaa_accounting_sql] expand: %{%{Stripped-User-Name}:-%{User-Name}} -> test.user+...@company.com [ucaaa_accounting_sql] sql_set_user escaped user --> ' test.user+...@company.com' [ucaaa_accounting_sql] expand: %{Acct-Delay-Time} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Input-Gigawords} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Input-Octets} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Output-Gigawords} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Output-Octets} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Input-Packets} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Output-Packets} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] expand: %{Acct-Session-Time} -> [ucaaa_accounting_sql] ... expanding second conditional [ucaaa_accounting_sql] 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= 2b...@company.com', '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', rlm_sql (ucaaa_accounting_sql): Reserving sql socket id: 4 rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 1 rlm_sql (ucaaa_accounting_sql): Released sql socket id: 4 ++[ucaaa_accounting_sql] returns ok } # server ucaaa Going to the next request <<< Received proxied response code 5 from internal virtual server. # Executing section post-proxy from file /etc/raddb/sites-enabled/default +- entering group post-proxy {...} [eap] No pre-existing handler found ++[eap] returns noop Sending Accounting-Response of id 151 to 127.0.0.1 port 56133 Finished request 0. Cleaning up request 0 ID 151 with timestamp +10 Going to the next request Ready to process requests.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html