# -*- text -*- ## ## dialup.conf -- MySQL configuration for default schema (schema.sql) ## ## $Id$ # Safe characters list for sql queries. Everything else is replaced # with their mime-encoded equivalents. # The default list should be ok #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" ####################################################################### # 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 # 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: # # 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 ####################################################################### # 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 # which will contain the corresponding check and reply items. # This profile will be queried in the authorize section for every user. # The point is to assign all users a default profile without having to # manually add each one to a group that will contain the profile. # The SQL module will also honor the User-Profile attribute. This # attribute can be set anywhere in the authorize section (ie the users # file). It is found exactly as the default profile is found. # If it is set then it will *overwrite* the default profile setting. # The idea is to select profiles based on checks on the incoming packets, # not on user group membership. For example: # -- users file -- # DEFAULT Service-Type == Outbound-User, User-Profile := "outbound" # DEFAULT Service-Type == Framed-User, User-Profile := "framed" # # By default the default_user_profile is not set # #default_user_profile = "DEFAULT" ####################################################################### # NAS Query ####################################################################### # This query retrieves the radius clients # # 0. Row ID (currently unused) # 1. Name (or IP address) # 2. Shortname # 3. Type # 4. Secret ####################################################################### nas_query = "SELECT id, nasname, shortname, type, secret FROM ${nas_table}" ####################################################################### # 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: # # 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" # 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" # 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 = '%{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_reply_query = "SELECT id, groupname, attribute, \ value, op \ FROM ${groupreply_table} \ WHERE groupname = '%{Sql-Group}' \ ORDER BY id" ####################################################################### # 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) # accounting_start_query - query for Accounting start packets # accounting_start_query_alt - query for Accounting start packets # (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 # 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_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}'" 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} + \ %{%{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}')" 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}')" 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}'" 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}')" ####################################################################### # Simultaneous Use Checking Queries ####################################################################### # simul_count_query - query for the number of current connections # - If this is not defined, no simultaneouls use checking # - will be performed by this module instance # simul_verify_query - query to return details of current connections for verification # - Leave blank or commented out to disable verification step # - Note that the returned field order should not be changed. ####################################################################### # 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_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 INTO ${postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', '%S')"