Chetan Gaonker | 7f4bf74 | 2016-05-04 15:56:08 -0700 | [diff] [blame] | 1 | # -*- text -*- |
| 2 | # |
| 3 | # main/mysql/queries.conf-- MySQL configuration for default schema (schema.sql) |
| 4 | # |
| 5 | # $Id: 0b3c210d6c0b04350d1a48738764b47f25f51bc4 $ |
| 6 | |
| 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.-_: /" |
| 11 | |
| 12 | ####################################################################### |
| 13 | # Connection config |
| 14 | ####################################################################### |
| 15 | # The character set is not configurable. The default character set of |
| 16 | # the mysql client library is used. To control the character set, |
| 17 | # create/edit my.cnf (typically in /etc/mysql/my.cnf or /etc/my.cnf) |
| 18 | # and enter |
| 19 | # [client] |
| 20 | # default-character-set = utf8 |
| 21 | # |
| 22 | |
| 23 | ####################################################################### |
| 24 | # Query config: Username |
| 25 | ####################################################################### |
| 26 | # This is the username that will get substituted, escaped, and added |
| 27 | # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below |
| 28 | # everywhere a username substitution is needed so you you can be sure |
| 29 | # the username passed from the client is escaped properly. |
| 30 | # |
| 31 | # Uncomment the next line, if you want the sql_user_name to mean: |
| 32 | # |
| 33 | # Use Stripped-User-Name, if it's there. |
| 34 | # Else use User-Name, if it's there, |
| 35 | # Else use hard-coded string "DEFAULT" as the user name. |
| 36 | #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}" |
| 37 | # |
| 38 | sql_user_name = "%{User-Name}" |
| 39 | |
| 40 | ####################################################################### |
| 41 | # Default profile |
| 42 | ####################################################################### |
| 43 | # This is the default profile. It is found in SQL by group membership. |
| 44 | # That means that this profile must be a member of at least one group |
| 45 | # which will contain the corresponding check and reply items. |
| 46 | # This profile will be queried in the authorize section for every user. |
| 47 | # The point is to assign all users a default profile without having to |
| 48 | # manually add each one to a group that will contain the profile. |
| 49 | # The SQL module will also honor the User-Profile attribute. This |
| 50 | # attribute can be set anywhere in the authorize section (ie the users |
| 51 | # file). It is found exactly as the default profile is found. |
| 52 | # If it is set then it will *overwrite* the default profile setting. |
| 53 | # The idea is to select profiles based on checks on the incoming packets, |
| 54 | # not on user group membership. For example: |
| 55 | # -- users file -- |
| 56 | # DEFAULT Service-Type == Outbound-User, User-Profile := "outbound" |
| 57 | # DEFAULT Service-Type == Framed-User, User-Profile := "framed" |
| 58 | # |
| 59 | # By default the default_user_profile is not set |
| 60 | # |
| 61 | #default_user_profile = "DEFAULT" |
| 62 | |
| 63 | ####################################################################### |
| 64 | # NAS Query |
| 65 | ####################################################################### |
| 66 | # This query retrieves the radius clients |
| 67 | # |
| 68 | # 0. Row ID (currently unused) |
| 69 | # 1. Name (or IP address) |
| 70 | # 2. Shortname |
| 71 | # 3. Type |
| 72 | # 4. Secret |
| 73 | # 5. Server |
| 74 | ####################################################################### |
| 75 | |
| 76 | client_query = "\ |
| 77 | SELECT id, nasname, shortname, type, secret, server \ |
| 78 | FROM ${client_table}" |
| 79 | |
| 80 | ####################################################################### |
| 81 | # Authorization Queries |
| 82 | ####################################################################### |
| 83 | # These queries compare the check items for the user |
| 84 | # in ${authcheck_table} and setup the reply items in |
| 85 | # ${authreply_table}. You can use any query/tables |
| 86 | # you want, but the return data for each row MUST |
| 87 | # be in the following order: |
| 88 | # |
| 89 | # 0. Row ID (currently unused) |
| 90 | # 1. UserName/GroupName |
| 91 | # 2. Item Attr Name |
| 92 | # 3. Item Attr Value |
| 93 | # 4. Item Attr Operation |
| 94 | ####################################################################### |
| 95 | # Use these for case sensitive usernames. |
| 96 | |
| 97 | #authorize_check_query = "\ |
| 98 | # SELECT id, username, attribute, value, op \ |
| 99 | # FROM ${authcheck_table} \ |
| 100 | # WHERE username = BINARY '%{SQL-User-Name}' \ |
| 101 | # ORDER BY id" |
| 102 | |
| 103 | #authorize_reply_query = "\ |
| 104 | # SELECT id, username, attribute, value, op \ |
| 105 | # FROM ${authreply_table} \ |
| 106 | # WHERE username = BINARY '%{SQL-User-Name}' \ |
| 107 | # ORDER BY id" |
| 108 | |
| 109 | # |
| 110 | # The default queries are case insensitive. (for compatibility with |
| 111 | # older versions of FreeRADIUS) |
| 112 | # |
| 113 | authorize_check_query = "\ |
| 114 | SELECT id, username, attribute, value, op \ |
| 115 | FROM ${authcheck_table} \ |
| 116 | WHERE username = '%{SQL-User-Name}' \ |
| 117 | ORDER BY id" |
| 118 | |
| 119 | authorize_reply_query = "\ |
| 120 | SELECT id, username, attribute, value, op \ |
| 121 | FROM ${authreply_table} \ |
| 122 | WHERE username = '%{SQL-User-Name}' \ |
| 123 | ORDER BY id" |
| 124 | |
| 125 | # |
| 126 | # Use these for case sensitive usernames. |
| 127 | # |
| 128 | group_membership_query = "\ |
| 129 | # SELECT groupname \ |
| 130 | # FROM ${usergroup_table} \ |
| 131 | # WHERE username = BINARY '%{SQL-User-Name}' \ |
| 132 | # ORDER BY priority" |
| 133 | |
| 134 | group_membership_query = "\ |
| 135 | SELECT groupname \ |
| 136 | FROM ${usergroup_table} \ |
| 137 | WHERE username = '%{SQL-User-Name}' \ |
| 138 | ORDER BY priority" |
| 139 | |
| 140 | authorize_group_check_query = "\ |
| 141 | SELECT id, groupname, attribute, \ |
| 142 | Value, op \ |
| 143 | FROM ${groupcheck_table} \ |
| 144 | WHERE groupname = '%{Sql-Group}' \ |
| 145 | ORDER BY id" |
| 146 | |
| 147 | authorize_group_reply_query = "\ |
| 148 | SELECT id, groupname, attribute, \ |
| 149 | value, op \ |
| 150 | FROM ${groupreply_table} \ |
| 151 | WHERE groupname = '%{Sql-Group}' \ |
| 152 | ORDER BY id" |
| 153 | |
| 154 | ####################################################################### |
| 155 | # Simultaneous Use Checking Queries |
| 156 | ####################################################################### |
| 157 | # simul_count_query - query for the number of current connections |
| 158 | # - If this is not defined, no simultaneouls use checking |
| 159 | # - will be performed by this module instance |
| 160 | # simul_verify_query - query to return details of current connections |
| 161 | # for verification |
| 162 | # - Leave blank or commented out to disable verification step |
| 163 | # - Note that the returned field order should not be changed. |
| 164 | ####################################################################### |
| 165 | |
| 166 | # |
| 167 | # Uncomment simul_count_query to enable simultaneous use checking |
| 168 | # |
| 169 | #simul_count_query = "\ |
| 170 | # SELECT COUNT(*) \ |
| 171 | # FROM ${acct_table1} \ |
| 172 | # WHERE username = '%{SQL-User-Name}' \ |
| 173 | # AND acctstoptime IS NULL" |
| 174 | |
| 175 | simul_verify_query = "\ |
| 176 | SELECT \ |
| 177 | radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \ |
| 178 | callingstationid, framedprotocol \ |
| 179 | FROM ${acct_table1} \ |
| 180 | WHERE username = '%{SQL-User-Name}' \ |
| 181 | AND acctstoptime IS NULL" |
| 182 | |
| 183 | ####################################################################### |
| 184 | # Accounting and Post-Auth Queries |
| 185 | ####################################################################### |
| 186 | # These queries insert/update accounting and authentication records. |
| 187 | # The query to use is determined by the value of 'reference'. |
| 188 | # This value is used as a configuration path and should resolve to one |
| 189 | # or more 'query's. If reference points to multiple queries, and a query |
| 190 | # fails, the next query is executed. |
| 191 | # |
| 192 | # Behaviour is identical to the old 1.x/2.x module, except we can now |
| 193 | # fail between N queries, and query selection can be based on any |
| 194 | # combination of attributes, or custom 'Acct-Status-Type' values. |
| 195 | ####################################################################### |
| 196 | accounting { |
| 197 | reference = "%{tolower:type.%{Acct-Status-Type}.query}" |
| 198 | |
| 199 | # Write SQL queries to a logfile. This is potentially useful for bulk inserts |
| 200 | # when used with the rlm_sql_null driver. |
| 201 | # logfile = ${logdir}/accounting.sql |
| 202 | |
| 203 | column_list = "\ |
| 204 | acctsessionid, acctuniqueid, username, \ |
| 205 | realm, nasipaddress, nasportid, \ |
| 206 | nasporttype, acctstarttime, acctupdatetime, \ |
| 207 | acctstoptime, acctsessiontime, acctauthentic, \ |
| 208 | connectinfo_start, connectinfo_stop, acctinputoctets, \ |
| 209 | acctoutputoctets, calledstationid, callingstationid, \ |
| 210 | acctterminatecause, servicetype, framedprotocol, \ |
| 211 | framedipaddress" |
| 212 | |
| 213 | type { |
| 214 | accounting-on { |
| 215 | # |
| 216 | # Bulk terminate all sessions associated with a given NAS |
| 217 | # |
| 218 | query = "\ |
| 219 | UPDATE ${....acct_table1} \ |
| 220 | SET \ |
| 221 | acctstoptime = FROM_UNIXTIME(\ |
| 222 | %{integer:Event-Timestamp}), \ |
| 223 | acctsessiontime = '%{integer:Event-Timestamp}' \ |
| 224 | - UNIX_TIMESTAMP(acctstarttime), \ |
| 225 | acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \ |
| 226 | WHERE acctstoptime IS NULL \ |
| 227 | AND nasipaddress = '%{NAS-IP-Address}' \ |
| 228 | AND acctstarttime <= FROM_UNIXTIME(\ |
| 229 | %{integer:Event-Timestamp})" |
| 230 | } |
| 231 | |
| 232 | accounting-off { |
| 233 | query = "${..accounting-on.query}" |
| 234 | } |
| 235 | |
| 236 | start { |
| 237 | # |
| 238 | # Insert a new record into the sessions table |
| 239 | # |
| 240 | query = "\ |
| 241 | INSERT INTO ${....acct_table1} \ |
| 242 | (${...column_list}) \ |
| 243 | VALUES \ |
| 244 | ('%{Acct-Session-Id}', \ |
| 245 | '%{Acct-Unique-Session-Id}', \ |
| 246 | '%{SQL-User-Name}', \ |
| 247 | '%{Realm}', \ |
| 248 | '%{NAS-IP-Address}', \ |
| 249 | '%{NAS-Port}', \ |
| 250 | '%{NAS-Port-Type}', \ |
| 251 | FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 252 | FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 253 | NULL, \ |
| 254 | '0', \ |
| 255 | '%{Acct-Authentic}', \ |
| 256 | '%{Connect-Info}', \ |
| 257 | '', \ |
| 258 | '0', \ |
| 259 | '0', \ |
| 260 | '%{Called-Station-Id}', \ |
| 261 | '%{Calling-Station-Id}', \ |
| 262 | '', \ |
| 263 | '%{Service-Type}', \ |
| 264 | '%{Framed-Protocol}', \ |
| 265 | '%{Framed-IP-Address}')" |
| 266 | |
| 267 | # |
| 268 | # Key constraints prevented us from inserting a new session, |
| 269 | # use the alternate query to update an existing session. |
| 270 | # |
| 271 | query = "\ |
| 272 | UPDATE ${....acct_table1} SET \ |
| 273 | acctstarttime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 274 | acctupdatetime = FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 275 | connectinfo_start = '%{Connect-Info}' \ |
| 276 | WHERE acctsessionid = '%{Acct-Session-Id}' \ |
| 277 | AND username = '%{SQL-User-Name}' \ |
| 278 | AND nasipaddress = '%{NAS-IP-Address}'" |
| 279 | } |
| 280 | |
| 281 | interim-update { |
| 282 | # |
| 283 | # Update an existing session and calculate the interval |
| 284 | # between the last data we received for the session and this |
| 285 | # update. This can be used to find stale sessions. |
| 286 | # |
| 287 | query = "\ |
| 288 | UPDATE ${....acct_table1} \ |
| 289 | SET \ |
| 290 | acctupdatetime = (@acctupdatetime_old:=acctupdatetime), \ |
| 291 | acctupdatetime = FROM_UNIXTIME(\ |
| 292 | %{integer:Event-Timestamp}), \ |
| 293 | acctinterval = %{integer:Event-Timestamp} - \ |
| 294 | UNIX_TIMESTAMP(@acctupdatetime_old), \ |
| 295 | framedipaddress = '%{Framed-IP-Address}', \ |
| 296 | acctsessiontime = '%{Acct-Session-Time}', \ |
| 297 | acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \ |
| 298 | << 32 | '%{%{Acct-Input-Octets}:-0}', \ |
| 299 | acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \ |
| 300 | << 32 | '%{%{Acct-Output-Octets}:-0}' \ |
| 301 | WHERE acctsessionid = '%{Acct-Session-Id}' \ |
| 302 | AND username = '%{SQL-User-Name}' \ |
| 303 | AND nasipaddress = '%{NAS-IP-Address}'" |
| 304 | |
| 305 | # |
| 306 | # The update condition matched no existing sessions. Use |
| 307 | # the values provided in the update to create a new session. |
| 308 | # |
| 309 | query = "\ |
| 310 | INSERT INTO ${....acct_table1} \ |
| 311 | (${...column_list}) \ |
| 312 | VALUES \ |
| 313 | ('%{Acct-Session-Id}', \ |
| 314 | '%{Acct-Unique-Session-Id}', \ |
| 315 | '%{SQL-User-Name}', \ |
| 316 | '%{Realm}', \ |
| 317 | '%{NAS-IP-Address}', \ |
| 318 | '%{NAS-Port}', \ |
| 319 | '%{NAS-Port-Type}', \ |
| 320 | FROM_UNIXTIME(%{integer:Event-Timestamp} - \ |
| 321 | %{%{Acct-Session-Time}:-0}), \ |
| 322 | FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 323 | NULL, \ |
| 324 | '%{Acct-Session-Time}', \ |
| 325 | '%{Acct-Authentic}', \ |
| 326 | '%{Connect-Info}', \ |
| 327 | '', \ |
| 328 | '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ |
| 329 | '%{%{Acct-Input-Octets}:-0}', \ |
| 330 | '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ |
| 331 | '%{%{Acct-Output-Octets}:-0}', \ |
| 332 | '%{Called-Station-Id}', \ |
| 333 | '%{Calling-Station-Id}', \ |
| 334 | '', \ |
| 335 | '%{Service-Type}', \ |
| 336 | '%{Framed-Protocol}', \ |
| 337 | '%{Framed-IP-Address}')" |
| 338 | } |
| 339 | |
| 340 | stop { |
| 341 | # |
| 342 | # Session has terminated, update the stop time and statistics. |
| 343 | # |
| 344 | query = "\ |
| 345 | UPDATE ${....acct_table2} SET \ |
| 346 | acctstoptime = FROM_UNIXTIME(\ |
| 347 | %{integer:Event-Timestamp}), \ |
| 348 | acctsessiontime = '%{Acct-Session-Time}', \ |
| 349 | acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \ |
| 350 | << 32 | '%{%{Acct-Input-Octets}:-0}', \ |
| 351 | acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \ |
| 352 | << 32 | '%{%{Acct-Output-Octets}:-0}', \ |
| 353 | acctterminatecause = '%{Acct-Terminate-Cause}', \ |
| 354 | connectinfo_stop = '%{Connect-Info}' \ |
| 355 | WHERE acctsessionid = '%{Acct-Session-Id}' \ |
| 356 | AND username = '%{SQL-User-Name}' \ |
| 357 | AND nasipaddress = '%{NAS-IP-Address}'" |
| 358 | |
| 359 | # |
| 360 | # The update condition matched no existing sessions. Use |
| 361 | # the values provided in the update to create a new session. |
| 362 | # |
| 363 | query = "\ |
| 364 | INSERT INTO ${....acct_table2} \ |
| 365 | (${...column_list}) \ |
| 366 | VALUES \ |
| 367 | ('%{Acct-Session-Id}', \ |
| 368 | '%{Acct-Unique-Session-Id}', \ |
| 369 | '%{SQL-User-Name}', \ |
| 370 | '%{Realm}', \ |
| 371 | '%{NAS-IP-Address}', \ |
| 372 | '%{NAS-Port}', \ |
| 373 | '%{NAS-Port-Type}', \ |
| 374 | FROM_UNIXTIME(%{integer:Event-Timestamp} - \ |
| 375 | %{%{Acct-Session-Time}:-0}), \ |
| 376 | FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 377 | FROM_UNIXTIME(%{integer:Event-Timestamp}), \ |
| 378 | '%{Acct-Session-Time}', \ |
| 379 | '%{Acct-Authentic}', '', \ |
| 380 | '%{Connect-Info}', \ |
| 381 | '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ |
| 382 | '%{%{Acct-Input-Octets}:-0}', \ |
| 383 | '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ |
| 384 | '%{%{Acct-Output-Octets}:-0}', \ |
| 385 | '%{Called-Station-Id}', \ |
| 386 | '%{Calling-Station-Id}', \ |
| 387 | '%{Acct-Terminate-Cause}', \ |
| 388 | '%{Service-Type}', \ |
| 389 | '%{Framed-Protocol}', \ |
| 390 | '%{Framed-IP-Address}')" |
| 391 | } |
| 392 | } |
| 393 | } |
| 394 | |
| 395 | ####################################################################### |
| 396 | # Authentication Logging Queries |
| 397 | ####################################################################### |
| 398 | # postauth_query - Insert some info after authentication |
| 399 | ####################################################################### |
| 400 | |
| 401 | post-auth { |
| 402 | # Write SQL queries to a logfile. This is potentially useful for bulk inserts |
| 403 | # when used with the rlm_sql_null driver. |
| 404 | # logfile = ${logdir}/post-auth.sql |
| 405 | |
| 406 | query = "\ |
| 407 | INSERT INTO ${..postauth_table} \ |
| 408 | (username, pass, reply, authdate) \ |
| 409 | VALUES ( \ |
| 410 | '%{SQL-User-Name}', \ |
| 411 | '%{%{User-Password}:-%{Chap-Password}}', \ |
| 412 | '%{reply:Packet-Type}', \ |
| 413 | '%S')" |
| 414 | } |