Externalization of your FortiSOAR™ PostgreSQL database

This chapter explains the steps required to externalize your FortiSOAR™ PostgreSQL database. For information about ElasticSearch configuration, including ElasticSearch externalization, see the ElasticSearch Configuration chapter.

Externalization is migration of data from your local database instance to a remote database instance that has same version of PostgreSQL, outside of the FortiSOAR™ virtual appliance.

To externalize your FortiSOAR™ PostgreSQL database you must have root access on your FortiSOAR™ system and you must use the FortiSOAR™ Admin CLI (csadm). For more information on csadm, see the FortiSOAR™ Admin CLI chapter in the "Administration Guide."

Prerequisites

  • Prepare your Remote instance:
    • Remote instance must allow inbound communication from your FortiSOAR™ local Virtual Machine.
    • Remote instance must have PostgreSQL version 12.
  • Prepare your Local FortiSOAR™ instance:
    • Ensure that port 5432 is opened for PostgreSQL to allow inbound and outbound communication with the remote instance.
  • Ensure that the connectivity between your FortiSOAR™ local instance and remote PostgreSQL instance is established.
  • If the FortiSOAR™ instance was connected previously to the same instance of the database that is being externalized, it could lead to a stale connection being present to the FortiSOAR™ database on the external PostgreSQL server. To resolve this issue and release all stale connections, restart the postgres service using the following command:
    systemctl start postgresql-<postgresql version>
  • Ensure that you have stopped all your schedules and that you have no playbooks in the running state.

Note

Ensure that you have enough disk space available to perform DB externalization tasks. It is recommended that you have available disk space of around 3X of the data size, for example, if your data size is 2GB, then you should have around 6GB of available disk space, to ensure that the processes do not stop or fail.

Externalizing FortiSOAR™ databases

  1. Create the db_external_config.yml file at the following location /opt/cyops/configs/database/db_external_config.yml
    Use the following command to create the db_external_config.yml file:
    # cp /opt/cyops/configs/database/db_config.yml /opt/cyops/configs/database/db_external_config.yml
  2. Update the newly created db_external_config.yml file for PostgreSQL as follows:
    In the postgres section, update the host (pg_host) and port (pg_port) (if needed). You must also add the encrypted password that you have set on your remote PostgreSQL server in the pg_password parameter.
    You can encrypt your PostgreSQL passwords by running the csadm db --encrypt command as a root user. For more information on csadm, see the FortiSOAR™ Admin CLI chapter.
  3. On the externalized PostgreSQL database run the following commands:
    1. To ensure that the PostgreSQL server allows connections, open the firewall port:
      # firewall-cmd --add-service=postgresql --permanent
      # firewall-cmd --reload
    2. To ensure that the pg_hba.conf file, trusts the FortiSOAR™ server for incoming connections:
      Add the following entry to the file /var/lib/pgsql/12/data/pg_hba.conf file:
      host all all ip/subnetmask trust
      For example, if the ip/subnetmask of your externalized PostgreSQL database is xxx.xxx.xxx.xxx/xx then add the following to the pg_hba.conf file:
      host all all xxx.xxx.xxx.xxx/xx trust
    3. To ensure that the postgresql.conf file, trusts the FortiSOAR™ server for incoming connections:
      Make the following changes to the /var/lib/pgsql/12/data/postgresql.conf file:
      listen_addresses = '*'
      port = 5432
    4. Restart PostgreSQL using the following command:
      # systemctl restart postgresql-12
    5. Create a cyberpgsql user using the following commands:
      # psql -U postgres -c "CREATE USER cyberpgsql WITH SUPERUSER PASSWORD '<password>'"
  4. SSH to your FortiSOAR™ VM and login as a root user.
  5. Check the connectivity between the FortiSOAR™ local instance and remote PostgreSQL database using the csadm db --check-connection command.
  6. To externalize the PostgreSQL database, type the following command:
    # csadm db --externalize
    Once you run the above command, you will be asked to provide the path in which you want to save your database backup file.
    Note: If you run the # csadm db --externalize option more than once (i.e., you are running the option again after the first time), then csadm will display a message such as:
    The databases already exist in postgresql, do you want to delete these databases (y/n): If you want to externalize your PostgreSQL database again you must type y.
  7. After you have completed externalizing your PostgreSQL database, you should restart your schedules.

Troubleshooting DB Externalization issues

Unable to log onto FortiSOAR™ if the IP of the externalized PostgreSQL database changes

If the IP of the externalized PostgreSQL database has changed, in cases such as crashing of the Postgres server, then you might not be able to log onto FortiSOAR™.

Resolution

  1. Update the PostgreSQL database IP to the new IP in the db_config.yml and the db_external_config.yml files. These files are present in the /opt/cyops/configs/database folder.
  2. Update the PostgreSQL database IP to the new IP in the appProdProjectContainer.php file located at /opt/cyops-api/app/cache/prod/appProdProjectContainer.php.
  3. Run the following command:
    # sudo -u nginx php /opt/cyops-api/app/console cache:clear --env=prod