diff options
Diffstat (limited to 'raddb/mods-config/sql/ippool-dhcp/postgresql/procedure.sql')
| -rw-r--r-- | raddb/mods-config/sql/ippool-dhcp/postgresql/procedure.sql | 119 |
1 files changed, 119 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 +$$; |