diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool-dhcp/postgresql')
3 files changed, 433 insertions, 0 deletions
diff --git a/raddb/mods-config/sql/ippool-dhcp/postgresql/procedure.sql b/raddb/mods-config/sql/ippool-dhcp/postgresql/procedure.sql new file mode 100644 index 0000000..379a349 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/postgresql/procedure.sql @@ -0,0 +1,119 @@ +-- +-- 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_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 = "" +-- + +CREATE OR REPLACE FUNCTION fr_dhcp_allocate_previous_or_new_framedipaddress ( + v_pool_name VARCHAR(64), + v_gateway VARCHAR(16), + v_pool_key VARCHAR(64), + v_lease_duration INT, + v_requested_address INET +) +RETURNS inet +LANGUAGE plpgsql +AS $$ +DECLARE + r_address INET; +BEGIN + + -- Reissue an existing IP address lease when re-authenticating a session + -- + WITH ips AS ( + SELECT framedipaddress FROM dhcpippool + WHERE pool_name = v_pool_name + AND pool_key = v_pool_key + AND expiry_time > NOW() + AND status IN ('dynamic', 'static') + LIMIT 1 FOR UPDATE SKIP LOCKED ) + UPDATE dhcpippool + SET expiry_time = NOW() + v_lease_duration * interval '1 sec' + FROM ips WHERE dhcpippool.framedipaddress = ips.framedipaddress + RETURNING dhcpippool.framedipaddress INTO r_address; + + -- 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 ips AS ( + -- SELECT framedipaddress FROM dhcpippool + -- WHERE pool_name = v_pool_name + -- AND pool_key = v_pool_key + -- AND status IN ('dynamic', 'static') + -- LIMIT 1 FOR UPDATE SKIP LOCKED ) + -- UPDATE dhcpippool + -- SET expiry_time = NOW + v_lease_duration * interval '1 sec' + -- FROM ips WHERE dhcpippool.framedipaddress = ips.framedipaddress + -- RETURNING dhcpippool.framedipaddress INTO r_address; + + -- Issue the requested IP address if it is available + -- + IF r_address IS NULL AND v_requested_address != '0.0.0.0' THEN + WITH ips AS ( + SELECT framedipaddress 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() ) + LIMIT 1 FOR UPDATE SKIP LOCKED ) + UPDATE dhcpippool + SET pool_key = v_pool_key, + expiry_time = NOW() + v_lease_duration * interval '1 sec', + gateway = v_gateway + FROM ips WHERE dhcpippool.framedipaddress = ips.framedipaddress + RETURNING dhcpippool.framedipaddress INTO r_address; + 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 + WITH ips AS ( + SELECT framedipaddress FROM dhcpippool + WHERE pool_name = v_pool_name + AND expiry_time < NOW() + AND status = 'dynamic' + ORDER BY expiry_time + LIMIT 1 FOR UPDATE SKIP LOCKED ) + UPDATE dhcpippool + SET pool_key = v_pool_key, + expiry_time = NOW() + v_lease_duration * interval '1 sec', + gateway = v_gateway + FROM ips WHERE dhcpippool.framedipaddress = ips.framedipaddress + RETURNING dhcpippool.framedipaddress INTO r_address; + END IF; + + -- Return the address that we allocated + RETURN r_address; + +END +$$; diff --git a/raddb/mods-config/sql/ippool-dhcp/postgresql/queries.conf b/raddb/mods-config/sql/ippool-dhcp/postgresql/queries.conf new file mode 100644 index 0000000..632fc70 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/postgresql/queries.conf @@ -0,0 +1,291 @@ +# -*- text -*- +# +# ippool-dhcp/postgresql/queries.conf -- PostgreSQL queries for rlm_sqlippool +# +# $Id$ + +# ***************** +# * DHCP DISCOVER * +# ***************** + +# +# 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_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 = "" + +# +# If stored procedures are not able to be used, the following queries can +# be used. +# Comment out all the above queries and choose the appropriate "allocate_find" +# to match the desired outcome and also the version of "allocate_update" below. +# + +# +# This sequence of queries allocates an IP address from the Pool +# +#allocate_begin = "BEGIN" + + +# 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" + +# The same query with SKIP LOCKED - requires PostgreSQL >= 9.5 +# 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'::timestamp(0) \ +# FOR UPDATE" + +# The same query with SKIP LOCKED - requires PostgreSQL >= 9.5 +#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'::timestamp(0) \ +# 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'::timestamp(0) \ +# AND status = 'dynamic' \ +# ORDER BY expiry_time \ +# LIMIT 1 \ +# FOR UPDATE" + +# The same query with SKIP LOCKED - requires PostgreSQL >= 9.5 +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < 'now'::timestamp(0) \ +# AND status = 'dynamic' \ +# 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. +# Use of either of these next two queries should have the allocate_begin line commented out +# and allocate_update below un-commented. +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' AND expiry_time < 'now'::timestamp(0) \ +# AND status = 'dynamic' \ +# ORDER BY RANDOM() \ +# LIMIT 1 \ +# FOR UPDATE" + +# +# The above query again, but with SKIP LOCKED. This requires PostgreSQL >= 9.5. +# +#allocate_find = "\ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' AND expiry_time < 'now'::timestamp(0) \ +# AND status = 'dynamic' \ +# ORDER BY RANDOM() \ +# LIMIT 1 \ +# FOR UPDATE SKIP LOCKED" + +# +# 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 \ +# gateway = '%{DHCP-Gateway-IP-Address}', \ +# pool_key = '${pool_key}', \ +# expiry_time = 'now'::timestamp(0) + '${offer_duration} second'::interval \ +# WHERE framedipaddress = '%I'" + + +# +# Alternatively, merge the matching of existing IP and free IP into a single query +# This version does the update as well - so allocate_begin, allocate_update and +# allocate_commit should be blank +# +#allocate_begin = "" +#allocate_find = "\ +# WITH found AS ( \ +# WITH existing AS ( \ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND pool_key = '${pool_key}' \ +# ORDER BY expiry_time DESC \ +# LIMIT 1 \ +# FOR UPDATE SKIP LOCKED \ +# ), requested AS ( \ +# SELECT framedipaddress 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'::timestamp(0) ) \ +# FOR UPDATE SKIP LOCKED \ +# ), new AS ( \ +# SELECT framedipaddress FROM ${ippool_table} \ +# WHERE pool_name = '%{control:${pool_name}}' \ +# AND expiry_time < 'now'::timestamp(0) \ +# AND status = 'dynamic' \ +# ORDER BY expiry_time \ +# LIMIT 1 \ +# FOR UPDATE SKIP LOCKED \ +# ) \ +# SELECT framedipaddress, 1 AS o FROM existing \ +# UNION ALL \ +# SELECT framedipaddress, 2 AS o FROM requested \ +# UNION ALL \ +# SELECT framedipaddress, 3 AS o FROM new \ +# ORDER BY o LIMIT 1 \ +# ) \ +# UPDATE ${ippool_table} \ +# SET pool_key = '${pool_key}', \ +# expiry_time = 'now'::timestamp(0) + '${offer_duration} second'::interval, \ +# gateway = '%{DHCP-Gateway-IP-Address}' \ +# FROM found \ +# WHERE found.framedipaddress = ${ippool_table}.framedipaddress \ +# RETURNING found.framedipaddress" +#allocate_update = "" +#allocate_commit = "" + + +# +# 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" + + +# **************** +# * DHCP REQUEST * +# **************** + +# +# This query revokes any active offers for addresses that a client is not +# requesting when a DHCP REQUEST packet arrives, i.e, each server (sharing the +# same database) may have simultaneously offered a unique address. +# +start_update = "\ + UPDATE ${ippool_table} \ + SET \ + gateway = '', \ + pool_key = '', \ + expiry_time = 'now'::timestamp(0) - '1 second'::interval \ + WHERE pool_name = '%{control:${pool_name}}' \ + AND pool_key = '${pool_key}' \ + AND framedipaddress <> '%{DHCP-Requested-IP-Address}' \ + AND expiry_time > 'now'::timestamp(0) \ + 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'::timestamp(0) + '${lease_duration} second'::interval, \ + 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'::timestamp(0) - '1 second'::interval \ + 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/postgresql/schema.sql b/raddb/mods-config/sql/ippool-dhcp/postgresql/schema.sql new file mode 100644 index 0000000..af86889 --- /dev/null +++ b/raddb/mods-config/sql/ippool-dhcp/postgresql/schema.sql @@ -0,0 +1,23 @@ +-- +-- Table structure for table 'dhcpippool' +-- +-- See also "procedure.sql" in this directory for +-- a stored procedure that gives much faster response. +-- + +CREATE TYPE dhcp_status AS ENUM ('dynamic', 'static', 'declined', 'disabled'); + +CREATE TABLE dhcpippool ( + id BIGSERIAL PRIMARY KEY, + pool_name varchar(64) NOT NULL, + FramedIPAddress INET NOT NULL, + pool_key VARCHAR(64) NOT NULL default '0', + gateway VARCHAR(16) NOT NULL default '', + expiry_time TIMESTAMP(0) without time zone NOT NULL default NOW(), + status dhcp_status DEFAULT 'dynamic', + counter INT NOT NULL default 0 +); + +CREATE INDEX dhcpippool_poolname_expire ON dhcpippool USING btree (pool_name, expiry_time); +CREATE INDEX dhcpippool_framedipaddress ON dhcpippool USING btree (framedipaddress); +CREATE INDEX dhcpippool_poolname_poolkey_ipaddress ON dhcpippool USING btree (pool_name, pool_key, framedipaddress); |