1-- 2-- # su - postgres (or whatever your database runs as ... usually postgres) 3-- $ psql -d pmacct -f pmacct-create-table_v3.pgsql 4-- 5 6-- Tables 7DROP TABLE IF EXISTS acct_uni_v3; 8CREATE TABLE acct_uni_v3 ( 9 agent_id BIGINT NOT NULL DEFAULT 0, 10 mac_src CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0', 11 mac_dst CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0', 12 vlan INT NOT NULL DEFAULT 0, 13 ip_src CHAR(45) NOT NULL DEFAULT '0.0.0.0', 14 ip_dst CHAR(45) NOT NULL DEFAULT '0.0.0.0', 15 port_src INT NOT NULL DEFAULT 0, 16 port_dst INT NOT NULL DEFAULT 0, 17 ip_proto SMALLINT NOT NULL DEFAULT 0, 18 tos INT NOT NULL DEFAULT 0, 19 packets INT NOT NULL, 20 bytes BIGINT NOT NULL, 21 stamp_inserted timestamp without time zone NOT NULL DEFAULT '0001-01-01 00:00:00', 22 stamp_updated timestamp without time zone, 23 CONSTRAINT acct_uni_v3_pk PRIMARY KEY (agent_id, mac_src, mac_dst, vlan, ip_src, ip_dst, port_src, port_dst, ip_proto, tos, stamp_inserted) 24); 25 26DROP TABLE IF EXISTS acct_v3; 27CREATE TABLE acct_v3 ( 28 agent_id BIGINT NOT NULL DEFAULT 0, 29 mac_src macaddr NOT NULL DEFAULT '0:0:0:0:0:0', 30 mac_dst macaddr NOT NULL DEFAULT '0:0:0:0:0:0', 31 vlan INT NOT NULL DEFAULT 0, 32 ip_src inet NOT NULL DEFAULT '0.0.0.0', 33 ip_dst inet NOT NULL DEFAULT '0.0.0.0', 34 port_src INT NOT NULL DEFAULT 0, 35 port_dst INT NOT NULL DEFAULT 0, 36 ip_proto SMALLINT NOT NULL DEFAULT 0, 37 tos INT NOT NULL DEFAULT 0, 38 packets INT NOT NULL, 39 bytes BIGINT NOT NULL, 40 stamp_inserted timestamp without time zone NOT NULL DEFAULT '0001-01-01 00:00:00', 41 stamp_updated timestamp without time zone, 42 CONSTRAINT acct_v3_pk PRIMARY KEY (agent_id, mac_src, mac_dst, vlan, ip_src, ip_dst, port_src, port_dst, ip_proto, tos, stamp_inserted) 43); 44 45DROP TABLE IF EXISTS acct_as_v3; 46CREATE TABLE acct_as_v3 ( 47 agent_id BIGINT NOT NULL DEFAULT 0, 48 mac_src macaddr NOT NULL DEFAULT '0:0:0:0:0:0', 49 mac_dst macaddr NOT NULL DEFAULT '0:0:0:0:0:0', 50 vlan INT NOT NULL DEFAULT 0, 51 ip_src INT NOT NULL DEFAULT 0, 52 ip_dst INT NOT NULL DEFAULT 0, 53 port_src INT NOT NULL DEFAULT 0, 54 port_dst INT NOT NULL DEFAULT 0, 55 ip_proto SMALLINT NOT NULL DEFAULT 0, 56 tos INT NOT NULL DEFAULT 0, 57 packets INT NOT NULL, 58 bytes BIGINT NOT NULL, 59 stamp_inserted timestamp without time zone NOT NULL DEFAULT '0001-01-01 00:00:00', 60 stamp_updated timestamp without time zone, 61 CONSTRAINT acct_as_v3_pk PRIMARY KEY (agent_id, mac_src, mac_dst, vlan, ip_src, ip_dst, port_src, port_dst, ip_proto, tos, stamp_inserted) 62); 63 64DROP TABLE IF EXISTS proto; 65CREATE TABLE proto ( 66 num SMALLINT NOT NULL, 67 description CHAR(20), 68 CONSTRAINT proto_pk PRIMARY KEY (num) 69); 70 71COPY proto FROM stdin USING DELIMITERS ','; 720,ip 731,icmp 742,igmp 753,ggp 764,ipencap 775,st 786,tcp 798,egp 809,igp 8117,udp 8218,mux 8327,rdp 8429,iso-tp4 8530,netblt 8637,ddp 8739,idpr-cmtp 8841,ipv6 8943,ipv6-route 9044,ipv6-frag 9146,rsvp 9247,gre 9350,ipv6-crypt 9451,ipv6-auth 9555,mobile 9656,tlsp 9758,ipv6-icmp 9859,ipv6-nonxt 9960,ipv6-opts 10080,iso-ip 10183,vines 10288,eigrp 10389,ospf 10490,sprite-rpc 10593,ax-25 10694,ipip 10798,encap 108102,pnni 109108,IPcomp 110111,ipx-in-ip 111112,vrrp 112115,l2tp 113124,isis 114132,sctp 115133,fc 116\. 117 118-- Perms 119GRANT SELECT, INSERT, UPDATE, DELETE ON acct_uni_v3 TO pmacct; 120GRANT SELECT, INSERT, UPDATE, DELETE ON acct_v3 TO pmacct; 121GRANT SELECT, INSERT, UPDATE, DELETE ON acct_as_v3 TO pmacct; 122GRANT SELECT, INSERT, UPDATE, DELETE ON proto TO pmacct; 123 124