MongoDB Configuration Guide

A dedicated detailed configuration guide for MongoDB is provided here.

Registering a MongoDB Connection

MongoDB is often configured in a multi-host structure using Replica Set or Sharded Cluster (Sharding) rather than a single-host setup. In such cases, you need to create a multi-host connection.

1. Understanding the MongoDB Connection String Structure

MongoDB uses two types of connection strings: standard and +srv.

< Standard Connection String Structure >

CODE

mongodb://[Hostname or IP]:[port],[Hostname or IP]:[port],[Hostname or IP]:[port]..../?[options]

mongodb://: Standard connection string scheme
Hostname or IP: Fully qualified domain name or IP address of the MongoDB cluster members.
options: Options related to the connection that follow /?. Use & to separate multiple options.
Example: /?authSource=admin&replicaSet=rs0

When using a replica set, include replicaSet=[replica set name] in the options. (e.g., /?replicaSet=rs0)

< +srv Connection String Structure >

CODE

mongodb+srv://<FQDN>/?[options]

mongodb+srv:// : +srv connection string scheme
FQDN : Fully qualified domain name combining hostname and domain (e.g., server1.example.com).
options : Connection-related options follow /?. Multiple options are separated by &.
Example: /?authSource=admin&replicaSet=rs0

Since the addresses of hosts in a cluster using +srv can change dynamically, QueryPie cannot directly use +srv for connections. Instead, it converts +srv to a standard connection string. However, this conversion process means direct access to sub-instances in a +srv connection is not supported. If direct access to sub-hosts in a +srv environment is necessary, use the nslookup command to find each host's information through DNS queries and create a connection using a standard connection string.

2. Creating a MongoDB Connection

Administrator > Databases > Connection Management > DB Connections > Create Connection

Navigate to the Administrator > Databases > Connection Management > DB Connections > Create Connection menu.
Click the Create Connection button in the upper right corner.
Select mongoDB as the data source.

3. Configuring for a Replica Set

< Standard Connection >

Set the appropriate network zone in Allowed Zone.
Specify a Connection Name.
Enable the Cluster switch to activate cluster input, then enter the following information:
1. Type: Select Primary (default), as the primary role can change dynamically.
2. Expose: Determine how the cluster's sub-nodes (hosts or instances) are displayed:
  - Expandable: Expand sub-nodes in the connection information or agent’s connection list.
  - Unexpandable: Sub-nodes are not expanded, only the cluster is displayed.
  - Hidden: Only sub-nodes are displayed, the cluster itself is hidden.
3. Connection String:
  - Select mongodb:// from the scheme options.
  - Enter hostnames and ports separated by commas.
4. Click Add Instance to add each member host as an instance.
  1. Instance Name: Enter a name to distinguish each host.
  2. Connection String: Enter each host in the format hostname (domain address or IP):[port].
  3. Expose: This option determines whether individual instances are visible in the connection information.
    To use the multi-host feature, each instance must be exposed. Therefore, it is essential to enable the Expose option.

Authentication DB: Enter the value for the authentication database (authSource).
Other Options: Enter additional connection string options, excluding authSource.
Example 1: For a single option, use the format [Key]=[Value] (e.g., replicaSet=rs0).
Example 2: For multiple options, use the format [Key]=[Value]&[Key]=[Value] (e.g., replicaSet=rs0&tls=true).
Note: If you are configuring a replica set, you must include the option replicaSet=[replica set name].
Secret Store: Choose where to manage passwords and keys. The default, QueryPie, means they are managed internally.
User Name & Password: Enter the username and password for the database.
Click Test Connection to verify the connection details.
Click Next to complete and save the connection.

For more detailed information on Cluster mode, refer to the DB Connections documentation.

< +srv Connection >

Set the appropriate network zone in Allowed Zone.
Specify a Connection Name.
Enable the Cluster switch to activate cluster input, then enter the following information:
1. Type: Select Primary (default), as the primary role can change dynamically.
2. Expose: Determine how the cluster's sub-nodes (hosts or instances) are displayed:
  - Expandable: Expand sub-nodes in the connection information or agent’s connection list.
  - Unexpandable: Sub-nodes are not expanded, only the cluster is displayed.
  - Hidden: Only sub-nodes are displayed, the cluster itself is hidden.
3. Connection String:
  - Select mongodb+srv:// from the scheme options.
  - Enter hostnames and ports separated by commas.
4. Connection String :
  1. Scheme 선택항목에서 mongodb+srv:// 를 선택합니다.
  2. Enter the hostname in FQDN format.
  3. Click Lookup to perform a DNS query. If successful, the nodes in the cluster are automatically added as instances.
5. Authentication DB and Other Options:
  1. The values are automatically populated from the DNS TXT record.
  2. For +srv, the tls=true option must be manually added to the standard string after conversion on the Other Options, as it's not included in the TXT record.
Secret Store: Choose where to manage passwords and keys. The default, QueryPie, means they are managed internally.
User Name & Password: Enter the username and password for the database.
Click Test Connection to verify the connection details.
Click Next to complete and save the connection.

4. Configuring for a Sharded Cluster

A Sharded Cluster setup differs from a Replica Set only in the connection string options. A Sharded Cluster does not require the replicaSet option.

< Standard Connection >

Enable the Cluster switch to activate cluster input, then enter the following information:
1. Type: Keep the default value, Primary, as the primary role can change dynamically.
2. Connection String:
  1. Select mongodb:// from the scheme options.
  2. Enter the hostnames and ports of the mongos instances separated by commas.
3. Click Add Instance to add each mongos instance.
  1. Enter the connection string in the format mongodb://[hostname]:[port], and assign an appropriate Instance Name.
  2. Expose: Determine the visibility of the instance in the connection information.
Authentication DB: Enter the value for the authentication database (authSource).
Other Options: Sharded clusters may not require additional options.
Secret Store: Choose where to manage passwords and keys. The default, QueryPie, means they are managed internally.
User Name & Password: Enter the username and password for the database.
Click Test Connection to verify the connection details.
Click Next to complete and save the connection.

< +srv Connection >

After converting to a standard string, ensure the tls=true option is correctly added under Other Options. There are no additional differences from the Replica Set configuration.

For more detailed information on Cluster mode, refer to the DB Connections documentation.

Proxy TLS (SSL) Configuration

When using the QueryPie SQL editor to connect, you must include tls=true in the connection string.

For connections using the +srv scheme, the TLS option is automatically set to true. However, when converting to a standard connection string, you must manually add tls=true in the Other options field because the TXT record does not include the TLS option. Therefore, as shown in the example, add &tls=true to the Other options field.

When using Proxy with SQL clients like DataGrip, you need to configure SSL separately in QueryPie. Please refer to the SSL Configurations document to configure SSL and apply the settings to the connection.

MongoDB Data Policies and Rules Configuration

In MongoDB, data that requires access restrictions, such as personal or sensitive information, can be masked or restricted from view at the Collection or JSON Value level. Since MongoDB stores data in JSON format, specific formatting logic is applied in QueryPie to handle these policies.

To create an initial policy, refer to the following links:

Registering a Masking Policy

Administrator > Databases > Policies > Data Masking > Add Rule List

After creating a policy, register the path to the data where the policy will apply:
1. Click on the policy you created in the Data Masking menu.
2. The policy details and rule registration screen will be displayed. On the right, click the Add Rule List button.
3. Select the following details:
  1. Database Name: Enter the database name.
  2. Table Name: For MongoDB, enter the Collection name.
  3. Column Name: For MongoDB, enter the JSON Attribute name where masking should be applied.
4. Apply data masking:
  1. Apply the desired masking pattern(s) to the selected column(s).
  2. QueryPie provides over 20 built-in patterns for masking personal and sensitive information.
  3. You can also create custom detection and masking patterns using regular expressions in the Masking Pattern menu.
5. You can exempt certain users or groups from the access restriction rule as needed.
  1. Allowed Users: Select the users or groups to exempt from the rule.
6. Click Ok to save the settings.
The rules will now be visible in the Rule List tab. When a user accesses a MongoDB Collection with the applied policy, the specified JSON Attribute values will be masked according to the pattern, such as showing *****@gmail.com.

Registering Access Restriction Policies for Collection/JSON Values

After creating a policy, register the path to the data where the policy will apply:

Administrator > Databases > Policies > Data Access > Add Rule List

Click on the policy you created in the Data Access menu.
The policy details and rule registration screen will be displayed. On the right, click the Add Rule List button.
Select the following details:
1. Database Name: Enter the database name (required).
2. Table Name: Enter the table name (required). In MongoDB, this corresponds to the Collection name. If only the table is selected, the entire table (or Collection) will be restricted from viewing.
3. Column Name: In MongoDB, enter the JSON Attribute name if you want to restrict access to specific values within a Document.
You can exempt certain users or groups from the access restriction rule as needed.
1. Allowed Users: Select the users or groups to exempt from the rule.
Click Ok to save the settings.

The rules will now be visible in the Rule List tab. When users access the data with these policies, the table itself will be inaccessible if the policy is applied at the table level, or restricted columns/values will display {RESTRICTED} if applied at the column level.

JSON PATH Examples

Below are examples of how to apply policies to MongoDB data.

CODE

{
    _id: ObjectId("64b3f7ad344ac8c881a09dc6"),
    item: "journal",
    qty: 25,
    tags: ["blank", "red"],
    size: {
        h: 14,
        w: 21,
        uom: "cm"
    }
}

Case 1. Restricting Access to the item Attribute

To restrict access to a single attribute, enter item in the Column Name field and click OK.

You will see that the value of the item (“T_PWD”) attribute is displayed as “Restricted Column” in the screenshot.

Case 2. Restricting Access to the size.h Attribute

To restrict access to the h attribute under size, enter $.size.h in the Column Name field and click OK.

You will see that the value of the h attribute under size is displayed as “Restricted Column” in the screenshot.

Case 3. Restricting Access to All Attributes under size Attribute

To restrict access to all attributes under size, enter $.size in the Column Name field and click OK.

You will see that all attribute values under size are displayed as “Restricted Column” in the screenshot.

Case 4. Restricting Access to the Second Value in the tags List

To restrict access to the second value in the tags list, enter $.tags[1] in the Column Name field and click OK.

You will see that the second value in the tags list is displayed as “Restricted Column” in the screenshot.