Detected Alerts Not Purging

[shanesmart]

Good morning -

After nightly build, the detected alerts will not purge. Version number. ntopng Community Edition v.3.1.171012. Thank you for your time.

Log:
12/Oct/2017 07:46:09 [AlertsManager.cpp:1150] ERROR: SQL Error: attempt to write a readonly database DELETE FROM flows_alerts WHERE 1=1
12/Oct/2017 06:02:34 [AlertsManager.cpp:814] ERROR: SQL Error: step [INSERT INTO flows_alerts (alert_tstamp, alert_type, alert_severity, alert_json, vlan_id, proto, l7_proto, first_switched, last_switched, cli_country, srv_country, cli_os, srv_os, cli_asn, srv_asn, cli_addr, srv_addr, cli_port, srv_port, cli2srv_bytes, srv2cli_bytes, cli2srv_packets, srv2cli_packets, cli2srv_tcpflags, srv2cli_tcpflags, cli_blacklisted, srv_blacklisted, cli_localhost, srv_localhost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ][SQL logic error or missing database]
12/Oct/2017 05:48:34 [AlertsManager.cpp:814] ERROR: SQL Error: step [INSERT INTO flows_alerts (alert_tstamp, alert_type, alert_severity, alert_json, vlan_id, proto, l7_proto, first_switched, last_switched, cli_country, srv_country, cli_os, srv_os, cli_asn, srv_asn, cli_addr, srv_addr, cli_port, srv_port, cli2srv_bytes, srv2cli_bytes, cli2srv_packets, srv2cli_packets, cli2srv_tcpflags, srv2cli_tcpflags, cli_blacklisted, srv_blacklisted, cli_localhost, srv_localhost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ][SQL logic error or missing database]
12/Oct/2017 05:48:34 [AlertsManager.cpp:814] ERROR: SQL Error: step [INSERT INTO flows_alerts (alert_tstamp, alert_type, alert_severity, alert_json, vlan_id, proto, l7_proto, first_switched, last_switched, cli_country, srv_country, cli_os, srv_os, cli_asn, srv_asn, cli_addr, srv_addr, cli_port, srv_port, cli2srv_bytes, srv2cli_bytes, cli2srv_packets, srv2cli_packets, cli2srv_tcpflags, srv2cli_tcpflags, cli_blacklisted, srv_blacklisted, cli_localhost, srv_localhost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ][SQL logic error or missing database]
12/Oct/2017 00:10:18 [AlertsManager.cpp:814] ERROR: SQL Error: step [INSERT INTO flows_alerts (alert_tstamp, alert_type, alert_severity, alert_json, vlan_id, proto, l7_proto, first_switched, last_switched, cli_country, srv_country, cli_os, srv_os, cli_asn, srv_asn, cli_addr, srv_addr, cli_port, srv_port, cli2srv_bytes, srv2cli_bytes, cli2srv_packets, srv2cli_packets, cli2srv_tcpflags, srv2cli_tcpflags, cli_blacklisted, srv_blacklisted, cli_localhost, srv_localhost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ][SQL logic error or missing database]
12/Oct/2017 00:08:38 [AlertsManager.cpp:814] ERROR: SQL Error: step [INSERT INTO flows_alerts (alert_tstamp, alert_type, alert_severity, alert_json, vlan_id, proto, l7_proto, first_switched, last_switched, cli_country, srv_country, cli_os, srv_os, cli_asn, srv_asn, cli_addr, srv_addr, cli_port, srv_port, cli2srv_bytes, srv2cli_bytes, cli2srv_packets, srv2cli_packets, cli2srv_tcpflags, srv2cli_tcpflags, cli_blacklisted, srv_blacklisted, cli_localhost, srv_localhost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ][SQL logic error or missing database]
11/Oct/2017 23:49:08 [AlertsManager.cpp:814] ERROR: SQL Error: step [INSERT INTO flows_alerts (alert_tstamp, alert_type, alert_severity, alert_json, vlan_id, proto, l7_proto, first_switched, last_switched, cli_country, srv_country, cli_os, srv_os, cli_asn, srv_asn, cli_addr, srv_addr, cli_port, srv_port, cli2srv_bytes, srv2cli_bytes, cli2srv_packets, srv2cli_packets, cli2srv_tcpflags, srv2cli_tcpflags, cli_blacklisted, srv_blacklisted, cli_localhost, srv_localhost) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ][SQL logic error or missing database]
11/Oct/2017 21:56:41 [NetworkInterface.cpp:2271] Started packet polling on interface lo [id: 1]...

[simonemainardi]

For security reasons, reads/writes from/to the alerts database are now performed with a nonprivileged user (unless you explicitly --dont-change-user)

As the alerts database, before security fix #1486, was created and accessed as root, it is now inaccessible by any nonprivileged user.

So please, either run ntopng with --dont-change-user or do a chmod/chown to make sure the alertsdb is owned by nobody. New privileges should be:

simone@devel:~/ntopng$ sudo ls -lha /var/tmp/ntopng/0/alerts
total 68K
drwx------  2 nobody nogroup 4.0K Oct 13 17:47 .
drwx------ 10 nobody nogroup 4.0K Oct 11 19:05 ..
-rw-r--r--  1 nobody nogroup  54K Oct 13 17:47 alerts_v5.db

Note that there are many dbs as the number of interfaces so you may have to chmod/chown multiple times.