Configuring PostgreSQL

This section specifies how to configure Federated Search for data stored in PostgreSQL database.

Step 1 - Install PostgreSQL Driver

Login to every ClickHouse Data Node and install the ODBC driver by taking the following steps.

1. Login to a node as root.

2. Install the driver by running the following command.

   sudo yum install -y postgresql-odbc

Step 2 - Configure Provider

Follow these steps to configure PostgreSQL ODBC Provider.

1. Go to Resources > External Datasets.

2. Click at the top of the left Resources pane, and select New Group.

3. Set the attributes as follows:

   1. From the Provider Type drop-down, select ODBC.

   2. In the Provider Name field, enter a provider name for identification purposes.

   3. From the Driver Type drop-down, select PostgreSQL.

   4. In the Server field, enter the PostgreSQL database FQDN or IP address.

   5. In the Port field, enter the port number where the PostgreSQL database is listening (default 5432).

   6. In the Database field, enter the PostgreSQL Database name to be searched.

   7. In the User Name field, enter the PostgreSQL username to be used.

   8. In the Password and Confirm Password fields, enter/paste the password associated with the above user.

   9. (Optional) In the Description field, enter any information pertaining to this external dataset that you wish to disclose.

4. Click Save.

5. At this point, the Provider's Provision Status will be Not Provisioned.

6. Select the Provider and click the Apply () icon. The Provider definition will be deployed to all ClickHouse Data Nodes.

7. If deployment succeeds, then Provision Status will change to Success. If the deployment failed, then your Provision Status will change to Failure. You will need to fix the issue and click the Apply () icon again.

8. The Provider will appear under Resources > External Datasets in the left Resources pane.

Step 3 - Configure Dataset

After configuring a Provider, you can create datasets to form a view of the data you want to search. Follow these steps:

1. Go to Resources > External Datasets.

2. Click the PostgreSQL Provider you created in Step 2 - Configure Provider from the left Resources pane, or select the PostgreSQL Provider in the main pane, and click the View () icon.

3. Under Datasets for <Provider Name>, click the New (+) icon.

4. Set the attributes as follows:

   1. In the Dataset Name field, enter a name for the dataset.

   2. If required, set Provider Type to ODBC

   3. If required, set Driver Type to PostgreSQL.

   4. In the Schema field, enter the PostgreSQL schema.

   5. In the Table Name field, enter the PostgreSQL table name.

   6. (Optional) If the data in this dataset corresponds to OCSF Schema, then from the Category drop-down, select the OCSF Category that this dataset corresponds to. Otherwise, select None.

   7. (Optional) If the data in this dataset corresponds to OCSF Schema, then from the Class drop-down, select the OCSF Class that this dataset corresponds to. Otherwise, select None.

   8. Click the Attribute Mapping Edit () icon. Then define a Dataset Attribute and mapped FortiSIEM Observable. You can also define a ClickHouse function for a Dataset Attribute. Multiple Dataset attributes can be mapped to a single FortiSIEM Observable, for example, dataset attributes src_ip_addr, dest_ip_addr, host_ip_addr can all be mapped to IP Address.

5. Click Save and the configuration will be saved in PostgreSQL database.

6. Select the Dataset and click the Apply () icon. The Dataset definition will be deployed to all ClickHouse Data Nodes.

7. If deployment succeeds, then Provision Status will be Success. If the deployment failed, then Provision Status will display Failure. In this situation, Clone () the dataset, fix the error, then perform Apply ().