Synchronize DB Resources in AWS

Overview

QueryPie supports integration with AWS for database registration and management. You can synchronize resources from AWS and register them as managed databases in QueryPie. Additionally, you can assign access permissions to users and groups and configure policies for the synchronized databases.

Registering AWS Integration in QueryPie

1. Navigate to the Administrator > Databases > Cloud Providers menu.

2. Click the Create Provider button in the upper right corner.

3. Name: Enter a name to distinguish the provider.

4. Cloud Provider: Select Amazon Web Services.

5. Region: Choose the region where the resources to be synchronized are located.

6. Credential: Enter the credentials required for synchronization.

   1. Refer to the Setting Up Credential Types section below for more details.

7. Database Type Filter: Select the resources to be synchronized.

8. Replication Frequency: Choose the synchronization method:

   • Manual: Synchronize only when triggered manually.

   • Scheduling: Synchronize on a regular schedule using Cron Expressions.

9. Click Save to register the Cloud Provider.

Setting Up Credential Types

• Default Credentials (Instance Profile) : If the QueryPie server is installed on the same AWS account, you can sync resources within the same AWS environment by assigning policies to the IAM role of the EC2 instance where QueryPie is installed. Assign the appropriate policy based on the resources needed.

• Cross Account Role : This method allows synchronization of resources from a different AWS account by creating an IAM role. Follow the on-screen steps to generate the necessary permissions and assign policies. (To use this credential, set the AWS account ID where QueryPie is installed in the AWS_ACCOUNT_ID environment variable.)

• Access Key : By clicking the Synchronize button, a manual synchronization method is provided by default, where you input the AWS account's access key and secret key.

  • Starting from QueryPie 10.2.2, the "Save Credential for Synchronization" option has been added, allowing synchronization through a schedule even when using the access key as the credential type.

Save Credential for Synchronization Option

• This option, once enabled and saved, cannot be disabled from the synchronization settings detail page, so it should be selected carefully. The saved credential cannot be replaced. If a different credential is needed, a new synchronization setting must be created. If a credential change is required, it is recommended to create a new credential with the same permissions, create a new synchronization setting, and then delete the existing synchronization setting.

• Synchronization settings saved without this option enabled can have the option activated by checking the checkbox on the detail page.

• When this option is enabled, synchronization can be done manually, or a schedule can be set.

Required Policies for Database Resource Synchronization:

• RDS : AmazonRDSReadOnlyAccess

• DynamoDB : AmazonDynamoDBFullAccess

• Redshift : AmazonRedshiftReadOnlyAccess

• Athena : AmazonAthenaFullAccess, AmazonS3DFullAccess

• Redis : AmazonElastiCacheFullAccess

Synchronizing and Managing AWS Cloud Providers

1. Navigate to the Administrator > Databases > Cloud Providers menu.

2. Click on the registered Cloud Provider to open the details screen.

3. Click the Synchronize button in the upper right corner to sync resources from AWS.

4. You can monitor synchronization progress in the Synchronization Log or view the history under Administrator > General > Systems > Jobs.

5. Once a Cloud Provider is registered, the provider's basic information and authentication details cannot be changed.

   1. Name: Changeable

   2. Cloud Provider: Not changeable

   3. Region: Not changeable

   4. Credential: Not changeable

      1. "Save Credential for Synchronization": The option can be changed from inactive to active, but it cannot be switched from active to inactive.

   5. Role ARN: Not changeable

   6. Database Type Filter: Changeable

   7. Replication Frequency: Changeable

Synchronization settings saved without the "Save Credential for Synchronization" option enabled can have the option activated by checking the checkbox on the detail page. Just like when creating a new setting, once this option is activated, it cannot be deactivated again, so it should be selected carefully.

Supported Resources for Synchronization

• Amazon RDS (MySQL, MariaDB, PostgreSQL, SQL Server, Oracle)

• DynamoDB,

• Redshift

• DocumentDB

• Athena

• Redis (ElastiCache)