1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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
$$;
|
