-- Need to do this or our triggers don't work. Why do we need to do this? -- CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/plpgsql' LANGUAGE C; -- CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler"; -- Table for VRF list CREATE TABLE vrfs ( vrf varchar(32) DEFAULT '' NOT NULL, comment text DEFAULT '' NOT NULL, location varchar(4) DEFAULT '' NOT NULL ); COPY vrfs (vrf, comment, location) FROM stdin; DEFAULT Initial default VRF \. ALTER TABLE ONLY vrfs ADD CONSTRAINT vrfs_pkey PRIMARY KEY (vrf); -- Table for tracking active netblock assignments and containers. -- Note that maskbits is obsolete but left in for a release cycle or two -- so that legacy dumps can still be loaded, in the unlikely event. CREATE TABLE allocations ( cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL, "type" character(2) DEFAULT ''::bpchar, city character varying(30) DEFAULT ''::character varying, description character varying(128) DEFAULT ''::character varying, notes text DEFAULT ''::text, maskbits integer DEFAULT 128, circuitid character varying(128) DEFAULT ''::character varying, createstamp timestamp with time zone DEFAULT now(), modifystamp timestamp with time zone DEFAULT now(), privdata text DEFAULT ''::text NOT NULL, custid character varying(16) DEFAULT ''::character varying, swip character(1) DEFAULT 'n'::bpchar, vrf text DEFAULT ''::text NOT NULL, vlan text DEFAULT ''::text NOT NULL, rdns text DEFAULT ''::text NOT NULL, parent_id integer DEFAULT 0 NOT NULL, master_id integer DEFAULT 0 NOT NULL, backup_id integer DEFAULT 0 NOT NULL, id serial NOT NULL ); ALTER TABLE ONLY allocations ADD CONSTRAINT allocations_pkey PRIMARY KEY (id); CREATE UNIQUE INDEX allocations_skey ON allocations (cidr,vrf,type); -- Customer or POP site cities or locations. CREATE TABLE cities ( id serial NOT NULL, city character varying(30) DEFAULT ''::character varying NOT NULL, routing character(1) DEFAULT 'n'::bpchar NOT NULL ); ALTER TABLE ONLY cities ADD CONSTRAINT cities_pkey PRIMARY KEY (id); -- Local table for rWHOIS customer data CREATE TABLE customers ( custid character varying(16) DEFAULT ''::character varying NOT NULL, name character varying(64), street character varying(25), street2 character varying(25), city character varying(30), province character(2), country character(2), pocode character varying(7), phone character varying(15), tech_handle character varying(50), abuse_handle character varying(50), admin_handle character varying(50), def_rdns character varying(40), special text ); ALTER TABLE ONLY customers ADD CONSTRAINT customers_pkey PRIMARY KEY (custid); -- Flag table for deciding if we can usefully do rDNS RPC calls. CREATE TABLE dnsavail ( "zone" cidr, "location" varchar(4) DEFAULT '', parent_alloc integer NOT NULL ); ALTER TABLE ONLY dnsavail ADD CONSTRAINT dnsavail_pkey PRIMARY KEY ("zone",parent_alloc); -- Store backup fields in a separate table CREATE TABLE backuplist ( backup_id serial NOT NULL, bkbrand text, bkmodel text, bktype text, bkport integer, bksrc text, bkuser text, bkvpass text, bkepass text, bkip inet ); -- Table for tracking netblocks available for assignment CREATE TABLE freeblocks ( cidr cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL, maskbits integer DEFAULT 128, city character varying(30) DEFAULT ''::character varying, routed character(1) DEFAULT 'n'::bpchar, vrf text DEFAULT ''::text NOT NULL, parent_id integer DEFAULT 0 NOT NULL, master_id integer DEFAULT 0 NOT NULL, reserve_for integer DEFAULT 0 NOT NULL, id serial NOT NULL ); ALTER TABLE ONLY freeblocks ADD CONSTRAINT freeblocks_pkey PRIMARY KEY (cidr, parent_id); -- Table for arbitrary infonotices tagged to an allocation CREATE TABLE blocknotices ( alloc_id integer NOT NULL, ipflag boolean DEFAULT false NOT NULL, notice text DEFAULT ''::text NOT NULL ); ALTER TABLE ONLY blocknotices ADD CONSTRAINT blocknotices_pkey PRIMARY KEY (alloc_id, ipflag); -- Network nodes - allows finding customers affected by a broken quickly CREATE TABLE noderef ( block inet NOT NULL PRIMARY KEY, node_id integer ); CREATE TABLE nodes ( node_id serial NOT NULL PRIMARY KEY, node_type character varying(2), node_name character varying(40), node_ip inet ); -- Email notifications on CREATE TABLE notify ( action varchar(5) NOT NULL PRIMARY KEY, reciplist varchar(500) ); -- Table for tracking single IP assignments. Single static IP assignments -- need somewhat stronger clustering than provided by other types. CREATE TABLE poolips ( pool cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL, ip cidr DEFAULT '255.255.255.255/32'::cidr NOT NULL, city character varying(30) DEFAULT ''::character varying NOT NULL, "type" character(2) DEFAULT ''::bpchar NOT NULL, available character(1) DEFAULT 'y'::bpchar NOT NULL, notes text DEFAULT ''::text NOT NULL, description character varying(128) DEFAULT ''::character varying NOT NULL, circuitid character varying(128) DEFAULT ''::character varying NOT NULL, privdata text DEFAULT ''::text NOT NULL, custid character varying(16) DEFAULT ''::character varying, createstamp timestamp with time zone DEFAULT now(), modifystamp timestamp with time zone DEFAULT now(), vrf text DEFAULT ''::text NOT NULL, vlan text DEFAULT ''::text NOT NULL, rdns text DEFAULT ''::text NOT NULL, parent_id integer DEFAULT 0 NOT NULL, master_id integer DEFAULT 0 NOT NULL, backup_id integer DEFAULT 0 NOT NULL, id serial NOT NULL, CONSTRAINT poolips_available_check CHECK (((available = 'y'::bpchar) OR (available = 'n'::bpchar))) ); ALTER TABLE ONLY poolips ADD CONSTRAINT poolips_pkey PRIMARY KEY (ip, parent_id); -- Combined netblock+IP view for searches CREATE VIEW searchme AS SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, allocations.description, allocations.notes, allocations.circuitid, allocations.vrf, allocations.vlan, allocations.id, allocations.parent_id, allocations.master_id, 'n' AS available FROM allocations UNION SELECT poolips.ip AS cidr, poolips.custid, poolips."type", poolips.city, poolips.description, poolips.notes, poolips.circuitid, poolips.vrf, poolips.vlan, poolips.id, poolips.parent_id, poolips.master_id, poolips.available FROM poolips; -- Type list for assignments CREATE TABLE alloctypes ( "type" character(2) DEFAULT ''::bpchar NOT NULL, listname character varying(40) DEFAULT ''::character varying, dispname character varying(40) DEFAULT ''::character varying, listorder integer DEFAULT 0, def_custid character varying(16) DEFAULT ''::character varying, arin_netname character varying(20) DEFAULT 'ISP'::character varying ); ALTER TABLE ONLY alloctypes ADD CONSTRAINT alloctypes_pkey PRIMARY KEY ("type"); -- Initial/standard allocation types. Update def_custid and arin_netname as appropriate. COPY alloctypes ("type", listname, dispname, listorder, def_custid, arin_netname) FROM stdin; cn Customer netblock Customer netblock 0 ISPCUST si Static IP - Server pool Server pool IP 20 ISP ci Static IP - Cable Static cable IP 21 ISP di Static IP - DSL Static DSL IP 22 ISP mi Static IP - Dialup Static dialup IP 23 ISP wi Static IP - Wireless Static wireless IP 24 ISP sd Static Pool - Servers Server pool 40 5554242 ISP cd Static Pool - Cable Cable pool 41 CBL-BUS ISP-STATIC-CABLE dp Static Pool - DSL DSL pool 42 DSL-BUS ISP-STATIC-DSL mp Static Pool - Dialup Static dialup pool 43 DIAL-BUS ISP-STATIC-DIAL wp Static Pool - Wireless Static wireless pool 44 WL-BUS ISP-STATIC-WIFI en End-use netblock End-use netblock 100 5554242 ISP me Dialup netblock Dialup netblock 101 DIAL-RES ISP-DIAL de Dynamic DSL block Dynamic DSL block 102 DSL-RES ISP-DSL ce Dynamic cable block Dynamic cable block 103 CBL-RES ISP-CABLE we Dynamic WiFi block Dynamic WiFi block 104 WL-RES ISP-WIFI ve Dynamic VoIP block Dynamic VoIP block 105 DYN-VOIP ISP li Static IP - LAN/POP Static LAN/POP IP 190 NOC-VPN ISP ai Static IP - Management Static management IP 192 NOC-VPN ISP bi Static IP - Wifi CPE Wifi CPE IP 193 ISP ld Static Pool - LAN/POP LAN pool 195 NOC-VPN ISP ad Static Pool - Management Management pool 196 NOC-VPN ISP bd Static pool - Wifi CPE Wifi CPE pool 197 ISP in Internal netblock Internal netblock 199 5554242 ISP wc Reserve for CORE/WAN blocks CORE/WAN blocks 200 5554242 ISP pc Reserve for dynamic-route DSL netblocks Dynamic-route netblocks 201 5554242 ISP-STATIC-DSL ac Reserve for ATM ATM blocks 202 5554242 ISP fc Reserve for fibre Fibre blocks 203 5554242 ISP wr CORE/WAN block CORE/WAN block 220 5554242 ISP pr Dynamic-route DSL netblock (cust) Dynamic-route DSL (cust) 221 ISPCUST ar ATM block ATM block 222 ISP fr Fibre Fibre 223 ISP rm Routing aggregation Routing aggregation 500 5554242 ISP mm Master block Master block 999 5554242 ISP \. -- User data table - required for proper ACLs CREATE TABLE users ( username character varying(256) NOT NULL, "password" character varying(256) DEFAULT ''::character varying, acl character varying(256) DEFAULT 'b'::character varying ); ALTER TABLE ONLY users ADD CONSTRAINT users_pkey PRIMARY KEY (username); -- Default password is admin INSERT INTO users VALUES ('admin','luef5C4XumqIs','bacdsAm'); -- Trigger and matching function to update modifystamp on allocations, poolips CREATE FUNCTION up_modtime () RETURNS OPAQUE AS ' BEGIN NEW.modifystamp := ''now''; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER up_modtime BEFORE UPDATE ON allocations FOR EACH ROW EXECUTE PROCEDURE up_modtime(); CREATE TRIGGER up_modtime BEFORE UPDATE ON poolips FOR EACH ROW EXECUTE PROCEDURE up_modtime();