Run Queries on Remote PostgreSQL from Snowflake

Within your AWS Console search for RDS in the search field at the top and select the matching result. Visit the Databases screen, and then click Create Database.

The Standard Create option provides a good set of defaults for creating a RDS Database, so unless you've previous knowledge or experience to know you might want something different I'd suggest choosing "PostgreSQL" and confirming the details and then clicking Create database at the bottom of the screen.

Once you've started the database creation it may take about 15 minutes for provisioning to complete and for your database to be available.

The Snowflake Query Remote PostgreSQL Connector by Ockam is available in the Snowflake Marketplace.

The first screen you're presented with will ask you to select the warehouse to utilize to activate the app.

Click the Grant button to the right of this screen. The app will then be automatically granted permissions to create a warehouse and create a compute pool.

Once the permissions grants complete, an Activate button will appear. Click it and the activation process will begin.

After the app activates you'll see a page that summarizes the privileges that the application now has. There's nothing we need to review or update on these screens yet, so proceed by clicking the Launch app button.

The Ockam Node for Amazon RDS is a streamlined way to provision a managed Ockam Node within your private AWS VPC.

To deploy the node that will allow Snowflake to reach your Amazon RDS PostgreSQL database visit the Ockam Node for Amazon RDS PostgreSQL listing in the AWS Marketplace, and click the Continue to Subscribe button, and then Continue to Configuration.

On the configuration page choose the region that your Amazon RDS cluster is running in, and then click Continue to Launch followed by Launch.

The initial Create Stack screen pre-fills the fields with the correct information for your node, so you can press Next to proceed.

This screen has important details to you need to fill in:

• Stack name: Give this stack a recognisable name, you'll see this in various locations in the AWS Console. It'll also make it easier to clean these resources up later if you wish to remove them.
• VPC ID: The ID of the Virtual Private Cloud network to deploy the node in. Make sure it's the same VPC where you've deployed your RDS instance.
• Subnet ID: Choose same subnet where your RDS instance is deployed.
• Enrollment ticket: Copy the contents of the postgres.ticket file we created earlier and paste it in here.
• RDS PostgreSQL Database Endpoint: In the Connectivity & security for your Amazon RDS Database you will find Endpoint details. Copy the Endpoint value for the Private RDS Database that's in the same subnet you chose above.
• JSON Node Configuration: Copy the contents of the postgres.json file we created earlier and paste it in here.

Once the stack has completed, you will need to modify the security group on your RDS instance to allow access on port 5432 from the instance that was just created.

We've now completed the highlighted part of the diagram below, and our Amazon RDS PostgreSQL node is waiting for another node to connect.

Click "Get started" to open the Snowflake setup screen.

Take the contents of the file snowflake.ticket that we just created and paste it into "Provide the above Enrollment Ticket" form field in the "Configure app" setup screen in Snowflake.

To be able to authenticate with Ockam Orchestrator and then discover the route to our outlet, the Snowflake app needs to allow outbound connections to your Ockam project.

Toggle the Grant access to egress and reach your Project and approve the connection by pressing Connect.

Toggle the Grant access to your postgre database and enter the username and password for your PostgreSQL database and store it as a secret in snowflake.

Enter the name of the PostgreSQL database.