#safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
#######################################################################
- # Query config: Username
+ # Query config: Username
#######################################################################
# This is the username that will get substituted, escaped, and added
- # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
+ # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
# everywhere a username substitution is needed so you you can be sure
# the username passed from the client is escaped properly.
#
- # Uncomment the next line, if you want the sql_user_name to mean:
+ # Uncomment the next line, if you want the sql_user_name to mean:
#
- # Use Stripped-User-Name, if it's there.
- # Else use User-Name, if it's there,
- # Else use hard-coded string "DEFAULT" as the user name.
+ # Use Stripped-User-Name, if it's there.
+ # Else use User-Name, if it's there,
+ # Else use hard-coded string "DEFAULT" as the user name.
#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}"
#
sql_user_name = "%{User-Name}"
#######################################################################
- # Default profile
+ # Default profile
#######################################################################
# This is the default profile. It is found in SQL by group membership.
# That means that this profile must be a member of at least one group
#default_user_profile = "DEFAULT"
#######################################################################
- # NAS Query
+ # NAS Query
#######################################################################
- # This query retrieves the radius clients
+ # This query retrieves the radius clients
#
- # 0. Row ID (currently unused)
- # 1. Name (or IP address)
- # 2. Shortname
- # 3. Type
- # 4. Secret
- # 5. Server
+ # 0. Row ID (currently unused)
+ # 1. Name (or IP address)
+ # 2. Shortname
+ # 3. Type
+ # 4. Secret
+ # 5. Server
#######################################################################
nas_query = "SELECT id, nasname, shortname, type, secret, server FROM ${nas_table}"
#######################################################################
- # Authorization Queries
+ # Authorization Queries
#######################################################################
- # These queries compare the check items for the user
- # in ${authcheck_table} and setup the reply items in
- # ${authreply_table}. You can use any query/tables
- # you want, but the return data for each row MUST
- # be in the following order:
+ # These queries compare the check items for the user
+ # in ${authcheck_table} and setup the reply items in
+ # ${authreply_table}. You can use any query/tables
+ # you want, but the return data for each row MUST
+ # be in the following order:
#
- # 0. Row ID (currently unused)
- # 1. UserName/GroupName
- # 2. Item Attr Name
- # 3. Item Attr Value
- # 4. Item Attr Operation
+ # 0. Row ID (currently unused)
+ # 1. UserName/GroupName
+ # 2. Item Attr Name
+ # 3. Item Attr Value
+ # 4. Item Attr Operation
#######################################################################
# Use these for case sensitive usernames.
-# authorize_check_query = "SELECT id, username, attribute, value, op \
-# FROM ${authcheck_table} \
-# WHERE username = BINARY '%{SQL-User-Name}' \
-# ORDER BY id"
-# authorize_reply_query = "SELECT id, username, attribute, value, op \
-# FROM ${authreply_table} \
-# WHERE username = BINARY '%{SQL-User-Name}' \
-# ORDER BY id"
+# authorize_check_query = " \
+# SELECT id, username, attribute, value, op \
+# FROM ${authcheck_table} \
+# WHERE username = BINARY '%{SQL-User-Name}' \
+# ORDER BY id"
+
+# authorize_reply_query = " \
+# SELECT id, username, attribute, value, op \
+# FROM ${authreply_table} \
+# WHERE username = BINARY '%{SQL-User-Name}' \
+# ORDER BY id"
# The default queries are case insensitive. (for compatibility with
# older versions of FreeRADIUS)
- authorize_check_query = "SELECT id, username, attribute, value, op \
- FROM ${authcheck_table} \
- WHERE username = '%{SQL-User-Name}' \
- ORDER BY id"
- authorize_reply_query = "SELECT id, username, attribute, value, op \
- FROM ${authreply_table} \
- WHERE username = '%{SQL-User-Name}' \
- ORDER BY id"
+ authorize_check_query = " \
+ SELECT id, username, attribute, value, op \
+ FROM ${authcheck_table} \
+ WHERE username = '%{SQL-User-Name}' \
+ ORDER BY id"
+
+ authorize_reply_query = " \
+ SELECT id, username, attribute, value, op \
+ FROM ${authreply_table} \
+ WHERE username = '%{SQL-User-Name}' \
+ ORDER BY id"
# Use these for case sensitive usernames.
-# group_membership_query = "SELECT groupname \
-# FROM ${usergroup_table} \
-# WHERE username = BINARY '%{SQL-User-Name}' \
-# ORDER BY priority"
+# group_membership_query = " \
+# SELECT groupname \
+# FROM ${usergroup_table} \
+# WHERE username = BINARY '%{SQL-User-Name}' \
+# ORDER BY priority"
+
+ group_membership_query = " \
+ SELECT groupname \
+ FROM ${usergroup_table} \
+ WHERE username = '%{SQL-User-Name}' \
+ ORDER BY priority"
- group_membership_query = "SELECT groupname \
- FROM ${usergroup_table} \
- WHERE username = '%{SQL-User-Name}' \
- ORDER BY priority"
+ authorize_group_check_query = " \
+ SELECT id, groupname, attribute, \
+ Value, op \
+ FROM ${groupcheck_table} \
+ WHERE groupname = '%{Sql-Group}' \
+ ORDER BY id"
- authorize_group_check_query = "SELECT id, groupname, attribute, \
- Value, op \
- FROM ${groupcheck_table} \
- WHERE groupname = '%{Sql-Group}' \
- ORDER BY id"
- authorize_group_reply_query = "SELECT id, groupname, attribute, \
- value, op \
- FROM ${groupreply_table} \
- WHERE groupname = '%{Sql-Group}' \
- ORDER BY id"
+ authorize_group_reply_query = " \
+ SELECT id, groupname, attribute, \
+ value, op \
+ FROM ${groupreply_table} \
+ WHERE groupname = '%{Sql-Group}' \
+ ORDER BY id"
#######################################################################
- # Accounting Queries
+ # Accounting Queries
#######################################################################
# accounting_onoff_query - query for Accounting On/Off packets
# accounting_update_query - query for Accounting update packets
# accounting_update_query_alt - query for Accounting update packets
- # (alternate in case first query fails)
+ # (alternate in case first query fails)
# accounting_start_query - query for Accounting start packets
# accounting_start_query_alt - query for Accounting start packets
- # (alternate in case first query fails)
+ # (alternate in case first query fails)
# accounting_stop_query - query for Accounting stop packets
# accounting_stop_query_alt - query for Accounting start packets
- # (alternate in case first query doesn't
+ # (alternate in case first query doesn't
# affect any existing rows in the table)
#######################################################################
- accounting_onoff_query = "\
- UPDATE ${acct_table1} \
- SET \
- acctstoptime = '%S', \
- acctsessiontime = unix_timestamp('%S') - \
- unix_timestamp(acctstarttime), \
- acctterminatecause = '%{Acct-Terminate-Cause}', \
- acctstopdelay = %{%{Acct-Delay-Time}:-0} \
- WHERE acctstoptime IS NULL \
- AND nasipaddress = '%{NAS-IP-Address}' \
- AND acctstarttime <= '%S'"
+ accounting_onoff_query = " \
+ UPDATE ${acct_table1} \
+ SET \
+ acctstoptime = '%S', \
+ acctsessiontime = unix_timestamp('%S') - \
+ unix_timestamp(acctstarttime), \
+ acctterminatecause = '%{Acct-Terminate-Cause}', \
+ acctstopdelay = %{%{Acct-Delay-Time}:-0} \
+ WHERE acctstoptime IS NULL \
+ AND nasipaddress = '%{NAS-IP-Address}' \
+ AND acctstarttime <= '%S'"
accounting_update_query = " \
- UPDATE ${acct_table1} \
- SET \
- framedipaddress = '%{Framed-IP-Address}', \
- acctsessiontime = '%{Acct-Session-Time}', \
- acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
- '%{%{Acct-Input-Octets}:-0}', \
- acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
- '%{%{Acct-Output-Octets}:-0}' \
- WHERE acctsessionid = '%{Acct-Session-Id}' \
- AND username = '%{SQL-User-Name}' \
- AND nasipaddress = '%{NAS-IP-Address}'"
+ UPDATE ${acct_table1} \
+ SET \
+ framedipaddress = '%{Framed-IP-Address}', \
+ acctsessiontime = '%{Acct-Session-Time}', \
+ acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
+ '%{%{Acct-Input-Octets}:-0}', \
+ acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
+ '%{%{Acct-Output-Octets}:-0}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
accounting_update_query_alt = " \
- INSERT INTO ${acct_table1} \
- (acctsessionid, acctuniqueid, username, \
- realm, nasipaddress, nasportid, \
- nasporttype, acctstarttime, acctsessiontime, \
- acctauthentic, connectinfo_start, acctinputoctets, \
- acctoutputoctets, calledstationid, callingstationid, \
- servicetype, framedprotocol, framedipaddress, \
- acctstartdelay, xascendsessionsvrkey) \
- VALUES \
- ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
- '%{SQL-User-Name}', \
- '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
- '%{NAS-Port-Type}', \
- DATE_SUB('%S', \
- INTERVAL (%{%{Acct-Session-Time}:-0} + \
+ INSERT INTO ${acct_table1} \
+ (acctsessionid, acctuniqueid, username, \
+ realm, nasipaddress, nasportid, \
+ nasporttype, acctstarttime, acctsessiontime, \
+ acctauthentic, connectinfo_start, acctinputoctets, \
+ acctoutputoctets, calledstationid, callingstationid, \
+ servicetype, framedprotocol, framedipaddress, \
+ acctstartdelay, xascendsessionsvrkey) \
+ VALUES \
+ ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
+ '%{NAS-Port-Type}', \
+ DATE_SUB('%S', \
+ INTERVAL (%{%{Acct-Session-Time}:-0} + \
%{%{Acct-Delay-Time}:-0}) SECOND), \
- '%{Acct-Session-Time}', \
- '%{Acct-Authentic}', '', \
- '%{%{Acct-Input-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}', \
- '0', '%{X-Ascend-Session-Svr-Key}')"
+ '%{Acct-Session-Time}', \
+ '%{Acct-Authentic}', '', \
+ '%{%{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}', \
+ '0', '%{X-Ascend-Session-Svr-Key}')"
accounting_start_query = " \
- INSERT INTO ${acct_table1} \
- (acctsessionid, acctuniqueid, username, \
- realm, nasipaddress, nasportid, \
- nasporttype, acctstarttime, acctstoptime, \
- acctsessiontime, acctauthentic, connectinfo_start, \
- connectinfo_stop, acctinputoctets, acctoutputoctets, \
- calledstationid, callingstationid, acctterminatecause, \
- servicetype, framedprotocol, framedipaddress, \
- acctstartdelay, acctstopdelay, xascendsessionsvrkey) \
- VALUES \
- ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
- '%{SQL-User-Name}', \
- '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
- '%{NAS-Port-Type}', '%S', NULL, \
- '0', '%{Acct-Authentic}', '%{Connect-Info}', \
- '', '0', '0', \
- '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
- '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
- '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
+ INSERT INTO ${acct_table1} \
+ (acctsessionid, acctuniqueid, username, \
+ realm, nasipaddress, nasportid, \
+ nasporttype, acctstarttime, acctstoptime, \
+ acctsessiontime, acctauthentic, connectinfo_start, \
+ connectinfo_stop, acctinputoctets, acctoutputoctets, \
+ calledstationid, callingstationid, acctterminatecause, \
+ servicetype, framedprotocol, framedipaddress, \
+ acctstartdelay, acctstopdelay, xascendsessionsvrkey) \
+ VALUES \
+ ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
+ '%{NAS-Port-Type}', '%S', NULL, \
+ '0', '%{Acct-Authentic}', '%{Connect-Info}', \
+ '', '0', '0', \
+ '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
+ '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
+ '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
- accounting_start_query_alt = " \
- UPDATE ${acct_table1} SET \
- acctstarttime = '%S', \
- acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \
- connectinfo_start = '%{Connect-Info}' \
- WHERE acctsessionid = '%{Acct-Session-Id}' \
- AND username = '%{SQL-User-Name}' \
- AND nasipaddress = '%{NAS-IP-Address}'"
+ accounting_start_query_alt = " \
+ UPDATE ${acct_table1} SET \
+ acctstarttime = '%S', \
+ acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \
+ connectinfo_start = '%{Connect-Info}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
accounting_stop_query = " \
- UPDATE ${acct_table2} SET \
- acctstoptime = '%S', \
- acctsessiontime = '%{Acct-Session-Time}', \
- acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
- '%{%{Acct-Input-Octets}:-0}', \
- acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
- '%{%{Acct-Output-Octets}:-0}', \
- acctterminatecause = '%{Acct-Terminate-Cause}', \
- acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \
- connectinfo_stop = '%{Connect-Info}' \
- WHERE acctsessionid = '%{Acct-Session-Id}' \
- AND username = '%{SQL-User-Name}' \
- AND nasipaddress = '%{NAS-IP-Address}'"
+ UPDATE ${acct_table2} SET \
+ acctstoptime = '%S', \
+ acctsessiontime = '%{Acct-Session-Time}', \
+ acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
+ '%{%{Acct-Input-Octets}:-0}', \
+ acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
+ '%{%{Acct-Output-Octets}:-0}', \
+ acctterminatecause = '%{Acct-Terminate-Cause}', \
+ acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \
+ connectinfo_stop = '%{Connect-Info}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
accounting_stop_query_alt = " \
- INSERT INTO ${acct_table2} \
- (acctsessionid, acctuniqueid, username, \
- realm, nasipaddress, nasportid, \
- nasporttype, acctstarttime, acctstoptime, \
- acctsessiontime, acctauthentic, connectinfo_start, \
- connectinfo_stop, acctinputoctets, acctoutputoctets, \
- calledstationid, callingstationid, acctterminatecause, \
- servicetype, framedprotocol, framedipaddress, \
- acctstartdelay, acctstopdelay) \
- VALUES \
- ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
- '%{SQL-User-Name}', \
- '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
- '%{NAS-Port-Type}', \
- DATE_SUB('%S', \
- INTERVAL (%{%{Acct-Session-Time}:-0} + \
- %{%{Acct-Delay-Time}:-0}) SECOND), \
- '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
- '%{Connect-Info}', \
- '%{%{Acct-Input-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}', \
- '0', '%{%{Acct-Delay-Time}:-0}')"
+ INSERT INTO ${acct_table2} \
+ (acctsessionid, acctuniqueid, username, \
+ realm, nasipaddress, nasportid, \
+ nasporttype, acctstarttime, acctstoptime, \
+ acctsessiontime, acctauthentic, connectinfo_start, \
+ connectinfo_stop, acctinputoctets, acctoutputoctets, \
+ calledstationid, callingstationid, acctterminatecause, \
+ servicetype, framedprotocol, framedipaddress, \
+ acctstartdelay, acctstopdelay) \
+ VALUES \
+ ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
+ '%{NAS-Port-Type}', \
+ DATE_SUB('%S', \
+ INTERVAL (%{%{Acct-Session-Time}:-0} + \
+ %{%{Acct-Delay-Time}:-0}) SECOND), \
+ '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
+ '%{Connect-Info}', \
+ '%{%{Acct-Input-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}', \
+ '0', '%{%{Acct-Delay-Time}:-0}')"
#######################################################################
# Simultaneous Use Checking Queries
#######################################################################
# Uncomment simul_count_query to enable simultaneous use checking
- #simul_count_query = "SELECT COUNT(*) \
- #FROM ${acct_table1} \
- #WHERE username = '%{SQL-User-Name}' \
- #AND acctstoptime IS NULL"
+# simul_count_query = " \
+# SELECT COUNT(*) \
+# FROM ${acct_table1} \
+# WHERE username = '%{SQL-User-Name}' \
+# AND acctstoptime IS NULL"
- simul_verify_query = "SELECT radacctid, acctsessionid, username, \
- nasipaddress, nasportid, framedipaddress, \
- callingstationid, framedprotocol \
- FROM ${acct_table1} \
- WHERE username = '%{SQL-User-Name}' \
- AND acctstoptime IS NULL"
+ simul_verify_query = " \
+ SELECT radacctid, acctsessionid, username, \
+ nasipaddress, nasportid, framedipaddress, \
+ callingstationid, framedprotocol \
+ FROM ${acct_table1} \
+ WHERE username = '%{SQL-User-Name}' \
+ AND acctstoptime IS NULL"
#######################################################################
# Authentication Logging Queries
#######################################################################
- # postauth_query - Insert some info after authentication
+ # postauth_query - Insert some info after authentication
#######################################################################
- postauth_query = "INSERT INTO ${postauth_table} \
- (username, pass, reply, authdate) \
- VALUES ( \
- '%{User-Name}', \
- '%{%{User-Password}:-%{Chap-Password}}', \
- '%{reply:Packet-Type}', '%S')"
+ postauth_query = " \
+ INSERT INTO ${postauth_table} \
+ (username, pass, reply, authdate) \
+ VALUES ( \
+ '%{SQL-User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', '%S')"