3 ## dialup.conf -- MySQL configuration for default schema (schema.sql)
7 # Safe characters list for sql queries. Everything else is replaced
8 # with their mime-encoded equivalents.
9 # The default list should be ok
10 #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
12 #######################################################################
13 # Query config: Username
14 #######################################################################
15 # This is the username that will get substituted, escaped, and added
16 # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
17 # everywhere a username substitution is needed so you you can be sure
18 # the username passed from the client is escaped properly.
20 # Uncomment the next line, if you want the sql_user_name to mean:
22 # Use Stripped-User-Name, if it's there.
23 # Else use User-Name, if it's there,
24 # Else use hard-coded string "DEFAULT" as the user name.
25 #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}"
27 sql_user_name = "%{User-Name}"
29 #######################################################################
31 #######################################################################
32 # This is the default profile. It is found in SQL by group membership.
33 # That means that this profile must be a member of at least one group
34 # which will contain the corresponding check and reply items.
35 # This profile will be queried in the authorize section for every user.
36 # The point is to assign all users a default profile without having to
37 # manually add each one to a group that will contain the profile.
38 # The SQL module will also honor the User-Profile attribute. This
39 # attribute can be set anywhere in the authorize section (ie the users
40 # file). It is found exactly as the default profile is found.
41 # If it is set then it will *overwrite* the default profile setting.
42 # The idea is to select profiles based on checks on the incoming packets,
43 # not on user group membership. For example:
45 # DEFAULT Service-Type == Outbound-User, User-Profile := "outbound"
46 # DEFAULT Service-Type == Framed-User, User-Profile := "framed"
48 # By default the default_user_profile is not set
50 #default_user_profile = "DEFAULT"
52 #######################################################################
54 #######################################################################
55 # This query retrieves the radius clients
57 # 0. Row ID (currently unused)
58 # 1. Name (or IP address)
62 #######################################################################
64 nas_query = "SELECT id, nasname, shortname, type, secret FROM ${nas_table}"
66 #######################################################################
67 # Authorization Queries
68 #######################################################################
69 # These queries compare the check items for the user
70 # in ${authcheck_table} and setup the reply items in
71 # ${authreply_table}. You can use any query/tables
72 # you want, but the return data for each row MUST
73 # be in the following order:
75 # 0. Row ID (currently unused)
76 # 1. UserName/GroupName
79 # 4. Item Attr Operation
80 #######################################################################
81 # Use these for case sensitive usernames.
82 # authorize_check_query = "SELECT id, username, attribute, value, op \
83 # FROM ${authcheck_table} \
84 # WHERE username = BINARY '%{SQL-User-Name}' \
86 # authorize_reply_query = "SELECT id, username, attribute, value, op \
87 # FROM ${authreply_table} \
88 # WHERE username = BINARY '%{SQL-User-Name}' \
91 # The default queries are case insensitive. (for compatibility with
92 # older versions of FreeRADIUS)
93 authorize_check_query = "SELECT id, username, attribute, value, op \
94 FROM ${authcheck_table} \
95 WHERE username = '%{SQL-User-Name}' \
97 authorize_reply_query = "SELECT id, username, attribute, value, op \
98 FROM ${authreply_table} \
99 WHERE username = '%{SQL-User-Name}' \
102 # Use these for case sensitive usernames.
103 # group_membership_query = "SELECT groupname \
104 # FROM ${usergroup_table} \
105 # WHERE username = BINARY '%{SQL-User-Name}' \
108 group_membership_query = "SELECT groupname \
109 FROM ${usergroup_table} \
110 WHERE username = '%{SQL-User-Name}' \
113 authorize_group_check_query = "SELECT id, groupname, attribute, \
115 FROM ${groupcheck_table} \
116 WHERE groupname = '%{Sql-Group}' \
118 authorize_group_reply_query = "SELECT id, groupname, attribute, \
120 FROM ${groupreply_table} \
121 WHERE groupname = '%{Sql-Group}' \
124 #######################################################################
126 #######################################################################
127 # accounting_onoff_query - query for Accounting On/Off packets
128 # accounting_update_query - query for Accounting update packets
129 # accounting_update_query_alt - query for Accounting update packets
130 # (alternate in case first query fails)
131 # accounting_start_query - query for Accounting start packets
132 # accounting_start_query_alt - query for Accounting start packets
133 # (alternate in case first query fails)
134 # accounting_stop_query - query for Accounting stop packets
135 # accounting_stop_query_alt - query for Accounting start packets
136 # (alternate in case first query doesn't
137 # affect any existing rows in the table)
138 #######################################################################
139 accounting_onoff_query = "\
140 UPDATE ${acct_table1} \
142 acctstoptime = '%S', \
143 acctsessiontime = unix_timestamp('%S') - \
144 unix_timestamp(acctstarttime), \
145 acctterminatecause = '%{Acct-Terminate-Cause}', \
146 acctstopdelay = %{%{Acct-Delay-Time}:-0} \
147 WHERE acctsessiontime = 0 \
148 AND acctstoptime = NULL \
149 AND nasipaddress = '%{NAS-IP-Address}' \
150 AND acctstarttime <= '%S'"
152 accounting_update_query = " \
153 UPDATE ${acct_table1} \
155 framedipaddress = '%{Framed-IP-Address}', \
156 acctsessiontime = '%{Acct-Session-Time}', \
157 acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
158 '%{%{Acct-Input-Octets}:-0}', \
159 acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
160 '%{%{Acct-Output-Octets}:-0}' \
161 WHERE acctsessionid = '%{Acct-Session-Id}' \
162 AND username = '%{SQL-User-Name}' \
163 AND nasipaddress = '%{NAS-IP-Address}'"
165 accounting_update_query_alt = " \
166 INSERT INTO ${acct_table1} \
167 (acctsessionid, acctuniqueid, username, \
168 realm, nasipaddress, nasportid, \
169 nasporttype, acctstarttime, acctsessiontime, \
170 acctauthentic, connectinfo_start, acctinputoctets, \
171 acctoutputoctets, calledstationid, callingstationid, \
172 servicetype, framedprotocol, framedipaddress, \
173 acctstartdelay, xascendsessionsvrkey) \
175 ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
176 '%{SQL-User-Name}', \
177 '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
178 '%{NAS-Port-Type}', \
180 INTERVAL (%{%{Acct-Session-Time}:-0} + \
181 %{%{Acct-Delay-Time}:-0}) SECOND), \
182 '%{Acct-Session-Time}', \
183 '%{Acct-Authentic}', '', \
184 '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
185 '%{%{Acct-Input-Octets}:-0}', \
186 '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
187 '%{%{Acct-Output-Octets}:-0}', \
188 '%{Called-Station-Id}', '%{Calling-Station-Id}', \
189 '%{Service-Type}', '%{Framed-Protocol}', \
190 '%{Framed-IP-Address}', \
191 '0', '%{X-Ascend-Session-Svr-Key}')"
193 accounting_start_query = " \
194 INSERT INTO ${acct_table1} \
195 (acctsessionid, acctuniqueid, username, \
196 realm, nasipaddress, nasportid, \
197 nasporttype, acctstarttime, acctstoptime, \
198 acctsessiontime, acctauthentic, connectinfo_start, \
199 connectinfo_stop, acctinputoctets, acctoutputoctets, \
200 calledstationid, callingstationid, acctterminatecause, \
201 servicetype, framedprotocol, framedipaddress, \
202 acctstartdelay, acctstopdelay, xascendsessionsvrkey) \
204 ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
205 '%{SQL-User-Name}', \
206 '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
207 '%{NAS-Port-Type}', '%S', NULL, \
208 '0', '%{Acct-Authentic}', '%{Connect-Info}', \
210 '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
211 '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
212 '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
214 accounting_start_query_alt = " \
215 UPDATE ${acct_table1} SET \
216 acctstarttime = '%S', \
217 acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \
218 connectinfo_start = '%{Connect-Info}' \
219 WHERE acctsessionid = '%{Acct-Session-Id}' \
220 AND username = '%{SQL-User-Name}' \
221 AND nasipaddress = '%{NAS-IP-Address}'"
223 accounting_stop_query = " \
224 UPDATE ${acct_table2} SET \
225 acctstoptime = '%S', \
226 acctsessiontime = '%{Acct-Session-Time}', \
227 acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
228 '%{%{Acct-Input-Octets}:-0}', \
229 acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
230 '%{%{Acct-Output-Octets}:-0}', \
231 acctterminatecause = '%{Acct-Terminate-Cause}', \
232 acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \
233 connectinfo_stop = '%{Connect-Info}' \
234 WHERE acctsessionid = '%{Acct-Session-Id}' \
235 AND username = '%{SQL-User-Name}' \
236 AND nasipaddress = '%{NAS-IP-Address}'"
238 accounting_stop_query_alt = " \
239 INSERT INTO ${acct_table2} \
240 (acctsessionid, acctuniqueid, username, \
241 realm, nasipaddress, nasportid, \
242 nasporttype, acctstarttime, acctstoptime, \
243 acctsessiontime, acctauthentic, connectinfo_start, \
244 connectinfo_stop, acctinputoctets, acctoutputoctets, \
245 calledstationid, callingstationid, acctterminatecause, \
246 servicetype, framedprotocol, framedipaddress, \
247 acctstartdelay, acctstopdelay) \
249 ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
250 '%{SQL-User-Name}', \
251 '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
252 '%{NAS-Port-Type}', \
254 INTERVAL (%{%{Acct-Session-Time}:-0} + \
255 %{%{Acct-Delay-Time}:-0}) SECOND), \
256 '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
258 '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
259 '%{%{Acct-Input-Octets}:-0}', \
260 '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
261 '%{%{Acct-Output-Octets}:-0}', \
262 '%{Called-Station-Id}', '%{Calling-Station-Id}', \
263 '%{Acct-Terminate-Cause}', \
264 '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
265 '0', '%{%{Acct-Delay-Time}:-0}')"
267 #######################################################################
268 # Simultaneous Use Checking Queries
269 #######################################################################
270 # simul_count_query - query for the number of current connections
271 # - If this is not defined, no simultaneouls use checking
272 # - will be performed by this module instance
273 # simul_verify_query - query to return details of current connections for verification
274 # - Leave blank or commented out to disable verification step
275 # - Note that the returned field order should not be changed.
276 #######################################################################
278 # Uncomment simul_count_query to enable simultaneous use checking
279 #simul_count_query = "SELECT COUNT(*) \
280 #FROM ${acct_table1} \
281 #WHERE username = '%{SQL-User-Name}' \
282 #AND acctstoptime = NULL"
284 simul_verify_query = "SELECT radacctid, acctsessionid, username, \
285 nasipaddress, nasportid, framedipaddress, \
286 callingstationid, framedprotocol \
287 FROM ${acct_table1} \
288 WHERE username = '%{SQL-User-Name}' \
289 AND acctstoptime = NULL"
291 #######################################################################
292 # Authentication Logging Queries
293 #######################################################################
294 # postauth_query - Insert some info after authentication
295 #######################################################################
297 postauth_query = "INSERT INTO ${postauth_table} \
298 (username, pass, reply, authdate) \
301 '%{%{User-Password}:-%{Chap-Password}}', \
302 '%{reply:Packet-Type}', '%S')"