raddb/mods-config/sql/main/mysql/queries.conf

   1 # -*- text -*-
   2 #
   3 #  main/mysql/queries.conf-- MySQL configuration for default schema (schema.sql)
   4 #
   5 #  $Id$
   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 = '%{${group_attribute}}' \
 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 = '%{${group_attribute}}' \
 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 simultaneous 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 simul_count_query = "\
 167         SELECT COUNT(*) \
 168         FROM ${acct_table1} \
 169         WHERE username = '%{SQL-User-Name}' \
 170         AND acctstoptime IS NULL"
 171
 172 simul_verify_query = "\
 173         SELECT \
 174                 radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \
 175                 callingstationid, framedprotocol \
 176         FROM ${acct_table1} \
 177         WHERE username = '%{SQL-User-Name}' \
 178         AND acctstoptime IS NULL"
 179
 180 #######################################################################
 181 # Accounting and Post-Auth Queries
 182 #######################################################################
 183 # These queries insert/update accounting and authentication records.
 184 # The query to use is determined by the value of 'reference'.
 185 # This value is used as a configuration path and should resolve to one
 186 # or more 'query's. If reference points to multiple queries, and a query
 187 # fails, the next query is executed.
 188 #
 189 # Behaviour is identical to the old 1.x/2.x module, except we can now
 190 # fail between N queries, and query selection can be based on any
 191 # combination of attributes, or custom 'Acct-Status-Type' values.
 192 #######################################################################
 193 accounting {
 194         reference = "%{tolower:type.%{Acct-Status-Type}.query}"
 195
 196         # Write SQL queries to a logfile. This is potentially useful for bulk inserts
 197         # when used with the rlm_sql_null driver.
 198 #       logfile = ${logdir}/accounting.sql
 199
 200         column_list = "\
 201                 acctsessionid,          acctuniqueid,           username, \
 202                 realm,                  nasipaddress,           nasportid, \
 203                 nasporttype,            acctstarttime,          acctupdatetime, \
 204                 acctstoptime,           acctsessiontime,        acctauthentic, \
 205                 connectinfo_start,      connectinfo_stop,       acctinputoctets, \
 206                 acctoutputoctets,       calledstationid,        callingstationid, \
 207                 acctterminatecause,     servicetype,            framedprotocol, \
 208                 framedipaddress"
 209
 210         type {
 211                 accounting-on {
 212                         #
 213                         #  Bulk terminate all sessions associated with a given NAS
 214                         #
 215                         query = "\
 216                                 UPDATE ${....acct_table1} \
 217                                 SET \
 218                                         acctstoptime = FROM_UNIXTIME(\
 219                                                 %{integer:Event-Timestamp}), \
 220                                         acctsessiontime = '%{integer:Event-Timestamp}' \
 221                                                 - UNIX_TIMESTAMP(acctstarttime), \
 222                                         acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' \
 223                                 WHERE acctstoptime IS NULL \
 224                                 AND nasipaddress   = '%{NAS-IP-Address}' \
 225                                 AND acctstarttime <= FROM_UNIXTIME(\
 226                                         %{integer:Event-Timestamp})"
 227                 }
 228
 229                 accounting-off {
 230                         query = "${..accounting-on.query}"
 231                 }
 232
 233                 start {
 234                         #
 235                         #  Insert a new record into the sessions table
 236                         #
 237                         query = "\
 238                                 INSERT INTO ${....acct_table1} \
 239                                         (${...column_list}) \
 240                                 VALUES \
 241                                         ('%{Acct-Session-Id}', \
 242                                         '%{Acct-Unique-Session-Id}', \
 243                                         '%{SQL-User-Name}', \
 244                                         '%{Realm}', \
 245                                         '%{NAS-IP-Address}', \
 246                                         '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
 247                                         '%{NAS-Port-Type}', \
 248                                         FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 249                                         FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 250                                         NULL, \
 251                                         '0', \
 252                                         '%{Acct-Authentic}', \
 253                                         '%{Connect-Info}', \
 254                                         '', \
 255                                         '0', \
 256                                         '0', \
 257                                         '%{Called-Station-Id}', \
 258                                         '%{Calling-Station-Id}', \
 259                                         '', \
 260                                         '%{Service-Type}', \
 261                                         '%{Framed-Protocol}', \
 262                                         '%{Framed-IP-Address}')"
 263
 264                         #
 265                         #  Key constraints prevented us from inserting a new session,
 266                         #  use the alternate query to update an existing session.
 267                         #
 268                         query = "\
 269                                 UPDATE ${....acct_table1} SET \
 270                                         acctstarttime   = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 271                                         acctupdatetime  = FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 272                                         connectinfo_start = '%{Connect-Info}' \
 273                                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
 274                 }
 275
 276                 interim-update {
 277                         #
 278                         #  Update an existing session and calculate the interval
 279                         #  between the last data we received for the session and this
 280                         #  update. This can be used to find stale sessions.
 281                         #
 282                         query = "\
 283                                 UPDATE ${....acct_table1} \
 284                                 SET \
 285                                         acctupdatetime  = (@acctupdatetime_old:=acctupdatetime), \
 286                                         acctupdatetime  = FROM_UNIXTIME(\
 287                                                 %{integer:Event-Timestamp}), \
 288                                         acctinterval    = %{integer:Event-Timestamp} - \
 289                                                 UNIX_TIMESTAMP(@acctupdatetime_old), \
 290                                         framedipaddress = '%{Framed-IP-Address}', \
 291                                         acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
 292                                         acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
 293                                                 << 32 | '%{%{Acct-Input-Octets}:-0}', \
 294                                         acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
 295                                                 << 32 | '%{%{Acct-Output-Octets}:-0}' \
 296                                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
 297
 298                         #
 299                         #  The update condition matched no existing sessions. Use
 300                         #  the values provided in the update to create a new session.
 301                         #
 302                         query = "\
 303                                 INSERT INTO ${....acct_table1} \
 304                                         (${...column_list}) \
 305                                 VALUES \
 306                                         ('%{Acct-Session-Id}', \
 307                                         '%{Acct-Unique-Session-Id}', \
 308                                         '%{SQL-User-Name}', \
 309                                         '%{Realm}', \
 310                                         '%{NAS-IP-Address}', \
 311                                         '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
 312                                         '%{NAS-Port-Type}', \
 313                                         FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
 314                                         FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 315                                         NULL, \
 316                                         %{%{Acct-Session-Time}:-NULL}, \
 317                                         '%{Acct-Authentic}', \
 318                                         '%{Connect-Info}', \
 319                                         '', \
 320                                         '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
 321                                         '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
 322                                         '%{Called-Station-Id}', \
 323                                         '%{Calling-Station-Id}', \
 324                                         '', \
 325                                         '%{Service-Type}', \
 326                                         '%{Framed-Protocol}', \
 327                                         '%{Framed-IP-Address}')"
 328                 }
 329
 330                 stop {
 331                         #
 332                         #  Session has terminated, update the stop time and statistics.
 333                         #
 334                         query = "\
 335                                 UPDATE ${....acct_table2} SET \
 336                                         acctstoptime    = FROM_UNIXTIME(\
 337                                                 %{integer:Event-Timestamp}), \
 338                                         acctsessiontime = %{%{Acct-Session-Time}:-NULL}, \
 339                                         acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' \
 340                                                 << 32 | '%{%{Acct-Input-Octets}:-0}', \
 341                                         acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' \
 342                                                 << 32 | '%{%{Acct-Output-Octets}:-0}', \
 343                                         acctterminatecause = '%{Acct-Terminate-Cause}', \
 344                                         connectinfo_stop = '%{Connect-Info}' \
 345                                 WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'"
 346
 347                         #
 348                         #  The update condition matched no existing sessions. Use
 349                         #  the values provided in the update to create a new session.
 350                         #
 351                         query = "\
 352                                 INSERT INTO ${....acct_table2} \
 353                                         (${...column_list}) \
 354                                 VALUES \
 355                                         ('%{Acct-Session-Id}', \
 356                                         '%{Acct-Unique-Session-Id}', \
 357                                         '%{SQL-User-Name}', \
 358                                         '%{Realm}', \
 359                                         '%{NAS-IP-Address}', \
 360                                         '%{%{NAS-Port-ID}:-%{NAS-Port}}', \
 361                                         '%{NAS-Port-Type}', \
 362                                         FROM_UNIXTIME(%{integer:Event-Timestamp} - %{%{Acct-Session-Time}:-0}), \
 363                                         FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 364                                         FROM_UNIXTIME(%{integer:Event-Timestamp}), \
 365                                         %{%{Acct-Session-Time}:-NULL}, \
 366                                         '%{Acct-Authentic}', \
 367                                         '', \
 368                                         '%{Connect-Info}', \
 369                                         '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', \
 370                                         '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', \
 371                                         '%{Called-Station-Id}', \
 372                                         '%{Calling-Station-Id}', \
 373                                         '%{Acct-Terminate-Cause}', \
 374                                         '%{Service-Type}', \
 375                                         '%{Framed-Protocol}', \
 376                                         '%{Framed-IP-Address}')"
 377                 }
 378         }
 379 }
 380
 381
 382 #######################################################################
 383 # Authentication Logging Queries
 384 #######################################################################
 385 # postauth_query        - Insert some info after authentication
 386 #######################################################################
 387
 388 post-auth {
 389         # Write SQL queries to a logfile. This is potentially useful for bulk inserts
 390         # when used with the rlm_sql_null driver.
 391 #       logfile = ${logdir}/post-auth.sql
 392
 393         query = "\
 394                 INSERT INTO ${..postauth_table} \
 395                         (username, pass, reply, authdate) \
 396                 VALUES ( \
 397                         '%{SQL-User-Name}', \
 398                         '%{%{User-Password}:-%{Chap-Password}}', \
 399                         '%{reply:Packet-Type}', \
 400                         '%S')"
 401 }