diff options
Diffstat (limited to 'raddb/mods-config/sql/main/sqlite')
5 files changed, 1137 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/main/sqlite/process-radacct-close-after-reload.pl b/raddb/mods-config/sql/main/sqlite/process-radacct-close-after-reload.pl new file mode 100755 index 0000000..c43da06 --- /dev/null +++ b/raddb/mods-config/sql/main/sqlite/process-radacct-close-after-reload.pl @@ -0,0 +1,119 @@ +#!/usr/bin/perl -Tw + +# +# main/sqlite/process-radacct-close-after_reload.pl -- Script for +# processing radacct entries to close sessions interrupted by a NAS reload +# +# Requires the DBD::SQLite module: perl-DBD-SQLite (RedHat); libdbd-sqlite3-perl (Debian) +# +# $Id$ +# +# 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. +# +# This script 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 a +# single long-lived lock on the table. +# +# It can be invoked as follows: +# +# ./process-radacct-close-after-reload.pl <sqlite_db_file> +# +# Note: This script walks radacct in strides of v_batch_size. It will +# typically skip closed and ongoing sessions at a rate significantly faster +# than 10,000 rows per second and process batched updates faster than 5000 +# orphaned sessions per second. If this isn't fast enough then you should +# really consider using a server-based database for accounting purposes. +# + +use strict; +use DBI; + +# +# Fine for most purposes +# +my $batch_size = 2500; + +if ($#ARGV != 0) { + print "Usage: process-radacct-close-after_reload.pl SQLITE_DB_FILE\n\n"; + exit 1; +} +die "The SQLite database must exist: $ARGV[0]" unless -r $ARGV[0]; + + +my $dbh = DBI->connect("DBI:SQLite:dbname=$ARGV[0]", '', '', { RaiseError => 1 }) or die $DBI::errstr; + +# +# There is no UPDATE ... JOIN/FROM in SQLite, so we have to resort to this +# construction # which does not provide an accurate rows updated count... +# +my $sth_upd = $dbh->prepare(<<'EOF'); + UPDATE radacct + SET + acctstoptime = ( + SELECT COALESCE(acctstoptime, CASE WHEN radacct.acctstarttime < reloadtime THEN reloadtime END) + FROM nasreload WHERE nasipaddress = radacct.nasipaddress + ), + acctsessiontime = ( + SELECT COALESCE(acctsessiontime, + CASE WHEN radacct.acctstoptime IS NULL AND radacct.acctstarttime < reloadtime THEN + CAST((julianday(reloadtime) - julianday(radacct.acctstarttime)) * 86400 AS integer) + END) + FROM nasreload WHERE nasipaddress = radacct.nasipaddress + ), + acctterminatecause = ( + SELECT + CASE WHEN radacct.acctstoptime IS NULL AND radacct.acctstarttime < reloadtime THEN + 'NAS reboot' + ELSE + acctterminatecause + END + FROM nasreload WHERE nasipaddress = radacct.nasipaddress + ) + WHERE + radacctid BETWEEN ? AND ? + AND acctstoptime IS NULL +EOF + +my $sth = $dbh->prepare('SELECT MIN(radacctid), MAX(radacctid) FROM radacct WHERE acctstoptime IS NULL'); +$sth->execute() or die $DBI::errstr; +(my $a, my $m) = $sth->fetchrow_array(); +$sth->finish; + +my $sth_nxt = $dbh->prepare('SELECT radacctid FROM radacct WHERE radacctid > ? ORDER BY radacctid LIMIT ?,1'); + + +my $last = 0; +my $last_report = 0; + +unless ($last) { + + $sth_nxt->execute($a, $batch_size) or die $DBI::errstr; + (my $z) = $sth_nxt->fetchrow_array(); + + unless ($z) { + $z = $m; + $last = 1; + } + + my $rc = $sth_upd->execute($a, $z) or die $DBI::errstr; + + $a = $z + 1; + + # + # Periodically report how far we've got + # + my $now = time(); + if ($last_report != $now || $last) { + print "RadAcctID: $z\n"; + $last_report = $now; + } + +} + +$sth_upd->finish; +$sth_nxt->finish; + +$dbh->disconnect; diff --git a/raddb/mods-config/sql/main/sqlite/process-radacct-new-data-usage-period.sh b/raddb/mods-config/sql/main/sqlite/process-radacct-new-data-usage-period.sh new file mode 100755 index 0000000..0deb391 --- /dev/null +++ b/raddb/mods-config/sql/main/sqlite/process-radacct-new-data-usage-period.sh @@ -0,0 +1,124 @@ +#!/bin/sh +# +# main/sqlite/process-radacct-new-data-usage-period.sh -- Script for +# processing radacct entries to extract daily usage +# +# $Id$ + +# +# See process-radacct-schema.sql for details. +# + +if [ "$#" -ne 1 ]; then + echo "Usage: process-radacct-new-data-usage-period.sh SQLITE_DB_FILE" 2>&1 + exit 1 +fi + +if [ ! -r "$1" ]; then + echo "The SQLite database must exist: $1" 1>&2 + exit 1 +fi + +cat <<EOF | sqlite3 "$1" + + -- + -- SQLite doesn't have a concept of session variables so we fake it. + -- + DROP TABLE IF EXISTS vars; + CREATE TEMPORARY TABLE vars ( + key text, + value text, + PRIMARY KEY (key) + ); + + INSERT INTO vars SELECT 'v_start', COALESCE(DATETIME(MAX(period_end), '+1 seconds'), DATETIME(0, 'unixepoch')) FROM data_usage_by_period; + INSERT INTO vars SELECT 'v_end', CURRENT_TIMESTAMP; + + + -- + -- Make of copy of the sessions that were active during this period to + -- avoid having to execute a potentially long transaction that might hold a + -- global database lock. + -- + DROP TABLE IF EXISTS radacct_sessions; + CREATE TEMPORARY TABLE radacct_sessions ( + username text, + acctstarttime datetime, + acctstoptime datetime, + acctinputoctets bigint, + acctoutputoctets bigint + ); + CREATE INDEX temp.idx_radacct_sessions_username ON radacct_sessions(username); + CREATE INDEX temp.idx_radacct_sessions_acctstoptime ON radacct_sessions(acctstoptime); + + INSERT INTO radacct_sessions + SELECT + username, + acctstarttime, + acctstoptime, + acctinputoctets, + acctoutputoctets + FROM + radacct + WHERE + acctstoptime > (SELECT value FROM vars WHERE key='v_start'); + + INSERT INTO radacct_sessions + SELECT + username, + acctstarttime, + acctstoptime, + acctinputoctets, + acctoutputoctets + FROM + radacct + WHERE + acctstoptime IS NULL; + + + -- + -- 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 + username, + (SELECT value FROM vars WHERE key='v_start'), + (SELECT value FROM vars WHERE key='v_end'), + SUM(acctinputoctets) AS acctinputoctets, + SUM(acctoutputoctets) AS acctoutputoctets + FROM + radacct_sessions + GROUP BY + username + ON CONFLICT(username,period_start) DO UPDATE + SET + acctinputoctets = data_usage_by_period.acctinputoctets + EXCLUDED.acctinputoctets, + acctoutputoctets = data_usage_by_period.acctoutputoctets + EXCLUDED.acctoutputoctets, + period_end = (SELECT value FROM vars WHERE key='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 + username, + (SELECT DATETIME(value, '+1 seconds') FROM vars WHERE key='v_end'), + NULL, + 0 - SUM(acctinputoctets), + 0 - SUM(acctoutputoctets) + FROM + radacct_sessions + WHERE + acctstoptime IS NULL + GROUP BY + username; + + DROP TABLE vars; + DROP TABLE radacct_sessions; + +EOF diff --git a/raddb/mods-config/sql/main/sqlite/process-radacct-schema.sql b/raddb/mods-config/sql/main/sqlite/process-radacct-schema.sql new file mode 100644 index 0000000..b429d4c --- /dev/null +++ b/raddb/mods-config/sql/main/sqlite/process-radacct-schema.sql @@ -0,0 +1,95 @@ +# -*- text -*- +# +# main/sqlite/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 +-- process-radacct-new-data-usage-period.sh script. +-- +-- This table can be queried in various ways to produce reports of aggregate +-- data use over time. For example, if the refresh script is invoked once 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 by queriing this table with: +-- +-- SELECT +-- STRFTIME('%Y-%m',CURRENT_TIMESTAMP) AS month, +-- SUM(acctinputoctets)*1.0/1000/1000/1000 AS gb_in, +-- SUM(acctoutputoctets)*1.0/1000/1000/1000 AS gb_out +-- FROM +-- data_usage_by_period +-- WHERE +-- username='bob' AND +-- period_end IS NOT NULL +-- GROUP BY +-- month; +-- +-- 2019-07|5.782279231|50.545664824 +-- 2019-08|4.230543344|48.523096424 +-- 2019-09|4.847360599|48.631835488 +-- 2019-10|6.456763254|51.686231937 +-- 2019-11|6.362537735|52.385710572 +-- 2019-12|4.301524442|50.762240277 +-- 2020-01|5.436280545|49.067775286 +-- +CREATE TABLE data_usage_by_period ( + username text, + period_start datetime, + period_end datetime, + acctinputoctets bigint, + acctoutputoctets bigint, + 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); + + +-- ------------------------------------------------------ +-- - "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 +-- process-radacct-close-after_reload.pl script.) +-- +-- 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, + CASE WHEN a.AcctStartTime < n.ReloadTime THEN n.ReloadTime END + ) AS AcctStopTime_With_Reloads, + COALESCE(a.AcctSessionTime, + CASE WHEN a.AcctStopTime IS NULL AND a.AcctStartTime < n.ReloadTime THEN + CAST((julianday(n.ReloadTime) - julianday(a.AcctStartTime)) * 86400 AS integer) + END + ) AS AcctSessionTime_With_Reloads +FROM radacct a +LEFT OUTER JOIN nasreload n USING (nasipaddress); diff --git a/raddb/mods-config/sql/main/sqlite/queries.conf b/raddb/mods-config/sql/main/sqlite/queries.conf new file mode 100644 index 0000000..35016f4 --- /dev/null +++ b/raddb/mods-config/sql/main/sqlite/queries.conf @@ -0,0 +1,635 @@ +# -*- 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})" +} diff --git a/raddb/mods-config/sql/main/sqlite/schema.sql b/raddb/mods-config/sql/main/sqlite/schema.sql new file mode 100644 index 0000000..4625a58 --- /dev/null +++ b/raddb/mods-config/sql/main/sqlite/schema.sql @@ -0,0 +1,164 @@ +----------------------------------------------------------------------------- +-- $Id$ -- +-- -- +-- schema.sql rlm_sql - FreeRADIUS SQLite Module -- +-- -- +-- Database schema for SQLite rlm_sql module -- +-- -- +----------------------------------------------------------------------------- + +-- +-- Table structure for table 'radacct' +-- +CREATE TABLE IF NOT EXISTS radacct ( + radacctid INTEGER PRIMARY KEY AUTOINCREMENT, + 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) 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 +); + +-- +-- You might not need all of these indexes. It should be safe to +-- delete indexes you do not use. For example, if you're not using +-- IPv6, you can delete the indexes on IPv6 attributes. +-- +-- You MUST however leave the indexes needed by the server, which +-- include username, acctstoptime, nasipaddress, acctstarttime, and +-- acctuniqueid. +-- +CREATE UNIQUE INDEX acctuniqueid ON radacct(acctuniqueid); +CREATE INDEX username ON radacct(username); +CREATE INDEX framedipaddress ON radacct (framedipaddress); +CREATE INDEX framedipv6address ON radacct (framedipv6address); +CREATE INDEX framedipv6prefix ON radacct (framedipv6prefix); +CREATE INDEX framedinterfaceid ON radacct (framedinterfaceid); +CREATE INDEX delegatedipv6prefix ON radacct (delegatedipv6prefix); +CREATE INDEX acctsessionid ON radacct(acctsessionid); +CREATE INDEX acctsessiontime ON radacct(acctsessiontime); +CREATE INDEX acctstarttime ON radacct(acctstarttime); +CREATE INDEX acctinterval ON radacct(acctinterval); +CREATE INDEX acctstoptime ON radacct(acctstoptime); +CREATE INDEX nasipaddress ON radacct(nasipaddress); +CREATE INDEX class ON radacct(class); + +-- +-- Table structure for table 'radcheck' +-- +CREATE TABLE IF NOT EXISTS radcheck ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + username varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '==', + value varchar(253) NOT NULL default '' +); +CREATE INDEX check_username ON radcheck(username); + +-- +-- Table structure for table 'radgroupcheck' +-- +CREATE TABLE IF NOT EXISTS radgroupcheck ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + groupname varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '==', + value varchar(253) NOT NULL default '' +); +CREATE INDEX check_groupname ON radgroupcheck(groupname); + +-- +-- Table structure for table 'radgroupreply' +-- +CREATE TABLE IF NOT EXISTS radgroupreply ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + groupname varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '=', + value varchar(253) NOT NULL default '' +); +CREATE INDEX reply_groupname ON radgroupreply(groupname); + +-- +-- Table structure for table 'radreply' +-- +CREATE TABLE IF NOT EXISTS radreply ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + username varchar(64) NOT NULL default '', + attribute varchar(64) NOT NULL default '', + op char(2) NOT NULL DEFAULT '=', + value varchar(253) NOT NULL default '' +); +CREATE INDEX reply_username ON radreply(username); + +-- +-- Table structure for table 'radusergroup' +-- +CREATE TABLE IF NOT EXISTS radusergroup ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + username varchar(64) NOT NULL default '', + groupname varchar(64) NOT NULL default '', + priority int(11) NOT NULL default '1' +); +CREATE INDEX usergroup_username ON radusergroup(username); + +-- +-- Table structure for table 'radpostauth' +-- +CREATE TABLE IF NOT EXISTS radpostauth ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + username varchar(64) NOT NULL default '', + pass varchar(64) NOT NULL default '', + reply varchar(32) NOT NULL default '', + authdate timestamp NOT NULL, + class varchar(64) default NULL +); +CREATE INDEX radpostauth_username ON radpostauth(username); +CREATE INDEX radpostauth_class ON radpostauth(class); + +-- +-- Table structure for table 'nas' +-- +CREATE TABLE IF NOT EXISTS nas ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + 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' +); +CREATE INDEX nasname ON nas(nasname); + +-- +-- Table structure for table 'nasreload' +-- +CREATE TABLE IF NOT EXISTS nasreload ( + nasipaddress varchar(15) PRIMARY KEY, + reloadtime datetime NOT NULL +); |