AWS Aurora PostgreSQL
Follow these steps to connect your AWS Aurora PostgreSQL to Select Star.
Last updated
Was this helpful?
Follow these steps to connect your AWS Aurora PostgreSQL to Select Star.
Last updated
Was this helpful?
To connect AWS Aurora to Select Star, you will need...
access to AWS User with with permissions to deploy CloudFormation, modify AWS IAM and AWS Aurora
access to admin user of AWS Aurora
Connect to the PostgreSQL database using an administrative user account and create a new user (service account) for the integration, by executing SQL query:
Replace s313ctst8r with strong and secure password.
Then, it is necessary to grant permissions to selected databases and schemas. To do this, run the following query individually in the context of the selected database for each schema:
Replace <schema_name> with the expected schema name and repeat for each databases & schemas.
To establish a connection between Select Star and your Aurora cluster, it is essential that your Aurora instance is accessible from the following IP addresses:
3.23.108.85
3.20.56.105
If your Aurora cluster is protected by a firewall, you'll need to add these two IP addresses to your whitelist to allow for the connection. If you encounter any challenges or require further assistance in adapting these configurations to your specific network topology, please don't hesitate to reach out to our technical support team for expert guidance and solutions.
To be able to generate lineage and popularity, we need to have access to a log of all queries performed on the instance via AWS CloudWatch. Enabling query logging for an Amazon RDS database and sending the logs to AWS CloudWatch involves several steps. Here's a step-by-step guide to achieve this using the AWS Management Console:
1. Sign in to AWS Console: Log in to your AWS Management Console using your credentials.
2. Open RDS Dashboard: Navigate to the Amazon RDS service in the AWS Management Console.
3. Select the Aurora Cluster: Choose the Amazon Aurora database cluster for which you want to enable query logging.
4. Modify the DB Cluster: In the cluster details page, click the "Modify" button to make changes to the cluster configuration.
5. Enable the Query Logging Parameter: In the "Modify DB Cluster" page, find the "Log exports" section. Look for the "PostgreSQL log" parameter. Set this parameter to "enabled."
6. Apply the Changes: Scroll down to the bottom of the "Modify DB Cluster" page and click "Continue."
7. Review and Apply Changes: Review the changes you're about to make and click "Apply immediately" if you want the changes to take effect immediately. Otherwise, choose a maintenance window for applying the changes. Click "Continue."
8. Create a New Parameter Group: In the RDS dashboard, click on "Parameter groups" on the left-hand navigation pane.
9. Create a New Parameter Group:
In the RDS dashboard, click on "Parameter groups" in the left-hand navigation pane.
Click the "Create parameter group" button.
Provide a name for the new parameter group, e.g., "CustomAuroraParameterGroup."
In the "Family" dropdown, select the appropriate DB engine family. For Aurora, you can choose "aurora-postgresql".
Provide a description for the parameter group (optional).
Click the "Create" button to create the new parameter group.
10. Edit the Parameter Group:
In the parameter group list, find your newly created parameter group, "CustomAuroraParameterGroup" and click on its name.
In the "Parameter group details" page, find the "Parameters" tab.
Click the "Edit parameters" button.
11. Set log_min_duration_statement and log_statement parameters:
In the "Modifiable parameters" page, you can search for parameters. In the search box, type "log_min_duration_statement" and "log_statement" one by one.
For parameter log_min_duration_statement set value to 0 (to log all statements, regardless of duration).
For parameter log_statement set value to all (to log all SQL statements).
After setting these parameters, click the "Save changes" button.
12. Modify the Aurora Instance and Associate the Parameter Group:
In the RDS dashboard, select your Aurora DB instance (not the DB cluster).
Click the "Modify" button for the instance.
In the "DB parameter group" section, select the custom parameter group you created, "CustomAuroraParameterGroup," from the dropdown.
Click "Continue" to proceed with the modification.
Review the changes and click "Apply immediately" or select a maintenance window for the change to take effect. Then click "Continue."
13. Monitor the Update: The changes will be applied to your Aurora instance. You can monitor the progress on the "Databases" page in the RDS dashboard.
14. Verify Query Logging: After the changes have been applied, query logging will be enabled for your Aurora instance, and the logs will be sent to CloudWatch. You can access these logs by navigating to the CloudWatch Logs section of the AWS Management Console. Before accepting credentials, we verify whether the query log has been configured, so it is important that some queries have already been logged.
1. Go to the Select Star Settings. Click Data in the sidebar, then + Add to create a new Data Source.
2. Fill form in the required information:
Source Type: Select "PostgreSQL"
Display Name: This value is PostgreSQL by default, but you can override it if desired.
Hostname: The public hostname of your instance.
Port: The port used to connect. By default is 5432, but you can adjust it if required.
Username: The PostgreSQL user name to connect. In our examples of SQL queries above, we use selectstar.
Password: The password of the PostgreSQL user.
DB Name: Any database we have access to that is used to initiate a first connection.
3. Click Connect.
Select Star recommends use of AWS CloudFormation to setup integration, which allows you to make necessary changes to the AWS IAM in a automatic, transparent, safe and auditable manner.
You can pass the link to CloudFormation to the infrastructure team to enable the integration to be created.
1. A simple form will be displayed in Select Star. In option Source Type select "AWS Aurora PostgreSQL".
2. Click Open CloudFormation. A new window will open to proceed to the creation of a CloudFormation stack by AWS Management Console. Make sure you are logged into the AWS account in which the Amazon Aurora cluster is hosted.
3. The Create Stack form will be displayed. Some of the values will be filled in by default, unnder Parameters, enter:
Aurora cluster name: Use the Amazon Aurora cluster name
4. Review the information and under Capabilities choose "I acknowledge that AWS CloudFormation might create IAM resources".
5. Choose Create stack.
6. Wait until the stack changes it status to "CREATE_COMPLETE" from "CREATE_IN_PROGRESS" in tab "Stack info". The operation should take up to 5 minutes. You need to refresh tab to see the progress.
7. After completing stack creation, the Role ARN is available from the "Outputs". Copy and save the RoleArn for later use.
1. Return to Select Star. You should see a form that allows you to provide "Role ARN" . Fill form in the required information:
Role ARN: Identifier of AWS IAM Role to use by Select Star.
Aurora cluster name: Name of the AWS Aurora cluster.
2. Click Connect.
After you fill in the information, you'll be asked to select the databases you'd like to load into Select Star.
Select the database and click Next.
For each database you selected, you'll be able to select the schemas.
Your metadata should start loading automatically. Please allow 24-48 hours to completely generate popularity and lineage.
When the sync is complete, you'll be able to explore PostgreSQL in Select Star.
The source code of the CloudFormation template along with build scripts and real-time logs of the continuous deployment system is available on our public repository hosted on Github, to be freely audited.
You can you have loaded if needed.
The AWS Aurora logging setup may in some cases result in sensitive information being recorded and stored in Amazon CloudWatch. To assess impact, risk, and take precaution and remediation steps corresponding to your organization's workload characteristics, refer to relevant and AWS support if required.
