Changes to automatically provision,build and run Radius containers for Auth tests.
Changes to cord test server to handle radius server restart requests.
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/mssql/queries.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mssql/queries.conf
new file mode 100644
index 0000000..9223d01
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mssql/queries.conf
@@ -0,0 +1,270 @@
+# -*- text -*-
+#
+# main/mssql/queries.conf -- MSSQL configuration for default schema (schema.sql)
+#
+# $Id: 6fcea6edb5998f9f6c302f6246a88cdddf83dbaa $
+
+# 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 "none" as the user name.
+#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
+#
+sql_user_name = "%{User-Name}"
+
+#######################################################################
+# 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
+#######################################################################
+# Query for case sensitive usernames was removed. Please contact with me,
+# if you know analog of STRCMP functions for MS SQL.
+
+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"
+
+authorize_group_check_query = "\
+ SELECT \
+ ${groupcheck_table}.id,${groupcheck_table}.GroupName, \
+ ${groupcheck_table}.Attribute,${groupcheck_table}.Value, \
+ ${groupcheck_table}.op \
+ FROM ${groupcheck_table},${usergroup_table} \
+ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
+ AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
+ ORDER BY ${groupcheck_table}.id"
+
+authorize_group_reply_query = "\
+ SELECT \
+ ${groupreply_table}.id, ${groupreply_table}.GroupName, \
+ ${groupreply_table}.Attribute,${groupreply_table}.Value, \
+ ${groupreply_table}.op \
+ FROM ${groupreply_table},${usergroup_table} \
+ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
+ AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
+ ORDER BY ${groupreply_table}.id"
+
+group_membership_query = "\
+ SELECT groupname \
+ FROM ${usergroup_table} \
+ WHERE username = '%{SQL-User-Name}' \
+ ORDER BY priority"
+
+#######################################################################
+# 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}.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
+
+ type {
+ accounting-on {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStopTime='%S', \
+ AcctSessionTime=unix_timestamp('%S') - \
+ unix_timestamp(AcctStartTime), \
+ AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \
+ AcctStopDelay = %{%{Acct-Delay-Time}:-0} \
+ WHERE AcctStopTime = 0 \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStartTime <= '%S'"
+ }
+
+ accounting-off {
+ query = "${..accounting-on.query}"
+ }
+
+ start {
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (AcctSessionId, AcctUniqueId, UserName, \
+ Realm, NASIPAddress, NASPort, \
+ NASPortType, AcctStartTime, AcctSessionTime, \
+ AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, \
+ AcctInputOctets, AcctOutputOctets, CalledStationId, \
+ CallingStationId, AcctTerminateCause, ServiceType, \
+ FramedProtocol, FramedIPAddress, AcctStartDelay, \
+ AcctStopDelay, XAscendSessionSvrKey) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', \
+ '%{NAS-IP-Address}', \
+ '%{NAS-Port-Id}', \
+ '%{NAS-Port-Type}', \
+ '%S', \
+ '0', \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ '', \
+ '0', \
+ '0', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}', \
+ '%{Acct-Delay-Time}', \
+ '0', \
+ '%{X-Ascend-Session-Svr-Key}')"
+
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStartTime = '%S', \
+ AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStopTime = 0"
+ }
+
+ interim-update {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ FramedIPAddress = '%{Framed-IP-Address}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress= '%{NAS-IP-Address}' \
+ AND AcctStopTime = 0"
+
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (AcctSessionId, AcctUniqueId, UserName, \
+ Realm, NASIPAddress, NASPort, \
+ NASPortType, AcctSessionTime, AcctAuthentic, \
+ ConnectInfo_start, AcctInputOctets, AcctOutputOctets, \
+ CalledStationId, CallingStationId, ServiceType, \
+ FramedProtocol, FramedIPAddress, AcctStartDelay, \
+ XAscendSessionSvrKey) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', \
+ '%{NAS-IP-Address}', \
+ '%{NAS-Port-Id}', \
+ '%{NAS-Port-Type}', \
+ '%{Acct-Session-Time}', \
+ '%{Acct-Authentic}', \
+ '', \
+ '%{Acct-Input-Octets}', \
+ '%{Acct-Output-Octets}', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}', \
+ '0', \
+ '%{X-Ascend-Session-Svr-Key}')"
+ }
+
+ stop {
+ query = "\
+ UPDATE ${....acct_table2} \
+ SET \
+ AcctStopTime = '%S', \
+ AcctSessionTime = '%{Acct-Session-Time}', \
+ AcctInputOctets = '%{Acct-Input-Octets}', \
+ AcctOutputOctets = '%{Acct-Output-Octets}', \
+ AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+ AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStopTime = 0"
+
+ query = "\
+ INSERT into ${....acct_table2} \
+ (AcctSessionId, AcctUniqueId, UserName, \
+ Realm, NASIPAddress, NASPort, \
+ NASPortType, AcctStopTime, AcctSessionTime, \
+ AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, \
+ AcctInputOctets, AcctOutputOctets, CalledStationId, \
+ CallingStationId, AcctTerminateCause, ServiceType, \
+ FramedProtocol, FramedIPAddress, AcctStartDelay, \
+ AcctStopDelay) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', \
+ '%{NAS-IP-Address}', \
+ '%{NAS-Port-Id}', \
+ '%{NAS-Port-Type}', \
+ '%S', \
+ '%{Acct-Session-Time}', \
+ '%{Acct-Authentic}', \
+ '', \
+ '%{Connect-Info}', \
+ '%{Acct-Input-Octets}', \
+ '%{Acct-Output-Octets}', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Acct-Terminate-Cause}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}', \
+ '0', \
+ '%{%{Acct-Delay-Time}:-0}')"
+ }
+ }
+}
+
+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
+}
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/mssql/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mssql/schema.sql
new file mode 100644
index 0000000..4062200
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mssql/schema.sql
@@ -0,0 +1,236 @@
+/***************************************************************************
+ * $Id: f89204918fc5951cb1920d5474563656ec9dee98 $ *
+ * *
+ * db_mssql.sql *
+ * *
+ * Database schema for MSSQL server *
+ * *
+ * To load: *
+ * isql -S db_ip_addr -d db_name -U db_login -P db_passwd -i db_mssql.sql *
+ * *
+ * Based on: db_mysql.sql (Mike Machado <mike@innercite.com>) *
+ * *
+ * Dmitri Ageev <d_ageev@ortcc.ru> *
+ ***************************************************************************/
+
+/****** Object: Table [radacct] Script Date: 26.03.02 16:55:17 ******/
+CREATE TABLE [radacct] (
+ [RadAcctId] [numeric](21, 0) IDENTITY (1, 1) NOT NULL ,
+ [AcctSessionId] [varchar] (64) DEFAULT ('') FOR [AcctSessionId],
+ [AcctUniqueId] [varchar] (32) DEFAULT ('') FOR [AcctUniqueId],
+ [UserName] [varchar] (64) DEFAULT ('') FOR [UserName],
+ [GroupName] [varchar] (64) DEFAULT ('') FOR [GroupName],
+ [Realm] [varchar] (64) DEFAULT ('') FOR [Realm],
+ [NASIPAddress] [varchar] (15) DEFAULT ('') FOR [NASIPAddress],
+ [NASPortId] [varchar] (15) NULL ,
+ [NASPortType] [varchar] (32) NULL ,
+ [AcctStartTime] [datetime] NOT NULL ,
+ [AcctStopTime] [datetime] NOT NULL ,
+ [AcctSessionTime] [bigint] NULL ,
+ [AcctAuthentic] [varchar] (32) NULL ,
+ [ConnectInfo_start] [varchar] (32) DEFAULT (null) FOR [ConnectInfo_start],
+ [ConnectInfo_stop] [varchar] (32) DEFAULT (null) FOR [ConnectInfo_stop],
+ [AcctInputOctets] [bigint] NULL ,
+ [AcctOutputOctets] [bigint] NULL ,
+ [CalledStationId] [varchar] (30) DEFAULT ('') FOR [CalledStationId],
+ [CallingStationId] [varchar] (30) DEFAULT ('') FOR [CallingStationId],
+ [AcctTerminateCause] [varchar] (32) DEFAULT ('') FOR [AcctTerminateCause],
+ [ServiceType] [varchar] (32) NULL ,
+ [FramedProtocol] [varchar] (32) NULL ,
+ [FramedIPAddress] [varchar] (15) DEFAULT ('') FOR [FramedIPAddress],
+ [XAscendSessionSvrKey] [varchar] (10) DEFAULT (null) FOR [XAscendSessionSvrKey],
+ [AcctStartDelay] [int] NULL ,
+ [AcctStopDelay] [int] NULL
+) ON [PRIMARY]
+GO
+
+/****** Object: Table [radcheck] Script Date: 26.03.02 16:55:17 ******/
+CREATE TABLE [radcheck] (
+ [id] [int] IDENTITY (1, 1) NOT NULL ,
+ [UserName] [varchar] (64) NOT NULL ,
+ [Attribute] [varchar] (32) NOT NULL ,
+ [Value] [varchar] (253) NOT NULL ,
+ [op] [char] (2) NULL
+) ON [PRIMARY]
+GO
+
+/****** Object: Table [radgroupcheck] Script Date: 26.03.02 16:55:17 ******/
+CREATE TABLE [radgroupcheck] (
+ [id] [int] IDENTITY (1, 1) NOT NULL ,
+ [GroupName] [varchar] (64) NOT NULL ,
+ [Attribute] [varchar] (32) NOT NULL ,
+ [Value] [varchar] (253) NOT NULL ,
+ [op] [char] (2) NULL
+) ON [PRIMARY]
+GO
+
+/****** Object: Table [radgroupreply] Script Date: 26.03.02 16:55:17 ******/
+CREATE TABLE [radgroupreply] (
+ [id] [int] IDENTITY (1, 1) NOT NULL ,
+ [GroupName] [varchar] (64) NOT NULL ,
+ [Attribute] [varchar] (32) NOT NULL ,
+ [Value] [varchar] (253) NOT NULL ,
+ [op] [char] (2) NULL ,
+ [prio] [int] NOT NULL
+) ON [PRIMARY]
+GO
+
+/****** Object: Table [radreply] Script Date: 26.03.02 16:55:18 ******/
+CREATE TABLE [radreply] (
+ [id] [int] IDENTITY (1, 1) NOT NULL ,
+ [UserName] [varchar] (64) NOT NULL ,
+ [Attribute] [varchar] (32) NOT NULL ,
+ [Value] [varchar] (253) NOT NULL ,
+ [op] [char] (2) NULL
+) ON [PRIMARY]
+GO
+
+/****** Object: Table [radusergroup] Script Date: 26.03.02 16:55:18 ******/
+CREATE TABLE [radusergroup] (
+ [id] [int] IDENTITY (1, 1) NOT NULL ,
+ [UserName] [varchar] (64) NOT NULL ,
+ [GroupName] [varchar] (64) NULL
+) ON [PRIMARY]
+GO
+
+/****** Object: Table [radusergroup] Script Date: 16.04.08 19:44:11 ******/
+CREATE TABLE [radpostauth] (
+ [id] [int] IDENTITY (1, 1) NOT NULL ,
+ [userName] [varchar] (64) NOT NULL ,
+ [pass] [varchar] (64) NOT NULL ,
+ [reply] [varchar] (32) NOT NULL ,
+ [authdate] [datetime] NOT NULL
+)
+GO
+
+ALTER TABLE [radacct] WITH NOCHECK ADD
+ CONSTRAINT [DF_radacct_GroupName] DEFAULT ('') FOR [GroupName],
+ CONSTRAINT [DF_radacct_AcctSessionId] DEFAULT ('') FOR [AcctSessionId],
+ CONSTRAINT [DF_radacct_AcctUniqueId] DEFAULT ('') FOR [AcctUniqueId],
+ CONSTRAINT [DF_radacct_UserName] DEFAULT ('') FOR [UserName],
+ CONSTRAINT [DF_radacct_Realm] DEFAULT ('') FOR [Realm],
+ CONSTRAINT [DF_radacct_NASIPAddress] DEFAULT ('') FOR [NASIPAddress],
+ CONSTRAINT [DF_radacct_NASPortId] DEFAULT (null) FOR [NASPortId],
+ CONSTRAINT [DF_radacct_NASPortType] DEFAULT (null) FOR [NASPortType],
+ CONSTRAINT [DF_radacct_AcctStartTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctStartTime],
+ CONSTRAINT [DF_radacct_AcctStopTime] DEFAULT ('1900-01-01 00:00:00') FOR [AcctStopTime],
+ CONSTRAINT [DF_radacct_AcctSessionTime] DEFAULT (null) FOR [AcctSessionTime],
+ CONSTRAINT [DF_radacct_AcctAuthentic] DEFAULT (null) FOR [AcctAuthentic],
+ CONSTRAINT [DF_radacct_ConnectInfo_start] DEFAULT (null) FOR [ConnectInfo_start],
+ CONSTRAINT [DF_radacct_ConnectInfo_stop] DEFAULT (null) FOR [ConnectInfo_stop],
+ CONSTRAINT [DF_radacct_AcctInputOctets] DEFAULT (null) FOR [AcctInputOctets],
+ CONSTRAINT [DF_radacct_AcctOutputOctets] DEFAULT (null) FOR [AcctOutputOctets],
+ CONSTRAINT [DF_radacct_CalledStationId] DEFAULT ('') FOR [CalledStationId],
+ CONSTRAINT [DF_radacct_CallingStationId] DEFAULT ('') FOR [CallingStationId],
+ CONSTRAINT [DF_radacct_AcctTerminateCause] DEFAULT ('') FOR [AcctTerminateCause],
+ CONSTRAINT [DF_radacct_ServiceType] DEFAULT (null) FOR [ServiceType],
+ CONSTRAINT [DF_radacct_FramedProtocol] DEFAULT (null) FOR [FramedProtocol],
+ CONSTRAINT [DF_radacct_FramedIPAddress] DEFAULT ('') FOR [FramedIPAddress],
+ CONSTRAINT [DF_radacct_AcctStartDelay] DEFAULT (null) FOR [AcctStartDelay],
+ CONSTRAINT [DF_radacct_AcctStopDelay] DEFAULT (null) FOR [AcctStopDelay],
+ CONSTRAINT [PK_radacct] PRIMARY KEY NONCLUSTERED
+ (
+ [RadAcctId]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [radcheck] WITH NOCHECK ADD
+ CONSTRAINT [DF_radcheck_UserName] DEFAULT ('') FOR [UserName],
+ CONSTRAINT [DF_radcheck_Attribute] DEFAULT ('') FOR [Attribute],
+ CONSTRAINT [DF_radcheck_Value] DEFAULT ('') FOR [Value],
+ CONSTRAINT [DF_radcheck_op] DEFAULT (null) FOR [op],
+ CONSTRAINT [PK_radcheck] PRIMARY KEY NONCLUSTERED
+ (
+ [id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [radgroupcheck] WITH NOCHECK ADD
+ CONSTRAINT [DF_radgroupcheck_GroupName] DEFAULT ('') FOR [GroupName],
+ CONSTRAINT [DF_radgroupcheck_Attribute] DEFAULT ('') FOR [Attribute],
+ CONSTRAINT [DF_radgroupcheck_Value] DEFAULT ('') FOR [Value],
+ CONSTRAINT [DF_radgroupcheck_op] DEFAULT (null) FOR [op],
+ CONSTRAINT [PK_radgroupcheck] PRIMARY KEY NONCLUSTERED
+ (
+ [id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [radgroupreply] WITH NOCHECK ADD
+ CONSTRAINT [DF_radgroupreply_GroupName] DEFAULT ('') FOR [GroupName],
+ CONSTRAINT [DF_radgroupreply_Attribute] DEFAULT ('') FOR [Attribute],
+ CONSTRAINT [DF_radgroupreply_Value] DEFAULT ('') FOR [Value],
+ CONSTRAINT [DF_radgroupreply_op] DEFAULT (null) FOR [op],
+ CONSTRAINT [DF_radgroupreply_prio] DEFAULT (0) FOR [prio],
+ CONSTRAINT [PK_radgroupreply] PRIMARY KEY NONCLUSTERED
+ (
+ [id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [radreply] WITH NOCHECK ADD
+ CONSTRAINT [DF_radreply_UserName] DEFAULT ('') FOR [UserName],
+ CONSTRAINT [DF_radreply_Attribute] DEFAULT ('') FOR [Attribute],
+ CONSTRAINT [DF_radreply_Value] DEFAULT ('') FOR [Value],
+ CONSTRAINT [DF_radreply_op] DEFAULT (null) FOR [op],
+ CONSTRAINT [PK_radreply] PRIMARY KEY NONCLUSTERED
+ (
+ [id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [radusergroup] WITH NOCHECK ADD
+ CONSTRAINT [DF_radusergroup_UserName] DEFAULT ('') FOR [UserName],
+ CONSTRAINT [DF_radusergroup_GroupName] DEFAULT ('') FOR [GroupName],
+ CONSTRAINT [PK_radusergroup] PRIMARY KEY NONCLUSTERED
+ (
+ [id]
+ ) ON [PRIMARY]
+GO
+
+ALTER TABLE [radpostauth] WITH NOCHECK ADD
+ CONSTRAINT [DF_radpostauth_userName] DEFAULT ('') FOR [userName],
+ CONSTRAINT [DF_radpostauth_pass] DEFAULT ('') FOR [pass],
+ CONSTRAINT [DF_radpostauth_reply] DEFAULT ('') FOR [reply],
+ CONSTRAINT [DF_radpostauth_authdate] DEFAULT (getdate()) FOR [authdate],
+ CONSTRAINT [PK_radpostauth] PRIMARY KEY NONCLUSTERED
+ (
+ [id]
+ ) ON [PRIMARY]
+GO
+
+ CREATE INDEX [UserName] ON [radacct]([UserName]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [FramedIPAddress] ON [radacct]([FramedIPAddress]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [AcctSessionId] ON [radacct]([AcctSessionId]) ON [PRIMARY]
+GO
+
+ CREATE UNIQUE INDEX [AcctUniqueId] ON [radacct]([AcctUniqueId]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [AcctStartTime] ON [radacct]([AcctStartTime]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [AcctStopTime] ON [radacct]([AcctStopTime]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [NASIPAddress] ON [radacct]([NASIPAddress]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [UserName] ON [radcheck]([UserName]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [GroupName] ON [radgroupcheck]([GroupName]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [GroupName] ON [radgroupreply]([GroupName]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [UserName] ON [radreply]([UserName]) ON [PRIMARY]
+GO
+
+ CREATE INDEX [UserName] ON [radusergroup]([UserName]) ON [PRIMARY]
+GO
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/extras/wimax/queries.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/extras/wimax/queries.conf
new file mode 100644
index 0000000..4087cb5
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/extras/wimax/queries.conf
@@ -0,0 +1,40 @@
+# -*- text -*-
+##
+## wimax.conf -- MySQL configuration for WiMAX keying
+##
+## $Id: 26942305017c59d4589d0645cfc79405b98b4c6a $
+
+# 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/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/extras/wimax/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/extras/wimax/schema.sql
new file mode 100644
index 0000000..e32224a
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/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/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/queries.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/queries.conf
new file mode 100644
index 0000000..60c0f27
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/queries.conf
@@ -0,0 +1,414 @@
+# -*- text -*-
+#
+# main/mysql/queries.conf-- MySQL configuration for default schema (schema.sql)
+#
+# $Id: 0b3c210d6c0b04350d1a48738764b47f25f51bc4 $
+
+# Safe characters list for sql queries. Everything else is replaced
+# with their mime-encoded equivalents.
+# The default list should be ok
+#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}"
+
+#######################################################################
+# 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 = '%{Sql-Group}' \
+ ORDER BY id"
+
+authorize_group_reply_query = "\
+ SELECT id, groupname, attribute, \
+ value, op \
+ FROM ${groupreply_table} \
+ WHERE groupname = '%{Sql-Group}' \
+ ORDER BY id"
+
+#######################################################################
+# Simultaneous Use Checking Queries
+#######################################################################
+# simul_count_query - query for the number of current connections
+# - If this is not defined, no simultaneouls 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.
+#######################################################################
+
+#
+# Uncomment simul_count_query to enable simultaneous use checking
+#
+#simul_count_query = "\
+# SELECT COUNT(*) \
+# FROM ${acct_table1} \
+# WHERE username = '%{SQL-User-Name}' \
+# AND acctstoptime IS NULL"
+
+simul_verify_query = "\
+ SELECT \
+ radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
+ callingstationid, framedprotocol \
+ FROM ${acct_table1} \
+ WHERE username = '%{SQL-User-Name}' \
+ AND acctstoptime 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}.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"
+
+ type {
+ accounting-on {
+ #
+ # Bulk terminate all sessions associated with a given NAS
+ #
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ acctstoptime = FROM_UNIXTIME(\
+ %{integer:Event-Timestamp}), \
+ acctsessiontime = '%{integer:Event-Timestamp}' \
+ - UNIX_TIMESTAMP(acctstarttime), \
+ acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
+ WHERE acctstoptime IS NULL \
+ AND nasipaddress = '%{NAS-IP-Address}' \
+ AND acctstarttime <= FROM_UNIXTIME(\
+ %{integer: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}', \
+ '%{NAS-Port-Type}', \
+ FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ NULL, \
+ '0', \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ '', \
+ '0', \
+ '0', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}')"
+
+ #
+ # Key constraints prevented us from inserting a new session,
+ # use the alternate query to update an existing session.
+ #
+ query = "\
+ UPDATE ${....acct_table1} SET \
+ acctstarttime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ acctupdatetime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ connectinfo_start = '%{Connect-Info}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
+ }
+
+ 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 = FROM_UNIXTIME(\
+ %{integer:Event-Timestamp}), \
+ acctinterval = %{integer:Event-Timestamp} - \
+ UNIX_TIMESTAMP(@acctupdatetime_old), \
+ framedipaddress = '%{Framed-IP-Address}', \
+ acctsessiontime = '%{Acct-Session-Time}', \
+ acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
+ << 32 | '%{%{Acct-Input-Octets}:-0}', \
+ acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
+ << 32 | '%{%{Acct-Output-Octets}:-0}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
+
+ #
+ # 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}', \
+ '%{NAS-Port-Type}', \
+ FROM_UNIXTIME(%{integer:Event-Timestamp} - \
+ %{%{Acct-Session-Time}:-0}), \
+ FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ NULL, \
+ '%{Acct-Session-Time}', \
+ '%{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}')"
+ }
+
+ stop {
+ #
+ # Session has terminated, update the stop time and statistics.
+ #
+ query = "\
+ UPDATE ${....acct_table2} SET \
+ acctstoptime = FROM_UNIXTIME(\
+ %{integer: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 acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
+
+ #
+ # 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}', \
+ '%{NAS-Port-Type}', \
+ FROM_UNIXTIME(%{integer:Event-Timestamp} - \
+ %{%{Acct-Session-Time}:-0}), \
+ FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ FROM_UNIXTIME(%{integer:Event-Timestamp}), \
+ '%{Acct-Session-Time}', \
+ '%{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}')"
+ }
+ }
+}
+
+#######################################################################
+# 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) \
+ VALUES ( \
+ '%{SQL-User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', \
+ '%S')"
+}
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/schema.sql
new file mode 100644
index 0000000..7a26230
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/schema.sql
@@ -0,0 +1,150 @@
+###########################################################################
+# $Id: c5185bee856646733a6bd9b341109cde0688b8f1 $ #
+# #
+# 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 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 '',
+ groupname varchar(64) NOT NULL default '',
+ realm varchar(64) default '',
+ nasipaddress varchar(15) NOT NULL default '',
+ nasportid varchar(15) 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(50) default NULL,
+ connectinfo_stop varchar(50) 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 '',
+ PRIMARY KEY (radacctid),
+ UNIQUE KEY acctuniqueid (acctuniqueid),
+ KEY username (username),
+ KEY framedipaddress (framedipaddress),
+ KEY acctsessionid (acctsessionid),
+ KEY acctsessiontime (acctsessiontime),
+ KEY acctstarttime (acctstarttime),
+ KEY acctinterval (acctinterval),
+ KEY acctstoptime (acctstoptime),
+ KEY nasipaddress (nasipaddress)
+) ENGINE = INNODB;
+
+#
+# Table structure for table 'radcheck'
+#
+
+CREATE TABLE 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 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 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 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 radusergroup (
+ username varchar(64) NOT NULL default '',
+ groupname varchar(64) NOT NULL default '',
+ priority int(11) NOT NULL default '1',
+ KEY username (username(32))
+);
+
+#
+# Table structure for table 'radpostauth'
+#
+CREATE TABLE 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 NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE = INNODB;
+
+#
+# Table structure for table 'nas'
+#
+CREATE TABLE 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)
+);
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/setup.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/setup.sql
new file mode 100644
index 0000000..d04a711
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/mysql/setup.sql
@@ -0,0 +1,24 @@
+# -*- text -*-
+##
+## admin.sql -- MySQL commands for creating the RADIUS user.
+##
+## WARNING: You should change 'localhost' and 'radpass'
+## to something else. Also update raddb/sql.conf
+## with the new RADIUS password.
+##
+## $Id: aff0505a473c67b65cfc19fae079454a36d4e119 $
+
+#
+# Create default administrator for RADIUS
+#
+CREATE USER 'radius'@'localhost';
+SET PASSWORD FOR 'radius'@'localhost' = PASSWORD('radpass');
+
+# The server can read any table in SQL
+GRANT SELECT ON radius.* TO 'radius'@'localhost';
+
+# The server can write to the accounting and post-auth logging table.
+#
+# i.e.
+GRANT ALL on radius.radacct TO 'radius'@'localhost';
+GRANT ALL on radius.radpostauth TO 'radius'@'localhost';
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/README b/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/README
new file mode 100644
index 0000000..71f5aa3
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/README
@@ -0,0 +1,5 @@
+ The SQL schema and 'create admin user" scripts are here in order to
+simplify the process of using MySQL cluster.
+
+ The queries are NOT located here, because the database driver for
+MySQL cluster is just "mysql", and not "ndb".
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/schema.sql
new file mode 100644
index 0000000..40ee953
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/schema.sql
@@ -0,0 +1,135 @@
+###########################################################################
+# $Id: a7f4c3121ded2b6557294de8bcab832c5715d038 $ #
+# #
+# schema.sql rlm_sql - FreeRADIUS SQL Module #
+# #
+# Database schema for MySQL Cluster. #
+# The only difference between this file and ../mysql/schema.sql #
+# is the definition of the storage engine. #
+# #
+# To load: #
+# mysql -uroot -prootpass radius < schema.sql #
+# #
+# Mike Machado <mike@innercite.com> #
+###########################################################################
+#
+# Table structure for table 'radacct'
+#
+
+CREATE TABLE 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 '',
+ groupname varchar(64) NOT NULL default '',
+ realm varchar(64) default '',
+ nasipaddress varchar(15) NOT NULL default '',
+ nasportid varchar(15) 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(50) default NULL,
+ connectinfo_stop varchar(50) 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 ''
+ PRIMARY KEY (radacctid),
+ UNIQUE KEY acctuniqueid (acctuniqueid),
+ KEY username (username),
+ KEY framedipaddress (framedipaddress),
+ KEY acctsessionid (acctsessionid),
+ KEY acctsessiontime (acctsessiontime),
+ KEY acctstarttime (acctstarttime),
+ KEY acctstoptime (acctstoptime),
+ KEY nasipaddress (nasipaddress)
+) ENGINE=ndbcluster;
+
+#
+# Table structure for table 'radcheck'
+#
+
+CREATE TABLE 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))
+) ENGINE=ndbcluster;
+
+#
+# Table structure for table 'radgroupcheck'
+#
+
+CREATE TABLE 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))
+) ENGINE=ndbcluster;
+
+#
+# Table structure for table 'radgroupreply'
+#
+
+CREATE TABLE 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))
+) ENGINE=ndbcluster;
+
+#
+# Table structure for table 'radreply'
+#
+
+CREATE TABLE 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))
+) ENGINE=ndbcluster;
+
+
+#
+# Table structure for table 'radusergroup'
+#
+
+CREATE TABLE radusergroup (
+ username varchar(64) NOT NULL default '',
+ groupname varchar(64) NOT NULL default '',
+ priority int(11) NOT NULL default '1',
+ KEY username (username(32))
+) ENGINE=ndbcluster;
+
+#
+# Table structure for table 'radpostauth'
+#
+
+CREATE TABLE 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 NOT NULL,
+ PRIMARY KEY (id)
+) ENGINE=ndbcluster;
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/setup.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/setup.sql
new file mode 100644
index 0000000..fda4cb9
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/ndb/setup.sql
@@ -0,0 +1,25 @@
+# -*- text -*-
+##
+## admin.sql -- MySQL commands for creating the RADIUS user.
+##
+## WARNING: You should change 'localhost' and 'radpass'
+## to something else. Also update raddb/sql.conf
+## with the new RADIUS password.
+##
+## $Id: 5c91384c0991ea9614b7c798a1ab4c89ca227115 $
+
+#
+# Create default administrator for RADIUS
+#
+CREATE USER 'radius'@'localhost';
+SET PASSWORD FOR 'radius'@'localhost' = PASSWORD('radpass');
+
+# The server can read any table in SQL
+GRANT ALL ON radius.* TO 'radius'@'localhost' identified by 'radpass';
+GRANT ALL ON radius.* TO 'radius'@'radsrvr' identified by 'radpass';
+
+# The server can write to the accounting and post-auth logging table.
+#
+# i.e.
+#GRANT ALL on radius.radacct TO 'radius'@'localhost' identified by 'radpass';
+#GRANT ALL on radius.radacct TO 'radius'@'radsrvr' identified by 'radpass';
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/oracle/queries.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/oracle/queries.conf
new file mode 100644
index 0000000..c062b66
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/oracle/queries.conf
@@ -0,0 +1,382 @@
+# -*- text -*-
+#
+# main/oracle/queries.conf -- Oracle configuration for default schema (schema.sql)
+#
+# $Id: ca22f5f5c9bf5dff47e60fb2bed56d6b161a4d08 $
+
+#######################################################################
+# 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}"
+
+#######################################################################
+# 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"
+#
+# Determines if we will query the default_user_profile or the User-Profile
+# if the user is not found. If the profile is found then we consider the user
+# found. By default this is set to 'no'.
+#
+#query_on_not_found = no
+
+
+#######################################################################
+# 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. Virtual 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
+#######################################################################
+#
+# WARNING: Oracle is case sensitive
+#
+# The main difference between MySQL and Oracle queries is the date format.
+# You must use the TO_DATE function to transform the radius date format to
+# the Oracle date format, and put NULL otherwise '0' in a void date field.
+#
+#######################################################################
+
+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"
+
+authorize_group_check_query = "\
+ SELECT \
+ ${groupcheck_table}.id, ${groupcheck_table}.GroupName, ${groupcheck_table}.Attribute, \
+ ${groupcheck_table}.Value,${groupcheck_table}.op \
+ FROM ${groupcheck_table}, ${usergroup_table} \
+ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
+ AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
+ ORDER BY ${groupcheck_table}.id"
+
+authorize_group_reply_query = "\
+ SELECT \
+ ${groupreply_table}.id, ${groupreply_table}.GroupName, ${groupreply_table}.Attribute, \
+ ${groupreply_table}.Value, ${groupreply_table}.op \
+ FROM ${groupreply_table}, ${usergroup_table} \
+ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' \
+ AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
+ ORDER BY ${groupreply_table}.id"
+
+#######################################################################
+# Simultaneous Use Checking Queries
+#######################################################################
+# simul_count_query - query for the number of current connections
+# - If this is not defined, no simultaneouls 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.
+#######################################################################
+
+#
+# Uncomment simul_count_query to enable simultaneous use checking
+#
+#simul_count_query = "\
+# SELECT COUNT(*) \
+# FROM ${acct_table1} \
+# WHERE UserName = '%{SQL-User-Name}' \
+# AND AcctStopTime IS NULL"
+
+simul_verify_query = "\
+ SELECT \
+ RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, \
+ FramedIPAddress, CallingStationId, FramedProtocol \
+ FROM ${acct_table1} \
+ WHERE UserName='%{SQL-User-Name}' \
+ AND AcctStopTime IS NULL"
+
+#######################################################################
+# Group Membership Queries
+#######################################################################
+# group_membership_query - Check user group membership
+#######################################################################
+
+group_membership_query = "\
+ SELECT GroupName \
+ FROM ${usergroup_table} \
+ WHERE UserName='%{SQL-User-Name}'"
+
+#######################################################################
+# 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}.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
+
+ type {
+ accounting-on {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
+ AcctSessionTime = round((TO_DATE('%S','yyyy-mm-dd hh24:mi:ss') - \
+ TO_DATE(TO_CHAR(acctstarttime, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))*86400), \
+ AcctTerminateCause='%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \
+ AcctStopDelay = %{%{Acct-Delay-Time}:-0} \
+ WHERE AcctStopTime IS NULL \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStartTime <= TO_DATE('%S','yyyy-mm-dd hh24:mi:ss')"
+ }
+
+ accounting-off {
+ query = "${..accounting-on.query}"
+ }
+
+ start {
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (RadAcctId, AcctSessionId, AcctUniqueId, \
+ UserName, Realm, NASIPAddress, \
+ NASPortId, NASPortType, AcctStartTime, \
+ AcctStopTime, AcctSessionTime, AcctAuthentic, \
+ ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, \
+ AcctOutputOctets, CalledStationId, CallingStationId, \
+ AcctTerminateCause, ServiceType, FramedProtocol, \
+ FramedIPAddress, AcctStartDelay, AcctStopDelay, \
+ XAscendSessionSvrKey) \
+ VALUES(\
+ '', \
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', \
+ '%{NAS-IP-Address}', \
+ '%{NAS-Port-Id}', \
+ '%{NAS-Port-Type}', \
+ TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
+ NULL, \
+ '0', \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ '', \
+ '0', \
+ '0', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}', \
+ '%{Acct-Delay-Time}', \
+ '0', \
+ '%{X-Ascend-Session-Svr-Key}')"
+
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStartTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
+ AcctStartDelay = '%{%{Acct-Delay-Time}:-0}', \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStopTime IS NULL"
+ }
+
+ interim-update {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', ''), \
+ AcctSessionTime = '%{Acct-Session-Time}', \
+ AcctInputOctets = '%{Acct-Input-Octets}' + \
+ ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
+ AcctOutputOctets = '%{Acct-Output-Octets}' + \
+ ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296) \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress= '%{NAS-IP-Address}' \
+ AND AcctStopTime IS NULL"
+
+ query = "\
+ INSERT into ${....acct_table1} \
+ (RadAcctId, AcctSessionId, AcctUniqueId, \
+ UserName, Realm, NASIPAddress, \
+ NASPortId, NASPortType, AcctStartTime, \
+ AcctSessionTime, AcctAuthentic, ConnectInfo_start, \
+ AcctInputOctets, AcctOutputOctets, CalledStationId, \
+ CallingStationId, ServiceType, FramedProtocol, \
+ FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \
+ VALUES(\
+ '', \
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', \
+ '%{NAS-IP-Address}', \
+ '%{NAS-Port-Id}', \
+ '%{NAS-Port-Type}', \
+ NULL, \
+ '%{Acct-Session-Time}', \
+ '%{Acct-Authentic}', \
+ '', \
+ '%{Acct-Input-Octets}' + \
+ ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
+ '%{Acct-Output-Octets}' + \
+ ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}', \
+ '0', \
+ '%{X-Ascend-Session-Svr-Key}')"
+ }
+
+ stop {
+ query = "\
+ UPDATE ${....acct_table2} \
+ SET \
+ AcctStopTime = TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
+ AcctSessionTime = '%{Acct-Session-Time}', \
+ AcctInputOctets = '%{Acct-Input-Octets}' + \
+ ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
+ AcctOutputOctets = '%{Acct-Output-Octets}' + \
+ ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
+ AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+ AcctStopDelay = '%{%{Acct-Delay-Time}:-0}', \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{NAS-IP-Address}' \
+ AND AcctStopTime IS NULL"
+
+ query = "\
+ INSERT into ${....acct_table2} \
+ (RadAcctId, AcctSessionId, AcctUniqueId, \
+ UserName, Realm, NASIPAddress, \
+ NASPortId, NASPortType, AcctStartTime, \
+ AcctStopTime, AcctSessionTime, AcctAuthentic, \
+ ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, \
+ AcctOutputOctets, CalledStationId, CallingStationId, \
+ AcctTerminateCause, ServiceType, FramedProtocol, \
+ FramedIPAddress, AcctStartDelay, AcctStopDelay) \
+ VALUES(\
+ '', \
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ '%{Realm}', \
+ '%{NAS-IP-Address}', \
+ '%{NAS-Port-Id}', \
+ '%{NAS-Port-Type}', \
+ NULL, \
+ TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), \
+ '%{Acct-Session-Time}', \
+ '%{Acct-Authentic}', \
+ '', \
+ '%{Connect-Info}', \
+ '%{Acct-Input-Octets}' + \
+ ('%{%{Acct-Input-Gigawords}:-0}' * 4294967296), \
+ '%{Acct-Output-Octets}' + \
+ ('%{%{Acct-Output-Gigawords}:-0}' * 4294967296), \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Acct-Terminate-Cause}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}', \
+ '0', \
+ '%{%{Acct-Delay-Time}:-0}')"
+
+ }
+ }
+}
+
+#######################################################################
+# 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) \
+ VALUES (\
+ '%{User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', \
+ TO_TIMESTAMP('%S','YYYY-MM-DDHH24:MI:SS'))"
+}
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/oracle/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/oracle/schema.sql
new file mode 100644
index 0000000..1dcaf7a
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/oracle/schema.sql
@@ -0,0 +1,230 @@
+/*
+ * $Id: c11295fa7307a7c05a586f5354dd59de32c059de $
+ *
+ * Oracle schema for FreeRADIUS
+ *
+ *
+ * NOTE: Which columns are NULLable??
+ */
+
+/*
+ * Table structure for table 'radacct'
+ */
+CREATE TABLE radacct (
+ radacctid INT PRIMARY KEY,
+ acctsessionid VARCHAR(96) NOT NULL,
+ acctuniqueid VARCHAR(32),
+ username VARCHAR(64) NOT NULL,
+ groupname VARCHAR(32),
+ realm VARCHAR(30),
+ nasipaddress VARCHAR(15) NOT NULL,
+ nasportid VARCHAR(32),
+ nasporttype VARCHAR(32),
+ acctstarttime TIMESTAMP WITH TIME ZONE,
+ acctstoptime TIMESTAMP WITH TIME ZONE,
+ acctsessiontime NUMERIC(19),
+ acctauthentic VARCHAR(32),
+ connectinfo_start VARCHAR(50),
+ connectinfo_stop VARCHAR(50),
+ acctinputoctets NUMERIC(19),
+ acctoutputoctets NUMERIC(19),
+ calledstationid VARCHAR(50),
+ callingstationid VARCHAR(50),
+ acctterminatecause VARCHAR(32),
+ servicetype VARCHAR(32),
+ framedprotocol VARCHAR(32),
+ framedipaddress VARCHAR(15),
+ acctstartdelay NUMERIC(12),
+ acctstopdelay NUMERIC(12),
+ XAscendSessionSvrKey VARCHAR(10)
+);
+
+CREATE UNIUQE INDEX radacct_idx0
+ ON radacct(acctuniqueid);
+CREATE UNIQUE INDEX radacct_idx1
+ ON radacct(acctsessionid,username,acctstarttime,
+ acctstoptime,nasipaddress,framedipaddress);
+
+CREATE SEQUENCE radacct_seq START WITH 1 INCREMENT BY 1;
+
+/* Trigger to emulate a serial # on the primary key */
+CREATE OR REPLACE TRIGGER radacct_serialnumber
+ BEFORE INSERT OR UPDATE OF radacctid ON radacct
+ FOR EACH ROW
+ BEGIN
+ if ( :new.radacctid = 0 or :new.radacctid is null ) then
+ SELECT radacct_seq.nextval into :new.radacctid from dual;
+ end if;
+ END;
+/
+
+/*
+ * Table structure for table 'radcheck'
+ */
+CREATE TABLE radcheck (
+ id INT PRIMARY KEY,
+ username VARCHAR(30) NOT NULL,
+ attribute VARCHAR(64),
+ op VARCHAR(2) NOT NULL,
+ value VARCHAR(40)
+);
+CREATE SEQUENCE radcheck_seq START WITH 1 INCREMENT BY 1;
+
+/* Trigger to emulate a serial # on the primary key */
+CREATE OR REPLACE TRIGGER radcheck_serialnumber
+ BEFORE INSERT OR UPDATE OF id ON radcheck
+ FOR EACH ROW
+ BEGIN
+ if ( :new.id = 0 or :new.id is null ) then
+ SELECT radcheck_seq.nextval into :new.id from dual;
+ end if;
+ END;
+/
+
+/*
+ * Table structure for table 'radgroupcheck'
+ */
+CREATE TABLE radgroupcheck (
+ id INT PRIMARY KEY,
+ groupname VARCHAR(20) UNIQUE NOT NULL,
+ attribute VARCHAR(64),
+ op CHAR(2) NOT NULL,
+ value VARCHAR(40)
+);
+CREATE SEQUENCE radgroupcheck_seq START WITH 1 INCREMENT BY 1;
+
+/*
+ * Table structure for table 'radgroupreply'
+ */
+CREATE TABLE radgroupreply (
+ id INT PRIMARY KEY,
+ GroupName VARCHAR(20) UNIQUE NOT NULL,
+ Attribute VARCHAR(64),
+ op CHAR(2) NOT NULL,
+ Value VARCHAR(40)
+);
+CREATE SEQUENCE radgroupreply_seq START WITH 1 INCREMENT BY 1;
+
+/*
+ * Table structure for table 'radreply'
+ */
+CREATE TABLE radreply (
+ id INT PRIMARY KEY,
+ UserName VARCHAR(30) NOT NULL,
+ Attribute VARCHAR(64),
+ op CHAR(2) NOT NULL,
+ Value VARCHAR(40)
+);
+CREATE INDEX radreply_idx1 ON radreply(UserName);
+CREATE SEQUENCE radreply_seq START WITH 1 INCREMENT BY 1;
+
+/* Trigger to emulate a serial # on the primary key */
+CREATE OR REPLACE TRIGGER radreply_serialnumber
+ BEFORE INSERT OR UPDATE OF id ON radreply
+ FOR EACH ROW
+ BEGIN
+ if ( :new.id = 0 or :new.id is null ) then
+ SELECT radreply_seq.nextval into :new.id from dual;
+ end if;
+ END;
+/
+
+/*
+ * Table structure for table 'radusergroup'
+ */
+CREATE TABLE radusergroup (
+ id INT PRIMARY KEY,
+ UserName VARCHAR(30) UNIQUE NOT NULL,
+ GroupName VARCHAR(30)
+);
+CREATE SEQUENCE radusergroup_seq START WITH 1 INCREMENT BY 1;
+
+/* Trigger to emulate a serial # on the primary key */
+CREATE OR REPLACE TRIGGER radusergroup_serialnumber
+ BEFORE INSERT OR UPDATE OF id ON radusergroup
+ FOR EACH ROW
+ BEGIN
+ if ( :new.id = 0 or :new.id is null ) then
+ SELECT radusergroup_seq.nextval into :new.id from dual;
+ end if;
+ END;
+/
+
+
+/*
+ * Table structure for table 'realmgroup'
+ */
+CREATE TABLE realmgroup (
+ id INT PRIMARY KEY,
+ RealmName VARCHAR(30) UNIQUE NOT NULL,
+ GroupName VARCHAR(30)
+);
+CREATE SEQUENCE realmgroup_seq START WITH 1 INCREMENT BY 1;
+
+CREATE TABLE realms (
+ id INT PRIMARY KEY,
+ realmname VARCHAR(64),
+ nas VARCHAR(128),
+ authport INT,
+ options VARCHAR(128)
+);
+CREATE SEQUENCE realms_seq START WITH 1 INCREMENT BY 1;
+
+CREATE TABLE radhuntgroup (
+ id INT PRIMARY KEY,
+ GroupName VARCHAR(64) NOT NULL,
+ Nasipaddress VARCHAR(15) UNIQUE NOT NULL,
+ NASPortID VARCHAR(15)
+);
+
+CREATE SEQUENCE radhuntgroup_seq START WITH 1 INCREMENT BY 1;
+
+CREATE OR REPLACE TRIGGER radhuntgroup_serialnumber
+ BEFORE INSERT OR UPDATE OF id ON radhuntgroup
+ FOR EACH ROW
+ BEGIN
+ if ( :new.id = 0 or :new.id is null ) then
+ SELECT radhuntgroup_seq.nextval into :new.id from dual;
+ end if;
+ END;
+
+CREATE TABLE radpostauth (
+ id INT PRIMARY KEY,
+ UserName VARCHAR(64) NOT NULL,
+ Pass VARCHAR(64),
+ Reply VARCHAR(64),
+ AuthDate DATE
+);
+
+CREATE SEQUENCE radpostauth_seq START WITH 1 INCREMENT BY 1;
+
+CREATE OR REPLACE TRIGGER radpostauth_TRIG
+ BEFORE INSERT OR UPDATE OF id ON radpostauth
+ FOR EACH ROW
+ BEGIN
+ if ( :new.id = 0 or :new.id is null ) then
+ SELECT radpostauth_seq.nextval into :new.id from dual;
+ end if;
+ if (:new.AuthDate is null) then
+ select sysdate into :new.AuthDate from dual;
+ end if;
+ END;
+
+/
+
+/*
+ * Table structure for table 'nas'
+ */
+CREATE TABLE nas (
+ id INT PRIMARY KEY,
+ nasname VARCHAR(128),
+ shortname VARCHAR(32),
+ type VARCHAR(30),
+ ports INT,
+ secret VARCHAR(60),
+ server VARCHAR(64),
+ community VARCHAR(50),
+ description VARCHAR(200)
+);
+CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;
+
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/cisco_h323_db_schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/cisco_h323_db_schema.sql
new file mode 100644
index 0000000..7d14c3c
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/cisco_h323_db_schema.sql
@@ -0,0 +1,295 @@
+/*
+ * $Id: ec9731648e83c1e1d4ee39ed24a994ee79bb7dd6 $
+ *
+ * --- Peter Nixon [ codemonkey@peternixon.net ]
+ *
+ * This is a custom SQL schema for doing H323 and SIP VoIP accounting
+ * with FreeRadius and Cisco equipment. It is currently known to work
+ * with 3640, 5300 and 5350 series as well as CSPS (Cisco SIP Proxy
+ * Server). It will scale A LOT better than the default radius schema
+ * which is designed for simple dialup installations of FreeRadius.
+ *
+ * For this schema to work properly you MUST use
+ * raddb/sql/postgresql/voip-postpaid.conf rather than
+ * raddb/sql/postgresql/dialup.conf
+ *
+ * If you wish to do RADIUS Authentication using the same database,
+ * you MUST use use raddb/sql/postgresql/schema.sql as well as this schema.
+ */
+
+/*
+ * Table structure for 'Start' tables
+ */
+
+CREATE TABLE StartVoIP (
+ RadAcctId BIGSERIAL PRIMARY KEY,
+ AcctTime TIMESTAMP with time zone NOT NULL,
+ h323SetupTime TIMESTAMP with time zone,
+ H323ConnectTime TIMESTAMP with time zone,
+ UserName VARCHAR(64),
+ RadiusServerName VARCHAR(32),
+ NASIPAddress INET NOT NULL,
+ CalledStationId VARCHAR(80),
+ CallingStationId VARCHAR(80),
+ AcctDelayTime INTEGER,
+ H323GWID VARCHAR(32),
+ h323CallOrigin VARCHAR(10),
+ CallID VARCHAR(80) NOT NULL,
+ processed BOOLEAN DEFAULT false
+);
+create index startvoipcombo on startvoip (AcctTime, nasipaddress);
+
+
+CREATE TABLE StartTelephony (
+ RadAcctId BIGSERIAL PRIMARY KEY,
+ AcctTime TIMESTAMP with time zone NOT NULL,
+ h323SetupTime TIMESTAMP with time zone,
+ H323ConnectTime TIMESTAMP with time zone,
+ UserName VARCHAR(64),
+ RadiusServerName VARCHAR(32),
+ NASIPAddress INET NOT NULL,
+ CalledStationId VARCHAR(80),
+ CallingStationId VARCHAR(80),
+ AcctDelayTime INTEGER,
+ H323GWID VARCHAR(32),
+ h323CallOrigin VARCHAR(10),
+ CallID VARCHAR(80) NOT NULL,
+ processed BOOLEAN DEFAULT false
+);
+create index starttelephonycombo on starttelephony (AcctTime, nasipaddress);
+
+
+
+/*
+ * Table structure for 'Stop' tables
+ */
+CREATE TABLE StopVoIP (
+ RadAcctId BIGSERIAL PRIMARY KEY,
+ AcctTime TIMESTAMP with time zone NOT NULL,
+ H323SetupTime TIMESTAMP with time zone,
+ H323ConnectTime TIMESTAMP with time zone,
+ H323DisconnectTime TIMESTAMP with time zone,
+ UserName VARCHAR(32),
+ RadiusServerName VARCHAR(32),
+ NASIPAddress INET NOT NULL,
+ AcctSessionTime BIGINT,
+ AcctInputOctets BIGINT,
+ AcctOutputOctets BIGINT,
+ CalledStationId VARCHAR(80),
+ CallingStationId VARCHAR(80),
+ AcctDelayTime SMALLINT,
+ CiscoNASPort VARCHAR(1),
+ H323GWID VARCHAR(32),
+ H323CallOrigin VARCHAR(10),
+ H323DisconnectCause VARCHAR(20),
+ H323RemoteAddress INET,
+ H323VoiceQuality INTEGER,
+ CallID VARCHAR(80) NOT NULL,
+ processed BOOLEAN DEFAULT false
+);
+create UNIQUE index stopvoipcombo on stopvoip (AcctTime, nasipaddress, CallID);
+
+
+CREATE TABLE StopTelephony (
+ RadAcctId BIGSERIAL PRIMARY KEY,
+ AcctTime TIMESTAMP with time zone NOT NULL,
+ H323SetupTime TIMESTAMP with time zone NOT NULL,
+ H323ConnectTime TIMESTAMP with time zone NOT NULL,
+ H323DisconnectTime TIMESTAMP with time zone NOT NULL,
+ UserName VARCHAR(32) DEFAULT '' NOT NULL,
+ RadiusServerName VARCHAR(32),
+ NASIPAddress INET NOT NULL,
+ AcctSessionTime BIGINT,
+ AcctInputOctets BIGINT,
+ AcctOutputOctets BIGINT,
+ CalledStationId VARCHAR(80),
+ CallingStationId VARCHAR(80),
+ AcctDelayTime SMALLINT,
+ CiscoNASPort VARCHAR(16),
+ H323GWID VARCHAR(32),
+ H323CallOrigin VARCHAR(10),
+ H323DisconnectCause VARCHAR(20),
+ H323RemoteAddress INET,
+ H323VoiceQuality INTEGER,
+ CallID VARCHAR(80) NOT NULL,
+ processed BOOLEAN DEFAULT false
+);
+-- You can have more than one record that is identical except for CiscoNASPort if you have a dial peer hungroup
+-- configured for multiple PRIs.
+create UNIQUE index stoptelephonycombo on stoptelephony (AcctTime, nasipaddress, CallID, CiscoNASPort);
+
+/*
+ * Table structure for 'gateways'
+ *
+ * This table should list the IP addresses, names and locations of all your gateways
+ * This can be used to make more useful reports.
+ *
+ * Note: This table should be removed in favour of using the "nas" table.
+ */
+
+CREATE TABLE gateways (
+ gw_ip INET NOT NULL,
+ gw_name VARCHAR(32) NOT NULL,
+ gw_city VARCHAR(32)
+);
+
+
+/*
+ * Table structure for 'customers'
+ *
+ * This table should list your Customers names and company
+ * This can be used to make more useful reports.
+ */
+
+CREATE TABLE customers (
+ cust_id SERIAL NOT NULL,
+ company VARCHAR(32),
+ customer VARCHAR(32)
+);
+
+/*
+ * Table structure for 'cust_gw'
+ *
+ * This table should list the IP addresses and Customer IDs of all your Customers gateways
+ * This can be used to make more useful reports.
+ */
+
+CREATE TABLE cust_gw (
+ cust_gw INET PRIMARY KEY,
+ cust_id INTEGER NOT NULL,
+ "location" VARCHAR(32)
+);
+
+
+CREATE VIEW customerip AS
+ SELECT gw.cust_gw AS ipaddr, cust.company, cust.customer, gw."location" FROM customers cust, cust_gw gw WHERE (cust.cust_id = gw.cust_id);
+
+
+-- create plpgsql language (You need to be a database superuser to be able to do this)
+CREATE FUNCTION "plpgsql_call_handler" () RETURNS LANGUAGE_HANDLER AS '$libdir/plpgsql' LANGUAGE C;
+CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
+
+/*
+ * Function 'strip_dot'
+ * removes "." from the start of cisco timestamps
+ *
+ * From the cisco website:
+ * "A timestamp that is preceded by an asterisk (*) or a dot (.) may not be accurate.
+ * An asterisk (*) means that after a gateway reboot, the gateway clock was not manually set
+ * and the gateway has not synchronized with an NTP server yet. A dot (.) means the gateway
+ * NTP has lost synchronization with an NTP server."
+ *
+ * We therefore do not bother to strip asterisks (*) from timestamps, as you NEED ntp setup
+ * unless you don't care about billing at all!
+ *
+ * * Example useage:
+ * insert into mytable values (strip_dot('.16:46:02.356 EET Wed Dec 11 2002'));
+ *
+ */
+
+
+CREATE OR REPLACE FUNCTION strip_dot (VARCHAR) RETURNS TIMESTAMPTZ AS '
+ DECLARE
+ original_timestamp ALIAS FOR $1;
+ BEGIN
+ IF original_timestamp = '''' THEN
+ RETURN NULL;
+ END IF;
+ IF substring(original_timestamp from 1 for 1) = ''.'' THEN
+ RETURN substring(original_timestamp from 2);
+ ELSE
+ RETURN original_timestamp;
+ END IF;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+CREATE OR REPLACE FUNCTION pick_id (VARCHAR, VARCHAR) RETURNS VARCHAR AS '
+ DECLARE
+ h323confid ALIAS FOR $1;
+ callid ALIAS FOR $2;
+ BEGIN
+ IF h323confid <> '''' THEN
+ RETURN h323confid;
+ END IF;
+ IF callid <> '''' THEN
+ RETURN callid;
+ END IF;
+ RETURN NULL;
+ END;
+' LANGUAGE 'plpgsql';
+
+
+
+/*
+ * Table structure for 'isdn_error_codes' table
+ *
+ * Taken from cisco.com this data can be JOINED against h323DisconnectCause to
+ * give human readable error reports.
+ *
+ */
+
+
+CREATE TABLE isdn_error_codes (
+ error_code VARCHAR(2) PRIMARY KEY,
+ desc_short VARCHAR(90),
+ desc_long TEXT
+);
+
+/*
+ * Data for 'isdn_error_codes' table
+ */
+
+INSERT INTO isdn_error_codes VALUES ('1', 'Unallocated (unassigned) number', 'The ISDN number was sent to the switch in the correct format; however, the number is not assigned to any destination equipment.');
+INSERT INTO isdn_error_codes VALUES ('10', 'Normal call clearing', 'Normal call clearing has occurred.');
+INSERT INTO isdn_error_codes VALUES ('11', 'User busy', 'The called system acknowledges the connection request but is unable to accept the call because all B channels are in use.');
+INSERT INTO isdn_error_codes VALUES ('12', 'No user responding', 'The connection cannot be completed because the destination does not respond to the call.');
+INSERT INTO isdn_error_codes VALUES ('13', 'No answer from user (user alerted)', 'The destination responds to the connection request but fails to complete the connection within the prescribed time. The problem is at the remote end of the connection.');
+INSERT INTO isdn_error_codes VALUES ('15', 'Call rejected', 'The destination is capable of accepting the call but rejected the call for an unknown reason.');
+INSERT INTO isdn_error_codes VALUES ('16', 'Number changed', 'The ISDN number used to set up the call is not assigned to any system.');
+INSERT INTO isdn_error_codes VALUES ('1A', 'Non-selected user clearing', 'The destination is capable of accepting the call but rejected the call because it was not assigned to the user.');
+INSERT INTO isdn_error_codes VALUES ('1B', 'Designation out of order', 'The destination cannot be reached because the interface is not functioning correctly, and a signaling message cannot be delivered. This might be a temporary condition, but it could last for an extended period of time. For example, the remote equipment might be turned off.');
+INSERT INTO isdn_error_codes VALUES ('1C', 'Invalid number format', 'The connection could be established because the destination address was presented in an unrecognizable format or because the destination address was incomplete.');
+INSERT INTO isdn_error_codes VALUES ('1D', 'Facility rejected', 'The facility requested by the user cannot be provided by the network.');
+INSERT INTO isdn_error_codes VALUES ('1E', 'Response to STATUS ENQUIRY', 'The status message was generated in direct response to the prior receipt of a status enquiry message.');
+INSERT INTO isdn_error_codes VALUES ('1F', 'Normal, unspecified', 'Reports the occurrence of a normal event when no standard cause applies. No action required.');
+INSERT INTO isdn_error_codes VALUES ('2', 'No route to specified transit network', 'The ISDN exchange is asked to route the call through an unrecognized intermediate network.');
+INSERT INTO isdn_error_codes VALUES ('22', 'No circuit/channel available', 'The connection cannot be established because no appropriate channel is available to take the call.');
+INSERT INTO isdn_error_codes VALUES ('26', 'Network out of order', 'The destination cannot be reached because the network is not functioning correctly, and the condition might last for an extended period of time. An immediate reconnect attempt will probably be unsuccessful.');
+INSERT INTO isdn_error_codes VALUES ('29', 'Temporary failure', 'An error occurred because the network is not functioning correctly. The problem will be resolved shortly.');
+INSERT INTO isdn_error_codes VALUES ('2A', 'Switching equipment congestion', 'The destination cannot be reached because the network switching equipment is temporarily overloaded.');
+INSERT INTO isdn_error_codes VALUES ('2B', 'Access information discarded', 'The network cannot provide the requested access information.');
+INSERT INTO isdn_error_codes VALUES ('2C', 'Requested circuit/channel not available', 'The remote equipment cannot provide the requested channel for an unknown reason. This might be a temporary problem.');
+INSERT INTO isdn_error_codes VALUES ('2F', 'Resources unavailable, unspecified', 'The requested channel or service is unavailable for an unknown reason. This might be a temporary problem.');
+INSERT INTO isdn_error_codes VALUES ('3', 'No route to destination', 'The call was routed through an intermediate network that does not serve the destination address.');
+INSERT INTO isdn_error_codes VALUES ('31', 'Quality of service unavailable', 'The requested quality of service cannot be provided by the network. This might be a subscription problem.');
+INSERT INTO isdn_error_codes VALUES ('32', 'Requested facility not subscribed', 'The remote equipment supports the requested supplementary service by subscription only.');
+INSERT INTO isdn_error_codes VALUES ('39', 'Bearer capability not authorized', 'The user requested a bearer capability that the network provides, but the user is not authorized to use it. This might be a subscription problem.');
+INSERT INTO isdn_error_codes VALUES ('3A', 'Bearer capability not presently available', 'The network normally provides the requested bearer capability, but it is unavailable at the present time. This might be due to a temporary network problem or to a subscription problem.');
+INSERT INTO isdn_error_codes VALUES ('3F', 'Service or option not available, unspecified', 'The network or remote equipment was unable to provide the requested service option for an unspecified reason. This might be a subscription problem.');
+INSERT INTO isdn_error_codes VALUES ('41', 'Bearer capability not implemented', 'The network cannot provide the bearer capability requested by the user.');
+INSERT INTO isdn_error_codes VALUES ('42', 'Channel type not implemented', 'The network or the destination equipment does not support the requested channel type.');
+INSERT INTO isdn_error_codes VALUES ('45', 'Requested facility not implemented', 'The remote equipment does not support the requested supplementary service.');
+INSERT INTO isdn_error_codes VALUES ('46', 'Only restricted digital information bearer capability is available', 'The network is unable to provide unrestricted digital information bearer capability.');
+INSERT INTO isdn_error_codes VALUES ('4F', 'Service or option not implemented, unspecified', 'The network or remote equipment is unable to provide the requested service option for an unspecified reason. This might be a subscription problem.');
+INSERT INTO isdn_error_codes VALUES ('51', 'Invalid call reference value', 'The remote equipment received a call with a call reference that is not currently in use on the user-network interface.');
+INSERT INTO isdn_error_codes VALUES ('52', 'Identified channel does not exist', 'The receiving equipment is requested to use a channel that is not activated on the interface for calls.');
+INSERT INTO isdn_error_codes VALUES ('53', 'A suspended call exists, but this call identity does not', 'The network received a call resume request. The call resume request contained a Call Identify information element that indicates that the call identity is being used for a suspended call.');
+INSERT INTO isdn_error_codes VALUES ('54', 'Call identity in use', 'The network received a call resume request. The call resume request contained a Call Identify information element that indicates that it is in use for a suspended call.');
+INSERT INTO isdn_error_codes VALUES ('55', 'No call suspended', 'The network received a call resume request when there was not a suspended call pending. This might be a transient error that will be resolved by successive call retries.');
+INSERT INTO isdn_error_codes VALUES ('56', 'Call having the requested call identity has been cleared', 'The network received a call resume request. The call resume request contained a Call Identity information element, which once indicated a suspended call. However, the suspended call was cleared either by timeout or by the remote user.');
+INSERT INTO isdn_error_codes VALUES ('58', 'Incompatible destination', 'Indicates that an attempt was made to connect to non-ISDN equipment. For example, to an analog line.');
+INSERT INTO isdn_error_codes VALUES ('5B', 'Invalid transit network selection', 'The ISDN exchange was asked to route the call through an unrecognized intermediate network.');
+INSERT INTO isdn_error_codes VALUES ('5F', 'Invalid message, unspecified', 'An invalid message was received, and no standard cause applies. This is usually due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
+INSERT INTO isdn_error_codes VALUES ('6', 'Channel unacceptable', 'The service quality of the specified channel is insufficient to accept the connection.');
+INSERT INTO isdn_error_codes VALUES ('60', 'Mandatory information element is missing', 'The receiving equipment received a message that did not include one of the mandatory information elements. This is usually due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
+INSERT INTO isdn_error_codes VALUES ('61', 'Message type non-existent or not implemented', 'The receiving equipment received an unrecognized message, either because the message type was invalid or because the message type was valid but not supported. The cause is due to either a problem with the remote configuration or a problem with the local D channel.');
+INSERT INTO isdn_error_codes VALUES ('62', 'Message not compatible with call state or message type non-existent or not implemented', 'The remote equipment received an invalid message, and no standard cause applies. This cause is due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
+INSERT INTO isdn_error_codes VALUES ('63', 'Information element non-existent or not implemented', 'The remote equipment received a message that includes information elements, which were not recognized. This is usually due to a D-channel error. If this error occurs systematically, report it to your ISDN service provider.');
+INSERT INTO isdn_error_codes VALUES ('64', 'Invalid information element contents', 'The remote equipment received a message that includes invalid information in the information element. This is usually due to a D-channel error.');
+INSERT INTO isdn_error_codes VALUES ('65', 'Message not compatible with call state', 'The remote equipment received an unexpected message that does not correspond to the current state of the connection. This is usually due to a D-channel error.');
+INSERT INTO isdn_error_codes VALUES ('66', 'Recovery on timer expires', 'An error-handling (recovery) procedure was initiated by a timer expiry. This is usually a temporary problem.');
+INSERT INTO isdn_error_codes VALUES ('6F', 'Protocol error, unspecified', 'An unspecified D-channel error when no other standard cause applies.');
+INSERT INTO isdn_error_codes VALUES ('7', 'Call awarded and being delivered in an established channel', 'The user is assigned an incoming call that is being connected to an already-established call channel.');
+INSERT INTO isdn_error_codes VALUES ('7F', 'Internetworking, unspecified', 'An event occurred, but the network does not provide causes for the action that it takes. The precise problem is unknown.');
+
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/update_radacct_group.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/update_radacct_group.sql
new file mode 100644
index 0000000..270a0c9
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/update_radacct_group.sql
@@ -0,0 +1,37 @@
+/*
+ * $Id: 37f42a0b13515b09f9c7792e8a64b2a3b187e7a3 $
+ *
+ * OPTIONAL Postgresql trigger for FreeRADIUS
+ *
+ * This trigger updates fills in the groupname field (which doesnt come in Accounting packets)
+ * by querying the radusergroup table.
+ * This makes it easier to do group summary reports, however note that it does add some extra
+ * database load to 50% of your SQL accounting queries. If you dont care about group summary
+ * reports then you dont need to install this.
+ *
+ */
+
+
+CREATE OR REPLACE FUNCTION upd_radgroups() RETURNS trigger AS'
+
+DECLARE
+ v_groupname varchar;
+
+BEGIN
+ SELECT INTO v_groupname GroupName FROM radusergroup WHERE CalledStationId = NEW.CalledStationId AND UserName = NEW.UserName;
+ IF FOUND THEN
+ UPDATE radacct SET GroupName = v_groupname WHERE RadAcctId = NEW.RadAcctId;
+ END IF;
+
+ RETURN NEW;
+END
+
+'LANGUAGE plpgsql;
+
+
+DROP TRIGGER upd_radgroups ON radacct;
+
+CREATE TRIGGER upd_radgroups AFTER INSERT ON radacct
+ FOR EACH ROW EXECUTE PROCEDURE upd_radgroups();
+
+
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/voip-postpaid.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/voip-postpaid.conf
new file mode 100644
index 0000000..6ae361d
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/extras/voip-postpaid.conf
@@ -0,0 +1,70 @@
+# -*- text -*-
+##
+## voip-postpaid.conf -- PostgreSQL configuration for H323 VoIP billingx
+## (cisco_h323_db_schema.sql)
+##
+## $Id: 9f1449cc37d80e37025bdfd08fbd4d028aa0c800 $
+
+
+ #######################################################################
+ # 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 "none" as the user name.
+ #
+ #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
+ #
+ sql_user_name = "%{User-Name}"
+
+ accounting {
+ reference = "%{tolower:type.%{Acct-Status-Type}.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
+
+ type {
+ start {
+ query = "INSERT INTO ${....acct_table1}%{h323-call-type} \
+ (RadiusServerName, UserName, NASIPAddress, AcctTime, CalledStationId, \
+ CallingStationId, AcctDelayTime, h323gwid, h323callorigin, \
+ h323setuptime, H323ConnectTime, callid) \
+ VALUES(\
+ '${radius_server_name}', '%{SQL-User-Name}', \
+ '%{NAS-IP-Address}', now(), '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', '%{%{Acct-Delay-Time}:-0}', '%{h323-gw-id}', \
+ '%{h323-call-origin}', strip_dot('%{h323-setup-time}'), \
+ strip_dot('%{h323-connect-time}'), pick_id('%{h323-conf-id}', \
+ '%{call-id}'))"
+ }
+
+ stop {
+ query = "INSERT INTO $....acct_table2}%{h323-call-type} \
+ (RadiusServerName, UserName, NASIPAddress, AcctTime, \
+ AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, \
+ CallingStationId, AcctDelayTime, H323RemoteAddress, H323VoiceQuality, \
+ CiscoNASPort, h323callorigin, callid, h323connecttime, \
+ h323disconnectcause, h323disconnecttime, h323gwid, h323setuptime) \
+ VALUES(\
+ '${radius_server_name}', '%{SQL-User-Name}', '%{NAS-IP-Address}', \
+ NOW(), '%{%{Acct-Session-Time}:-0}', \
+ '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Octets}:-0}', \
+ '%{Called-Station-Id}', '%{Calling-Station-Id}', \
+ '%{%{Acct-Delay-Time}:-0}', NULLIF('%{h323-remote-address}', '')::inet, \
+ NULLIF('%{h323-voice-quality}','')::integer, \
+ NULLIF('%{Cisco-NAS-Port}', ''), \
+ '%{h323-call-origin}', pick_id('%{h323-conf-id}', '%{call-id}'), \
+ strip_dot('%{h323-connect-time}'), '%{h323-disconnect-cause}', \
+ strip_dot('%{h323-disconnect-time}'), '%{h323-gw-id}', \
+ strip_dot('%{h323-setup-time}'))"
+ }
+ }
+ }
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/queries.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/queries.conf
new file mode 100644
index 0000000..d5b61cf
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/queries.conf
@@ -0,0 +1,448 @@
+# -*- text -*-
+#
+# main/postgresql/queries.conf -- PostgreSQL configuration for default schema (schema.sql)
+#
+# $Id: 0f2a29afff36136bb171a9a97ee90199b017e46c $
+
+# 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 "none" as the user name.
+#
+#sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-none}}"
+
+sql_user_name = "%{User-Name}"
+
+#######################################################################
+# 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"
+
+#######################################################################
+# Open Query
+#######################################################################
+# This query is run whenever a new connection is opened.
+# It is commented out by default.
+#
+# If you have issues with connections hanging for too long, uncomment
+# the next line, and set the timeout in milliseconds. As a general
+# rule, if the queries take longer than a second, something is wrong
+# with the database.
+#open_query = "set statement_timeout to 1000"
+
+#######################################################################
+# 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 insensitive usernames. WARNING: Slower queries!
+#
+#authorize_check_query = "\
+# SELECT id, UserName, Attribute, Value, Op \
+# FROM ${authcheck_table} \
+# WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
+# ORDER BY id"
+
+#authorize_reply_query = "\
+# SELECT id, UserName, Attribute, Value, Op \
+# FROM ${authreply_table} \
+# WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
+# ORDER BY id"
+
+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 insensitive usernames. WARNING: Slower queries!
+#
+#authorize_group_check_query = "\
+# SELECT \
+# ${groupcheck_table}.id, ${groupcheck_table}.GroupName, ${groupcheck_table}.Attribute, \
+# ${groupcheck_table}.Value, ${groupcheck_table}.Op \
+# FROM ${groupcheck_table}, ${usergroup_table} \
+# WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') \
+# AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
+# ORDER BY ${groupcheck_table}.id"
+
+#authorize_group_reply_query = "\
+# SELECT \
+# ${groupreply_table}.id, ${groupreply_table}.GroupName, \
+# ${groupreply_table}.Attribute, ${groupreply_table}.Value, ${groupreply_table}.Op \
+# FROM ${groupreply_table}, ${usergroup_table} \
+# WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') \
+# AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
+# ORDER BY ${groupreply_table}.id"
+
+authorize_group_check_query = "\
+ SELECT id, GroupName, Attribute, Value, op \
+ FROM ${groupcheck_table} \
+ WHERE GroupName = '%{Sql-Group}' \
+ ORDER BY id"
+
+authorize_group_reply_query = "\
+ SELECT id, GroupName, Attribute, Value, op \
+ FROM ${groupreply_table} \
+ WHERE GroupName = '%{Sql-Group}' \
+ 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.
+#######################################################################
+
+#
+# Uncomment simul_count_query to enable simultaneous use checking
+#
+#simul_count_query = "\
+# SELECT COUNT(*) \
+# FROM ${acct_table1} \
+# WHERE UserName='%{SQL-User-Name}' \
+# AND AcctStopTime IS NULL"
+
+#simul_verify_query = "\
+# SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, \
+# FramedProtocol \
+# FROM ${acct_table1} \
+# WHERE UserName='%{SQL-User-Name}' \
+# AND AcctStopTime IS NULL"
+
+#######################################################################
+# Group Membership Queries
+#######################################################################
+# group_membership_query - Check user group membership
+#######################################################################
+
+# Use these for case insensitive usernames. WARNING: Slower queries!
+#group_membership_query = "\
+# SELECT GroupName \
+# FROM ${usergroup_table} \
+# WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
+# ORDER BY priority"
+
+group_membership_query = "\
+ SELECT GroupName \
+ FROM ${usergroup_table} \
+ WHERE UserName='%{SQL-User-Name}' \
+ ORDER BY priority"
+
+#######################################################################
+# 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}:-none}.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"
+
+ type {
+ accounting-on {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStopTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctSessionTime = (%{integer:Event-Timestamp} - EXTRACT(EPOCH FROM(AcctStartTime))), \
+ AcctTerminateCause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}', \
+ WHERE AcctStopTime IS NULL \
+ AND NASIPAddress= '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+ AND AcctStartTime <= '%S'::timestamp"
+ }
+
+ accounting-off {
+ query = "${..accounting-on.query}"
+ }
+
+ start {
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ %{%{NAS-Port}:-NULL}, \
+ '%{NAS-Port-Type}', \
+ TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ NULL, \
+ 0, \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ NULL, \
+ 0, \
+ 0, \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ NULL, \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet)"
+
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStartTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+ AND AcctStopTime IS NULL"
+
+ # and again where we don't have "AND AcctStopTime IS NULL"
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ AcctStartTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ ConnectInfo_start = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}'"
+ }
+
+ interim-update {
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, \
+ AcctInterval = (%{integer:Event-Timestamp} - EXTRACT(EPOCH FROM (COALESCE(AcctUpdateTime, AcctStartTime)))), \
+ AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Output-Octets}:-0}'::bigint) \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress= '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+ AND AcctStopTime IS NULL"
+
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ %{%{NAS-Port}:-NULL}, \
+ '%{NAS-Port-Type}', \
+ TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ NULL, \
+ %{%{Acct-Session-Time}:-NULL}, \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ NULL, \
+ (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ NULL, \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet)"
+ }
+
+ stop {
+ query = "\
+ UPDATE ${....acct_table2} \
+ SET \
+ AcctStopTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
+ (%{integer:Event-Timestamp} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
+ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}' \
+ AND AcctStopTime IS NULL"
+
+ query = "\
+ INSERT INTO ${....acct_table1} \
+ (${...column_list}) \
+ VALUES(\
+ '%{Acct-Session-Id}', \
+ '%{Acct-Unique-Session-Id}', \
+ '%{SQL-User-Name}', \
+ NULLIF('%{Realm}', ''), \
+ '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', \
+ %{%{NAS-Port}:-NULL}, \
+ '%{NAS-Port-Type}', \
+ TO_TIMESTAMP(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
+ TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ NULLIF('%{Acct-Session-Time}', '')::bigint, \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ NULL, \
+ (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '%{Acct-Terminate-Cause}', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ NULLIF('%{Framed-IP-Address}', '')::inet)"
+
+ # and again where we don't have "AND AcctStopTime IS NULL"
+ query = "\
+ UPDATE ${....acct_table2} \
+ SET \
+ AcctStopTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctUpdateTime = TO_TIMESTAMP(%{integer:Event-Timestamp}), \
+ AcctSessionTime = COALESCE(%{%{Acct-Session-Time}:-NULL}, \
+ (%{integer:Event-Timestamp} - EXTRACT(EPOCH FROM(AcctStartTime)))), \
+ AcctInputOctets = (('%{%{Acct-Input-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Input-Octets}:-0}'::bigint), \
+ AcctOutputOctets = (('%{%{Acct-Output-Gigawords}:-0}'::bigint << 32) + \
+ '%{%{Acct-Output-Octets}:-0}'::bigint), \
+ AcctTerminateCause = '%{Acct-Terminate-Cause}', \
+ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
+ ConnectInfo_stop = '%{Connect-Info}' \
+ WHERE AcctSessionId = '%{Acct-Session-Id}' \
+ AND UserName = '%{SQL-User-Name}' \
+ AND NASIPAddress = '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}'"
+ }
+
+ #
+ # No Acct-Status-Type == ignore the packet
+ #
+ none {
+ 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) \
+ VALUES(\
+ '%{User-Name}', \
+ '%{%{User-Password}:-Chap-Password}', \
+ '%{reply:Packet-Type}', \
+ NOW())"
+}
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/schema.sql
new file mode 100644
index 0000000..5d7b439
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/schema.sql
@@ -0,0 +1,178 @@
+/*
+ * $Id: 5ab9d29306cbef460fe310aafd5d2046267cbbdc $
+ *
+ * Postgresql schema for FreeRADIUS
+ *
+ * All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13
+ *
+ */
+
+/*
+ * Table structure for table 'radacct'
+ *
+ * Note: Column type bigserial does not exist prior to Postgres 7.2
+ * If you run an older version you need to change this to serial
+ */
+CREATE TABLE radacct (
+ RadAcctId bigserial PRIMARY KEY,
+ AcctSessionId text NOT NULL,
+ AcctUniqueId text NOT NULL UNIQUE,
+ UserName text,
+ GroupName text,
+ Realm text,
+ NASIPAddress inet NOT NULL,
+ NASPortId text,
+ NASPortType text,
+ AcctStartTime timestamp with time zone,
+ AcctUpdateTime timestamp with time zone,
+ AcctStopTime timestamp with time zone,
+ AcctInterval bigint,
+ AcctSessionTime bigint,
+ AcctAuthentic text,
+ ConnectInfo_start text,
+ ConnectInfo_stop text,
+ AcctInputOctets bigint,
+ AcctOutputOctets bigint,
+ CalledStationId text,
+ CallingStationId text,
+ AcctTerminateCause text,
+ ServiceType text,
+ FramedProtocol text,
+ FramedIPAddress inet
+);
+-- This index may be useful..
+-- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
+
+-- For use by update-, stop- and simul_* queries
+CREATE INDEX radacct_active_user_idx ON radacct (AcctSessionId, UserName, NASIPAddress) WHERE AcctStopTime IS NULL;
+
+-- For use by onoff-
+create INDEX radacct_bulk_close ON radacct (NASIPAddress, AcctStartTime) WHERE AcctStopTime IS NULL;
+
+-- and for common statistic queries:
+CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
+
+-- and, optionally
+-- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
+
+/*
+ * There was WAAAY too many indexes previously. This combo index
+ * should take care of the most common searches.
+ * I have commented out all the old indexes, but left them in case
+ * someone wants them. I don't recomend anywone use them all at once
+ * as they will slow down your DB too much.
+ * - pnixon 2003-07-13
+ */
+
+/*
+ * create index radacct_UserName on radacct (UserName);
+ * create index radacct_AcctSessionId on radacct (AcctSessionId);
+ * create index radacct_AcctUniqueId on radacct (AcctUniqueId);
+ * create index radacct_FramedIPAddress on radacct (FramedIPAddress);
+ * create index radacct_NASIPAddress on radacct (NASIPAddress);
+ * create index radacct_AcctStartTime on radacct (AcctStartTime);
+ * create index radacct_AcctStopTime on radacct (AcctStopTime);
+*/
+
+
+
+/*
+ * Table structure for table 'radcheck'
+ */
+CREATE TABLE radcheck (
+ id serial PRIMARY KEY,
+ UserName text NOT NULL DEFAULT '',
+ Attribute text NOT NULL DEFAULT '',
+ op VARCHAR(2) NOT NULL DEFAULT '==',
+ Value text NOT NULL DEFAULT ''
+);
+create index radcheck_UserName on radcheck (UserName,Attribute);
+/*
+ * Use this index if you use case insensitive queries
+ */
+-- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
+
+/*
+ * Table structure for table 'radgroupcheck'
+ */
+CREATE TABLE radgroupcheck (
+ id serial PRIMARY KEY,
+ GroupName text NOT NULL DEFAULT '',
+ Attribute text NOT NULL DEFAULT '',
+ op VARCHAR(2) NOT NULL DEFAULT '==',
+ Value text NOT NULL DEFAULT ''
+);
+create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
+
+/*
+ * Table structure for table 'radgroupreply'
+ */
+CREATE TABLE radgroupreply (
+ id serial PRIMARY KEY,
+ GroupName text NOT NULL DEFAULT '',
+ Attribute text NOT NULL DEFAULT '',
+ op VARCHAR(2) NOT NULL DEFAULT '=',
+ Value text NOT NULL DEFAULT ''
+);
+create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
+
+/*
+ * Table structure for table 'radreply'
+ */
+CREATE TABLE radreply (
+ id serial PRIMARY KEY,
+ UserName text NOT NULL DEFAULT '',
+ Attribute text NOT NULL DEFAULT '',
+ op VARCHAR(2) NOT NULL DEFAULT '=',
+ Value text NOT NULL DEFAULT ''
+);
+create index radreply_UserName on radreply (UserName,Attribute);
+/*
+ * Use this index if you use case insensitive queries
+ */
+-- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
+
+/*
+ * Table structure for table 'radusergroup'
+ */
+CREATE TABLE radusergroup (
+ id serial PRIMARY KEY,
+ UserName text NOT NULL DEFAULT '',
+ GroupName text NOT NULL DEFAULT '',
+ priority integer NOT NULL DEFAULT 0
+);
+create index radusergroup_UserName on radusergroup (UserName);
+/*
+ * Use this index if you use case insensitive queries
+ */
+-- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
+
+--
+-- Table structure for table 'radpostauth'
+--
+
+CREATE TABLE radpostauth (
+ id bigserial PRIMARY KEY,
+ username text NOT NULL,
+ pass text,
+ reply text,
+ CalledStationId text,
+ CallingStationId text,
+ authdate timestamp with time zone NOT NULL default now()
+);
+
+/*
+ * Table structure for table 'nas'
+ */
+CREATE TABLE nas (
+ id serial PRIMARY KEY,
+ nasname text NOT NULL,
+ shortname text NOT NULL,
+ type text NOT NULL DEFAULT 'other',
+ ports integer,
+ secret text NOT NULL,
+ server text,
+ community text,
+ description text
+);
+create index nas_nasname on nas (nasname);
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/setup.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/setup.sql
new file mode 100644
index 0000000..17157d0
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/postgresql/setup.sql
@@ -0,0 +1,32 @@
+/*
+ * admin.sql -- PostgreSQL commands for creating the RADIUS user.
+ *
+ * WARNING: You should change 'localhost' and 'radpass'
+ * to something else. Also update raddb/sql.conf
+ * with the new RADIUS password.
+ *
+ * WARNING: This example file is untested. Use at your own risk.
+ * Please send any bug fixes to the mailing list.
+ *
+ * $Id: 26d08cae41c788321bdf8fd1b0c41a443b2da6f4 $
+ */
+
+/*
+ * Create default administrator for RADIUS
+ */
+CREATE USER radius WITH PASSWORD 'radpass';
+
+/*
+ * The server can read any table in SQL
+ */
+GRANT SELECT ON radcheck TO radius;
+GRANT SELECT ON radreply TO radius;
+GRANT SELECT ON radgroupcheck TO radius;
+GRANT SELECT ON radgroupreply TO radius;
+GRANT SELECT ON radusergroup TO radius;
+
+/*
+ * The server can write to the accounting and post-auth logging table.
+ */
+GRANT SELECT, INSERT, UPDATE on radacct TO radius;
+GRANT SELECT, INSERT, UPDATE on radpostauth TO radius;
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/sqlite/queries.conf b/src/test/setup/radius-config/freeradius/mods-config/sql/main/sqlite/queries.conf
new file mode 100644
index 0000000..c91f543
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/sqlite/queries.conf
@@ -0,0 +1,397 @@
+# -*- 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}"
+
+#######################################################################
+# 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 = '%{Sql-Group}' \
+ ORDER BY id"
+
+authorize_group_reply_query = "\
+ SELECT id, groupname, attribute, \
+ value, op \
+ FROM ${groupreply_table} \
+ WHERE groupname = '%{Sql-Group}' \
+ ORDER BY id"
+
+#######################################################################
+# Simultaneous Use Checking Queries
+#######################################################################
+# simul_count_query - query for the number of current connections
+# - If this is not defined, no simultaneouls 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.
+#######################################################################
+
+#
+# Uncomment simul_count_query to enable simultaneous use checking
+#
+#simul_count_query = "\
+# SELECT COUNT(*) \
+# FROM ${acct_table1} \
+# WHERE username = '%{SQL-User-Name}' \
+# AND acctstoptime IS NULL"
+
+simul_verify_query = "\
+ SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
+ callingstationid, framedprotocol \
+ FROM ${acct_table1} \
+ WHERE username = '%{SQL-User-Name}' \
+ AND acctstoptime 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}.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"
+
+ type {
+ accounting-on {
+ #
+ # Bulk terminate all sessions associated with a given NAS
+ #
+ query = "\
+ UPDATE ${....acct_table1} \
+ SET \
+ acctstoptime = %{%{integer:Event-Timestamp}:-date('now')}, \
+ acctsessiontime = \
+ %{%{integer:Event-Timestamp}:-strftime('%s', 'now')} \
+ - strftime('%s', acctstarttime)), \
+ acctterminatecause = '%{Acct-Terminate-Cause}' \
+ WHERE acctstoptime IS NULL \
+ AND nasipaddress = '%{NAS-IP-Address}' \
+ AND acctstarttime <= %{integer: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}', \
+ '%{NAS-Port-Type}', \
+ %{%{integer:Event-Timestamp}:-date('now')}, \
+ %{%{integer:Event-Timestamp}:-date('now')}, \
+ NULL, \
+ '0', \
+ '%{Acct-Authentic}', \
+ '%{Connect-Info}', \
+ '', \
+ '0', \
+ '0', \
+ '%{Called-Station-Id}', \
+ '%{Calling-Station-Id}', \
+ '', \
+ '%{Service-Type}', \
+ '%{Framed-Protocol}', \
+ '%{Framed-IP-Address}')"
+
+ #
+ # Key constraints prevented us from inserting a new session,
+ # use the alternate query to update an existing session.
+ #
+ query = "\
+ UPDATE ${....acct_table1} SET \
+ acctstarttime = %{%{integer:Event-Timestamp}:-date('now')}, \
+ acctupdatetime = %{%{integer:Event-Timestamp}:-date('now'))}, \
+ connectinfo_start = '%{Connect-Info}' \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
+ }
+
+ 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 = %{%{integer:Event-Timestamp}:-date('now')}, \
+ acctinterval = 0, \
+ framedipaddress = '%{Framed-IP-Address}', \
+ acctsessiontime = '%{Acct-Session-Time}', \
+ acctinputoctets = %{%{Acct-Input-Gigawords}:-0} \
+ << 32 | %{%{Acct-Input-Octets}:-0}, \
+ acctoutputoctets = %{%{Acct-Output-Gigawords}:-0} \
+ << 32 | %{%{Acct-Output-Octets}:-0} \
+ WHERE acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
+
+ #
+ # 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}', \
+ '%{NAS-Port-Type}', \
+ (%{%{integer:Event-Timestamp}:-strftime('%s', 'now')} - %{%{Acct-Session-Time}:-0}), \
+ %{%{integer:Event-Timestamp}:-date('now')}, \
+ NULL, \
+ '%{Acct-Session-Time}', \
+ '%{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}')"
+ }
+
+ stop {
+ #
+ # Session has terminated, update the stop time and statistics.
+ #
+ query = "\
+ UPDATE ${....acct_table2} SET \
+ acctstoptime = %{%{integer:Event-Timestamp}:-date('now')}, \
+ 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 acctsessionid = '%{Acct-Session-Id}' \
+ AND username = '%{SQL-User-Name}' \
+ AND nasipaddress = '%{NAS-IP-Address}'"
+
+ #
+ # 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}', \
+ '%{NAS-Port-Type}', \
+ %{%{integer:Event-Timestamp}:-strftime('%s', 'now')} - %{%{Acct-Session-Time}:-0}), \
+ %{%{integer:Event-Timestamp}:-date('now')}, \
+ %{%{integer:Event-Timestamp}:-date('now')}, \
+ '%{Acct-Session-Time}', \
+ '%{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}')"
+ }
+ }
+}
+
+#######################################################################
+# 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) \
+ VALUES ( \
+ '%{SQL-User-Name}', \
+ '%{%{User-Password}:-%{Chap-Password}}', \
+ '%{reply:Packet-Type}', \
+ %{%{integer:Event-Timestamp}:-date('now')})"
+}
diff --git a/src/test/setup/radius-config/freeradius/mods-config/sql/main/sqlite/schema.sql b/src/test/setup/radius-config/freeradius/mods-config/sql/main/sqlite/schema.sql
new file mode 100644
index 0000000..c2a671e
--- /dev/null
+++ b/src/test/setup/radius-config/freeradius/mods-config/sql/main/sqlite/schema.sql
@@ -0,0 +1,137 @@
+-----------------------------------------------------------------------------
+-- $Id: 83a455e620e5ac9603c659697ce9c756c9ccddb1 $ --
+-- --
+-- schema.sql rlm_sql - FreeRADIUS SQLite Module --
+-- --
+-- Database schema for SQLite rlm_sql module --
+-- --
+-- To load: --
+-- mysql -uroot -prootpass radius < schema.sql --
+-- --
+-----------------------------------------------------------------------------
+
+--
+-- Table structure for table 'radacct'
+--
+CREATE TABLE radacct (
+ radacctid bigint(21) PRIMARY KEY,
+ acctsessionid varchar(64) NOT NULL default '',
+ acctuniqueid varchar(32) NOT NULL default '',
+ username varchar(64) NOT NULL default '',
+ groupname varchar(64) NOT NULL default '',
+ realm varchar(64) default '',
+ nasipaddress varchar(15) NOT NULL default '',
+ nasportid varchar(15) 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(50) default NULL,
+ connectinfo_stop varchar(50) 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 ''
+);
+
+CREATE UNIQUE INDEX acctuniqueid ON radacct(acctuniqueid);
+CREATE INDEX username ON radacct(username);
+CREATE INDEX framedipaddress ON radacct (framedipaddress);
+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);
+
+--
+-- Table structure for table 'radcheck'
+--
+CREATE TABLE radcheck (
+ id int(11) PRIMARY KEY,
+ 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 radgroupcheck (
+ id int(11) PRIMARY KEY,
+ 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 radgroupreply (
+ id int(11) PRIMARY KEY,
+ 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 radreply (
+ id int(11) PRIMARY KEY,
+ 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 radusergroup (
+ 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 radpostauth (
+ id int(11) PRIMARY KEY,
+ username varchar(64) NOT NULL default '',
+ pass varchar(64) NOT NULL default '',
+ reply varchar(32) NOT NULL default '',
+ authdate timestamp NOT NULL
+);
+
+--
+-- Table structure for table 'nas'
+--
+CREATE TABLE nas (
+ id int(11) PRIMARY KEY,
+ 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);