source: branches/stable/cgi-bin/ipdb.psql@ 359

Last change on this file since 359 was 353, checked in by Kris Deugau, 18 years ago

/branches/stable

Update ipdb.psql with current alloctypes
Change "Internal netblock" entry to be visible in allocation lists
Remove hackery in admin.cgi that allowed changing a block to
"Internal netblock" even if it wasn't retrieved from the database
like everything else.
File size: 7.0 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);
42
43REVOKE ALL on "masterblocks" from PUBLIC;
44GRANT ALL on "masterblocks" to "ipdb";
45
46CREATE TABLE "routed" (
47 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
48 "maskbits" integer DEFAULT 128,
49 "city" character varying(30) DEFAULT '',
50 "ctime" timestamp DEFAULT now()
51);
52
53REVOKE ALL on "routed" from PUBLIC;
54GRANT ALL on "routed" to "ipdb";
55GRANT SELECT on "routed" to "ipdb";
56
57CREATE TABLE "temp" (
58 "ofs" integer
59);
60
61REVOKE ALL on "temp" from PUBLIC;
62GRANT ALL on "temp" to "ipdb";
63
64CREATE TABLE "freeblocks" (
65 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
66 "maskbits" integer DEFAULT 128,
67 "city" character varying(30) DEFAULT '',
68 "routed" character(1) DEFAULT 'n'
69);
70
71REVOKE ALL on "freeblocks" from PUBLIC;
72GRANT ALL on "freeblocks" to "ipdb";
73
74CREATE TABLE "poolips" (
75 "pool" cidr DEFAULT '255.255.255.255/32' NOT NULL,
76 "ip" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
77 "oldcustid" character varying(16) DEFAULT '' NOT NULL,
78 "city" character varying(30) DEFAULT '' NOT NULL,
79 "type" character(2) DEFAULT '' NOT NULL,
80 "available" character(1) DEFAULT 'y' NOT NULL,
81 "notes" text DEFAULT '' NOT NULL,
82 "description" character varying(64) DEFAULT '' NOT NULL,
83 "circuitid" character varying(128) DEFAULT '' NOT NULL,
84 "privdata" text DEFAULT '' NOT NULL,
85 "custid" character varying(16) DEFAULT '',
86 "createstamp" timestamp DEFAULT now(),
87 "modifystamp" timestamp DEFAULT now(),
88 CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar)))
89);
90
91REVOKE ALL on "poolips" from PUBLIC;
92GRANT ALL on "poolips" to "ipdb";
93
94CREATE TABLE "allocations" (
95 "cidr" cidr DEFAULT '255.255.255.255/32' NOT NULL PRIMARY KEY,
96 "oldcustid" character varying(16) DEFAULT '',
97 "type" character(2) DEFAULT '',
98 "city" character varying(30) DEFAULT '',
99 "description" character varying(64) DEFAULT '',
100 "notes" text DEFAULT '',
101 "maskbits" integer DEFAULT 128,
102 "circuitid" character varying(128) DEFAULT '',
103 "createstamp" timestamp DEFAULT now(),
104 "modifystamp" timestamp DEFAULT now(),
105 "privdata" text DEFAULT '' NOT NULL,
106 "custid" character varying(16) DEFAULT '',
107 swip character(1) DEFAULT 'n'
108);
109
110REVOKE ALL on "allocations" from PUBLIC;
111GRANT ALL on "allocations" to "ipdb";
112
113CREATE VIEW "searchme" as SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes FROM allocations UNION SELECT poolips.ip, poolips.custid, poolips.type, poolips.city, poolips.description, poolips.notes FROM poolips;
114
115REVOKE ALL on "searchme" from PUBLIC;
116GRANT ALL on "searchme" to "ipdb";
117
118CREATE TABLE "alloctypes" (
119 "type" character(2) DEFAULT '' NOT NULL PRIMARY KEY,
120 "listname" character varying(40) DEFAULT '',
121 "dispname" character varying(40) DEFAULT '',
122 "listorder" integer DEFAULT 0,
123 "def_custid" character varying(16) DEFAULT '',
124 "arin_netname" character varying(20) DEFAULT 'ISP'
125);
126
127--
128-- Name: alloctypes; Type: TABLE DATA; Schema: public; Owner: ipdb
129--
130
131COPY "alloctypes" FROM stdin;
132cn Customer netblock Customer netblock 0 ISPCUST
133si Static IP - Server pool Server pool IP 20 6750400 ISP
134ci Static IP - Cable Static cable IP 21 ISP
135di Static IP - DSL Static DSL IP 22 ISP
136mi Static IP - Dialup Static dialup IP 23 ISP
137wi Static IP - Wireless Static wireless IP 24 ISP
138sd Static Pool - Servers Server pool 40 6750400 ISP
139cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE
140dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL
141mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL
142wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI
143en End-use netblock End-use netblock 100 6750400 ISP
144me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL
145de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL
146ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE
147we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI
148ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP
149li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP
150li Static IP - Management Static management IP 191 NOC-VPN ISP
151ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP
152ld Static Pool - Management Management pool 196 NOC-VPN ISP
153in Internal netblock Internal netblock 199 6750400 ISP
154wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 6750400 ISP
155pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 6750400 ISP-STATIC-DSL
156ac Reserve for ATM ATM blocks 202 6750400 ISP
157fc Reserve for fibre Fibre blocks 203 6750400 ISP
158wr CORE/WAN block CORE/WAN block 220 6750400 ISP
159pr Dynamic-route DSL netblock Dynamic-route DSL 221 ISP
160ar ATM block ATM block 222 ISP
161fr Fibre Fibre 223 ISP
162rm Routing Routed netblock 500 6750400 ISP
163mm Master block Master block 999 6750400 ISP
164\.
165
166REVOKE ALL on "alloctypes" from PUBLIC;
167GRANT ALL on "alloctypes" to "ipdb";
168
169CREATE TABLE "cities" (
170 "id" serial NOT NULL PRIMARY KEY,
171 "city" character varying(30) DEFAULT '' NOT NULL,
172 "routing" character(1) DEFAULT 'n' NOT NULL
173);
174
175REVOKE ALL on "cities" from PUBLIC;
176GRANT ALL on "cities" to "ipdb";
177
178--
179-- Trigger and matching function to update modifystamp on allocations, poolips
180--
181CREATE FUNCTION up_modtime () RETURNS OPAQUE AS '
182 BEGIN
183 NEW.modifystamp := ''now'';
184 RETURN NEW;
185 END;
186' LANGUAGE 'plpgsql';
187
188CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations
189 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
190
191CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips
192 FOR EACH ROW EXECUTE PROCEDURE up_modtime();
193
194--
195-- User data table - required for proper ACLs
196--
197
198CREATE TABLE "users" (
199 "username" varchar(16) NOT NULL PRIMARY KEY,
200 "password" varchar(16) DEFAULT '',
201 "acl" varchar(16) DEFAULT 'b'
202);
203
204-- Default password is admin
205INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsA');
206
207CREATE TABLE "dns" (
208 "ip" inet NOT NULL PRIMARY KEY,
209 "hostname" character varying(128),
210 "auto" character(1) DEFAULT 'y'
211);
Note: See TracBrowser for help on using the repository browser.