raddb/sql/mysql/dialup.conf

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