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
|
SELECT * FROM region_audit() ORDER BY poolName, regionName;
SELECT * FROM netblock_audit() ORDER BY poolName, network;
SELECT * FROM ip_audit() ORDER BY ipAddress;
CREATE TABLE IF NOT EXISTS netblock
(
id SERIAL,
network CIDR NOT NULL UNIQUE,
poolId INTEGER NOT NULL,
regionId INTEGER,
active BOOLEAN NOT NULL DEFAULT TRUE,
description VARCHAR(32),
PRIMARY KEY ( id ),
FOREIGN KEY ( poolId )
REFERENCES pool
( id ),
FOREIGN KEY ( regionId )
REFERENCES region
( id )
);
CREATE TABLE IF NOT EXISTS ip
(
address INET,
modified timestamp WITH TIME ZONE NOT NULL
DEFAULT TIMESTAMP 'epoch',
username VARCHAR(64),
acctSession VARCHAR(32),
nasId INTEGER,
PRIMARY KEY ( address ),
FOREIGN KEY ( nasId )
REFERENCES nas
( id )
);
\c syzdek
set role wheel;
drop database radius_ippool;
psql -h raddbtest.prv.acsalaska.net -f radius-roles.sql
psql -h raddbtest.prv.acsalaska.net -f ippool-database.sql
psql -h raddbtest.prv.acsalaska.net -f ippool-schema.sql \
-f ippool-functions.sql radius_ippool
psql -h raddbtest.prv.acsalaska.net -f ippool-demo.sql radius_ippool
SELECT ip.address AS address,
region.name AS region,
pool.name AS pool,
ip.modified AS modified,
ip.username AS username,
ip.acctsession AS acctsession
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 nas.name LIKE lower( 'br1.sol' )
AND pool.name LIKE lower( 'DHCP' )
AND netblock.active = TRUE
ORDER BY ip.modified ASC
LIMIT 1;
UPDATE ip
SET nasId = nas.id,
username = 'test1'
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 nas.name LIKE 'br1.sol'
AND pool.name LIKE lower( 'DHCP' )
AND netblock.active = TRUE
ORDER BY ip.modified ASC
LIMIT 1;
DECLARE
blah := 'test1';
BEGIN
SELECT *, blah, FROM region;
END;
INSERT INTO foo
( attr1,
attr2,
attr3
)
VALUES ( 'val1',
'val2',
'val3'
);
DELETE FROM foo
WHERE attr1 like 'val1';
UPDATE foo
SET attr1 = 'valI',
attr2 = 'valII',
attr3 = 'valIII'
WHERE attr1 like 'val1';
|
