From 10493f224f11bcf1dac3b509c3e440cf63fe39fc Mon Sep 17 00:00:00 2001 From: Kristian Lyngstol Date: Sat, 26 Mar 2016 03:59:37 +0100 Subject: NMS: Dump updated schema It needs cleaning, obviously, but at least it's there. --- nms/nms-dump.sql | 149 ++++++++++++++++++++++++++++++++++++++++++++++++++++--- 1 file changed, 142 insertions(+), 7 deletions(-) (limited to 'nms/nms-dump.sql') diff --git a/nms/nms-dump.sql b/nms/nms-dump.sql index 335a3b0..25ba3ac 100644 --- a/nms/nms-dump.sql +++ b/nms/nms-dump.sql @@ -49,7 +49,7 @@ SET default_with_oids = false; CREATE TABLE dhcp ( switch integer, - "time" timestamp without time zone, + "time" timestamp with time zone, mac macaddr, ip inet ); @@ -78,9 +78,11 @@ ALTER TABLE linknet_ping OWNER TO nms; CREATE TABLE linknets ( linknet integer NOT NULL, switch1 integer NOT NULL, - addr1 inet NOT NULL, + addr1 inet, switch2 integer NOT NULL, - addr2 inet NOT NULL + addr2 inet, + port1 character varying(10), + port2 character varying(10) ); @@ -253,19 +255,26 @@ ALTER TABLE switch_temp OWNER TO nms; CREATE TABLE switches ( switch integer DEFAULT nextval(('"switches_switch_seq"'::text)::regclass) NOT NULL, - ip inet, + mgmt_v4_addr inet, sysname character varying NOT NULL, switchtype character varying DEFAULT 'ex2200'::character varying NOT NULL, last_updated timestamp with time zone, locked boolean DEFAULT false NOT NULL, poll_frequency interval DEFAULT '00:01:00'::interval NOT NULL, - community character varying DEFAULT 'public'::character varying NOT NULL, + community character varying DEFAULT 'FullPuppTilNMS'::character varying NOT NULL, lldp_chassis_id character varying, - secondary_ip inet, + mgmt_v6_addr inet, placement box, subnet4 cidr, subnet6 cidr, - distro character varying + distro_name character varying, + distro_phy_port character varying(100), + mgmt_v6_gw inet, + mgmt_v4_gw inet, + mgmt_vlan integer DEFAULT 666, + traffic_vlan integer, + last_config_fetch timestamp with time zone, + current_mac macaddr ); @@ -285,6 +294,17 @@ CREATE SEQUENCE switches_switch_seq ALTER TABLE switches_switch_seq OWNER TO nms; +-- +-- Name: test_table; Type: TABLE; Schema: public; Owner: nms; Tablespace: +-- + +CREATE TABLE test_table ( + test timestamp with time zone +); + + +ALTER TABLE test_table OWNER TO nms; + -- -- Name: linknet; Type: DEFAULT; Schema: public; Owner: nms -- @@ -354,6 +374,20 @@ ALTER TABLE ONLY switches ADD CONSTRAINT switches_sysname_key1 UNIQUE (sysname); +-- +-- Name: dhcp_ip; Type: INDEX; Schema: public; Owner: nms; Tablespace: +-- + +CREATE INDEX dhcp_ip ON dhcp USING btree (ip); + + +-- +-- Name: dhcp_mac; Type: INDEX; Schema: public; Owner: nms; Tablespace: +-- + +CREATE INDEX dhcp_mac ON dhcp USING btree (mac); + + -- -- Name: dhcp_switch; Type: INDEX; Schema: public; Owner: nms; Tablespace: -- @@ -375,6 +409,13 @@ CREATE INDEX dhcp_time ON dhcp USING btree ("time"); CREATE INDEX ping_index ON ping USING btree ("time"); +-- +-- Name: ping_secondary_index; Type: INDEX; Schema: public; Owner: nms; Tablespace: +-- + +CREATE INDEX ping_secondary_index ON ping_secondary_ip USING btree ("time"); + + -- -- Name: polls_ifname; Type: INDEX; Schema: public; Owner: nms; Tablespace: -- @@ -514,6 +555,67 @@ REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM postgres; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO PUBLIC; +GRANT ALL ON SCHEMA public TO fap; + + +-- +-- Name: dhcp; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE dhcp FROM PUBLIC; +REVOKE ALL ON TABLE dhcp FROM nms; +GRANT ALL ON TABLE dhcp TO nms; +GRANT ALL ON TABLE dhcp TO fap; + + +-- +-- Name: linknet_ping; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE linknet_ping FROM PUBLIC; +REVOKE ALL ON TABLE linknet_ping FROM nms; +GRANT ALL ON TABLE linknet_ping TO nms; +GRANT ALL ON TABLE linknet_ping TO fap; + + +-- +-- Name: linknets; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE linknets FROM PUBLIC; +REVOKE ALL ON TABLE linknets FROM nms; +GRANT ALL ON TABLE linknets TO nms; +GRANT ALL ON TABLE linknets TO fap; + + +-- +-- Name: ping; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE ping FROM PUBLIC; +REVOKE ALL ON TABLE ping FROM nms; +GRANT ALL ON TABLE ping TO nms; +GRANT ALL ON TABLE ping TO fap; + + +-- +-- Name: ping_secondary_ip; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE ping_secondary_ip FROM PUBLIC; +REVOKE ALL ON TABLE ping_secondary_ip FROM nms; +GRANT ALL ON TABLE ping_secondary_ip TO nms; +GRANT ALL ON TABLE ping_secondary_ip TO fap; + + +-- +-- Name: polls; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE polls FROM PUBLIC; +REVOKE ALL ON TABLE polls FROM nms; +GRANT ALL ON TABLE polls TO nms; +GRANT ALL ON TABLE polls TO fap; -- @@ -523,6 +625,7 @@ GRANT ALL ON SCHEMA public TO PUBLIC; REVOKE ALL ON TABLE seen_mac FROM PUBLIC; REVOKE ALL ON TABLE seen_mac FROM nms; GRANT ALL ON TABLE seen_mac TO nms; +GRANT ALL ON TABLE seen_mac TO fap; -- @@ -533,6 +636,7 @@ REVOKE ALL ON TABLE snmp FROM PUBLIC; REVOKE ALL ON TABLE snmp FROM nms; GRANT ALL ON TABLE snmp TO nms; GRANT ALL ON TABLE snmp TO postgres; +GRANT ALL ON TABLE snmp TO fap; -- @@ -545,6 +649,26 @@ GRANT ALL ON SEQUENCE snmp_id_seq TO nms; GRANT ALL ON SEQUENCE snmp_id_seq TO postgres; +-- +-- Name: switch_comments; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE switch_comments FROM PUBLIC; +REVOKE ALL ON TABLE switch_comments FROM nms; +GRANT ALL ON TABLE switch_comments TO nms; +GRANT ALL ON TABLE switch_comments TO fap; + + +-- +-- Name: switch_temp; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE switch_temp FROM PUBLIC; +REVOKE ALL ON TABLE switch_temp FROM nms; +GRANT ALL ON TABLE switch_temp TO nms; +GRANT ALL ON TABLE switch_temp TO fap; + + -- -- Name: switches; Type: ACL; Schema: public; Owner: nms -- @@ -552,6 +676,17 @@ GRANT ALL ON SEQUENCE snmp_id_seq TO postgres; REVOKE ALL ON TABLE switches FROM PUBLIC; REVOKE ALL ON TABLE switches FROM nms; GRANT ALL ON TABLE switches TO nms; +GRANT ALL ON TABLE switches TO fap; + + +-- +-- Name: test_table; Type: ACL; Schema: public; Owner: nms +-- + +REVOKE ALL ON TABLE test_table FROM PUBLIC; +REVOKE ALL ON TABLE test_table FROM nms; +GRANT ALL ON TABLE test_table TO nms; +GRANT ALL ON TABLE test_table TO fap; -- -- cgit v1.2.3