-
-
Notifications
You must be signed in to change notification settings - Fork 44
/
tables.sql
249 lines (211 loc) · 8.78 KB
/
tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE TABLE IF NOT EXISTS db_details (
version BIGINT NOT NULL,
upgraded_on TIMESTAMPTZ NOT NULL);
CREATE UNIQUE INDEX db_details_one_row
ON db_details((version IS NOT NULL));
CREATE FUNCTION db_version_no_delete ()
RETURNS trigger
LANGUAGE plpgsql AS $f$
BEGIN
RAISE EXCEPTION 'You may not delete the db_details!';
END; $f$;
CREATE FUNCTION array_distinct(anyarray) RETURNS anyarray AS $f$
SELECT array_agg(DISTINCT x) FROM unnest($1) t(x);
$f$ LANGUAGE SQL IMMUTABLE;
CREATE TRIGGER db_details_no_delete
BEFORE DELETE ON db_details
FOR EACH ROW EXECUTE PROCEDURE db_version_no_delete();
INSERT INTO db_details (version, upgraded_on) VALUES (18, now());
CREATE TABLE IF NOT EXISTS bgp_updates (
key VARCHAR ( 32 ) NOT NULL,
prefix inet,
origin_as BIGINT,
peer_asn BIGINT,
as_path BIGINT[],
service VARCHAR ( 50 ),
type VARCHAR ( 1 ),
communities json,
timestamp TIMESTAMP NOT NULL,
hijack_key text[],
handled BOOLEAN,
matched_prefix inet,
orig_path json,
PRIMARY KEY(timestamp, key),
UNIQUE(timestamp, key)
);
CREATE INDEX withdrawal_idx
ON bgp_updates(prefix, peer_asn, type, hijack_key);
CREATE INDEX handled_idx
ON bgp_updates(handled);
SELECT create_hypertable('bgp_updates', 'timestamp', if_not_exists => TRUE);
create trigger send_update_event
after insert on bgp_updates
for each row execute procedure rabbitmq.on_row_change('update-insert');
create trigger send_update_event2
after update on bgp_updates
for each row execute procedure rabbitmq.on_row_change('update-update');
CREATE TABLE IF NOT EXISTS hijacks (
key VARCHAR ( 32 ) NOT NULL,
type VARCHAR ( 7 ),
prefix inet,
hijack_as BIGINT,
peers_seen BIGINT[],
peers_withdrawn BIGINT[],
num_peers_seen INTEGER,
asns_inf BIGINT[],
num_asns_inf INTEGER,
time_started TIMESTAMP,
time_last TIMESTAMP,
time_ended TIMESTAMP,
mitigation_started TIMESTAMP,
time_detected TIMESTAMP NOT NULL,
under_mitigation BOOLEAN,
resolved BOOLEAN,
active BOOLEAN,
ignored BOOLEAN,
withdrawn BOOLEAN,
outdated BOOLEAN DEFAULT FALSE,
dormant BOOLEAN DEFAULT FALSE,
configured_prefix inet,
timestamp_of_config TIMESTAMP,
comment text,
seen BOOLEAN DEFAULT FALSE,
community_annotation text DEFAULT 'NA',
PRIMARY KEY(time_detected, key),
UNIQUE(time_detected, key),
CONSTRAINT possible_states CHECK (
(
active=true and under_mitigation=false and resolved=false and ignored=false and withdrawn=false and outdated=false
) or (
active=true and under_mitigation=true and resolved=false and ignored=false and withdrawn=false and outdated=false
) or (
active=false and under_mitigation=false and resolved=true and ignored=false and withdrawn=false and outdated=false
) or (
active=false and under_mitigation=false and resolved=false and ignored=true and withdrawn=false and outdated=false
) or (
active=false and under_mitigation=false and resolved=false and ignored=false and withdrawn=false and outdated=true
) or (
active=false and under_mitigation=false and resolved=true and ignored=false and withdrawn=false and outdated=true
) or (
active=false and under_mitigation=false and resolved=false and ignored=true and withdrawn=false and outdated=true
) or (
active=false and under_mitigation=false and resolved=false and ignored=false and withdrawn=true and outdated=false
) or (
active=false and under_mitigation=false and resolved=false and ignored=false and withdrawn=true and outdated=true
) or (
active=false and under_mitigation=false and resolved=true and ignored=false and withdrawn=true and outdated=false
) or (
active=false and under_mitigation=false and resolved=false and ignored=true and withdrawn=true and outdated=false
) or (
active=false and under_mitigation=false and resolved=true and ignored=false and withdrawn=true and outdated=true
) or (
active=false and under_mitigation=false and resolved=false and ignored=true and withdrawn=true and outdated=true
)
),
CONSTRAINT dormant_active CHECK (
(
active=true and dormant=false
) or (
active=true and dormant=true
) or (
active=false and dormant=false
)
)
);
CREATE INDEX active_idx
ON hijacks(active);
CREATE INDEX hijack_table_idx
ON hijacks(time_last, hijack_as, prefix, type);
SELECT create_hypertable('hijacks', 'time_detected', if_not_exists => TRUE);
create trigger send_hijack_event
after insert or update on hijacks
for each row execute procedure rabbitmq.on_row_change("hijack-update");
CREATE TABLE IF NOT EXISTS configs (
key VARCHAR ( 32 ) NOT NULL,
raw_config text,
comment text,
time_modified TIMESTAMP NOT NULL
);
CREATE TABLE IF NOT EXISTS stats (
monitored_prefixes BIGINT NOT NULL DEFAULT 0,
configured_prefixes BIGINT NOT NULL DEFAULT 0,
monitor_peers BIGINT NOT NULL DEFAULT 0
);
CREATE UNIQUE INDEX stats_one_row
ON stats((monitored_prefixes IS NOT NULL));
CREATE FUNCTION stats_no_delete ()
RETURNS trigger
LANGUAGE plpgsql AS $f$
BEGIN
RAISE EXCEPTION 'You may not delete the stats!';
END; $f$;
CREATE TRIGGER stats_no_delete
BEFORE DELETE ON stats
FOR EACH ROW EXECUTE PROCEDURE stats_no_delete();
INSERT INTO stats (monitored_prefixes, configured_prefixes, monitor_peers) VALUES (0, 0, 0);
CREATE OR REPLACE VIEW view_configs AS SELECT raw_config, comment, time_modified FROM configs;
CREATE OR REPLACE VIEW view_hijacks AS SELECT key, type, prefix, hijack_as, num_peers_seen, num_asns_inf, time_started, time_ended, time_last, mitigation_started, time_detected, timestamp_of_config, under_mitigation, resolved, active, dormant, ignored, configured_prefix, comment, seen, withdrawn, peers_withdrawn, peers_seen, outdated, community_annotation FROM hijacks;
CREATE OR REPLACE VIEW view_bgpupdates AS SELECT prefix, origin_as, peer_asn, as_path, service, type, communities, timestamp, hijack_key, handled, matched_prefix, orig_path FROM bgp_updates;
CREATE OR REPLACE VIEW view_index_all_stats
AS
SELECT stats.monitored_prefixes, stats.configured_prefixes, stats.monitor_peers,
(SELECT count(*) total_hijacks FROM hijacks WHERE key is not NULL),
(SELECT count(*) ignored_hijacks FROM hijacks WHERE ignored = true),
(SELECT count(*) resolved_hijacks FROM hijacks WHERE resolved = true),
(SELECT count(*) withdrawn_hijacks FROM hijacks WHERE withdrawn = true),
(SELECT count(*) mitigation_hijacks FROM hijacks WHERE under_mitigation = true),
(SELECT count(*) ongoing_hijacks FROM hijacks WHERE active = true),
(SELECT count(*) dormant_hijacks FROM hijacks WHERE dormant = true),
(SELECT count(*) acknowledged_hijacks FROM hijacks WHERE seen = true),
(SELECT count(*) outdated_hijacks FROM hijacks WHERE outdated = true),
(SELECT count(*) total_bgp_updates FROM bgp_updates WHERE key is not NULL),
(SELECT count(*) total_unhandled_updates FROM bgp_updates WHERE handled = false)
FROM stats;
CREATE OR REPLACE FUNCTION inet_search (inet)
RETURNS SETOF bgp_updates AS $$
SELECT * FROM bgp_updates WHERE prefix << $1;
$$ LANGUAGE SQL;
CREATE TABLE IF NOT EXISTS process_states (
name VARCHAR (32) UNIQUE,
running BOOLEAN DEFAULT FALSE,
timestamp TIMESTAMP default current_timestamp
);
CREATE TABLE IF NOT EXISTS intended_process_states (
name VARCHAR (32) UNIQUE,
running BOOLEAN DEFAULT FALSE
);
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.timestamp = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_process_timestamp
BEFORE UPDATE ON process_states
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
CREATE OR REPLACE VIEW view_processes AS SELECT * FROM process_states;
CREATE OR REPLACE VIEW view_intended_process_states AS SELECT * FROM intended_process_states;
CREATE OR REPLACE VIEW view_db_details AS SELECT version, upgraded_on FROM db_details;
CREATE FUNCTION search_bgpupdates_as_path(as_paths BIGINT[])
RETURNS SETOF view_bgpupdates AS $$
SELECT *
FROM view_bgpupdates
WHERE
as_paths <@ view_bgpupdates.as_path
$$ LANGUAGE sql STABLE;
CREATE FUNCTION search_bgpupdates_by_hijack_key(key text)
RETURNS SETOF view_bgpupdates AS $$
SELECT *
FROM view_bgpupdates
WHERE
key = ANY(view_bgpupdates.hijack_key)
$$ LANGUAGE sql STABLE;
CREATE FUNCTION search_bgpupdates_by_as_path_and_hijack_key(key text, as_paths BIGINT[])
RETURNS SETOF view_bgpupdates AS $$
SELECT *
FROM view_bgpupdates
WHERE
key = ANY(view_bgpupdates.hijack_key) and as_paths <@ view_bgpupdates.as_path
$$ LANGUAGE sql STABLE;