--- /dev/null
+# $Id$
+#
+# FreeRADIUS "dialup" SQL Queries for the MySQL Dialect
+
+ # 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 AcctSessionTime = 0 \
+ AND AcctStopTime = 0 \
+ 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', '0', \
+ '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 = 0"
+
+ simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, \
+ NASIPAddress, NASPortId, FramedIPAddress, \
+ CallingStationId, FramedProtocol \
+ FROM ${acct_table1} \
+ WHERE UserName='%{SQL-User-Name}' \
+ AND AcctStopTime = 0"
+
+ #######################################################################
+ # Authentication Logging Queries
+ #######################################################################
+ # postauth_query - Insert some info after authentication
+ #######################################################################
+
+ postauth_query = "INSERT INTO ${postauth_table} \
+ (user, pass, reply, date) \
+ VALUES ( \
+ '%{User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', '%S')"
+
projects / freeradius.git / commitdiff