# common-sql.cfg
#
# Example Radiator configuration file that allows you to
# authenticate from an SQL database.
# With Radiator you can interface with almost any databse schema,
# and there are many more configurable parameters that allow you
# to control database fallback, select statements, column names
# and arrangements etc etc etc.
# See the reference manual for more details.
# This is a very simple exmaple to get you started. It will
# work with the tables created by the goodies/*.sql scripts.
# You can create a test table, test user and mysql user with the following
# commands:
# mysql -uroot -p
# > CREATE DATABASE radius;
# > GRANT ALL PRIVILEGES ON radius.* TO 'mikem'@'localhost' IDENTIFIED BY 'fred';
#
# You should consider this file to be a starting point only
# $Id: sql.cfg,v 1.12 2007/12/18 21:23:50 mikem Exp $

Foreground
LogStdout
LogDir		.
DbDir		.
Trace		4

# You will probably want to change this to suit your site.
<Client DEFAULT>
	Secret	mysecret
	DupInterval 0
</Client>

# You can put client details in a database table
# and get their details from there with something like this:
<ClientListSQL>
	DBSource	dbi:mysql:radius
	DBUsername	mikem
	DBAuth		fred

	# If RefreshPeriod is set to non-zero, it specifies the period in seconds that the client list will
	# be refreshed by rereading the database. Each RefreshPeriod,
	# any Clients previously created by this ClientList are
	# cleared and a new set of clients read from the database.
	# Clients defined in the configuration file will not be clobbered.
	# The same effect can be got by signalling the process with with SIGHUP
	#RefreshPeriod 600
</ClientListSQL>

# This will authenticate users from SUBSCRIBERS
<Realm DEFAULT>
    <AuthBy SQL>
	# Adjust DBSource, DBUsername, DBAuth to suit your DB

	DBSource	dbi:mysql:radius
	DBUsername	mikem
	DBAuth		fred

	# You can customise the SQL query used to get user details with the
        # AuthSelect parameter:
	#  AuthSelect select PASSWORD from SUBSCRIBERS where USERNAME=%0
	# You can use statement caching and bound variables with AuthSelectParam:
	#  AuthSelect select PASSWORD from SUBSCRIBERS where USERNAME=?
	#  AuthSelectParam %u
	# You can control what is done with each field returned from the
	#  AuthSelect query with the AuthColumnDef parameter:
	#  AuthColumnDef 0, User-Password, check

	# You may want to tailor these for your ACCOUNTING table
	# You can add your own columns to store whatever you like
	AccountingTable	ACCOUNTING
	AcctColumnDef	USERNAME,User-Name
	AcctColumnDef	TIME_STAMP,Timestamp,integer
	AcctColumnDef	ACCTSTATUSTYPE,Acct-Status-Type
	AcctColumnDef	ACCTDELAYTIME,Acct-Delay-Time,integer
	AcctColumnDef	ACCTINPUTOCTETS,Acct-Input-Octets,integer
	AcctColumnDef	ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
	AcctColumnDef	ACCTSESSIONID,Acct-Session-Id
	AcctColumnDef	ACCTSESSIONTIME,Acct-Session-Time,integer
	AcctColumnDef	ACCTTERMINATECAUSE,Acct-Terminate-Cause
	AcctColumnDef	NASIDENTIFIER,NAS-Identifier
	AcctColumnDef	NASPORT,NAS-Port,integer
	AcctColumnDef	FRAMEDIPADDRESS,Framed-IP-Address

	# You can arrange to log accounting to a file if the
	# SQL insert fails with AcctFailedLogFileName
	# That way you could recover from a broken SQL
	# server
	#AcctFailedLogFileName %D/missedaccounting

	# Alternatively, you can arrange to save failed SQL accounting insert queries to a text
	# file with SQLRecoveryFile
	SQLRecoveryFile %D/missedaccounting

	# You can run a hook whenever Radiator (re)connects to the database. This
	# can be useful for doing database-specific config or setup
	# The hook is called like hook($object, $handle)
	# $object is the SqlDb object that is doing the connecting,
	# and $handle is the database handle if the newly connected database
	# This example shows how to set some connection specific attributes
	# for Interbase
	#ConnectionHook sub {$_[1]->func(-access_mode => 'read_write',\
        #	-isolation_level => 'read_committed',\
        #	-lock_resolution => 'wait',\
        #	'ib_set_tx_param')}

	# You can implement queries to get total session times in order
	# to enforce Max-All-Session, Max-Daily-Session,
	# Max-Hourly-Session and Max-Monthly-Session check items
	# %0 is replaced bythe username and %1 is replaced by the untx time of
	# the start of the time interval convcerned.
	#AcctTotalQuery SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName=%0
	#AcctTotalSinceQuery SELECT SUM(AcctSessionTime - GREATEST((%1 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName=%0 AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > %1
    </AuthBy>
</Realm>