# -*- text -*- # # main/sqlite/queries.conf -- SQLite configuration for default schema (schema.sql) # # Id: e1e83bf94814ed8be6239977b7bacfed21c0cd6a $ # 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}" ####################################################################### # 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 = "${event_timestamp_epoch}" # NOTE: Recent SQLite versions allow proper arithmetic with dates # stored as strings including comparison using an index, so we keep # these variables differentiated in preparation for switching away from # integer storage. ####################################################################### # 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. ####################################################################### 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 = '%{${group_attribute}}' \ 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} \ - acctstarttime), \ acctterminatecause = '%{Acct-Terminate-Cause}' \ 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 OR REPLACE INTO nasreload (nasipaddress, reloadtime) \ VALUES ('%{NAS-IP-Address}', ${....event_timestamp})" } accounting-off { query = "${..accounting-on.query}" } 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 = ${....event_timestamp}, \ acctinterval = 0, \ 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}', \ (${....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}', \ (${....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}:-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 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})" }