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.
- Ensure that you when install and configure your PostgreSQL server, you also install the following package:
postgresql<<version>>-contrib-<postgresql_version>-2PGDG.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-12
- Ensure that you have stopped all your schedules and that you have no playbooks in the running state.
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
- Create the
db_external_config.yml
file at the following location/opt/cyops/configs/database/
Use the following command to create thedb_external_config.yml
file:# cp /opt/cyops/configs/database/db_config.yml /opt/cyops/configs/database/db_external_config.yml
- Update the newly created
db_external_config.yml
file for PostgreSQL as follows:
In thepostgres
section:- 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. - 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 thepg_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.
- 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 thecsadm db --encrypt
command as a root user. For more information oncsadm
, see the FortiSOAR Admin CLI chapter.
- Set the
- On the externalized PostgreSQL database run the following commands:
- To ensure that the PostgreSQL server allows connections, open the firewall port:
# firewall-cmd --add-service=postgresql --permanent
# firewall-cmd --reload
- 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 thepg_hba.conf
file:host all all xxx.xxx.xxx.xxx/xx trust
- 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
- Restart PostgreSQL using the following command:
# systemctl restart postgresql-12
- Create a
cyberpgsql
user using the following commands:# psql -U postgres -c "CREATE USER cyberpgsql WITH SUPERUSER PASSWORD '<password>'";
- To ensure that the PostgreSQL server allows connections, open the firewall port:
- SSH to your FortiSOAR VM and login as a root user.
- Check the connectivity between the FortiSOAR local instance and remote PostgreSQL database using the
csadm db --check-connection
command. - 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), thencsadm
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 typey
. - After you have completed externalizing your PostgreSQL database, you should restart your schedules.
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'. |
You must keep the |
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:
- Set up your AWS RDS with its default port, i.e., 5432 and postgresql-12.1.
- Setup the master password for the PostgreSQL user.
- 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
- Once the connectivity is verified, create the
db_external_config.yml
file at the following location:/opt/cyops/configs/database/
- Navigate to
/opt/cyops/configs/database/
and then run the following command:cp db_config.yml db_external_config.yml
- Edit the
db_external_config.yml
file and update thepostgres
section as mentioned in the Externalizing FortiSOAR databases section.
You need to encrypt the password using the following command:csadm db --encrypt
- Connect to the RDS system to create a user:
psql -h <pg_hostname> -U <pg_username> -p <port_no> -d postgres
- Create the
cyberpsql
user in the RDS system using the following command:CREATE USER cyberpgsql WITH PASSWORD ‘<your password>’ CREATEROLE CREATEDB
- Grant appropriate access to the
cyberpsql
user using the following command:GRANT cyberpgsql TO postgres;
- Exit the psql shell.
- To externalize the PostgreSQL database, type the following command:
# csadm db --externalize
This command takes some time for completion.
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
- Update the PostgreSQL database IP to the new IP in the
db_config.yml
and thedb_external_config.yml
files. These files are present in the/opt/cyops/configs/database
folder. - Update the PostgreSQL database IP to the new IP in the
appProdProjectContainer.php
file located at/opt/cyops-api/app/cache/prod/appProdProjectContainer.php
. - Run the following command:
$ sudo -u nginx php /opt/cyops-api/bin/console cache:clear --env=prod