1# -*- text -*- 2# 3# ippool-dhcp/mysql/queries.conf -- MySQL queries for rlm_sqlippool 4# 5# $Id: 6aaecb1b2075f32ca9eacd32872f6c771885030a $ 6 7# ***************** 8# * DHCP DISCOVER * 9# ***************** 10 11# 12# This series of queries allocates an IP address 13 14# If using MySQL < 8.0.1 then remove SKIP LOCKED 15# 16# Attempt to find the most recent existing IP address for the client 17# 18allocate_existing = "\ 19 SELECT framedipaddress FROM ${ippool_table} \ 20 WHERE pool_name = '%{control:${pool_name}}' \ 21 AND pool_key = '${pool_key}' \ 22 AND `status` IN ('dynamic', 'static') \ 23 ORDER BY expiry_time DESC LIMIT 1 FOR UPDATE SKIP LOCKED" 24 25# 26# Determine whether the requested IP address is available 27# 28allocate_requested = "\ 29 SELECT framedipaddress FROM ${ippool_table} \ 30 WHERE pool_name = '%{control:${pool_name}}' \ 31 AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ 32 AND `status` = 'dynamic' \ 33 AND expiry_time < NOW() \ 34 FOR UPDATE SKIP LOCKED" 35 36# 37# If the existing address can't be found this query will be run to 38# find a free address 39# 40allocate_find = "\ 41 SELECT framedipaddress FROM ${ippool_table} \ 42 WHERE pool_name = '%{control:${pool_name}}' \ 43 AND expiry_time < NOW() \ 44 AND `status` = 'dynamic' \ 45 ORDER BY expiry_time LIMIT 1 FOR UPDATE SKIP LOCKED" 46 47# 48# The ORDER BY clause of this query tries to allocate the same IP-address 49# which the user last had. Ensure that pool_key is unique to the user 50# within a given pool. 51# 52 53# 54# Alternatively do the operations in one query. Depending on transaction 55# isolation mode, this can cause deadlocks 56# 57#allocate_find = "\ 58# (SELECT framedipaddress, 1 AS o FROM ${ippool_table} \ 59# WHERE pool_name = '%{control:${pool_name}}' \ 60# AND pool_key = '${pool_key}' \ 61# AND `status` IN ('dynamic', 'static') \ 62# ORDER BY expiry_time DESC LIMIT 1 FOR UPDATE SKIP LOCKED \ 63# ) UNION ( \ 64# SELECT framedipaddress, 2 AS o FROM ${ippool_table} \ 65# WHERE pool_name = '%{control:${pool_name}}' \ 66# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ 67# AND `status` = 'dynamic' \ 68# AND ( pool_key = '${pool_key}' OR expiry_time < NOW() ) \ 69# FOR UPDATE SKIP LOCKED \ 70# ) UNION ( \ 71# SELECT framedipaddress, 3 AS o FROM ${ippool_table} \ 72# WHERE pool_name = '%{control:${pool_name}}' \ 73# AND expiry_time < NOW() \ 74# AND `status` = 'dynamic' \ 75# ORDER BY expiry_time LIMIT 1 FOR UPDATE SKIP LOCKED \ 76# ) ORDER BY o \ 77# LIMIT 1" 78 79# 80# If you prefer to allocate a random IP address every time, use this query instead. 81# Note: This is very slow if you have a lot of free IPs. 82# 83#allocate_find = "\ 84# SELECT framedipaddress FROM ${ippool_table} \ 85# WHERE pool_name = '%{control:${pool_name}}' \ 86# AND expiry_time < NOW() \ 87# AND `status` = 'dynamic' \ 88# ORDER BY \ 89# RAND() \ 90# LIMIT 1 \ 91# FOR UPDATE" 92 93# 94# The above query again, but with SKIP LOCKED. This requires MySQL >= 8.0.1, 95# and InnoDB. 96# 97#allocate_find = "\ 98# SELECT framedipaddress FROM ${ippool_table} \ 99# WHERE pool_name = '%{control:${pool_name}}' \ 100# AND expiry_time < NOW() \ 101# AND `status` = 'dynamic' \ 102# ORDER BY \ 103# RAND() \ 104# LIMIT 1 \ 105# FOR UPDATE SKIP LOCKED" 106 107# 108# If an IP could not be allocated, check to see if the pool exists or not 109# This allows the module to differentiate between a full pool and no pool 110# Note: If you are not running redundant pool modules this query may be 111# commented out to save running this query every time an ip is not allocated. 112# 113pool_check = "\ 114 SELECT id \ 115 FROM ${ippool_table} \ 116 WHERE pool_name='%{control:${pool_name}}' \ 117 LIMIT 1" 118 119# 120# This is the final IP Allocation query, which saves the allocated ip details. 121# 122allocate_update = "\ 123 UPDATE ${ippool_table} \ 124 SET \ 125 gateway = '%{DHCP-Gateway-IP-Address}', pool_key = '${pool_key}', \ 126 expiry_time = NOW() + INTERVAL ${offer_duration} SECOND \ 127 WHERE framedipaddress = '%I'" 128 129# 130# Use a stored procedure to find AND allocate the address. Read and customise 131# `procedure.sql` in this directory to determine the optimal configuration. 132# 133#allocate_begin = "" 134#allocate_find = "\ 135# CALL fr_dhcp_allocate_previous_or_new_framedipaddress( \ 136# '%{control:${pool_name}}', \ 137# '%{DHCP-Gateway-IP-Address}', \ 138# '${pool_key}', \ 139# ${offer_duration}, \ 140# '%{%{${req_attribute_name}}:-0.0.0.0}' \ 141# )" 142#allocate_update = "" 143#allocate_commit = "" 144 145 146# **************** 147# * DHCP REQUEST * 148# **************** 149 150# 151# This query revokes any active offers for addresses that a client is not 152# requesting when a DHCP REQUEST packet arrives 153# 154start_update = "\ 155 UPDATE ${ippool_table} \ 156 SET \ 157 gateway = '', \ 158 pool_key = '', \ 159 expiry_time = NOW() \ 160 WHERE pool_name = '%{control:${pool_name}}' \ 161 AND pool_key = '${pool_key}' \ 162 AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \ 163 AND expiry_time > NOW() \ 164 AND `status` = 'dynamic'" 165 166# 167# This query extends an existing lease (or offer) when a DHCP REQUEST packet 168# arrives. This query must update a row when a lease is succesfully requested 169# - queries that update no rows will result in a "notfound" response to 170# the module which by default will give a DHCP-NAK reply. In this example 171# incrementing "counter" is used to achieve this. 172# 173alive_update = "\ 174 UPDATE ${ippool_table} \ 175 SET \ 176 expiry_time = NOW() + INTERVAL ${lease_duration} SECOND, \ 177 counter = counter + 1 \ 178 WHERE pool_name = '%{control:${pool_name}}' \ 179 AND pool_key = '${pool_key}' \ 180 AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'" 181 182 183# **************** 184# * DHCP RELEASE * 185# **************** 186 187# 188# This query frees an IP address when a DHCP RELEASE packet arrives 189# 190stop_clear = "\ 191 UPDATE ${ippool_table} \ 192 SET \ 193 gateway = '', \ 194 pool_key = '', \ 195 expiry_time = NOW() \ 196 WHERE pool_name = '%{control:${pool_name}}' \ 197 AND pool_key = '${pool_key}' \ 198 AND framedipaddress = '%{DHCP-Client-IP-Address}' \ 199 AND `status` = 'dynamic'" 200 201 202# 203# This query is not applicable to DHCP 204# 205on_clear = "" 206 207 208# **************** 209# * DHCP DECLINE * 210# **************** 211 212# 213# This query marks an IP address as declined when a DHCP Decline 214# packet arrives 215# 216off_clear = "\ 217 UPDATE ${ippool_table} \ 218 SET status = 'declined' \ 219 WHERE pool_name = '%{control:${pool_name}}' \ 220 AND pool_key = '${pool_key}' \ 221 AND framedipaddress = '%{DHCP-Requested-IP-Address}'" 222