/etc/freeradius/3.0/mods-config/sql/ippool-dhcp/mysql/queries.conf is in freeradius-config 3.0.12+dfsg-5+deb9u1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 | # -*- text -*-
#
# ippool-dhcp/mysql/queries.conf -- MySQL queries for rlm_sqlippool
#
# $Id: d4d390afc633cf220ba00f796bb5d6ac453e0c49 $
#
# This series of queries allocates an IP address
#
#allocate_clear = "\
# UPDATE ${ippool_table} \
# SET \
# nasipaddress = '', \
# pool_key = 0, \
# callingstationid = '', \
# username = '', \
# expiry_time = NOW() \
# 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 = NOW() \
WHERE expiry_time <= NOW() - INTERVAL 1 SECOND \
AND nasipaddress = '%{Nas-IP-Address}'"
#
# Search for the SAME Calling-Station-ID as last time, OR an entry
# where there is no Calling-Station-Id.
#
# If the lease was expired, the allocate_clear above will reset the
# Calling-Station-Id to ''.
#
# If the lease wasn't expired, we want to give the user the same IP
# as last time.
#
# Then, we order by expiry_time, so that when there is no existing
# entry for the Calling-Station-Id, it picks the OLDEST expired IP.
#
allocate_find = "\
SELECT framedipaddress \
FROM ${ippool_table} \
WHERE pool_name = '%{control:Pool-Name}' \
AND (callingstationid = '%{Calling-Station-Id}' or callingstationid = '') \
ORDER BY \
(callingstationid <> '%{Calling-Station-Id}'), \
expiry_time \
LIMIT 1 \
FOR UPDATE"
#
# If you prefer to allocate a random IP address every time, use this query instead
#
#allocate_find = "\
# SELECT framedipaddress \
# FROM ${ippool_table} \
# WHERE pool_name = '%{control:Pool-Name}' \
# AND expiry_time < NOW() \
# 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'"
#
# 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 = NOW() \
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 = NOW() \
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 = NOW() \
WHERE nasipaddress = '%{Nas-IP-Address}'"
|