summaryrefslogtreecommitdiffstats
path: root/raddb/mods-config/sql/main/mysql
diff options
context:
space:
mode:
Diffstat (limited to 'raddb/mods-config/sql/main/mysql')
-rw-r--r--raddb/mods-config/sql/main/mysql/extras/wimax/queries.conf40
-rw-r--r--raddb/mods-config/sql/main/mysql/extras/wimax/schema.sql16
-rw-r--r--raddb/mods-config/sql/main/mysql/process-radacct.sql279
-rw-r--r--raddb/mods-config/sql/main/mysql/queries.conf694
-rw-r--r--raddb/mods-config/sql/main/mysql/schema.sql179
-rwxr-xr-xraddb/mods-config/sql/main/mysql/setup.sql40
6 files changed, 1248 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..04c1c5b
--- /dev/null
+++ b/raddb/mods-config/sql/main/mysql/process-radacct.sql
@@ -0,0 +1,279 @@
+# -*- 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
+ radacct
+ WHERE
+ acctstoptime > v_start OR
+ acctstoptime IS NULL
+ 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..4b5f6e2
--- /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}, \
+ NULL, \
+ 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';