#
#	Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
#
#  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
#

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

	accounting_update_query = "UPDATE ${acct_table1} \
		SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
		AcctSessionTime = (EXTRACT(EPOCH FROM(now()::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) \
		values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
		'%{NAS-Port}', '%{NAS-Port-Type}', (now() -  '%{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)"

	accounting_start_query = "INSERT into ${acct_table1} \
		(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
		ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) \
		values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
		'%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', \
		'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
		NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')"

	accounting_start_query_alt  = "UPDATE ${acct_table1} \
		SET AcctStartTime = (now() - '%{Acct-Delay-Time:-0}'::interval), 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 = (now() - '%{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 = '%{Acct-Delay-Time:-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}', (now() -  '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
		(now() - '%{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, '%{Acct-Delay-Time:-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} (username, pass, reply, authdate) \
		values ('%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())"



}
