search

voipmonitor / sniffer

VoIPmonitor sniffer sources
226 stars 105 forks source link

Using an external database #95

Closed dmgeurts closed 2 years ago

dmgeurts commented 2 years ago

I'm looking at running voipmonitor with an external database. How do I get past the following errors? It seems voipmonitor expects root-level access to the MySQL database which isn't compliant with our security standards.

voipmonitor[1028026]: start voipmonitor - version 29.1.4
voipmonitor[1028026]: query error in [SET GLOBAL innodb_file_per_table=1;]:  1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
voipmonitor[1028026]: connect - db version 10.5.12 (100512) mariadb / maximum partitions: 8192 / connect via 10.*.*.*
voipmonitor[1028026]: creating and upgrading MySQL schema...
voipmonitor[1028026]: query error in [SET GLOBAL innodb_compression_algorithm='lz4']:  1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
voipmonitor[1028026]: query error in [SET GLOBAL innodb_compression_algorithm='zlib']:  1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
voipmonitor[1028026]: done
voipmonitor[1028026]: query error in [SET GLOBAL innodb_compression_algorithm='zlib']:  1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation - exit!

The MySQL server already has these values set, so voipmonitor doesn't need to:

MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 104857600 |
+--------------------+-----------+
1 row in set (0.002 sec)

MariaDB [(none)]> show variables like 'innodb_compression_algorithm';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_compression_algorithm | zlib  |
+------------------------------+-------+
1 row in set (0.001 sec)

The simple (insecure) answer would be to grant global super privileges to the MySQL user, but this is insecure as it would give access to more than what is required for normal operation, and the values that voipmonitor tries to set are already present. Why does voipmonitor not check if the values are already set, I fail to see the point?

dmgeurts commented 2 years ago

Setting the following in /etc/voipmonitor.conf appears to allow the service to not exit when started:

mysqlcompress_type = compression="zlib"

However, I am now left troubleshooting other issues like, and many others. The external database resides on a Galera cluster:

Feb  2 22:12:40 vmon02 voipmonitor[1325292]: query error in [CREATE TABLE IF NOT EXISTS `cdr_ua` (#011#011#011`id` int unsigned NOT NULL AUTO_INCREMENT,#011#011#011`ua` varchar(512
) DEFAULT NULL,#011#011PRIMARY KEY (`id`),#011#011UNIQUE KEY `ua` (`ua`)#011) ENGINE=InnoDB DEFAULT CHARSET=latin1...]:  1911 - Unknown option 'compression'                        Feb  2 22:12:40 vmon02 voipmonitor[1325292]: query error in [CREATE TABLE IF NOT EXISTS `cdr` (#011#011#011`ID` bigint unsigned NOT NULL AUTO_INCREMENT,#011#011#011`calldate` datet
ime NOT NULL,#011#011#011`callend` datetime NOT NULL,#011#011#011`duration` mediumint unsigned DEFAULT NULL,#011#011#011`conne...]:  1911 - Unknown option 'compression'
Feb  2 22:12:40 vmon02 voipmonitor[1325292]: query error in [show columns from cdr like 'sipcallerip']:  1146 - Table 'ipn_voipmonitor.cdr' doesn't exist                           Feb  2 22:12:40 vmon02 voipmonitor[1325292]: fetch row error in function mysql_use_result:  1146 - Table 'ipn_voipmonitor.cdr' doesn't exist

And then, one reads the comments in voipmonitor.conf properly and I find that the following line is the thing to use with mariadb 10.5 and voipmonitor stops throwing errors:

mysqlcompress_type = PAGE_COMPRESSED=1