#
#  Configuration for the SQL module, when using Postgresql.
#
#  The database schema is available at:
#
#   src/radiusd/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql
#
#	$Id: postgresql.conf,v 1.6 2002/11/13 10:55:01 pnixon Exp $
#
sql {

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

	# 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"
		
	authcheck_table = "radcheck"
	authreply_table = "radreply"
	
	groupcheck_table = "radgroupcheck"
	groupreply_table = "radgroupreply"
	
	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

	#######################################################################
	#  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
	#######################################################################
	# Use these for case sensitive usernames. WARNING: Slower queries!
#	authorize_check_query = "SELECT id,UserName,Attribute,Value,Op FROM ${authcheck_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"
#	authorize_reply_query = "SELECT id,UserName,Attribute,Value,Op FROM ${authreply_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 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 sensitive usernames. WANRING: Slower queries!
#	authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value FROM ${groupcheck_table},${usergroup_table} WHERE STRCMP(${usergroup_table}.Username, '%{SQL-User-Name}') = 0 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 FROM ${groupreply_table},${usergroup_table} WHERE STRCMP(${usergroup_table}.Username, '%{SQL-User-Name}') = 0 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 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 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"

	#######################################################################
	#  Accounting Queries
	#######################################################################
	# accounting_onoff_query	- query for Accounting On/Off packets 
	# accounting_update_query	- query for Accounting update packets 
	# 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)
	#######################################################################
	
# I've changed this section... removed inserting of radacctid (it's autoinctremented by database)
# And unix_timestamp is mysql fuction... extract (epoch.. seem to work, but situation when
# NAS stops/starts accounting is quite rare so i never tested it (but i tried it as an sql request, it works :) )
# I also removed insertition of AcctStopTime from "Start" query (it fails with postgres)
# and added NAS-IP-Address to Stop query (strange, but radius can not determine username when updating field with ip address and query is empty)
# also i changed NAS-Port-id to NAS-Port (cisco nas gives me NAS-Port)
# Hmmm... please let me know if i forgot somthing... and if i made mistake :)
	accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S', AcctSessionTime=extract(epoch from (timestamp('%S') - timestamp(AcctStartTime))), AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time:-0} WHERE AcctSessionTime=0 AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"

	accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress = '%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"

	accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, 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}', '%S', '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')"

	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}' AND AcctStopTime IS NULL"

	accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = CASE WHEN '%{Acct-Input-Octets}' = '' THEN 0 ELSE  '%{Acct-Input-Octets}' END, AcctOutputOctets = CASE WHEN '%{Acct-Output-Octets}' = '' THEN 0 ELSE '%{Acct-Output-Octets}' END, AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', FramedIPAddress = '%{Framed-IP-Address}', 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, 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}', '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-Info}', CASE WHEN '%{Acct-Input-Octets}' = '' THEN 0 ELSE  '%{Acct-Input-Octets}' END, CASE WHEN '%{Acct-Output-Octets}' = '' THEN 0 ELSE '%{Acct-Output-Octets}' END, '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time:-0}')"

}