#
#  Configuration for the SQL module, when using MySQL.
#
#  The database schema is available at:
#
#	doc/examples/mysql.sql
#
#  If you are using PostgreSQL, please use 'postgresql.conf', instead.
#  If you are using Oracle, please use 'oracle.conf', instead.
#  If you are using MS-SQL, please use 'mssql.conf', instead.
#
#  $Id: sql.conf,v 1.41.2.2.2.2 2006/02/04 14:13:03 nbk Exp $
#
sql {
	# Database type
	# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
	# rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc, rlm_sql_freetds
	driver = "rlm_sql_sqlite"

	# Connect info
	server = "localhost"
	login = "root"
	password = "rootpass"

	# Database table configuration
	radius_db = "radius"

	# If you want both stop and start records logged to the
	# same SQL table, leave this as is.  If you want them in
	# different tables, put the start table in acct_table1
	# and stop table in acct_table2
	acct_table1 = "radacct"
	acct_table2 = "radacct"

	# Allow for storing data after authentication
	postauth_table = "radpostauth"

	authcheck_table = "radcheck"
	authreply_table = "radreply"

	groupcheck_table = "radgroupcheck"
	groupreply_table = "radgroupreply"

	usergroup_table = "usergroup"

	# Table to keep radius client info
	nas_table = "nas"

	# Remove stale session if checkrad does not see a double login
	deletestalesessions = yes

	# Print all SQL statements when in debug mode (-x)
	sqltrace = no
	sqltracefile = ${logdir}/sqltrace.sql

	# number of sql connections to make to server
	num_sql_socks = 5

	# number of seconds to dely retrying on a failed database
	# connection (per_socket)
	connect_failure_retry_delay = 60

	# 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"
	#
	# Determines if we will query the default_user_profile or the User-Profile
	# if the user is not found. If the profile is found then we consider the user
	# found. By default this is set to 'no'.
	#
	#query_on_not_found = no

	#######################################################################
	#  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.
#	authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username = BINARY '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY ${groupcheck_table}.id"
#	authorize_group_reply_query = "SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op  FROM ${groupreply_table},${usergroup_table} WHERE ${usergroup_table}.Username = BINARY '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id"

	authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op  FROM ${groupcheck_table},${usergroup_table} WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY ${groupcheck_table}.id"
	authorize_group_reply_query = "SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op  FROM ${groupreply_table},${usergroup_table} WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY ${groupreply_table}.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}' WHERE AcctSessionTime=0 AND AcctStopTime=0 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', '0', \
             '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}', 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 = 0"

        simul_verify_query  = "SELECT RadAcctId, AcctSessionId, UserName, \
                               NASIPAddress, NASPortId, FramedIPAddress, \
                               CallingStationId, FramedProtocol \
                               FROM ${acct_table1} \
                               WHERE UserName='%{SQL-User-Name}' \
                               AND AcctStopTime = 0"

	#######################################################################
	# Group Membership Queries
	#######################################################################
	# group_membership_query	- Check user group membership
	#######################################################################

	group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'"

	#######################################################################
	# Authentication Logging Queries
	#######################################################################
	# postauth_query		- Insert some info after authentication
	#######################################################################

	postauth_query = "INSERT into ${postauth_table} (user, pass, reply, date) values ('%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())"

	#
	# Set to 'yes' to read radius clients from the database ('nas' table)
	readclients = yes
}