diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool-dhcp/mysql')
4 files changed, 546 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool-dhcp/mysql/procedure-no-skip-locked.sql b/raddb/mods-config/sql/ippool-dhcp/mysql/procedure-no-skip-locked.sql new file mode 100644 index 0000000..bee37de --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mysql/procedure-no-skip-locked.sql @@ -0,0 +1,160 @@ +-- +-- A stored procedure to reallocate a user's previous address, otherwise +-- provide a free address. +-- +-- NOTE: This version of the SP is intended for MySQL variants that do not +-- support the SKIP LOCKED pragma, i.e. MariaDB and versions of MySQL +-- prior to 8.0. It should be a lot faster than using the default SP +-- without the SKIP LOCKED pragma under highly concurrent workloads +-- and not result in thread starvation. +-- +-- It is however a *useful hack* which should not be used if SKIP +-- LOCKED is available. +-- +-- WARNING: This query uses server-local, "user locks" (GET_LOCK and +-- RELEASE_LOCK), without the need for a transaction, to emulate +-- row locking with locked-row skipping. User locks are not +-- supported on clusters such as Galera and MaxScale. +-- +-- Using this SP reduces the usual set dialogue of queries to a single +-- query: +-- +-- START TRANSACTION; SELECT FOR UPDATE; UPDATE; COMMIT; -> CALL sp() +-- +-- The stored procedure is executed within a single round trip which often +-- leads to reduced deadlocking and significant performance improvements. +-- +-- To use this stored procedure the corresponding queries.conf statements must +-- be configured as follows: +-- +-- allocate_begin = "" +-- allocate_find = "\ +-- CALL fr_dhcp_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{DHCP-Gateway-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration}, \ +-- '%{%{${req_attribute_name}}:-0.0.0.0}' \ +-- )" +-- allocate_update = "" +-- allocate_commit = "" +-- + +DELIMITER $$ + +DROP PROCEDURE IF EXISTS fr_dhcp_allocate_previous_or_new_framedipaddress; +CREATE PROCEDURE fr_allocate_previous_or_new_framedipaddress ( + IN v_pool_name VARCHAR(64), + IN v_gateway VARCHAR(15), + IN v_pool_key VARCHAR(64), + IN v_lease_duration INT, + IN v_requested_address VARCHAR(15) +) +SQL SECURITY INVOKER +proc:BEGIN + DECLARE r_address VARCHAR(15); + + -- Reissue an existing IP address lease when re-authenticating a session + -- + -- Note: In this query we get away without the need for FOR UPDATE + -- becase: + -- + -- (a) Each existing lease only belongs to a single device, so + -- no two devices will be racing over a single address. + -- (b) The set of existing leases (not yet expired) are + -- disjoint from the set of free leases, so not subject to + -- reallocation. + -- + SELECT framedipaddress INTO r_address + FROM dhcpippool + WHERE pool_name = v_pool_name + AND expiry_time > NOW() + AND pool_key = v_pool_key + AND `status` IN ('dynamic', 'static') + LIMIT 1; + + -- Reissue an user's previous IP address, provided that the lease is + -- available (i.e. enable sticky IPs) + -- + -- When using this SELECT you should delete the one above. You must also + -- set allocate_clear = "" in queries.conf to persist the associations + -- for expired leases. + -- + -- SELECT framedipaddress INTO r_address + -- FROM dhcpippool + -- WHERE pool_name = v_pool_name + -- AND pool_key = v_pool_key + -- AND `status` IN ('dynamic', 'static') + -- LIMIT 1; + + -- + -- Normally here we would honour an IP address hint if the IP were + -- available, however we cannot do that without taking a lock which + -- defeats the purpose of this version of the stored procedure. + -- + -- It you need to honour an IP address hint then use a database with + -- support for SKIP LOCKED and use the normal stored procedure. + -- + + IF r_address IS NOT NULL THEN + UPDATE dhcpippool + SET + gateway = v_gateway, + pool_key = v_pool_key, + expiry_time = NOW() + INTERVAL v_lease_duration SECOND + WHERE + framedipaddress = r_address; + SELECT r_address; + LEAVE proc; + END IF; + + REPEAT + + -- If we didn't reallocate a previous address then pick the least + -- recently used address from the pool which maximises the likelihood + -- of re-assigning the other addresses to their recent user + -- + SELECT framedipaddress INTO r_address + FROM dhcpippool + WHERE pool_name = v_pool_name + AND expiry_time < NOW() + AND `status` = 'dynamic' + -- + -- WHERE ... GET_LOCK(...,0) = 1 is a poor man's SKIP LOCKED that simulates + -- a row-level lock using a "user lock" that allows the locked "rows" to be + -- skipped. After the user lock is acquired and the SELECT retired it does + -- not mean that the entirety of the WHERE clause is still true: Another + -- thread may have updated the expiry time and released the lock after we + -- checked the expiry_time but before we acquired the lock since SQL is free + -- to reorder the WHERE condition. Therefore we must recheck the condition + -- in the UPDATE statement below to detect this race. + -- + AND GET_LOCK(CONCAT('dhcpippool_', framedipaddress), 0) = 1 + LIMIT 1; + + IF r_address IS NULL THEN + DO RELEASE_LOCK(CONCAT('dhcpippool_', r_address)); + LEAVE proc; + END IF; + + UPDATE dhcpippool + SET + gateway = v_gateway, + pool_key = v_pool_key, + expiry_time = NOW() + INTERVAL v_lease_duration SECOND + WHERE + framedipaddress = r_address + -- + -- Here we re-evaluate the original condition for selecting the address + -- to detect a race, in which case we try again... + -- + AND expiry_time<NOW(); + + UNTIL ROW_COUNT() <> 0 END REPEAT; + + DO RELEASE_LOCK(CONCAT('dhcpippool_', r_address)); + SELECT r_address; + +END$$ + +DELIMITER ; diff --git a/raddb/mods-config/sql/ippool-dhcp/mysql/procedure.sql b/raddb/mods-config/sql/ippool-dhcp/mysql/procedure.sql new file mode 100644 index 0000000..b5dfae0 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mysql/procedure.sql @@ -0,0 +1,144 @@ +-- +-- A stored procedure to reallocate a user's previous address, otherwise +-- provide a free address. +-- +-- Using this SP reduces the usual set dialogue of queries to a single +-- query: +-- +-- START TRANSACTION; SELECT FOR UPDATE; UPDATE; COMMIT; -> CALL sp() +-- +-- The stored procedure is executed on an database instance within a single +-- round trip which often leads to reduced deadlocking and significant +-- performance improvements especially on multi-master clusters, perhaps even +-- by an order of magnitude or more. +-- +-- To use this stored procedure the corresponding queries.conf statements must +-- be configured as follows: +-- +-- allocate_begin = "" +-- allocate_find = "\ +-- CALL fr_dhcp_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{DHCP-Gateway-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration}, \ +-- '%{%{${req_attribute_name}}:-0.0.0.0}' \ +-- )" +-- allocate_update = "" +-- allocate_commit = "" +-- + +DELIMITER $$ + +DROP PROCEDURE IF EXISTS fr_dhcp_allocate_previous_or_new_framedipaddress; +CREATE PROCEDURE fr_dhcp_allocate_previous_or_new_framedipaddress ( + IN v_pool_name VARCHAR(30), + IN v_gateway VARCHAR(15), + IN v_pool_key VARCHAR(30), + IN v_lease_duration INT, + IN v_requested_address VARCHAR(15) +) +SQL SECURITY INVOKER +proc:BEGIN + DECLARE r_address VARCHAR(15); + + DECLARE EXIT HANDLER FOR SQLEXCEPTION + BEGIN + ROLLBACK; + RESIGNAL; + END; + + SET TRANSACTION ISOLATION LEVEL READ COMMITTED; + + START TRANSACTION; + + -- Reissue an existing IP address lease when re-authenticating a session + -- + SELECT framedipaddress INTO r_address + FROM dhcpippool + WHERE pool_name = v_pool_name + AND expiry_time > NOW() + AND pool_key = v_pool_key + AND `status` IN ('dynamic', 'static') + LIMIT 1 + FOR UPDATE; +-- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support + + -- NOTE: You should enable SKIP LOCKED here (as well as any other + -- instances) if your database server supports it. If it is not + -- supported and you are not running a multi-master cluster (e.g. + -- Galera or MaxScale) then you should instead consider using the + -- SP in procedure-no-skip-locked.sql which will be faster and + -- less likely to result in thread starvation under highly + -- concurrent load. + + -- Reissue an user's previous IP address, provided that the lease is + -- available (i.e. enable sticky IPs) + -- + -- When using this SELECT you should delete the one above. You must also + -- set allocate_clear = "" in queries.conf to persist the associations + -- for expired leases. + -- + -- SELECT framedipaddress INTO r_address + -- FROM dhcpippool + -- WHERE pool_name = v_pool_name + -- AND pool_key = v_pool_key + -- AND `status` IN ('dynamic', 'static') + -- LIMIT 1 + -- FOR UPDATE; + -- -- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support + + -- Issue the requested IP address if it is available + -- + IF r_address IS NULL AND v_requested_address <> '0.0.0.0' THEN + SELECT framedipaddress INTO r_address + FROM dhcpippool + WHERE pool_name = v_pool_name + AND framedipaddress = v_requested_address + AND `status` = 'dynamic' + AND ( pool_key = v_pool_key OR expiry_time < NOW() ) + FOR UPDATE; +-- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support + END IF; + + -- If we didn't reallocate a previous address then pick the least + -- recently used address from the pool which maximises the likelihood + -- of re-assigning the other addresses to their recent user + -- + IF r_address IS NULL THEN + SELECT framedipaddress INTO r_address + FROM dhcpippool + WHERE pool_name = v_pool_name + AND expiry_time < NOW() + AND `status` = 'dynamic' + ORDER BY + expiry_time + LIMIT 1 + FOR UPDATE; +-- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support + END IF; + + -- Return nothing if we failed to allocated an address + -- + IF r_address IS NULL THEN + COMMIT; + LEAVE proc; + END IF; + + -- Update the pool having allocated an IP address + -- + UPDATE dhcpippool + SET + gateway = v_gateway, + pool_key = v_pool_key, + expiry_time = NOW() + INTERVAL v_lease_duration SECOND + WHERE framedipaddress = r_address; + + COMMIT; + + -- Return the address that we allocated + SELECT r_address; + +END$$ + +DELIMITER ; diff --git a/raddb/mods-config/sql/ippool-dhcp/mysql/queries.conf b/raddb/mods-config/sql/ippool-dhcp/mysql/queries.conf new file mode 100644 index 0000000..6aaecb1 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mysql/queries.conf @@ -0,0 +1,221 @@ +# -*- text -*- +# +# ippool-dhcp/mysql/queries.conf -- MySQL queries for rlm_sqlippool +# +# $Id$ + +# ***************** +# * DHCP DISCOVER * +# ***************** + +# +# This series of queries allocates an IP address + +# If using MySQL < 8.0.1 then remove SKIP LOCKED +# +# Attempt to find the most recent existing IP address for the client +# +allocate_existing = "\ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND `status` IN ('dynamic', 'static') \ + ORDER BY expiry_time DESC LIMIT 1 FOR UPDATE SKIP LOCKED" + +# +# Determine whether the requested IP address is available +# +allocate_requested = "\ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ + AND `status` = 'dynamic' \ + AND expiry_time < NOW() \ + FOR UPDATE SKIP LOCKED" + +# +# If the existing address can't be found this query will be run to +# find a free address +# +allocate_find = "\ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < NOW() \ + AND `status` = 'dynamic' \ + ORDER BY expiry_time LIMIT 1 FOR UPDATE SKIP LOCKED" + +# +# The ORDER BY clause of this query tries to allocate the same IP-address +# which the user last had. Ensure that pool_key is unique to the user +# within a given pool. +# + +# +# Alternatively do the operations in one query. Depending on transaction +# isolation mode, this can cause deadlocks +# +#allocate_find = "\ +# (SELECT framedipaddress, 1 AS o FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND pool_key = '${pool_key}' \ +# AND `status` IN ('dynamic', 'static') \ +# ORDER BY expiry_time DESC LIMIT 1 FOR UPDATE SKIP LOCKED \ +# ) UNION ( \ +# SELECT framedipaddress, 2 AS o FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND framedipaddress = '%{%{${req_attribute_name}}:-0.0.0.0}' \ +# AND `status` = 'dynamic' \ +# AND ( pool_key = '${pool_key}' OR expiry_time < NOW() ) \ +# FOR UPDATE SKIP LOCKED \ +# ) UNION ( \ +# SELECT framedipaddress, 3 AS o FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < NOW() \ +# AND `status` = 'dynamic' \ +# ORDER BY expiry_time LIMIT 1 FOR UPDATE SKIP LOCKED \ +# ) ORDER BY o \ +# LIMIT 1" + +# +# If you prefer to allocate a random IP address every time, use this query instead. +# Note: This is very slow if you have a lot of free IPs. +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < NOW() \ +# AND `status` = 'dynamic' \ +# ORDER BY \ +# RAND() \ +# LIMIT 1 \ +# FOR UPDATE" + +# +# The above query again, but with SKIP LOCKED. This requires MySQL >= 8.0.1, +# and InnoDB. +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < NOW() \ +# AND `status` = 'dynamic' \ +# ORDER BY \ +# RAND() \ +# LIMIT 1 \ +# FOR UPDATE SKIP LOCKED" + +# +# 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 \ + gateway = '%{DHCP-Gateway-IP-Address}', pool_key = '${pool_key}', \ + expiry_time = NOW() + INTERVAL ${offer_duration} SECOND \ + WHERE framedipaddress = '%I'" + +# +# Use a stored procedure to find AND allocate the address. Read and customise +# `procedure.sql` in this directory to determine the optimal configuration. +# +#allocate_begin = "" +#allocate_find = "\ +# CALL fr_dhcp_allocate_previous_or_new_framedipaddress( \ +# '%{control:${pool_name}}', \ +# '%{DHCP-Gateway-IP-Address}', \ +# '${pool_key}', \ +# ${offer_duration}, \ +# '%{%{${req_attribute_name}}:-0.0.0.0}' \ +# )" +#allocate_update = "" +#allocate_commit = "" + + +# **************** +# * DHCP REQUEST * +# **************** + +# +# This query revokes any active offers for addresses that a client is not +# requesting when a DHCP REQUEST packet arrives +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + gateway = '', \ + pool_key = '', \ + expiry_time = NOW() \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \ + AND expiry_time > NOW() \ + AND `status` = 'dynamic'" + +# +# This query extends an existing lease (or offer) when a DHCP REQUEST packet +# arrives. This query must update a row when a lease is succesfully requested +# - queries that update no rows will result in a "notfound" response to +# the module which by default will give a DHCP-NAK reply. In this example +# incrementing "counter" is used to achieve this. +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = NOW() + INTERVAL ${lease_duration} SECOND, \ + counter = counter + 1 \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{%{DHCP-Requested-IP-Address}:-%{DHCP-Client-IP-Address}}'" + + +# **************** +# * DHCP RELEASE * +# **************** + +# +# This query frees an IP address when a DHCP RELEASE packet arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + gateway = '', \ + pool_key = '', \ + expiry_time = NOW() \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{DHCP-Client-IP-Address}' \ + AND `status` = 'dynamic'" + + +# +# This query is not applicable to DHCP +# +on_clear = "" + + +# **************** +# * DHCP DECLINE * +# **************** + +# +# This query marks an IP address as declined when a DHCP Decline +# packet arrives +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET status = 'declined' \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{DHCP-Requested-IP-Address}'" diff --git a/raddb/mods-config/sql/ippool-dhcp/mysql/schema.sql b/raddb/mods-config/sql/ippool-dhcp/mysql/schema.sql new file mode 100644 index 0000000..d8b1219 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/mysql/schema.sql @@ -0,0 +1,21 @@ +-- +-- Table structure for table 'dhcpippool' +-- +-- See also "procedure.sql" in this directory for a stored procedure +-- that is much faster. +-- + +CREATE TABLE dhcpippool ( + id int unsigned NOT NULL auto_increment, + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL default '', + pool_key varchar(30) NOT NULL default '', + gateway varchar(15) NOT NULL default '', + expiry_time DATETIME NOT NULL default NOW(), + `status` ENUM('dynamic', 'static', 'declined', 'disabled') DEFAULT 'dynamic', + counter int unsigned NOT NULL default 0, + PRIMARY KEY (id), + KEY dhcpippool_poolname_expire (pool_name, expiry_time), + KEY framedipaddress (framedipaddress), + KEY dhcpippool_poolname_poolkey_ipaddress (pool_name, pool_key, framedipaddress) +) ENGINE=InnoDB; |