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