#
#  $Id$
#
#  Configuration for the SQL module, when using Postgresql.
#
#  The database schema is available at:
#
#	doc/examples/postgresql.sql
#

sql {

	# Database type
	# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
	# rlm_sql_unixodbc, rlm_sql_oracle.
	driver = "rlm_sql_postgresql"

	# Connect info
	server = "localhost"

	# The following credentials will most likely work on a default
	# install of Postgresql. If they do work however, it means that
	# you have a HUGE GAPING SECURITY RISK on your server! Please
	# change the "postgres" users password and setup a separate
	# radius user.
	login = "postgres"
	password = ""

	# 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"

	# Table to keep group info
	usergroup_table = "usergroup"

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

	# Print all SQL statements when in debug mode (-x)
	sqltrace = yes
	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

	#######################################################################
	#  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 "none" as the user name.
	#
	#sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}"
	#
	sql_user_name = "%{User-Name}"


	#######################################################################
	#  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 insensitive usernames. WARNING: Slower queries!
# authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
#   FROM ${authcheck_table} \
#   WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
#   ORDER BY id"
# authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \
#   FROM ${authreply_table} \
#   WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
#   ORDER BY id"

	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 insensitive usernames. WARNING: Slower queries!
# 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 LOWER(${usergroup_table}.UserName) = LOWER('%{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 LOWER(${usergroup_table}.UserName) = LOWER('%{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"

	#######################################################################
	#  Authentication Query
	#######################################################################
	# This query is used only to get the Password for the
	# user we want to authenticate.  The password MUST
	# be the first field in the return row data.
	#######################################################################

	authenticate_query = "SELECT Value,Attribute FROM ${authcheck_table} \
		WHERE UserName = '%{User-Name}' AND ( Attribute = 'User-Password' OR Attribute = 'Crypt-Password' ) \
		ORDER BY Attribute DESC"

        #######################################################################
        # 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"



	#######################################################################
	#  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'::timestamp - '%{Acct-Delay-Time:-0}'::interval), \
  AcctSessionTime = (EXTRACT(EPOCH FROM('%S'::timestamp with time zone - \
                    AcctStartTime::timestamp with time zone - \
                    '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
  AcctTerminateCause='%{Acct-Terminate-Cause}', \
  AcctStopDelay = 0 \
  WHERE AcctSessionTime IS NULL \
  AND AcctStopTime IS NULL \
  AND NASIPAddress= '%{NAS-IP-Address}' \
  AND AcctStartTime <= '%S'::timestamp"

 accounting_update_query = "UPDATE ${acct_table1} \
  SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
  AcctSessionTime = (EXTRACT(EPOCH FROM('%S'::timestamp with time zone - \
                    AcctStartTime::timestamp with time zone - \
                    '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
  AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + \
                    '%{Acct-Input-Octets:-0}'::bigint), \
  AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + \
                    '%{Acct-Output-Octets:-0}'::bigint) \
  WHERE AcctSessionId = '%{Acct-Session-Id}' \
  AND UserName = '%{SQL-User-Name}' \
  AND NASIPAddress= '%{NAS-IP-Address}' \
  AND AcctStopTime IS NULL"

 
accounting_update_query_alt = "INSERT into ${acct_table1} \
  (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, \
  AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
		ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) \
		values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
		'%{NAS-Port}', '%{NAS-Port-Type}', \
                ('%S'::timestamp -  '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
		'%{Acct-Session-Time}', '%{Acct-Authentic}', \
		(('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
		(('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
		'%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
		NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')"

	accounting_start_query = "INSERT into ${acct_table1} \
		(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
		ConnectInfo_start, 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}', ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', \
		'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
		NULLIF('%{Framed-IP-Address}', '')::inet, 0, '%{X-Ascend-Session-Svr-Key}')"

	accounting_start_query_alt  = "UPDATE ${acct_table1} \
		SET AcctStartTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), AcctStartDelay = 0, \
		ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
		AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"

	accounting_stop_query = "UPDATE ${acct_table2} \
		SET AcctStopTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), \
		AcctSessionTime = NULLIF('%{Acct-Session-Time}', '')::bigint, \
		AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
		AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), \
		AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 0, \
		FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, ConnectInfo_stop = '%{Connect-Info}' \
		WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
		AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"

	accounting_stop_query_alt = "INSERT into ${acct_table2} \
		(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \
		AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
		AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) \
		values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
		'%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp -  '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
		('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, \
		'%{Acct-Authentic}', '%{Connect-Info}', \
		(('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
		(('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
		'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', \
		NULLIF('%{Framed-IP-Address}', '')::inet, 0)"

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

	# Use these for case insensitive usernames. WARNING: Slower queries!
#	group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}')"

	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} (username, pass, reply, authdate) VALUES ('%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())"

}