WebSVN – ALCASAR – Diff – /conf/radius/queries.conf



# -*- text -*-
##
## query.conf -- MySQL configuration for default schema (schema.sql)
##
 
 
	# Safe characters list for sql queries. Everything else is replaced
	# with their mime-encoded equivalents.
	# The default list should be ok (define in 'expr' module
	#safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"

             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        = BINARY '%{SQL-User-Name}' \
          AND nasipaddress    = '%{NAS-IP-Address}'"
 
        accounting_update_query_alt = " \
          INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,      username, \

             acctoutputoctets, calledstationid,   callingstationid, \
             servicetype,      framedprotocol,    framedipaddress, \
             acctstartdelay,   xascendsessionsvrkey) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             BINARY '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \
             DATE_SUB('%S', \
                      INTERVAL (%{%{Acct-Session-Time}:-0} + \
                                %{%{Acct-Delay-Time}:-0}) SECOND), \

             calledstationid,  callingstationid, acctterminatecause, \
             servicetype,      framedprotocol,   framedipaddress, \
             acctstartdelay,   acctstopdelay,    xascendsessionsvrkey) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             BINARY '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', '%S', NULL, \
             '0', '%{Acct-Authentic}', '%{Connect-Info}', \
             '', '0', '0', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \

          UPDATE ${acct_table1} SET \
             acctstarttime     = '%S', \
             acctstartdelay    = '%{%{Acct-Delay-Time}:-0}', \
             connectinfo_start = '%{Connect-Info}' \
          WHERE acctsessionid  = '%{Acct-Session-Id}' \
          AND username         = BINARY '%{SQL-User-Name}' \
          AND nasipaddress     = '%{NAS-IP-Address}'"
 
	accounting_stop_query = " \
          UPDATE ${acct_table2} SET \
             acctstoptime       = '%S', \

                                  '%{%{Acct-Output-Octets}:-0}', \
             acctterminatecause = '%{Acct-Terminate-Cause}', \
             acctstopdelay      = '%{%{Acct-Delay-Time}:-0}', \
             connectinfo_stop   = '%{Connect-Info}' \
          WHERE acctsessionid   = '%{Acct-Session-Id}' \
          AND username          = BINARY '%{SQL-User-Name}' \
          AND nasipaddress      = '%{NAS-IP-Address}'"
 
	accounting_stop_query_alt = " \
          INSERT INTO ${acct_table2} \
            (acctsessionid, acctuniqueid, username, \

             calledstationid, callingstationid, acctterminatecause, \
             servicetype, framedprotocol, framedipaddress, \
             acctstartdelay, acctstopdelay) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             BINARY '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \
             DATE_SUB('%S', \
                 INTERVAL (%{%{Acct-Session-Time}:-0} + \
                 %{%{Acct-Delay-Time}:-0}) SECOND), \

	#######################################################################
 
	# Uncomment simul_count_query to enable simultaneous use checking
	simul_count_query = "SELECT COUNT(*) \
                             FROM ${acct_table1} \
                             WHERE username = BINARY '%{SQL-User-Name}' \
                             AND acctstoptime IS NULL"
 
	#simul_verify_query  = "SELECT radacctid, acctsessionid, username, \
                               #nasipaddress, nasportid, framedipaddress, \
                               #callingstationid, framedprotocol \

#                          '%{%{User-Password}:-%{Chap-Password}}', \
 
	postauth_query = "INSERT INTO ${postauth_table} \
                          (username, reply, authdate) \
                          VALUES ( \
                          BINARY '%{User-Name}', \
                          '%{reply:Packet-Type}', '%S')"
# We add the following patch (freeradius V2 to V3) in order ALCASAR works
post-auth {
	query = "${..postauth_query}"
}

Subversion Repositories ALCASAR

(root)/conf/radius/queries.conf @ 2706 – Rev 2438 → 2476

Rev 2438		Rev 2476
Line 1...		Line 1...
1	`# -- text --`	1	`# -- text --`
2	`##`	2	`##`
3	`## query.conf -- MySQL configuration for default schema (schema.sql)`	3	`## query.conf -- MySQL configuration for default schema (schema.sql)`
4	`##`	4	`##`
5	`## $Id: dialup.conf 983 2012-08-16 13:34:14Z franck $`	-
6		5
7	`# Safe characters list for sql queries. Everything else is replaced`	6	`# Safe characters list for sql queries. Everything else is replaced`
8	`# with their mime-encoded equivalents.`	7	`# with their mime-encoded equivalents.`
9	`# The default list should be ok (define in 'expr' module`	8	`# The default list should be ok (define in 'expr' module`
10	`#safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"`	9	`#safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"`
Line 156...		Line 155...
156	`acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 \| \`	155	`acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 \| \`
157	`'%{%{Acct-Input-Octets}:-0}', \`	156	`'%{%{Acct-Input-Octets}:-0}', \`
158	`acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`	157	`acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 \| \`
159	`'%{%{Acct-Output-Octets}:-0}' \`	158	`'%{%{Acct-Output-Octets}:-0}' \`
160	`WHERE acctsessionid = '%{Acct-Session-Id}' \`	159	`WHERE acctsessionid = '%{Acct-Session-Id}' \`
161	`AND username = '%{SQL-User-Name}' \`	160	`AND username = BINARY '%{SQL-User-Name}' \`
162	`AND nasipaddress = '%{NAS-IP-Address}'"`	161	`AND nasipaddress = '%{NAS-IP-Address}'"`
163		162
164	`accounting_update_query_alt = " \`	163	`accounting_update_query_alt = " \`
165	`INSERT INTO ${acct_table1} \`	164	`INSERT INTO ${acct_table1} \`
166	`(acctsessionid, acctuniqueid, username, \`	165	`(acctsessionid, acctuniqueid, username, \`
Line 170...		Line 169...
170	`acctoutputoctets, calledstationid, callingstationid, \`	169	`acctoutputoctets, calledstationid, callingstationid, \`
171	`servicetype, framedprotocol, framedipaddress, \`	170	`servicetype, framedprotocol, framedipaddress, \`
172	`acctstartdelay, xascendsessionsvrkey) \`	171	`acctstartdelay, xascendsessionsvrkey) \`
173	`VALUES \`	172	`VALUES \`
174	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`	173	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`
175	`'%{SQL-User-Name}', \`	174	`BINARY '%{SQL-User-Name}', \`
176	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`	175	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`
177	`'%{NAS-Port-Type}', \`	176	`'%{NAS-Port-Type}', \`
178	`DATE_SUB('%S', \`	177	`DATE_SUB('%S', \`
179	`INTERVAL (%{%{Acct-Session-Time}:-0} + \`	178	`INTERVAL (%{%{Acct-Session-Time}:-0} + \`
180	`%{%{Acct-Delay-Time}:-0}) SECOND), \`	179	`%{%{Acct-Delay-Time}:-0}) SECOND), \`
Line 199...		Line 198...
199	`calledstationid, callingstationid, acctterminatecause, \`	198	`calledstationid, callingstationid, acctterminatecause, \`
200	`servicetype, framedprotocol, framedipaddress, \`	199	`servicetype, framedprotocol, framedipaddress, \`
201	`acctstartdelay, acctstopdelay, xascendsessionsvrkey) \`	200	`acctstartdelay, acctstopdelay, xascendsessionsvrkey) \`
202	`VALUES \`	201	`VALUES \`
203	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`	202	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`
204	`'%{SQL-User-Name}', \`	203	`BINARY '%{SQL-User-Name}', \`
205	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`	204	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`
206	`'%{NAS-Port-Type}', '%S', NULL, \`	205	`'%{NAS-Port-Type}', '%S', NULL, \`
207	`'0', '%{Acct-Authentic}', '%{Connect-Info}', \`	206	`'0', '%{Acct-Authentic}', '%{Connect-Info}', \`
208	`'', '0', '0', \`	207	`'', '0', '0', \`
209	`'%{Called-Station-Id}', '%{Calling-Station-Id}', '', \`	208	`'%{Called-Station-Id}', '%{Calling-Station-Id}', '', \`
Line 214...		Line 213...
214	`UPDATE ${acct_table1} SET \`	213	`UPDATE ${acct_table1} SET \`
215	`acctstarttime = '%S', \`	214	`acctstarttime = '%S', \`
216	`acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \`	215	`acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \`
217	`connectinfo_start = '%{Connect-Info}' \`	216	`connectinfo_start = '%{Connect-Info}' \`
218	`WHERE acctsessionid = '%{Acct-Session-Id}' \`	217	`WHERE acctsessionid = '%{Acct-Session-Id}' \`
219	`AND username = '%{SQL-User-Name}' \`	218	`AND username = BINARY '%{SQL-User-Name}' \`
220	`AND nasipaddress = '%{NAS-IP-Address}'"`	219	`AND nasipaddress = '%{NAS-IP-Address}'"`
221		220
222	`accounting_stop_query = " \`	221	`accounting_stop_query = " \`
223	`UPDATE ${acct_table2} SET \`	222	`UPDATE ${acct_table2} SET \`
224	`acctstoptime = '%S', \`	223	`acctstoptime = '%S', \`
Line 229...		Line 228...
229	`'%{%{Acct-Output-Octets}:-0}', \`	228	`'%{%{Acct-Output-Octets}:-0}', \`
230	`acctterminatecause = '%{Acct-Terminate-Cause}', \`	229	`acctterminatecause = '%{Acct-Terminate-Cause}', \`
231	`acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \`	230	`acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \`
232	`connectinfo_stop = '%{Connect-Info}' \`	231	`connectinfo_stop = '%{Connect-Info}' \`
233	`WHERE acctsessionid = '%{Acct-Session-Id}' \`	232	`WHERE acctsessionid = '%{Acct-Session-Id}' \`
234	`AND username = '%{SQL-User-Name}' \`	233	`AND username = BINARY '%{SQL-User-Name}' \`
235	`AND nasipaddress = '%{NAS-IP-Address}'"`	234	`AND nasipaddress = '%{NAS-IP-Address}'"`
236		235
237	`accounting_stop_query_alt = " \`	236	`accounting_stop_query_alt = " \`
238	`INSERT INTO ${acct_table2} \`	237	`INSERT INTO ${acct_table2} \`
239	`(acctsessionid, acctuniqueid, username, \`	238	`(acctsessionid, acctuniqueid, username, \`
Line 244...		Line 243...
244	`calledstationid, callingstationid, acctterminatecause, \`	243	`calledstationid, callingstationid, acctterminatecause, \`
245	`servicetype, framedprotocol, framedipaddress, \`	244	`servicetype, framedprotocol, framedipaddress, \`
246	`acctstartdelay, acctstopdelay) \`	245	`acctstartdelay, acctstopdelay) \`
247	`VALUES \`	246	`VALUES \`
248	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`	247	`('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \`
249	`'%{SQL-User-Name}', \`	248	`BINARY '%{SQL-User-Name}', \`
250	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`	249	`'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \`
251	`'%{NAS-Port-Type}', \`	250	`'%{NAS-Port-Type}', \`
252	`DATE_SUB('%S', \`	251	`DATE_SUB('%S', \`
253	`INTERVAL (%{%{Acct-Session-Time}:-0} + \`	252	`INTERVAL (%{%{Acct-Session-Time}:-0} + \`
254	`%{%{Acct-Delay-Time}:-0}) SECOND), \`	253	`%{%{Acct-Delay-Time}:-0}) SECOND), \`
Line 299...		Line 298...
299	`#######################################################################`	298	`#######################################################################`
300		299
301	`# Uncomment simul_count_query to enable simultaneous use checking`	300	`# Uncomment simul_count_query to enable simultaneous use checking`
302	`simul_count_query = "SELECT COUNT(*) \`	301	`simul_count_query = "SELECT COUNT(*) \`
303	`FROM ${acct_table1} \`	302	`FROM ${acct_table1} \`
304	`WHERE username = '%{SQL-User-Name}' \`	303	`WHERE username = BINARY '%{SQL-User-Name}' \`
305	`AND acctstoptime IS NULL"`	304	`AND acctstoptime IS NULL"`
306		305
307	`#simul_verify_query = "SELECT radacctid, acctsessionid, username, \`	306	`#simul_verify_query = "SELECT radacctid, acctsessionid, username, \`
308	`#nasipaddress, nasportid, framedipaddress, \`	307	`#nasipaddress, nasportid, framedipaddress, \`
309	`#callingstationid, framedprotocol \`	308	`#callingstationid, framedprotocol \`
Line 320...		Line 319...
320	`# '%{%{User-Password}:-%{Chap-Password}}', \`	319	`# '%{%{User-Password}:-%{Chap-Password}}', \`
321		320
322	`postauth_query = "INSERT INTO ${postauth_table} \`	321	`postauth_query = "INSERT INTO ${postauth_table} \`
323	`(username, reply, authdate) \`	322	`(username, reply, authdate) \`
324	`VALUES ( \`	323	`VALUES ( \`
325	`'%{User-Name}', \`	324	`BINARY '%{User-Name}', \`
326	`'%{reply:Packet-Type}', '%S')"`	325	`'%{reply:Packet-Type}', '%S')"`
327	`# We add the following patch (freeradius V2 to V3) in order ALCASAR works`	326	`# We add the following patch (freeradius V2 to V3) in order ALCASAR works`
328	`post-auth {`	327	`post-auth {`
329	`query = "${..postauth_query}"`	328	`query = "${..postauth_query}"`
330	`}`	329	`}`