source: trunk/cgi-bin/ipdb.psql@ 864

Last change on this file since 864 was 864, checked in by Kris Deugau, 9 years ago

/trunk

Refine initial database creation script based on correct-in-production
2.7-3.0 upgrade. Still arguably some things wrong with GRANTs, but those
are more artifacts of legacy dumps.
File size: 9.4 KB
Line 
1-- Need to do this or our triggers don't work. Why do we need to do this?
2-- CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
3-- CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
4
5-- Table for VRF list
6CREATE TABLE vrfs (
7 vrf varchar(32) DEFAULT '' NOT NULL,
8 comment text DEFAULT '' NOT NULL,
9 location varchar(4) DEFAULT '' NOT NULL
10);
11
12COPY vrfs (vrf, comment, location) FROM stdin;
13DEFAULT Initial default VRF
14\.
15
16ALTER TABLE ONLY vrfs
17 ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf);
18
19-- Table for tracking active netblock assignments and containers.
20-- Note that maskbits is obsolete but left in for a release cycle or two
21-- so that legacy dumps can still be loaded, in the unlikely event.
22CREATE TABLE allocations (
23 cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
24 "type" character(2) DEFAULT ''::bpchar,
25 city character varying(30) DEFAULT ''::character varying,
26 description character varying(128) DEFAULT ''::character varying,
27 notes text DEFAULT ''::text,
28 maskbits integer DEFAULT 128,
29 circuitid character varying(128) DEFAULT ''::character varying,
30 createstamp timestamp with time zone DEFAULT now(),
31 modifystamp timestamp with time zone DEFAULT now(),
32 privdata text DEFAULT ''::text NOT NULL,
33 custid character varying(16) DEFAULT ''::character varying,
34 swip character(1) DEFAULT 'n'::bpchar,
35 vrf text DEFAULT ''::text NOT NULL,
36 vlan text DEFAULT ''::text NOT NULL,
37 rdns text DEFAULT ''::text NOT NULL,
38 parent_id integer DEFAULT 0 NOT NULL,
39 master_id integer DEFAULT 0 NOT NULL,
40 backup_id integer DEFAULT 0 NOT NULL,
41 id serial NOT NULL
42);
43
44ALTER TABLE ONLY allocations
45 ADD CONSTRAINT allocations_pkey PRIMARY KEY (id);
46CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type);
47
48-- Customer or POP site cities or locations.
49CREATE TABLE cities (
50 id serial NOT NULL,
51 city character varying(30) DEFAULT ''::character varying NOT NULL,
52 routing character(1) DEFAULT 'n'::bpchar NOT NULL
53);
54
55ALTER TABLE ONLY cities
56 ADD CONSTRAINT cities_pkey PRIMARY KEY (id);
57
58-- Local table for rWHOIS customer data
59CREATE TABLE customers (
60 custid character varying(16) DEFAULT ''::character varying NOT NULL,
61 name character varying(64),
62 street character varying(25),
63 street2 character varying(25),
64 city character varying(30),
65 province character(2),
66 country character(2),
67 pocode character varying(7),
68 phone character varying(15),
69 tech_handle character varying(50),
70 abuse_handle character varying(50),
71 admin_handle character varying(50),
72 def_rdns character varying(40),
73 special text
74);
75
76ALTER TABLE ONLY customers
77 ADD CONSTRAINT customers_pkey PRIMARY KEY (custid);
78
79-- Flag table for deciding if we can usefully do rDNS RPC calls.
80CREATE TABLE dnsavail (
81 "zone" cidr PRIMARY KEY,
82 "location" varchar(4) DEFAULT '',
83 parent_alloc integer NOT NULL
84);
85
86-- Store backup fields in a separate table
87CREATE TABLE backuplist (
88 backup_id serial NOT NULL,
89 bkbrand text,
90 bkmodel text,
91 bktype text,
92 bkport integer,
93 bksrc text,
94 bkuser text,
95 bkvpass text,
96 bkepass text,
97 bkip inet
98);
99
100-- Table for tracking netblocks available for assignment
101CREATE TABLE freeblocks (
102 cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
103 maskbits integer DEFAULT 128,
104 city character varying(30) DEFAULT ''::character varying,
105 routed character(1) DEFAULT 'n'::bpchar,
106 vrf text DEFAULT ''::text NOT NULL,
107 parent_id integer DEFAULT 0 NOT NULL,
108 master_id integer DEFAULT 0 NOT NULL,
109 reserve_for integer DEFAULT 0 NOT NULL,
110 id serial NOT NULL
111);
112
113ALTER TABLE ONLY freeblocks
114 ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id);
115
116-- Network nodes - allows finding customers affected by a broken <x> quickly
117CREATE TABLE noderef (
118 block inet NOT NULL PRIMARY KEY,
119 node_id integer
120);
121
122CREATE TABLE nodes (
123 node_id serial NOT NULL PRIMARY KEY,
124 node_type character varying(2),
125 node_name character varying(40),
126 node_ip inet
127);
128
129
130-- Email notifications on <action>
131CREATE TABLE notify (
132 action varchar(5) NOT NULL PRIMARY KEY,
133 reciplist varchar(500)
134);
135
136
137-- Table for tracking single IP assignments. Single static IP assignments
138-- need somewhat stronger clustering than provided by other types.
139CREATE TABLE poolips (
140 pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
141 ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL,
142 city character varying(30) DEFAULT ''::character varying NOT NULL,
143 "type" character(2) DEFAULT ''::bpchar NOT NULL,
144 available character(1) DEFAULT 'y'::bpchar NOT NULL,
145 notes text DEFAULT ''::text NOT NULL,
146 description character varying(128) DEFAULT ''::character varying NOT NULL,
147 circuitid character varying(128) DEFAULT ''::character varying NOT NULL,
148 privdata text DEFAULT ''::text NOT NULL,
149 custid character varying(16) DEFAULT ''::character varying,
150 createstamp timestamp with time zone DEFAULT now(),
151 modifystamp timestamp with time zone DEFAULT now(),
152 vrf text DEFAULT ''::text NOT NULL,
153 vlan text DEFAULT ''::text NOT NULL,
154 rdns text DEFAULT ''::text NOT NULL,
155 parent_id integer DEFAULT 0 NOT NULL,
156 master_id integer DEFAULT 0 NOT NULL,
157 backup_id integer DEFAULT 0 NOT NULL,
158 id serial NOT NULL,
159 CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
160);
161
162ALTER TABLE ONLY poolips
163 ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id);
164
165
166-- Combined netblock+IP view for searches
167CREATE VIEW searchme AS
168 SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city,
169 allocations.description, allocations.notes, allocations.circuitid, allocations.vrf,
170 allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available
171 FROM allocations
172 UNION
173 SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city,
174 poolips.description, poolips.notes, poolips.circuitid, poolips.vrf,
175 poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available
176 FROM poolips;
177
178
179-- Type list for assignments
180CREATE TABLE alloctypes (
181 "type" character(2) DEFAULT ''::bpchar NOT NULL,
182 listname character varying(40) DEFAULT ''::character varying,
183 dispname character varying(40) DEFAULT ''::character varying,
184 listorder integer DEFAULT 0,
185 def_custid character varying(16) DEFAULT ''::character varying,
186 arin_netname character varying(20) DEFAULT 'ISP'::character varying
187);
188
189ALTER TABLE ONLY alloctypes
190 ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type");
191
192
193-- Initial/standard allocation types. Update def_custid and arin_netname as appropriate.
194COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
195cn Customer netblock Customer netblock 0 ISPCUST
196si Static IP - Server pool Server pool IP 20 ISP
197ci Static IP - Cable Static cable IP 21 ISP
198di Static IP - DSL Static DSL IP 22 ISP
199mi Static IP - Dialup Static dialup IP 23 ISP
200wi Static IP - Wireless Static wireless IP 24 ISP
201sd Static Pool - Servers Server pool 40 5554242 ISP
202cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
203dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
204mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
205wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
206en End-use netblock End-use netblock 100 5554242 ISP
207me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
208de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
209ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
210we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
211ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
212li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
213ai Static IP - Management Static management IP 192 NOC-VPN ISP
214bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
215ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
216ad Static Pool - Management Management pool 196 NOC-VPN ISP
217bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
218in Internal netblock Internal netblock 199 5554242 ISP
219wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP
220pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL
221ac Reserve for ATM ATM blocks 202 5554242 ISP
222fc Reserve for fibre Fibre blocks 203 5554242 ISP
223wr CORE/WAN block CORE/WAN block 220 5554242 ISP
224pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
225ar ATM block ATM block 222 ISP
226fr Fibre Fibre 223 ISP
227rm Routing aggregation Routing aggregation 500 5554242 ISP
228mm Master block Master block 999 5554242 ISP
229\.
230
231-- User data table - required for proper ACLs
232CREATE TABLE users (
233 username character varying(256) NOT NULL,
234 "password" character varying(256) DEFAULT ''::character varying,
235 acl character varying(256) DEFAULT 'b'::character varying
236);
237
238ALTER TABLE ONLY users
239 ADD CONSTRAINT users_pkey PRIMARY KEY (username);
240
241-- Default password is admin
242INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsAm');
243
244
245-- Trigger and matching function to update modifystamp on allocations, poolips
246CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
247 BEGIN
248 NEW.modifystamp := ''now'';
249 RETURN NEW;
250 END;
251' LANGUAGE 'plpgsql';
252
253CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
254 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
255
256CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
257 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
Note: See TracBrowser for help on using the repository browser.