MongoDB Specific Guide

Registering MongoDB Connections

MongoDB often uses multi-host structures such as Replica Set or Sharded Cluster (Sharding) rather than single host configurations. In this case, you need to create multi-host connections.

1. Understanding Basic MongoDB Connection String Structure

MongoDB connection strings have two types: standard and +srv.

< Standard Connection String Structure >

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

• mongodb:// : standard connection string scheme
• Hostname or IP : Full domain name or IP address of mongodb cluster member hosts
• options : Options related to connection attached after /?. When using multiple options, connect them with &.
  ex. /?authSource=admin&replicaSet=rs0

< +srv Connection String Structure >

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

• mongodb+srv:// : +srv connection string scheme
• FQDN : Fully qualified domain name - Name composed of hostname and domain name combination registered in DNS ex. server1.example.com
• options : Options related to connection attached after /?. When using multiple options, connect them with &.
  ex. /?authSource=admin&replicaSet=rs0

2. Creating MongoDB Connections

1. Navigate to Administrator > Databases > Connection Management > DB Connections menu.
2. Click the Create Connection button in the top right.
3. Select mongoDB in the data source selection step.

3. Setting Up as Replica Set

< Standard Connection >

1. Set appropriate network zone settings in Allowed Zone.
2. Specify Connection Name.
3. Turn on the Cluster switch to activate cluster input, then enter the following information:
   1. Type : Area exposed in connection information. Since Primary role can change dynamically, select the default Primary.
   2. Expose : To allow viewing subordinate nodes (hosts or instances) of the cluster in connection information screen or agent’s connection target list, select “Expandable”, to prevent viewing, select “Unexpandable”, to hide the cluster and show only subordinate nodes (hosts or instances), select “Hidden”.
   3. Connection String :
      1. Select mongodb:// from the Scheme selection options.
      2. Enter subordinate host names and ports separated by commas.
   4. Click the Add Instance button to add each member host as an instance.
      1. Instance Name : Enter a name that can distinguish each host.
      2. Connection String : Enter each host in hostname(domain address or IP):[port] format.
      3. Expose is an option that can determine the exposure of individual instances in connection information.
         To use multi-host functionality, instances must be in expose state, so be sure to activate the Expose option.

1. Authentication DB : Enter the value of Authentication DB (authSource).
2. Other Options : Enter remaining values except authSource among options used in connection string.
   Example 1) When using only single option format ( [Key]=[Value] ) : replicaSet=rs0
   Example 2) When using multiple options format ( [Key]=[Vaule]&[Key]=[Value] ) : replicaSet=rs0&tls=true
   When using replica set configuration, you must enter replicaSet=[replicaset name] option.
3. Secret Store : Option for using 3rd party for password and key management. Default Querypie means managed within QueryPie.
4. User Name & Password : Enter the username and password for the database.
5. Click the Test Connection button to verify if the entered connection information is valid.
6. Complete and save by clicking the Next button.

< +srv Connection >

1. Set appropriate network zone settings in Allowed Zone.
2. Specify Connection Name.
3. Turn on the Cluster switch to activate cluster input, then enter the following information:
   1. Type : Area exposed in connection information. Since Primary role can change dynamically, select the default Primary.
   2. Expose : To allow viewing subordinate nodes (hosts or instances) of the cluster in connection information screen or agent’s connection target list, select “Expandable”, to prevent viewing, select “Unexpandable”, to hide the cluster and show only subordinate nodes (hosts or instances), select “Hidden”.
   3. Connection String :
      1. Select mongodb+srv:// from the Scheme selection options.
      2. Enter domain format host name (FQDN).
      3. Click the Lookup button. If DNS lookup is successful, nodes included in the cluster are automatically added as instances.
   4. Authentication DB and Other Options
      1. Authentication DB value and Other Options are automatically entered with values queried from DNS TXT records.
      2. Since +srv scheme automatically has TLS option as true, when converting to standard string, tls=true must be manually entered. (Because TXT record doesn’t have TLS option.) Therefore, enter &tls=true in Other options field as shown in the figure above.
4. Secret Store : Option for using 3rd party for password and key management. Default Querypie means managed within QueryPie.
5. User Name & Password : Enter the username and password for the database.
6. Click the Test Connection button to verify if the entered connection information is valid.
7. Complete and save by clicking the Next button

4. Setting Up as Sharded Cluster (Sharding)

Sharded Cluster has the same setup method as Replica Set, only the Connection String options are different. Replica Set requires replicaSet=[replica set name] option, but Sharded Cluster doesn’t need this option.

< Standard Connection >

1. Turn on the Cluster switch to activate cluster input, then enter the following information:
   1. Type : Area exposed in connection information. Since Primary role can change dynamically, maintain the Primary value.
   2. Connection String :
      1. Select mongodb:// from the Scheme selection options.
      2. Enter each mongos host name and port separated by commas.
   3. Click the Add Instance button to add each mongos as an instance.
      1. Enter in mongodb://hostname(domain address or IP):[port] format, then enter appropriate Instance Name.
      2. Expose is a value that can determine exposure in connection information.
2. Authentication DB : Enter the value of Authentication DB (authSource).
3. Other Options : Sharded cluster may not need additional options.
4. Secret Store : Option for using 3rd party for password and key management. Default Querypie means managed within QueryPie.
5. User Name & Password : Enter the username and password for the database.
6. Click the Test Connection button to verify if the entered connection information is valid.
7. Complete and save by clicking the Next button.

< +srv Connection >

Just put the tls=true option in Other options after converting to standard string. There’s no difference from replica set setup.

TLS(SSL) Settings When Using Proxy

As mentioned above, when using QueryPie’s SQL editor for connection, tls=true is required in the connection string.

Since +srv scheme automatically has TLS option as true, when converting to standard string, tls=true must be manually entered. (Because TXT record doesn’t have TLS option.) Therefore, enter &tls=true in Other options field as shown in the figure above. When using Proxy, SSL settings are required in QueryPie separately from TLS settings in SQL clients like DataGrip.
Refer to SSL Configurations for setup and reflect in the connection’s SSL settings.

MongoDB Data Policy and Rule Settings

For data requiring access restrictions such as personal information or sensitive information in MongoDB, you can set data access restrictions and masking policies for Collections and JSON Values so that such data cannot be viewed during queries. Since MongoDB among DBMS supported by QueryPie always stores data in JSON format, separate formatting logic is used.

Please check the links below for initial policy creation.

• Data Masking
• Data Access

Registering Rules in Masking Policy

1. After creating a policy, register the data path to which the policy will be applied as rules. You can check that rules are registered in the Rule List tab. Now when users query Documents of the corresponding Collection in MongoDB, the Value of the pre-specified Attribute is masked and displayed according to the applied masking pattern, such as ’*****@gmail.com’.
   1. Click the policy created in the Data Masking menu.
   2. The policy detail information and rule registration screen is displayed, click the Add Rule List button on the right.
   3. Select the data path to which the policy will be applied in order.
      1. Database Name : Required value for rule registration.
      2. Table Name : For MongoDB, enter the Collection name.
      3. Column Name : For MongoDB, enter the JSON Attribute value to apply masking within the Document.
   4. Apply masking pattern.
      1. Select one or more masking patterns to apply to the column selected in step 3.
      2. Basically provides patterns for masking personal information and sensitive information in about 20 types.
      3. You can create detection patterns and masking patterns directly through regular expressions in the Masking Pattern menu.
   5. Exception handling of masking rules can be done for users or groups who need to query the data.
      1. Allowed Users : Select users or groups to exception handle the rule.
   6. Save by clicking the Ok button.

Registering Rules in Collection / JSON Value Access Restriction Policy

After creating a policy, register the data path to which the policy will be applied as rules.

1. Click the policy created in the Data Access menu.
2. The policy detail information and rule registration screen is displayed, click the Add Rule List button on the right.
3. Select the data path to which the policy will be applied in order.
   1. Database Name : Required value for rule registration.
   2. Table Name : Required value for rule registration. When only table is selected, querying the table itself is restricted. For MongoDB, enter the Collection name.
   3. Column Name : For MongoDB, enter the JSON Attribute value to restrict data access for specific Values within Documents.
4. Exception handling of access restriction rules can be done for users or groups who need to query the data.
   1. Allowed Users : Select users or groups to exception handle the rule.
5. Save by clicking the Ok button.

You can check that rules are registered in the Rule List tab. Now when users query the data, if policy is applied to the table, the table itself cannot be queried, and if rules are applied to columns, it is displayed as {RESTRICTED}.

JSON PATH Examples

Examples of MongoDB data to which policies are applied are as follows:

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

Case 1. item Attribute Access Restriction

When setting access restriction policy for one attribute, enter the corresponding item in the “Column Name” field and click the OK button.

As shown in the screenshot above, you can see that the value of the item(“T_PWD”) attribute is displayed as “Restricted Column”.

Case 2. size.h Attribute Access Restriction

When setting access restriction policy for the h attribute under size, enter $.size.h in the “Column Name” field and click the OK button.

As shown in the screenshot above, you can see that the value of the h attribute under size is displayed as “Restricted Column”.

Case 3. All Attributes Under size Access Restriction

When setting access restriction policy for all attributes under size, enter $.size in the “Column Name” field and click the OK button.

As shown in the screenshot above, you can see that the values of all attributes under size are displayed as “Restricted Column”.

Case 4. Access Restriction for 2nd Value in tags Attribute

When setting access restriction policy for the 2nd value in the tags attribute list, enter $.tags[1] in the “Column Name” field and click the OK button.

As shown in the screenshot above, you can see that the 2nd value of the tags attribute is displayed as “Restricted Column”.