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

Last change on this file since 388 was 388, checked in by Kris Deugau, 15 years ago

/trunk

Update alloctypes preseeding
File size: 7.3 KB
Line 
1DROP DATABASE ipdb;
2
3CREATE USER ipdb WITH PASSWORD 'ipdbpwd';
4
5CREATE DATABASE ipdb;
6
7-- Need to do this or our triggers don't work. Why do we need to do this?
8CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C;
9CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
10
11UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE usename='ipdb')
12 WHERE datname='ipdb';
13
14\connect ipdb ipdb
15
16CREATE TABLE "customers" (
17 "custid" character varying(16) DEFAULT '' NOT NULL,
18 "name" character varying(64),
19 "street" character varying(25),
20 "street2" character varying(25),
21 "city" character varying(30),
22 "province" character(2),
23 "country" character(2),
24 "pocode" character varying(7),
25 "phone" character varying(15),
26 "tech_handle" character varying(50),
27 "abuse_handle" character varying(50),
28 "admin_handle" character varying(50),
29 "def_rdns" character varying(40),
30 "special" text,
31 Constraint "customers_pkey" Primary Key ("custid")
32);
33
34REVOKE ALL on "customers" from PUBLIC;
35GRANT ALL on "customers" to "ipdb";
36
37CREATE TABLE "masterblocks" (
38 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
39 "ctime" timestamp DEFAULT now(),
40 "mtime" timestamp DEFAULT now(),
41 "rwhois" character(1) DEFAULT 'n' NOT NULL
42);
43
44REVOKE ALL on "masterblocks" from PUBLIC;
45GRANT ALL on "masterblocks" to "ipdb";
46
47CREATE TABLE "routed" (
48 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
49 "maskbits" integer DEFAULT 128,
50 "city" character varying(30) DEFAULT '',
51 "ctime" timestamp DEFAULT now()
52);
53
54REVOKE ALL on "routed" from PUBLIC;
55GRANT ALL on "routed" to "ipdb";
56GRANT SELECT on "routed" to "ipdb";
57
58CREATE TABLE "temp" (
59 "ofs" integer
60);
61
62REVOKE ALL on "temp" from PUBLIC;
63GRANT ALL on "temp" to "ipdb";
64
65CREATE TABLE "freeblocks" (
66 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
67 "maskbits" integer DEFAULT 128,
68 "city" character varying(30) DEFAULT '',
69 "routed" character(1) DEFAULT 'n'
70);
71
72REVOKE ALL on "freeblocks" from PUBLIC;
73GRANT ALL on "freeblocks" to "ipdb";
74
75CREATE TABLE "poolips" (
76 "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
77 "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
78 "oldcustid" character varying(16) DEFAULT '' NOT NULL,
79 "city" character varying(30) DEFAULT '' NOT NULL,
80 "type" character(2) DEFAULT '' NOT NULL,
81 "available" character(1) DEFAULT 'y' NOT NULL,
82 "notes" text DEFAULT '' NOT NULL,
83 "description" character varying(64) DEFAULT '' NOT NULL,
84 "circuitid" character varying(128) DEFAULT '' NOT NULL,
85 "privdata" text DEFAULT '' NOT NULL,
86 "custid" character varying(16) DEFAULT '',
87 "createstamp" timestamp DEFAULT now(),
88 "modifystamp" timestamp DEFAULT now(),
89 CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
90);
91
92REVOKE ALL on "poolips" from PUBLIC;
93GRANT ALL on "poolips" to "ipdb";
94
95CREATE TABLE "allocations" (
96 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
97 "oldcustid" character varying(16) DEFAULT '',
98 "type" character(2) DEFAULT '',
99 "city" character varying(30) DEFAULT '',
100 "description" character varying(64) DEFAULT '',
101 "notes" text DEFAULT '',
102 "maskbits" integer DEFAULT 128,
103 "circuitid" character varying(128) DEFAULT '',
104 "createstamp" timestamp DEFAULT now(),
105 "modifystamp" timestamp DEFAULT now(),
106 "privdata" text DEFAULT '' NOT NULL,
107 "custid" character varying(16) DEFAULT '',
108 swip character(1) DEFAULT 'n'
109);
110
111REVOKE ALL on "allocations" from PUBLIC;
112GRANT ALL on "allocations" to "ipdb";
113
114CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.oldcustid, allocations.circuitid FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes, poolips.oldcustid, poolips.circuitid FROM poolips;
115
116REVOKE ALL on "searchme" from PUBLIC;
117GRANT ALL on "searchme" to "ipdb";
118
119CREATE TABLE "alloctypes" (
120 "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
121 "listname" character varying(40) DEFAULT '',
122 "dispname" character varying(40) DEFAULT '',
123 "listorder" integer DEFAULT 0,
124 "def_custid" character varying(16) DEFAULT '',
125 "arin_netname" character varying(20) DEFAULT 'ISP'
126);
127
128--
129-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
130--
131
132COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin;
133fc Reserve for fibre Fibre blocks 203 6750400 ISP
134fr Fibre Fibre 223 ISP
135ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
136li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
137ai Static IP - Managment Static management IP 192 NOC-VPN ISP
138ad Static Pool - Managment Management pool 196 NOC-VPN ISP
139in Internal netblock Internal netblock 199 6750400 ISP
140pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST
141cn Customer netblock Customer netblock 0 ISPCUST
142cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
143dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
144mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
145wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
146me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
147de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
148ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
149we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
150pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 6750400 ISP-STATIC-DSL
151ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
152wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 6750400 ISP
153wr CORE/WAN block CORE/WAN block 220 6750400 ISP
154sd Static Pool - Servers Server pool 40 6750400 ISP
155ac Reserve for ATM ATM blocks 202 6750400 ISP
156en End-use netblock End-use netblock 100 6750400 ISP
157mm Master block Master block 999 6750400 ISP
158ar ATM block ATM block 222 ISP
159rm Routing Routed netblock 500 6750400 ISP
160si Static IP - Server pool Server pool IP 20 ISP
161ci Static IP - Cable Static cable IP 21 ISP
162di Static IP - DSL Static DSL IP 22 ISP
163mi Static IP - Dialup Static dialup IP 23 ISP
164wi Static IP - Wireless Static wireless IP 24 ISP
165bi Static IP - Wifi CPE Wifi CPE IP 193 ISP
166bd Static pool - Wifi CPE Wifi CPE pool 197 ISP
167\.
168
169REVOKE ALL on "alloctypes" from PUBLIC;
170GRANT ALL on "alloctypes" to "ipdb";
171
172CREATE TABLE "cities" (
173 "id" serial NOT NULL PRIMARY KEY,
174 "city" character varying(30) DEFAULT '' NOT NULL,
175 "routing" character(1) DEFAULT 'n' NOT NULL
176);
177
178REVOKE ALL on "cities" from PUBLIC;
179GRANT ALL on "cities" to "ipdb";
180
181--
182-- Trigger and matching function to update modifystamp on allocations, poolips
183--
184CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
185 BEGIN
186 NEW.modifystamp := ''now'';
187 RETURN NEW;
188 END;
189' LANGUAGE 'plpgsql';
190
191CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
192 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
193
194CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
195 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
196
197--
198-- User data table - required for proper ACLs
199--
200
201CREATE TABLE "users" (
202 "username" varchar(16) NOT NULL PRIMARY KEY,
203 "password" varchar(16) DEFAULT '',
204 "acl" varchar(16) DEFAULT 'b'
205);
206
207-- Default password is admin
208INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
209
210CREATE TABLE "dns" (
211 "ip" inet NOT NULL PRIMARY KEY,
212 "hostname" character varying(128),
213 "auto" character(1) DEFAULT 'y'
214);
Note: See TracBrowser for help on using the repository browser.