diff options
Diffstat (limited to 'raddb/mods-config/sql/main/mysql')
| -rw-r--r-- | raddb/mods-config/sql/main/mysql/extras/wimax/queries.conf | 40 | ||||
| -rw-r--r-- | raddb/mods-config/sql/main/mysql/extras/wimax/schema.sql | 16 | ||||
| -rw-r--r-- | raddb/mods-config/sql/main/mysql/process-radacct.sql | 289 | ||||
| -rw-r--r-- | raddb/mods-config/sql/main/mysql/queries.conf | 694 | ||||
| -rw-r--r-- | raddb/mods-config/sql/main/mysql/schema.sql | 179 | ||||
| -rwxr-xr-x | raddb/mods-config/sql/main/mysql/setup.sql | 40 |
6 files changed, 1258 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/mysql/extras/wimax/queries.conf b/raddb/mods-config/sql/main/mysql/extras/wimax/queries.conf new file mode 100644 index 0000000..2694230 --- /dev/null +++ b/raddb/mods-config/sql/main/mysql/extras/wimax/queries.conf @@ -0,0 +1,40 @@ +# -*- text -*- +## +## wimax.conf -- MySQL configuration for WiMAX keying +## +## $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}" + +####################################################################### +# Logging of WiMAX SPI -> key mappings +####################################################################### +# postauth_query - Insert some info after authentication +####################################################################### + +postauth_query = "INSERT INTO wimax \ + (username, authdate, spi, mipkey, lifetime) \ + VALUES ( \ + '%{User-Name}', '%S' \ + '%{%{reply:WiMAX-MN-hHA-MIP4-SPI}:-%{reply:WiMAX-MN-hHA-MIP6-SPI}}', \ + '%{%{reply:WiMAX-MN-hHA-MIP4-Key}:-%{reply:WiMAX-MN-hHA-MIP6-Key}}', '%{%{reply:Session-Timeout}:-86400}' )" diff --git a/raddb/mods-config/sql/main/mysql/extras/wimax/schema.sql b/raddb/mods-config/sql/main/mysql/extras/wimax/schema.sql new file mode 100644 index 0000000..e32224a --- /dev/null +++ b/raddb/mods-config/sql/main/mysql/extras/wimax/schema.sql @@ -0,0 +1,16 @@ +# +# WiMAX Table structure for table 'wimax', +# which replaces the "radpostauth" table. +# + +CREATE TABLE wimax ( + id int(11) NOT NULL auto_increment, + username varchar(64) NOT NULL default '', + authdate timestamp NOT NULL, + spi varchar(16) NOT NULL default '', + mipkey varchar(400) NOT NULL default '', + lifetime int(12) default NULL, + PRIMARY KEY (id), + KEY username (username), + KEY spi (spi) +) ; diff --git a/raddb/mods-config/sql/main/mysql/process-radacct.sql b/raddb/mods-config/sql/main/mysql/process-radacct.sql new file mode 100644 index 0000000..8902338 --- /dev/null +++ b/raddb/mods-config/sql/main/mysql/process-radacct.sql @@ -0,0 +1,289 @@ +# -*- text -*- +# +# main/mysql/process-radacct.sql -- Schema extensions for processing radacct entries +# +# $Id$ + +-- --------------------------------- +-- - Per-user data usage over time - +-- --------------------------------- +-- +-- An extension to the standard schema to hold per-user data usage statistics +-- for arbitrary periods. +-- +-- The data_usage_by_period table is populated by periodically calling the +-- fr_new_data_usage_period stored procedure. +-- +-- This table can be queried in various ways to produce reports of aggregate +-- data use over time. For example, if the fr_new_data_usage_period SP is +-- invoked one per day just after midnight, to produce usage data with daily +-- granularity, then a reasonably accurate monthly bandwidth summary for a +-- given user could be obtained with: +-- +-- SELECT +-- DATE_FORMAT(period_start, '%Y-%M') AS month, +-- SUM(acctinputoctets)/1000/1000/1000 AS GB_in, +-- SUM(acctoutputoctets)/1000/1000/1000 AS GB_out +-- FROM +-- data_usage_by_period +-- WHERE +-- username='bob' AND +-- period_end IS NOT NULL +-- GROUP BY +-- YEAR(period_start), MONTH(period_start); +-- +-- +----------------+----------------+-----------------+ +-- | month | GB_in | GB_out | +-- +----------------+----------------+-----------------+ +-- | 2019-July | 5.782279230000 | 50.545664820000 | +-- | 2019-August | 4.230543340000 | 48.523096420000 | +-- | 2019-September | 4.847360590000 | 48.631835480000 | +-- | 2019-October | 6.456763250000 | 51.686231930000 | +-- | 2019-November | 6.362537730000 | 52.385710570000 | +-- | 2019-December | 4.301524440000 | 50.762240270000 | +-- | 2020-January | 5.436280540000 | 49.067775280000 | +-- +----------------+----------------+-----------------+ +-- 7 rows in set (0.000 sec) +-- +CREATE TABLE data_usage_by_period ( + username VARCHAR(64), + period_start DATETIME, + period_end DATETIME, + acctinputoctets BIGINT(20), + acctoutputoctets BIGINT(20), + PRIMARY KEY (username,period_start) +); +CREATE INDEX idx_data_usage_by_period_period_start ON data_usage_by_period (period_start); +CREATE INDEX idx_data_usage_by_period_period_end ON data_usage_by_period (period_end); + + +-- +-- Stored procedure that when run with some arbitrary frequency, say +-- once per day by cron, will process the recent radacct entries to extract +-- time-windowed data containing acct{input,output}octets ("data usage") per +-- username, per period. +-- +-- Each invocation will create new rows in the data_usage_by_period tables +-- containing the data used by each user since the procedure was last invoked. +-- The intervals do not need to be identical but care should be taken to +-- ensure that the start/end of each period aligns well with any intended +-- reporting intervals. +-- +-- It can be invoked by running: +-- +-- CALL fr_new_data_usage_period(); +-- +-- +DELIMITER $$ + +DROP PROCEDURE IF EXISTS fr_new_data_usage_period; +CREATE PROCEDURE fr_new_data_usage_period () +SQL SECURITY INVOKER +BEGIN + + DECLARE v_start DATETIME; + DECLARE v_end DATETIME; + + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + ROLLBACK; + RESIGNAL; + END; + + SELECT IFNULL(DATE_ADD(MAX(period_end), INTERVAL 1 SECOND), FROM_UNIXTIME(0)) INTO v_start FROM data_usage_by_period; + SELECT NOW() INTO v_end; + + START TRANSACTION; + + -- + -- Add the data usage for the sessions that were active in the current + -- period to the table. Include all sessions that finished since the start + -- of this period as well as those still ongoing. + -- + INSERT INTO data_usage_by_period (username, period_start, period_end, acctinputoctets, acctoutputoctets) + SELECT * + FROM ( + SELECT + username, + v_start, + v_end, + SUM(acctinputoctets) AS acctinputoctets, + SUM(acctoutputoctets) AS acctoutputoctets + FROM (( + SELECT + username, acctinputoctets, acctoutputoctets + FROM + radacct + WHERE + acctstoptime > v_start + ) UNION ALL ( + SELECT + username, acctinputoctets, acctoutputoctets + FROM + radacct + WHERE + acctstoptime IS NULL + )) AS a + GROUP BY + username + ) AS s + ON DUPLICATE KEY UPDATE + acctinputoctets = data_usage_by_period.acctinputoctets + s.acctinputoctets, + acctoutputoctets = data_usage_by_period.acctoutputoctets + s.acctoutputoctets, + period_end = v_end; + + -- + -- Create an open-ended "next period" for all ongoing sessions and carry a + -- negative value of their data usage to avoid double-accounting when we + -- process the next period. Their current data usage has already been + -- allocated to the current and possibly previous periods. + -- + INSERT INTO data_usage_by_period (username, period_start, period_end, acctinputoctets, acctoutputoctets) + SELECT * + FROM ( + SELECT + username, + DATE_ADD(v_end, INTERVAL 1 SECOND), + NULL, + 0 - SUM(acctinputoctets), + 0 - SUM(acctoutputoctets) + FROM + radacct + WHERE + acctstoptime IS NULL + GROUP BY + username + ) AS s; + + COMMIT; + +END$$ + +DELIMITER ; + + +-- ------------------------------------------------------ +-- - "Lightweight" Accounting-On/Off strategy resources - +-- ------------------------------------------------------ +-- +-- The following resources are for use only when the "lightweight" +-- Accounting-On/Off strategy is enabled in queries.conf. +-- +-- Instead of bulk closing the radacct sessions belonging to a reloaded NAS, +-- this strategy leaves them open and records the NAS reload time in the +-- nasreload table. +-- +-- Where applicable, the onus is on the administator to: +-- +-- * Consider the nas reload times when deriving a list of +-- active/inactive sessions, and when determining the duration of sessions +-- interrupted by a NAS reload. (Refer to the view below.) +-- +-- * Close the affected sessions out of band. (Refer to the SP below.) +-- +-- +-- The radacct_with_reloads view presents the radacct table with two additional +-- columns: acctstoptime_with_reloads and acctsessiontime_with_reloads +-- +-- Where the session isn't closed (acctstoptime IS NULL), yet it started before +-- the last reload of the NAS (radacct.acctstarttime < nasreload.reloadtime), +-- the derived columns are set based on the reload time of the NAS (effectively +-- the point in time that the session was interrupted.) +-- +CREATE VIEW radacct_with_reloads AS +SELECT + a.*, + COALESCE(a.acctstoptime, + IF(a.acctstarttime < n.reloadtime, n.reloadtime, NULL) + ) AS acctstoptime_with_reloads, + COALESCE(a.acctsessiontime, + IF(a.acctstoptime IS NULL AND a.acctstarttime < n.reloadtime, + UNIX_TIMESTAMP(n.reloadtime) - UNIX_TIMESTAMP(a.acctstarttime), NULL) + ) AS acctsessiontime_with_reloads +FROM radacct a +LEFT OUTER JOIN nasreload n USING (nasipaddress); + + +-- +-- It may be desirable to periodically "close" radacct sessions belonging to a +-- reloaded NAS, replicating the "bulk close" Accounting-On/Off behaviour, +-- just not in real time. +-- +-- The fr_radacct_close_after_reload SP will set radacct.acctstoptime to +-- nasreload.reloadtime, calculate the corresponding radacct.acctsessiontime, +-- and set acctterminatecause to "NAS reboot" for interrupted sessions. It +-- does so in batches, which avoids long-lived locks on the affected rows. +-- +-- It can be invoked as follows: +-- +-- CALL fr_radacct_close_after_reload(); +-- +-- Note: This SP walks radacct in strides of v_batch_size. It will typically +-- skip closed and ongoing sessions at a rate significantly faster than +-- 100,000 rows per second and process batched updates faster than 20,000 +-- orphaned sessions per second. If this isn't fast enough then you should +-- really consider using a custom schema that includes partitioning by +-- nasipaddress or acct{start,stop}time. +-- +DELIMITER $$ + +DROP PROCEDURE IF EXISTS fr_radacct_close_after_reload; +CREATE PROCEDURE fr_radacct_close_after_reload () +SQL SECURITY INVOKER +BEGIN + + DECLARE v_a BIGINT(21); + DECLARE v_z BIGINT(21); + DECLARE v_updated BIGINT(21) DEFAULT 0; + DECLARE v_last_report DATETIME DEFAULT 0; + DECLARE v_last BOOLEAN DEFAULT FALSE; + DECLARE v_batch_size INT(12); + + -- + -- This works for many circumstances + -- + SET v_batch_size = 2500; + + SELECT MIN(radacctid) INTO v_a FROM radacct WHERE acctstoptime IS NULL; + + update_loop: LOOP + + SET v_z = NULL; + SELECT radacctid INTO v_z FROM radacct WHERE radacctid > v_a ORDER BY radacctid LIMIT v_batch_size,1; + + IF v_z IS NULL THEN + SELECT MAX(radacctid) INTO v_z FROM radacct; + SET v_last = TRUE; + END IF; + + UPDATE radacct a INNER JOIN nasreload n USING (nasipaddress) + SET + acctstoptime = n.reloadtime, + acctsessiontime = UNIX_TIMESTAMP(n.reloadtime) - UNIX_TIMESTAMP(acctstarttime), + acctterminatecause = 'NAS reboot' + WHERE + radacctid BETWEEN v_a AND v_z + AND acctstoptime IS NULL + AND acctstarttime < n.reloadtime; + + SET v_updated = v_updated + ROW_COUNT(); + + SET v_a = v_z + 1; + + -- + -- Periodically report how far we've got + -- + IF v_last_report != NOW() OR v_last THEN + SELECT v_z AS latest_radacctid, v_updated AS sessions_closed; + SET v_last_report = NOW(); + END IF; + + IF v_last THEN + LEAVE update_loop; + END IF; + + END LOOP; + +END$$ + +DELIMITER ; 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})" +} diff --git a/raddb/mods-config/sql/main/mysql/schema.sql b/raddb/mods-config/sql/main/mysql/schema.sql new file mode 100644 index 0000000..84846b2 --- /dev/null +++ b/raddb/mods-config/sql/main/mysql/schema.sql @@ -0,0 +1,179 @@ +########################################################################### +# $Id$ # +# # +# schema.sql rlm_sql - FreeRADIUS SQL Module # +# # +# Database schema for MySQL rlm_sql module # +# # +# To load: # +# mysql -uroot -prootpass radius < schema.sql # +# # +# Mike Machado <mike@innercite.com> # +########################################################################### +# +# Table structure for table 'radacct' +# + +CREATE TABLE IF NOT EXISTS radacct ( + radacctid bigint(21) NOT NULL auto_increment, + acctsessionid varchar(64) NOT NULL default '', + acctuniqueid varchar(32) NOT NULL default '', + username varchar(64) NOT NULL default '', + realm varchar(64) default '', + nasipaddress varchar(15) NOT NULL default '', + nasportid varchar(32) default NULL, + nasporttype varchar(32) default NULL, + acctstarttime datetime NULL default NULL, + acctupdatetime datetime NULL default NULL, + acctstoptime datetime NULL default NULL, + acctinterval int(12) default NULL, + acctsessiontime int(12) unsigned default NULL, + acctauthentic varchar(32) default NULL, + connectinfo_start varchar(128) default NULL, + connectinfo_stop varchar(128) default NULL, + acctinputoctets bigint(20) default NULL, + acctoutputoctets bigint(20) default NULL, + calledstationid varchar(50) NOT NULL default '', + callingstationid varchar(50) NOT NULL default '', + acctterminatecause varchar(32) NOT NULL default '', + servicetype varchar(32) default NULL, + framedprotocol varchar(32) default NULL, + framedipaddress varchar(15) NOT NULL default '', + framedipv6address varchar(45) NOT NULL default '', + framedipv6prefix varchar(45) NOT NULL default '', + framedinterfaceid varchar(44) NOT NULL default '', + delegatedipv6prefix varchar(45) NOT NULL default '', + class varchar(64) default NULL, + PRIMARY KEY (radacctid), + UNIQUE KEY acctuniqueid (acctuniqueid), + KEY username (username), + KEY framedipaddress (framedipaddress), + KEY framedipv6address (framedipv6address), + KEY framedipv6prefix (framedipv6prefix), + KEY framedinterfaceid (framedinterfaceid), + KEY delegatedipv6prefix (delegatedipv6prefix), + KEY acctsessionid (acctsessionid), + KEY acctsessiontime (acctsessiontime), + KEY acctstarttime (acctstarttime), + KEY acctinterval (acctinterval), + KEY acctstoptime (acctstoptime), + KEY nasipaddress (nasipaddress), + KEY class (class) +) ENGINE = INNODB; + +# +# Table structure for table 'radcheck' +# + +CREATE TABLE IF NOT EXISTS radcheck ( + id int(11) unsigned NOT NULL auto_increment, + username varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '==', + value varchar(253) NOT NULL default '', + PRIMARY KEY (id), + KEY username (username(32)) +); + +# +# Table structure for table 'radgroupcheck' +# + +CREATE TABLE IF NOT EXISTS radgroupcheck ( + id int(11) unsigned NOT NULL auto_increment, + groupname varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '==', + value varchar(253) NOT NULL default '', + PRIMARY KEY (id), + KEY groupname (groupname(32)) +); + +# +# Table structure for table 'radgroupreply' +# + +CREATE TABLE IF NOT EXISTS radgroupreply ( + id int(11) unsigned NOT NULL auto_increment, + groupname varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '=', + value varchar(253) NOT NULL default '', + PRIMARY KEY (id), + KEY groupname (groupname(32)) +); + +# +# Table structure for table 'radreply' +# + +CREATE TABLE IF NOT EXISTS radreply ( + id int(11) unsigned NOT NULL auto_increment, + username varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '=', + value varchar(253) NOT NULL default '', + PRIMARY KEY (id), + KEY username (username(32)) +); + + +# +# Table structure for table 'radusergroup' +# + +CREATE TABLE IF NOT EXISTS radusergroup ( + id int(11) unsigned NOT NULL auto_increment, + username varchar(64) NOT NULL default '', + groupname varchar(64) NOT NULL default '', + priority int(11) NOT NULL default '1', + PRIMARY KEY (id), + KEY username (username(32)) +); + +# +# Table structure for table 'radpostauth' +# +# Note: MySQL versions since 5.6.4 support fractional precision timestamps +# which we use here. Replace the authdate definition with the following +# if your software is too old: +# +# authdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP +# +CREATE TABLE IF NOT EXISTS radpostauth ( + id int(11) NOT NULL auto_increment, + username varchar(64) NOT NULL default '', + pass varchar(64) NOT NULL default '', + reply varchar(32) NOT NULL default '', + authdate timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), + class varchar(64) default NULL, + PRIMARY KEY (id), + KEY username (username), + KEY class (class) +) ENGINE = INNODB; + +# +# Table structure for table 'nas' +# +CREATE TABLE IF NOT EXISTS nas ( + id int(10) NOT NULL auto_increment, + nasname varchar(128) NOT NULL, + shortname varchar(32), + type varchar(30) DEFAULT 'other', + ports int(5), + secret varchar(60) DEFAULT 'secret' NOT NULL, + server varchar(64), + community varchar(50), + description varchar(200) DEFAULT 'RADIUS Client', + PRIMARY KEY (id), + KEY nasname (nasname) +) ENGINE = INNODB; + +# +# Table structure for table 'nasreload' +# +CREATE TABLE IF NOT EXISTS nasreload ( + nasipaddress varchar(15) NOT NULL, + reloadtime datetime NOT NULL, + PRIMARY KEY (nasipaddress) +) ENGINE = INNODB; diff --git a/raddb/mods-config/sql/main/mysql/setup.sql b/raddb/mods-config/sql/main/mysql/setup.sql new file mode 100755 index 0000000..5ae98cc --- /dev/null +++ b/raddb/mods-config/sql/main/mysql/setup.sql @@ -0,0 +1,40 @@ +# -*- text -*- +## +## setup.sql -- MySQL commands for creating the RADIUS user. +## +## WARNING: You should change 'localhost' and 'radpass' +## to something else. Also update raddb/mods-available/sql +## with the new RADIUS password. +## +## $Id$ + +# +# Create default administrator for RADIUS +# +CREATE USER 'radius'@'localhost' IDENTIFIED BY 'radpass'; + +# +# The server can read the authorisation data +# +GRANT SELECT ON radius.radcheck TO 'radius'@'localhost'; +GRANT SELECT ON radius.radreply TO 'radius'@'localhost'; +GRANT SELECT ON radius.radusergroup TO 'radius'@'localhost'; +GRANT SELECT ON radius.radgroupcheck TO 'radius'@'localhost'; +GRANT SELECT ON radius.radgroupreply TO 'radius'@'localhost'; + +# +# The server can write accounting and post-auth data +# +GRANT SELECT, INSERT, UPDATE ON radius.radacct TO 'radius'@'localhost'; +GRANT SELECT, INSERT, UPDATE ON radius.radpostauth TO 'radius'@'localhost'; + +# +# The server can read the NAS data +# +GRANT SELECT ON radius.nas TO 'radius'@'localhost'; + +# +# In the case of the "lightweight accounting-on/off" strategy, the server also +# records NAS reload times +# +GRANT SELECT, INSERT, UPDATE ON radius.nasreload TO 'radius'@'localhost'; |