diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool')
16 files changed, 1742 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool/mongo/queries.conf b/raddb/mods-config/sql/ippool/mongo/queries.conf new file mode 100644 index 0000000..9d7d070 --- /dev/null +++ b/raddb/mods-config/sql/ippool/mongo/queries.conf @@ -0,0 +1,109 @@ +# -*- text -*- +# +# ippool/mongo/queries.conf -- Mongo queries for rlm_sqlippool +# +# $Id$ + +# +# The IP Pool queries expect a result like: +# +# { +# pool_key: "bob" +# pool_name: "my_pool" +# expiry_time: xxx +# value: "192.168.1.1" +# } +# +# i.e. the results are in "value", and not "framed_ip_address". +# +# When using dynamic expansions such as "%{sql:... mongo query ...}", +# Mongo uses a lot of curly brackets, {..}. Any closing braces have +# to be escaped as %}. Sorry, that is a limitation of the FreeRADIUS +# parser. +# + +# +# TBD +# +on_begin = "" +off_begin = "" + +allocate_begin = "" + +# +# This query allocates an IP address from the Pool +# +allocate_find = "db.mypool_collection.findAndModify( \ + { \ + 'query': {' \ + '$and': [ \ + { \ + 'pool_name': '%{control:Pool-Name}' \ + }, \ + { \ + 'nas_ip': '%{Nas-IP-Address}' \ + }, \ + { \ + '$or': [ \ + { \ + 'calling_station_id': '%{Calling-Station-Id}' \ + }, \ + { \ + 'locked': 0 \ + } \ + ] \ + } \ + ] \ + }, \ + 'update': { \ + 'locked': 1', \ + 'calling_station_id': '%{Calling-Station-Id'}' \ + }, \ + 'fields': { \ + '_id': 0, 'framed_ip_address': 1 \ + } \ + })" + +allocate_update = "" + +allocate_clear = "db.mypool_collection.findAndModify( \ + { \ + 'query': { \ + '$and': [ \ + { \ + 'pool_name': '%{Control:Pool-Name}' \ + }, \ + { \ + 'nas_ip': '%{Nas-IP-Address}' \ + }, \ + { \ + 'calling_station_id': '%{Calling-Station-Id}' \ + }, \ + { \ + 'locked': 1 \ + } \ + ] \ + }, \ + 'update': { \ + 'locked': 0, \ + 'calling_station_id': '' \ + } \ + })" + +allocate_commit = "" + +start_begin = "" +start_update = "" +start_commit = "" + +stop_begin = "" +stop_clear = "" +stop_commit = "" + +alive_begin = "" +alive_update = "" +alive_commit = "" + +on_clear = "" +off_clear = "" + diff --git a/raddb/mods-config/sql/ippool/mssql/procedure.sql b/raddb/mods-config/sql/ippool/mssql/procedure.sql new file mode 100644 index 0000000..5c621fb --- /dev/null +++ b/raddb/mods-config/sql/ippool/mssql/procedure.sql @@ -0,0 +1,137 @@ +-- +-- 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: +-- +-- BEGIN TRAN; "SELECT FOR UPDATE"; UPDATE; COMMIT TRAN; -> EXEC 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 = "\ +-- EXEC fr_allocate_previous_or_new_framedipaddress \ +-- @v_pool_name = '%{control:${pool_name}}', \ +-- @v_username = '%{User-Name}', \ +-- @v_callingstationid = '%{Calling-Station-Id}', \ +-- @v_nasipaddress = '%{NAS-IP-Address}', \ +-- @v_pool_key = '${pool_key}', \ +-- @v_lease_duration = ${lease_duration} \ +-- " +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE INDEX UserName_CallingStationId ON radippool(pool_name,UserName,CallingStationId) +GO + +CREATE OR ALTER PROCEDURE fr_allocate_previous_or_new_framedipaddress + @v_pool_name VARCHAR(64), + @v_username VARCHAR(64), + @v_callingstationid VARCHAR(64), + @v_nasipaddress VARCHAR(15), + @v_pool_key VARCHAR(64), + @v_lease_duration INT +AS + BEGIN + + -- MS SQL lacks a "SELECT FOR UPDATE" statement, and its table + -- hints do not provide a direct means to implement the row-level + -- read lock needed to guarentee that concurrent queries do not + -- select the same Framed-IP-Address for allocation to distinct + -- users. + -- + -- The "WITH cte AS ( SELECT ... ) UPDATE cte ... OUTPUT INTO" + -- patterns in this procedure body compensate by wrapping + -- the SELECT in a synthetic UPDATE which locks the row. + + DECLARE @r_address_tab TABLE(id VARCHAR(15)); + DECLARE @r_address VARCHAR(15); + + BEGIN TRAN; + + -- Reissue an existing IP address lease when re-authenticating a session + -- + WITH cte AS ( + SELECT TOP(1) FramedIPAddress + FROM radippool WITH (xlock rowlock readpast) + WHERE pool_name = @v_pool_name + AND expiry_time > CURRENT_TIMESTAMP + AND NASIPAddress = @v_nasipaddress AND pool_key = @v_pool_key + ) + UPDATE cte + SET FramedIPAddress = FramedIPAddress + OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + SELECT @r_address = id FROM @r_address_tab; + + -- 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. + -- + -- WITH cte AS ( + -- SELECT TOP(1) FramedIPAddress + -- FROM radippool WITH (xlock rowlock readpast) + -- WHERE pool_name = @v_pool_name + -- AND NASIPAddress = @v_nasipaddress AND pool_key = @v_pool_key + -- ) + -- UPDATE cte + -- SET FramedIPAddress = FramedIPAddress + -- OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + -- SELECT @r_address = id FROM @r_address_tab; + + -- 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 + BEGIN + WITH cte AS ( + SELECT TOP(1) FramedIPAddress + FROM radippool WITH (xlock rowlock readpast) + WHERE pool_name = @v_pool_name + AND expiry_time < CURRENT_TIMESTAMP + ORDER BY + expiry_time + ) + UPDATE cte + SET FramedIPAddress = FramedIPAddress + OUTPUT INSERTED.FramedIPAddress INTO @r_address_tab; + SELECT @r_address = id FROM @r_address_tab; + END + + -- Return nothing if we failed to allocated an address + -- + IF @r_address IS NULL + BEGIN + COMMIT TRAN; + RETURN; + END + + -- Update the pool having allocated an IP address + -- + UPDATE radippool + SET + NASIPAddress = @v_nasipaddress, + pool_key = @v_pool_key, + CallingStationId = @v_callingstationid, + UserName = @v_username, + expiry_time = DATEADD(SECOND,@v_lease_duration,CURRENT_TIMESTAMP) + WHERE framedipaddress = @r_address; + + COMMIT TRAN; + + -- Return the address that we allocated + SELECT @r_address; + + END +GO diff --git a/raddb/mods-config/sql/ippool/mssql/queries.conf b/raddb/mods-config/sql/ippool/mssql/queries.conf new file mode 100644 index 0000000..8105dcc --- /dev/null +++ b/raddb/mods-config/sql/ippool/mssql/queries.conf @@ -0,0 +1,175 @@ +# -*- text -*- +# +# ippool/mssql/queries.conf -- MSSQL queries for rlm_sqlippool +# +# $Id$ + +# +# MSSQL-specific syntax - required if finding the address and updating +# it are separate queries +# +#allocate_begin = "BEGIN TRAN" +#allocate_commit = "COMMIT TRAN" + +allocate_begin = "" +allocate_commit = "" + +# +# This series of queries allocates an IP address +# + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + WITH cte AS ( \ + SELECT TOP(1) FramedIPAddress, CallingStationId, UserName, expiry_time \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND NASIPAddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ORDER BY expiry_time DESC \ + ) \ + UPDATE cte \ + SET \ + CallingStationId = '%{Calling-Station-Id}', \ + UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + OUTPUT INSERTED.FramedIPAddress" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + WITH cte AS ( \ + SELECT TOP(1) FramedIPAddress, NASIPAddress, pool_key, \ + CallingStationId, UserName, expiry_time \ + FROM ${ippool_table} WITH (xlock rowlock readpast) \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < CURRENT_TIMESTAMP \ + ORDER BY expiry_time \ + ) \ + UPDATE cte \ + SET \ + NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ + CallingStationId = '%{Calling-Station-Id}', \ + UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + OUTPUT INSERTED.FramedIPAddress" + +# +# 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 = "\ +# WITH cte AS ( \ +# SELECT TOP(1) FramedIPAddress, NASIPAddress, pool_key, \ +# CallingStationId, UserName, expiry_time \ +# FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < CURRENT_TIMESTAMP \ +# ORDER BY newid() \ +# ) \ +# UPDATE cte WITH (rowlock, readpast) \ +# SET \ +# NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ +# CallingStationId = '%{Calling-Station-Id}', \ +# UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ +# OUTPUT INSERTED.FramedIPAddress" + +# +# 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 TOP(1) id \ + FROM ${ippool_table} \ + WHERE pool_name='%{control:${pool_name}}'" + +# +# This is the final IP Allocation query, which saves the allocated ip details. +# Only needed if allocate_existing / allocate_find do not also update the pool. +# +#allocate_update = "\ +# UPDATE ${ippool_table} \ +# SET \ +# NASIPAddress = '%{NAS-IP-Address}', pool_key = '${pool_key}', \ +# CallingStationId = '%{Calling-Station-Id}', \ +# UserName = '%{User-Name}', expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ +# 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 = "\ +# EXEC fr_allocate_previous_or_new_framedipaddress \ +# @v_pool_name = '%{control:${pool_name}}', \ +# @v_username = '%{User-Name}', \ +# @v_callingstationid = '%{Calling-Station-Id}', \ +# @v_nasipaddress = '%{NAS-IP-Address}', \ +# @v_pool_key = '${pool_key}', \ +# @v_lease_duration = ${lease_duration} \ +# " +#allocate_update = "" +#allocate_commit = "" + +# +# This series of queries frees an IP number when an accounting START record arrives. +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + WHERE NASIPAddress = '%{NAS-IP-Address}' \ + AND pool_key = '${pool_key}' \ + AND UserName = '%{User-Name}' \ + AND CallingStationId = '%{Calling-Station-Id}' \ + AND FramedIPAddress = '%{${attribute_name}}'" + +# +# Expire an IP when an accounting STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = CURRENT_TIMESTAMP \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND UserName = '%{User-Name}' \ + AND CallingStationId = '%{Calling-Station-Id}' \ + AND FramedIPAddress = '%{${attribute_name}}'" + +# +# Update the expiry time for an IP when an accounting ALIVE record arrives +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = DATEADD(SECOND,${lease_duration},CURRENT_TIMESTAMP) \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND UserName = '%{User-Name}' \ + AND CallingStationId = '%{Calling-Station-Id}' \ + AND FramedIPAddress = '%{${attribute_name}}'" + +# +# Expires all IPs allocated to a NAS when an accounting ON record arrives +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = CURRENT_TIMESTAMP \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# Expires all IPs allocated to a NAS when an accounting OFF record arrives +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = CURRENT_TIMESTAMP \ + WHERE NASIPAddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" diff --git a/raddb/mods-config/sql/ippool/mssql/schema.sql b/raddb/mods-config/sql/ippool/mssql/schema.sql new file mode 100644 index 0000000..d4bff44 --- /dev/null +++ b/raddb/mods-config/sql/ippool/mssql/schema.sql @@ -0,0 +1,25 @@ +-- +-- Table structure for table 'radippool' +-- +CREATE TABLE radippool ( + id int IDENTITY (1,1) NOT NULL, + pool_name varchar(30) NOT NULL, + FramedIPAddress varchar(15) NOT NULL default '', + NASIPAddress varchar(15) NOT NULL default '', + CalledStationId VARCHAR(32) NOT NULL default '', + CallingStationId VARCHAR(30) NOT NULL default '', + expiry_time DATETIME NOT NULL default CURRENT_TIMESTAMP, + UserName varchar(64) NOT NULL default '', + pool_key varchar(30) NOT NULL default '', + PRIMARY KEY (id) +) +GO + +CREATE INDEX poolname_expire ON radippool(pool_name, expiry_time) +GO + +CREATE INDEX FramedIPAddress ON radippool(FramedIPAddress) +GO + +CREATE INDEX NASIPAddress_poolkey_FramedIPAddress ON radippool(NASIPAddress, pool_key, FramedIPAddress) +GO diff --git a/raddb/mods-config/sql/ippool/mysql/procedure-no-skip-locked.sql b/raddb/mods-config/sql/ippool/mysql/procedure-no-skip-locked.sql new file mode 100644 index 0000000..1c88446 --- /dev/null +++ b/raddb/mods-config/sql/ippool/mysql/procedure-no-skip-locked.sql @@ -0,0 +1,149 @@ +-- +-- 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_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{User-Name}', \ +-- '%{Calling-Station-Id}', \ +-- '%{NAS-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration} \ +-- )" +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE INDEX poolname_username_callingstationid ON radippool(pool_name,username,callingstationid); + +DELIMITER $$ + +DROP PROCEDURE IF EXISTS fr_allocate_previous_or_new_framedipaddress; +CREATE PROCEDURE fr_allocate_previous_or_new_framedipaddress ( + IN v_pool_name VARCHAR(64), + IN v_username VARCHAR(64), + IN v_callingstationid VARCHAR(64), + IN v_nasipaddress VARCHAR(15), + IN v_pool_key VARCHAR(64), + IN v_lease_duration INT +) +SQL SECURITY INVOKER +proc:BEGIN + DECLARE r_address VARCHAR(15); + + -- Reissue an existing IP address lease when re-authenticating a session + -- + SELECT framedipaddress INTO r_address + FROM radippool + WHERE pool_name = v_pool_name + AND expiry_time > NOW() + AND nasipaddress = v_nasipaddress + AND pool_key = v_pool_key + 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 radippool + -- WHERE pool_name = v_pool_name + -- AND nasipaddress = v_nasipaddress + -- AND pool_key = v_pool_key + -- LIMIT 1; + + IF r_address IS NOT NULL THEN + UPDATE radippool + SET + nasipaddress = v_nasipaddress, + pool_key = v_pool_key, + callingstationid = v_callingstationid, + username = v_username, + 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 radippool + WHERE pool_name = v_pool_name + AND expiry_time < NOW() + -- + -- 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('radippool_', framedipaddress), 0) = 1 + LIMIT 1; + + IF r_address IS NULL THEN + DO RELEASE_LOCK(CONCAT('radippool_', r_address)); + LEAVE proc; + END IF; + + UPDATE radippool + SET + nasipaddress = v_nasipaddress, + pool_key = v_pool_key, + callingstationid = v_callingstationid, + username = v_username, + 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('radippool_', r_address)); + SELECT r_address; + +END$$ + +DELIMITER ; diff --git a/raddb/mods-config/sql/ippool/mysql/procedure.sql b/raddb/mods-config/sql/ippool/mysql/procedure.sql new file mode 100644 index 0000000..2a52566 --- /dev/null +++ b/raddb/mods-config/sql/ippool/mysql/procedure.sql @@ -0,0 +1,139 @@ +-- +-- 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_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{User-Name}', \ +-- '%{Calling-Station-Id}', \ +-- '%{Called-Station-Id}', \ +-- '%{NAS-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration} \ +-- )" +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE INDEX poolname_username_callingstationid ON radippool(pool_name,username,callingstationid); + +DELIMITER $$ + +DROP PROCEDURE IF EXISTS fr_allocate_previous_or_new_framedipaddress; +CREATE PROCEDURE fr_allocate_previous_or_new_framedipaddress ( + IN v_pool_name VARCHAR(64), + IN v_username VARCHAR(64), + IN v_callingstationid VARCHAR(64), + IN v_calledstationid VARCHAR(64), + IN v_nasipaddress VARCHAR(15), + IN v_pool_key VARCHAR(64), + IN v_lease_duration INT +) +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 radippool + WHERE pool_name = v_pool_name + AND expiry_time > NOW() + AND nasipaddress = v_nasipaddress + AND pool_key = v_pool_key + 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 radippool + -- WHERE pool_name = v_pool_name + -- AND nasipaddress = v_nasipaddress + -- AND pool_key = v_pool_key + -- LIMIT 1 + -- FOR UPDATE; + -- -- FOR UPDATE SKIP LOCKED; -- Better performance, but limited support + + -- 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 radippool + WHERE pool_name = v_pool_name + AND expiry_time < NOW() + 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 radippool + SET + nasipaddress = v_nasipaddress, + pool_key = v_pool_key, + callingstationid = v_callingstationid, + calledstationid = v_calledstationid, + username = v_username, + 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/mysql/queries.conf b/raddb/mods-config/sql/ippool/mysql/queries.conf new file mode 100644 index 0000000..c421020 --- /dev/null +++ b/raddb/mods-config/sql/ippool/mysql/queries.conf @@ -0,0 +1,156 @@ +# -*- text -*- +# +# ippool/mysql/queries.conf -- MySQL queries for rlm_sqlippool +# +# $Id$ + + +# Using SKIP LOCKED speeds up selection queries +# However, it requires MySQL >= 8.0.1 or MariaDB >= 10.6. +# Uncomment the following if you are running a suitable +# version of MySQL +# +#skip_locked = "SKIP LOCKED" +skip_locked = "" + +# +# This series of queries allocates an IP address +# + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ORDER BY expiry_time DESC \ + LIMIT 1 \ + FOR UPDATE ${skip_locked}" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < NOW() \ + ORDER BY expiry_time \ + LIMIT 1 \ + FOR UPDATE ${skip_locked}" + +# +# 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() \ +# 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 \ + 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'" + +# +# 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_allocate_previous_or_new_framedipaddress( \ +# '%{control:${pool_name}}', \ +# '%{User-Name}', \ +# '%{Calling-Station-Id}', \ +# '%{Called-Station-Id}', \ +# '%{NAS-IP-Address}', \ +# '${pool_key}', \ +# ${lease_duration} \ +# )" +#allocate_update = "" +#allocate_commit = "" + +# +# 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 = '%{${attribute_name}}'" + +# +# This query expires an IP number when an accounting STOP record arrives. +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = NOW() \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# 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}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# This series of queries expires the IP numbers allocate to a +# NAS when an accounting ON record arrives +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = NOW() \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# This series of queries expires the IP numbers allocate to a +# NAS when an accounting OFF record arrives +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = NOW() \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" diff --git a/raddb/mods-config/sql/ippool/mysql/schema.sql b/raddb/mods-config/sql/ippool/mysql/schema.sql new file mode 100644 index 0000000..f79d1b1 --- /dev/null +++ b/raddb/mods-config/sql/ippool/mysql/schema.sql @@ -0,0 +1,18 @@ +# +# Table structure for table 'radippool' +# +CREATE TABLE IF NOT EXISTS radippool ( + id int(11) unsigned NOT NULL auto_increment, + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL default '', + nasipaddress varchar(15) NOT NULL default '', + calledstationid VARCHAR(30) NOT NULL default '', + callingstationid VARCHAR(30) NOT NULL default '', + expiry_time DATETIME NOT NULL default NOW(), + username varchar(64) NOT NULL default '', + pool_key varchar(30) NOT NULL default '', + PRIMARY KEY (id), + KEY radippool_poolname_expire (pool_name, expiry_time), + KEY framedipaddress (framedipaddress), + KEY radippool_nasip_poolkey_ipaddress (nasipaddress, pool_key, framedipaddress) +) ENGINE=InnoDB; diff --git a/raddb/mods-config/sql/ippool/oracle/procedure.sql b/raddb/mods-config/sql/ippool/oracle/procedure.sql new file mode 100644 index 0000000..e483236 --- /dev/null +++ b/raddb/mods-config/sql/ippool/oracle/procedure.sql @@ -0,0 +1,129 @@ +-- +-- 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: +-- +-- BEGIN; SELECT FOR UPDATE; UPDATE; COMMIT; -> SELECT sp() FROM dual +-- +-- 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 = "\ +-- SELECT fr_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{User-Name}', \ +-- '%{%{Calling-Station-Id}:-0}', \ +-- '%{NAS-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration} \ +-- ) FROM dual" +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE OR REPLACE FUNCTION fr_allocate_previous_or_new_framedipaddress ( + v_pool_name IN VARCHAR2, + v_username IN VARCHAR2, + v_callingstationid IN VARCHAR2, + v_nasipaddress IN VARCHAR2, + v_pool_key IN VARCHAR2, + v_lease_duration IN INTEGER +) +RETURN varchar2 IS + PRAGMA AUTONOMOUS_TRANSACTION; + r_address varchar2(15); +BEGIN + + -- Reissue an existing IP address lease when re-authenticating a session + -- + BEGIN + SELECT framedipaddress INTO r_address FROM radippool WHERE id IN ( + SELECT id FROM ( + SELECT * + FROM radippool + WHERE pool_name = v_pool_name + AND expiry_time > current_timestamp + AND username = v_username + AND callingstationid = v_callingstationid + ) WHERE ROWNUM <= 1 + ) FOR UPDATE SKIP LOCKED; + EXCEPTION + WHEN NO_DATA_FOUND THEN + r_address := NULL; + END; + + -- 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. + -- + -- BEGIN + -- SELECT framedipaddress INTO r_address FROM radippool WHERE id IN ( + -- SELECT id FROM ( + -- SELECT * + -- FROM radippool + -- WHERE pool_name = v_pool_name + -- AND username = v_username + -- AND callingstationid = v_callingstationid + -- ) WHERE ROWNUM <= 1 + -- ) FOR UPDATE SKIP LOCKED; + -- EXCEPTION + -- WHEN NO_DATA_FOUND THEN + -- r_address := NULL; + -- END; + + -- 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 + BEGIN + SELECT framedipaddress INTO r_address FROM radippool WHERE id IN ( + SELECT id FROM ( + SELECT * + FROM radippool + WHERE pool_name = v_pool_name + AND expiry_time < CURRENT_TIMESTAMP + ORDER BY expiry_time + ) WHERE ROWNUM <= 1 + ) FOR UPDATE SKIP LOCKED; + EXCEPTION + WHEN NO_DATA_FOUND THEN + r_address := NULL; + END; + END IF; + + -- Return nothing if we failed to allocated an address + -- + IF r_address IS NULL THEN + COMMIT; + RETURN r_address; + END IF; + + -- Update the pool having allocated an IP address + -- + UPDATE radippool + SET + nasipaddress = v_nasipaddress, + pool_key = v_pool_key, + callingstationid = v_callingstationid, + username = v_username, + expiry_time = CURRENT_TIMESTAMP + v_lease_duration * INTERVAL '1' SECOND(1) + WHERE framedipaddress = r_address; + + -- Return the address that we allocated + COMMIT; + RETURN r_address; + +END; +/ diff --git a/raddb/mods-config/sql/ippool/oracle/queries.conf b/raddb/mods-config/sql/ippool/oracle/queries.conf new file mode 100644 index 0000000..1a64b28 --- /dev/null +++ b/raddb/mods-config/sql/ippool/oracle/queries.conf @@ -0,0 +1,172 @@ +# -*- text -*- +# +# ippool/oracle/queries.conf -- Oracle queries for rlm_sqlippool +# +# $Id$ + +# Using SKIP LOCKED speeds up selection queries +# However, it requires Oracle > 11g. It MAY work in 9i and 10g +# but is not documented. Uncomment the following if you are +# running a suitable version of Oracle +# +#skip_locked = "SKIP LOCKED" +skip_locked = "" + +allocate_begin = "commit" +start_begin = "commit" +alive_begin = "commit" +stop_begin = "commit" +on_begin = "commit" +off_begin = "commit" + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \ + SELECT id FROM ( \ + SELECT * \ + FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ) \ + ORDER BY expiry_time DESC \ + ) WHERE ROWNUM <= 1 \ + ) FOR UPDATE ${skip_locked}" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + SELECT framedipaddress FROM ${ippool_table} WHERE id IN ( \ + SELECT id FROM ( \ + SELECT * \ + FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < current_timestamp \ + ) \ + ORDER BY expiry_time \ + ) WHERE ROWNUM <= 1 \ + ) FOR UPDATE ${skip_locked}" + +# +# 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 id IN ( \ +# SELECT id FROM ( \ +# SELECT * \ +# FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < current_timestamp \ +# ORDER BY DBMS_RANDOM.VALUE \ +# ) WHERE ROWNUM <= 1 \ +# ) FOR UPDATE ${skip_locked}" + +# +# If an IP could not be allocated, check to see whether 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 (\ + SELECT id \ + FROM ${ippool_table} \ + WHERE pool_name='%{control:${pool_name}}'\ + ) \ + WHERE ROWNUM = 1" + +# +# This query marks the IP address handed out by "allocate-find" as used +# for the period of "lease_duration" after which time it may be reused. +# +allocate_update = "\ + UPDATE ${ippool_table} \ + SET \ + nasipaddress = '%{NAS-IP-Address}', \ + pool_key = '${pool_key}', \ + callingstationid = '%{%{Calling-Station-Id}:-0}', \ + username = '%{SQL-User-Name}', \ + expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1) \ + 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 = "\ +# SELECT fr_allocate_previous_or_new_framedipaddress( \ +# '%{control:${pool_name}}', \ +# '%{SQL-User-Name}', \ +# '%{%{Calling-Station-Id}:-0}', \ +# '%{NAS-IP-Address}', \ +# '${pool_key}', \ +# '${lease_duration}' \ +# )" +#allocate_update = "" +#allocate_commit = "" + +# +# This query extends an IP address lease by "lease_duration" when an accounting +# START record arrives +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1) \ + WHERE nasipaddress = '%{NAS-IP-Address}' \ + AND pool_key = '${pool_key}'" + +# +# This query expires an IP address when an accounting STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp - INTERVAL '1' second(1) \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{%{Calling-Station-Id}:-0}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# This query extends an IP address lease by "lease_duration" when an accounting +# ALIVE record arrives +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp + INTERVAL '${lease_duration}' second(1) \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{${attribute_name}}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{%{Calling-Station-Id}:-0}'" + +# +# This query expires all IP addresses allocated to a NAS when an +# accounting ON record arrives from that NAS +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp - INTERVAL '1' second(1) \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# This query expires all IP addresses allocated to a NAS when an +# accounting OFF record arrives from that NAS +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = current_timestamp - INTERVAL '1' second(1) \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" diff --git a/raddb/mods-config/sql/ippool/oracle/schema.sql b/raddb/mods-config/sql/ippool/oracle/schema.sql new file mode 100644 index 0000000..adf1419 --- /dev/null +++ b/raddb/mods-config/sql/ippool/oracle/schema.sql @@ -0,0 +1,27 @@ +CREATE TABLE radippool ( + id INT PRIMARY KEY, + pool_name VARCHAR(30) NOT NULL, + framedipaddress VARCHAR(15) NOT NULL, + nasipaddress VARCHAR(15) NOT NULL, + pool_key VARCHAR(30) DEFAULT '', + CalledStationId VARCHAR(64) DEFAULT '', + CallingStationId VARCHAR(64) DEFAULT '', + expiry_time timestamp(0) DEFAULT CURRENT_TIMESTAMP, + username VARCHAR(64) DEFAULT '' +); + +CREATE INDEX radippool_poolname_expire ON radippool (pool_name, expiry_time); +CREATE INDEX radippool_framedipaddress ON radippool (framedipaddress); +CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool (nasipaddress, pool_key, framedipaddress); + +CREATE SEQUENCE radippool_seq START WITH 1 INCREMENT BY 1; + +CREATE OR REPLACE TRIGGER radippool_serialnumber + BEFORE INSERT OR UPDATE OF id ON radippool + FOR EACH ROW + BEGIN + if ( :new.id = 0 or :new.id is null ) then + SELECT radippool_seq.nextval into :new.id from dual; + end if; + END; +/ diff --git a/raddb/mods-config/sql/ippool/postgresql/procedure.sql b/raddb/mods-config/sql/ippool/postgresql/procedure.sql new file mode 100644 index 0000000..b1d580c --- /dev/null +++ b/raddb/mods-config/sql/ippool/postgresql/procedure.sql @@ -0,0 +1,111 @@ +-- +-- 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; -> SELECT 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 = "\ +-- SELECT fr_allocate_previous_or_new_framedipaddress( \ +-- '%{control:${pool_name}}', \ +-- '%{User-Name}', \ +-- '%{Calling-Station-Id}', \ +-- '%{NAS-IP-Address}', \ +-- '${pool_key}', \ +-- ${lease_duration} \ +-- )" +-- allocate_update = "" +-- allocate_commit = "" +-- + +CREATE INDEX radippool_poolname_username_callingstationid ON radippool(pool_name,username,callingstationid); + +CREATE OR REPLACE FUNCTION fr_allocate_previous_or_new_framedipaddress ( + v_pool_name VARCHAR(64), + v_username VARCHAR(64), + v_callingstationid VARCHAR(64), + v_nasipaddress VARCHAR(16), + v_pool_key VARCHAR(64), + v_lease_duration INT +) +RETURNS inet +LANGUAGE plpgsql +AS $$ +DECLARE + r_address inet; +BEGIN + + -- Reissue an existing IP address lease when re-authenticating a session + -- + SELECT framedipaddress INTO r_address + FROM radippool + WHERE pool_name = v_pool_name + AND expiry_time > NOW() + AND username = v_username + AND callingstationid = v_callingstationid + LIMIT 1 + FOR UPDATE SKIP LOCKED; + + -- 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 radippool + -- WHERE pool_name = v_pool_name + -- AND username = v_username + -- AND callingstationid = v_callingstationid + -- LIMIT 1 + -- FOR UPDATE SKIP LOCKED; + + -- 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 radippool + WHERE pool_name = v_pool_name + AND expiry_time < NOW() + ORDER BY + expiry_time + LIMIT 1 + FOR UPDATE SKIP LOCKED; + END IF; + + -- Return nothing if we failed to allocated an address + -- + IF r_address IS NULL THEN + RETURN r_address; + END IF; + + -- Update the pool having allocated an IP address + -- + UPDATE radippool + SET + nasipaddress = v_nasipaddress, + pool_key = v_pool_key, + callingstationid = v_callingstationid, + username = v_username, + expiry_time = NOW() + v_lease_duration * interval '1 sec' + WHERE framedipaddress = r_address; + + -- Return the address that we allocated + RETURN r_address; + +END +$$; diff --git a/raddb/mods-config/sql/ippool/postgresql/queries.conf b/raddb/mods-config/sql/ippool/postgresql/queries.conf new file mode 100644 index 0000000..ce6f355 --- /dev/null +++ b/raddb/mods-config/sql/ippool/postgresql/queries.conf @@ -0,0 +1,207 @@ +# -*- text -*- +# +# ippool/postgresql/queries.conf -- PostgreSQL queries for rlm_sqlippool +# +# $Id$ + + +# Using SKIP LOCKED speeds up selection queries +# However, it requires PostgreSQL >= 9.5 Uncomment the +# following if you are running a suitable version of PostgreSQL +# +#skip_locked = "SKIP LOCKED" +skip_locked = "" + +# +# This series of queries allocates an IP address +# + +# +# The suggested queries locate IPs and update them in one query +# so no need for transaction wrappers +# +allocate_begin = "" +allocate_commit = "" + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + WITH cte AS ( \ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ORDER BY expiry_time DESC \ + LIMIT 1 \ + FOR UPDATE ${skip_locked} \ + ) \ + UPDATE ${ippool_table} \ + SET \ + nasipaddress = '%{NAS-IP-Address}', \ + pool_key = '${pool_key}', \ + callingstationid = '%{Calling-Station-Id}', \ + username = '%{SQL-User-Name}', \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ + FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \ + RETURNING cte.framedipaddress" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + WITH cte AS ( \ + SELECT framedipaddress FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND expiry_time < 'now'::timestamp(0) \ + ORDER BY expiry_time \ + LIMIT 1 \ + FOR UPDATE ${skip_locked} \ + ) \ + UPDATE ${ippool_table} \ + SET \ + nasipaddress = '%{NAS-IP-Address}', \ + pool_key = '${pool_key}', \ + callingstationid = '%{Calling-Station-Id}', \ + username = '%{SQL-User-Name}', \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ + FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \ + RETURNING cte.framedipaddress" + +# +# 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 = "\ +# WITH cte AS ( \ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < 'now'::timestamp(0) \ +# ORDER BY RANDOM() \ +# LIMIT 1 \ +# FOR UPDATE ${skip_locked} \ +# ) \ +# UPDATE ${ippool_table} \ +# SET \ +# nasipaddress = '%{NAS-IP-Address}', \ +# pool_key = '${pool_key}', \ +# callingstationid = '%{Calling-Station-Id}', \ +# username = '%{SQL-User-Name}', \ +# expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ +# FROM cte WHERE cte.framedipaddress = ${ippool_table}.framedipaddress \ +# RETURNING cte.framedipaddress" + +# +# If an IP could not be allocated, check to see whether 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 query marks the IP address handed out by "allocate-find" as used +# for the period of "lease_duration" after which time it may be reused. +# This is only needed if the allocate_existing / allocate_find queries +# do not update the pool +# +#allocate_update = "\ +# UPDATE ${ippool_table} \ +# SET \ +# nasipaddress = '%{NAS-IP-Address}', \ +# pool_key = '${pool_key}', \ +# callingstationid = '%{Calling-Station-Id}', \ +# username = '%{SQL-User-Name}', \ +# expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ +# 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. +# +# This requires PostgreSQL >= 9.5 as SKIP LOCKED is used. +# +# The "NO LOAD BALANCE" comment is included here to indicate to a PgPool +# system that this needs to be a write transaction. PgPool itself cannot +# detect this from the statement alone. If you are using PgPool and do not +# have this comment, the query may go to a read only server, and will fail. +# This has no negative effect if you are not using PgPool. +# +#allocate_begin = "" +#allocate_find = "\ +# /*NO LOAD BALANCE*/ \ +# SELECT fr_allocate_previous_or_new_framedipaddress( \ +# '%{control:${pool_name}}', \ +# '%{SQL-User-Name}', \ +# '%{Calling-Station-Id}', \ +# '%{NAS-IP-Address}', \ +# '${pool_key}', \ +# '${lease_duration}' \ +# )" +#allocate_update = "" +#allocate_commit = "" + +# +# This query extends an IP address lease by "lease_duration" when an accounting +# START record arrives +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} second'::interval \ + WHERE nasipaddress = '%{NAS-IP-Address}' \ + AND pool_key = '${pool_key}'" + +# +# This query expires an IP address when an accounting +# STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# This query extends an IP address lease by "lease_duration" when an accounting +# ALIVE record arrives +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) + '${lease_duration} seconds'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress = '%{${attribute_name}}' \ + AND username = '%{SQL-User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}'" + +# +# This query expires all IP addresses allocated to a NAS when an +# accounting ON record arrives from that NAS +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# This query expires all IP addresses allocated to a NAS when an +# accounting OFF record arrives from that NAS +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" diff --git a/raddb/mods-config/sql/ippool/postgresql/schema.sql b/raddb/mods-config/sql/ippool/postgresql/schema.sql new file mode 100644 index 0000000..1ef57b7 --- /dev/null +++ b/raddb/mods-config/sql/ippool/postgresql/schema.sql @@ -0,0 +1,22 @@ +-- +-- Table structure for table 'radippool' +-- +-- See also "procedure.sql" in this directory for additional +-- indices and a stored procedure that is much faster. +-- + +CREATE TABLE radippool ( + id BIGSERIAL PRIMARY KEY, + pool_name text NOT NULL, + FramedIPAddress INET NOT NULL, + NASIPAddress text NOT NULL default '', + pool_key text NOT NULL default '', + CalledStationId text NOT NULL default '', + CallingStationId text NOT NULL default ''::text, + expiry_time TIMESTAMP(0) without time zone NOT NULL default NOW(), + username text DEFAULT ''::text +); + +CREATE INDEX radippool_poolname_expire ON radippool USING btree (pool_name, expiry_time); +CREATE INDEX radippool_framedipaddress ON radippool USING btree (framedipaddress); +CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool USING btree (nasipaddress, pool_key, framedipaddress); diff --git a/raddb/mods-config/sql/ippool/sqlite/queries.conf b/raddb/mods-config/sql/ippool/sqlite/queries.conf new file mode 100644 index 0000000..46ce58e --- /dev/null +++ b/raddb/mods-config/sql/ippool/sqlite/queries.conf @@ -0,0 +1,148 @@ +# -*- text -*- +# +# ippool/sqlite/queries.conf -- SQLite queries for rlm_sqlippool +# +# $Id$ + +# +# SQLite does not implement SELECT FOR UPDATE which is normally used to place +# an exclusive lock over rows to prevent the same address from being +# concurrently selected for allocation to multiple users. +# +# The most granular read-blocking lock that SQLite has is an exclusive lock +# over the database, so that's what we use. All locking in SQLite is performed +# over the entire database and we perform a row update for any IP that we +# allocate, requiring an exclusive lock. Taking the exclusive lock from the +# start of the transaction (even if it were not required to guard the SELECT) +# is actually quicker than if we deferred it causing SQLite to "upgrade" the +# automatic shared lock for the transaction to an exclusive lock for the +# subsequent UPDATE. +# +allocate_begin = "BEGIN EXCLUSIVE" +allocate_commit = "COMMIT" + +# +# This series of queries allocates an IP address +# + +# +# Attempt to allocate the address a client previously had. This is based on pool_key +# and nasipaddress. Change the criteria if the identifier for "stickyness" is different. +# If different criteria are used, check the indexes on the IP pool table to ensure the fields +# are appropriately indexed. To disable stickyness comment out this query. +# +allocate_existing = "\ + SELECT framedipaddress \ + FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND nasipaddress = '%{NAS-IP-Address}' AND pool_key = '${pool_key}' \ + ORDER BY expiry_time DESC \ + LIMIT 1" + +# +# Find a free IP address from the pool, choosing the oldest expired one. +# +allocate_find = "\ + SELECT framedipaddress \ + FROM ${ippool_table} \ + WHERE pool_name = '%{control:${pool_name}}' \ + expiry_time < datetime('now') \ + ORDER BY expiry_time \ + LIMIT 1" + +# +# If you prefer to allocate a random IP address every time, i +# 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 IS NULL \ +# ORDER BY RAND() \ +# LIMIT 1" + +# +# 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 = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \ + WHERE framedipaddress = '%I'" + +# +# Extend an IP expiry time when an accounting START record arrives +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \ + WHERE nasipaddress = '%{NAS-IP-Address}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# Expire an IP when an accounting STOP record arrives +# +stop_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = datetime('now') \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# Update the expiry time for an IP when an accounting ALIVE record arrives +# +alive_update = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = datetime(strftime('%%s', 'now') + ${lease_duration}, 'unixepoch') \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}' \ + AND pool_key = '${pool_key}' \ + AND username = '%{User-Name}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND framedipaddress = '%{${attribute_name}}'" + +# +# Expires all IPs allocated to a NAS when an accounting ON record arrives +# +on_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = datetime('now') \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + +# +# Expires all IPs allocated to a NAS when an accounting OFF record arrives +# +off_clear = "\ + UPDATE ${ippool_table} \ + SET \ + expiry_time = datetime('now') \ + WHERE nasipaddress = '%{%{Nas-IP-Address}:-%{Nas-IPv6-Address}}'" + diff --git a/raddb/mods-config/sql/ippool/sqlite/schema.sql b/raddb/mods-config/sql/ippool/sqlite/schema.sql new file mode 100644 index 0000000..b020c62 --- /dev/null +++ b/raddb/mods-config/sql/ippool/sqlite/schema.sql @@ -0,0 +1,18 @@ +-- +-- Table structure for table 'radippool' +-- +CREATE TABLE radippool ( + id int(11) PRIMARY KEY, + pool_name varchar(30) NOT NULL, + framedipaddress varchar(15) NOT NULL default '', + nasipaddress varchar(15) NOT NULL default '', + calledstationid VARCHAR(30) NOT NULL default '', + callingstationid VARCHAR(30) NOT NULL default '', + expiry_time DATETIME NOT NULL default (DATETIME('now')), + username varchar(64) NOT NULL default '', + pool_key varchar(30) NOT NULL default '' +); + +CREATE INDEX radippool_poolname_expire ON radippool(pool_name, expiry_time); +CREATE INDEX radippool_framedipaddress ON radippool(framedipaddress); +CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool(nasipaddress, pool_key, framedipaddress); |