DB Connections

Overview

The DB Connections page allows you to view and configure the list of database connections registered in QueryPie.

This guide explains the following features:

Registering a DB Connection Manually

Click the Create Connection button in the upper right corner of the DB Connections page.

1. In Step 1, select the data source type and proceed to Step 2.

2. In Step 2, enter the following DB connection information:

   • Allowed Zone : Select the network zone allowed to access the DB connection.

     • Zones can be set in the Allowed Zones menu.

     • Default: Default (All allowed)

   • Connection Name: Enter the name of the DB connection.

   • Cluster: Toggle to activate the cluster registration mode.

     • See the Registering DB Connections in Cluster Mode section below for more details.

   • Host: Enter the host for the DB connection (can be a domain or IP address).

   • Port: Enter the port number for the DB connection.

   • Database Name: Enter the name of the database to connect to.

   • Secret Store: Choose where to store authentication information:

     • QueryPie: Store internally within QueryPie.

     • If other Vaults are registered, their names will be displayed.

     • Additional authentication fields may vary based on the selected storage location.

   • Click the Test Connection button to verify the connection details.

   • Additional Information : Enter additional information.

     • See the Additional Information section below for more details.

   • SSL / SSH Setting : Enter SSL/SSH settings.

     • See the SSL / SSH Setting section below for more details.

   • Click Next to proceed to Step 3.

3. In Step 3, review the entered DB connection information.

4. The newly created DB connection will appear in the DB Connections list.

Registering DB Connections in Cluster Mode

When synchronizing resources through a Cloud Provider, if the resources are configured as a cluster, the Cluster mode is activated.

1. Toggle the Cluster option to enable a hierarchical structure for entering cluster and instance information.

2. If you know the endpoints for each cluster and instance, you can manually register them using the Add Cluster option.

   • Type : Choose the replication type (Primary or Secondary).

     • Permissions can be granted at the cluster level.

   • Expose : Determine how the cluster is displayed in the connection list:

     • Expandable : Display the cluster and instance details.

     • Unexpandable : Display only the cluster without showing instances.

     • Hidden : Hide the cluster and display only the instances in the connection list.

   • Host : Enter the host endpoint corresponding to the cluster.

   • Add Instance : If you need to manage instances at a more granular level, you can add each instance by entering its name and host details.

     • Expose : Decide whether to expose the instance endpoint in the user's Connections list.

       • e.g., If a Secondary cluster has 3 instances and only 2 have Expose set to On, users with Secondary permissions will only be able to connect to the 2 exposed instance endpoints.

What Is Cluster Mode?

Cluster mode is typically used to manage Primary and Secondary clusters and endpoints, especially when synchronizing through a Cloud Provider, where resources are registered in a cluster structure. Even when registering connections manually, the Cluster toggle button allows you to register connections in a cluster structure. Using Cluster mode helps manage access permissions based on the cluster and instance endpoint structure, allowing you to distribute load and manage permissions by granting access to specific instance endpoints for some users.

Viewing DB Connections List

In the DB Connections page within the Databases settings of the administrator console, you can view the list of all DB connections currently registered in QueryPie.

• Search for DB connections by name.

• Available Filters:

  • Database Type: Filter connections by DB type (e.g., MySQL, MariaDB, PostgreSQL, etc.).

  • Cloud Provider: Filter connections by cloud provider type (e.g., AWS, Azure, GCP, or QueryPie Connection).

  • Favorite View: Filter connections based on whether they are marked as favorites.

    • You can set favorites using the button located on the far right of the table.

      

  • SSL Status: Filter by whether SSL is enabled for the connection.

  • Label: Filter to see if the connection is a Ledger DB.

  • Tag: Filter connections based on assigned tags.

    • How to Enter Tags: Input Key → press Enter → input Operator → input Value → press Enter.

      • Supported operators: =, !=, :, !:

    • If multiple tags with the same key are entered, an OR search is performed (union).

    • If multiple tags with different keys are entered, an AND search is performed (intersection).

Viewing and Configuring DB Connection Details

In the DB Connections page, click on the item for which you want to view detailed information. This will take you to the detailed view page.

Viewing Accessible Users

Click the Accessible Users button in the top right of the DB connection details page to view the list of users who have access to the connection.

Edit DB Connection Information

In the Connection Information section, view and modify the connection details entered during creation.

• Allowed Zone : Select the network zone allowed to access the DB connection.

  • Zones can be set in the Allowed Zones menu.

  • Default: Default (All allowed)

• Connection Name: Enter the name of the DB connection.

• Host: Enter the host for the DB connection (can be a domain or IP address).

• Port: Enter the port number for the DB connection.

• Database Name: Enter the name of the database to connect to.

• Secret Store: Choose where to store authentication information:

  • QueryPie: Store internally within QueryPie.

  • If other Vaults are registered, their names will be displayed.

  • Additional authentication fields may vary based on the selected storage location.

Click the Test Connection button to verify the connection details.

After making changes, click Save Changes to apply the updates.

The Secret Store option appears only when the Secret Store usage is enabled in the Security settings. To use the Secret Store feature, you need to register a Vault in the Admin > General > Integration > HashiCorp Vault page. For detailed instructions on setting up and integrating the Secret Store, please refer to the Secret Store Integration documentation.

Note: How to Configure the Secret Store

The display of DB Account fields on the connection information page depends on the Secret Engine type stored in the Secret Store configuration.

• Secret Engine = K/V Configuration:

  • When this type is selected, the Username / Password input fields will appear on the connection information page.

  • In the Username / Password fields, input the Vault Path.

  • The path should be formatted as: prod_db/data/mysql?username.

    • In this example, it is the case which the actual path in the Vault is prod_db > mysql and the key is username.

    • You need to include /data in the path.

• Secret Engine = Database Configuration:

  • When this type is selected, the Account input field will appear on the connection information page.

  • In the Account field, input the Vault Path.

  • The path should be formatted as: prod_db/creds/mysql_role.

Additional Configuration

In the DB Connections page, additional settings for a DB connection can be configured in the tabs located at the bottom of the page. Refer to the descriptions for each item.

Additional Information

This tab allows you to specify detailed policies that will be applied to the DB connection. Click the Save Changes button to save your settings.

• Max Display Rows: Limit the maximum number of rows that can be displayed after executing a query.

• Max Export Rows: Limit the maximum number of rows that can be exported at one time.

• Access Start Time: Set the start time for when the connection can be accessed.

• Access End Time: Set the end time for when the connection can be accessed.

• Weekday Access Denied: Select multiple days on which the connection cannot be accessed.

• Maximum Login Failures: Limit the maximum number of allowed login failures for the connection.

• Specified Time Interval Before Lockout: Set the time interval that defines the lockout period after reaching the maximum login failures.

• Database Version: Enter the database version used by the connection.

• Query Audit On/Off: Choose whether to log query executions for the connection. (Default = On)

• DML Snapshot On/Off: Choose whether to record before-and-after data for DML operations. (Default = Off)

• User Action Purpose Required: Set actions for which users must provide a reason for the operation.

  • Allow Reusing the Same Reason Throughout a Session: Allow users to reuse the same reason within a session.

  • Export Schema Purpose: Set whether to require a reason when exporting data schema (Export > SQL > Schema).

  • Import Schema Purpose: Set whether to require a reason when importing data schema (Import > SQL > Schema).

  • Export Data Purpose: Set whether to require a reason when exporting data (Export > CSV / JSON / EXCEL).

  • Import Data Purpose: Set whether to require a reason when importing data (Import > CSV / JSON / EXCEL).

  • SQL Execute: Set whether to require a reason when executing SQL queries and viewing tables.

• Proxy Usage: Select whether to use a proxy for the connection. Refer to the Enable Database Proxy Settings documentation for more details.

• Character Set: Choose the character set to be used for the connection.

• Collation: Choose the collation method for the selected character set.

• Description: Enter a description for the connection.

SSL/SSH Setting

Activate SSL or SSH by checking the relevant boxes, and then select pre-registered settings from the dropdown menu. Click the Save Changes button to save your settings.

SSL/SSH configuration information must be pre-registered. Refer to the following documents:

• SSL Configurations

• SSH Configurations

Connection Owner

In the Connection Owner tab, click the Add Connection Owner button to assign a Connection Owner to each DB connection. Owners can be designated on a user or group basis. Click Save Changes to finalize the settings.

A Connection Owner can be designated as an approver or executor for SQL Requests or SQL Export Requests related to the DB connection.

Tags

In the Tags tab, you can register and manage tags for each DB connection. Click the Add Tag button to add a row for entering the Key and Value of the tag. After completing the entries, click Save Changes to save the tags.

Privilege Setting

This tab allows you to force specific DB accounts to be used for each privilege.

The Privilege Setting tab is displayed only when Advanced Privilege Setting is activated in the Security settings. Refer to the DB Connection Security section in the Security documentation for more information.

1. Click the Add button to select a privilege and enter the DB Username and Password that will be fixed for that privilege.

2. Use the Test Connection button to verify that the entered credentials can successfully connect.

3. Click Save Changes to save and finalize the privilege settings.

Note: BigQuery, Dynamo, Athena, Impala, Presto, Trino, and Redis do not support the Privilege Setting feature.

• When privileges with enforced settings are assigned to users, they must use the DB account mapped to that privilege when connecting to the database. The DB Username and Password fields will not be visible in the Connections page.

• The enforced DB account will be used across all connection methods, including the web editor, agent proxy, SQL Request, and Export Request.