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