Adding upstream version 3.2.3+dfsg.upstream/3.2.3+dfsg

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

3 files changed, 340 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
+$$;
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);