Fortinet black logo

Administration Guide

Externalization of your FortiSOAR PostgreSQL database

Copy Link
Copy Doc ID b60b554b-4ea9-11ed-9d74-fa163e15d75b: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 14.x.
    • Ensure that you when install and configure your PostgreSQL server, you also install the following package:
      postgresql<<version>>-contrib-<postgresql_version>-1PGDG.rhel7.x86_64
      NOTE: The rpm versions of your 'postgresql-server' and 'postgresql-contrib' must be the same.
      Example of rpms:
      postgresql-server: postgresql14-server-14.4-1PGDG.rhel7.x86_64
      postgresql-contrib: postgresql14-contrib-14.4-1PGDG.rhel7.x86_64
  • 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-14
  • 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.
      Important: In the case of the Aurora database, in the pg_host parameter, specify the global database cluster identifier name. For more information on setting up a High Availability FortiSOAR cluster in AWS Cloud with Aurora as the external database, see the High Availability chapter.
    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 entries to the file /var/lib/pgsql/14/data/pg_hba.conf file:
      local all all trust
      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:
      local all all trust
      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/14/data/postgresql.conf file:
      listen_addresses = '*'
      port = 5432
    4. Restart PostgreSQL using the following command:
      systemctl start postgresql-<postgresql version>
    5. Create a cyberpgsql user using the following command:
      # psql -U postgres -c "CREATE USER cyberpgsql WITH SUPERUSER PASSWORD '<password>'";
    6. Enable the hstore and uuid-osop extensions (this is a recommended step) using the following commands:
      #psql -U cyberpgsql -d postgres -c "CREATE EXTENSION IF NOT EXISTS hstore;"
      #psql -U cyberpgsql -d postgres -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'
  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.
Note

You can choose to externalize both the main PostgreSQL database and your archival database to the same external database or a different external databases. However, if you have externalized your main PostgreSQL database, then you must externalize your archival database, i.e., the external database for data archival cannot be set to 'localhost'.
If you are upgrading your FortiSOAR instance from a release prior to 7.2.0 to a 7.2.0 or later release , then ensure that your externalized PostgreSQL database does not contain a database with the name 'data archival', else there might be conflicts with the data archival feature.
For more information, see the "Data Archival" topic in the System Configuration chapter.

Tooltip

You must keep the max_connections value for your external PostgreSQL to 200. By default, the max_connections value is 100. You can set the max_connections value in the postgresql.conf file, which is generally located at /var/lib/pgsql/14/data/postgresql.conf.

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-<postgresql_version>, for example, postgresql-14.4
  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.

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 14.x.
    • Ensure that you when install and configure your PostgreSQL server, you also install the following package:
      postgresql<<version>>-contrib-<postgresql_version>-1PGDG.rhel7.x86_64
      NOTE: The rpm versions of your 'postgresql-server' and 'postgresql-contrib' must be the same.
      Example of rpms:
      postgresql-server: postgresql14-server-14.4-1PGDG.rhel7.x86_64
      postgresql-contrib: postgresql14-contrib-14.4-1PGDG.rhel7.x86_64
  • 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-14
  • 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.
      Important: In the case of the Aurora database, in the pg_host parameter, specify the global database cluster identifier name. For more information on setting up a High Availability FortiSOAR cluster in AWS Cloud with Aurora as the external database, see the High Availability chapter.
    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 entries to the file /var/lib/pgsql/14/data/pg_hba.conf file:
      local all all trust
      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:
      local all all trust
      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/14/data/postgresql.conf file:
      listen_addresses = '*'
      port = 5432
    4. Restart PostgreSQL using the following command:
      systemctl start postgresql-<postgresql version>
    5. Create a cyberpgsql user using the following command:
      # psql -U postgres -c "CREATE USER cyberpgsql WITH SUPERUSER PASSWORD '<password>'";
    6. Enable the hstore and uuid-osop extensions (this is a recommended step) using the following commands:
      #psql -U cyberpgsql -d postgres -c "CREATE EXTENSION IF NOT EXISTS hstore;"
      #psql -U cyberpgsql -d postgres -c 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp";'
  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.
Note

You can choose to externalize both the main PostgreSQL database and your archival database to the same external database or a different external databases. However, if you have externalized your main PostgreSQL database, then you must externalize your archival database, i.e., the external database for data archival cannot be set to 'localhost'.
If you are upgrading your FortiSOAR instance from a release prior to 7.2.0 to a 7.2.0 or later release , then ensure that your externalized PostgreSQL database does not contain a database with the name 'data archival', else there might be conflicts with the data archival feature.
For more information, see the "Data Archival" topic in the System Configuration chapter.

Tooltip

You must keep the max_connections value for your external PostgreSQL to 200. By default, the max_connections value is 100. You can set the max_connections value in the postgresql.conf file, which is generally located at /var/lib/pgsql/14/data/postgresql.conf.

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-<postgresql_version>, for example, postgresql-14.4
  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.