ntopng stores flows data in various databases including MySQL, Elastic and ClickHouse that is the database storage that we have selected as it outpaces the others in terms of speed and reduced disk space. ClickHouse is a columnar database and while it is very fast during data access, it is optimised for batch data insertion. This means that ntopng imports flow data as follows:
- High cardinality data such as flows are saved in a temporary file and imported every minute using clickhouse-client. The default TCP communication port is 9000.
- Low-cardinality data such as alerts are stored with SQL queries. In this case we perform queries using the MySQL driver part or ClickHouse as it does not have a native SQL query engine. The default TCP communication port used by the MySQL driver is 9004.
Out of the box ClickHouse uses plain text communications but you can enable TLS in a few steps as described in this document and also secure the MySQL configuration. All settings are specified in /etc/clickhouse-server/config.xml as follows:
- <tcp_port> is used for insecure clickhouse-client connection.
- <tcp_port_secure> is used for secure (TLS) clickhouse-client connection.
- <mysql_port> defines the port where the MySQL driver is listening to. ClickHouse does not offer TLS support for the MySQL driver, hence if you want to secure communications you need to put a TLS proxy in front of that port.
<!-- Port for interaction by native protocol with:
- clickhouse-client and other native ClickHouse tools (clickhouse-benchmark, clickhouse-copier);
- clickhouse-server with other clickhouse-servers for distributed query processing;
- ClickHouse drivers and applications supporting native protocol
(this protocol is also informally called as "the TCP protocol");
See also 'tcp_port_secure' for secure connections.
-->
<tcp_port>9000</tcp_port>
<!-- Compatibility with MySQL protocol.
ClickHouse will pretend to be MySQL for applications connecting to this port.
-->
<mysql_port>9004</mysql_port>
<!-- Native interface with TLS.
You have to configure certificate to enable this interface.
See the openSSL section below.
-->
<tcp_port_secure>9440</tcp_port_secure>
<openSSL>
<server> <!-- Used for https server AND secure tcp port -->
<!-- openssl req -subj "/CN=localhost" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt -->
<certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
<privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
<!-- dhparams are optional. You can delete the <dhParamsFile> element.
To generate dhparams, use the following command:
openssl dhparam -out /etc/clickhouse-server/dhparam.pem 4096
Only file format with BEGIN DH PARAMETERS is supported.
-->
<!-- <dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile>-->
<verificationMode>none</verificationMode>
<loadDefaultCAFile>true</loadDefaultCAFile>
<cacheSessions>true</cacheSessions>
<disableProtocols>sslv2,sslv3</disableProtocols>
<preferServerCiphers>true</preferServerCiphers>
<invalidCertificateHandler>
<!-- The server, in contrast to the client, cannot ask about the certificate interactively.
The only reasonable option is to reject.
-->
<name>RejectCertificateHandler</name>
</invalidCertificateHandler>
</server>
..
</openSSL>
The -F flag has been enhanced to specify the use of secure ports by adding a ‘s’ after the port. Example:
-
-F “clickhouse;127.0.0.1@9000,9004;ntopng;default;” [Insecure]
-
-F “clickhouse;127.0.0.1@9440s,9004;ntopng;default;” [ClickHouse TLS, plain MySQL]
- -F “clickhouse;127.0.0.1@9440s,9014s;ntopng;default;” [ClickHouse TLS, MySQL TLS (behing a TLS proxy active on port 9014]
Note that the ‘s’ option is also available when using MySQL (without ClickHouse) with ntopng (i.e. -F “mysql;127.0.0.1;ntopng;root;“)
It’s now time to connect to your ClickHouse (or MySQL) database over TLS for secure database communications.
Enjoy !