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

Last change on this file since 409 was 409, checked in by Kris Deugau, 12 years ago

/trunk

Minor typofix on preseeded alloctypes. See #13.

File size: 7.6 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;
133cn      Customer netblock       Customer netblock       0               ISPCUST
134si      Static IP - Server pool Server pool IP  20              ISP
135ci      Static IP - Cable       Static cable IP 21              ISP
136di      Static IP - DSL Static DSL IP   22              ISP
137mi      Static IP - Dialup      Static dialup IP        23              ISP
138wi      Static IP - Wireless    Static wireless IP      24              ISP
139sd      Static Pool - Servers   Server pool     40      6750400 ISP
140cd      Static Pool - Cable     Cable pool      41      CBL-BUS ISP-STATIC-CABLE
141dp      Static Pool - DSL       DSL pool        42      DSL-BUS ISP-STATIC-DSL
142mp      Static Pool - Dialup    Static dialup pool      43      DIAL-BUS        ISP-STATIC-DIAL
143wp      Static Pool - Wireless  Static wireless pool    44      WL-BUS  ISP-STATIC-WIFI
144en      End-use netblock        End-use netblock        100     6750400 ISP
145me      Dialup netblock Dialup netblock 101     DIAL-RES        ISP-DIAL
146de      Dynamic DSL block       Dynamic DSL block       102     DSL-RES ISP-DSL
147ce      Dynamic cable block     Dynamic cable block     103     CBL-RES ISP-CABLE
148we      Dynamic WiFi block      Dynamic WiFi block      104     WL-RES  ISP-WIFI
149ve      Dynamic VoIP block      Dynamic VoIP block      105     DYN-VOIP        ISP
150li      Static IP - LAN/POP     Static LAN/POP IP       190     NOC-VPN ISP
151ai      Static IP - Management  Static management IP    192     NOC-VPN ISP
152bi      Static IP - Wifi CPE    Wifi CPE IP     193             ISP
153ld      Static Pool - LAN/POP   LAN pool        195     NOC-VPN ISP
154ad      Static Pool - Management        Management pool 196     NOC-VPN ISP
155bd      Static pool - Wifi CPE  Wifi CPE pool   197             ISP
156in      Internal netblock       Internal netblock       199     6750400 ISP
157wc      Reserve for CORE/WAN blocks     CORE/WAN blocks 200     6750400 ISP
158pc      Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201     6750400 ISP-STATIC-DSL
159ac      Reserve for ATM ATM blocks      202     6750400 ISP
160fc      Reserve for fibre       Fibre blocks    203     6750400 ISP
161wr      CORE/WAN block  CORE/WAN block  220     6750400 ISP
162pr      Dynamic-route DSL netblock (cust)       Dynamic-route DSL (cust)        221             ISPCUST
163ar      ATM block       ATM block       222             ISP
164fr      Fibre   Fibre   223             ISP
165rm      Routing Routed netblock 500     6750400 ISP
166mm      Master block    Master block    999     6750400 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);
215
216-- Network nodes - allows finding customers affected by a broken <x> quickly
217CREATE TABLE noderef (
218    block inet NOT NULL PRIMARY KEY,
219    node_id integer
220);
221
222CREATE TABLE nodes (
223    node_id serial NOT NULL PRIMARY KEY,
224    node_type character varying(2),
225    node_name character varying(40),
226    node_ip inet
227);
Note: See TracBrowser for help on using the repository browser.