# -*- 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 # 5. Server ####################################################################### nas_query = "SELECT id, nasname, shortname, type, secret, server 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" authorize_reply_query = "SELECT id, username, attribute, value, op \ FROM wdsl.framed_reply \ WHERE username = '%{SQL-User-Name}' \ ORDER BY id" # The default queries are case insensitive. (for compatibility with # older versions of FreeRADIUS) #VALID #authorize_check_query = "SELECT sifrakupca as id, user as username, 'Cleartext-Password' as attribute, pass as value,':=' as op \ # FROM wdsl.users \ # WHERE user = '%{SQL-User-Name}' and disabled='no'\ # union all select sifrakupca as id, user as username, 'Simultaneous-Use' as attribute, '1' as value, ':=' as op from wdsl.users WHERE user = '%{SQL-User-Name}' and disabled='no'\ # union all select id, username, attribute, value, ':=' as op from wdsl.pool_name WHERE username = '%{SQL-User-Name}'\ # ORDER BY id" #TEST authorize_check_query = "SELECT sifrakupca as id, user as username, 'Cleartext-Password' as attribute, pass as value,':=' as op \ FROM wdsl.users \ WHERE user = '%{SQL-User-Name}'\ union all select sifrakupca as id, user as username, 'Simultaneous-Use' as attribute, '1' as value, ':=' as op from wdsl.users WHERE user = '%{SQL-User-Name}'\ union all select id, username, attribute, value, ':=' as op from wdsl.pool_name 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" group_membership_query = "SELECT profile as groupname \ FROM wdsl.users \ WHERE user = '%{SQL-User-Name}' \ ORDER BY 1" 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 wdsl.packet_rate_limit_4 \ # WHERE groupname = '%{Sql-Group}' \ # ORDER BY id" authorize_group_reply_query = "SELECT id, groupname, attribute, \ value, op \ FROM wdsl.packet_rate_limit \ WHERE groupname = '%{Sql-Group}' \ union all select '%{Sql-Group}' AS id, '%{Sql-Group}' AS groupname,'Filter-Id' AS attribute, '101.in' AS value,':=' AS op from wdsl.spamer WHERE username = '%{SQL-User-Name}' AND active = '1'\ union all select '%{Sql-Group}' AS id, '%{Sql-Group}' AS groupname, 'Filter-Id' AS attribute, '106.in' AS value, ':=' AS op FROM wdsl.users WHERE user = '%{SQL-User-Name}' AND (disabled = 'yes' OR active = '0')\ # union all select '%{Sql-Group}' AS id, '%{Sql-Group}' AS groupname, 'Filter-Id' AS attribute, '106.in' AS value, ':=' AS op FROM wdsl.users WHERE user = '%{SQL-User-Name}' AND disabled = 'yes'\ 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 ########## used on auth.novi-net.net ################################################################## # #simul_count_query = "SELECT COUNT(*) \ # FROM radippool \ # WHERE username = '%{SQL-User-Name}'" # # #simul_verify_query = "SELECT radacctid, acctsessionid, username, \ # nasipaddress, nasportid, framedipaddress, \ # callingstationid, framedprotocol \ # FROM ${acct_table1} \ # WHERE username = '%{SQL-User-Name}' \ # AND acctstoptime IS NULL" # ####################################################################################################### ########## cisco testing simul_use ################################################################### 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')"