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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
/*
* IP Engineering RADIUS Servers
*
* ippool-demo.sql - inserts demo data into RADIUS IP Pools database
*
*/
SET ROLE radius_user;
/*
* find user's previous address if available
*
* This query is provided as an example, production systems should use the
* function ip_autoassign().
*/
\set myUsername 'jdoe8@example.net'
\set myNasName 'br1.sol'
\set myPoolName 'DHCP'
SELECT ip.address AS address,
ip.modified AS modified,
ip.username AS username,
pool.name AS pool,
region.name AS region
FROM ip
INNER JOIN netblock
ON netblock.network >>= ip.address
INNER JOIN region
ON region.id = netblock.regionId
INNER JOIN pool
ON pool.id = netblock.poolId
INNER JOIN nas
ON nas.regionId = region.id
WHERE ip.nasId IS NULL
AND ip.username LIKE lower( :'myUsername' )
AND nas.name LIKE lower( :'myNasName' )
AND pool.name LIKE lower( :'myPoolName' )
AND netblock.assignable = TRUE
ORDER BY ip.modified DESC,
ip.address ASC
LIMIT 1;
/*
* find next available IP address for a given NAS and pool
*
* This query is provided as an example, production systems should use the
* function ip_autoassign().
*/
\set myNasName 'br1.sol'
\set myPoolName 'DHCP'
SELECT ip.address AS address,
ip.modified AS modified,
ip.username AS username,
pool.name AS pool,
region.name AS region
FROM ip
INNER JOIN netblock
ON netblock.network >>= ip.address
AND netblock.assignable IS TRUE
INNER JOIN region
ON region.id = netblock.regionId
INNER JOIN pool
ON pool.id = netblock.poolId
INNER JOIN nas
ON nas.regionId = region.id
WHERE ip.nasId IS NULL
AND nas.name LIKE lower( :'myNasName' )
AND pool.name LIKE lower( :'myPoolName' )
AND netblock.assignable = TRUE
ORDER BY ip.modified ASC,
ip.address ASC
LIMIT 1;
/*
* list all IP addresses with referenced/inferred data
*
* This query will return a potentialy large dataset which makes it
* unsuitable for a function or stored procedure. The implementation of
* `RETURN QUERY` in PostgreSQL 11 stores the entire result set before
* returning from a function. If the dataset is very large, the result
* set will be written to disk to avoid memory exchaustion. This will lead
* to performance issues.
*
* Additionally, the following query is very generic and most likely an
* administrator will need to customize the query to obtain the needed
* result set for a given need.
*/
--
-- Method 1:
SELECT DISTINCT pool.name AS poolName,
region.name AS regionName,
ip.address AS address,
TRUE = ALL
(
SELECT nb2.assignable
FROM netblock AS nb2
WHERE ip.address <<= nb2.network
) AS active,
(
nasId IS NOT NULL
) AS assigned,
ip.modified AS modified,
nas.name AS nasName,
ip.username AS username
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
INNER JOIN pool
ON netblock.poolId = pool.id
LEFT OUTER JOIN region
ON netblock.regionId = region.id
LEFT OUTER JOIN nas
ON ip.nasId = nas.id
ORDER BY address;
--
-- Method 2:
SELECT DISTINCT pool.name AS poolName,
region.name AS regionName,
ip.address AS address,
NOT EXISTS
(
SELECT *
FROM netblock AS nb2
WHERE ip.address <<= nb2.network
AND NOT nb2.assignable
) AS active,
(
nasId IS NOT NULL
) AS assigned,
ip.modified AS modified,
nas.name AS nasName,
ip.username AS username
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
INNER JOIN pool
ON netblock.poolId = pool.id
LEFT OUTER JOIN region
ON netblock.regionId = region.id
LEFT OUTER JOIN nas
ON ip.nasId = nas.id
ORDER BY address;
/*
* list all inactive IP addresses
*/
SELECT DISTINCT ip.address AS ipAddress,
netblock.network AS network,
netblock.assignable AS active
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
WHERE netblock.assignable = FALSE
ORDER BY ipAddress;
/*
* list all active IP addresses
*/
SELECT DISTINCT netblock.regionId AS regionId,
ip.address AS ipAddress,
netblock.assignable AS active
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
WHERE netblock.assignable = TRUE
AND ip.address NOT IN
(
SELECT address AS ipAddress
FROM ip
INNER JOIN netblock
ON ip.address <<= netblock.network
AND netblock.assignable = FALSE
)
ORDER BY ipAddress;
/* end of sql */
|
