Fortinet black logo

Administration Guide

Externalization of your FortiSOAR PostgreSQL database

Copy Link
Copy Doc ID 5c3f3ace-db1f-11eb-97f7-00505692583a:970205
Download PDF

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.

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, and then the csadm db --externalize command could fail with a "Failed to drop database <name of database>" error. To resolve this issue and release all stale connections, restart the postgres service using the following command:
    systemctl start postgresql-<postgresql version>
    For example, systemctl start postgresql-12
  • 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/
    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:
    1. Set the pg_external parameter to "true".
      This parameter determines whether or not the postgres database needs to be externalized. If it is set to "true", then the postgres database is externalized, and if set to "false" (default), then the postgres database is not externalized.
    2. Update the value of the postgres host (pg_host) and postgres port (pg_port) (if needed) parameters.
    3. 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.

Setting up an externalized database on the cloud

If you want to set up your externalized database on the cloud, for example AWS, do the following:

  1. Set up your AWS RDS with its default port, i.e., 5432 and postgresql-12.1.
  2. Setup the master password for the PostgreSQL user.
  3. Ensure that your FortiSOAR system is able to access the RDS system using RDS endpoint, for which you might have to update security groups in AWS RDS. Check the connectivity between FortiSOAR and RDS systems using the telnet command and using the following psql command:
    psql -h <pg_hostname> -U <pg_username> -p <port_no> -l postgres
  4. Once the connectivity is verified, create the db_external_config.yml file at the following location:
    /opt/cyops/configs/database/
  5. Navigate to /opt/cyops/configs/database/ and then run the following command:
    cp db_config.yml db_external_config.yml
  6. Edit the db_external_config.yml file and update the postgres section as mentioned in the Externalizing FortiSOAR databases section.
    You need to encrypt the password using the following command:
    csadm db --encrypt
  7. Connect to the RDS system to create a user:
    psql -h <pg_hostname> -U <pg_username> -p <port_no> -d postgres
  8. Create the cyberpsql user in the RDS system using the following command:
    CREATE USER cyberpgsql WITH PASSWORD ‘<your password>’ CREATEROLE CREATEDB
  9. Grant appropriate access to the cyberpsql user using the following command:
    GRANT cyberpgsql TO postgres;
  10. Exit the psql shell.
  11. To externalize the PostgreSQL database, type the following command:
    # csadm db --externalize
    This command takes some time for completion.
Note

Performance differences might be seen in cases where there is high network latency between the FortiSOAR and RDS systems.

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

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.

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, and then the csadm db --externalize command could fail with a "Failed to drop database <name of database>" error. To resolve this issue and release all stale connections, restart the postgres service using the following command:
    systemctl start postgresql-<postgresql version>
    For example, systemctl start postgresql-12
  • 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/
    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:
    1. Set the pg_external parameter to "true".
      This parameter determines whether or not the postgres database needs to be externalized. If it is set to "true", then the postgres database is externalized, and if set to "false" (default), then the postgres database is not externalized.
    2. Update the value of the postgres host (pg_host) and postgres port (pg_port) (if needed) parameters.
    3. 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.

Setting up an externalized database on the cloud

If you want to set up your externalized database on the cloud, for example AWS, do the following:

  1. Set up your AWS RDS with its default port, i.e., 5432 and postgresql-12.1.
  2. Setup the master password for the PostgreSQL user.
  3. Ensure that your FortiSOAR system is able to access the RDS system using RDS endpoint, for which you might have to update security groups in AWS RDS. Check the connectivity between FortiSOAR and RDS systems using the telnet command and using the following psql command:
    psql -h <pg_hostname> -U <pg_username> -p <port_no> -l postgres
  4. Once the connectivity is verified, create the db_external_config.yml file at the following location:
    /opt/cyops/configs/database/
  5. Navigate to /opt/cyops/configs/database/ and then run the following command:
    cp db_config.yml db_external_config.yml
  6. Edit the db_external_config.yml file and update the postgres section as mentioned in the Externalizing FortiSOAR databases section.
    You need to encrypt the password using the following command:
    csadm db --encrypt
  7. Connect to the RDS system to create a user:
    psql -h <pg_hostname> -U <pg_username> -p <port_no> -d postgres
  8. Create the cyberpsql user in the RDS system using the following command:
    CREATE USER cyberpgsql WITH PASSWORD ‘<your password>’ CREATEROLE CREATEDB
  9. Grant appropriate access to the cyberpsql user using the following command:
    GRANT cyberpgsql TO postgres;
  10. Exit the psql shell.
  11. To externalize the PostgreSQL database, type the following command:
    # csadm db --externalize
    This command takes some time for completion.
Note

Performance differences might be seen in cases where there is high network latency between the FortiSOAR and RDS systems.

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