1# -*- text -*- 2# 3# ippool-dhcp/postgresql/queries.conf -- PostgreSQL queries for rlm_sqlippool 4# 5# $Id: 632fc7040f5912a289641440faba8accc9d27a0e $ 6 7# ***************** 8# * DHCP DISCOVER * 9# ***************** 10 11# 12# Use a stored procedure to find AND allocate the address. Read and customise 13# `procedure.sql` in this directory to determine the optimal configuration. 14# 15# This requires PostgreSQL >= 9.5 as SKIP LOCKED is used. 16# 17# The "NO LOAD BALANCE" comment is included here to indicate to a PgPool 18# system that this needs to be a write transaction. PgPool itself cannot 19# detect this from the statement alone. If you are using PgPool and do not 20# have this comment, the query may go to a read only server, and will fail. 21# This has no negative effect if you are not using PgPool. 22# 23allocate_begin = "" 24allocate_find = "\ 25 /*NO LOAD BALANCE*/ \ 26 SELECT fr_dhcp_allocate_previous_or_new_framedipaddress( \ 27 '%{control:${pool_name}}', \ 28 '%{DHCP-Gateway-IP-Address}', \ 29 '${pool_key}', \ 30 '${offer_duration}', \ 31 '%{%{${req_attribute_name}}:-0.0.0.0}' \ 32 )" 33allocate_update = "" 34allocate_commit = "" 35 36# 37# If stored procedures are not able to be used, the following queries can 38# be used. 39# Comment out all the above queries and choose the appropriate "allocate_find" 40# to match the desired outcome and also the version of "allocate_update" below. 41# 42 43# 44# This sequence of queries allocates an IP address from the Pool 45# 46#allocate_begin = "BEGIN" 47 48 49# Attempt to find the most recent existing IP address for the client 50# 51#allocate_existing = "\ 52# SELECT framedipaddress FROM ${ippool_table} \ 53# WHERE pool_name = '%{control:${pool_name}}' \ 54# AND pool_key = '${pool_key}' \ 55# AND status IN ('dynamic', 'static') \ 56# ORDER BY expiry_time DESC \ 57# LIMIT 1 \ 58# FOR UPDATE" 59 60# The same query with SKIP LOCKED - requires PostgreSQL >= 9.5 61# allocate_existing = "\ 62# SELECT framedipaddress FROM ${ippool_table} \ 63# WHERE pool_name = '%{control:${pool_name}}' \ 64# AND pool_key = '${pool_key}' \ 65# AND status IN ('dynamic', 'static') \ 66# ORDER BY expiry_time DESC \ 67# LIMIT 1 \ 68# FOR UPDATE SKIP LOCKED" 69 70 71# 72# Determine whether the requested IP address is available 73# 74#allocate_requested = "\ 75# SELECT framedipaddress FROM ${ippool_table} \ 76# WHERE pool_name = '%{control:${pool_name}}' \ 77# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ 78# AND status = 'dynamic' \ 79# AND expiry_time < 'now'::timestamp(0) \ 80# FOR UPDATE" 81 82# The same query with SKIP LOCKED - requires PostgreSQL >= 9.5 83#allocate_requested = "\ 84# SELECT framedipaddress FROM ${ippool_table} \ 85# WHERE pool_name = '%{control:${pool_name}}' \ 86# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ 87# AND status = 'dynamic' \ 88# AND expiry_time < 'now'::timestamp(0) \ 89# FOR UPDATE SKIP LOCKED" 90 91 92# 93# If the existing address can't be found this query will be run to 94# find a free address 95# 96#allocate_find = "\ 97# SELECT framedipaddress FROM ${ippool_table} \ 98# WHERE pool_name = '%{control:${pool_name}}' \ 99# AND expiry_time < 'now'::timestamp(0) \ 100# AND status = 'dynamic' \ 101# ORDER BY expiry_time \ 102# LIMIT 1 \ 103# FOR UPDATE" 104 105# The same query with SKIP LOCKED - requires PostgreSQL >= 9.5 106#allocate_find = "\ 107# SELECT framedipaddress FROM ${ippool_table} \ 108# WHERE pool_name = '%{control:${pool_name}}' \ 109# AND expiry_time < 'now'::timestamp(0) \ 110# AND status = 'dynamic' \ 111# ORDER BY expiry_time \ 112# LIMIT 1 \ 113# FOR UPDATE SKIP LOCKED" 114 115# 116# If you prefer to allocate a random IP address every time, use this query instead 117# Note: This is very slow if you have a lot of free IPs. 118# Use of either of these next two queries should have the allocate_begin line commented out 119# and allocate_update below un-commented. 120# 121#allocate_find = "\ 122# SELECT framedipaddress FROM ${ippool_table} \ 123# WHERE pool_name = '%{control:${pool_name}}' AND expiry_time < 'now'::timestamp(0) \ 124# AND status = 'dynamic' \ 125# ORDER BY RANDOM() \ 126# LIMIT 1 \ 127# FOR UPDATE" 128 129# 130# The above query again, but with SKIP LOCKED. This requires PostgreSQL >= 9.5. 131# 132#allocate_find = "\ 133# SELECT framedipaddress FROM ${ippool_table} \ 134# WHERE pool_name = '%{control:${pool_name}}' AND expiry_time < 'now'::timestamp(0) \ 135# AND status = 'dynamic' \ 136# ORDER BY RANDOM() \ 137# LIMIT 1 \ 138# FOR UPDATE SKIP LOCKED" 139 140# 141# This query marks the IP address handed out by "allocate-find" as used 142# for the period of "lease_duration" after which time it may be reused. 143# 144#allocate_update = "\ 145# UPDATE ${ippool_table} \ 146# SET \ 147# gateway = '%{DHCP-Gateway-IP-Address}', \ 148# pool_key = '${pool_key}', \ 149# expiry_time = 'now'::timestamp(0) + '${offer_duration} second'::interval \ 150# WHERE framedipaddress = '%I'" 151 152 153# 154# Alternatively, merge the matching of existing IP and free IP into a single query 155# This version does the update as well - so allocate_begin, allocate_update and 156# allocate_commit should be blank 157# 158#allocate_begin = "" 159#allocate_find = "\ 160# WITH found AS ( \ 161# WITH existing AS ( \ 162# SELECT framedipaddress FROM ${ippool_table} \ 163# WHERE pool_name = '%{control:${pool_name}}' \ 164# AND pool_key = '${pool_key}' \ 165# ORDER BY expiry_time DESC \ 166# LIMIT 1 \ 167# FOR UPDATE SKIP LOCKED \ 168# ), requested AS ( \ 169# SELECT framedipaddress FROM ${ippool_table} \ 170# WHERE pool_name = '%{control:${pool_name}}' \ 171# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ 172# AND status = 'dynamic' \ 173# AND ( pool_key = '${pool_key}' OR expiry_time < 'now'::timestamp(0) ) \ 174# FOR UPDATE SKIP LOCKED \ 175# ), new AS ( \ 176# SELECT framedipaddress FROM ${ippool_table} \ 177# WHERE pool_name = '%{control:${pool_name}}' \ 178# AND expiry_time < 'now'::timestamp(0) \ 179# AND status = 'dynamic' \ 180# ORDER BY expiry_time \ 181# LIMIT 1 \ 182# FOR UPDATE SKIP LOCKED \ 183# ) \ 184# SELECT framedipaddress, 1 AS o FROM existing \ 185# UNION ALL \ 186# SELECT framedipaddress, 2 AS o FROM requested \ 187# UNION ALL \ 188# SELECT framedipaddress, 3 AS o FROM new \ 189# ORDER BY o LIMIT 1 \ 190# ) \ 191# UPDATE ${ippool_table} \ 192# SET pool_key = '${pool_key}', \ 193# expiry_time = 'now'::timestamp(0) + '${offer_duration} second'::interval, \ 194# gateway = '%{DHCP-Gateway-IP-Address}' \ 195# FROM found \ 196# WHERE found.framedipaddress = ${ippool_table}.framedipaddress \ 197# RETURNING found.framedipaddress" 198#allocate_update = "" 199#allocate_commit = "" 200 201 202# 203# If an IP could not be allocated, check to see whether the pool exists or not 204# This allows the module to differentiate between a full pool and no pool 205# Note: If you are not running redundant pool modules this query may be commented 206# out to save running this query every time an ip is not allocated. 207# 208pool_check = "\ 209 SELECT id \ 210 FROM ${ippool_table} \ 211 WHERE pool_name='%{control:${pool_name}}' \ 212 LIMIT 1" 213 214 215# **************** 216# * DHCP REQUEST * 217# **************** 218 219# 220# This query revokes any active offers for addresses that a client is not 221# requesting when a DHCP REQUEST packet arrives, i.e, each server (sharing the 222# same database) may have simultaneously offered a unique address. 223# 224start_update = "\ 225 UPDATE ${ippool_table} \ 226 SET \ 227 gateway = '', \ 228 pool_key = '', \ 229 expiry_time = 'now'::timestamp(0) - '1 second'::interval \ 230 WHERE pool_name = '%{control:${pool_name}}' \ 231 AND pool_key = '${pool_key}' \ 232 AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \ 233 AND expiry_time > 'now'::timestamp(0) \ 234 AND status = 'dynamic'" 235 236# 237# This query extends an existing lease (or offer) when a DHCP REQUEST packet 238# arrives. This query must update a row when a lease is succesfully requested 239# - queries that update no rows will result in a "notfound" response to 240# the module which by default will give a DHCP-NAK reply. In this example 241# incrementing "counter" is used to achieve this. 242# 243alive_update = "\ 244 UPDATE ${ippool_table} \ 245 SET \ 246 expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval, \ 247 counter = counter + 1 \ 248 WHERE pool_name = '%{control:${pool_name}}' \ 249 AND pool_key = '${pool_key}' \ 250 AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'" 251 252 253# **************** 254# * DHCP RELEASE * 255# **************** 256 257# 258# This query frees an IP address when a DHCP RELEASE packet arrives 259# 260stop_clear = "\ 261 UPDATE ${ippool_table} \ 262 SET \ 263 gateway = '', \ 264 pool_key = '', \ 265 expiry_time = 'now'::timestamp(0) - '1 second'::interval \ 266 WHERE pool_name = '%{control:${pool_name}}' \ 267 AND pool_key = '${pool_key}' \ 268 AND framedipaddress = '%{DHCP-Client-IP-Address}' \ 269 AND status = 'dynamic'" 270 271 272# 273# This query is not applicable to DHCP 274# 275on_clear = "" 276 277 278# **************** 279# * DHCP DECLINE * 280# **************** 281 282# 283# This query marks an IP address as declined when a DHCP DECLINE packet 284# arrives 285# 286off_clear = "\ 287 UPDATE ${ippool_table} \ 288 SET status = 'declined' \ 289 WHERE pool_name = '%{control:${pool_name}}' \ 290 AND pool_key = '${pool_key}' \ 291 AND framedipaddress = '%{DHCP-Requested-IP-Address}'" 292