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 acctstoptime = NULL \
148 AND nasipaddress = '%{NAS-IP-Address}' \
149 AND acctstarttime <= '%S'"
151 accounting_update_query = " \
152 UPDATE ${acct_table1} \
154 framedipaddress = '%{Framed-IP-Address}', \
155 acctsessiontime = '%{Acct-Session-Time}', \
156 acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
157 '%{%{Acct-Input-Octets}:-0}', \
158 acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
159 '%{%{Acct-Output-Octets}:-0}' \
160 WHERE acctsessionid = '%{Acct-Session-Id}' \
161 AND username = '%{SQL-User-Name}' \
162 AND nasipaddress = '%{NAS-IP-Address}'"
164 accounting_update_query_alt = " \
165 INSERT INTO ${acct_table1} \
166 (acctsessionid, acctuniqueid, username, \
167 realm, nasipaddress, nasportid, \
168 nasporttype, acctstarttime, acctsessiontime, \
169 acctauthentic, connectinfo_start, acctinputoctets, \
170 acctoutputoctets, calledstationid, callingstationid, \
171 servicetype, framedprotocol, framedipaddress, \
172 acctstartdelay, xascendsessionsvrkey) \
174 ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
175 '%{SQL-User-Name}', \
176 '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
177 '%{NAS-Port-Type}', \
179 INTERVAL (%{%{Acct-Session-Time}:-0} + \
180 %{%{Acct-Delay-Time}:-0}) SECOND), \
181 '%{Acct-Session-Time}', \
182 '%{Acct-Authentic}', '', \
183 '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
184 '%{%{Acct-Input-Octets}:-0}', \
185 '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
186 '%{%{Acct-Output-Octets}:-0}', \
187 '%{Called-Station-Id}', '%{Calling-Station-Id}', \
188 '%{Service-Type}', '%{Framed-Protocol}', \
189 '%{Framed-IP-Address}', \
190 '0', '%{X-Ascend-Session-Svr-Key}')"
192 accounting_start_query = " \
193 INSERT INTO ${acct_table1} \
194 (acctsessionid, acctuniqueid, username, \
195 realm, nasipaddress, nasportid, \
196 nasporttype, acctstarttime, acctstoptime, \
197 acctsessiontime, acctauthentic, connectinfo_start, \
198 connectinfo_stop, acctinputoctets, acctoutputoctets, \
199 calledstationid, callingstationid, acctterminatecause, \
200 servicetype, framedprotocol, framedipaddress, \
201 acctstartdelay, acctstopdelay, xascendsessionsvrkey) \
203 ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
204 '%{SQL-User-Name}', \
205 '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
206 '%{NAS-Port-Type}', '%S', NULL, \
207 '0', '%{Acct-Authentic}', '%{Connect-Info}', \
209 '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
210 '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
211 '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"
213 accounting_start_query_alt = " \
214 UPDATE ${acct_table1} SET \
215 acctstarttime = '%S', \
216 acctstartdelay = '%{%{Acct-Delay-Time}:-0}', \
217 connectinfo_start = '%{Connect-Info}' \
218 WHERE acctsessionid = '%{Acct-Session-Id}' \
219 AND username = '%{SQL-User-Name}' \
220 AND nasipaddress = '%{NAS-IP-Address}'"
222 accounting_stop_query = " \
223 UPDATE ${acct_table2} SET \
224 acctstoptime = '%S', \
225 acctsessiontime = '%{Acct-Session-Time}', \
226 acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
227 '%{%{Acct-Input-Octets}:-0}', \
228 acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
229 '%{%{Acct-Output-Octets}:-0}', \
230 acctterminatecause = '%{Acct-Terminate-Cause}', \
231 acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \
232 connectinfo_stop = '%{Connect-Info}' \
233 WHERE acctsessionid = '%{Acct-Session-Id}' \
234 AND username = '%{SQL-User-Name}' \
235 AND nasipaddress = '%{NAS-IP-Address}'"
237 accounting_stop_query_alt = " \
238 INSERT INTO ${acct_table2} \
239 (acctsessionid, acctuniqueid, username, \
240 realm, nasipaddress, nasportid, \
241 nasporttype, acctstarttime, acctstoptime, \
242 acctsessiontime, acctauthentic, connectinfo_start, \
243 connectinfo_stop, acctinputoctets, acctoutputoctets, \
244 calledstationid, callingstationid, acctterminatecause, \
245 servicetype, framedprotocol, framedipaddress, \
246 acctstartdelay, acctstopdelay) \
248 ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
249 '%{SQL-User-Name}', \
250 '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
251 '%{NAS-Port-Type}', \
253 INTERVAL (%{%{Acct-Session-Time}:-0} + \
254 %{%{Acct-Delay-Time}:-0}) SECOND), \
255 '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
257 '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
258 '%{%{Acct-Input-Octets}:-0}', \
259 '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
260 '%{%{Acct-Output-Octets}:-0}', \
261 '%{Called-Station-Id}', '%{Calling-Station-Id}', \
262 '%{Acct-Terminate-Cause}', \
263 '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
264 '0', '%{%{Acct-Delay-Time}:-0}')"
266 #######################################################################
267 # Simultaneous Use Checking Queries
268 #######################################################################
269 # simul_count_query - query for the number of current connections
270 # - If this is not defined, no simultaneouls use checking
271 # - will be performed by this module instance
272 # simul_verify_query - query to return details of current connections for verification
273 # - Leave blank or commented out to disable verification step
274 # - Note that the returned field order should not be changed.
275 #######################################################################
277 # Uncomment simul_count_query to enable simultaneous use checking
278 #simul_count_query = "SELECT COUNT(*) \
279 #FROM ${acct_table1} \
280 #WHERE username = '%{SQL-User-Name}' \
281 #AND acctstoptime = NULL"
283 simul_verify_query = "SELECT radacctid, acctsessionid, username, \
284 nasipaddress, nasportid, framedipaddress, \
285 callingstationid, framedprotocol \
286 FROM ${acct_table1} \
287 WHERE username = '%{SQL-User-Name}' \
288 AND acctstoptime = NULL"
290 #######################################################################
291 # Authentication Logging Queries
292 #######################################################################
293 # postauth_query - Insert some info after authentication
294 #######################################################################
296 postauth_query = "INSERT INTO ${postauth_table} \
297 (username, pass, reply, authdate) \
300 '%{%{User-Password}:-%{Chap-Password}}', \
301 '%{reply:Packet-Type}', '%S')"