WebSVN – ALCASAR – Diff – /conf/radius/queries.conf


# -*- text -*-
##
## query.conf -- MySQL configuration for default schema (schema.sql)
##
##	$Id: dialup.conf 983 2012-08-16 13:34:14Z franck $
 
	# Safe characters list for sql queries. Everything else is replaced
	# with their mime-encoded equivalents.
	# The default list should be ok (define in 'expr' module
	#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 = "ldap"
 
	#######################################################################
	#  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}')"
# We add the following patch (freeradius V2 to V3) in order ALCASAR works
# be carefull : 'tolower' need now the 'expr' module
accounting {
	reference = "%{tolower:type.%{Acct-Status-Type}.query}"
	type {
		accounting-on {
			query = "${....accounting_onoff_query}"
		}
		accounting-off {
			query = "${....accounting_onoff_query}"
		}
		start {
			query = "${....accounting_start_query}"
			query = "${....accounting_start_query_alt}"
		}
		interim-update {
			query = "${....accounting_update_query}"
			query = "${....accounting_update_query_alt}"
		}
		stop {
			query = "${....accounting_stop_query}"
			query = "${....accounting_stop_query_alt}"
		}
	}
}
	#######################################################################
	# 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
	#######################################################################
#                          (username, pass, reply, authdate) \
#                          '%{%{User-Password}:-%{Chap-Password}}', \
 
	postauth_query = "INSERT INTO ${postauth_table} \
                          (username, reply, authdate) \
                          VALUES ( \
                          '%{User-Name}', \
                          '%{reply:Packet-Type}', '%S')"
# We add the following patch (freeradius V2 to V3) in order ALCASAR works
post-auth {
	query = "${..postauth_query}"
}
 
Subversion Repositories ALCASAR

(root)/conf/radius/queries.conf – Rev 2422 → 2438
