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