diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool/postgresql')
| -rw-r--r-- | raddb/mods-config/sql/ippool/postgresql/procedure.sql | 111 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool/postgresql/queries.conf | 207 | ||||
| -rw-r--r-- | raddb/mods-config/sql/ippool/postgresql/schema.sql | 22 |
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); |