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 : This method allows only manual synchronization. After saving, you can click the Synchronize button on the Provider details screen, enter the AWS account's Access Key, and synchronize the resources once.

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, certain provider details cannot be modified:

   1. Name: Changeable

   2. Cloud Provider: Not changeable

   3. Region: Not changeable

   4. Credential: Not changeable

   5. Role ARN: Not changeable

   6. Database Type Filter: Changeable

   7. Replication Frequency: Changeable

Supported Resources for Synchronization

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

• DynamoDB,

• Redshift

• DocumentDB

• Athena

• Redis (ElastiCache)