1 files changed, 111 insertions, 0 deletions
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
+$$;