Subversion Repositories ALCASAR

Rev

Rev 40 | Go to most recent revision | Blame | Last modification | View Log

# -*- text -*-
##
## dialup.conf -- MySQL configuration for default schema (schema.sql)
##
##      $Id: dialup.conf 57 2010-04-05 13:05:53Z franck $

        # Safe characters list for sql queries. Everything else is replaced
        # with their mime-encoded equivalents.
        # The default list should be ok
        #safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"

        #######################################################################
        #  Query config:  Username
        #######################################################################
        # This is the username that will get substituted, escaped, and added
        # as attribute 'SQL-User-Name'.  '%{SQL-User-Name}' should be used below
        # everywhere a username substitution is needed so you you can be sure
        # the username passed from the client is escaped properly.
        #
        #  Uncomment the next line, if you want the sql_user_name to mean:
        #
        #    Use Stripped-User-Name, if it's there.
        #    Else use User-Name, if it's there,
        #    Else use hard-coded string "DEFAULT" as the user name.
        #sql_user_name = "%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}}"
        #
        sql_user_name = "%{User-Name}"

        #######################################################################
        #  Default profile
        #######################################################################
        # This is the default profile. It is found in SQL by group membership.
        # That means that this profile must be a member of at least one group
        # which will contain the corresponding check and reply items.
        # This profile will be queried in the authorize section for every user.
        # The point is to assign all users a default profile without having to
        # manually add each one to a group that will contain the profile.
        # The SQL module will also honor the User-Profile attribute. This
        # attribute can be set anywhere in the authorize section (ie the users
        # file). It is found exactly as the default profile is found.
        # If it is set then it will *overwrite* the default profile setting.
        # The idea is to select profiles based on checks on the incoming packets,
        # not on user group membership. For example:
        # -- users file --
        # DEFAULT       Service-Type == Outbound-User, User-Profile := "outbound"
        # DEFAULT       Service-Type == Framed-User, User-Profile := "framed"
        #
        # By default the default_user_profile is not set
        #
        #default_user_profile = "DEFAULT"

        #######################################################################
        #  NAS Query
        #######################################################################
        #  This query retrieves the radius clients
        #
        #  0. Row ID (currently unused)
        #  1. Name (or IP address)
        #  2. Shortname
        #  3. Type
        #  4. Secret
        #######################################################################

        nas_query = "SELECT id, nasname, shortname, type, secret FROM ${nas_table}"

        #######################################################################
        #  Authorization Queries
        #######################################################################
        #  These queries compare the check items for the user
        #  in ${authcheck_table} and setup the reply items in
        #  ${authreply_table}.  You can use any query/tables
        #  you want, but the return data for each row MUST
        #  be in the  following order:
        #
        #  0. Row ID (currently unused)
        #  1. UserName/GroupName
        #  2. Item Attr Name
        #  3. Item Attr Value
        #  4. Item Attr Operation
        #######################################################################
        # Use these for case sensitive usernames.
        authorize_check_query = "SELECT id, username, attribute, value, op \
         FROM ${authcheck_table} \
         WHERE username = BINARY '%{SQL-User-Name}' \
         ORDER BY id"
        authorize_reply_query = "SELECT id, username, attribute, value, op \
         FROM ${authreply_table} \
         WHERE username = BINARY '%{SQL-User-Name}' \
         ORDER BY id"

        # The default queries are case insensitive. (for compatibility with
        # older versions of FreeRADIUS)
#       authorize_check_query = "SELECT id, username, attribute, value, op \
#          FROM ${authcheck_table} \
#          WHERE username = '%{SQL-User-Name}' \
#          ORDER BY id"
#       authorize_reply_query = "SELECT id, username, attribute, value, op \
#          FROM ${authreply_table} \
#          WHERE username = '%{SQL-User-Name}' \
#          ORDER BY id"

        # Use these for case sensitive usernames.
        group_membership_query = "SELECT groupname \
         FROM ${usergroup_table} \
         WHERE username = BINARY '%{SQL-User-Name}' \
         ORDER BY priority"

#       group_membership_query = "SELECT groupname \
#          FROM ${usergroup_table} \
#          WHERE username = '%{SQL-User-Name}' \
#          ORDER BY priority"

        authorize_group_check_query = "SELECT id, groupname, attribute, \
          Value, op \
          FROM ${groupcheck_table} \
          WHERE groupname = '%{Sql-Group}' \
          ORDER BY id"
        authorize_group_reply_query = "SELECT id, groupname, attribute, \
          value, op \
          FROM ${groupreply_table} \
          WHERE groupname = '%{Sql-Group}' \
          ORDER BY id"

        #######################################################################
        #  Accounting Queries
        #######################################################################
        # accounting_onoff_query        - query for Accounting On/Off packets
        # accounting_update_query       - query for Accounting update packets
        # accounting_update_query_alt   - query for Accounting update packets
        #                               (alternate in case first query fails)
        # accounting_start_query        - query for Accounting start packets
        # accounting_start_query_alt    - query for Accounting start packets
        #                               (alternate in case first query fails)
        # accounting_stop_query         - query for Accounting stop packets
        # accounting_stop_query_alt     - query for Accounting start packets
        #                               (alternate in case first query doesn't
        #                                affect any existing rows in the table)
        #######################################################################
        accounting_onoff_query = "\
          UPDATE ${acct_table1} \
          SET \
             acctstoptime       =  '%S', \
             acctsessiontime    =  unix_timestamp('%S') - \
                                   unix_timestamp(acctstarttime), \
             acctterminatecause =  '%{Acct-Terminate-Cause}', \
             acctstopdelay      =  %{%{Acct-Delay-Time}:-0} \
          WHERE acctstoptime IS NULL \
          AND nasipaddress      =  '%{NAS-IP-Address}' \
          AND acctstarttime     <= '%S'"

        accounting_update_query = " \
          UPDATE ${acct_table1} \
          SET \
             framedipaddress = '%{Framed-IP-Address}', \
             acctsessiontime     = '%{Acct-Session-Time}', \
             acctinputoctets     = '%{%{Acct-Input-Gigawords}:-0}'  << 32 | \
                                   '%{%{Acct-Input-Octets}:-0}', \
             acctoutputoctets    = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
                                   '%{%{Acct-Output-Octets}:-0}' \
          WHERE acctsessionid = '%{Acct-Session-Id}' \
          AND username        = '%{SQL-User-Name}' \
          AND nasipaddress    = '%{NAS-IP-Address}'"

        accounting_update_query_alt = " \
          INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,      username, \
             realm,            nasipaddress,      nasportid, \
             nasporttype,      acctstarttime,     acctsessiontime, \
             acctauthentic,    connectinfo_start, acctinputoctets, \
             acctoutputoctets, calledstationid,   callingstationid, \
             servicetype,      framedprotocol,    framedipaddress, \
             acctstartdelay,   xascendsessionsvrkey) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \
             DATE_SUB('%S', \
                      INTERVAL (%{%{Acct-Session-Time}:-0} + \
                                %{%{Acct-Delay-Time}:-0}) SECOND), \
                      '%{Acct-Session-Time}', \
             '%{Acct-Authentic}', '', \
             '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Input-Octets}:-0}', \
             '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Output-Octets}:-0}', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', \
             '%{Service-Type}', '%{Framed-Protocol}', \
             '%{Framed-IP-Address}', \
             '0', '%{X-Ascend-Session-Svr-Key}')"

        accounting_start_query = " \
          INSERT INTO ${acct_table1} \
            (acctsessionid,    acctuniqueid,     username, \
             realm,            nasipaddress,     nasportid, \
             nasporttype,      acctstarttime,    acctstoptime, \
             acctsessiontime,  acctauthentic,    connectinfo_start, \
             connectinfo_stop, acctinputoctets,  acctoutputoctets, \
             calledstationid,  callingstationid, acctterminatecause, \
             servicetype,      framedprotocol,   framedipaddress, \
             acctstartdelay,   acctstopdelay,    xascendsessionsvrkey) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', '%S', NULL, \
             '0', '%{Acct-Authentic}', '%{Connect-Info}', \
             '', '0', '0', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
             '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"

        accounting_start_query_alt  = " \
          UPDATE ${acct_table1} SET \
             acctstarttime     = '%S', \
             acctstartdelay    = '%{%{Acct-Delay-Time}:-0}', \
             connectinfo_start = '%{Connect-Info}' \
          WHERE acctsessionid  = '%{Acct-Session-Id}' \
          AND username         = '%{SQL-User-Name}' \
          AND nasipaddress     = '%{NAS-IP-Address}'"

        accounting_stop_query = " \
          UPDATE ${acct_table2} SET \
             acctstoptime       = '%S', \
             acctsessiontime    = '%{Acct-Session-Time}', \
             acctinputoctets    = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
                                  '%{%{Acct-Input-Octets}:-0}', \
             acctoutputoctets   = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
                                  '%{%{Acct-Output-Octets}:-0}', \
             acctterminatecause = '%{Acct-Terminate-Cause}', \
             acctstopdelay      = '%{%{Acct-Delay-Time}:-0}', \
             connectinfo_stop   = '%{Connect-Info}' \
          WHERE acctsessionid   = '%{Acct-Session-Id}' \
          AND username          = '%{SQL-User-Name}' \
          AND nasipaddress      = '%{NAS-IP-Address}'"

        accounting_stop_query_alt = " \
          INSERT INTO ${acct_table2} \
            (acctsessionid, acctuniqueid, username, \
             realm, nasipaddress, nasportid, \
             nasporttype, acctstarttime, acctstoptime, \
             acctsessiontime, acctauthentic, connectinfo_start, \
             connectinfo_stop, acctinputoctets, acctoutputoctets, \
             calledstationid, callingstationid, acctterminatecause, \
             servicetype, framedprotocol, framedipaddress, \
             acctstartdelay, acctstopdelay) \
          VALUES \
            ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
             '%{SQL-User-Name}', \
             '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
             '%{NAS-Port-Type}', \
             DATE_SUB('%S', \
                 INTERVAL (%{%{Acct-Session-Time}:-0} + \
                 %{%{Acct-Delay-Time}:-0}) SECOND), \
             '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', \
             '%{Connect-Info}', \
             '%{%{Acct-Input-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Input-Octets}:-0}', \
             '%{%{Acct-Output-Gigawords}:-0}' << 32 | \
             '%{%{Acct-Output-Octets}:-0}', \
             '%{Called-Station-Id}', '%{Calling-Station-Id}', \
             '%{Acct-Terminate-Cause}', \
             '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', \
             '0', '%{%{Acct-Delay-Time}:-0}')"

        #######################################################################
        # Simultaneous Use Checking Queries
        #######################################################################
        # simul_count_query     - query for the number of current connections
        #                       - If this is not defined, no simultaneouls use checking
        #                       - will be performed by this module instance
        # simul_verify_query    - query to return details of current connections for verification
        #                       - Leave blank or commented out to disable verification step
        #                       - Note that the returned field order should not be changed.
        #######################################################################

        # Uncomment simul_count_query to enable simultaneous use checking
        simul_count_query = "SELECT COUNT(*) \
                             FROM ${acct_table1} \
                             WHERE username = '%{SQL-User-Name}' \
                             AND acctstoptime IS NULL"

        #simul_verify_query  = "SELECT radacctid, acctsessionid, username, \
                               #nasipaddress, nasportid, framedipaddress, \
                               #callingstationid, framedprotocol \
                               #FROM ${acct_table1} \
                               #WHERE username = '%{SQL-User-Name}' \
                               #AND acctstoptime IS NULL"

        #######################################################################
        # Authentication Logging Queries
        #######################################################################
        # postauth_query                - Insert some info after authentication
        #######################################################################
#                          (username, pass, reply, authdate) \
#                          '%{%{User-Password}:-%{Chap-Password}}', \

        postauth_query = "INSERT INTO ${postauth_table} \
                          (username, reply, authdate) \
                          VALUES ( \
                          '%{User-Name}', \
                          '%{reply:Packet-Type}', '%S')"