diff options
Diffstat (limited to 'raddb/mods-config/sql/main/mysql/queries.conf')
| -rw-r--r-- | raddb/mods-config/sql/main/mysql/queries.conf | 694 |
1 files changed, 694 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/mysql/queries.conf b/raddb/mods-config/sql/main/mysql/queries.conf new file mode 100644 index 0000000..e7c9782 --- /dev/null +++ b/raddb/mods-config/sql/main/mysql/queries.conf @@ -0,0 +1,694 @@ +# -*- text -*- +# +# main/mysql/queries.conf-- MySQL configuration for default schema (schema.sql) +# +# $Id$ + +# Use the driver specific SQL escape method. +# +# If you enable this configuration item, the "safe_characters" +# configuration is ignored. FreeRADIUS then uses the MySQL escape +# functions to escape input strings. The only downside to making this +# change is that the MySQL escaping method is not the same the one +# used by FreeRADIUS. So characters which are NOT in the +# "safe_characters" list will now be stored differently in the database. +# +#auto_escape = yes + +# Safe characters list for sql queries. Everything else is replaced +# with their mime-encoded equivalents. +# The default list should be ok +# Using 'auto_escape' is preferred +safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" + +####################################################################### +# Connection config +####################################################################### +# The character set is not configurable. The default character set of +# the mysql client library is used. To control the character set, +# create/edit my.cnf (typically in /etc/mysql/my.cnf or /etc/my.cnf) +# and enter +# [client] +# default-character-set = utf8 +# + +####################################################################### +# 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}" + +####################################################################### +# Query config: Event-Timestamp +####################################################################### +# event_timestamp_epoch is the basis for the time inserted into +# accounting records. Typically this will be the Event-Timestamp of the +# accounting request, which is usually provided by a NAS. +# +# Uncomment the next line, if you want the timestamp to be based on the +# request reception time recorded by this server, for example if you +# distrust the provided Event-Timestamp. +#event_timestamp_epoch = "%l" + +event_timestamp_epoch = "%{%{integer:Event-Timestamp}:-%l}" + +# event_timestamp is the SQL snippet for converting an epoch timestamp +# to an SQL date. + +event_timestamp = "FROM_UNIXTIME(${event_timestamp_epoch})" + +####################################################################### +# Query config: Class attribute +####################################################################### +# +# 3.0.22 and later have a "class" column in the accounting table. +# +# However, we do NOT want to break existing configurations by adding +# the Class attribute to the default queries. If we did that, then +# systems using newer versions of the server would fail, because +# there is no "class" column in their accounting tables. +# +# The solution to that is the following "class" subsection. If your +# database has a "class" column for the various tables, then you can +# uncomment the configuration items here. The queries below will +# then automatically insert the Class attribute into radacct, +# radpostauth, etc. +# +class { + # + # Delete the '#' character from each of the configuration + # items in this section. This change puts the Class + # attribute into the various tables. Leave the double-quoted + # string there, as the value for the configuration item. + # + # See also policy.d/accounting, and the "insert_acct_class" + # policy. You will need to list (or uncomment) + # "insert_acct_class" in the "post-auth" section in order to + # create a Class attribute. + # + column_name = # ", class" + packet_xlat = # ", '%{Class}'" + reply_xlat = # ", '%{reply:Class}'" +} + +####################################################################### +# 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 +####################################################################### + +client_query = "\ + SELECT id, nasname, shortname, type, secret, server \ + FROM ${client_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 = '%{${group_attribute}}' \ + ORDER BY id" + +authorize_group_reply_query = "\ + SELECT id, groupname, attribute, \ + value, op \ + FROM ${groupreply_table} \ + WHERE groupname = '%{${group_attribute}}' \ + ORDER BY id" + +####################################################################### +# Simultaneous Use Checking Queries +####################################################################### +# simul_count_query - query for the number of current connections +# - If this is not defined, no simultaneous 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. +# +# Note: Sessions that started prior to the most recent reload of their NAS will +# be correctly considered inactive, even if the radacct entry itself is not +# marked as stopped. +# +####################################################################### + +simul_count_query = "\ + SELECT COUNT(*) \ + FROM ${acct_table1} a \ + LEFT OUTER JOIN nasreload n USING (nasipaddress) \ + WHERE username = '%{SQL-User-Name}' \ + AND acctstoptime IS NULL \ + AND (a.acctstarttime > n.reloadtime OR n.reloadtime IS NULL)" + +simul_verify_query = "\ + SELECT \ + radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \ + callingstationid, framedprotocol \ + FROM ${acct_table1} a \ + LEFT OUTER JOIN nasreload n USING (nasipaddress) \ + WHERE username = '%{SQL-User-Name}' \ + AND acctstoptime IS NULL \ + AND (a.acctstarttime > n.reloadtime OR n.reloadtime IS NULL)" + +####################################################################### +# Accounting and Post-Auth Queries +####################################################################### +# These queries insert/update accounting and authentication records. +# The query to use is determined by the value of 'reference'. +# This value is used as a configuration path and should resolve to one +# or more 'query's. If reference points to multiple queries, and a query +# fails, the next query is executed. +# +# Behaviour is identical to the old 1.x/2.x module, except we can now +# fail between N queries, and query selection can be based on any +# combination of attributes, or custom 'Acct-Status-Type' values. +####################################################################### +accounting { + reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}" + + # Write SQL queries to a logfile. This is potentially useful for bulk inserts + # when used with the rlm_sql_null driver. +# logfile = ${logdir}/accounting.sql + + column_list = "\ + acctsessionid, acctuniqueid, username, \ + realm, nasipaddress, nasportid, \ + nasporttype, acctstarttime, acctupdatetime, \ + acctstoptime, acctsessiontime, acctauthentic, \ + connectinfo_start, connectinfo_stop, acctinputoctets, \ + acctoutputoctets, calledstationid, callingstationid, \ + acctterminatecause, servicetype, framedprotocol, \ + framedipaddress, framedipv6address, framedipv6prefix, \ + framedinterfaceid, delegatedipv6prefix ${..class.column_name}" + + type { + accounting-on { + + # + # "Bulk update" Accounting-On/Off strategy. + # + # Immediately terminate all sessions associated with a + # given NAS. + # + # Note: If a large number of sessions require closing + # then the bulk update may be take a long time to run + # and lock an excessive number of rows. See the + # strategy below for an alternative approach that does + # not touch the radacct session data. + # + query = "\ + UPDATE ${....acct_table1} \ + SET \ + acctstoptime = ${....event_timestamp}, \ + acctsessiontime = '${....event_timestamp_epoch}' \ + - UNIX_TIMESTAMP(acctstarttime), \ + acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \ + WHERE acctstoptime IS NULL \ + AND nasipaddress = '%{NAS-IP-Address}' \ + AND acctstarttime <= ${....event_timestamp}" + + # + # "Lightweight" Accounting-On/Off strategy. + # + # Record the reload time of the NAS and let the + # administrator actually close the sessions in radacct + # out-of-band, if desired. + # + # Implementation advice, together with a stored + # procedure for closing sessions and a view showing + # the effective stop time of each session is provided + # in process-radacct.sql. + # + # To enable this strategy, just change the previous + # query to "-query", and this one to "query". The + # previous one will be ignored, and this one will be + # enabled. + # + -query = "\ + INSERT INTO nasreload \ + SET \ + nasipaddress = '%{NAS-IP-Address}', \ + reloadtime = ${....event_timestamp} \ + ON DUPLICATE KEY UPDATE reloadtime = ${....event_timestamp}" + + } + + accounting-off { + query = "${..accounting-on.query}" + } + + # + # Implement the "sql_session_start" policy. + # See raddb/policy.d/accounting for more details. + # + # You also need to fix the other queries as + # documented below. Look for "sql_session_start". + # + post-auth { + query = "\ + INSERT INTO ${....acct_table1} \ + (${...column_list}) \ + VALUES(\ + '%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \ + NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), \ + '%{NAS-Port-Type}', \ + ${....event_timestamp}, \ + ${....event_timestamp}, \ + NULL, \ + 0, \ + '', \ + '%{Connect-Info}', \ + NULL, \ + 0, \ + 0, \ + '%{Called-Station-Id}', \ + '%{Calling-Station-Id}', \ + '', \ + '%{Service-Type}', \ + NULL, \ + '', \ + '', \ + '', \ + '', \ + '' \ + ${....class.packet_xlat})" + + query = "\ + UPDATE ${....acct_table1} SET \ + AcctStartTime = ${....event_timestamp}, \ + AcctUpdateTime = ${....event_timestamp}, \ + ConnectInfo_start = '%{Connect-Info}', \ + AcctSessionId = '%{Acct-Session-Id}' \ + WHERE UserName = '%{SQL-User-Name}' \ + AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ + AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ + AND NASPortType = '%{NAS-Port-Type}' \ + AND AcctStopTime IS NULL" + } + + start { + # + # Insert a new record into the sessions table + # + query = "\ + INSERT INTO ${....acct_table1} \ + (${...column_list}) \ + VALUES \ + ('%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{NAS-IP-Address}', \ + '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ + '%{NAS-Port-Type}', \ + ${....event_timestamp}, \ + ${....event_timestamp}, \ + NULL, \ + '0', \ + '%{Acct-Authentic}', \ + '%{Connect-Info}', \ + '', \ + '0', \ + '0', \ + '%{Called-Station-Id}', \ + '%{Calling-Station-Id}', \ + '', \ + '%{Service-Type}', \ + '%{Framed-Protocol}', \ + '%{Framed-IP-Address}', \ + '%{Framed-IPv6-Address}', \ + '%{Framed-IPv6-Prefix}', \ + '%{Framed-Interface-Id}', \ + '%{Delegated-IPv6-Prefix}' \ + ${....class.packet_xlat})" + + # + # When using "sql_session_start", you should comment out + # the previous query, and enable this one. + # + # Just change the previous query to "-query", + # and this one to "query". The previous one + # will be ignored, and this one will be + # enabled. + # + -query = "\ + UPDATE ${....acct_table1} \ + SET \ + AcctSessionId = '%{Acct-Session-Id}', \ + AcctUniqueId = '%{Acct-Unique-Session-Id}', \ + AcctAuthentic = '%{Acct-Authentic}', \ + ConnectInfo_start = '%{Connect-Info}', \ + ServiceType = '%{Service-Type}', \ + FramedProtocol = '%{Framed-Protocol}', \ + framedipaddress = '%{Framed-IP-Address}', \ + framedipv6address = '%{Framed-IPv6-Address}', \ + framedipv6prefix = '%{Framed-IPv6-Prefix}', \ + framedinterfaceid = '%{Framed-Interface-Id}', \ + delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', \ + AcctStartTime = ${....event_timestamp}, \ + AcctUpdateTime = ${....event_timestamp} \ + WHERE UserName = '%{SQL-User-Name}' \ + AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ + AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ + AND NASPortType = '%{NAS-Port-Type}' \ + AND AcctStopTime IS NULL" + + # + # Key constraints prevented us from inserting a new session, + # use the alternate query to update an existing session. + # + query = "\ + UPDATE ${....acct_table1} SET \ + acctstarttime = ${....event_timestamp}, \ + acctupdatetime = ${....event_timestamp}, \ + connectinfo_start = '%{Connect-Info}' \ + WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'" + + } + + interim-update { + # + # Update an existing session and calculate the interval + # between the last data we received for the session and this + # update. This can be used to find stale sessions. + # + query = "\ + UPDATE ${....acct_table1} \ + SET \ + acctupdatetime = (@acctupdatetime_old:=acctupdatetime), \ + acctupdatetime = ${....event_timestamp}, \ + acctinterval = ${....event_timestamp_epoch} - \ + UNIX_TIMESTAMP(@acctupdatetime_old), \ + framedipaddress = '%{Framed-IP-Address}', \ + framedipv6address = '%{Framed-IPv6-Address}', \ + framedipv6prefix = '%{Framed-IPv6-Prefix}', \ + framedinterfaceid = '%{Framed-Interface-Id}', \ + delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', \ + acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \ + acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \ + << 32 | '%{%{Acct-Input-Octets}:-0}', \ + acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \ + << 32 | '%{%{Acct-Output-Octets}:-0}' \ + WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'" + + # + # The update condition matched no existing sessions. Use + # the values provided in the update to create a new session. + # + query = "\ + INSERT INTO ${....acct_table1} \ + (${...column_list}) \ + VALUES \ + ('%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{NAS-IP-Address}', \ + '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ + '%{NAS-Port-Type}', \ + FROM_UNIXTIME(${....event_timestamp_epoch} - %{%{Acct-Session-Time}:-0}), \ + ${....event_timestamp}, \ + NULL, \ + %{%{Acct-Session-Time}:-NULL}, \ + '%{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}', \ + '', \ + '%{Service-Type}', \ + '%{Framed-Protocol}', \ + '%{Framed-IP-Address}', \ + '%{Framed-IPv6-Address}', \ + '%{Framed-IPv6-Prefix}', \ + '%{Framed-Interface-Id}', \ + '%{Delegated-IPv6-Prefix}' \ + ${....class.packet_xlat})" + + # + # When using "sql_session_start", you should comment out + # the previous query, and enable this one. + # + # Just change the previous query to "-query", + # and this one to "query". The previous one + # will be ignored, and this one will be + # enabled. + # + -query = "\ + UPDATE ${....acct_table1} \ + SET \ + AcctSessionId = '%{Acct-Session-Id}', \ + AcctUniqueId = '%{Acct-Unique-Session-Id}', \ + AcctAuthentic = '%{Acct-Authentic}', \ + ConnectInfo_start = '%{Connect-Info}', \ + ServiceType = '%{Service-Type}', \ + FramedProtocol = '%{Framed-Protocol}', \ + framedipaddress = '%{Framed-IP-Address}', \ + framedipv6address = '%{Framed-IPv6-Address}', \ + framedipv6prefix = '%{Framed-IPv6-Prefix}', \ + framedinterfaceid = '%{Framed-Interface-Id}', \ + delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', \ + AcctUpdateTime = ${....event_timestamp}, \ + AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \ + AcctInputOctets = '%{%{Acct-Input-Gigawords}:-0}' \ + << 32 | '%{%{Acct-Input-Octets}:-0}', \ + AcctOutputOctets = '%{%{Acct-Output-Gigawords}:-0}' \ + << 32 | '%{%{Acct-Output-Octets}:-0}' \ + WHERE UserName = '%{SQL-User-Name}' \ + AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ + AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ + AND NASPortType = '%{NAS-Port-Type}' \ + AND AcctStopTime IS NULL" + + } + + stop { + # + # Session has terminated, update the stop time and statistics. + # + query = "\ + UPDATE ${....acct_table2} SET \ + acctstoptime = ${....event_timestamp}, \ + acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \ + acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \ + << 32 | '%{%{Acct-Input-Octets}:-0}', \ + acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \ + << 32 | '%{%{Acct-Output-Octets}:-0}', \ + acctterminatecause = '%{Acct-Terminate-Cause}', \ + connectinfo_stop = '%{Connect-Info}' \ + WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'" + + # + # The update condition matched no existing sessions. Use + # the values provided in the update to create a new session. + # + query = "\ + INSERT INTO ${....acct_table2} \ + (${...column_list}) \ + VALUES \ + ('%{Acct-Session-Id}', \ + '%{Acct-Unique-Session-Id}', \ + '%{SQL-User-Name}', \ + '%{Realm}', \ + '%{NAS-IP-Address}', \ + '%{%{NAS-Port-ID}:-%{NAS-Port}}', \ + '%{NAS-Port-Type}', \ + FROM_UNIXTIME(${....event_timestamp_epoch} - %{%{Acct-Session-Time}:-0}), \ + ${....event_timestamp}, \ + ${....event_timestamp}, \ + %{%{Acct-Session-Time}:-NULL}, \ + '%{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}', \ + '%{Framed-IPv6-Address}', \ + '%{Framed-IPv6-Prefix}', \ + '%{Framed-Interface-Id}', \ + '%{Delegated-IPv6-Prefix}' \ + ${....class.packet_xlat})" + + # + # When using "sql_session_start", you should comment out + # the previous query, and enable this one. + # + # Just change the previous query to "-query", + # and this one to "query". The previous one + # will be ignored, and this one will be + # enabled. + # + -query = "\ + UPDATE ${....acct_table1} \ + SET \ + AcctSessionId = '%{Acct-Session-Id}', \ + AcctUniqueId = '%{Acct-Unique-Session-Id}', \ + AcctAuthentic = '%{Acct-Authentic}', \ + ConnectInfo_start = '%{Connect-Info}', \ + ServiceType = '%{Service-Type}', \ + FramedProtocol = '%{Framed-Protocol}', \ + framedipaddress = '%{Framed-IP-Address}', \ + framedipv6address = '%{Framed-IPv6-Address}', \ + framedipv6prefix = '%{Framed-IPv6-Prefix}', \ + framedinterfaceid = '%{Framed-Interface-Id}', \ + delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', \ + AcctStopTime = ${....event_timestamp}, \ + AcctUpdateTime = ${....event_timestamp}, \ + 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}', \ + ConnectInfo_stop = '%{Connect-Info}' \ + WHERE UserName = '%{SQL-User-Name}' \ + AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \ + AND NASPortId = '%{%{NAS-Port-ID}:-%{NAS-Port}}' \ + AND NASPortType = '%{NAS-Port-Type}' \ + AND AcctStopTime IS NULL" + + } + + # + # No Acct-Status-Type == ignore the packet + # + accounting { + query = "SELECT true" + } + } +} + + +####################################################################### +# Authentication Logging Queries +####################################################################### +# postauth_query - Insert some info after authentication +####################################################################### + +post-auth { + # Write SQL queries to a logfile. This is potentially useful for bulk inserts + # when used with the rlm_sql_null driver. +# logfile = ${logdir}/post-auth.sql + + query = "\ + INSERT INTO ${..postauth_table} \ + (username, pass, reply, authdate ${..class.column_name}) \ + VALUES ( \ + '%{SQL-User-Name}', \ + '%{%{User-Password}:-%{Chap-Password}}', \ + '%{reply:Packet-Type}', \ + '%S.%M' \ + ${..class.reply_xlat})" +} |