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