etc/freeradius/mods-available/*
etc/freeradius/mods-enabled/*
etc/freeradius/policy.d/*
-etc/freeradius/policy.txt
+etc/freeradius/certs/*
etc/freeradius/preproxy_users
etc/freeradius/proxy.conf
etc/freeradius/sites-available/*
#
# Write Chargeable-User-Identity to the database.
#
-# Schema raddb/sql/DB/cui.sql
-# Queries raddb/sql/DB/cui.conf
+# Schema raddb/sql/cui/<DB>/schema.sql
+# Queries raddb/sql/cui/<DB>/queries.conf
#
sql cuisql {
+ # The submodule to use to execute queries. This should match
+ # the database you're attempting to connect to.
#
- # Set the database to one of:
+ # There are CUI queries available for:
+ # * rlm_sql_mysql
+ # * rlm_sql_postgresql
+ # * rlm_sql_null (log queries to disk)
#
- # mysql, postgresql
+ driver = "rlm_sql_null"
+
+ # The dialect of SQL you want to use, this should usually match
+ # the driver you selected above.
#
- database = "mysql"
- driver = "rlm_sql_${database}"
+ # If you're using rlm_sql_null, then it should be the type of
+ # database the logged queries are going to be exected against.
+ dialect = "mysql"
+
server = "localhost"
login = "db_login_name"
password = "db_password"
radius_db = "db_name"
# sqltrace = yes
# sqltracefile = ${logdir}/cuitrace.sql
+
pool {
start = 5
min = 4
lifetime = 0
idle_timeout = 60
}
+
cui_table = "cui"
sql_user_name = "%{User-Name}"
- $INCLUDE sql/${database}/cui.conf
+ $INCLUDE sql/cui/${dialect}/queries.conf
}
#
# The database schemas and queries are located in subdirectories:
#
-# sql/DB/schema.sql Schema
-# sql/DB/dialup.conf Basic dialup (including policy) queries
-# sql/DB/counter.conf counter
-# sql/DB/ippool.conf IP Pools in SQL
-# sql/DB/ippool.sql schema for IP pools.
+# sql/<DB>/main/schema.sql Schema
+# sql/<DB>/main/queries.conf Authorisation and Accounting queries
#
# Where "DB" is mysql, mssql, oracle, or postgresql.
#
sql {
+ # The submodule to use to execute queries. This should match
+ # the database you're attempting to connect to.
#
- # Set the database to one of:
+ # * rlm_sql_mysql
+ # * rlm_sql_mssql
+ # * rlm_sql_oracle
+ # * rlm_sql_postgresql
+ # * rlm_sql_null (log queries to disk)
+ #
+ driver = "rlm_sql_null"
+
+ # The dialect of SQL you want to use, this should usually match
+ # the driver you selected above.
#
- # mysql, mssql, oracle, postgresql
- #
- database = "mysql"
-
- #
- # Which FreeRADIUS driver to use.
- #
- driver = "rlm_sql_${database}"
-
+ # If you're using rlm_sql_null, then it should be the type of
+ # database the logged queries are going to be exected against.
+ dialect = "mysql"
+
# Connection info:
- server = "localhost"
- #port = 3306
- login = "radius"
- password = "radpass"
+ #
+ # server = "localhost"
+ # port = 3306
+ # login = "radius"
+ # password = "radpass"
# Database table configuration for everything except Oracle
radius_db = "radius"
+
# If you are using Oracle then use this instead
# radius_db = "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=your_sid)))"
# Allow for storing data after authentication
postauth_table = "radpostauth"
+ # Tables containing 'check' items
authcheck_table = "radcheck"
- authreply_table = "radreply"
-
groupcheck_table = "radgroupcheck"
+
+ # Tables containing 'reply' items
+ authreply_table = "radreply"
groupreply_table = "radgroupreply"
# Table to keep group info
# lifetime
# max_queries
+ #
# The connection pool is new for 3.0, and will be used in many
# modules, for all kinds of connection-related activity.
#
# Table to keep radius client info
nas_table = "nas"
- # Read driver-specific configuration
- $INCLUDE sql/${database}/dialup.conf
+ # Read database-specific queries
+ $INCLUDE sql/main/${dialect}/queries.conf
}
+++ /dev/null
-# -*- text -*-
-#
-# $Id$
-
-#
-# The rlm_sql_log module appends the SQL queries in a log
-# file which is read later by the radsqlrelay program.
-#
-# This module only performs the dynamic expansion of the
-# variables found in the SQL statements. No operation is
-# executed on the database server. (this could be done
-# later by an external program) That means the module is
-# useful only with non-"SELECT" statements.
-#
-# See rlm_sql_log(5) manpage.
-#
-# This same functionality could also be implemented by logging
-# to a "detail" file, reading that, and then writing to SQL.
-# See raddb/sites-available/buffered-sql for an example.
-#
-sql_log {
- path = "${radacctdir}/sql-relay"
- acct_table = "radacct"
- postauth_table = "radpostauth"
- sql_user_name = "%{%{User-Name}:-DEFAULT}"
-
- #
- # Setting this to "yes" will allow UTF-8 characters to be
- # written to the log file. Otherwise, they are escaped
- # as being potentially invalid.
- #
- utf8 = no
-
- #
- # The names here are taken from the Acct-Status-Type names.
- # Just add another entry here for Accounting-On,
- # Accounting-Off, etc.
- #
- Start = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
- NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
- AcctSessionTime, AcctTerminateCause) VALUES \
- ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
- '%{Framed-IP-Address}', '%S', '0', '0', '');"
-
- Stop = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
- NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
- AcctSessionTime, AcctTerminateCause) VALUES \
- ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
- '%{Framed-IP-Address}', '0', '%S', '%{Acct-Session-Time}', \
- '%{Acct-Terminate-Cause}');"
-
- Alive = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
- NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
- AcctSessionTime, AcctTerminateCause) VALUES \
- ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
- '%{Framed-IP-Address}', '0', '0', '%{Acct-Session-Time}','');"
-
- # The same as "Alive"
- Interim-Update = "INSERT INTO ${acct_table} (AcctSessionId, UserName, \
- NASIPAddress, FramedIPAddress, AcctStartTime, AcctStopTime, \
- AcctSessionTime, AcctTerminateCause) VALUES \
- ('%{Acct-Session-Id}', '%{User-Name}', '%{NAS-IP-Address}', \
- '%{Framed-IP-Address}', '0', '0', '%{Acct-Session-Time}','');"
-
- Post-Auth = "INSERT INTO ${postauth_table} \
- (username, pass, reply, authdate) VALUES \
- ('%{User-Name}', '%{User-Password:-Chap-Password}', \
- '%{reply:Packet-Type}', '%S');"
-
- Accounting-On = "UPDATE ${acct_table} \
- SET \
- acctstoptime = '%S', \
- acctsessiontime = unix_timestamp('%S') - \
- unix_timestamp(acctstarttime), \
- acctterminatecause = '%{Acct-Terminate-Cause}', \
- acctstopdelay = %{%{Acct-Delay-Time}:-0} \
- WHERE acctstoptime IS NULL \
- AND nasipaddress = '%{NAS-IP-Address}' \
- AND acctstarttime <= '%S'""
-
- Accounting-Off = "UPDATE ${acct_table} \
- SET \
- acctstoptime = '%S', \
- acctsessiontime = unix_timestamp('%S') - \
- unix_timestamp(acctstarttime), \
- acctterminatecause = '%{Acct-Terminate-Cause}', \
- acctstopdelay = %{%{Acct-Delay-Time}:-0} \
- WHERE acctstoptime IS NULL \
- AND nasipaddress = '%{NAS-IP-Address}' \
- AND acctstarttime <= '%S'""
-}
-
--- /dev/null
+# Configuration for the SQL based IP Pool module (rlm_sqlippool)
+#
+# Database queries and main configuration are available at:
+#
+# raddb/sql/counter/<DB>/schema.sql
+# raddb/sql/counter/<DB>/queries.conf
+#
+# $Id$
+
+$INCLUDE sql/counter/mysql/queries.conf
+++ /dev/null
-# -*- text -*-
-#
-# $Id$
-
-#
-# Set an account to expire T seconds after first login.
-# Requires the Expire-After attribute to be set, in seconds.
-# You may need to edit raddb/dictionary to add the Expire-After
-# attribute.
-#
-# This example is for MySQL. Other SQL variants should be similar.
-#
-# For versions prior to 2.1.11, this module defined the following
-# expansion strings:
-#
-# %k key_name
-# %S sqlmod_inst
-#
-# These SHOULD NOT be used. If these are used in your configuration,
-# they should be replaced by the following strings, which will work
-# identically to the previous ones:
-#
-# %k ${key}
-# %S ${sqlmod-inst}
-#
-sqlcounter expire_on_login {
- counter-name = Expire-After-Initial-Login
- check-name = Expire-After
- sqlmod-inst = sql
- key = User-Name
- reset = never
- query = "SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \
- FROM radacct \
- WHERE UserName='%{${key}}' \
- ORDER BY acctstarttime \
- LIMIT 1;"
-}
-## Configuration for the SQL based IP Pool module (rlm_sqlippool)
-##
-## The database schemas are available at:
-##
-## raddb/sql/DB/ippool.sql
-##
-## $Id$
+# Configuration for the SQL based IP Pool module (rlm_sqlippool)
+#
+# The database schemas are available at:
+#
+# raddb/sql/ippool/<DB>/schema.sql
+#
+# $Id$
sqlippool {
-
- #########################################
- ## SQL instance to use (from sql.conf) ##
- ##
- ## If you have multiple sql instances, such as "sql sql1 {...}",
- ## use the *instance* name here: sql1.
- #########################################
- sql-instance-name = "sql"
-
- ## SQL table to use for ippool range and lease info
- ippool_table = "radippool"
-
- ## IP lease duration. (Leases expire even if Acct Stop packet is lost)
- lease-duration = 3600
-
- ## Attribute which should be considered unique per NAS
- ## Using NAS-Port gives behaviour similar to rlm_ippool. (And ACS)
- ## Using Calling-Station-Id works for NAS that send fixed NAS-Port
- ## ONLY change this if you know what you are doing!
- pool-key = "%{NAS-Port}"
- # pool-key = "%{Calling-Station-Id}"
-
- ################################################################
- #
- # WARNING: MySQL has certain limitations that means it can
- # hand out the same IP address to 2 different users.
- #
- # We suggest using an SQL DB with proper transaction
- # support, such as PostgreSQL, or using MySQL
- # with InnoDB.
- #
- ################################################################
-
- #
- # Use the same database as configured in the "sql" module, "database"
- # configuration item. Change the "postgresql" name below to be the
- # same as the "database" field of the SQL module referred to in the
- # "sql-instance-name", above.
- #
-$INCLUDE sql/postgresql/ippool.conf
-
- ## Logging configuration. (Comment out to disable logging)
- sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \
- (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
-
- sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} \
- (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
-
- sqlippool_log_clear = "Released IP %{Framed-IP-Address}\
- (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"
-
- sqlippool_log_failed = "IP Allocation FAILED from %{control:Pool-Name} \
- (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
-
- sqlippool_log_nopool = "No Pool-Name defined \
- (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
-
+ # SQL instance to use (from sql.conf)
+ #
+ # If you have multiple sql instances, such as "sql sql1 {...}",
+ # use the *instance* name here: sql1.
+ sql-instance-name = "sql"
+
+ # SQL table to use for ippool range and lease info
+ ippool_table = "radippool"
+
+ # IP lease duration. (Leases expire even if Acct Stop packet is lost)
+ lease-duration = 3600
+
+ # Attribute which should be considered unique per NAS
+ #
+ # Using NAS-Port gives behaviour similar to rlm_ippool. (And ACS)
+ # Using Calling-Station-Id works for NAS that send fixed NAS-Port
+ # ONLY change this if you know what you are doing!
+ pool-key = "%{NAS-Port}"
+ # pool-key = "%{Calling-Station-Id}"
+
+ ################################################################
+ #
+ # WARNING: MySQL (MyISAM) has certain limitations that means it can
+ # hand out the same IP address to 2 different users.
+ #
+ # We suggest using an SQL DB with proper transaction
+ # support, such as PostgreSQL, or using MySQL
+ # with InnoDB.
+ #
+ ################################################################
+
+ # Logging configuration. (Comment out to disable logging)
+ sqlippool_log_exists = "Existing IP: %{reply:Framed-IP-Address} \
+ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+ sqlippool_log_success = "Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} \
+ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+ sqlippool_log_clear = "Released IP %{Framed-IP-Address}\
+ (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"
+
+ sqlippool_log_failed = "IP Allocation FAILED from %{control:Pool-Name} \
+ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+ sqlippool_log_nopool = "No Pool-Name defined \
+ (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+ # Use the same database as configured in the "sql" module, "database"
+ # configuration item. Change the "postgresql" name below to be the
+ # same as the "database" field of the SQL module referred to in the
+ # "sql-instance-name", above.
+ #
+ $INCLUDE sql/ippool/${modules.${sql-instance-name}.dialect}/queries.conf
}
# -*- text -*-
##
-## counter.conf -- PostgreSQL queries for rlm_sqlcounter
+## counter.conf -- MySQL queries for rlm_sqlcounter
##
## $Id$
# totally dependent on the SQL module to process Accounting
# packets.
#
-# The 'sqlmod_inst' parameter holds the instance of the sql
+# The sql-module-instance' parameter holds the instance of the sql
# module to use when querying the SQL database. Normally it
# is just "sql". If you define more and one SQL module
# instance (usually for failover situations), you can
# The 'query' parameter specifies the SQL query used to get
# the current Counter value from the database. There are 3
# parameters that can be used in the query:
-# %k 'key' parameter
# %b unix time value of beginning of reset period
# %e unix time value of end of reset period
#
# Reply-Message = "You've used up more than one hour today"
#
sqlcounter dailycounter {
+ sql-module-instance = sql
+
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
- sqlmod-inst = sql
+
key = User-Name
reset = daily
# below
query = "SELECT SUM(acctsessiontime - \
GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
- FROM radacct WHERE username = '%{%k}' AND \
+ FROM radacct WHERE username = '%{${key}}' AND \
UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
# This query ignores calls that started in a previous
# reset period and continue into into this one. But it
# is a little easier on the SQL server
# query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
-# username = '%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')"
+# username = '%{${key}}' AND acctstarttime > FROM_UNIXTIME('%b')"
# This query is the same as above, but demonstrates an
# additional counter parameter '%e' which is the
# timestamp for the end of the period
# query = "SELECT SUM(acctsessiontime) FROM radacct \
-# WHERE username = '%{%k}' AND acctstarttime BETWEEN \
+# WHERE username = '%{${key}}' AND acctstarttime BETWEEN \
# FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
}
sqlcounter monthlycounter {
+ sql-module-instance = sql
+
counter-name = Monthly-Session-Time
- check-name = Max-Monthly-Session
- reply-name = Session-Timeout
- sqlmod-inst = sql
- key = User-Name
- reset = monthly
+ check-name = Max-Monthly-Session
+ reply-name = Session-Timeout
+ key = User-Name
+ reset = monthly
# This query properly handles calls that span from the
# previous reset period into the current period but
# below
query = "SELECT SUM(acctsessiontime - \
GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
- FROM radacct WHERE username='%{%k}' AND \
+ FROM radacct WHERE username='%{${key}}' AND \
UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"
# This query ignores calls that started in a previous
# reset period and continue into into this one. But it
# is a little easier on the SQL server
# query = "SELECT SUM(acctsessiontime) FROM radacct WHERE \
-# username='%{%k}' AND acctstarttime > FROM_UNIXTIME('%b')"
+# username='%{${key}}' AND acctstarttime > FROM_UNIXTIME('%b')"
# This query is the same as above, but demonstrates an
# additional counter parameter '%e' which is the
# timestamp for the end of the period
# query = "SELECT SUM(acctsessiontime) FROM radacct \
-# WHERE username='%{%k}' AND acctstarttime BETWEEN \
+# WHERE username='%{${key}}' AND acctstarttime BETWEEN \
# FROM_UNIXTIME('%b') AND FROM_UNIXTIME('%e')"
}
sqlcounter noresetcounter {
+ sql-module-instance = sql
+
counter-name = Max-All-Session-Time
- check-name = Max-All-Session
- sqlmod-inst = sql
- key = User-Name
- reset = never
- query = "SELECT IFNULL(SUM(AcctSessionTime),0) FROM radacct WHERE UserName='%{%k}'"
+ check-name = Max-All-Session
+ key = User-Name
+ reset = never
+ query = "SELECT IFNULL(SUM(AcctSessionTime),0) \
+ FROM radacct \
+ WHERE UserName='%{${key}}'"
}
-
+#
+# Set an account to expire T seconds after first login.
+# Requires the Expire-After attribute to be set, in seconds.
+# You may need to edit raddb/dictionary to add the Expire-After
+# attribute.
+sqlcounter expire_on_login {
+ sql-module-instance = sql
+
+ counter-name = Expire-After-Initial-Login
+ check-name = Expire-After
+ key = User-Name
+ reset = never
+ query = "SELECT TIME_TO_SEC(TIMEDIFF(NOW(), acctstarttime)) \
+ FROM radacct \
+ WHERE UserName='%{${key}}' \
+ ORDER BY acctstarttime \
+ LIMIT 1;"
+}
# totally dependent on the SQL module to process Accounting
# packets.
#
-# The 'sqlmod_inst' parameter holds the instance of the sql
+# The 'sql-module-instance' parameter holds the instance of the sql
# module to use when querying the SQL database. Normally it
# is just "sql". If you define more and one SQL module
# instance (usually for failover situations), you can
# The 'query' parameter specifies the SQL query used to get
# the current Counter value from the database. There are 3
# parameters that can be used in the query:
-# %k 'key' parameter
# %b unix time value of beginning of reset period
# %e unix time value of end of reset period
#
# Reply-Message = "You've used up more than one hour today"
#
sqlcounter dailycounter {
+ sql-module-instance = sql
+
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
- sqlmod-inst = sql
key = User-Name
reset = daily
# below
query = "SELECT SUM(AcctSessionTime - \
GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \
- FROM radacct WHERE UserName='%{%k}' AND \
+ FROM radacct WHERE UserName='%{${key}}' AND \
AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
# This query ignores calls that started in a previous
# reset period and continue into into this one. But it
# is a little easier on the SQL server
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
-# UserName='%{%k}' AND AND AcctStartTime::ABSTIME::INT4 > '%b'"
+# UserName='%{${key}}' AND AND AcctStartTime::ABSTIME::INT4 > '%b'"
# This query is the same as above, but demonstrates an
# additional counter parameter '%e' which is the
# timestamp for the end of the period
# query = "SELECT SUM(AcctSessionTime) FROM radacct \
-# WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 \
+# WHERE UserName='%{${key}}' AND AcctStartTime::ABSTIME::INT4 \
# BETWEEN '%b' AND '%e'"
}
sqlcounter monthlycounter {
+ sql-module-instance = sql
+
counter-name = Monthly-Session-Time
- check-name = Max-Monthly-Session
- reply-name = Session-Timeout
- sqlmod-inst = sql
- key = User-Name
- reset = monthly
+ check-name = Max-Monthly-Session
+ reply-name = Session-Timeout
+ key = User-Name
+ reset = monthly
# This query properly handles calls that span from the
# previous reset period into the current period but
# below
query = "SELECT SUM(AcctSessionTime - \
GREATER((%b - AcctStartTime::ABSTIME::INT4), 0)) \
- FROM radacct WHERE UserName='%{%k}' AND \
+ FROM radacct WHERE UserName='%{${key}}' AND \
AcctStartTime::ABSTIME::INT4 + AcctSessionTime > '%b'"
# reset period and continue into into this one. But it
# is a little easier on the SQL server
# query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE \
-# UserName='%{%k}' AND AND AcctStartTime::ABSTIME::INT4 > '%b'"
+# UserName='%{${key}}' AND AND AcctStartTime::ABSTIME::INT4 > '%b'"
# This query is the same as above, but demonstrates an
# additional counter parameter '%e' which is the
# timestamp for the end of the period
# query = "SELECT SUM(AcctSessionTime) FROM radacct \
-# WHERE UserName='%{%k}' AND AcctStartTime::ABSTIME::INT4 \
+# WHERE UserName='%{${key}}' AND AcctStartTime::ABSTIME::INT4 \
# BETWEEN '%b' AND '%e'"
}
sqlcounter noresetcounter {
+ sql-module-instance = sql
+
counter-name = Max-All-Session-Time
- check-name = Max-All-Session
- sqlmod-inst = sql
- key = User-Name
- reset = never
- query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'"
+ check-name = Max-All-Session
+ key = User-Name
+ reset = never
+
+ query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{${key}}'"
}
--- /dev/null
+# -*- text -*-
+##
+## ippool.conf -- MySQL queries for rlm_sqlippool
+##
+## $Id$
+
+# ## This series of queries allocates an IP address
+# allocate-clear = "UPDATE ${ippool_table} \
+# SET nasipaddress = '', pool_key = 0, \
+# callingstationid = '', username = '', \
+# expiry_time = NULL \
+# WHERE pool_key = '${pool-key}'"
+
+## This series of queries allocates an IP address
+## (Note: If your pool-key is set to Calling-Station-Id and not NAS-Port
+## then you may wish to delete the "AND nasipaddress = '%{Nas-IP-Address}'
+## from the WHERE clause)
+
+ allocate-clear = "UPDATE ${ippool_table} \
+ SET nasipaddress = '', pool_key = 0, \
+ callingstationid = '', username = '', \
+ expiry_time = NULL \
+ WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
+ AND nasipaddress = '%{Nas-IP-Address}'"
+
+
+
+## The ORDER BY clause of this query tries to allocate the same IP-address
+## which user had last session...
+allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
+ WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR expiry_time IS NULL) \
+ ORDER BY (username <> '%{User-Name}'), \
+ (callingstationid <> '%{Calling-Station-Id}'), \
+ expiry_time \
+ LIMIT 1 \
+ FOR UPDATE"
+
+# ## If you prefer to allocate a random IP address every time, i
+# ## use this query instead
+# allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
+# WHERE pool_name = '%{control:Pool-Name}' \
+# AND expiry_time IS NULL \
+# ORDER BY RAND() \
+# LIMIT 1 \
+# FOR UPDATE"
+
+
+
+## If an IP could not be allocated, check to see if the pool exists or not
+## This allows the module to differentiate between a full pool and no pool
+## Note: If you are not running redundant pool modules this query may be
+## commented out to save running this query every time an ip is not allocated.
+pool-check = "SELECT id FROM ${ippool_table} \
+ WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
+
+
+## This is the final IP Allocation query, which saves the allocated ip details
+allocate-update = "UPDATE ${ippool_table} \
+ SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
+ callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
+ expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
+ WHERE framedipaddress = '%I' AND expiry_time IS NULL"
+
+
+
+## This series of queries frees an IP number when an accounting
+## START record arrives
+start-update = "UPDATE ${ippool_table} \
+ SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
+ WHERE nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool-key}' \
+ AND username = '%{User-Name}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND framedipaddress = '%{Framed-IP-Address}'"
+
+## This series of queries frees an IP number when an accounting
+## STOP record arrives
+stop-clear = "UPDATE ${ippool_table} \
+ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
+ expiry_time = NULL \
+ WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
+ AND username = '%{User-Name}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND framedipaddress = '%{Framed-IP-Address}'"
+
+
+
+## This series of queries frees an IP number when an accounting
+## ALIVE record arrives
+alive-update = "UPDATE ${ippool_table} \
+ SET expiry_time = NOW() + INTERVAL ${lease-duration} SECOND \
+ WHERE nasipaddress = '%{Nas-IP-Address}' AND pool_key = '${pool-key}' \
+ AND username = '%{User-Name}' \
+ AND callingstationid = '%{Calling-Station-Id}' \
+ AND framedipaddress = '%{Framed-IP-Address}'"
+
+
+
+## This series of queries frees the IP numbers allocate to a
+## NAS when an accounting ON record arrives
+on-clear = "UPDATE ${ippool_table} \
+ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
+ expiry_time = NULL \
+ WHERE nasipaddress = '%{Nas-IP-Address}'"
+
+## This series of queries frees the IP numbers allocate to a
+## NAS when an accounting OFF record arrives
+off-clear = "UPDATE ${ippool_table} \
+ SET nasipaddress = '', pool_key = 0, callingstationid = '', username = '', \
+ expiry_time = NULL \
+ WHERE nasipaddress = '%{Nas-IP-Address}'"
+
--- /dev/null
+# -*- text -*-
+##
+## wimax.conf -- MySQL configuration for WiMAX keying
+##
+## $Id$
+
+# 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}"
+
+#######################################################################
+# Logging of WiMAX SPI -> key mappings
+#######################################################################
+# postauth_query - Insert some info after authentication
+#######################################################################
+
+postauth_query = "INSERT INTO wimax \
+ (username, authdate, spi, mipkey, lifetime) \
+ VALUES ( \
+ '%{User-Name}', '%S' \
+ '%{%{reply:WiMAX-MN-hHA-MIP4-SPI}:-%{reply:WiMAX-MN-hHA-MIP6-SPI}}', \
+ '%{%{reply:WiMAX-MN-hHA-MIP4-Key}:-%{reply:WiMAX-MN-hHA-MIP6-Key}}', '%{%{reply:Session-Timeout}:-86400}' )"
#
# Table structure for table 'radpostauth'
#
-
CREATE TABLE radpostauth (
id int(11) NOT NULL auto_increment,
username varchar(64) NOT NULL default '',
authdate timestamp NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
+
+#
+# Table structure for table 'nas'
+#
+CREATE TABLE nas (
+ id int(10) NOT NULL auto_increment,
+ nasname varchar(128) NOT NULL,
+ shortname varchar(32),
+ type varchar(30) DEFAULT 'other',
+ ports int(5),
+ secret varchar(60) DEFAULT 'secret' NOT NULL,
+ server varchar(64),
+ community varchar(50),
+ description varchar(200) DEFAULT 'RADIUS Client',
+ PRIMARY KEY (id),
+ KEY nasname (nasname)
+);
END;
/
+
+/*
+ * Table structure for table 'nas'
+ */
+CREATE TABLE nas (
+ id INT PRIMARY KEY,
+ nasname VARCHAR(128),
+ shortname VARCHAR(32),
+ type VARCHAR(30),
+ ports INT,
+ secret VARCHAR(60),
+ server VARCHAR(64),
+ community VARCHAR(50),
+ description VARCHAR(200)
+);
+CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;
+
CallingStationId VARCHAR(50),
authdate TIMESTAMP with time zone NOT NULL default 'now()'
);
+
+/*
+ * Table structure for table 'nas'
+ */
+CREATE TABLE nas (
+ id SERIAL PRIMARY KEY,
+ nasname VARCHAR(128) NOT NULL,
+ shortname VARCHAR(32) NOT NULL,
+ type VARCHAR(30) NOT NULL DEFAULT 'other',
+ ports int4,
+ secret VARCHAR(60) NOT NULL,
+ server VARCHAR(64),
+ community VARCHAR(50),
+ description VARCHAR(200)
+);
+create index nas_nasname on nas (nasname);
+++ /dev/null
-# -*- text -*-
-##
-## ippool-dhcp.conf -- MySQL queries for sqlippool-dhcp instance
-## Only post-auth method is used
-##
-## $Id$
-
-## This series of queries allocates an IP address
-## First, clear expired entries
- allocate-clear = "UPDATE ${ippool_table} \
- SET nasipaddress = '', pool_key = 0, \
- callingstationid = '', username = '', \
- expiry_time = NULL \
- WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
- "
-
-## Then find an available IP address
-## The ORDER BY clause of this query tries to allocate the same IP address
-## which user had last session regardless of expiry time
-## to handle DHCP request and duplicates from the same client
-allocate-find = "SELECT framedipaddress FROM ${ippool_table} \
- WHERE pool_name = '%{control:Pool-Name}' AND \
- ( \
- (callingstationid = '%{Calling-Station-Id}') OR \
- (expiry_time < NOW() OR expiry_time IS NULL) \
- ) \
- ORDER BY (callingstationid = '%{Calling-Station-Id}') DESC, \
- expiry_time DESC\
- LIMIT 1 \
- FOR UPDATE"
-
-## If an IP could not be allocated, check to see if the pool exists or not
-## This allows the module to differentiate between a full pool and no pool
-## Note: If you are not running redundant pool modules this query may be
-## commented out to save running this query every time an ip is not allocated.
-pool-check = "SELECT id FROM ${ippool_table} \
- WHERE pool_name='%{control:Pool-Name}' LIMIT 1"
-
-
-## This is the final IP Allocation query, which saves the allocated ip details
-allocate-update = "UPDATE ${ippool_table} \
- SET nasipaddress = '%{NAS-IP-Address}', pool_key = '${pool-key}', \
- callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', \
- calledstationid = 'Freeradius-DHCP', \
- expiry_time = GREATEST( \
- IF(ISNULL(expiry_time),'0000-00-00 00:00:00',expiry_time), \
- NOW() + INTERVAL ${lease-duration} SECOND) \
- WHERE framedipaddress = '%I' AND \
- ( \
- (callingstationid = '%{Calling-Station-Id}') OR \
- (expiry_time < NOW() OR expiry_time IS NULL) \
- ) \
- "
-
-## This series of queries frees an IP number when an accounting
-## START record arrives. Unused, but must be set to non-empty query
-start-update = "SELECT NOW()"
-
-## This series of queries frees an IP number when an accounting
-## STOP record arrives. Unused, but must be set to non-empty query
-stop-clear = "SELECT NOW()"
-
-## This series of queries frees an IP number when an accounting
-## ALIVE record arrives. Unused, but must be set to non-empty query
-alive-update = "SELECT NOW()"
-
-## This series of queries frees the IP numbers allocate to a
-## NAS when an accounting ON record arrives. Unused, but must be set
-## to non-empty query
-on-clear = "SELECT NOW()"
-
-## This series of queries frees the IP numbers allocate to a
-## NAS when an accounting OFF record arrives. Unused, but must be set
-## to non-empty query
-off-clear = "SELECT NOW()"
+++ /dev/null
-#
-# Table structure for table 'nas'
-#
-CREATE TABLE nas (
- id int(10) NOT NULL auto_increment,
- nasname varchar(128) NOT NULL,
- shortname varchar(32),
- type varchar(30) DEFAULT 'other',
- ports int(5),
- secret varchar(60) DEFAULT 'secret' NOT NULL,
- server varchar(64),
- community varchar(50),
- description varchar(200) DEFAULT 'RADIUS Client',
- PRIMARY KEY (id),
- KEY nasname (nasname)
-);
+++ /dev/null
-# -*- text -*-
-##
-## wimax.conf -- MySQL configuration for WiMAX keying
-##
-## $Id$
-
- # 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}"
-
- #######################################################################
- # Logging of WiMAX SPI -> key mappings
- #######################################################################
- # postauth_query - Insert some info after authentication
- #######################################################################
-
- postauth_query = "INSERT INTO wimax \
- (username, authdate, spi, mipkey, lifetime) \
- VALUES ( \
- '%{User-Name}', '%S' \
- '%{%{reply:WiMAX-MN-hHA-MIP4-SPI}:-%{reply:WiMAX-MN-hHA-MIP6-SPI}}', \
- '%{%{reply:WiMAX-MN-hHA-MIP4-Key}:-%{reply:WiMAX-MN-hHA-MIP6-Key}}', '%{%{reply:Session-Timeout}:-86400}' )"
+++ /dev/null
-/*
- * Table structure for table 'nas'
- */
-
-CREATE TABLE nas (
- id INT PRIMARY KEY,
- nasname VARCHAR(128),
- shortname VARCHAR(32),
- type VARCHAR(30),
- ports INT,
- secret VARCHAR(60),
- server VARCHAR(64),
- community VARCHAR(50),
- description VARCHAR(200)
-);
-CREATE SEQUENCE nas_seq START WITH 1 INCREMENT BY 1;
-
+++ /dev/null
-/*
- * Table structure for table 'nas'
- */
-CREATE TABLE nas (
- id SERIAL PRIMARY KEY,
- nasname VARCHAR(128) NOT NULL,
- shortname VARCHAR(32) NOT NULL,
- type VARCHAR(30) NOT NULL DEFAULT 'other',
- ports int4,
- secret VARCHAR(60) NOT NULL,
- server VARCHAR(64),
- community VARCHAR(50),
- description VARCHAR(200)
-);
-create index nas_nasname on nas (nasname);
{ "check-name", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,check_name), NULL, NULL },
{ "reply-name", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,reply_name), NULL, "Session-Timeout" },
{ "key", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,key_name), NULL, NULL },
- { "sqlmod-inst", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,sqlmod_inst), NULL, NULL },
+ { "sql-module-instance", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,sqlmod_inst), NULL, NULL },
{ "query", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,query), NULL, NULL },
{ "reset", PW_TYPE_STRING_PTR, offsetof(rlm_sqlcounter_t,reset), NULL, NULL },
{ NULL, -1, 0, NULL, NULL }