Use the RADIUS SQL IP Pool module to allocate addresses for DHCP

This commit adds MySQL-specific queries for DHCP in ippool-dhcp.conf,
a sample configuration for the sqlippool module in dhcp_sqlippool,
examples of using it in sites-available/dhcp,
and "glue" policies in policy.conf

diff --git a/raddb/modules/dhcp_sqlippool b/raddb/modules/dhcp_sqlippool
new file mode 100644 (file)
index 0000000..f1193de
--- /dev/null
+++ b/raddb/modules/dhcp_sqlippool
@@ -0,0 +1,30 @@
+##  Configuration for DHCP to use SQL IP Pools.
+##
+##  See sqlippool.conf for common configuration explanation
+##
+##  $Id$
+
+sqlippool sqlippool-dhcp {
+       sql-instance-name = "sql"
+
+       ippool_table = "radippool"
+
+       lease-duration = 7200
+
+       # Client's MAC address is mapped to Calling-Station-Id in policy.conf
+       pool-key = "%{Calling-Station-Id}"
+
+       # For now, it only works with MySQL.
+       $INCLUDE ${confdir}/sql/mysql/ippool-dhcp.conf
+ 
+       sqlippool_log_exists = "DHCP: Existing IP: %{reply:Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+       sqlippool_log_success = "DHCP: 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 = "DHCP: Released IP %{Framed-IP-Address} (did %{Called-Station-Id} cli %{Calling-Station-Id} user %{User-Name})"
+
+       sqlippool_log_failed = "DHCP: IP Allocation FAILED from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+       sqlippool_log_nopool = "DHCP: No Pool-Name defined (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})"
+
+}

diff --git a/raddb/policy.conf b/raddb/policy.conf
index 3955429..7a64ac8 100644 (file)
--- a/raddb/policy.conf
+++ b/raddb/policy.conf
@@ -333,4 +333,29 @@ policy {
                        noop
                }
        }
+
+       #  Assign compatibility data to request for sqlippool
+       dhcp_sqlippool.postauth {
+
+
+               #  Do some minor hacks to the request so that it looks
+               #  like a RADIUS request to the SQL IP Pool module.
+               update request {
+                       User-Name = "DHCP-%{DHCP-Client-Hardware-Address}"
+                       Calling-Station-Id = "%{DHCP-Client-Hardware-Address}"
+                       NAS-IP-Address = %{%{DHCP-Gateway-IP-Address}:-127.0.0.1}
+                       Acct-Status-Type = Start
+               }
+
+               #  Call the actual module
+               dhcp_sqlippool
+
+               #  Convert Framed-IP-Address to DHCP, but only if we
+               #  actually allocated an address.
+               if (ok) {
+                       update reply {
+                               DHCP-Your-IP-Address = "%{reply:Framed-IP-Address}"
+                       }
+               }
+       }
 }

diff --git a/raddb/sites-available/dhcp b/raddb/sites-available/dhcp
index 2639900..6f207ad 100644 (file)
--- a/raddb/sites-available/dhcp
+++ b/raddb/sites-available/dhcp
@@ -148,6 +148,9 @@ dhcp DHCP-Discover {
        # ...
        #}
 
+       #  Or, allocate IPs from the DHCP pool in SQL.
+#      dhcp_sqlippool
+
        ok
 }
 
@@ -180,6 +183,9 @@ dhcp DHCP-Request {
        # ...
        #}
 
+       #  Or, allocate IPs from the DHCP pool in SQL.
+#      dhcp_sqlippool
+
        ok
 }
 

diff --git a/raddb/sql/mysql/ippool-dhcp.conf b/raddb/sql/mysql/ippool-dhcp.conf
new file mode 100644 (file)
index 0000000..1716ba9
--- /dev/null
+++ b/raddb/sql/mysql/ippool-dhcp.conf
@@ -0,0 +1,75 @@
+# -*- 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()"