📙 You will learn

By default, ThreadFix installs with a database deployed inside a container with the appropriate values already set. The following instructions cover installation of ThreadFix with an external database or other advanced database configurations.

Prerequisites

Audience: IT Professional
Difficulty: Intermediate
Time needed: Approximately 25 minutes
Tools required: MySQL cli or Kubernetes cluster with mysql container

Requirements:

• MySQL server running MySQL 5.7

• FQDN or IP for the MySQL server

• Account with Administrative access to MySQL

• Account with permissions to modify MySQL configuration

Helm installations are configured with yaml files that override default properties. The following examples would need to be invoked with the command -f .yaml appended to the helm installation command. For example, if the yaml file is namedappsec-db.yaml, invoke with ‘helm install tf denimgroup/threadfix -f appsec-db.yaml.

Configuring External AppSec Database

The following properties must be set for the database to function correctly:

The database properties must be set to function correctly, these configurations need to be carefully tuned if different resources are allocated to the MySQL server.

The following examples are configurations for MySQL on an EC2 instance type [t3a.2xlarge] with [8] vCPU and [32 GB] RAM. This specific setup has been tested to handle AppSec ingestion pipeline scaling up to 6 nodes each for the following services: AppSec Importer, VIP and Data Service.

• Innodb Buffer Pool Configurations

Note: ThreadFix recommends for the buffer pools size to be 50-70% of the overall RAM.

innodb_buffer_pool_size=16GB
innodb_buffer_pool_instances=16

• IO Threads

innodb_read_io_threads=10
innodb_write_io_threads=14

Additional References

#https://dev.mysql.com/doc/refman/5.6/en/innodb-performance-multiple_io_threads.html
#https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_read_io_threads
#https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_write_io_threads

• Other configurations

innodb_log_file_size=1GB
innodb_log_buffer_size = 256M
innodb_io_capacity=1000
join_buffer_size=256K

• These configs were previously recommended by the ThreadFix setup guides

max_allowed_packet=256MB

# Disable these configs for now, if they have been customized. 
#tmp_table_size=6GB
#max_heap_table_size=6GB

Consult vendors guides for how to properly set these values:

• Amazon RDS MySQL

• Azure Database for MySQL

1. Log into MySQL using the MySQL CLI

   1. If a local MySQL CLI is installed, it may be run directly (replace values in angle brackets, <> with their appropriate value).

      Code Block
      mysql -u<user> -h<hostname> -p

   2. If a local MySQL CLI is not installed, the ThreadFix kubernetes cluster may be leveraged to run one

      kubectl run -it --restart=Never --rm --image=mysql:5.7 setup-db -- /bin/bash
      When a bash prompt appear, run the MySQL CLI (replace values in angle brackets, <> with their appropriate value)
      mysql -u<user> -h<hostname> -p
      

2. Validate that the lower_case_table_names parameter is correctly set.

   Code Block
   language 1
   SHOW VARIABLES LIKE 'lower_case_%';

   The following output should appear:

   Code Block
   +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_file_system | OFF | | lower_case_table_names | 1 | +------------------------+-------+

   
   Warning: If the above parameter is not set, the ThreadFix installation will fail. Attempting to install ThreadFix without this variable will cause issues with the installation that will be difficult to reverse.
   

3. Create the ThreadFix database.

   Code Block
   CREATE DATABASE IF NOT EXISTS `threadfix` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

4. Create the ThreadFix user (replace values in angle brackets, <> with appropriate values).

   Code Block
   CREATE USER 'ThreadFix'@'%' IDENTIFIED BY '<password>';

5. Grant access to the ThreadFix user.

   Code Block
   GRANT ALL ON threadfix.* TO 'ThreadFix'@'%';

6. Exit the CLI with CTRL-C.
   

7. Validate the user login functions (replace values in angle brackets, <> with appropriate values).

   Code Block
   mysql -u ThreadFix -h <hostname> -p

8. Validate access to the database.

   Code Block
   SHOW GRANTS;

   The output should show the following:

   Code Block
   +--------------------------------------------------------+ | Grants for ThreadFix@% | +--------------------------------------------------------+ | GRANT ALL PRIVILEGES ON threadfix.* TO 'ThreadFix'@'%' | +--------------------------------------------------------+

Create External Database Helm Values

To review the pre-requisites for hosting an SQL Server ThreadFix database externally, please refer to the MySQL database setup guides(recommended) or the Windows SQL Server Configuration guide (supported).

1. Create two files, username.txt and password.txt, containing the username and password credentials respectively for the external database.

   • Warning: The external database must already contain the database threadfix, and the username and password configured above must be granted all permissions on that database. For external MySQL instances, the instance must be run with the configuration lower_case_table_names=1.

   • Warning: Many text editing tools insert a trailing newline character, \n, to text files by default. If the text editor being used has this property, consult the documentation on how to disable this functionality.
     

2. Validate no newline characters have been added to the username and password files. The following should output 0 for both files.

   Code Block
   wc -l username.txt password.txt

3. Create a kubernetes secret for the external database.

   Code Block
   kubectl create secret generic db-user-pass --from-file=username=./username.txt --from-file=password=./password.txt

4. Remove the previously created files.

   Code Block
   rm username.txt rm password.txt

5. Set the FQDN or IP of the database (replace <hostname> with the FQDN or IP of the database).

   Code Block
   DB_HOSTNAME=<hostname>

6. Set the name of the database to be used (replace <db-name> with the appropriate value default threadfix).

   Code Block
   DB_NAME=<db-name>

7. Create myValues folder (if it does not exist).

   Code Block
   mkdir -p myValues

8. Create appsec-db.yaml.

   Code Block

   echo "appsec: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME auth: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME ## 3.1 only appsec: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME auth: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME appsecdata: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME appsecimporter: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME appsecvip: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME queue: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME jobcoordinator: db: existingSecret: db-user-pass hostnameOverride: $DB_HOSTNAME database: $DB_NAME" > myValues/appsec-db.yaml

9. Finish any other tasks from the Installation Checklist - 3.1 WIP , then Install with Helm - 3.1 For Review WIP