String expansion of attributes value from database

Krzysztof Grobelak krzysztof.grobelak at enet.ie
Wed Jul 13 13:29:33 CEST 2016


Hello All,

I have been testing the below feature and it looks like the server is successfully performing the expansion of the attributes values when the value is enclosed with the “`” as can be seen below:

MariaDB [radius]> SELECT * FROM radgroupreply;
+----+-----------+--------------------+----+----------------------------------------------------------------------------------------------------------------------+
| id | groupname | attribute          | op | value                                                                                                                |
+----+-----------+--------------------+----+----------------------------------------------------------------------------------------------------------------------+
|  2 | profile_1 | Framed-IP-Address  | := | `%{sql:SELECT framedipaddress from radippool WHERE pool_name='bng1_dyn' AND username='%{Alc-Client-Hardware-Addr}'}` |
|  3 | profile_1 | Alc-Subsc-ID-Str   | := | `%{Alc-Client-Hardware-Addr}`                                                                                        |
|  4 | profile_1 | Alc-Subsc-Prof-Str | := | sub_profile_1                                                                                                        |
|  5 | profile_1 | Alc-SLA-Prof-Str   | := | sla_profile_1                                                                                                        |
+----+-----------+--------------------+----+----------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

The resulting attributes are as follows:

(1)  Sent Access-Accept Id 178 from 10.100.1.1:1812 to 172.30.30.2:64414 via p1p2 length 0
(1)    Framed-IP-Address = 0.0.0.0
(1)    Alc-Subsc-ID-Str = "`84:8f:69:f1:16:0a`"
(1)    Alc-Subsc-Prof-Str = "sub_profile_1"
(1)    Alc-SLA-Prof-Str = "sla_profile_1"

The  %{Alc-Client-Hardware-Addr} gets correctly expanded to 84:8f:69:f1:16:0a however the “ `”  are still encapsulating the mac.
The Framed-IP-Address somehow gets assigned 0.0.0.0 even though	debug log show the correct expansion and execution of the SQL query.

Can you please advise how to remove the “`” and how to correctly assign the SQL returned value to Framed-IP-Addess attribute?

Regards,
Kris

Full debug:

FreeRADIUS Version 3.1.0
Copyright (C) 1999-2016 The FreeRADIUS server project and contributors
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE
You may redistribute copies of FreeRADIUS under the terms of the
GNU General Public License
For more information about these matters, see the file named COPYRIGHT
Starting - reading configuration files ...
including dictionary file /usr/local/share/freeradius/dictionary
including dictionary file /usr/local/share/freeradius/dictionary.dhcp
including dictionary file /usr/local/share/freeradius/dictionary.vqp
including dictionary file /usr/local/etc/raddb/dictionary
including configuration file /usr/local/etc/raddb/radiusd.conf
including configuration file /usr/local/etc/raddb/proxy.conf
including configuration file /usr/local/etc/raddb/clients.conf
including files in directory /usr/local/etc/raddb/mods-enabled/
including configuration file /usr/local/etc/raddb/mods-enabled/pap
including configuration file /usr/local/etc/raddb/mods-enabled/sqlippool
including configuration file /usr/local/etc/raddb/mods-config/sql/ippool/mysql/queries.conf
including configuration file /usr/local/etc/raddb/mods-enabled/digest
including configuration file /usr/local/etc/raddb/mods-enabled/cache_eap
including configuration file /usr/local/etc/raddb/mods-enabled/files
including configuration file /usr/local/etc/raddb/mods-enabled/sradutmp
including configuration file /usr/local/etc/raddb/mods-enabled/always
including configuration file /usr/local/etc/raddb/mods-enabled/sql
including configuration file /usr/local/etc/raddb/mods-config/sql/driver/mysql
including configuration file /usr/local/etc/raddb/mods-config/sql/main/mysql/queries.conf
including configuration file /usr/local/etc/raddb/mods-enabled/ntlm_auth
including configuration file /usr/local/etc/raddb/mods-enabled/detail
including configuration file /usr/local/etc/raddb/mods-enabled/eap_inner
including configuration file /usr/local/etc/raddb/mods-enabled/expiration
including configuration file /usr/local/etc/raddb/mods-enabled/linelog
including configuration file /usr/local/etc/raddb/mods-enabled/attr_filter
including configuration file /usr/local/etc/raddb/mods-enabled/unpack
including configuration file /usr/local/etc/raddb/mods-enabled/radutmp
including configuration file /usr/local/etc/raddb/mods-enabled/dynamic_clients
including configuration file /usr/local/etc/raddb/mods-enabled/eap
including configuration file /usr/local/etc/raddb/mods-enabled/echo
including configuration file /usr/local/etc/raddb/mods-enabled/dhcp
including configuration file /usr/local/etc/raddb/mods-enabled/detail.log
including configuration file /usr/local/etc/raddb/mods-enabled/exec
including configuration file /usr/local/etc/raddb/mods-enabled/unix
including configuration file /usr/local/etc/raddb/mods-enabled/soh
including configuration file /usr/local/etc/raddb/mods-enabled/preprocess
including configuration file /usr/local/etc/raddb/mods-enabled/utf8
including configuration file /usr/local/etc/raddb/mods-enabled/realm
including configuration file /usr/local/etc/raddb/mods-enabled/mschap
including configuration file /usr/local/etc/raddb/mods-enabled/expr
including configuration file /usr/local/etc/raddb/mods-enabled/chap
including configuration file /usr/local/etc/raddb/mods-enabled/passwd
including configuration file /usr/local/etc/raddb/mods-enabled/replicate
including configuration file /usr/local/etc/raddb/mods-enabled/dhcp_sqlippool
including configuration file /usr/local/etc/raddb/mods-config/sql/ippool-dhcp/mysql/queries.conf
including configuration file /usr/local/etc/raddb/mods-enabled/logintime
including files in directory /usr/local/etc/raddb/policy.d/
including configuration file /usr/local/etc/raddb/policy.d/canonicalization
including configuration file /usr/local/etc/raddb/policy.d/operator-name
including configuration file /usr/local/etc/raddb/policy.d/eap
including configuration file /usr/local/etc/raddb/policy.d/dhcp
including configuration file /usr/local/etc/raddb/policy.d/filter
including configuration file /usr/local/etc/raddb/policy.d/cui
including configuration file /usr/local/etc/raddb/policy.d/debug
including configuration file /usr/local/etc/raddb/policy.d/vendor
including configuration file /usr/local/etc/raddb/policy.d/control
including configuration file /usr/local/etc/raddb/policy.d/accounting
including configuration file /usr/local/etc/raddb/policy.d/abfab-tr
including files in directory /usr/local/etc/raddb/sites-enabled/
including configuration file /usr/local/etc/raddb/sites-enabled/inner-tunnel
including configuration file /usr/local/etc/raddb/sites-enabled/default
main {
 security {
 	allow_core_dumps = no
 }
	name = "radiusd"
	prefix = "/usr/local"
	localstatedir = "/var"
	logdir = "/var/log/radius"
	run_dir = "/var/run/radiusd"
}
main {
	name = "radiusd"
	prefix = "/usr/local"
	localstatedir = "/var"
	sbindir = "/usr/local/sbin"
	logdir = "/var/log/radius"
	run_dir = "/var/run/radiusd"
	libdir = "/usr/local/lib"
	radacctdir = "/var/log/radius/radacct"
	hostname_lookups = no
	max_request_time = 30
	cleanup_delay = 5
	continuation_timeout = 15
	max_requests = 16384
	pidfile = "/var/run/radiusd/radiusd.pid"
	checkrad = "/usr/local/sbin/checkrad"
	debug_level = 0
	proxy_requests = no
 log {
 	stripped_names = no
 	auth = yes
 	auth_badpass = yes
 	auth_goodpass = yes
 	colourise = yes
 	msg_denied = "You are already logged in - access denied"
 }
 resources {
 }
 security {
 	max_attributes = 200
 	reject_delay = 1.000000
 	status_server = yes
 	allow_vulnerable_openssl = "yes"
 }
}
radiusd: #### Loading Realms and Home Servers ####
 proxy server {
 	retry_delay = 5
 	retry_count = 3
 	default_fallback = no
 	dynamic = no
 	dead_time = 120
 	wake_all_if_all_dead = no
 }
 home_server localhost {
 	ipaddr = 127.0.0.1
 	port = 1812
 	type = "auth"
 	secret = <<< secret >>>
 	response_window = 20.000000
 	response_timeouts = 1
 	max_outstanding = 65536
 	zombie_period = 40
 	status_check = "status-server"
 	ping_interval = 30
 	check_interval = 30
 	check_timeout = 4
 	num_answers_to_alive = 3
 	revive_interval = 120
  limit {
  	max_connections = 16
  	max_requests = 0
  	lifetime = 0
  	idle_timeout = 0
  }
  coa {
  	irt = 2
  	mrt = 16
  	mrc = 5
  	mrd = 30
  }
 }
 home_server_pool my_auth_failover {
	type = fail-over
	home_server = localhost
 }
 realm example.com {
	auth_pool = my_auth_failover
 }
 realm LOCAL {
 }
radiusd: #### Loading Clients ####
 client localhost {
 	ipaddr = 127.0.0.1
 	require_message_authenticator = no
 	secret = <<< secret >>>
 	nas_type = "other"
 	proto = "*"
  limit {
  	max_connections = 16
  	lifetime = 0
  	idle_timeout = 30
  }
 }
 client localhost_ipv6 {
 	ipv6addr = ::1
 	require_message_authenticator = no
 	secret = <<< secret >>>
  limit {
  	max_connections = 16
  	lifetime = 0
  	idle_timeout = 30
  }
 }
Debugger not attached
 thread pool {
 	start_servers = 5
 	max_servers = 32
 	min_spare_servers = 3
 	max_spare_servers = 10
 	max_requests_per_server = 0
 	cleanup_delay = 5
 	max_queue_size = 65536
 	queue_priority = "default"
 	auto_limit_acct = no
 }
listen {
  	type = "auth"
  	ipaddr = 127.0.0.1
  	port = 18120
  	recv_buff = 0
}
  # Creating Auth-Type = PAP
  # Creating Auth-Type = CHAP
  # Creating Auth-Type = MS-CHAP
  # Creating Auth-Type = eap
listen {
  	type = "auth"
  	ipaddr = *
  	port = 0
  	recv_buff = 0
   limit {
   	max_connections = 16
   	lifetime = 0
   	idle_timeout = 30
   }
}
listen {
  	type = "acct"
  	ipaddr = *
  	port = 0
  	recv_buff = 0
   limit {
   	max_connections = 16
   	lifetime = 0
   	idle_timeout = 30
   }
}
listen {
  	type = "auth"
  	ipv6addr = ::
  	port = 0
  	recv_buff = 0
   limit {
   	max_connections = 16
   	lifetime = 0
   	idle_timeout = 30
   }
}
listen {
  	type = "acct"
  	ipv6addr = ::
  	port = 0
  	recv_buff = 0
   limit {
   	max_connections = 16
   	lifetime = 0
   	idle_timeout = 30
   }
}
  # Creating Auth-Type = digest
radiusd: #### Loading modules ####
 modules {
  # Loaded module "rlm_pap"
  # Loading module "pap" from file /usr/local/etc/raddb/mods-enabled/pap
  pap {
  	normalise = yes
  }
  # Loaded module "rlm_sqlippool"
  # Loading module "sqlippool" from file /usr/local/etc/raddb/mods-enabled/sqlippool
  sqlippool {
  	sql_module_instance = "sql"
  	lease_duration = 3600
  	pool_name = ""
  	default_pool = "main_pool"
  	allocate_begin = "START TRANSACTION"
  	allocate_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}'"
  	allocate_find = "SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR expiry_time IS NULL or expiry_time = 0) ORDER BY (username <> '%{User-Name}'), (callingstationid <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE"
  	allocate_update = "UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = NOW() + INTERVAL 3600 SECOND WHERE framedipaddress = '%I' AND expiry_time IS NULL"
  	allocate_commit = "COMMIT"
  	pool_check = "SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
  	start_begin = "START TRANSACTION"
  	start_update = "UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{NAS-Port}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
  	start_commit = "COMMIT"
  	alive_begin = "START TRANSACTION"
  	alive_update = "UPDATE radippool SET expiry_time = NOW() + INTERVAL 3600 SECOND WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
  	alive_commit = "COMMIT"
  	stop_begin = "START TRANSACTION"
  	stop_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{NAS-Port}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
  	stop_commit = "COMMIT"
  	on_begin = "START TRANSACTION"
  	on_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE nasipaddress = '%{Nas-IP-Address}'"
  	on_commit = "COMMIT"
  	off_begin = "START TRANSACTION"
  	off_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NULL WHERE nasipaddress = '%{Nas-IP-Address}'"
  	off_commit = "COMMIT"
   messages {
   	exists = "Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
   	success = "Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
   	clear = "Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"
   	failed = "IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
   	nopool = "No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
   }
  }
  # Loaded module "rlm_digest"
  # Loading module "digest" from file /usr/local/etc/raddb/mods-enabled/digest
  # Loaded module "rlm_cache"
  # Loading module "cache_eap" from file /usr/local/etc/raddb/mods-enabled/cache_eap
  cache cache_eap {
  	driver = "rlm_cache_rbtree"
  	ttl = 15
  	max_entries = 0
  	epoch = 0
  	add_stats = no
  }
  # Loaded module "rlm_files"
  # Loading module "files" from file /usr/local/etc/raddb/mods-enabled/files
  files {
  	filename = "/usr/local/etc/raddb/mods-config/files/authorize"
  	acctusersfile = "/usr/local/etc/raddb/mods-config/files/accounting"
  	preproxy_usersfile = "/usr/local/etc/raddb/mods-config/files/pre-proxy"
  }
  # Loaded module "rlm_radutmp"
  # Loading module "sradutmp" from file /usr/local/etc/raddb/mods-enabled/sradutmp
  radutmp sradutmp {
  	filename = "/var/log/radius/sradutmp"
  	username = "%{User-Name}"
  	case_sensitive = yes
  	check_with_nas = yes
  	permissions = 420
  	caller_id = no
  }
  # Loaded module "rlm_always"
  # Loading module "reject" from file /usr/local/etc/raddb/mods-enabled/always
  always reject {
  	rcode = "reject"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "fail" from file /usr/local/etc/raddb/mods-enabled/always
  always fail {
  	rcode = "fail"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "ok" from file /usr/local/etc/raddb/mods-enabled/always
  always ok {
  	rcode = "ok"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "handled" from file /usr/local/etc/raddb/mods-enabled/always
  always handled {
  	rcode = "handled"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "invalid" from file /usr/local/etc/raddb/mods-enabled/always
  always invalid {
  	rcode = "invalid"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "userlock" from file /usr/local/etc/raddb/mods-enabled/always
  always userlock {
  	rcode = "userlock"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "notfound" from file /usr/local/etc/raddb/mods-enabled/always
  always notfound {
  	rcode = "notfound"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "noop" from file /usr/local/etc/raddb/mods-enabled/always
  always noop {
  	rcode = "noop"
  	simulcount = 0
  	mpp = no
  }
  # Loading module "updated" from file /usr/local/etc/raddb/mods-enabled/always
  always updated {
  	rcode = "updated"
  	simulcount = 0
  	mpp = no
  }
  # Loaded module "rlm_sql"
  # Loading module "sql" from file /usr/local/etc/raddb/mods-enabled/sql
  sql {
  	driver = "rlm_sql_mysql"
  	server = "localhost"
  	port = 3306
  	login = "radius"
  	password = <<< secret >>>
  	radius_db = "radius"
  	read_groups = yes
  	read_profiles = yes
  	read_clients = yes
  	delete_stale_sessions = yes
  	sql_user_name = "%{User-Name}"
  	group_attribute = "sql-sql-Group"
  	default_user_profile = ""
  	client_query = "SELECT id, nasname, shortname, type, secret, server FROM nas"
  	authorize_check_query = "SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id"
  	authorize_reply_query = "SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id"
  	authorize_group_check_query = "SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{sql-sql-Group}' ORDER BY id"
  	authorize_group_reply_query = "SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{sql-sql-Group}' ORDER BY id"
  	group_membership_query = "SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority"
  	simul_count_query = "SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL"
  	simul_verify_query = "SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL"
  	safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
   accounting {
   	reference = "%{tolower:type.%{Acct-Status-Type}.query}"
    type {
     accounting-on {
     	query = "UPDATE radacct SET acctstoptime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime	= '%{integer:Event-Timestamp}' - UNIX_TIMESTAMP(acctstarttime), acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' WHERE acctstoptime IS NULL AND nasipaddress   = '%{NAS-IP-Address}' AND acctstarttime <= FROM_UNIXTIME(%{integer:Event-Timestamp})"
     }
     accounting-off {
     	query = "UPDATE radacct SET acctstoptime = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime	= '%{integer:Event-Timestamp}' - UNIX_TIMESTAMP(acctstarttime), acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' WHERE acctstoptime IS NULL AND nasipaddress   = '%{NAS-IP-Address}' AND acctstarttime <= FROM_UNIXTIME(%{integer:Event-Timestamp})"
     }
     start {
     	query = "INSERT INTO radacct (acctsessionid,		acctuniqueid,		username, realm,		nasipaddress,		nasportid, nasporttype,		acctstarttime,		acctupdatetime, acctstoptime,		acctsessiontime, 	acctauthentic, connectinfo_start,	connectinfo_stop, 	acctinputoctets, acctoutputoctets,	calledstationid, 	callingstationid, acctterminatecause,	servicetype,		framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"
     	query = "UPDATE radacct SET acctstarttime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), acctupdatetime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), connectinfo_start = '%{Connect-Info}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
     }
     interim-update {
     	query = "UPDATE radacct SET acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), acctupdatetime  = FROM_UNIXTIME(%{integer:Event-Timestamp}), acctinterval    = %{integer:Event-Timestamp} - UNIX_TIMESTAMP(@acctupdatetime_old), framedipaddress = '%{Framed-IP-Address}', acctsessiontime = %{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
     	query = "INSERT INTO radacct (acctsessionid,		acctuniqueid,		username, realm,		nasipaddress,		nasportid, nasporttype,		acctstarttime,		acctupdatetime, acctstoptime,		acctsessiontime, 	acctauthentic, connectinfo_start,	connectinfo_stop, 	acctinputoctets, acctoutputoctets,	calledstationid, 	callingstationid, acctterminatecause,	servicetype,		framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), FROM_UNIXTIME(%{integer:Event-Timestamp}), NULL, %{%{Acct-Session-Time}:-NULL}, '%{Acct-Authentic}', '%{Connect-Info}', '', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"
     }
     stop {
     	query = "UPDATE radacct SET acctstoptime	= FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime	= %{%{Acct-Session-Time}:-NULL}, acctinputoctets	= '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', acctterminatecause = '%{Acct-Terminate-Cause}', connectinfo_stop = '%{Connect-Info}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
     	query = "INSERT INTO radacct (acctsessionid,		acctuniqueid,		username, realm,		nasipaddress,		nasportid, nasporttype,		acctstarttime,		acctupdatetime, acctstoptime,		acctsessiontime, 	acctauthentic, connectinfo_start,	connectinfo_stop, 	acctinputoctets, acctoutputoctets,	calledstationid, 	callingstationid, acctterminatecause,	servicetype,		framedprotocol, framedipaddress) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), FROM_UNIXTIME(%{integer:Event-Timestamp}), FROM_UNIXTIME(%{integer:Event-Timestamp}), %{%{Acct-Session-Time}:-NULL}, '%{Acct-Authentic}', '', '%{Connect-Info}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')"
     }
    }
   }
   post-auth {
   	reference = ".query"
   	query = "INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')"
   }
  }
rlm_sql (sql) - Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Creating attribute sql-sql-Group
  # Loaded module "rlm_exec"
  # Loading module "ntlm_auth" from file /usr/local/etc/raddb/mods-enabled/ntlm_auth
  exec ntlm_auth {
  	wait = yes
  	program = "/path/to/ntlm_auth --request-nt-key --domain=MYDOMAIN --username=%{mschap:User-Name} --password=%{User-Password}"
  	shell_escape = yes
  }
  # Loaded module "rlm_detail"
  # Loading module "detail" from file /usr/local/etc/raddb/mods-enabled/detail
  detail {
  	filename = "/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d"
  	header = "%t"
  	permissions = 384
  	locking = no
  	escape_filenames = no
  	log_packet_header = no
  }
  # Loaded module "rlm_eap"
  # Loading module "inner-eap" from file /usr/local/etc/raddb/mods-enabled/eap_inner
  eap inner-eap {
  	default_eap_type = "mschapv2"
  	ignore_unknown_eap_types = no
  	cisco_accounting_username_bug = no
  }
   # Linked to sub-module rlm_eap_md5
   # Linked to sub-module rlm_eap_gtc
   gtc {
   	challenge = "Password: "
   	auth_type = "PAP"
   }
   # Linked to sub-module rlm_eap_mschapv2
   mschapv2 {
   	with_ntdomain_hack = no
   	send_error = no
   }
   # Linked to sub-module rlm_eap_tls
   tls {
   	tls = "tls-peer"
   	require_client_cert = yes
   	include_length = yes
   }
   tls-config tls-peer {
   	verify_depth = 0
   	ca_path = "/usr/local/etc/raddb/certs"
   	pem_file_type = yes
   	private_key_file = "/usr/local/etc/raddb/certs/server.key"
   	certificate_file = "/usr/local/etc/raddb/certs/server.pem"
   	ca_file = "/usr/local/etc/raddb/certs/ca.pem"
   	private_key_password = <<< secret >>>
   	dh_file = "/usr/local/etc/raddb/certs/dh"
   	fragment_size = 16384
   	auto_chain = yes
   	check_crl = no
   	allow_renegotiation = no
   	ecdh_curve = "prime256v1"
    cache {
    	lifetime = 86400
    	verify = no
    }
    verify {
    }
    ocsp {
    	enable = no
    	override_cert_url = no
    	use_nonce = yes
    	timeout = 0
    	softfail = no
    }
    staple {
    	enable = no
    	override_cert_url = no
    	use_nonce = yes
    	timeout = 0
    	softfail = no
    }
   }
  # Loaded module "rlm_expiration"
  # Loading module "expiration" from file /usr/local/etc/raddb/mods-enabled/expiration
  # Loaded module "rlm_linelog"
  # Loading module "linelog" from file /usr/local/etc/raddb/mods-enabled/linelog
  linelog {
  	destination = "file"
  	delimiter = " "
   file {
   	filename = "/var/log/radius/linelog"
   	permissions = 384
   	escape_filenames = no
   }
   syslog {
   	severity = "info"
   }
   unix {
   }
   tcp {
   	port = 514
   	timeout = 2.000000
   }
   udp {
   	port = 514
   	timeout = 2.000000
   }
  }
  # Loading module "log_accounting" from file /usr/local/etc/raddb/mods-enabled/linelog
  linelog log_accounting {
  	destination = "file"
  	delimiter = " "
   file {
   	filename = "/var/log/radius/linelog-accounting"
   	permissions = 384
   	escape_filenames = no
   }
   syslog {
   	severity = "info"
   }
   unix {
   }
   tcp {
   	timeout = 1000.000000
   }
   udp {
   	timeout = 1000.000000
   }
  }
  # Loaded module "rlm_attr_filter"
  # Loading module "attr_filter.post-proxy" from file /usr/local/etc/raddb/mods-enabled/attr_filter
  attr_filter attr_filter.post-proxy {
  	filename = "/usr/local/etc/raddb/mods-config/attr_filter/post-proxy"
  	relaxed = no
  }
  # Loading module "attr_filter.pre-proxy" from file /usr/local/etc/raddb/mods-enabled/attr_filter
  attr_filter attr_filter.pre-proxy {
  	filename = "/usr/local/etc/raddb/mods-config/attr_filter/pre-proxy"
  	relaxed = no
  }
  # Loading module "attr_filter.access_reject" from file /usr/local/etc/raddb/mods-enabled/attr_filter
  attr_filter attr_filter.access_reject {
  	filename = "/usr/local/etc/raddb/mods-config/attr_filter/access_reject"
  	relaxed = no
  }
  # Loading module "attr_filter.access_challenge" from file /usr/local/etc/raddb/mods-enabled/attr_filter
  attr_filter attr_filter.access_challenge {
  	filename = "/usr/local/etc/raddb/mods-config/attr_filter/access_challenge"
  	relaxed = no
  }
  # Loading module "attr_filter.accounting_response" from file /usr/local/etc/raddb/mods-enabled/attr_filter
  attr_filter attr_filter.accounting_response {
  	filename = "/usr/local/etc/raddb/mods-config/attr_filter/accounting_response"
  	relaxed = no
  }
  # Loaded module "rlm_unpack"
  # Loading module "unpack" from file /usr/local/etc/raddb/mods-enabled/unpack
  # Loading module "radutmp" from file /usr/local/etc/raddb/mods-enabled/radutmp
  radutmp {
  	filename = "/var/log/radius/radutmp"
  	username = "%{User-Name}"
  	case_sensitive = yes
  	check_with_nas = yes
  	permissions = 384
  	caller_id = yes
  }
  # Loaded module "rlm_dynamic_clients"
  # Loading module "dynamic_clients" from file /usr/local/etc/raddb/mods-enabled/dynamic_clients
  # Loading module "eap" from file /usr/local/etc/raddb/mods-enabled/eap
  eap {
  	default_eap_type = "md5"
  	ignore_unknown_eap_types = no
  	cisco_accounting_username_bug = no
  }
   # Linked to sub-module rlm_eap_md5
   # Linked to sub-module rlm_eap_leap
   # Linked to sub-module rlm_eap_gtc
   gtc {
   	challenge = "Password: "
   	auth_type = "PAP"
   }
   # Linked to sub-module rlm_eap_tls
   tls {
   	tls = "tls-common"
   	require_client_cert = yes
   	include_length = yes
   }
   tls-config tls-common {
   	verify_depth = 0
   	ca_path = "/usr/local/etc/raddb/certs"
   	pem_file_type = yes
   	private_key_file = "/usr/local/etc/raddb/certs/server.pem"
   	certificate_file = "/usr/local/etc/raddb/certs/server.pem"
   	ca_file = "/usr/local/etc/raddb/certs/ca.pem"
   	private_key_password = <<< secret >>>
   	dh_file = "/usr/local/etc/raddb/certs/dh"
   	fragment_size = 1024
   	auto_chain = yes
   	check_crl = no
   	cipher_list = "DEFAULT"
   	allow_renegotiation = no
   	ecdh_curve = "prime256v1"
    cache {
    	lifetime = 86400
    	verify = no
    }
    verify {
    }
    ocsp {
    	enable = no
    	override_cert_url = yes
    	url = "http://127.0.0.1/ocsp/"
    	use_nonce = yes
    	timeout = 0
    	softfail = no
    }
    staple {
    	enable = no
    	override_cert_url = yes
    	url = "http://127.0.0.1/ocsp/"
    	use_nonce = yes
    	timeout = 0
    	softfail = no
    }
   }
   # Linked to sub-module rlm_eap_ttls
   ttls {
   	tls = "tls-common"
   	virtual_server = "inner-tunnel"
   	include_length = yes
   	require_client_cert = no
   }
tls - Using cached TLS configuration from previous invocation
   # Linked to sub-module rlm_eap_peap
   peap {
   	tls = "tls-common"
   	proxy_tunneled_request_as_eap = yes
   	virtual_server = "inner-tunnel"
   	soh = no
   	require_client_cert = no
   }
tls - Using cached TLS configuration from previous invocation
   # Linked to sub-module rlm_eap_mschapv2
   mschapv2 {
   	with_ntdomain_hack = no
   	send_error = no
   }
  # Loading module "echo" from file /usr/local/etc/raddb/mods-enabled/echo
  exec echo {
  	wait = yes
  	program = "/bin/echo %{User-Name}"
  	input_pairs = "request"
  	output_pairs = "reply"
  	shell_escape = yes
  }
  # Loaded module "rlm_dhcp"
  # Loading module "dhcp" from file /usr/local/etc/raddb/mods-enabled/dhcp
  # Loading module "auth_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  detail auth_log {
  	filename = "/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d"
  	header = "%t"
  	permissions = 384
  	locking = no
  	escape_filenames = no
  	log_packet_header = no
  }
  # Loading module "reply_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  detail reply_log {
  	filename = "/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d"
  	header = "%t"
  	permissions = 384
  	locking = no
  	escape_filenames = no
  	log_packet_header = no
  }
  # Loading module "pre_proxy_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  detail pre_proxy_log {
  	filename = "/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/pre-proxy-detail-%Y%m%d"
  	header = "%t"
  	permissions = 384
  	locking = no
  	escape_filenames = no
  	log_packet_header = no
  }
  # Loading module "post_proxy_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  detail post_proxy_log {
  	filename = "/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/post-proxy-detail-%Y%m%d"
  	header = "%t"
  	permissions = 384
  	locking = no
  	escape_filenames = no
  	log_packet_header = no
  }
  # Loading module "exec" from file /usr/local/etc/raddb/mods-enabled/exec
  exec {
  	wait = no
  	input_pairs = "request"
  	shell_escape = yes
  	timeout = 10
  }
  # Loaded module "rlm_unix"
  # Loading module "unix" from file /usr/local/etc/raddb/mods-enabled/unix
  unix {
  	radwtmp = "/var/log/radius/radwtmp"
  }
Creating attribute Unix-Group
  # Loaded module "rlm_soh"
  # Loading module "soh" from file /usr/local/etc/raddb/mods-enabled/soh
  soh {
  	dhcp = yes
  }
  # Loaded module "rlm_preprocess"
  # Loading module "preprocess" from file /usr/local/etc/raddb/mods-enabled/preprocess
  preprocess {
  	huntgroups = "/usr/local/etc/raddb/mods-config/preprocess/huntgroups"
  	hints = "/usr/local/etc/raddb/mods-config/preprocess/hints"
  	with_ascend_hack = no
  	ascend_channels_per_line = 23
  	with_ntdomain_hack = no
  	with_specialix_jetstream_hack = no
  	with_cisco_vsa_hack = no
  	with_alvarion_vsa_hack = no
  }
  # Loaded module "rlm_utf8"
  # Loading module "utf8" from file /usr/local/etc/raddb/mods-enabled/utf8
  # Loaded module "rlm_realm"
  # Loading module "IPASS" from file /usr/local/etc/raddb/mods-enabled/realm
  realm IPASS {
  	format = "prefix"
  	delimiter = "/"
  	ignore_default = no
  	ignore_null = no
  }
  # Loading module "suffix" from file /usr/local/etc/raddb/mods-enabled/realm
  realm suffix {
  	format = "suffix"
  	delimiter = "@"
  	ignore_default = no
  	ignore_null = no
  }
  # Loading module "realmpercent" from file /usr/local/etc/raddb/mods-enabled/realm
  realm realmpercent {
  	format = "suffix"
  	delimiter = "%"
  	ignore_default = no
  	ignore_null = no
  }
  # Loading module "ntdomain" from file /usr/local/etc/raddb/mods-enabled/realm
  realm ntdomain {
  	format = "prefix"
  	delimiter = "\\"
  	ignore_default = no
  	ignore_null = no
  }
  # Loaded module "rlm_mschap"
  # Loading module "mschap" from file /usr/local/etc/raddb/mods-enabled/mschap
  mschap {
  	use_mppe = yes
  	require_encryption = no
  	require_strong = no
  	with_ntdomain_hack = yes
   passchange {
   }
  	allow_retry = yes
  }
  # Loaded module "rlm_expr"
  # Loading module "expr" from file /usr/local/etc/raddb/mods-enabled/expr
  expr {
  	safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /äéöüàâæçèéêëîïôœùûüaÿÄÉÖÜßÀÂÆÇÈÉÊËÎÏÔŒÙÛÜŸ"
  }
  # Loaded module "rlm_chap"
  # Loading module "chap" from file /usr/local/etc/raddb/mods-enabled/chap
  # Loaded module "rlm_passwd"
  # Loading module "etc_passwd" from file /usr/local/etc/raddb/mods-enabled/passwd
  passwd etc_passwd {
  	filename = "/etc/passwd"
  	format = "*User-Name:Crypt-Password:"
  	delimiter = ":"
  	ignore_nislike = no
  	ignore_empty = yes
  	allow_multiple_keys = no
  	hash_size = 100
  }
  # Loaded module "rlm_replicate"
  # Loading module "replicate" from file /usr/local/etc/raddb/mods-enabled/replicate
  # Loading module "dhcp_sqlippool" from file /usr/local/etc/raddb/mods-enabled/dhcp_sqlippool
  sqlippool dhcp_sqlippool {
  	sql_module_instance = "sql"
  	lease_duration = 7200
  	pool_name = ""
  	default_pool = "main_pool"
  	allocate_begin = "START TRANSACTION"
  	allocate_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NOW() WHERE expiry_time <= NOW() - INTERVAL 1 SECOND AND nasipaddress = '%{Nas-IP-Address}'"
  	allocate_find = "SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND (callingstationid = '%{Calling-Station-Id}' or callingstationid = '') ORDER BY (callingstationid <> '%{Calling-Station-Id}'), expiry_time LIMIT 1 FOR UPDATE"
  	allocate_update = "UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{Calling-Station-Id}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = NOW() + INTERVAL 7200 SECOND WHERE framedipaddress = '%I'"
  	allocate_commit = "COMMIT"
  	pool_check = "SELECT id FROM radippool WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
  	start_begin = "START TRANSACTION"
  	start_update = "UPDATE radippool SET expiry_time = NOW() + INTERVAL 7200 SECOND WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '%{Calling-Station-Id}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
  	start_commit = "COMMIT"
  	alive_begin = "START TRANSACTION"
  	alive_update = "UPDATE radippool SET expiry_time = NOW() + INTERVAL 7200 SECOND WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '%{Calling-Station-Id}' AND username = '%{User-Name}' AND callingstationid = '%{Calling-Station-Id}' AND framedipaddress = '%{Framed-IP-Address}'"
  	alive_commit = "COMMIT"
  	stop_begin = "START TRANSACTION"
  	stop_clear = "UPDATE radippool 	SET 		nasipaddress = '', 		pool_key = 0, 		callingstationid = '', 		username = '', 		expiry_time = NOW() 	WHERE nasipaddress = '%{Nas-IP-Address}' 	AND pool_key = '%{Calling-Station-Id}' 	AND username = '%{User-Name}' 	AND callingstationid = '%{Calling-Station-Id}' 	AND framedipaddress = '%{Framed-IP-Address}'"
  	stop_commit = "COMMIT"
  	on_begin = "START TRANSACTION"
  	on_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NOW() WHERE nasipaddress = '%{Nas-IP-Address}'"
  	on_commit = "COMMIT"
  	off_begin = "START TRANSACTION"
  	off_clear = "UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', expiry_time = NOW() WHERE nasipaddress = '%{Nas-IP-Address}'"
  	off_commit = "COMMIT"
   messages {
   }
  }
  # Loaded module "rlm_logintime"
  # Loading module "logintime" from file /usr/local/etc/raddb/mods-enabled/logintime
  logintime {
  	minimum_timeout = 60
  }
  instantiate {
  }
 } # modules
  # Instantiating module "pap" from file /usr/local/etc/raddb/mods-enabled/pap
  # Instantiating module "sqlippool" from file /usr/local/etc/raddb/mods-enabled/sqlippool
  # Instantiating module "sql" from file /usr/local/etc/raddb/mods-enabled/sql
rlm_sql_mysql - libmysql version: 10.0.26-MariaDB
   mysql {
    tls {
    }
   	warnings = "auto"
   }
rlm_sql (sql) - Attempting to connect to database "radius"
rlm_sql (sql) - Initialising connection pool
   pool {
   	start = 5
   	min = 3
   	max = 32
   	spare = 10
   	uses = 0
   	lifetime = 0
   	cleanup_interval = 30
   	idle_timeout = 60
   	connect_timeout = 3.000000
   	held_trigger_min = 0.000000
   	held_trigger_max = 0.500000
   	retry_delay = 30
   	spread = no
   }
rlm_sql (sql) - Opening additional connection (0), 1 of 32 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
rlm_sql (sql) - Opening additional connection (1), 1 of 31 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
rlm_sql (sql) - Opening additional connection (2), 1 of 30 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
rlm_sql (sql) - Opening additional connection (3), 1 of 29 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
rlm_sql (sql) - Opening additional connection (4), 1 of 28 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
rlm_sql (sql) - Processing generate_sql_clients
rlm_sql (sql) - Query is: SELECT id, nasname, shortname, type, secret, server FROM nas
rlm_sql (sql) - Reserved connection (0)
rlm_sql (sql) - Executing select query: SELECT id, nasname, shortname, type, secret, server FROM nas
rlm_sql (sql) - Adding client 172.30.30.2 (BNG1) to global clients list
rlm_sql (sql) - Client "172.30.30.2" (BNG1) added
rlm_sql (sql) - Adding client 172.30.30.4 (BNG2) to global clients list
rlm_sql (sql) - Client "172.30.30.4" (BNG2) added
rlm_sql (sql) - Adding client 172.30.30.254 (BNG_test_V13) to global clients list
rlm_sql (sql) - Client "172.30.30.254" (BNG_test_V13) added
rlm_sql (sql) - Released connection (0)
rlm_sql (sql) - Need 5 more connections to reach 10 spares
rlm_sql (sql) - Opening additional connection (5), 1 of 27 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
  # Instantiating module "cache_eap" from file /usr/local/etc/raddb/mods-enabled/cache_eap
cache_eap - Driver rlm_cache_rbtree loaded and linked
  # Instantiating module "files" from file /usr/local/etc/raddb/mods-enabled/files
      reading file /usr/local/etc/raddb/mods-config/files/authorize
      reading file /usr/local/etc/raddb/mods-config/files/accounting
      reading file /usr/local/etc/raddb/mods-config/files/pre-proxy
  # Instantiating module "reject" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "fail" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "ok" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "handled" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "invalid" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "userlock" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "notfound" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "noop" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "updated" from file /usr/local/etc/raddb/mods-enabled/always
  # Instantiating module "detail" from file /usr/local/etc/raddb/mods-enabled/detail
  # Instantiating module "expiration" from file /usr/local/etc/raddb/mods-enabled/expiration
  # Instantiating module "linelog" from file /usr/local/etc/raddb/mods-enabled/linelog
  # Instantiating module "log_accounting" from file /usr/local/etc/raddb/mods-enabled/linelog
  # Instantiating module "attr_filter.post-proxy" from file /usr/local/etc/raddb/mods-enabled/attr_filter
      reading file /usr/local/etc/raddb/mods-config/attr_filter/post-proxy
  # Instantiating module "attr_filter.pre-proxy" from file /usr/local/etc/raddb/mods-enabled/attr_filter
      reading file /usr/local/etc/raddb/mods-config/attr_filter/pre-proxy
  # Instantiating module "attr_filter.access_reject" from file /usr/local/etc/raddb/mods-enabled/attr_filter
      reading file /usr/local/etc/raddb/mods-config/attr_filter/access_reject
[/usr/local/etc/raddb/mods-config/attr_filter/access_reject]:11 Check item "FreeRADIUS-Response-Delay" 	found in filter list for realm "DEFAULT".
[/usr/local/etc/raddb/mods-config/attr_filter/access_reject]:11 Check item "FreeRADIUS-Response-Delay-USec" 	found in filter list for realm "DEFAULT".
  # Instantiating module "attr_filter.access_challenge" from file /usr/local/etc/raddb/mods-enabled/attr_filter
      reading file /usr/local/etc/raddb/mods-config/attr_filter/access_challenge
  # Instantiating module "attr_filter.accounting_response" from file /usr/local/etc/raddb/mods-enabled/attr_filter
      reading file /usr/local/etc/raddb/mods-config/attr_filter/accounting_response
  # Instantiating module "auth_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
rlm_detail (auth_log) - 'User-Password' suppressed, will not appear in detail output
  # Instantiating module "reply_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  # Instantiating module "pre_proxy_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  # Instantiating module "post_proxy_log" from file /usr/local/etc/raddb/mods-enabled/detail.log
  # Instantiating module "preprocess" from file /usr/local/etc/raddb/mods-enabled/preprocess
      reading file /usr/local/etc/raddb/mods-config/preprocess/huntgroups
      reading file /usr/local/etc/raddb/mods-config/preprocess/hints
  # Instantiating module "IPASS" from file /usr/local/etc/raddb/mods-enabled/realm
  # Instantiating module "suffix" from file /usr/local/etc/raddb/mods-enabled/realm
  # Instantiating module "realmpercent" from file /usr/local/etc/raddb/mods-enabled/realm
  # Instantiating module "ntdomain" from file /usr/local/etc/raddb/mods-enabled/realm
  # Instantiating module "mschap" from file /usr/local/etc/raddb/mods-enabled/mschap
mschap: using internal authentication
  # Instantiating module "etc_passwd" from file /usr/local/etc/raddb/mods-enabled/passwd
  # Instantiating module "dhcp_sqlippool" from file /usr/local/etc/raddb/mods-enabled/dhcp_sqlippool
  # Instantiating module "logintime" from file /usr/local/etc/raddb/mods-enabled/logintime
radiusd: #### Loading Virtual Servers ####
server inner-tunnel { # from file /usr/local/etc/raddb/sites-enabled/inner-tunnel
/usr/local/etc/raddb/sites-enabled/inner-tunnel[157]: Ignoring "ldap" (see raddb/mods-available/README.rst)
} # server inner-tunnel
server default { # from file /usr/local/etc/raddb/sites-enabled/default
/usr/local/etc/raddb/sites-enabled/default[391]: Ignoring "ldap" (see raddb/mods-available/README.rst)
} # server default
radiusd: #### Opening IP addresses and Ports ####
Listening on auth address 127.0.0.1 port 18120 bound to server inner-tunnel
Listening on auth address * port 1812 bound to server default
Listening on acct address * port 1813 bound to server default
Listening on auth address :: port 1812 bound to server default
Listening on acct address :: port 1813 bound to server default
Ready to process requests

(1)  Received Access-Request Id 178 from 172.30.30.2:64414 to 10.100.1.1:1812 via p1p2 length 122
(1)    User-Name = "84:8f:69:f1:16:0a"
(1)    User-Password = "super_secret"
(1)    NAS-IP-Address = 172.30.30.2
(1)    NAS-Identifier = "vSR_BNG1"
(1)    Alc-Client-Hardware-Addr = "84:8f:69:f1:16:0a"
(1)    Acct-Session-Id = "0002FF00000134578620FF"
(1)  Running section authorize from file /usr/local/etc/raddb/sites-enabled/default
(1)    authorize {
(1)      filter_username {
(1)        if (&User-Name) {
(1)          if (&User-Name =~ / /) {
(1)            ...
(1)          }
(1)          if (&User-Name =~ /@[^@]*@/ ) {
(1)            ...
(1)          }
(1)          if (&User-Name =~ /\.\./ ) {
(1)            ...
(1)          }
(1)          if ((&User-Name =~ /@/) && (&User-Name !~ /@[^.]+(\.[^.]+)+$/)) {
(1)            ...
(1)          }
(1)          if (&User-Name =~ /\.$/) {
(1)            ...
(1)          }
(1)          if (&User-Name =~ /@\./) {
(1)            ...
(1)          }
(1)        } # if (&User-Name) (notfound)
(1)      } # filter_username (notfound)
(1)      preprocess (ok)
(1)      chap (noop)
(1)      mschap (noop)
(1)      digest (noop)
(1)      suffix - Checking for suffix after "@"
(1)      suffix - No '@' in User-Name = "84:8f:69:f1:16:0a", looking up realm NULL
(1)      suffix - No such realm "NULL"
(1)      suffix (noop)
(1)      eap - No EAP-Message, not doing EAP
(1)      eap (noop)
(1)      files (noop)
(1)      sql - EXPAND %{User-Name}
(1)      sql - --> 84:8f:69:f1:16:0a
(1)      sql - SQL-User-Name set to '84:8f:69:f1:16:0a'
(1)      sql - Reserved connection (1)
(1)      sql - EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id
(1)      sql - --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = '84:8f:69:f1:16:0a' ORDER BY id
(1)      sql - Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '84:8f:69:f1:16:0a' ORDER BY id
(1)      sql - User found in radcheck table
(1)      sql - Conditional check items matched, merging assignment check items
(1)      sql -   &Cleartext-Password := "super_secret"
(1)      sql - EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id
(1)      sql - --> SELECT id, username, attribute, value, op FROM radreply WHERE username = '84:8f:69:f1:16:0a' ORDER BY id
(1)      sql - Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = '84:8f:69:f1:16:0a' ORDER BY id
(1)      sql - EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority
(1)      sql - --> SELECT groupname FROM radusergroup WHERE username = '84:8f:69:f1:16:0a' ORDER BY priority
(1)      sql - Executing select query: SELECT groupname FROM radusergroup WHERE username = '84:8f:69:f1:16:0a' ORDER BY priority
(1)      sql - User found in the group table
(1)      sql - EXPAND SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{sql-sql-Group}' ORDER BY id
(1)      sql - --> SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'profile_1' ORDER BY id
(1)      sql - Executing select query: SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = 'profile_1' ORDER BY id
(1)      sql - Group "profile_1": Conditional check items matched
(1)      sql - Group "profile_1": Merging assignment check items
(1)      sql - EXPAND SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{sql-sql-Group}' ORDER BY id
(1)      sql - --> SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'profile_1' ORDER BY id
(1)      sql - Executing select query: SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = 'profile_1' ORDER BY id
(1)      sql - Group "profile_1": Merging reply items
(1)      sql -   &Framed-IP-Address = `%{sql:SELECT framedipaddress from radippool WHERE pool_name='bng1_dyn' AND username='%{Alc-Client-Hardware-Addr}'}`
(1)      sql -   &Alc-Subsc-ID-Str := "`%{Alc-Client-Hardware-Addr}`"
(1)      sql -   &Alc-Subsc-Prof-Str := "sub_profile_1"
(1)      sql -   &Alc-SLA-Prof-Str := "sla_profile_1"
(1)      sql - EXPAND `%{sql:SELECT framedipaddress from radippool WHERE pool_name='bng1_dyn' AND username='%{Alc-Client-Hardware-Addr}'}`
(1)      sql -   Reserved connection (2)
(1)      sql -   Released connection (2)
(1)      sql -   Need 5 more connections to reach 10 spares
(1)      sql -   Opening additional connection (6), 1 of 26 pending slots used
rlm_sql_mysql - Starting connect to MySQL server
rlm_sql_mysql - Connected to database 'radius' on Localhost via UNIX socket, server version 10.0.26-MariaDB-wsrep, protocol version 10
(1)      sql -   Reserved connection (3)
(1)      sql -   Executing select query: SELECT framedipaddress from radippool WHERE pool_name='bng1_dyn' AND username='84:8f:69:f1:16:0a'
(1)      sql -   Released connection (3)
(1)      sql - --> `192.168.10.3`
(1)      sql - EXPAND `%{Alc-Client-Hardware-Addr}`
(1)      sql - --> `84:8f:69:f1:16:0a`
(1)      sql - Released connection (1)
(1)      sql (ok)
(1)      expiration (noop)
(1)      logintime (noop)
(1)      pap (updated)
(1)    } # authorize (updated)
(1)  Using 'Auth-Type = PAP' for authenticate {...}
(1)  Running Auth-Type PAP from file /usr/local/etc/raddb/sites-enabled/default
(1)    Auth-Type PAP {
(1)      pap - Login attempt with password
(1)      pap - Comparing with "known good" Cleartext-Password
(1)      pap - User authenticated successfully
(1)      pap (ok)
(1)    } # Auth-Type PAP (ok)
(1)  Login OK: [84:8f:69:f1:16:0a/super_secret] (from client BNG1 port 0)
(1)  Running section post-auth from file /usr/local/etc/raddb/sites-enabled/default
(1)    post-auth {
(1)      update {
(1)        &reply: skipped: No values available
(1)      } # update (noop)
(1)      sql - EXPAND .query
(1)      sql - --> .query
(1)      sql - Using query template 'query'
(1)      sql - Reserved connection (4)
(1)      sql - EXPAND %{User-Name}
(1)      sql - --> 84:8f:69:f1:16:0a
(1)      sql - SQL-User-Name set to '84:8f:69:f1:16:0a'
(1)      sql - EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')
(1)      sql - --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '84:8f:69:f1:16:0a', 'super_secret', 'Access-Accept', '2016-07-13 12:08:01')
(1)      sql - Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '84:8f:69:f1:16:0a', 'super_secret', 'Access-Accept', '2016-07-13 12:08:01')
(1)      sql - SQL query returned: success
(1)      sql - 1 record(s) updated
(1)      sql - Released connection (4)
(1)      sql (ok)
(1)      exec (noop)
(1)      remove_reply_message_if_eap {
(1)        if (&reply:EAP-Message && &reply:Reply-Message) {
(1)          ...
(1)        }
(1)        else {
(1)          noop (noop)
(1)        } # else (noop)
(1)      } # remove_reply_message_if_eap (noop)
(1)    } # post-auth (ok)
(1)  Sent Access-Accept Id 178 from 10.100.1.1:1812 to 172.30.30.2:64414 via p1p2 length 0
(1)    Framed-IP-Address = 0.0.0.0
(1)    Alc-Subsc-ID-Str = "`84:8f:69:f1:16:0a`"
(1)    Alc-Subsc-Prof-Str = "sub_profile_1"
(1)    Alc-SLA-Prof-Str = "sla_profile_1"
(1)  Finished request
Waking up in 4.9 seconds.
^CWaking up in 2.1 seconds.
Signalled to terminate
Exiting normally



> On 12 Jul 2016, at 17:46, Krzysztof Grobelak <krzysztof.grobelak at enet.ie> wrote:
> 
> Hello Arran,
> 
> Thanks for the response.
>> 
>> In v3.1.x you can use %{xlat:<value>}
> Could you explain a little bit more how to use this feature?
> I’m unclear how to apply this to the value returned from db. I suspect i need to do it in post-auth section.
> 
> I know that I can do the following:
> 
> post-auth {
> 	update reply { 
> 		 Alc-Subsc-ID-Str := "%{Alc-Client-Hardware-Addr}”
> 	}
> }
> 
> And this works but this will update all the reply packet for all successful authentication responses. And i would like to be able to do this per profile.
> 
> Like so:
> <pseudo unlang>
> 
> post-auth {
> 	if (sql:groupname == “profile_1”){ <—"just finding out this would be veery helpful" 
> 		update reply { 
> 			Alc-Subsc-ID-Str := “%{xlat: profile_1.attribute_1}”
> 		}
> 	}
> }
> 
> </pseudo unlang>
> 
> I hope the above illustrates what i’m trying to achieve.
> 
> Actually just being able to check the returned groupname (profile) would get me out of trouble.
> 
>> 
>> But yeah, there should be a quoting column or something I guess.  Fell free to submit patches against v4.0.x.
> Would love to but i’m afraid the complexity of the server is slightly beyond my skill level :/
> 
> Regards,
> Kris
> 
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html




More information about the Freeradius-Users mailing list