Dialup.conf :

[root@RADIUS4 radius]# cat /etc/raddb/sql/mysql/dialup.conf

# -*- text -*-

##

## dialup.conf -- MySQL configuration for default schema (schema.sql)

##

##      $Id$

# 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

#######################################################################

postauth_query = " \

INSERT INTO ${postauth_table} \

(username, pass, reply, authdate) \

VALUES ( \

'%{User-Name}', \

'%{%{User-Password}:-%{Chap-Password}}', \

'%{reply:Packet-Type}', '%S')"